1、oraclle數據庫庫級別優化化分析工具具介紹當我們對數數據庫優化化診斷時，需需要收集相相應的信息息以供參考考，從個人人的使用經經驗來說，這這種統計數數據分為兩兩大類一類類是數據庫庫級別的統統計信息二二類是oss級別的統統計信息下面就分別別介紹在不不同的級別別下，常用用什么工具具來收集信信息幫助優優化診斷首先是orraclee數據庫級級別優化分分析工具介介紹目錄：1.staatspaack2.ASH33.AWRR4.ORRACLEE EXPPLAINN PLAAN的總結結(查詢ssql的執執行計劃)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收集統計信信息sqll exxecutte sttatsppack.snapp或SQL exe

3、ec sttatsppack.SNAPP(i_ssnap_leveel =5);生成報告ssql ORAACLE_HOMEErdbbmsaadminnsprreporrt.sqql定時收集信信息有兩種種方式，一一種是orraclee jobb，一種是是os的ccronttab，我我比較習慣慣用os級級別的crrontaab設定其每個個小時自動動收集一次次采樣的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自動停止采采樣jobbdeclaareVaariabble job nummber ;beggindbbms_jjob.ssubmiit(:jjob, dbmms_joob.brrokenn(44,truee); ,truunc( sysddate + 1 ), nulll );commmit ;end ;/清空

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

6、，因因為代價較較高。eg：SQQL eexec stattspacck.SNNAP(ii_snaap_leevel =6);oraclle不僅提提供生成數數據庫報告告的腳本ssprepport.sql，還還提供另一一個staatspaack 報報告腳本sspreppsql.sql來來生成SQQL的報告告sql ORAACLE_HOMEErdbbmsaadminnsprrepsqql.sqql參考文檔：利用sttatsppack來來獲取生成成環境中ttop SSQL及其其執行計劃劃 HYPERLINK /Doc/statspack_report_sql.htm httpp:/Doc/statts

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

8、數據庫庫實例，指指定快照區區間的指定定SQL語語句的統計計報表；aawrdddrpt.sql ：指定兩兩個不同的的時間周期期，生成這這兩個周期期的統計對對比報表；awrdddrpii.sqll ：指定定數據庫實實例，并指指定兩個的的不同時間間周期，生生成這兩個個周期的統統計對比報報表；修改Snaapshoots設置置通過過MODIIFY_SSNAPSSHOT_SETTTINGSS過 程，DDBA可以以調整包括括快照收集集頻率、快快照保存時時間、以及及捕獲的SSQL數量量三個方面面的設置。 分別對對應MODDIFY_SNAPPSHOTT_SETTTINGGS 的三三個參數：Retenntionn

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

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

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

12、 生生成數據庫庫實例級別別的ASHH統計報表表，常用于于RAC單單實例4.ORAACLE EXPLLAIN PLANN的總結(查詢sqql的執行行計劃)a.auttotraace安裝用sys用用戶運行腳腳本ulttxplaan.sqql建立這個表表的腳本是是：（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;-授權所有有用戶要在數據庫庫中建立一一個角色pplusttracee,用syys用戶運運行腳本pplusttrce.sql來來創建這個個角色，這這個腳本在在目錄 （UUNIX:$ORAACLE_HOMEE/sqllpluss/admmin, Winddows:%ORAACLE_HOMEE%

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

15、量conssisteent ggets:從bufffer cachhe中讀取取的unddo數據的的blocck的數量量physsicall reaads: 從磁盤讀讀取的bllock的的數量reedo ssize: DMLL生成的rredo的的大小soorts (memmory):在內存存執行的排排序量soorts (dissk):在在磁盤上執執行的排序序量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執執行每一條條SQL語語句，都必必須經過OOraclle優化器器的評估。所以，了了解優化器器是如何選選擇(搜索索)路徑以以及索引是是如何被使使用的，對對優化SQQL語句有有很大的幫幫助。Exxplaiin可以用用來迅速方方便地查出出對于給定定SQL語語句中的查查詢數據是是如何得到到的即搜索索路徑(我我們通常稱稱為Acccess Pathh)。從而而使我們選選擇最優的的查詢方式式達到最大大的優化效效果。1.1 、安 裝 要使用EEXPLAAIN首先先要執行相相應的腳本本，創建出出Expllain_plann表。具體腳本執執行如下： $OORACLLE_HOOME/rrdbmss/ad