oracle數(shù)據(jù)庫級(jí)別優(yōu)化分析工具介紹

1、oracle數(shù)據(jù)庫級(jí)別優(yōu)化分析工具介紹當(dāng)我們對(duì)數(shù)據(jù)庫庫優(yōu)化診斷時(shí)時(shí)，需要收集集相應(yīng)的信息息以供參考，從從個(gè)人的使用用經(jīng)驗(yàn)來說，這這種統(tǒng)計(jì)數(shù)據(jù)據(jù)分為兩大類類一類是數(shù)據(jù)據(jù)庫級(jí)別的統(tǒng)統(tǒng)計(jì)信息二類類是os級(jí)別別的統(tǒng)計(jì)信息息下面就分別介紹紹在不同的級(jí)級(jí)別下，常用用什么工具來來收集信息幫幫助優(yōu)化診斷斷首先是oraccle數(shù)據(jù)庫庫級(jí)別優(yōu)化分分析工具介紹紹目錄：1.statsspack22.ASH33.AWR44.ORACCLE EXXPLAINN PLANN的總結(jié)(查查詢sql的的執(zhí)行計(jì)劃)a.auttotracceb.exxplainn的使用1.statsspacka。安裝sql sqqlpluss /

2、aas syssdbaSQL seelect file_name from dba_ddata_ffiles;SQL crreate tableespacee perffstat dataffile e:orracleoradaataskkatepperfsttat.dbbf siize 20000m;sql OORACLEE_HOMEErdbmmsadmminsppcreatte.sqllb。使用SQL coonn peerfstaat/passswd收集統(tǒng)計(jì)信息ssql eexecutte staatspacck.snaap或SQL exec statsspack.SNAP(i_snaap

3、_levvel =5);生成報(bào)告sqll ORRACLE_HOMErdbmssadmiinsprreportt.sql定時(shí)收集信息有有兩種方式，一一種是oraacle jjob，一種種是os的ccrontaab，我比較較習(xí)慣用oss級(jí)別的crrontabb設(shè)定其每個(gè)小時(shí)時(shí)自動(dòng)收集一一次采樣的jjobdeclareeVariaable job numbeer ;beegindbbms_joob.subbmit(:job, stattspackk.snapp; ,ttrunc( sysddate + 1/244 , hhh24 ), ttrunc(sysdaate+1/24,hhh24) );cc

4、ommitt ;endd ;/查看job使用用情況SQL seelect job,sschemaa_userr,nextt_datee,inteerval,what from user_jobs自動(dòng)停止采樣jjobdeclareeVariaable job numbeer ;beegindbbms_joob.subbmit(:job, dbmss_job.brokeen(44,true); ,ttrunc( sysddate + 1 ), nulll );commiit ;ennd ;/清空所有staats統(tǒng)計(jì)信信息表里的數(shù)數(shù)據(jù)sql OORACLEE_HOMEErdbmmsadmminspp

5、truncc.sqlsnapshoot的levvel，這可可以通過EXXEC STTATSPAACK.MOODIFY_STATSSPACK_PARAMMETER(i_snaap_levvel=N)來修改，NN可以為0，55，6，7，110，缺 省省為5。 0 僅提提供一般性能能統(tǒng)計(jì)5 增增加了對(duì)SQQL語句總體體分析6 增增加了SQLL計(jì)劃和使用用7 增加了了分段（Seegmentts）級(jí)的統(tǒng)統(tǒng)計(jì)10增加加了對(duì)閂鎖（LLatchees）的分析析其中文檔建議對(duì)對(duì)10要慎重重，因?yàn)榇鷥r(jià)價(jià)較高。eg：SQL execc stattspackk.SNAPP(i_snnap_leevel =6);ora

6、cle不不僅提供生成成數(shù)據(jù)庫報(bào)告告的腳本sppreporrt.sqll，還提供另另一個(gè)staatspacck 報(bào)告腳腳本spreepsql.sql來生生成SQL的的報(bào)告sql OORACLEE_HOMEErdbmmsadmminspprepsqql.sqll參考文檔：利用用statsspack來來獲取生成環(huán)環(huán)境中topp SQL及及其執(zhí)行計(jì)劃劃 HYPERLINK /Doc/statspack_report_sql.htm http:/DDoc/sttatspaack_reeport_sql.hhtm2.AWRawr是建庫是是自動(dòng)配置和和啟用的，他他對(duì)性能數(shù)據(jù)據(jù)的收集默認(rèn)認(rèn)是一小時(shí)，aawr對(duì)

7、歷史史數(shù)據(jù)的分析析生成報(bào)告腳本在在目錄下生成成報(bào)告使用$ORACLLE_HOMME/rdbbms/addmin/，如如下：awrrpt.sql ：生成指定快快照區(qū)間的統(tǒng)統(tǒng)計(jì)報(bào)表；aawrrptti.sqll ：生成指指定數(shù)據(jù)庫實(shí)實(shí)例，并且指指定快照區(qū)間間的統(tǒng)計(jì)報(bào)表表；awrssqlrptt.sql ：生成指定定快照區(qū)間，指指定SQL語語句(實(shí)際指指定的是該語語句的SQLLID)的統(tǒng)統(tǒng)計(jì)報(bào)表；aawrsqrrpi.sqql ：生成成指定數(shù)據(jù)庫庫實(shí)例，指定定快照區(qū)間的的指定SQLL語句的統(tǒng)計(jì)計(jì)報(bào)表；awwrddrppt.sqll ：指定兩兩個(gè)不同的時(shí)時(shí)間周期，生生成這兩個(gè)周周期的統(tǒng)計(jì)對(duì)對(duì)比報(bào)表；a

8、awrddrrpi.sqql ：指定定數(shù)據(jù)庫實(shí)例例，并指定兩兩個(gè)的不同時(shí)時(shí)間周期，生生成這兩個(gè)周周期的統(tǒng)計(jì)對(duì)對(duì)比報(bào)表；修改Snapsshots設(shè)設(shè)置通過過MODIFFY_SNAAPSHOTT_SETTTINGS過過 程，DBBA可以調(diào)整整包括快照收收集頻率、快快照保存時(shí)間間、以及捕獲獲的SQL數(shù)數(shù)量三個(gè)方面面的設(shè)置。 分別對(duì)應(yīng)MMODIFYY_SNAPPSHOT_SETTIINGS 的的三個(gè)參數(shù)：Retentiion ：設(shè)設(shè)置快照保存存的時(shí)間，單單位是分鐘?？稍O(shè)置的值值最小為1天天，最大為1100年。設(shè)設(shè)置該參數(shù)值值為0的話，就就表示永久保保留收集的快快照信息。Intervaal ：設(shè)置置快照

9、收集的的頻率，以分分鐘為單位。可設(shè)置的值值最小為100分鐘，最大大為1年。如如果設(shè)置該參參數(shù)值為0，就就表示禁用AAWR特性。Topnsqll ：指定收收集的比較占占用資源的SSQL數(shù)量，可可設(shè)置的值最最小為30，最最大不超過。AWR相關(guān)幾個(gè)個(gè)視圖：DBA_HISST_WR_CONTRROL：查看看當(dāng)前快照收收集的相關(guān)設(shè)設(shè)置v$acctive_sessiion_hiistoryy:由ASHH自動(dòng)在 內(nèi)內(nèi)存中維護(hù)，以以每秒一次的的頻率收集當(dāng)當(dāng)前系統(tǒng)中活活動(dòng)sesssion的信信息dba_hist_activve_sesss_hisstory:是視圖 vv$actiive_seessionn_hi

10、sttory的歷歷史數(shù)據(jù)，保保存在硬盤上上dba_hhist_ddatabaase_innstancce:顯示數(shù)數(shù)據(jù)庫 是實(shí)實(shí)例的信息ddba_hiist_snnapshoot:當(dāng)前數(shù)數(shù)據(jù)庫收集到到的快照信息息3.ASHash和awrr不是完全分分離的兩個(gè)功功能，ashh以秒為單位位從v$seessionn中收集信息息并保存在內(nèi)內(nèi)存中，這塊塊內(nèi)存可以重重用，內(nèi)存滿滿時(shí)，ASHH數(shù)據(jù) 交給給AWR，最最后寫入系統(tǒng)統(tǒng)視圖ash包括兩部部分內(nèi)容，一一部分是SGGA中的，這這部分反映是是本次系統(tǒng)啟啟動(dòng)以來的數(shù)數(shù)據(jù)，并且AASH盡量保保留1小時(shí)的的內(nèi)容，這部部分內(nèi)容保存存在 v$aactivee_ses

11、ssion_hhistorry另一部分分保存在系統(tǒng)統(tǒng)字典表里ddba_hiist_acctive_sess_histoory,是永永久的數(shù)據(jù)ASH也有生成成報(bào)告的腳本本，在目錄下下$ORACCLE_HOOME/rddbms/aadmin/ashrpt.sql: 生成數(shù)據(jù)庫庫級(jí)別的ASSH統(tǒng)計(jì)報(bào)表表ashrppti.sqql: 生成成數(shù)據(jù)庫實(shí)例例級(jí)別的ASSH統(tǒng)計(jì)報(bào)表表，常用于RRAC單實(shí)例例4.ORACLLE EXPPLAIN PLAN的的總結(jié)(查詢?cè)僺ql的執(zhí)執(zhí)行計(jì)劃)a.autottrace安裝用sys用戶運(yùn)運(yùn)行腳本ulltxplaan.sqll建立這個(gè)表的腳腳本是：（UUNIX:$ORA

12、CLLE_HOMME/rdbbms/addmin, Windoows:%OORACLEE_HOMEE%rdbbmsaddmin）uultxpllan.sqql。SQL coonnectt HYPERLINK mailto:sys/syscolm2 sys/sysccolm2 as syysdba;SQL CC:oraacleoora92rdbmssadmiinutllxplann.sql;SQL crreate publiic synnonym plan_tablee for plan_tablee;-建立立同義詞SQL grrant aall onn plann_tablle to publ

13、iic;-授授權(quán)所有用戶戶要在數(shù)據(jù)庫中建建立一個(gè)角色色plusttrace,用sys用用戶運(yùn)行腳本本plusttrce.ssql來創(chuàng)建建這個(gè)角色，這這個(gè)腳本在目目錄 （UNNIX:$OORACLEE_HOMEE/sqlpplus/aadmin, Winddows:%ORACLLE_HOMME%sqqlplussadmiin）中；SQL CC:oraacleoora92sqlpllusaddminpplustrrce.sqql;然后將角色pllustraace授予需需要autootracee的用戶；SQLgraant pllustraace too publlic;經(jīng)過以上步驟的的設(shè)置，就可可以

14、在sqll*pluss中使用auutotraace了，aautotrrace功能能只能在SQQL*PLUUS里使用AUTOTRAACE Sttatisttics常用用列解釋 db bloock geets :從從buffeer cacche中讀取取的blocck的數(shù)量cconsisstent gets:從bufffer caache中讀讀取的unddo數(shù)據(jù)的bblock的的數(shù)量phyysicall readds: 從磁磁盤讀取的bblock的的數(shù)量reddo sizze: DMML生成的rredo的大大小sortts (meemory):在內(nèi)存執(zhí)執(zhí)行的排序量量sortss (dissk):在磁

15、磁盤上執(zhí)行的的排序量eg: HYPERLINK mailto:SYSdbset SYSdbset aautotrrace Ussage: SET AAUTOTRACE OFFF | ONN | TRRACEOONLY EXPPLAINN SSTATIISTICSS HYPERLINK mailto:SYSdbset SYSdbset ttimingg on HYPERLINK mailto:SYSdbset SYSdbset aautot tracee exp stat HYPERLINK mailto:SYSdbselect SYSdbselecct * ffrom ttab;3809 ro

16、ows seelecteed.Elapsedd: 00:00:000.06Executiion Pllan-| IId | Operaation | NName | Roows | Bytees | CCost (%CPU)|-| 00 | SEELECT STATEEMENT | | 10559 | 446596 | 2216 (2)| 1 | NEESTED LOOPSS OUTEER | | 10599 | 466596 | 2116 (2)| 2 | TAABLE AACCESSS FULLL | OBJ$ | 1059 | 391183 | 1588 (22)| 3 | TABBLE

17、 ACCCESS CLUSTTER| TTAB$ | 1 | 7 | 1 (0)| 4 | INDDEX UNNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|-Note- PLAN_TABLEE is old vversioonStatisttics-0 rrecurssive ccalls00 db blockk getss9077 conssistennt getts0 pphysiccal reeads0 redoo sizee byttes seent viia SQLL*Net to cllient33252 bytess receeived via SSQL*N

18、eet froom cliient2555 SQQL*Nett rounndtripps to/from cliennt0 ssorts (memoory)0 sortts (diisk)38809 rrows pprocesssedb。EXPLAAIN的使用用 Oraacle RRDBMS執(zhí)執(zhí)行每一條SSQL語句，都都必須經(jīng)過OOraclee優(yōu)化器的評(píng)評(píng)估。所以，了了解優(yōu)化器是是如何選擇(搜索)路徑徑以及索引是是如何被使用用的，對(duì)優(yōu)化化SQL語句句有很大的幫幫助。Expplain可可以用來迅速速方便地查出出對(duì)于給定SSQL語句中中的查詢數(shù)據(jù)據(jù)是如何得到到的即搜索路路徑(我們通通常稱為Acccess Path)。從而使我我們選擇最優(yōu)優(yōu)的查詢方式式達(dá)到最大的的優(yōu)化效果。1.1 、安 裝 要使用EXPPLAIN首首先要執(zhí)行相相應(yīng)的腳本，創(chuàng)創(chuàng)建出Expplain_plan表表。具體腳本執(zhí)行如如下： $ORAACLE_HHOME/rrdbms/admi