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

1、oracle數(shù)據(jù)庫級別優(yōu)化分析工具介紹當我們對數(shù)據(jù)庫優(yōu)化診斷時，需要收集相應(yīng)的信息以供參考，從個人的使用經(jīng)驗來說，這種統(tǒng)計數(shù)據(jù)分為兩大類一類是數(shù)據(jù)庫級別的統(tǒng)計信息二類是os級別的統(tǒng)計信息下面就分別介紹紹在不同的級級別下，常用用什么工具來來收集信息幫幫助優(yōu)化診斷斷首先是oraccle數(shù)據(jù)庫庫級別優(yōu)化分分析工具介紹紹目錄：1.statsspack22.ASH33.AWR44.ORACCLE EXXPLAINN PLANN的總結(jié)(查查詢sql的的執(zhí)行計劃)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)計信息ssql eexecutte staatspacck.snaap或SQL exec statsspack.SNAP(i_snaap_levvel =5);生成

3、報告sqll ORRACLE_HOMErdbmssadmiinsprreportt.sql定時收集信息有有兩種方式，一一種是oraacle jjob，一種種是os的ccrontaab，我比較較習慣用oss級別的crrontabb設(shè)定其每個小時時自動收集一一次采樣的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自動停止采樣jjobdeclareeVariaable job numbeer ;beegindbbms_joob.subbmit(:job, dbmss_job.brokeen(44,true); ,ttrunc( sysddate + 1 ), nulll );commiit ;ennd ;/清空所有staats統(tǒng)計信信息表里的數(shù)數(shù)據(jù)sql OORACLEE_HOMEErdbmmsadmminspptruncc.sqlsnap

5、shoot的levvel，這可可以通過EXXEC STTATSPAACK.MOODIFY_STATSSPACK_PARAMMETER(i_snaap_levvel=N)來修改，NN可以為0，55，6，7，110，缺 省省為5。 0 僅提提供一般性能能統(tǒng)計5 增增加了對SQQL語句總體體分析6 增增加了SQLL計劃和使用用7 增加了了分段（Seegmentts）級的統(tǒng)統(tǒng)計10增加加了對閂鎖（LLatchees）的分析析其中文檔建議對對10要慎重重，因為代價價較高。eg：SQL execc stattspackk.SNAPP(i_snnap_leevel =6);oracle不不僅提供生成成數(shù)據(jù)庫

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

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

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

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

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

11、sion_hhistorry另一部分分保存在系統(tǒng)統(tǒng)字典表里ddba_hiist_acctive_sess_histoory,是永永久的數(shù)據(jù)ASH也有生成成報告的腳本本，在目錄下下$ORACCLE_HOOME/rddbms/aadmin/ashrpt.sql: 生成數(shù)據(jù)庫庫級別的ASSH統(tǒng)計報表表ashrppti.sqql: 生成成數(shù)據(jù)庫實例例級別的ASSH統(tǒng)計報表表，常用于RRAC單實例例4.ORACLLE EXPPLAIN PLAN的的總結(jié)(查詢詢sql的執(zhí)執(zhí)行計劃)a.autottrace安裝用sys用戶運運行腳本ulltxplaan.sqll建立這個表的腳腳本是：（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)所有用戶戶要在數(shù)據(jù)庫中建建立一個角色色plusttrace,用sys用用戶運行腳本本plusttrce.ssql來創(chuàng)建建這個角色，這這個腳本在目目錄 （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 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語句，都都必須經(jīng)過OOraclee優(yōu)化器的評評估。所以，了了解優(yōu)化器是是如何選擇(搜索)路徑徑以及索引是是如何被使用用的，對優(yōu)化化SQL語句句有很大的幫幫助。Expplain可可以用來迅速速方便地查出出對于給定SSQL語句中中的查詢數(shù)據(jù)據(jù)是如何得到到的即搜索路路徑(我們通通常稱為Acccess Path)。從而使我我們選擇最優(yōu)優(yōu)的查詢方式式達到最大的的優(yōu)化效果。1.1 、安 裝 要使用EXPPLAIN首首先要執(zhí)行相相應(yīng)的腳本，創(chuàng)創(chuàng)建出Expplain_plan表表。具體腳本執(zhí)行如如下： $ORAACLE_HHOME/rrdbms/adm