<轉>oracle效能調整讀書筆記(1)
最近重讀 (sybex)_OCP Oracle9i Performance Tuning Study Guide.pdf 時作了一些讀書筆記，感覺一邊理解教材一邊作些筆記，過些天再重閱這些筆記，效果要好很多。 &4M, ) Q (
E Ig:@o&Jj
現陸續將這些筆記貼出來，歡迎大家討論和指正。 bQu@ .'O!k
%Rm `YH?
第二章 調整的資訊來源 2e({ %P@2?
G?:5L 0g
_s Czee&uQ
Alert Log 檔案中記錄的與效能有關的資訊： % 6:" tu A
1. ORA-01652，不能擴充套件臨時段； I5rAL\y-G
2. ORA-01653，不能擴充套件表段； )hfI,9 I~
3. ORA-01650，不能擴充套件回滾段；
4. ORA-01631，到達了表的最大的Extents； Q nIF{T S=
5. 檢查點未完成（Checkpoint not complete）； LJ*W&y(2>Q
6. 執行緒推進日誌序列（Thread n Advanced to Log Sequence n）； 0? 'v| 5}
7. ORA-01555，快照過舊。 9 ?+?V }o
R!RgQwEak
後臺跟蹤檔案 $HFimU,V=0
自動生成 Uc ]s W cR
相關引數：BACKUPGROUND_DUMP_DEST *|3G"B{ w6
K,f - w2!
事件跟蹤檔案 7 DW HADr
設定後生成 ^wb$wtL ('
相關引數：EVENT， BACKUPGROUND_DUMP_DEST， USER_DUMP_DEST U oG+d u[
*Z V=4[#bT
使用者跟蹤檔案 v |;} }ol
相關引數：SQL_TRACE，USER_DUMP_DEST， MAX_DUMP_FILE_SIZE rFG_ C C2
啟用使用者跟蹤的三種方法： NrWgaPO)i
1. 例項層次的跟蹤：配置引數SQL_TRACE = TRUE | FALSE，然後重啟例項； )wfqGkr=m!
2. 使用者層次的自行實置：Alter session set sql_trace = true | false； !9vq"J~hz"
3. 使用者層次的DBA設定：Exec dbms_system.set_sql_trace_in_session(SID, SERIAL#, TRUE | FALSE) + `l )W`zX
)^LiA L h
V$檢視和DBA_檢視 %v, a3^Qu
區別： #B!
1. V$檢視通常是單數，DBA檢視通常是複數，例如V$DATAFILE與DBA_DATA_FILES； `xF^ 9;5mi
2. 當資料庫處於Nomout或者Mount時，許多V$檢視已經是可用的，而DBA檢視必須在資料庫處於Open時才可用； ND\ M
3. V$檢視查詢出來的資料多小寫，DBA檢視查詢出的資料通常大寫，所以在寫WHERE條件時需特別小心； <7Ry"z6g;
4. V$檢視中包含的是自例項啟動以來的動態資料，在資料庫關閉後會消失，查詢V$檢視時必須關注時效性，DBA檢視中包含的是靜態資料； ^!* nhs%
5. V$檢視的基表是X$表，X$表是存在於記憶體中的虛表，DBA檢視的基表是資料字典表，如SYS.OBJ$， SYS.FILE$等，這兩種基表都很少有文件。 ,U ?^ u%
$tB `dDj
最常用的V$檢視： AS/z 1M_U
表名 描述 aI_[h v
V$SGASTAT 顯示SGA元件大小的資訊 Hb 3t|
V$EVENT_NAME 顯示當前版本的所有等待事件 =B0AG 9Fz
V$SYSTEM_EVENT 自例項啟動已來的等待事件 1 ' f &
V$SESSION_EVENT 目前連線會話的等待事件 ?10L *PD@
V$SESSION_WAIT 目前連線會話正在發生的等待事件 $g _|U:,
V$STATNAME 顯示當前版本的所有統計名稱 ~(\ . j=x
V$SYSSTAT 自例項啟動以來的統計 lP *p7Y '
V$SESSTAT 目前連線會話的統計 = Oz pI
V$SESSION 目前連線會話的資訊 '0 v ]?mM
V$WAITSTAT 塊競爭的統計 Y RQ?: a{H
j{ YIVX
最常用的DBA檢視（有些欄位的值需要分析表或索引後才會有值）： 2=,d.1E3 d
表名 描述 1 dO B|
DBA_TABLES 表的儲存，統計等 T ?C QgVR
DBA_INDEXES 索引的儲存，統計等 ']N\y6=fn9
INDEX_STATS 索引的深度和鍵值的離散度等 Bf ;<3k)5.
DBA_DATA_FILES 資料檔案的命名，位置，大小 F~ /~_9 RJ
DBA_SEGMENTS 段的相關資訊 9{ 0%M
DBA_HISTOGRAMS 表，索引的柱狀圖定義資訊 #
m~4ik 1 wq
&3Sm Tg %
Oracle 提供的指令碼和包 eN`G2eE
下面提到的指令碼都位於%ORACLE_HOME%\RDBMS\ADMIN目錄下，有些只需執行一次，有些需要每次都執行。 aGAr24] y
· UTLBSTAT.SQL 和 UTLESTAT.SQL 5mZwg (si
這些指令碼的功能絕大部分已被STATSPACK所取代。 uX%$3k
· STATSPACK i~)EU F
Ø 配置STATSPACK：在SQL*Plus下執行spcreate.sql，這個指令碼執行時呼叫另外三個指令碼（spcusr.sql，spctab.sql，spcpkg.sql），執行過程中會詢問perfstat方案的密碼，預設表空間和臨時表空間，可以通過這三個指令碼的執行日誌（spcusr.lis，spctab.lis，spcpkg.lis）檢視配置是否成功。 hZ Wk w{c
Ø perfstat方案的預設表空間需要有足夠的空間來容納目前的物件和將來的快照資料； [.6 b xK
Ø 檢視日誌如果發現有錯誤，執行spdrop.sql來清除已建立的物件，然後重新執行spcreate.sql； 1=e(g#Ajn\
Ø 配置成功後生成快照有兩種方法：手工執行exec statspack.snap; 執行spauto.sql指令碼，建立job來定時生成快照； BB_(!om q[
Ø 生成效能報表：執行spreport.sql指令碼，指令碼執行過程中要求輸入三個引數：開始快照號，結束快照號，報表名；兩次選擇的快照號需要是在例項的一次執行期間的不同點的快照，否則就沒有參考意義； GkI{7GD: z
Ø statspack報表含有各個方面的效能資料，如何理解這些資料貫穿整本書。 D *IeG>%
sCp)o, ;
第三章 SQL 調整 1.SQL效能測量工具 s,l* =<
}*O8 ] lG
;0NJX)GL
TKPROF PO^#G @
全名為Trace Kernel Profile，用來格式化跟蹤檔案，是一個命令列工具，基本的格式如下： bH :C/P
tkprof tracefile outputfile … P" Z1K5>2L
可選引數及說明如下： Vup|*d2r0E
Option Description oq b (w+ <
EXPLAIN 為每個SQL語句產生執行計劃，指定使用者方案和密碼，使用其中的PLAN_TABLE表 @gmo;8? k
TABLE 當EXPLAIN選項生效時可用，指定替代PLAN_TABLE的表 Rm6i [y&
SYS 啟用該選項時不包含遞迴呼叫 M m Ft G-
SORT 指定SQL語句的排序方式 |CQ0{1R1
RECORD 指定檔名，將SQL語句寫入，排除遞迴的SQL U Ls'oT)K;
PRINT 限定只輸出指定數量的分析SQL語句 a Sg K h
INSERT 指定一個檔名，容納相關的插入SQL語句，將分析的SQL及統計插入TKPROF_TABLE BxZ7B k
AGGREGATE 指定多使用者共用的SQL語句統計方式 1s(i \ & B
WAITS 指定是否統計跟蹤檔案中的等待事件 ~dP\0x0AB
(.L?sDQ
SORT選項可分三類，解析選項，執行選項和取資料選項（PRS,EXE,FCH），根椐這三種操作佔用相關資源來排序。 17 0r5
1'8-+ ? r
TKPROF針對SQL的統計也分為三類：解析，執行，取數，具體的統計選項見下表： <=w! :
統計名稱 統計描述 ) r)Zm S5O
Count Parse,Execute,Fetch發生的次數 ( v? rZ v
CPU 佔用CPU秒 v ( S h+p
Elapsed 經過秒 'fk6]& -I
Disk 從磁碟讀取的資料塊數 pK" Z9y&
Query 從SGA中讀取的回滾段塊數 s B ^ejH
Current 從SGA中讀取的一致性資料塊數 3"k n5)x
Rows 執行INSERT，UPDATE，DELETE影響的行數或者FETCH返回的行數 "} H 2dn2n
(NJ .\m
從TKPROF的統計結果觀察，發現下列特徵的SQL可能需要調優： 9RkNRB )8
1. 消耗過度的CPU資源； o=x M a A
2. 花費太長的時間在Parse，Execute，Fetch階斷； ` 9P`f4 x
3. 從磁碟讀資料塊太多而從SGA中讀塊很少； t - fmA?\
4. 存取太多的資料塊（從SGA或者磁碟）而返回的行數很少； z3>4 xn {
TOP SQL J 6 rWe
這個工具從Enterprise Manager Console中啟動，有與TKPROF類似的統計，用於確定需要調整的SQL語句。 Z / 4bxO=m
av:%wJUl,$
EXPLAIN PLAN 8+3 2hg@^F
用EXPLAIN PLAN FOR sql語句產生執行計劃，然後再從PLAN_TABLE中查詢；
執行計劃中各步驟的執行順序遵照如下原則： $~?)E;S
1. 從上到下； bQ I.Qk
2. 從右到左（或者說，從縮排最多的到縮排最少的）； -B- H Z_
3. 索引操作不參與上面的規則，索引從屬於表操作，先於表操作。 ]5M T-q U
y S
執行計劃的詳盡解釋超出本書範圍，需要參考其它資料； u~mpZ"9$ 3
AUTOTRACE W v ,?xm
AUTOTRACE綜合了TKPROF和EXPLAIN PLAN FOR的功能；
配置AUTOTRACE包括以下步驟： ygUX] *m!
1. 保證需要AUTOTRACE功能的使用者下有PLAN_TABLE或者有該表的全域性同義詞和那個基表的存取許可權； gAsjkN t?
2. 建立PLUSTRACE角色，並將該角色許可權賦給相應的使用者，或者相應會話有這個角色對應的許可權； C?6q ]k]r
3. 相關指令碼：%ORACLE_HOME%\rdbms\admin\utlxplan.sql，%ORACLE_HOME%\sqlplus\admin\plustrce.sql +|4olK$[
?t T89m3_E
AUTOTRACE的特點： !wro7ilMB
1. 只能在SQL*Plus的會話中執行； F g2/rC: _
2. 產生執行計劃前會實際執行SQL，這點與EXPLAIN PLAN FOR不同； - , $:^ 4
3. 在發出SQL之前，需執行SET AUTOTRACE ON語句。 a4q02 cV
$! g ~p V
SET AUTOTRACE語句各選項的意義： X hOg >
選項 描述 j ?! / #'
ON 顯示查詢結果，執行計劃，統計 @,p n /[
ON STATISTICS 顯示查詢結果，統計 X(C=O? A
ON EXPLAIN 顯示查詢結果，執行計劃 m WVq > ~
TRACEONLY 顯示執行計劃，統計 U a1Z,~ *
TRACEONLY STATISTICS 顯示統計 [Lf8 * U"
OFF 關掉AUTOTRACE IXR%IggJA
$,1KD3;+]
STATSPACK :Z3]Dk;y
STATSPACK中也包含幾種方法排序的SQL，通常只顯示排序值大於一定閥值的SQL，這些閥值可能改變： ban;HGGNG{
SQL型別 說明 ?a ~# `<
SQL ordered by Gets 根椐邏輯I/O來排序 ->@i w!5xu
SQL ordered by Reads 根椐物理I/O來排序 R ^ 6^ {q
SQL ordered by Executions 根椐執行次數來排序 *Di ;Gf@
SQL (Executions) ordered by Parse Calls 根椐分析次數來排序 DA/ \[w?J
lJU]sZ9~b
第三章 SQL 調整 2. 理解ORACLE優化器 PkDh [i9Z|
/Au7 X'}
tQIz
· 優化器的職責是從多行種執行路徑中選擇一種最優的執行路徑； DMd &9EsRG
· 有兩種優化模式：RBO（基於規則），CBO（基於成本）； vO B XAF
· RBO根椐一系列規則來確定執行計劃，不考慮表的大小，欄位的集勢等統計資料，主要用於早期的版本或者新版本的遞迴SQL； \ .{ZgL5"
· CBO會考查表或索引的統計，然後比較不同執行計劃的IO成本，CPU成本，臨時表空間的需求，得出一個綜合成本最小的執行計劃； !+ ? ?3-q
· CBO考查的統計包括：表或索引的大小，表或索引的行數，表或索引的資料塊數，錶行的長度，索引欄位的集勢等； 8 \e8$ y3
· 預設情況下，字典裡並不包含表或索引的統計，這些資料是在分析表，索引，方案或整個資料庫的時候寫進字典裡的。 ^1z) \p1
· 分析可以使用兩個方式：COMPUTE STATISTICS（整體分析）， ESTIMATE STATISTICS（樣本分析，可以用SAMPLE子句指定樣本行數或者比例）； H @ .1cO
· 可以分析欄位的柱狀圖以指示優化器欄位資料的離散分佈狀況，ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE integer_value(1-254)，預設分析選項下只儲存欄位的最大最小值，優化器假設欄位值是均勻分佈的，在某些不均衡的情況下，優估器可能產生效率極低的執行計劃，因此分析柱狀圖顯得相當重要； ]Kp -2KW
· 可以用包程式來分析整個方案或者整個資料庫，DBMS_UTILITY， DBMS_STATS； & %` 0&y
· 在ORACLE9中推薦使用DBMS_STATS，它有以下新特性： # l-/ !j
v 可以在分析前備份現有的統計，用於當更新統計後CBO效能反而下降的情形下恢復以前的統計； G Q= Pkko
v 樣本分析時隨機取資料塊，而不只是資料行； >G xh=** F
v 可以在並行模式下收集統計； :V:siIDn
v DBMS_STATS.GATHER_SCHEMA_STATS可以定期自動收集高變更的表的統計，也可以用來自動收集柱狀圖的統計並自動決定切片數以及哪些欄位需要柱狀圖統計； | NXe{q7{
v DBMS_STATS.GATHER_SYSTEM_STATS可以用來收集系統的CPU和IO負載統計，為CBO決策提供參考，避免系統產生CPU或IO瓶頸； vB1nj
v 可以用於將生產環境的統計轉移到開發環境，這一點對於從開發環境調優生產環境相當重要。 ~X%W2 N 2
· 統計結果存放在下面一些字典裡面：DBA_TABLES， DBA_INDEXES， DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。 $o> 6 Io|D
設定優化器模式 l xXIu8
· 可以在下面三種級別配置優化器模式：例項級，會話級，語句級，優先順序從低到高； 3 pI )
· 設定優化器行為版本差異的9i新引數：OPTIMIZER_FEATURES_ENABLE； ] { PJ
· OPTIMIZER_MODE初始化引數確定例項中所有會話預設的優化模式，可選值有RULE， CHOOSE（預設值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（與CHOOSE似乎沒有區別？）； u^]Z { K_B
· 會話級的優化模式設定用ALTER SESSION SET OPTIMIZER_MODE = mode，可選項同上; C&-]RffA
· 在SQL語句中內嵌提示/*+ mode */ 可用來指示當前語句的優化模式，mode選項可用上面提到的除CHOOSE外的其它四種，另外還有四十多種提示可用； !D Nk!]|
· 在RULE模式下，如果SQL涉及到下面的特性時還是會用CBO：分割槽表和分割槽索引，索引組織表，反向索引，基於函式的索引，點陣圖索引，查詢重寫，物化檢視； uT 2 w 2A;
· 在非RULE的其它四種模式下，如果涉及到的所有的表或者索引的統計都不存在，使用RBO模式，其它情況下使用CBO； '" f ZGz ?
· FIRST_ROWS， FIRST_ROWS_n 優化響應時間， ALL_ROWS優化吞吐量； ` w/b];e1)
+n&9 ZC H
E Ig:@o&Jj
現陸續將這些筆記貼出來，歡迎大家討論和指正。 bQu@ .'O!k
%Rm `YH?
第二章 調整的資訊來源 2e({ %P@2?
G?:5L 0g
_s Czee&uQ
Alert Log 檔案中記錄的與效能有關的資訊： % 6:" tu A
1. ORA-01652，不能擴充套件臨時段； I5rAL\y-G
2. ORA-01653，不能擴充套件表段； )hfI,9 I~
3. ORA-01650，不能擴充套件回滾段；
4. ORA-01631，到達了表的最大的Extents； Q nIF{T S=
5. 檢查點未完成（Checkpoint not complete）； LJ*W&y(2>Q
6. 執行緒推進日誌序列（Thread n Advanced to Log Sequence n）； 0? 'v| 5}
7. ORA-01555，快照過舊。 9 ?+?V }o
R!RgQwEak
後臺跟蹤檔案 $HFimU,V=0
自動生成 Uc ]s W cR
相關引數：BACKUPGROUND_DUMP_DEST *|3G"B{ w6
K,f - w2!
事件跟蹤檔案 7 DW HADr
設定後生成 ^wb$wtL ('
相關引數：EVENT， BACKUPGROUND_DUMP_DEST， USER_DUMP_DEST U oG+d u[
*Z V=4[#bT
使用者跟蹤檔案 v |;} }ol
相關引數：SQL_TRACE，USER_DUMP_DEST， MAX_DUMP_FILE_SIZE rFG_ C C2
啟用使用者跟蹤的三種方法： NrWgaPO)i
1. 例項層次的跟蹤：配置引數SQL_TRACE = TRUE | FALSE，然後重啟例項； )wfqGkr=m!
2. 使用者層次的自行實置：Alter session set sql_trace = true | false； !9vq"J~hz"
3. 使用者層次的DBA設定：Exec dbms_system.set_sql_trace_in_session(SID, SERIAL#, TRUE | FALSE) + `l )W`zX
)^LiA L h
V$檢視和DBA_檢視 %v, a3^Qu
區別： #B!
1. V$檢視通常是單數，DBA檢視通常是複數，例如V$DATAFILE與DBA_DATA_FILES； `xF^ 9;5mi
2. 當資料庫處於Nomout或者Mount時，許多V$檢視已經是可用的，而DBA檢視必須在資料庫處於Open時才可用； ND\ M
3. V$檢視查詢出來的資料多小寫，DBA檢視查詢出的資料通常大寫，所以在寫WHERE條件時需特別小心； <7Ry"z6g;
4. V$檢視中包含的是自例項啟動以來的動態資料，在資料庫關閉後會消失，查詢V$檢視時必須關注時效性，DBA檢視中包含的是靜態資料； ^!* nhs%
5. V$檢視的基表是X$表，X$表是存在於記憶體中的虛表，DBA檢視的基表是資料字典表，如SYS.OBJ$， SYS.FILE$等，這兩種基表都很少有文件。 ,U ?^ u%
$tB `dDj
最常用的V$檢視： AS/z 1M_U
表名 描述 aI_[h v
V$SGASTAT 顯示SGA元件大小的資訊 Hb 3t|
V$EVENT_NAME 顯示當前版本的所有等待事件 =B0AG 9Fz
V$SYSTEM_EVENT 自例項啟動已來的等待事件 1 ' f &
V$SESSION_EVENT 目前連線會話的等待事件 ?10L *PD@
V$SESSION_WAIT 目前連線會話正在發生的等待事件 $g _|U:,
V$STATNAME 顯示當前版本的所有統計名稱 ~(\ . j=x
V$SYSSTAT 自例項啟動以來的統計 lP *p7Y '
V$SESSTAT 目前連線會話的統計 = Oz pI
V$SESSION 目前連線會話的資訊 '0 v ]?mM
V$WAITSTAT 塊競爭的統計 Y RQ?: a{H
j{ YIVX
最常用的DBA檢視（有些欄位的值需要分析表或索引後才會有值）： 2=,d.1E3 d
表名 描述 1 dO B|
DBA_TABLES 表的儲存，統計等 T ?C QgVR
DBA_INDEXES 索引的儲存，統計等 ']N\y6=fn9
INDEX_STATS 索引的深度和鍵值的離散度等 Bf ;<3k)5.
DBA_DATA_FILES 資料檔案的命名，位置，大小 F~ /~_9 RJ
DBA_SEGMENTS 段的相關資訊 9{ 0%M
DBA_HISTOGRAMS 表，索引的柱狀圖定義資訊 #
m~4ik 1 wq
&3Sm Tg %
Oracle 提供的指令碼和包 eN`G2eE
下面提到的指令碼都位於%ORACLE_HOME%\RDBMS\ADMIN目錄下，有些只需執行一次，有些需要每次都執行。 aGAr24] y
· UTLBSTAT.SQL 和 UTLESTAT.SQL 5mZwg (si
這些指令碼的功能絕大部分已被STATSPACK所取代。 uX%$3k
· STATSPACK i~)EU F
Ø 配置STATSPACK：在SQL*Plus下執行spcreate.sql，這個指令碼執行時呼叫另外三個指令碼（spcusr.sql，spctab.sql，spcpkg.sql），執行過程中會詢問perfstat方案的密碼，預設表空間和臨時表空間，可以通過這三個指令碼的執行日誌（spcusr.lis，spctab.lis，spcpkg.lis）檢視配置是否成功。 hZ Wk w{c
Ø perfstat方案的預設表空間需要有足夠的空間來容納目前的物件和將來的快照資料； [.6 b xK
Ø 檢視日誌如果發現有錯誤，執行spdrop.sql來清除已建立的物件，然後重新執行spcreate.sql； 1=e(g#Ajn\
Ø 配置成功後生成快照有兩種方法：手工執行exec statspack.snap; 執行spauto.sql指令碼，建立job來定時生成快照； BB_(!om q[
Ø 生成效能報表：執行spreport.sql指令碼，指令碼執行過程中要求輸入三個引數：開始快照號，結束快照號，報表名；兩次選擇的快照號需要是在例項的一次執行期間的不同點的快照，否則就沒有參考意義； GkI{7GD: z
Ø statspack報表含有各個方面的效能資料，如何理解這些資料貫穿整本書。 D *IeG>%
sCp)o, ;
第三章 SQL 調整 1.SQL效能測量工具 s,l* =<
}*O8 ] lG
;0NJX)GL
TKPROF PO^#G @
全名為Trace Kernel Profile，用來格式化跟蹤檔案，是一個命令列工具，基本的格式如下： bH :C/P
tkprof tracefile outputfile … P" Z1K5>2L
可選引數及說明如下： Vup|*d2r0E
Option Description oq b (w+ <
EXPLAIN 為每個SQL語句產生執行計劃，指定使用者方案和密碼，使用其中的PLAN_TABLE表 @gmo;8? k
TABLE 當EXPLAIN選項生效時可用，指定替代PLAN_TABLE的表 Rm6i [y&
SYS 啟用該選項時不包含遞迴呼叫 M m Ft G-
SORT 指定SQL語句的排序方式 |CQ0{1R1
RECORD 指定檔名，將SQL語句寫入，排除遞迴的SQL U Ls'oT)K;
PRINT 限定只輸出指定數量的分析SQL語句 a Sg K h
INSERT 指定一個檔名，容納相關的插入SQL語句，將分析的SQL及統計插入TKPROF_TABLE BxZ7B k
AGGREGATE 指定多使用者共用的SQL語句統計方式 1s(i \ & B
WAITS 指定是否統計跟蹤檔案中的等待事件 ~dP\0x0AB
(.L?sDQ
SORT選項可分三類，解析選項，執行選項和取資料選項（PRS,EXE,FCH），根椐這三種操作佔用相關資源來排序。 17 0r5
1'8-+ ? r
TKPROF針對SQL的統計也分為三類：解析，執行，取數，具體的統計選項見下表： <=w! :
統計名稱 統計描述 ) r)Zm S5O
Count Parse,Execute,Fetch發生的次數 ( v? rZ v
CPU 佔用CPU秒 v ( S h+p
Elapsed 經過秒 'fk6]& -I
Disk 從磁碟讀取的資料塊數 pK" Z9y&
Query 從SGA中讀取的回滾段塊數 s B ^ejH
Current 從SGA中讀取的一致性資料塊數 3"k n5)x
Rows 執行INSERT，UPDATE，DELETE影響的行數或者FETCH返回的行數 "} H 2dn2n
(NJ .\m
從TKPROF的統計結果觀察，發現下列特徵的SQL可能需要調優： 9RkNRB )8
1. 消耗過度的CPU資源； o=x M a A
2. 花費太長的時間在Parse，Execute，Fetch階斷； ` 9P`f4 x
3. 從磁碟讀資料塊太多而從SGA中讀塊很少； t - fmA?\
4. 存取太多的資料塊（從SGA或者磁碟）而返回的行數很少； z3>4 xn {
TOP SQL J 6 rWe
這個工具從Enterprise Manager Console中啟動，有與TKPROF類似的統計，用於確定需要調整的SQL語句。 Z / 4bxO=m
av:%wJUl,$
EXPLAIN PLAN 8+3 2hg@^F
用EXPLAIN PLAN FOR sql語句產生執行計劃，然後再從PLAN_TABLE中查詢；
執行計劃中各步驟的執行順序遵照如下原則： $~?)E;S
1. 從上到下； bQ I.Qk
2. 從右到左（或者說，從縮排最多的到縮排最少的）； -B- H Z_
3. 索引操作不參與上面的規則，索引從屬於表操作，先於表操作。 ]5M T-q U
y S
執行計劃的詳盡解釋超出本書範圍，需要參考其它資料； u~mpZ"9$ 3
AUTOTRACE W v ,?xm
AUTOTRACE綜合了TKPROF和EXPLAIN PLAN FOR的功能；
配置AUTOTRACE包括以下步驟： ygUX] *m!
1. 保證需要AUTOTRACE功能的使用者下有PLAN_TABLE或者有該表的全域性同義詞和那個基表的存取許可權； gAsjkN t?
2. 建立PLUSTRACE角色，並將該角色許可權賦給相應的使用者，或者相應會話有這個角色對應的許可權； C?6q ]k]r
3. 相關指令碼：%ORACLE_HOME%\rdbms\admin\utlxplan.sql，%ORACLE_HOME%\sqlplus\admin\plustrce.sql +|4olK$[
?t T89m3_E
AUTOTRACE的特點： !wro7ilMB
1. 只能在SQL*Plus的會話中執行； F g2/rC: _
2. 產生執行計劃前會實際執行SQL，這點與EXPLAIN PLAN FOR不同； - , $:^ 4
3. 在發出SQL之前，需執行SET AUTOTRACE ON語句。 a4q02 cV
$! g ~p V
SET AUTOTRACE語句各選項的意義： X hOg >
選項 描述 j ?! / #'
ON 顯示查詢結果，執行計劃，統計 @,p n /[
ON STATISTICS 顯示查詢結果，統計 X(C=O? A
ON EXPLAIN 顯示查詢結果，執行計劃 m WVq > ~
TRACEONLY 顯示執行計劃，統計 U a1Z,~ *
TRACEONLY STATISTICS 顯示統計 [Lf8 * U"
OFF 關掉AUTOTRACE IXR%IggJA
$,1KD3;+]
STATSPACK :Z3]Dk;y
STATSPACK中也包含幾種方法排序的SQL，通常只顯示排序值大於一定閥值的SQL，這些閥值可能改變： ban;HGGNG{
SQL型別 說明 ?a ~# `<
SQL ordered by Gets 根椐邏輯I/O來排序 ->@i w!5xu
SQL ordered by Reads 根椐物理I/O來排序 R ^ 6^ {q
SQL ordered by Executions 根椐執行次數來排序 *Di ;Gf@
SQL (Executions) ordered by Parse Calls 根椐分析次數來排序 DA/ \[w?J
lJU]sZ9~b
第三章 SQL 調整 2. 理解ORACLE優化器 PkDh [i9Z|
/Au7 X'}
tQIz
· 優化器的職責是從多行種執行路徑中選擇一種最優的執行路徑； DMd &9EsRG
· 有兩種優化模式：RBO（基於規則），CBO（基於成本）； vO B XAF
· RBO根椐一系列規則來確定執行計劃，不考慮表的大小，欄位的集勢等統計資料，主要用於早期的版本或者新版本的遞迴SQL； \ .{ZgL5"
· CBO會考查表或索引的統計，然後比較不同執行計劃的IO成本，CPU成本，臨時表空間的需求，得出一個綜合成本最小的執行計劃； !+ ? ?3-q
· CBO考查的統計包括：表或索引的大小，表或索引的行數，表或索引的資料塊數，錶行的長度，索引欄位的集勢等； 8 \e8$ y3
· 預設情況下，字典裡並不包含表或索引的統計，這些資料是在分析表，索引，方案或整個資料庫的時候寫進字典裡的。 ^1z) \p1
· 分析可以使用兩個方式：COMPUTE STATISTICS（整體分析）， ESTIMATE STATISTICS（樣本分析，可以用SAMPLE子句指定樣本行數或者比例）； H @ .1cO
· 可以分析欄位的柱狀圖以指示優化器欄位資料的離散分佈狀況，ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE integer_value(1-254)，預設分析選項下只儲存欄位的最大最小值，優化器假設欄位值是均勻分佈的，在某些不均衡的情況下，優估器可能產生效率極低的執行計劃，因此分析柱狀圖顯得相當重要； ]Kp -2KW
· 可以用包程式來分析整個方案或者整個資料庫，DBMS_UTILITY， DBMS_STATS； & %` 0&y
· 在ORACLE9中推薦使用DBMS_STATS，它有以下新特性： # l-/ !j
v 可以在分析前備份現有的統計，用於當更新統計後CBO效能反而下降的情形下恢復以前的統計； G Q= Pkko
v 樣本分析時隨機取資料塊，而不只是資料行； >G xh=** F
v 可以在並行模式下收集統計； :V:siIDn
v DBMS_STATS.GATHER_SCHEMA_STATS可以定期自動收集高變更的表的統計，也可以用來自動收集柱狀圖的統計並自動決定切片數以及哪些欄位需要柱狀圖統計； | NXe{q7{
v DBMS_STATS.GATHER_SYSTEM_STATS可以用來收集系統的CPU和IO負載統計，為CBO決策提供參考，避免系統產生CPU或IO瓶頸； vB1nj
v 可以用於將生產環境的統計轉移到開發環境，這一點對於從開發環境調優生產環境相當重要。 ~X%W2 N 2
· 統計結果存放在下面一些字典裡面：DBA_TABLES， DBA_INDEXES， DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。 $o> 6 Io|D
設定優化器模式 l xXIu8
· 可以在下面三種級別配置優化器模式：例項級，會話級，語句級，優先順序從低到高； 3 pI )
· 設定優化器行為版本差異的9i新引數：OPTIMIZER_FEATURES_ENABLE； ] { PJ
· OPTIMIZER_MODE初始化引數確定例項中所有會話預設的優化模式，可選值有RULE， CHOOSE（預設值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（與CHOOSE似乎沒有區別？）； u^]Z { K_B
· 會話級的優化模式設定用ALTER SESSION SET OPTIMIZER_MODE = mode，可選項同上; C&-]RffA
· 在SQL語句中內嵌提示/*+ mode */ 可用來指示當前語句的優化模式，mode選項可用上面提到的除CHOOSE外的其它四種，另外還有四十多種提示可用； !D Nk!]|
· 在RULE模式下，如果SQL涉及到下面的特性時還是會用CBO：分割槽表和分割槽索引，索引組織表，反向索引，基於函式的索引，點陣圖索引，查詢重寫，物化檢視； uT 2 w 2A;
· 在非RULE的其它四種模式下，如果涉及到的所有的表或者索引的統計都不存在，使用RBO模式，其它情況下使用CBO； '" f ZGz ?
· FIRST_ROWS， FIRST_ROWS_n 優化響應時間， ALL_ROWS優化吞吐量； ` w/b];e1)
+n&9 ZC H
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/119501/viewspace-609375/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- <轉>oracle效能調整讀書筆記(2)Oracle筆記
- <轉>oracle效能調整讀書筆記(3)Oracle筆記
- <轉>oracle效能調整讀書筆記(4)Oracle筆記
- <轉>oracle效能調整讀書筆記(5)Oracle筆記
- 讀書筆記-高階owi與oracle效能調整-oracle internal筆記Oracle
- 讀書筆記-高階owi與oracle效能調整-io筆記Oracle
- 讀書筆記-高階owi與oracle效能調整-segment筆記Oracle
- 讀書筆記-高階owi與oracle效能調整-transaction筆記Oracle
- oracle高效能sql調整-筆記1 (轉發）OracleSQL筆記
- 讀書筆記-高階owi與oracle效能調整-network筆記Oracle
- 讀書筆記-高階owi與oracle效能調整-share pool筆記Oracle
- 讀書筆記-高階owi與oracle效能調整-cache buffer筆記Oracle
- Oracle效能調整筆記Oracle筆記
- Oracle效能調整－1（轉）Oracle
- 讀書筆記-高階owi與oracle效能調整-latch和lock筆記Oracle
- oracle效能調整筆記[zt]Oracle筆記
- oracle效能調整(1)Oracle
- ORACLE效能調整--1Oracle
- 程式碼整潔之道--讀書筆記(1)筆記
- Oracle效能調整－2（轉）Oracle
- Oracle效能調整－3（轉）Oracle
- 《整數分解》讀書筆記筆記
- oracle 效能調整Oracle
- 筆記： Oracle 11g效能調整（11.2）目錄筆記Oracle
- 《CSS重構：樣式表效能調優》讀書筆記CSS筆記
- 轉載大師的讀書筆記 1筆記
- Oracle效能調整之--DML語句效能調整Oracle
- 效能與RAC 讀書筆記筆記
- FPGA讀書筆記1FPGA筆記
- oracle資料庫的效能調整(轉)Oracle資料庫
- oracle效能調整(2)Oracle
- ORACLE效能調整---2Oracle
- Oracle 效能調整for HWOracle
- (zt)Oracle效能調整Oracle
- oracle效能調整2Oracle
- 讀書筆記：RUP (轉)筆記
- 《高效能JavaScript》讀書筆記JavaScript筆記
- 【讀書筆記】《高效能JavaScript》筆記JavaScript