oracle數(shù)據(jù)庫級別優(yōu)化分析工具介紹
轉(zhuǎn)貼(
http://blog.csdn.net/wyzxg/archive/2010/03/04/5346974.aspx)
當我們對數(shù)據(jù)庫優(yōu)化診斷時，需要收集相應(yīng)的信息以供參考，從個人的使用經(jīng)驗來說，這種統(tǒng)計數(shù)據(jù)分為兩大類
一類是數(shù)據(jù)庫級別的統(tǒng)計信息
二類是os級別的統(tǒng)計信息
下面就分別介紹在不同的級別下，常用什么工具來收集信息幫助優(yōu)化診斷
首先是oracle數(shù)據(jù)庫級別優(yōu)化分析工具介紹
目錄：
1.statspack
2.ASH
3.AWR
4.ORACLE EXPLAIN PLAN的總結(jié)(查詢sql的執(zhí)行計劃)
a.autotrace
b.explain的使用
1.statspack
a。安裝
sql> sqlplus "/ as sysdba"
SQL> select file_name from dba_data_files;
SQL> create tablespace perfstat datafile 'e:\oracle\oradata\skate\perfstat.dbf' size 2000m;
sql> @ORACLE_HOME\rdbms\admin\spcreate.sql
b。使用
SQL> conn perfstat/passwd
收集統(tǒng)計信息
sql> execute statspack.snap
或
SQL> exec statspack.SNAP(i_snap_level =>5);
生成報告
sql> @ORACLE_HOME\rdbms\admin\spreport.sql
定時收集信息有兩種方式，一種是oracle job，一種是os的crontab，我比較習慣用os級別的crontab
設(shè)定其每個小時自動收集一次采樣的job
declare
Variable job number ;
begin
dbms_job.submit(:job, "statspack.snap;" ,trunc( sysdate + 1/24 , 'hh24' ), "trunc(sysdate+1/24,'hh24')" );
commit ;
end ;
/
查看job使用情況
SQL> select job,schema_user,next_date,interval,what from user_jobs
自動停止采樣job
declare
Variable job number ;
begin
dbms_job.submit(:job, "dbms_job.broken(44,true);" ,trunc( sysdate + 1 ), "null" );
commit ;
end ;
/
清空所有stats統(tǒng)計信息表里的數(shù)據(jù)
sql> @ORACLE_HOME\rdbms\admin\sptrunc.sql
snapshot的level，這可以通過EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=N)來修改，N可以為0，5，6，7，10，缺省為5。
0 僅提供一般性能統(tǒng)計
5 增加了對SQL語句總體分析
6 增加了SQL計劃和使用
7 增加了分段（Segments）級的統(tǒng)計
10增加了對閂鎖（Latches）的分析
其中文檔建議對10要慎重，因為代價較高。
eg：
SQL> exec statspack.SNAP(i_snap_level =>6);
oracle不僅提供生成數(shù)據(jù)庫報告的腳本spreport.sql，還提供另一個statspack 報告腳本sprepsql.sql來生成SQL的報告
sql> @ORACLE_HOME\rdbms\admin\sprepsql.sql
參考文檔：
利用statspack來獲取生成環(huán)境中top SQL及其執(zhí)行計劃
http://www.hellodba.com/Doc/statspack_report_sql.htm2.AWR
awr是建庫是自動配置和啟用的，他對性能數(shù)據(jù)的收集默認是一小時，awr對歷史數(shù)據(jù)的分析
生成報告腳本在目錄下生成報告使用$ORACLE_HOME/rdbms/admin/，如下：
awrrpt.sql ：生成指定快照區(qū)間的統(tǒng)計報表；
awrrpti.sql ：生成指定數(shù)據(jù)庫實例，并且指定快照區(qū)間的統(tǒng)計報表；
awrsqlrpt.sql ：生成指定快照區(qū)間，指定SQL語句(實際指定的是該語句的SQLID)的統(tǒng)計報表；
awrsqrpi.sql ：生成指定數(shù)據(jù)庫實例，指定快照區(qū)間的指定SQL語句的統(tǒng)計報表；
awrddrpt.sql ：指定兩個不同的時間周期，生成這兩個周期的統(tǒng)計對比報表；
awrddrpi.sql ：指定數(shù)據(jù)庫實例，并指定兩個的不同時間周期，生成這兩個周期的統(tǒng)計對比報表；
修改Snapshots設(shè)置
通過MODIFY_SNAPSHOT_SETTINGS過程，DBA可以調(diào)整包括快照收集頻率、快照保存時間、以及捕獲的SQL數(shù)量三個方面的設(shè)置。 分別對應(yīng)MODIFY_SNAPSHOT_SETTINGS 的三個參數(shù)：
Retention ：設(shè)置快照保存的時間，單位是分鐘?？稍O(shè)置的值最小為1天，最大為100年。設(shè)置該參數(shù)值為0的話，就表示永久保留收集的快照信息。
Interval ：設(shè)置快照收集的頻率，以分鐘為單位?？稍O(shè)置的值最小為10分鐘，最大為1年。如果設(shè)置該參數(shù)值為0，就表示禁用AWR特性。
Topnsql ：指定收集的比較占用資源的SQL數(shù)量，可設(shè)置的值最小為30，最大不超過100000000。
AWR相關(guān)幾個視圖：
DBA_HIST_WR_CONTROL：查看當前快照收集的相關(guān)設(shè)置
v$active_session_history:由ASH自動在內(nèi)存中維護，以每秒一次的頻率收集當前系統(tǒng)中活動session的信息
dba_hist_active_sess_history:是視圖 v$active_session_history的歷史數(shù)據(jù)，保存在硬盤上
dba_hist_database_instance:顯示數(shù)據(jù)庫是實例的信息
dba_hist_snapshot:當前數(shù)據(jù)庫收集到的快照信息
3.ASH
ash和awr不是完全分離的兩個功能，ash以秒為單位從v$session中收集信息并保存在內(nèi)存中，這塊內(nèi)存可以重用，內(nèi)存滿時，ASH數(shù)據(jù)交給AWR，最后寫入系統(tǒng)視圖
ash包括兩部分內(nèi)容，一部分是SGA中的，這部分反映是本次系統(tǒng)啟動以來的數(shù)據(jù)，并且ASH盡量保留1小時的內(nèi)容，這部分內(nèi)容保存在 v$active_session_history另一部分保存在系統(tǒng)字典表里dba_hist_active_sess_history,是永久的數(shù)據(jù)
ASH也有生成報告的腳本，在目錄下$ORACLE_HOME/rdbms/admin/
ashrpt.sql: 生成數(shù)據(jù)庫級別的ASH統(tǒng)計報表
ashrpti.sql: 生成數(shù)據(jù)庫實例級別的ASH統(tǒng)計報表，常用于RAC單實例
4.ORACLE EXPLAIN PLAN的總結(jié)(查詢sql的執(zhí)行計劃)
a.autotrace
安裝
用sys用戶運行腳本ultxplan.sql
建立這個表的腳本是：（UNIX
ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin）ultxplan.sql。
SQL> connect sys/sys@colm2 as sysdba;
SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql;
SQL> create public synonym plan_table for plan_table;--建立同義詞
SQL> grant all on plan_table to public;--授權(quán)所有用戶
要在數(shù)據(jù)庫中建立一個角色plustrace,用sys用戶運行腳本plustrce.sql來創(chuàng)建這個角色，這個腳本在目
錄（UNIX
ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%\sqlplus\admin）中；
SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql;
然后將角色plustrace授予需要autotrace的用戶；
SQL>grant plustrace to public;
經(jīng)過以上步驟的設(shè)置，就可以在sql*plus中使用autotrace了，autotrace功能只能在SQL*PLUS里使用
AUTOTRACE Statistics常用列解釋
db block gets :從buffer cache中讀取的block的數(shù)量
consistent gets:從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量
physical reads: 從磁盤讀取的block的數(shù)量
redo size: DML生成的redo的大小
sorts (memory):在內(nèi)存執(zhí)行的排序量
sorts (disk):在磁盤上執(zhí)行的排序量
eg:
SYS@db>set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SYS@db>set timing on
SYS@db>set autot trace exp stat
SYS@db>select * from tab;
3809 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1059 | 46596 | 216 (2)|
| 1 | NESTED LOOPS OUTER | | 1059 | 46596 | 216 (2)|
| 2 | TABLE ACCESS FULL | OBJ$ | 1059 | 39183 | 158 (2)|
| 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)|
| 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|
---------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9077 consistent gets
0 physical reads
0 redo size
133502 bytes sent via SQL*Net to client
3252 bytes received via SQL*Net from client
255 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3809 rows processed
b。EXPLAIN的使用
Oracle RDBMS執(zhí)行每一條SQL語句，都必須經(jīng)過Oracle優(yōu)化器的評估。所以，了解優(yōu)化器是如何選擇(搜索)路徑以
及索引是如何被使用的，對優(yōu)化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對于給定SQL語句中的查
詢數(shù)據(jù)是如何得到的即搜索路徑(我們通常稱為Access Path)。從而使我們選擇最優(yōu)的查詢方式達到最大的優(yōu)化效果。
1.1 、安 裝
要使用EXPLAIN首先要執(zhí)行相應(yīng)的腳本，創(chuàng)建出Explain_plan表。
具體腳本執(zhí)行如下：
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 該腳本后會生成一個表這個程序會創(chuàng)建一個名為plan_table的表。
1.2 、使用
常規(guī)使用語法：
explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ]
FOR < sql_statement >
其中：
STATEMENT_ID：是一個唯一的字符串，把當前執(zhí)行計劃與存儲在同一PLAN中的其它執(zhí)行計劃區(qū)別開來。
TABLE_NAME：是plan表名，它結(jié)構(gòu)如前所示，你可以任意設(shè)定這個名稱。
SQL_STATEMENT：是真正的SQL語句。
比如：
SQL>explain plan set statement_id='T_TEST' for select * from t_test;
SQL>
Explained
執(zhí)行下面語句可以查詢到執(zhí)行計劃
SQL>SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
2 FROM PLAN_TABLE a
3 WHERE STATEMENT_ID='T_TEST'
4 ORDER BY Id;
也可以用這句話 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE里的數(shù)據(jù)羅列出來。
