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行。
，