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 | 外部散列连接 | | 2513 | 2513 505K| 470 (2)|
|* 8 | 哈希连接 | | 2513 | 2513 485K| 415 (1)|
| 9 | 表格访问完全 | | TS$ | 7 | 133 | 133 4 (0)|
|* 10 | 外部散列连接 | | 2513 | 2513 439K| 410 (1)|
| 11 | 11 嵌套循环 | | 2485 | 2485 325K| 352 (1)|
|* 12 | 桌子访问完全 | OBJ$ | 3142 | 3142 11万| 236 (2)|
|* 13 | 表访问集群|标签$ | 1 | 98 | 98 1 (0)|
|* 14 | 索引唯一扫描 | I_OBJ# | 1 | | 0 (0)|
| 15 | 15 表格访问完全 | | SEG$ | 7092 | 7092 311K| 58 (0)|
| 16 | 16 索引快速全面扫描 | I_OBJ1 | | 69116 | 539K| 54 (0)|
| 17 | 17 索引快速全面扫描 | | I_OBJ2 | | 69116 | 2024K| 187 (0)|
PLAN_TABLE_OUTPUT
-------------------------------------------------------- --------------------------------------------------
| 18 | 缓冲区排序 | | 1 | 55 | 55 516 (10)|
|* 19 | 固定桌已满 | | X$KSPPI | 1 | 55 | 55 0 (0)|
-------------------------------------------------------- ------------------------------------------------
谓词信息（通过操作id标识）：
------------------------------------ ----------------
1 - 访问("KSPPI"."INDX"="KSPPCV"."INDX")
4 - 访问("T"."BOBJ#"="CO"."OBJ#")
5 - 访问("CX"."OWNER#"="CU"."USER#")
7 - 访问("T"."DATAOBJ#"="CX"."OBJ#")
PLAN_TABLE_OUTPUT
-------------------------------------------------------- -------------------------------------------------
8 - 访问("T"."TS #"="TS"."TS#")
10 - 访问("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S". "BLOCK#" AND
"T"."TS#"="S"."TS#")
12 - 过滤器(("O"."OWNER#"=USERENV('SCHEMAID') AND
BITAND("O"."FLAGS",128)=0))
13 - 过滤器(BITAND("T"."PROPERTY",1)=0)
14 - 访问("O "."OBJ#"="T"."OBJ#")
19 - 过滤器("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
注意
-----
PLAN_TABLE_OUTPUT
-------------------------------------------------------- -------------------------------------------------
- 'DYNAMIC_PLAN_TABLE' 是旧版本
已选择45行。
，
