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

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

2、 sqlpplus / aas syysdbaaSQL seleect ffile_namee froom dbba_daata_ffiless;SQL creaate ttableespacce peerfsttat ddataffile e:oraccleooradaatasskateeperrfstaat.dbbf ssize 20000m;sql ORAACLE_HOMEErdbbmsaadminnspccreatte.sqqlb。使用SQL connn perrfstaat/paasswdd收集統(tǒng)計(jì)信信息sqll exxecutte sttatsppack.snapp或SQL exe

3、ec sttatsppack.SNAPP(i_ssnap_leveel =5);生成報(bào)告ssql ORAACLE_HOMEErdbbmsaadminnsprreporrt.sqql定時(shí)收集信信息有兩種種方式，一一種是orraclee jobb，一種是是os的ccronttab，我我比較習(xí)慣慣用os級(jí)級(jí)別的crrontaab設(shè)定其每個(gè)個(gè)小時(shí)自動(dòng)動(dòng)收集一次次采樣的jjobdeclaareVaariabble job nummber ;beggindbbms_jjob.ssubmiit(:jjob, staatspaack.ssnap; ,ttruncc( syysdatte + 1/244 , h

4、h244 ), trrunc(sysddate+1/244,hhh24) );commmit ;end ;/查看jobb使用情況況SQL seleect jjob,sschemma_usser,nnext_datee,inttervaal,whhat ffrom userr_jobbs自動(dòng)停止采采樣jobbdeclaareVaariabble job nummber ;beggindbbms_jjob.ssubmiit(:jjob, dbmms_joob.brrokenn(44,truee); ,truunc( sysddate + 1 ), nulll );commmit ;end ;/清空

5、所有sstatss統(tǒng)計(jì)信息息表里的數(shù)數(shù)據(jù)sql ORAACLE_HOMEErdbbmsaadminnspttruncc.sqllsnapsshot的的leveel，這可可以通過EEXEC STATTSPACCK.MOODIFYY_STAATSPAACK_PPARAMMETERR(i_ssnap_leveel=N)來修改，NN可以為00，5，66，7，110，缺 省為5。 0 僅提供一一般性能統(tǒng)統(tǒng)計(jì)5 增增加了對SSQL語句句總體分析析6 增加加了SQLL計(jì)劃和使使用7 增增加了分段段（Seggmentts）級(jí)的的統(tǒng)計(jì)100增加了對對閂鎖（LLatchhes）的的分析其中文檔建建議對100要慎重

6、，因因?yàn)榇鷥r(jià)較較高。eg：SQQL eexec stattspacck.SNNAP(ii_snaap_leevel =6);oraclle不僅提提供生成數(shù)數(shù)據(jù)庫報(bào)告告的腳本ssprepport.sql，還還提供另一一個(gè)staatspaack 報(bào)報(bào)告腳本sspreppsql.sql來來生成SQQL的報(bào)告告sql ORAACLE_HOMEErdbbmsaadminnsprrepsqql.sqql參考文檔：利用sttatsppack來來獲取生成成環(huán)境中ttop SSQL及其其執(zhí)行計(jì)劃劃 HYPERLINK /Doc/statspack_report_sql.htm httpp:/Doc/statts

7、pacck_reeportt_sqll.htmm2.AWRRawr是建建庫是自動(dòng)動(dòng)配置和啟啟用的，他他對性能數(shù)數(shù)據(jù)的收集集默認(rèn)是一一小時(shí)，aawr對歷歷史數(shù)據(jù)的的分析生成報(bào)告腳腳本在目錄錄下生成報(bào)報(bào)告使用$ORACCLE_HHOME/rdbmms/addmin/，如下：awrrppt.sqql ：生生成指定快快照區(qū)間的的統(tǒng)計(jì)報(bào)表表；awrrrptii.sqll ：生成成指定數(shù)據(jù)據(jù)庫實(shí)例，并并且指定快快照區(qū)間的的統(tǒng)計(jì)報(bào)表表；awrrsqlrrpt.ssql ：生成指定定快照區(qū)間間，指定SSQL語句句(實(shí)際指指定的是該該語句的SSQLIDD)的統(tǒng)計(jì)計(jì)報(bào)表；aawrsqqrpi.sql ：生成指指定

8、數(shù)據(jù)庫庫實(shí)例，指指定快照區(qū)區(qū)間的指定定SQL語語句的統(tǒng)計(jì)計(jì)報(bào)表；aawrdddrpt.sql ：指定兩兩個(gè)不同的的時(shí)間周期期，生成這這兩個(gè)周期期的統(tǒng)計(jì)對對比報(bào)表；awrdddrpii.sqll ：指定定數(shù)據(jù)庫實(shí)實(shí)例，并指指定兩個(gè)的的不同時(shí)間間周期，生生成這兩個(gè)個(gè)周期的統(tǒng)統(tǒng)計(jì)對比報(bào)報(bào)表；修改Snaapshoots設(shè)置置通過過MODIIFY_SSNAPSSHOT_SETTTINGSS過 程，DDBA可以以調(diào)整包括括快照收集集頻率、快快照保存時(shí)時(shí)間、以及及捕獲的SSQL數(shù)量量三個(gè)方面面的設(shè)置。 分別對對應(yīng)MODDIFY_SNAPPSHOTT_SETTTINGGS 的三三個(gè)參數(shù)：Retenntionn

9、 ：設(shè)置置快照保存存的時(shí)間，單單位是分鐘鐘。可設(shè)置置的值最小小為1天，最最大為1000年。設(shè)設(shè)置該參數(shù)數(shù)值為0的的話，就表表示永久保保留收集的的快照信息息。Interrval ：設(shè)置快快照收集的的頻率，以以分鐘為單單位。可設(shè)設(shè)置的值最最小為100分鐘，最最大為1年年。如果設(shè)設(shè)置該參數(shù)數(shù)值為0，就就表示禁用用AWR特特性。Topnssql ：指定收集集的比較占占用資源的的SQL數(shù)數(shù)量，可設(shè)設(shè)置的值最最小為300，最大不不超過10000000000。AWR相關(guān)關(guān)幾個(gè)視圖圖：DBA_HHIST_WR_CCONTRROL：查查看當(dāng)前快快照收集的的相關(guān)設(shè)置置v$acctivee_sesssionn_hi

10、sstoryy:由ASSH自動(dòng)在在 內(nèi)存中中維護(hù)，以以每秒一次次的頻率收收集當(dāng)前系系統(tǒng)中活動(dòng)動(dòng)sesssion的的信息dbba_hiist_aactivve_seess_hhistoory:是是視圖 vv$acttive_sesssion_histtory的的歷史數(shù)據(jù)據(jù)，保存在在硬盤上ddba_hhist_dataabasee_insstancce:顯示示數(shù)據(jù)庫 是實(shí)例的的信息dbba_hiist_ssnapsshot:當(dāng)前數(shù)據(jù)據(jù)庫收集到到的快照信信息3.ASHHash和aawr不是是完全分離離的兩個(gè)功功能，assh以秒為為單位從vv$sesssionn中收集信信息并保存存在內(nèi)存中中，這塊內(nèi)內(nèi)

11、存可以重重用，內(nèi)存存滿時(shí)，AASH數(shù)據(jù)據(jù) 交給AAWR，最最后寫入系系統(tǒng)視圖ash包括括兩部分內(nèi)內(nèi)容，一部部分是SGGA中的，這這部分反映映是本次系系統(tǒng)啟動(dòng)以以來的數(shù)據(jù)據(jù)，并且AASH盡量量保留1小小時(shí)的內(nèi)容容，這部分分內(nèi)容保存存在 v$actiive_ssessiion_hhistoory另一一部分保存存在系統(tǒng)字字典表里ddba_hhist_actiive_ssess_histtory,是永久的的數(shù)據(jù)ASH也有有生成報(bào)告告的腳本，在在目錄下$ORACCLE_HHOME/rdbmms/addmin/ashrppt.sqql: 生生成數(shù)據(jù)庫庫級(jí)別的AASH統(tǒng)計(jì)計(jì)報(bào)表asshrptti.sqql:

12、 生生成數(shù)據(jù)庫庫實(shí)例級(jí)別別的ASHH統(tǒng)計(jì)報(bào)表表，常用于于RAC單單實(shí)例4.ORAACLE EXPLLAIN PLANN的總結(jié)(查詢sqql的執(zhí)行行計(jì)劃)a.auttotraace安裝用sys用用戶運(yùn)行腳腳本ulttxplaan.sqql建立這個(gè)表表的腳本是是：（UNNIX:$ORACCLE_HHOME/rdbmms/addmin, Winndowss:%ORRACLEE_HOMME%rrdbmssadmmin）uultxpplan.sql。SQL connnect HYPERLINK mailto:sys/syscolm2 sys/syscolmm2 ass syssdba;SQL C:ora

13、ccleoora922rdbbmsaadminnutllxplaan.sqql;SQL creaate ppubliic syynonyym pllan_ttablee forr plaan_taable;-建立立同義詞SQL grannt alll onn plaan_taable to ppubliic;-授權(quán)所有有用戶要在數(shù)據(jù)庫庫中建立一一個(gè)角色pplusttracee,用syys用戶運(yùn)運(yùn)行腳本pplusttrce.sql來來創(chuàng)建這個(gè)個(gè)角色，這這個(gè)腳本在在目錄 （UUNIX:$ORAACLE_HOMEE/sqllpluss/admmin, Winddows:%ORAACLE_HOMEE%

14、sqqlpluusaddmin）中中；SQL C:oraccleoora922sqllplussadmminpplusttrce.sql;然后將角色色plusstracce授予需需要auttotraace的用用戶；SQLggrantt pluustraace tto puublicc;經(jīng)過以上步步驟的設(shè)置置，就可以以在sqll*pluus中使用用autootracce了，aautottracee功能只能能在SQLL*PLUUS里使用用AUTOTTRACEE Staatisttics常常用列解釋釋 db bblockk getts :從從bufffer ccachee中讀取的的blocck的數(shù)量

15、量conssisteent ggets:從bufffer cachhe中讀取取的unddo數(shù)據(jù)的的blocck的數(shù)量量physsicall reaads: 從磁盤讀讀取的bllock的的數(shù)量reedo ssize: DMLL生成的rredo的的大小soorts (memmory):在內(nèi)存存執(zhí)行的排排序量soorts (dissk):在在磁盤上執(zhí)執(zhí)行的排序序量eg: HYPERLINK mailto:SYSdbset SYSddbseet auutotrrace Usaage: SET AUTOOTRAACE OFFF | OON | TRACCEONNLY EXXPLAAIN STTATIIS

16、TICCS HYPERLINK mailto:SYSdbset SYSddbseet tiimingg on HYPERLINK mailto:SYSdbset SYSddbseet auutot tracce exxp sttat HYPERLINK mailto:SYSdbselect SYSddbseelectt * ffrom tab;3809 rowss sellecteed.Elapssed: 00:000:000.06Execuutionn Plaan-| Idd | Operratioon | Namee | Rowws | Byttes | Cosst (%CPU)|-| 0

17、 | SEELECTT STAATEMEENT | | 10599 | 4465966 | 2166 (2)| 11 | NESTTED LLOOPSS OUTTER | | 10559 | 465996 | 2116 (2)| 2 | TAABLE ACCEESS FFULL | OBJ$ | 10059 | 391183 | 1158 (2)| 3 | TTABLEE ACCCESS CLUSSTER| TABB$ | 1 | 7 | 1 (00)| 4 | INDDEX UUNIQUUE SCCAN | I_OBJ# | 11 | | 00 (0)|-Note- PPLAN_TABLLE

18、 iis olld veersioonStatiisticcs-00 reecurssive callls0 db bblockk getts90777 cconsiistennt geets0 phyysicaal reeads00 reedo ssize11335002 bbytess sennt viia SQQL*Neet too cliient33252 byttes rreceiived via SQL*Net fromm cliient2255 SQL*Net rounndtriips tto/frrom ccliennt0 sortts (mmemorry)0 sorrts (

19、diskk)38009 rrows proccesseedb。EXPPLAINN的使用 OOraclle RDDBMS執(zhí)執(zhí)行每一條條SQL語語句，都必必須經(jīng)過OOraclle優(yōu)化器器的評估。所以，了了解優(yōu)化器器是如何選選擇(搜索索)路徑以以及索引是是如何被使使用的，對對優(yōu)化SQQL語句有有很大的幫幫助。Exxplaiin可以用用來迅速方方便地查出出對于給定定SQL語語句中的查查詢數(shù)據(jù)是是如何得到到的即搜索索路徑(我我們通常稱稱為Acccess Pathh)。從而而使我們選選擇最優(yōu)的的查詢方式式達(dá)到最大大的優(yōu)化效效果。1.1 、安 裝 要使用EEXPLAAIN首先先要執(zhí)行相相應(yīng)的腳本本，創(chuàng)建出出Expllain_plann表。具體腳本執(zhí)執(zhí)行如下： $OORACLLE_HOOME/rrdbmss/ad