----当前最耗资源的10个cpu
select * from (select address,hash_value,
round(cpu_time/1000000) cpu_time_s,
round(cpu_time/decode(executions,0,1,executions)/1000000,2) cpu_time_per,
executions,
SQL_TEXT
from v$sqlarea
order by cpu_time_s desc)
where rownum <= 10;
----当前最耗资源的10个cpu
select * from (select A.ADDRESS,A.hash_value,B.sid,p.SPID,
round (A.CPU_TIME /1000000) cpu_time_s,
round(A.cpu_time/decode(executions,0,1,executions)/1000000,2) cpu_time_per,
A.executions,
A.SQL_TEXT
from V$SQLAREA A, V$SESSION B, V$PROCESS P
WHERE B.SQL_HASH_VALUE = A.HASH_VALUE
AND P.ADDR = B.PADDR
order by cpu_time_s desc)
where rownum <= 10;
---历史分析sql_id
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/