oracle查看历史执行,Oracle查看历史执行计划

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