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博客 ” ，链接：https://blog.itpub.net/7301064/viewspace-448814/ ，如需转载，请注明出处，否则将追究法律责任。
