SQL获取AWR信息

----快照排序
select snap_id,dbid,end_time from (
select snap_id,dbid,to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') end_time from dba_hist_snapshot
order by end_interval_time desc)
where rownum<100;


------PLSQL取AWR报告


select * from table(dbms_workload_repository.awr_report_html('203185647',1,175129,175130));






----创建快照


exec dbms_workload_repository.create_snapshot;




-----某个SQL在时间范围内的情况


select a.snap_id,c.begin_interval_time,c.end_interval_time,a.elapsed_time_delta/a.executions_delta/1000000 "S",a.sql_id,b.sql_text,a.buffer_gets_delta/a.executions_delta "buffer_gets/次",a.disk_reads_delta/a.executions_delta "disk_read/次" from dba_hist_sqlstat a, dba_hist_sqltext b,dba_hist_snapshot c
where a.sql_id=b.sql_id 
and a.snap_id=c.snap_id
and a.sql_id='18ahw8aawvkbr'
and a.snap_id between 182723 and 184126
and a.executions_delta >0;


------








select s.instance_number,s.snap_id, to_char(sn.end_interval_time,'YYYYMMDD HH24:MI:SS'),s.plan_hash_value, s.executions_delta,
round(s.elapsed_time_delta/s.executions_delta) ,
round(s.disk_reads_delta/s.executions_delta) ,
round(s.BUFFER_GETS_delta/s.executions_delta) ,
round(s.CPU_TIME_delta/s.executions_delta),
round(s.ccwait_delta/s.executions_delta),
round(s.iowait_delta/s.executions_delta),
round(s.apwait_delta/s.executions_delta)
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and s.sql_id='dd4rakddcp6tp'
and s.instance_number=sn.instance_number
and s.executions_delta>0
order by sn.end_interval_time desc;








select s.instance_number,s.snap_id, to_char(sn.end_interval_time,'YYYYMMDD HH24:MI:SS'),s.plan_hash_value, s.executions_delta,
round(s.elapsed_time_delta) ,
round(s.disk_reads_delta) ,
round(s.BUFFER_GETS_delta) ,
round(s.CPU_TIME_delta),
round(s.ccwait_delta),
round(s.iowait_delta),
round(s.apwait_delta)
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and s.sql_id='6m8yzqna87n8t'
and s.instance_number=sn.instance_number
order by sn.end_interval_time desc;










select s.instance_number,s.snap_id, to_char(sn.end_interval_time,'YYYYMMDD HH24:MI:SS'),s.plan_hash_value, s.executions_delta,
round(s.elapsed_time_delta/s.executions_delta) elspsed_time ,
round(s.disk_reads_delta/s.executions_delta) disk_reads,
round(s.BUFFER_GETS_delta/s.executions_delta) BUFFER_GETS ,
round(s.CPU_TIME_delta/s.executions_delta) CPU_TIME,
round(s.ccwait_delta/s.executions_delta) ccwait_delta,
round(s.iowait_delta/s.executions_delta) iowait_delta,
round(s.apwait_delta/s.executions_delta) apwait_delta
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and s.sql_id='1q6wcxm90w0tr'
and s.instance_number=sn.instance_number
and s.executions_delta>0
order by sn.end_interval_time desc;










----索引聚因子
在上述sql语句的输出结果中，如果dba_indexes.clustering_factor接近表中块的数量，
那么表中大多数行是排序的。这是期望的。


然而，如果clustering factor接近表中行的数量，
它意味着表中的行是随机排列，这种情况对于同样叶块中的索引块来说，
指向同样的数据块中的行是不可能的，因此它会导致更多的I/O来完成操作。


你可以采取rebuilding表来改善索引clustering factor，为了行根据索引键来排序，其后重建索引。




select id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocks
 from dba_indexes id,dba_tables tb
 where id.table_name=tb.table_name 
 and tb.table_name='XXXX'and tb.owner='&2'