Oracle 如何根据动态性能视图来查看某条SQL语句的执行计划

1、授权某用户查看 sys.v$sql_plan 的权限

2、创建类plan_table的视图

SQL> create or replace view dynamic_plan_table
2 as
3 select rawtohex(address)|| '_' ||child_number statement_id,
4 sysdate timestamp,operation,options,object_node,
5 object_owner,object_name,0 object_instance,
6 optimizer,search_columns,id,parent_id,position,
7 cost,cardinality,bytes,other_tag,partition_start,
8 partition_stop,partition_id,other,distribution,
9 cpu_cost,io_cost,temp_space,access_predicates,
10 filter_predicates
11 from v$sql_plan;

视图已创建。

3、查看执行计划

SQL> select plan_table_output
2 from table(dbms_xplan.display
3 ('dynamic_plan_table',
4 (select rawtohex(address)||'_'||child_number x
5 from v$sql where sql_text=
6 'select * from user_tables'),
7 'serial'))
8 /

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

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 703 (100)|
|* 1 | HASH JOIN | | 2513 | 5700K| 703 (7)|
| 2 | FIXED TABLE FULL | X$KSPPCV | 100 | 196K| 0 (0)|
| 3 | MERGE JOIN CARTESIAN | | 2513 | 755K| 703 (7)|
|* 4 | HASH JOIN OUTER | | 2513 | 620K| 661 (1)|
|* 5 | HASH JOIN RIGHT OUTER | | 2513 | 547K| 473 (2)|
| 6 | TABLE ACCESS FULL | USER$ | 84 | 1428 | 3 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | HASH JOIN OUTER | | 2513 | 505K| 470 (2)|
|* 8 | HASH JOIN | | 2513 | 485K| 415 (1)|
| 9 | TABLE ACCESS FULL | TS$ | 7 | 133 | 4 (0)|
|* 10 | HASH JOIN OUTER | | 2513 | 439K| 410 (1)|
| 11 | NESTED LOOPS | | 2485 | 325K| 352 (1)|
|* 12 | TABLE ACCESS FULL | OBJ$ | 3142 | 110K| 236 (2)|
|* 13 | TABLE ACCESS CLUSTER| TAB$ | 1 | 98 | 1 (0)|
|* 14 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)|
| 15 | TABLE ACCESS FULL | SEG$ | 7092 | 311K| 58 (0)|
| 16 | INDEX FAST FULL SCAN | I_OBJ1 | 69116 | 539K| 54 (0)|
| 17 | INDEX FAST FULL SCAN | I_OBJ2 | 69116 | 2024K| 187 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 18 | BUFFER SORT | | 1 | 55 | 516 (10)|
|* 19 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)|
------------------------------------------------------------------------------

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

1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
4 - access("T"."BOBJ#"="CO"."OBJ#")
5 - access("CX"."OWNER#"="CU"."USER#")
7 - access("T"."DATAOBJ#"="CX"."OBJ#")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8 - access("T"."TS#"="TS"."TS#")
10 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND
"T"."TS#"="S"."TS#")
12 - filter(("O"."OWNER#"=USERENV('SCHEMAID') AND
BITAND("O"."FLAGS",128)=0))
13 - filter(BITAND("T"."PROPERTY",1)=0)
14 - access("O"."OBJ#"="T"."OBJ#")
19 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'DYNAMIC_PLAN_TABLE' is old version

已选择45行。