oracle dbms_xplan獲取執行計劃
dbms_xplan包
display:返回儲存在plan_table中的執行計劃
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
table_name :預設為plan_table
statement_id:預設null將顯示最近插入plan_table中的執行計劃，指定sql語句的名字( plan_table.STATEMENT_ID ),explain plan時此引數可選
format:提供那些輸出，基本的
basic:顯示最少的資訊（操作+操作物件）
typical:顯示大部分（除去別名，outline和欄位投影外所有資訊），defualt typical
serial:也typical一樣，不顯示並行操作
all:除去outline外都顯示
advanced:都顯示
修飾符+，-，bytes,cost,alias(查詢塊和別名),note(note部分顯示)，outline顯示,parallel（是否顯示並行資訊）
partition(控制分割槽),peeked_binds(窺視bind,explain plan for不會 窺視，而plan_table 一般是透過explain for獲取，也可以insert 所以這個修飾符沒對於display沒什麼意義)，predicate(謂詞顯示 access,filter部分),projection(投影資訊),rows,remote(遠端執行sql語句顯示)
filter_preds：基於plan_table的一個where條件,預設null 表示輸出最近的plan_table,10gR2 顯示
基本使用
SQL> select count(*) from t1;
COUNT(*)
----------
3000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
需要explain plan 儲存plan_table(也可以建立成別的名字)
SQL> explain plan for select count(*) from t1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS FULL| T1 | 3000 | 30000 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- SQL plan baseline "SYS_SQL_PLAN_b98b6a04616acf47" used for this statement
SQL> explain plan set statement_id='xh_test' for select * from t1;
Explained.
SQL> select * from table(dbms_xplan.display(null,'xh_test'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
SQL> select id,operation ,object_name from plan_table where statement_id='xh_test';
ID OPERATION OBJECT_NAME
---------- ------------------------------ ------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS T1
SQL> explain plan for select * from t1;
Explained.
SQL> select * from table(dbms_xplan.display(null,null,'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T1 | 只顯示最基本的資訊
----------------------------------
8 rows selected.
SQL> select * from table(dbms_xplan.display(null,null,'TYPICAl'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 | 平常看的資訊cost(%cpu) 6(2)表示cpu佔了cost的百分比
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL> select * from table(dbms_xplan.display(null,null,'all'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - SEL$1 /
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "COUNTRY"[VARCHAR2,16], "T1"."CONTINENT"[VARCHAR2,9], ~~~~~~~~~~~~~~~~~~~~顯示了 欄位（可以看到欄位定義）
"T1"."PLANET"[CHARACTER,5], "T1"."ONE"[NUMBER,22]
19 rows selected.
SQL> select * from table(dbms_xplan.display(null,null,'advanced')); ~~~顯示了所有資訊
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - SEL$1 /
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA ~~~顯示了outline資訊，實際就是一堆hints，並不是表示使用了outline，只是顯示如果做成outline的話 包含哪些hints
FULL(@"SEL$1" ") 可以理解為 這些hint可以固定這個plan（使用outline的話note中會顯示use outline xx）
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_××DED_HINTS
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "COUNTRY"[VARCHAR2,16], "T1"."CONTINENT"[VARCHAR2,9],
"T1"."PLANET"[CHARACTER,5], "T1"."ONE"[NUMBER,22]
33 rows selected.
組合一些修飾符使用
SQL> select * from table(dbms_xplan.display(null,null,'typical -rows'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
------------------------------------------------------------------
| Id | Operation | Name | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 111K| 6 (0)| 00:00:01 |
------------------------------------------------------------------
8 rows selected.
SQL> select * from table(dbms_xplan.display(null,null,'typical +outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" ")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.6')
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
IGNORE_OPTIM_××DED_HINTS
END_OUTLINE_DATA
*/
22 rows selected.
filter_preds引數使用
SQL> explain plan for select count(*) from t1;剛才plan_table中已經儲存了一個plan，現在再生成一個
Explained.
SQL> select * from table(dbms_xplan.display);實際就是查詢plan_table
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS FULL| T1 | 3000 | 30000 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- SQL plan baseline "SYS_SQL_PLAN_b98b6a04616acf47" used for this statement
13 rows selected.
SQL> select count(*) from plan_table;
COUNT(*)
----------
5
SQL> select distinct plan_id from plan_table;
PLAN_ID
----------
233
234
SQL> select * from table(dbms_xplan.display(null,null,null,'plan_id=233'));實際就是查詢plan_table，現在加了一個where plan_id=233
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3000 | 111K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 3000 | 111K| 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
display_cursor：從libary cache中獲取執行計劃,(10g才可用)
sql_id;返回sql語句的父cursor，預設null,返回當前session最後一條sql語句執行計劃
cursor_chilid_no：子游標number，預設為null,表示父游下所有子游標都會返回
format:包含display中的所有（加修飾符）,另外多了一個執行時統計資訊（parameter statistics_level=all,or use hint gahter_plan_statistics），預設typical
使用display_cursor需要v$session,v$sql,v$sql_plan,v$sql_plan_statistics_all 的select 許可權，role=select_catalog_role 或擁有select any dictionary系統許可權就可以了
format針對執行時統計資訊多的 修飾符
allstats=iostats+memstats
iostats=i/o資訊顯示
last=預設顯示所有執行計算過的統計，要是指定了last只顯示最後一次執行的統計資訊
memstats:pga資訊
runstats_last=iostats last 10gr1
runstats_tot=iostats 10gr1
執行時統
starts:操作執行次數,a-rows:操作返回真實記錄數，a-time:操作執行真實時間（HH：MM：SS.FF）
i/o統計:
buffers:邏輯讀數量
reads:物理讀數量
writes:物理寫數量
從這裡有些有用的資訊,a-rows,與buffers，a-rows/buffers表示返回每行的邏輯讀次數
a-rows/buffers<5個logical reads表示訪問路徑不錯
a-rows/buffers between 10 and 15 logical reads，表示訪問路徑可以接受
a-rows/buffers>15or20 logical reads，表示路徑不好，可以最佳化
另外a-rows與e-rows可以簡單的識別錯誤的評估（統計資訊）e-rows評估基數（card）,a-rows 真實基數（card）,但有時候要考慮相關型別操作（執行計劃用一個child控制其他child，會次執行，而非相關型只執行一次）比如nl
SQL> select /*+gather_plan_statistics*/count(*) from t1;
COUNT(*)
----------
1
SQL> select sql_id,child_number from v$sql where sql_text='select /*+gather_plan_statistics*/count(*) from t1';
SQL_ID CHILD_NUMBER
------------- ------------
87da3j7f1yd4a 0
SQL> select * from table(dbms_xplan.display_cursor('87da3j7f1yd4a',0,'allstats last'));加 這個是最後一次執行的統計資訊
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 87da3j7f1yd4a, child number 0
-------------------------------------
select /*+gather_plan_statistics*/count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------
13 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('87da3j7f1yd4a',0,'allstats')); 這個是輸出總的 這個sql執行幾次（且共享了這個子游標），裡面資訊 就是這幾次的
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 87da3j7f1yd4a, child number 0
-------------------------------------
select /*+gather_plan_statistics*/count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 2 | 1 | 2 |00:00:00.01 | 14 | 可以看出 這個sql執行了2次
| 2 | TABLE ACCESS FULL| T1 | 2 | 1 | 2 |00:00:00.01 | 14 |
-------------------------------------------------------------------------------------
13 rows selected.
SQL> select /*+gather_plan_statistics*/* from t1 order by 1;
A
----------
1
SQL> select sql_id,child_number from v$sql where sql_text='select /*+gather_plan_statistics*/* from t1 order by 1';
SQL_ID CHILD_NUMBER
------------- ------------
1x49dbnz86s5f 0
SQL> select * from table(dbms_xplan.display_cursor('1x49dbnz86s5f',0,'memstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1x49dbnz86s5f, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from t1 order by 1
Plan hash value: 2148421099
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.01 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | | | |
------------------------------------------------------------------------------------------------------
13 rows selected.
SQL>
可以看到iostats,memesats都需要執行時統計資訊
SQL> select * from table(dbms_xplan.display_cursor('4hrkxzxp0uj5q',0,'iostats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hrkxzxp0uj5q, child number 0
-------------------------------------
select * from t1 order by 1
Plan hash value: 2148421099
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 1 | SORT ORDER BY | | 1 |
| 2 | TABLE ACCESS FULL| T1 | 1 |
--------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system l
SQL> select * from table(dbms_xplan.display_cursor('4hrkxzxp0uj5q',0,'memstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hrkxzxp0uj5q, child number 0
-------------------------------------
select * from t1 order by 1
Plan hash value: 2148421099
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | | | |
-----------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
19 rows selected.
由於explain plan for 不會peeking（按5%算選擇率）,而display_cursor從library cache中提取，所以 peeked_binds這個修飾符 有些用處，我們可以看到query opitimzer peeking的值是多少（11g會多次peeking）
SQL> variable a number
SQL> execute :a:=1
PL/SQL procedure successfully completed.
SQL> select * from t1 where a=:a;
A
----------
1
SQL> select sql_id,child_number from v$sql where sql_text='select * from t1 where a=:a';
SQL_ID CHILD_NUMBER
------------- ------------
b980nwgpprj6w 0
SQL> select * from table(dbms_xplan.display_cursor('b980nwgpprj6w',0,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b980nwgpprj6w, child number 0
-------------------------------------
select * from t1 where a=:a
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :A (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=:A)
display_awr：可以從awr中提取資訊執行計劃
sql_id:父遊標id
plan_hash_value:執行計劃hash value，預設null（表示sql_id下所有執行計劃都會輸出）
db_id:返回執行sql語句所在db,預設null（返回當恰db）
format:與display的全兼。預設typical
與display_cursor區別display_cursor來自shared pool libirary cache，而display_awr來自awr特性，最後儲存在 dba_hist類的view裡（ASH特性），所以flush shared pool後
display cursor無法獲得執行計劃了，而display_awr還可以（存在固定view中）
SQL> @ F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql生成awr報告
6aq34nj2zb2n7 select col#, grantee#, privilege#, max(mod(nvl(option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
用 SQL_ID 6aq34nj2zb2n7
SQL> select * from table(dbms_xplan.display_awr('6aq34nj2zb2n7'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6aq34nj2zb2n7
--------------------
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
Plan hash value: 2874733959
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| OBJAUTH$ |
| 3 | INDEX RANGE SCAN | I_OBJAUTH1 |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
已選擇21行。
SQL> alter system flush shared_pool;～～～重新整理shared pool也可以
系統已更改。
SQL> select * from table(dbms_xplan.display_awr('6aq34nj2zb2n7'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 6aq34nj2zb2n7
--------------------
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
Plan hash value: 2874733959
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS BY INDEX ROWID| OBJAUTH$ |
| 3 | INDEX RANGE SCAN | I_OBJAUTH1 |
---------------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
已選擇21行。
9i 獲取plan，從library cache中,9i中沒有display_cursor，而用explain plan for 這種方式 不太準確尤其有bind的時候，此時用下面的方法
[oracle@smxdmk1 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@smxdmk1 admin]$ sqlplus '/ as sysdba'
SQL> @utlxplan.sql
Table created.
SQL> create table t (a int);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
1
SQL> select hash_value,child_number,address from v$sql where sql_text='select count(*) from t';
HASH_VALUE CHILD_NUMBER ADDRESS
---------- ------------ ----------------
2816538551 0 00000001311BC490
SQL> INSERT INTO plan_table ( operation, options,
2 object_node, object_owner, object_name, optimizer,
3 search_columns, id, parent_id, position, cost,
4 cardinality, bytes, other_tag, partition_start,
5 partition_stop, partition_id, other, distribution,
6 cpu_cost, io_cost, temp_space, access_predicates,
7 filter_predicates)
8 SELECT
9 operation, options, object_node, object_owner, object_name,
10 optimizer, search_columns, id, parent_id, position, cost,
11 cardinality, bytes, other_tag, partition_start, partition_stop,
12 partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13 access_predicates, filter_predicates
14 FROM v$sql_plan
15 WHERE address = '00000001311BC490'
16 AND hash_value = 2816538551
17 AND child_number = 0;
3 rows created.
SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan
SQL> delete plan_table;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> INSERT INTO plan_table ( timestamp,operation, options,
2 object_node, object_owner, object_name, optimizer,
3 search_columns, id, parent_id, position, cost,
4 cardinality, bytes, other_tag, partition_start,
5 partition_stop, partition_id, other, distribution,
6 cpu_cost, io_cost, temp_space, access_predicates,
7 filter_predicates)
8 SELECT
9 to_timestamp(sysdate),operation, options, object_node, object_owner, object_name,
10 optimizer, search_columns, id, parent_id, position, cost,
11 cardinality, bytes, other_tag, partition_start, partition_stop,
12 partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13 access_predicates, filter_predicates
14 FROM v$sql_plan
15 WHERE address = '00000001311BC490'
AND hash_value = 2816538551
AND child_number = 0;
16 17
3 rows created.
SQL> SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display(null,'xh'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch plan for statement_id 'xh'SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> alter session set events '10046 trace name context forever ,level 12';
Session altered.
SQL> select * from table(dbms_xplan.display(null,'xh'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch plan for statement_id 'xh'
SQL> alter session set events '10046 trace name context off';
Session altered.
PARSING IN CURSOR #2 len=2152 dep=1 uid=0 ct=3 lid=0 tim=1240937193104553 hv=1438696113 ad='33f697b0'
SELECT /* EXEC_FROM_DBMS_XPLAN */ id, position, level , operation, options, object_name , cardinality, bytes, temp_space, cost, io_cost, cpu_cost ,decode(partition_sta
rt, 'ROW LOCATION',
'ROWID', decode(partition_start, 'KEY', 'KEY',
decode(partition_start, 'KEY(INLIST)', 'KEY(I)',
decode(substr(partition_start, 1, 6),
'NUMBER', substr(substr(partition_start, 8, 10), 1,
length(substr(partition_start, 8, 10))-1),
decode(partition_start,null,' ',partition_start))))),
decode(partition_stop, 'ROW LOCATION', 'ROW L',
decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
'NUMBER', substr(substr(partition_stop, 8, 10), 1,
length(substr(partition_stop, 8, 10))-1),
decode(partition_stop,null,' ',partition_stop))))), object_node, other_tag, decode(distribution, null,' ',
decode(distribution, 'PARTITION (ROWID)', 'PART (RID)',
decode(distribution, 'PARTITION (KEY)', 'PART (KEY)',
decode(distribution, 'ROUND-ROBIN', 'RND-ROBIN',
decode(distribution, 'BROADCAST', 'BROADCAST',
substr(distribution, 1, 12)))))) , access_predicates, filter_predicates , null from PLAN_TABLE start with id = 0
and timestamp >= (select max(timestamp)
from PLAN_TABLE where id=0 and statement_id = 'xh' and nvl(statement_id, ' ') not like 'SYS_LE%')
and nvl(statement_id, ' ') not like 'SYS_LE%' and statement_id = 'xh' connect by (prior id = parent_id
and prior nvl(statement_id, ' ') =
nvl(statement_id, ' ')
and prior timestamp <= timestamp)
or (prior nvl(object_name, ' ') like 'SYS_LE%'
and prior nvl(object_name, ' ') =
nvl(statement_id, ' ')
and id = 0 and prior timestamp <= timestamp)
order siblings by id
經過trace select * from table(dbms_xplan.display) 可以發現關鍵一步 (select max(timestamp)表示預設從plan_table取時間最近的執行計劃資訊
SQL> explain plan for select * from t;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Note: rule based optimization
9 rows selected.
SQL> select timestamp from plan_table;
TIMESTAMP
---------
08-APR-10
08-APR-10
SQL> select to_char(timestamp,'YYYY-MM-DD HH24:mi:ss') from plan_table;
TO_CHAR(TIMESTAMP,'
-------------------
2010-04-08 17:44:31
2010-04-08 17:44:31
SQL> SQL> INSERT INTO plan_table ( timestamp,operation, options,
2 object_node, object_owner, object_name, optimizer,
3 search_columns, id, parent_id, position, cost,
4 cardinality, bytes, other_tag, partition_start,
partition_stop, partition_id, other, distribution,
5 6 cpu_cost, io_cost, temp_space, access_predicates,
7 filter_predicates)
8 SELECT
9 to_timestamp(sysdate),operation, options, object_node, object_owner, object_name,
10 optimizer, search_columns, id, parent_id, position, cost,
11 cardinality, bytes, other_tag, partition_start, partition_stop,
12 partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13 access_predicates, filter_predicates
14 FROM v$sql_plan
15 WHERE address = '00000001311BC490'
16 AND hash_value = 2816538551
17 AND child_number = 0;
3 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Note: rule based optimization
10 rows selected.
SQL> select * from t;
A
----------
1
SQL> select hash_value,child_number,address from v$sql where sql_text='select * from t';
HASH_VALUE CHILD_NUMBER ADDRESS
---------- ------------ ----------------
520543201 0 0000000133F1E708
INSERT INTO plan_table ( timestamp,statement_id,operation, options,
object_node, object_owner, object_name, optimizer,
search_columns, id, parent_id, position, cost,
cardinality, bytes, other_tag, partition_start,
partition_stop, partition_id, other, distribution,
cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates)
SELECT
to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
optimizer, search_columns, id, parent_id, position, cost,
cardinality, bytes, other_tag, partition_start, partition_stop,
partition_id, other, distribution, cpu_cost, io_cost, temp_space,
access_predicates, filter_predicates
FROM v$sql_plan
WHERE address = '0000000133F1E708'
AND hash_value = 520543201
AND child_number = 0;
SQL>
SQL> INSERT INTO plan_table ( timestamp,statement_id,operation, options,
2 object_node, object_owner, object_name, optimizer,
3 search_columns, id, parent_id, position, cost,
4 cardinality, bytes, other_tag, partition_start,
5 partition_stop, partition_id, other, distribution,
6 cpu_cost, io_cost, temp_space, access_predicates,
7 filter_predicates)
8 SELECT
9 to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
10 optimizer, search_columns, id, parent_id, position, cost,
11 cardinality, bytes, other_tag, partition_start, partition_stop,
12 partition_id, other, distribution, cpu_cost, io_cost, temp_space,
13 access_predicates, filter_predicates
14 FROM v$sql_plan
15 WHERE address = '0000000133F1E708'
16 AND hash_value = 520543201
17 AND child_number = 0;
2 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> select * from table(dbms_xplan.display(null,'xh'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL | T | | | |
--------------------------------------------------------------------
Note: rule based optimization
9 rows selected.
SQL>
10g ，11g 要從libary cache中 將執行計劃 insert到plan_table ，要加上plan_id
INSERT INTO plan_table ( plan_id,statement_id,operation, options,
object_node, object_owner, object_name, optimizer,
search_columns, id, parent_id, position, cost,
cardinality, bytes, other_tag, partition_start,
partition_stop, partition_id, other, distribution,
cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates)
SELECT
to_timestamp(sysdate),'xh',operation, options, object_node, object_owner, object_name,
optimizer, search_columns, id, parent_id, position, cost,
cardinality, bytes, other_tag, partition_start, partition_stop,
partition_id, other, distribution, cpu_cost, io_cost, temp_space,
access_predicates, filter_predicates
FROM v$sql_plan
WHERE address = '0000000133F1E708'
AND hash_value = 520543201
AND child_number = 0;
因為 trace select * from table(dbms_xplan.display)
SQL> SELECT /*+ opt_param('parallel_execution_enabled','false') */
2 /* EXEC_FROM_DBMS_XPLAN */ id, position, depth, operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost , time, partition_start, partition_stop, object_node, other_tag, distribution, projection, access_predicates, filter_predicates , other, qblock_name, object_alias, nvl(other_xml, remarks) other_xml, null sql_profile, null sql_plan_baseline, null, null, null, null, null, null, null,
3 null, null, null, null, null,
4 null, null, null, null from PLAN_TABLE where plan_id = (select max(plan_id)
5 from PLAN_TABLE where id=0 and statement_id = 'xh')
6 order by id
7 /
可以發現 這個(select max(plan_id)，這也就是為什麼預設是顯示最近的執行計劃從plan_table中 max(plan_id)最近的，
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2137059/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- Oracle獲取執行計劃的方法Oracle
- Oracle 獲取SQL執行計劃方法OracleSQL
- 執行計劃-1：獲取執行計劃
- DBMS_XPLAN檢視執行計劃
- 獲取SQL執行計劃SQL
- Oracle 獲取執行計劃的幾種方法Oracle
- Oracle10g如何獲取執行計劃Oracle
- 【sql調優之執行計劃】獲取執行計劃SQL
- 用DBMS_XPLAN來展示執行計劃
- Oracle中使用DBMS_XPLAN處理執行計劃詳解Oracle
- 獲取執行計劃之Autotrace
- 獲取執行計劃的方法
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- 獲取SQL執行計劃的方式：SQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- 如何獲取真實的執行計劃
- 獲取執行計劃的6種方法
- oracle 9i 獲取sql執行計劃(書寫長的sql)OracleSQL
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- ORACLE執行計劃Oracle
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- Oracle 執行計劃 分析和動態取樣Oracle
- 會話的跟蹤以及執行計劃的獲取會話
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- 介紹幾種獲取SQL執行計劃的方法（上）SQL
- 設定CURRENT_SCHEMA後獲取執行計劃報錯
- SQL效能的度量 - 透過explain和dbms_xplan包分析執行計劃SQLAI
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- Oracle執行計劃詳解Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- Oracle 索引和執行計劃Oracle索引
- Oracle閱讀執行計劃Oracle