q_execution_plan 查询sql_id的执行计划

admin

1. set lines 170 pages 1000
   
2. select * from table(dbms_xplan.display('&sqlid'));--预估执行计划
   
3. select * from table(dbms_xplan.display_cursor('&sqlid'));--内存执行计划
   
4. select * from table(dbms_xplan.display_cursor('&sql_id',0,'ADVANCED'));--cursor advanced
   
5. select * from table(dbms_xplan.display_awr('&sqlid'));--awr执行计划
   
6. select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));--awr advanced
   
7. select * from table(dbms_xplan.display_sqlset('&sqlid'));--sqlset执行计划
   

复制代码

可以通过下面的三个视图查询到sql执行计划的历史信息：
DBA_HIST_SQL_PLAN
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT
查看语句的历史执行信息，是否发生变化，何时发生了变化。如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同。

1. --v$sql_plan
   
2. select distinct sql_id,plan_hash_value,to_char(timestamp,'yyyymmdd hh24:mi:ss') timestamp from v$sql_plan where sql_id='&sql_id' order by timestamp;
   

复制代码

1. --dba_hist_sql_plan
   
2. select distinct sql_id,plan_hash_value,to_char(timestamp,'yyyymmdd hh24:mi:ss') timestamp from dba_hist_sql_plan where sql_id='&sql_id' order by timestamp;

复制代码

1. --v$sql_plan
   
2. col options
   
3. FOR a15 col operation
   
4. FOR a20 col object_name
   
5. FOR a20
   
6. 
   
7. SET lines 0
   
8. 
   
9. SELECT plan_hash_value
   
10. ,id
    
11. ,operation
    
12. ,options
    
13. ,object_name
    
14. ,depth
    
15. ,cost
    
16. ,to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
    
17. FROM v$sql_plan
    
18. WHERE sql_id = '&&sql_id'
    
19. AND plan_hash_value IN (
    
20. SELECT DISTINCT plan_hash_value
    
21. FROM v$sql_plan
    
22. WHERE sql_id = '&&sql_id'
    
23. )
    
24. ORDER BY id
    
25. ,TIMESTAMP;

复制代码

1. --dba_hist_sql_plan
   
2. col options
   
3. FOR a15 col operation
   
4. FOR a20 col object_name
   
5. FOR a20
   
6. 
   
7. SET lines 0
   
8. 
   
9. SELECT plan_hash_value
   
10.         ,id
    
11.         ,operation
    
12.         ,options
    
13.         ,object_name
    
14.         ,depth
    
15.         ,cost
    
16.         ,bytes
    
17.         ,cpu_cost
    
18.         ,io_cost
    
19.         ,TIME
    
20.         ,to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP
    
21. FROM dba_hist_sql_plan
    
22. WHERE sql_id = '&&sql_id'
    
23.         AND plan_hash_value IN (
    
24.                 SELECT DISTINCT plan_hash_value
    
25.                 FROM dba_hist_sql_plan
    
26.                 WHERE sql_id = '&&sql_id'
    
27.                 )
    
28. ORDER BY id
    
29.         ,TIMESTAMP;

复制代码

查看出来执行计划的变化之后 可以使用如下sql查看发生了那些变化！

1. col options for a15 col operation for a20 col object_name for a20
   
2. select
   
3.         plan_hash_value
   
4.   , id
   
5.   , operation
   
6.   , options
   
7.   , object_name
   
8.   , depth
   
9.   , cost
   
10.   , to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss')
    
11. from
    
12.         DBA_HIST_SQL_PLAN
    
13. where
    
14.         sql_id ='&&sql_id'
    
15.         and plan_hash_value in (select distinct(plan_hash_value) from DBA_HIST_SQL_PLAN where sql_id='&&sql_id')
    
16. order by ID , TIMESTAMP;

复制代码