Oracle查看历史执行计划

yxsailing

　　select b.instance_number,
　　b.snap_id,
　　a.begin_interval_time,
　　a.end_interval_time,
　　b.sql_id,
　　b.plan_hash_value,
　　b.parsing_schema_name,
　　b.executions_delta,
　　b.rows_processed_delta,
　　round(b.elapsed_time_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_elapsed_time,
　　round(b.cpu_time_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_cpu_time,
　　round(b.buffer_gets_delta / decode(b.executions_delta,0,1,b.executions_delta),0) as avg_buffer_gets,
　　round(b.iowait_delta/decode(b.executions_delta,0,1,b.executions_delta),0) as avg_iowait,
　　round(b.disk_reads_delta/decode(b.executions_delta,0,1,b.executions_delta),0) as avg_disk_reads,
　　round(b.ccwait_delta/decod
　　select dt.owner,
　　dt.table_name tablename,
　　dc.comments,
　　dt.num_rows,
　　dt.last_analyzed,
　　bb.tab_size_GB table_size_GB,
　　bb.index_size_GB index_size_GB,
　　decode(bb.islob, 0, null, 'LOB') islob,
　　dt.temporary
　　from dba_tables dt,
　　dba_tab_comments dc,
　　(select table_owner,
　　table_name,
　　round(sum(tab_size_GB) / power(1024, 3), 2) as tab_size_GB,
　　round(sum(index_size_GB) / power(1024, 3), 2) as index_size_GB,
　　sum(IsLob) as IsLob
　　from (select ss.owner table_owner,
　　NVL(dl.table_name, ss.segment_name) table_name,
　　ss.bytes tab_size_GB,
　　0 index_size_GB,
　　decode(dl.table_name, null, 0, 1) IsLob
　　from dba_segments ss, dba_lobs dl
　　where ss.owner = dl.owner(+)
　　and ss.segment_name = dl.segment_name(+)
　　and (ss.segment_type like 'TABLE%' or
　　ss.segment_type LIKE 'LOB%')
　　union all
　　select ind.table_owner,
　　ind.table_name,
　　0,
　　ss.bytes        index_size_GB,
　　0               IsLob
　　from dba_segments ss, dba_indexes ind
　　where ind.owner = ss.owner
　　and ind.index_name = ss.segment_name)
　　group by table_owner, table_name) bb
　　where dt.owner not in (select username from dbmgr.sys_users)
　　and dt.owner not in ('GGMGR', 'APPMGR')
　　and dt.owner = bb.table_owner
　　and dt.table_name = bb.table_name
　　and dt.owner = dc.owner(+)
　　and dt.table_name = dc.table_name(+)
　　http://www.dataguru.cn/article-10217-1.html