查找最近5分钟里面最消耗IO的SQL语句:select s.sql_text from (select sql_id,count(*) as cnfrom v$active_session_historywhere sample_time > sysdate - 5/24/60 and session_type 'BACKGROUND'and WAIT_CLASS='User I/O'group by sql_idorder by cn desc) ash, v$sq...
显示全部查找最近5分钟里面最消耗IO的SQL语句:
select s.sql_text from
(select sql_id,count(*) as cn
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
and WAIT_CLASS='User I/O'
group by sql_id
order by cn desc) ash, v$sql s
where ash.sql_id = s.sql_id;
查找最近5分钟里面最消耗CPU的SQL语句:
select s.sql_text from
(select sql_id,count(*) as cn
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
and SESSION_STATE='ON CPU'
group by sql_id
order by cn desc) ash, v$sql s
where ash.sql_id = s.sql_id;
查找最近5分钟里面索引读最严重的SQL语句:
select s.sql_text from
(select sql_id,count(*) as cn
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
and event='db file sequential read'
group by sql_id
order by cn desc) ash, v$sql s
where ash.sql_id = s.sql_id;
这些都依赖于shared pool
shared pool的目的:缓存曾经执行过的SQL以及其对应的执行计划,再次执行同样的SQL的时候
能够在内存里命中该sql以及对应的执行计划,从而减少硬解析的次数,节省CPU的资源开销。
收起