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

2、 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_levvel =5);生成

3、報(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) );ccommitt ;endd ;

4、/查看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_HOMEErdbmmsadmminspptruncc.sqlsnap

5、shoot的levvel，這可可以通過(guò)EXXEC STTATSPAACK.MOODIFY_STATSSPACK_PARAMMETER(i_snaap_levvel=N)來(lái)修改，NN可以為0，55，6，7，110，缺 省省為5。 0 僅提提供一般性能能統(tǒng)計(jì)5 增增加了對(duì)SQQL語(yǔ)句總體體分析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);oracle不不僅提供生成成數(shù)據(jù)庫(kù)

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

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

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

9、位。可可設(shè)置的值最最小為10分分鐘，最大為為1年。如果果設(shè)置該參數(shù)數(shù)值為0，就就表示禁用AAWR特性。Topnsqll ：指定收收集的比較占占用資源的SSQL數(shù)量，可可設(shè)置的值最最小為30，最最大不超過(guò)11000000000。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_his

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

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

12、LLE_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 publi

13、ic;-授授權(quán)所有用戶(hù)戶(hù)要在數(shù)據(jù)庫(kù)中建建立一個(gè)角色色plusttrace,用sys用用戶(hù)運(yùn)行腳本本plusttrce.ssql來(lái)創(chuàng)建建這個(gè)角色，這這個(gè)腳本在目目錄 （UNNIX:$OORACLEE_HOMEE/sqlpplus/aadmin, Winddows:%ORACLLE_HOMME%sqqlplussadmiin）中；SQL CC:oraacleoora92sqlpllusaddminpplustrrce.sqql;然后將角色pllustraace授予需需要autootracee的用戶(hù)；SQLgraant pllustraace too publlic;經(jīng)過(guò)以上步驟的的設(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: 從磁磁盤(pán)讀取的bblock的的數(shù)量reddo sizze: DMML生成的rredo的大大小sortts (meemory):在內(nèi)存執(zhí)執(zhí)行的排序量量sortss (dissk):在磁磁

15、盤(pán)上執(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 roo

16、ws 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 sizee1335002 byytes ssent vvia SQQL*Nett to cclientt3252 bytees recceivedd vi

18、a SQL*NNet frrom cllient2255 SSQL*Neet rouundtriips too/fromm clieent0 sortss (memmory)00 sorrts (ddisk)33809 rows proceessedb。EXPLAAIN的使用用 Oraacle RRDBMS執(zhí)執(zhí)行每一條SSQL語(yǔ)句，都都必須經(jīng)過(guò)OOraclee優(yōu)化器的評(píng)評(píng)估。所以，了了解優(yōu)化器是是如何選擇(搜索)路徑徑以及索引是是如何被使用用的，對(duì)優(yōu)化化SQL語(yǔ)句句有很大的幫幫助。Expplain可可以用來(lái)迅速速方便地查出出對(duì)于給定SSQL語(yǔ)句中中的查詢(xún)數(shù)據(jù)據(jù)是如何得到到的即搜索路路徑(我們通通常稱(chēng)為Acccess Path)。從而使我我們選擇最優(yōu)優(yōu)的查詢(xún)方式式達(dá)到最大的的優(yōu)化效果。1.1 、安 裝 要使用EXPPLAIN首首先要執(zhí)行相相應(yīng)的腳本，創(chuàng)創(chuàng)建出Expplain_plan表表。具體腳本執(zhí)行如如下： $ORAACLE_HHOME/rrdbms/adm