oracle執行計劃相關

執行計劃：
首先在分析的使用者下執行rdbms\admin\utlxplan.sql
用sys使用者登入：sqlplus\admin\plustrace.sql
grant sqlplus to user_name;

1. 找出耗費資源比較多的語句

SELECT ADDRESS,
substr(SQL_TEXT,1,20) Text,
buffer_gets,
executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions>0
AND buffer_gets > 100000
ORDER BY 5;

2. 如何分析執行計劃：
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT ptimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]

TABLE ACCESS FULL large_table:在large_table上做全表掃描
[:Q65001] 表明該部分查詢是以並行方式執行的。
[ANALYZED] 表明操作中引用的物件被分析過了，在資料字典中有該物件的統計資訊可以供CBO使用。

3. 各個表之間是如何關聯的
在執行計劃中，需要知道哪個操作是先執行的，哪個操作是後執行的，這對於判斷哪個表為驅動表有用處。判斷之前，如果對錶的訪問是通過rowid，且該rowid的值是從索引掃描中得來得，則將該索引掃描先從執行計劃中暫時去掉。然後在執行計劃剩下的部分中，判斷執行順序的指導原則就是：最右、最上的操作先執行。

4. 在RBO中，以from 子句中從右到左的順序選擇驅動表，即最右邊的表為第一個驅動表但是，在RBO中，也是有一套規則來決定使用哪種連線型別和哪個表作為驅動表，在選擇時肯定會考慮當前索引的情況，還可能會考慮where 中的限制條件，但是肯定是與where中限制條件的位置無關。

5. 在CBO中，如果沒有統計資訊，則在from子句中從左到右的順序選擇驅動表。如果用ordered它會按從左到右的順序選擇驅動表。但是如果對錶或索引進行分析，則優化器會自動根據cost值決定採用哪種連線型別，這與where子句中各個限制條件的位置沒有任何關係，如果想改變優化器選擇的連線型別或驅動表，則要使用hints。

CBO與RBO總結：
在RBO中，以從右到左的順序選擇驅動表，即最右邊的表為第一個驅動表，但是在RBO中也有一套規則來決定使用哪種連線型別和哪個表作為驅動表，在選擇時肯定會考慮到當前索引的情況，還可能會考慮到where中的限制條件，但是肯定是與where中限制條件的位置無關。

在CBO中，如果沒有統計資訊，則以從右到左的順序選擇驅動表，但是如果對錶或索引進行分析，則優化器會自動根據cost值決定採用哪種連線型別，與where子句中各個限制的條件位置沒有任何關係，如果想改變優化器選擇型別或驅動表，剛要使用hints.如果使用ordered它也會按從左到右的順序選擇驅動表。

6. 下面我們來干預執行計劃：使用hints提示
我們可以用hints來實現：
1） 使用優化器的型別
2） 基於代價的優化器的優化目標，是all_rows還是first_rows
3） 表的訪問路徑，是全表掃描還是索引掃描，還是直接利用rowid
4） 表之間的連線型別
5） 表之間的連線順序
6） 語句的並行程式

如何使用hints:
Hints只應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有hints，則該hints不會影響另一個sql語句。

{DELETE | INSERT | SELECT | UPDATE } /*+ hint [text] [hint[text]]..*/
or
{DELETE | INSERT | SELECT | UPDATE} --+ hint [text] [hint[text]]...

註解：
1) DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。
2) “+”號表示該註釋是一個hints，該加號必須立即跟在”/*”的後面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
4) text 是其它說明hint的註釋性文字
如果你沒有正確的指定hints，Oracle將忽略該hints，並且不會給出任何錯誤。

下面是使用hints的例子：
ORDERED提示指出了連線的順序，也為不同的表指定了連線方法
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */
b.application_id, b.set_of_books_id ,
b.personnel_id, p.vendor_id Personnel,
p.segment1 PersonnelNumber, p.vendor_name Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ...

6.1 指示優化器的方法與目標的hints：
ALL_ROWS -- 基於代價的優化器，以吞吐量為目標
FIRST_ROWS(n) -- 基於代價的優化器，以響應時間為目標
CHOOSE -- 根據是否有統計資訊，選擇不同的優化器
RULE -- 使用基於規則的優化器

SELECT /*+ FIRST_ROWS(19) */ employ_id,empname
FROM employees
WHERE department_id = 20;

6.2 指示儲存路徑的hints：
FULL /*+ FULL ( table ) */
指定該表使用全表掃描
ROWID /*+ ROWID ( table ) */
指定對該表使用rowid存取方法，該提示用的較少
INDEX /*+ INDEX ( table [index]) */
使用該表上指定的索引對錶進行索引掃描
INDEX_FFS /*+ INDEX_FFS ( table [index]) */
使用快速全表掃描
NO_INDEX /*+ NO_INDEX ( table [index]) */
不使用該表上指定的索引進行存取，仍然可以使用其它的索引進行索引掃描

SELECT /*+ FULL(e) */ emp_id,empname
FROM employees e;

SELECT /*+ INDEX(A sex_index) use sex_index because there are few
male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = 'm';

6.3 指示連線順序的hints:
ORDERED /*+ ORDERED */
按from 字句中表的順序從左到右的連線
STAR /*+ STAR */
指示優化器使用星型查詢

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

6.4 指示連線型別的hints：
USE_NL /*+ USE_NL ( table [,table, ...] ) */
使用巢狀連線
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */
使用排序- -合併連線
USE_HASH /*+ USE_HASH ( table [,table, ...]) */
使用HASH連線
注意：如果表有alias(別名)，則上面的table指的是表的別名，而不是真實的表名

對物件進行分析： analyze table a compute statistices;
analyze index inx_col12A compute statistics;

2) 當CBO選擇了一個次優化的執行計劃時, 不要同CBO過意不去, 先採取如下措施:
a) 檢查是否在表與索引上又最新的統計資料
b) 對所有的資料進行分析，而不是隻分析一部分資料
c) 檢查是否引用的資料字典表，在oracle 10G之前，預設情況下是不對資料字典表進行分析的。
d) 試試RBO優化器，看語句執行的效率如何，有時RBO能比CBO產生的更好的執行計劃
e) 如果還不行，跟蹤該語句的執行，生成trace資訊，然後用tkprof格式化trace資訊，這樣可以得到全面的供優化的資訊。

5) 如果一個row source 超過10000行資料，則可以被認為大row source
6) 有(+)的表不是driving table，注意：如果有外聯接，而且order hint指定的順序與外聯結決定的順序衝突，則忽略order hint

7. 如何通過跟蹤一個客戶端程式發出的sql的方法來優化SQL

1) 識別要跟蹤的客戶端程式到資料庫的連線(後面都用session代替)，主要找出能唯一識別一個session的sid與serial#.
2) 設定相應的引數，如開啟時間開關(可以知道一個sql執行了多長時間)，存放跟蹤資料的檔案的位置、最大值。
3) 啟動跟蹤功能
4) 讓系統執行一段時間，以便可以收集到跟蹤資料
5) 關閉跟蹤功能
6) 格式化跟蹤資料，得到我們易於理解的跟蹤結果。

1) 識別要跟蹤的客戶端程式到資料庫的資料庫連線
查詢session資訊(在sql*plus中執行)：
set linesize 190
col machine format a30 wrap
col program for a40
col username format a15 wrap
set pagesize 500
select s.sid sid, s.SERIAL# "serial#", s.username, s.machine, s.program,
p.spid ServPID, s.server
from v$session s, v$process p
where p.addr = s.paddr ;

上面的結果中比較有用的列為：
sid, serial# : 這兩個值聯合起來唯一標識一個session
username : 程式連線資料庫的使用者名稱
machine : 連線資料庫的程式所在的機器的機器名，可以hostname得到
program : 連線資料庫的程式名，所有用java jdbc thin的程式的名字都一樣，
servpid : 與程式對應的伺服器端的伺服器程式的程式號，在unix下比較有用
server : 程式連線資料庫的模式：專用模式(dedicaed)、共享模式(shared)。只有在專用模式下的資料庫連線，對其程式跟蹤才有效
logon_time : 程式連線資料庫的登陸時間
根據machine, logon_time 可以方便的識別出一個資料庫連線對應的session，從而得到該sesion的唯一標識sid, serial#, 為對該session進行跟蹤做好準備

2) 設定相應的引數
引數說明：
timed_statistics : 收集跟蹤資訊時，是否將收集時間資訊，如果收集，
則可以知道一個sql的各個執行階段耗費的時間情況
user_dump_dest : 存放跟蹤資料的檔案的位置
max_dump_file_size : 放跟蹤資料的檔案的最大值，防止由於無意的疏忽，
使跟蹤資料的檔案佔用整個硬碟，影響系統的正常執行

設定的方法：
SQL> exec sys.dbms_system.set_bool_param_in_session( -
sid => 8, -
serial# => 3, -
parnam => 'timed_statistics', -
bval => true);
SQL> alter system set user_dump_dest='c:\temp';
-- 注意這個語句會改變整個系統的跟蹤檔案存放的位置，所以我一般不改這個引數，而用系統的預設值，要檢視當前系統的該引數的值，可以用system使用者登陸後：
SQL> show parameter user_dump_dest
SQL> exec sys.dbms_system.set_int_param_in_session( -
sid => 8, -
serial# => 3, -
parnam => 'max_dump_file_size', -
intval => 2147483647)

3) 啟動跟蹤功能
SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 3, true);
注意，只有跟蹤的session再次發出sql語句後，才會產生trc檔案

4) 讓系統執行一段時間，以便可以收集到跟蹤資料

5) 關閉跟蹤功能

SQL> exec sys.dbms_system.set_sql_trace_in_session(8,3,false);

6) 格式化跟蹤資料，得到我們易於理解的跟蹤結果。
對產生的trace檔案進行格式化：
在命令提示符下，執行下面的命令

tkprof dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER
其它使用tkprof的例子：
(a) tkprof tracefile.trc sort_1.prf explain=apps/your_apps_password print=10 sort='(prsqry,exeqry,fchqry,prscu,execu,fchcu)' (b) tkprof tracefile.trc sort_2.prf explain=apps/your_apps_password print=10 sort='(prsela,exeela,fchela)' (c) tkprof tracefile.trc sort_3.prf explain=apps/your_apps_password print=10 sort='(prscnt,execnt,fchcnt)' (d) tkprof tracefile.trc normal.prf explain=apps/your_apps_password

當在開啟跟蹤功能時發生了recursive calls，則tkprof也會產生這些recursive calls的統計資訊，並清楚的在格式化輸出檔案中標名它們為recursive calls。
注意：recursive calls的統計資料是包含在recursive calls上的，並不包含在引起該recursive calls語句的sql語句上面。所以計算一個sql語句耗費的資源時，也要考慮該sql語句引起recursive calls語句花費的資源。通過將sys引數設為no時，我們變可以在格式化的輸出檔案中遮蔽掉這些recursive calls資訊。

如何得到tkprof的幫助資訊：
執行tkprof時，不帶任何引數，就可以得到該工具的幫助資訊。

TKPROF的使用語法：
TKPROF command ::=
>>-- TKPROF traced_file formatted_file ----------------------------------------------&gt
| |
+- SORT = ---------------------------------+
| |
+-- OPTION --+
| |
| +---- , ----+ |
| V | |
|__( OPTION )__|
>-----------------------------------------------------------------------------&gt
| | | | | |
+-- PRINT = integer --+ +-- INSERT = filname3 --+ +-- SYS = ---------+
| |
+- YES -+
| |
+- NO --+
>-----------------------------------------------------------------------------&gt
| |
+---------------------------------------- EXPLAIN = user/password ------+
| |
+---- TABLE = schema.table ----+
>----------------------------------------------------------------------------&gt<
| |
+---- RECORD = filname ----+

各個引數的含義：

' traced_file ' 指定輸入檔案，即oracle產生的trace檔案，該檔案中可以只包含一個session的跟蹤資訊，也可以包含系統中所有session的資訊(此時需要在系統級進行跟蹤)

'formatted_file'指定輸出檔案，即我們想得到的易於理解的格式化檔案，我們利用該檔案對會話執行的sql進行分析。

'EXPLAIN' 利用哪個使用者對trace檔案中的sql進行分析，從而得到該sql語句的執行計劃，這也說明在trace file中並沒有各個sql語句的執行計劃，只是在執行tkprof程式時才將trace file檔案中的sql語句用explian引數指定的使用者連線到資料庫，然後運用EXPLAIN PLAN命令生成sql的執行計劃。這個使用者一般是你的程式中連線資料庫的使用者

'TABLE' 在對sql語句進行分析時，將產生的執行計劃暫時存放到該表中。一般不需要該引數，這樣當表不存在時，tkprof會自動建立相應的表，並在分析完之後，將建立的表自動刪除。如果要指定自定義的表，該表的結構必須與utlxplan.sql檔案中指定的表的結構一樣。我一般不設定這個引數，讓其採用預設的表名，並自動建立、刪除

'SYS' 是否對sys使用者執行的sql語句或被跟蹤session產生的recursive SQL也進行分析，並將分析結果放到輸出檔案中。預設值為YES。我一般設為NO，這樣輸出檔案中只包含我發出的sql語句，而不包含系統產生的sql。

SORT： 按照指定的排序選項(條件)對格式化好的sql語句進行降序排列，然後存放到輸出檔案中。可以將多個排序選項組合起來，如果沒有指定排序選項，則按照使用sql的先後順序。

PRINT只列出指定數量的已排序的sql語句，排序的條件參見SORT引數。如果忽略此引數，tkprof將跟蹤檔案中的所有的sql語句及其相關的分析資料存放到輸出檔案中。Print與sort引數組合在一起，可以實現:找出某一階段耗費cpu最多的前n個sql找出某一階段讀硬碟最多的前n個sql等等。

INSERT： 建立一個sql指令碼檔案，裡面包含create table 與insert語句。利用這個指令碼檔案建立一個表及插入資料後，可以得到跟蹤檔案中所有sql語句(包含recursive SQL)的統計資訊

RECORD： 建立一個包含客戶端程式發出的所有的sql語句的指令碼檔案。注意，並不包含recursive SQL 。想知道它的用處嗎？對了可以窺探別人程式是如何訪問資料庫的，從而對了解程式的訪問流程。此時，最好不用sort引數，這樣就可以按先後發出的順序的到sql.

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12 TABLE ACCESS FULL EMP
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
12 TABLE ACCESS (FULL) OF 'EMP'
DELETE FROM RM$HASH_ELMS
call    count   cpu    elapsed disk query current rows
------- ------ ------ -------- ----- ------ -------- ----
Parse     2     0.00    0.00     0 0 0 0
Execute   29    12.04   12.61    6786 6853 108 19
Fetch    0 0.00 0.00 0 0 0 0
------- ------ ------ -------- ----- ------ -------- ----
total    31 12.04 12.61 6786 6853 108 19
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 9 (DES12A) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT HINT: CHOOSE
16 TABLE ACCESS (FULL) OF 'RM$HASH_ELMS'

下面對每個列進行說明：
call : 表示sql語句執行的每個階段，每個sql語句的活動被分成以下3部分

Parse: 語句被解析的次數，主要是生成執行計劃。包含hard parse與soft parse。需要做的工作：許可權檢查，表、列、引用的表的存在性檢查；比較執行計劃，選出最好的一個等等。

Execute: 真正執行語句的時間，對於DML語句，在此階段中修改資料；對於select語句，這步只是標識出查詢出的行。

Fetch : 只對select語句有效，DML語句的執行並沒有該階段其它列的值都是與sql執行三個階段中所耗費的資源的統計值

COUNT: 一個語句被parsed、executed、fetched的次數

CPU: 執行這個語句的每個階段耗費的cpu時間

ELAPSED: 執行這個語句的每個階段耗費的總時間(包括上面的cpu時間與其它時間，如讀資料)

DISK: 每個階段讀硬碟的次數(有可能從file system buffer中取得資料)對於該引數，我們希望該值越小越好，如果該值很大，該sql需要調整，建立相關索引或看是否正確的使用了索引

QUERY: 每個階段以consistent mode 方式從資料庫buffer中查詢的buffers數。對於查詢，其buffer一般都是以consistent mode模式被讀取

CURRENT: 每個階段以current mode方式從資料庫buffer中查詢的buffers數。Buffers are often對於DML語句，需要的buffer是以current mode模式被讀取的。QUERY + CURRENT 的和是該sql語句總的存取的buffer數目

ROWS:這個sql語句最後處理的行數，不包括子查詢中查詢出來的行數。對於select語句，該值產生於fetch階段；對於dml該值產生於execute階段。

1. 是否發生過量的parsing
2. 快取記憶體的命中率
3. fetch的次數與rows小，可以高效地取得查詢資料
4. 讀資料字典告訴快取的次數

第二步 – 檢查耗費大量資源的語句
upadte ...
where ...
1. 需要訪問多少個資料塊才能找到我們修改的資料
2. 修改了多少個資料塊
3. 修改了幾行資料

第三步 - 檢視是否有過量的parse現象
select ...

1.

格式化sql語句tkprof

【格式化輸出檔案最後部分，即彙總部分】

檢查是否有過量的parsing現象：SQL statements in session(54)與parsing(7)比較，執行54個語句分析7次
命中率：1 - ([execute與fetch中]disk的和 / [execute與fetch中]query的和 + [execute與fetch中]current的和))
在取結果的時候fetch的次數要比rows的次數少最好
parase中的disk：為了對語句進行分析，讀資料字典告訴快取的次數，這個值影響不大，不用關心，它的值不是我們能控制的

【檢查耗費大量資源的語句】
update ... where
在execute 中的query中的資料，表明需要訪問多少個資料塊才能找到我們需要修改的資料
在execute 中的current中的資料，表明我們的修改操作才修改多少個資料塊中的資料
在execute 中的rows中的資料，表明我們只修改了一行資料（其它資料塊的修改應為undo,redo資訊）

【檢查是否有過量的parse現象】
select ...

在 fetch 中的rows中的資料，表明這個查詢只反回一行資料
在 fetch 中的query中的資料，表明我們需要fetch多少次才能得到我們
在 parse 中的count中的資料，表明我們進行了幾次parse（包含hard parse 與soft parse）--這是我們不想看到的，特別是當parse階段操作耗費cpu資源比execute階段耗費的cpu資源
Misses in library cache during parse:2 在這個後面的值（2），如果它為1這個語句有一個hard parse然後跟著一個soft parse（僅僅從庫快取中得到上次分析的資訊，比hard parse 要高效的多）。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-713823/，如需轉載，請註明出處，否則將追究法律責任。