获取已执行语句的执行计划-CSDN博客

曾经都是写脚本到数据字典v$sql_plan里面去取，Christian Antognini却提供了更好的办法。

(1)在Oracle 10g中，使用dbms_xplan.display_cursor传递sql_id和子游标child_number参数即可获得。

JCQ0> SELECT * FROM table(dbms_xplan.display_cursor('1hqjydsjbvmwq',0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq, child number 0
-------------------------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID =
P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'
Plan hash value: 265338492
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 517 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 913K| 26M| 517 (4)| 00:00:07 |
|* 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 17 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 8075K| 494 (3)| 00:00:06 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 494 (3)| 00:00:06 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."PROMO_ID"="P"."PROMO_ID")
3 - filter("PROMO_SUBCATEGORY"='online discount')

(2)在Oracle 9i中，将v$sql_plan中的信息插入plan_table也可以得到格式化好的执行计划。

JCQ0> SELECT address, hash_value, child_number, sql_text
2 FROM v$sql
3 WHERE sql_text LIKE '%online discount%' AND sql_text NOT LIKE '%v$sql%';
ADDRESS HASH_VALUE CHILD_NUMBER SQL_TEXT
---------------- ---------- ------------ -------------------------------------
0000000055DCD888 4132422484 0 SELECT sum(amount_sold) FROM sales s,
promotions p WHERE s.promo_id =
p.promo_id AND promo_subcategory =
'online discount'
JCQ0> DELETE plan_table;
JCQ0> INSERT INTO plan_table (operation, options, object_node, object_owner,
2 object_name, optimizer, search_columns, id,
3 parent_id, position, cost, cardinality, bytes,
4 other_tag, partition_start, partition_stop,
5 partition_id, other, distribution, cpu_cost,
6 io_cost, temp_space, access_predicates,
7 filter_predicates)
8 SELECT operation, options, object_node, object_owner, object_name,
9 optimizer, search_columns, id, parent_id, position, cost,
10 cardinality, bytes, other_tag, partition_start, partition_stop,
11 partition_id, other, distribution, cpu_cost, io_cost, temp_space,
12 access_predicates, filter_predicates
13 FROM v$sql_plan
14 WHERE address = '0000000055DCD888'
15 AND hash_value = 4132422484
16 AND child_number = 0;
JCQ0> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | 1 | 29 | |
|* 2 | HASH JOIN | | 64482 | 1826K| 426 (24)|
|* 3 | TABLE ACCESS FULL | PROMOTIONS | 24 | 504 | 4 (25)|
| 4 | PARTITION RANGE ALL| | | | |
| 5 | TABLE ACCESS FULL | SALES | 1016K| 7939K| 398 (19)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROMO_ID"="PROMO_ID")
3 - filter("PROMO_SUBCATEGORY"='online discount')