v$sql

1.查询v$sqlarea定位使用资源比较多的sql

select a.SQL_TEXT,b.username,fetches,executions,disk_reads,a.disk_reads/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rate ,buffer_gets
from v$sqlarea a,dba_users b where a.PARSING_USER_ID=b.user_id order by a.DISK_READS desc;

2.查找buffer_get大的sql语句,讲算其占比

v$sql可以替换为v$sqlarea

rank() over ( order by buffer_gets desc)  rank_fufgets,返回排序后的序号

如1

    2

    3
to_char(100*ratio_to_report(buffer_gets) over () ,'999,99')计算占比

select * from
( select sql_text, address,
 rank() over ( order by buffer_gets desc)  rank_fufgets,
to_char(100*ratio_to_report(buffer_gets) over () ,'999.99') pct_bufgets from v$sql)
where rank_fufgets<11;

3.查找当前session资源使用状况,15秒钟左右

select b.USERNAME,b.MACHINE,b.MODULE,a.* from v$sessmetric a,v$session b where a.SESSION_ID=b.SID;

 

4.查看可用的awr snapshots

select * from dba_hist_snapshot;

5.查找历史sql信息

select snap_id,disk_reads_delta reads_delta,executions_delta,disk_reads_delta,sql_id from dba_hist_sqlstat
where disk_reads_delta>1000 order by disk_reads_delta desc;
select * from dba_hist_sqltext where sql_id='8n7qvaja450hy'

 

select * from table(dbms_xplan.display_awr('cjvjnr0n0yr29'));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7301064/viewspace-448814/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7301064/viewspace-448814/