1.dbms_xplan.display

dbms_xplan由9i中引入,用來顯示explan plan裡的執行計劃資訊,explan
plan的結果儲存在plan_table表中,9i裡plan_table是個物理表,而在10g
plan_table是個全域性臨時表,如果從9i升級到10g裡的應用,可以drop掉
plan_table以顯示更全的執行計劃資訊.

用法:

plan for select count(*)
2 from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name
3 /

已解釋。

* from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 420753894

--------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |   145 |    59   (4)| 00:00:01 |
|   1 | SORT AGGREGATE     |          |     1 |   145 |            |          |
|   2 |   NESTED LOOPS      |          |    12 | 1740 |    59   (4)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| TEST_2   |     3 |   237 |    56   (4)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN | I_TEST_1 |     5 |   330 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T2"."OBJECT_ID"<10)
4 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

Note
-----
- dynamic sampling used for this statement

已選擇21行。

rows:預測返回的行數
cost:某個返回路徑的成本
bytes:預測返回的位元組說
time :預測執行時間

Predicate Information (identified by operation id):
operation id對應的謂詞資訊
(9i裡引入,9i只有explain plan顯示謂詞資訊,10g裡set autot trace也能顯示謂詞資訊)

Note:

顯示是否使用動態取樣,outline和profile等

display的一些option:

BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

explain plan裡應該重點關注的部分:

1.rows 評估的行數很重要,大部分的sql效能問題都是因為rows評估錯誤導致
2.cost 是cbo選擇執行計劃的標準
3.謂詞資訊 ,謂詞資訊可以檢視一些謂詞的轉換,如隱式轉換等,同時結合謂詞
資訊和rows來判斷oracle為什麼會評估錯rows,然後再採取解決方式,同時注意
filter和access謂詞

2.autotrace

explain plan的同胞兄弟,oracle間接呼叫的explan plan(10046就能看出),但是autotrace的一個
好處是能夠顯示sql的物理讀,邏輯讀,遞迴呼叫,排序等資訊,因此autotrace可以作為單條sql語句
效率的基準測試工具

常見選項:

set autotrace explain
set autotrace traceonly
set autotrace on
set autotrace statistics

3.v$sql_plan

v$sql_plan在9i中引入,對sql tuning帶來了巨大的幫助,上面的2個方法不能檢視sql執行時刻的
執行計劃,只能看到評估出來的執行計劃,因為sql在解析時刻和真正執行時刻的執行計劃可能不一樣
(如使用繫結變數),因此真實的執行計劃非常重要

例如:

select child_number,
       '[' || ltrim(to_char(depth, '00')) || ']' "ID",
       lpad(' ', 3 * (depth - 1)) || operation ||
       decode(options, null, '', ' ' || options) "Operation",
       OBJECT_NAME "OBJECT_NAME",
       COST "COST",
       CARDINALITY "CARD",
       BYTES,
       ACCESS_PREDICATES,
       FILTER_PREDICATES
from V$SQL_PLAN
where hash_value = &hash_value;

4. 10046 and sql_trace

10046是一個比較強大的工具,在troubleshooting和sql tuning中的幫助很大,可以檢視繫結變數的資訊
以及wait event的資訊,同時可以檢視執行計劃中的每個步驟消耗的邏輯讀資訊,物理讀資訊,以及消耗的
時間等

select count(*)
from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.12          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        252          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.03       0.13          0        255          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=252 pr=0 pw=0 time=2306 us)
      8   NESTED LOOPS (cr=252 pr=0 pw=0 time=450 us)
      8    TABLE ACCESS FULL TEST_2 (cr=242 pr=0 pw=0 time=112 us)
      8    INDEX RANGE SCAN I_TEST_1 (cr=10 pr=0 pw=0 time=196 us)(object id 53804)

可以看到邏輯讀基本都消耗在test_2的全表掃描上,因此可能可以在這個欄位上建立個索引來減少
邏輯讀情況

建立索引後的情況:

select count(*)
from test_1 t1,test_2 t2 where t2.object_id<10 and t1.object_name=t2.object_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.00       0.00          0         13          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.01          0         13          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=13 pr=0 pw=0 time=682 us)
      8   NESTED LOOPS (cr=13 pr=0 pw=0 time=921 us)
      8    TABLE ACCESS BY INDEX ROWID TEST_2 (cr=3 pr=0 pw=0 time=276 us)
      8     INDEX RANGE SCAN I_TEST_2 (cr=2 pr=0 pw=0 time=146 us)(object id 53807)
      8    INDEX RANGE SCAN I_TEST_1 (cr=10 pr=0 pw=0 time=312 us)(object id 53804)

ps:sql_trace會產生一個新的解析環境(sql_trace是optimizer引數的一個選項),因此可能產生一個
新的version_count,所以經常看到在bind_peeking發生的情況下,走sql_trace執行很快,而不開啟sql_trace
的時候又執行的很慢

5.dbms_xplan.display_cursor

10g r2後oracle新提供的方法,實際上是對v$sql_plan以及相關檢視的封裝,可以透過10046就能
發現,display_cursor顯示的也是真實的執行計劃,oracle推出這個方法後,v$sql_plan和10046
將慢慢退出舞臺(透過gather_plan_statistics 的hint)

display_cursor語法:

select * from table(dbms_xplan.display_cursor('&sql_id','&child_number','options'));

display_cursor的一些比較強大的options:

a. 'All'
b. 'advanced'
c. 'peeked_binds'
d. 'outline'
e. 'allstats last'

一般比較常用的是'advanced','peeked_binds','allstats last'

更多介紹參考:

gather_plan_statistics和dbms_xplan.display_cursor連用的例子:

* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 766bq2cgr9byq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test_1 t1,test_2 t2 where
t2.object_id<10 and t1.object_name=t2.object_name

Plan hash value: 2539381227

--------------------------------------------------------------------------------
--------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
A-Time | Buffers |

--------------------------------------------------------------------------------
--------------------

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:
00:00.01 | 13 |

| 2 | NESTED LOOPS | | 1 | 12 | 8 |00:
00:00.01 | 13 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_2 | 1 | 8 | 8 |00:
00:00.01 | 3 |

|* 4 | INDEX RANGE SCAN | I_TEST_2 | 1 | 8 | 8 |00:
00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | I_TEST_1 | 8 | 2 | 8 |00:
00:00.01 | 10 |

--------------------------------------------------------------------------------
--------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T2"."OBJECT_ID"<10)
5 - access("T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

已選擇23行。

重點介紹:

e-rows:表示預測返回的行數,explain plan裡的資料
a-rows:實際返回的行數
a-times:表示實際消耗的時間
buffers:表示邏輯讀情況
starts表示迴圈次數,一般情況下都是1,但是在nest loops和filter的情況下可能大於1,表示執行次數,
一般是驅動表的a-rows.

6.display_awr

在10g以前的版本中,診斷歷史發生的效能問題比較難,只能藉助statspack或者自己收集歷史資訊,oracle
10g裡在這方面有了極大的增強,包括awr,ash,以及dba_hist相關的檢視,為dba做診斷提供了很大的幫助

display_awr可以顯示awr裡sql的執行資訊,和display_cursor類似,只是語法有些不同

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
DB_ID                          NUMBER(38)              IN     DEFAULT
FORMAT                         VARCHAR2                IN     DEFAULT

a.sql_id可以從awr報告或者ash報告裡得到
b.sql_id可以從dba_hist_sqltext裡查詢sql語句得到
c.可以透過dba_hist_sqlstat檢視sql的消耗情況

例如:

sql_id,sql_text from dba_hist_sqltext where sql_text
2 like 'select count(*) from t%';

SQL_ID SQL_TEXT
-------------------------------------------------------------------------------
a2gxctfkz4z5h select count(*) from test_peek where name=:a and id=:b

* from table(dbms_xplan.display_awr('a2gxctfkz4z5h',null,null,'peeked_binds'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a2gxctfkz4z5h
--------------------
select count(*) from test_peek where name=:a and id=:b

Plan hash value: 2988370418

--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |    41 (100)|          |
|   1 | SORT AGGREGATE    |           |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| TEST_PEEK | 25282 | 98K| 41 (13)| 00:00:01 |
--------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :A (VARCHAR2(30), CSID=852): 'a'
2 - :B (NUMBER): 1

已選擇20行。

檢視sql執行計劃

相關文章