--消耗cpu资源

–消耗cpu资源
SELECT *
FROM (SELECT SUBSTR(sql_text, 1, 40) sql,
buffer_gets,
executions,
buffer_gets / executions “Gets/Exec”,
hash_value,
address
FROM v$sqlarea
WHERE buffer_gets > 0
AND executions > 0
ORDER BY buffer_gets DESC)
WHERE ROWNUM <= 10;

select * from v$database;
ps aux|sort -n -k4|tail -20

SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = ‘58036’))
ORDER BY piece ASC;

select *
from (select sqt.logicr logical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), sqt.logicr ) Reads_per_Exec,
(100 * sqt.logicr) ,
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b,
DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = 22297
AND E.SNAP_ID = 22299
AND B.DBID = 1528541826
AND E.DBID = 1528541826
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME =
‘session logical reads’
and b.stat_name =
‘session logical reads’) Total_rate,
nvl((sqt.cput), to_number(null)) CPU_Time_s,
nvl((sqt.elap ), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, ‘Module: ’ || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob(’** SQL Text Not Available **’)) SQL_Text
from (select sql_id,
max(module) module,
sum(buffer_gets_delta) logicr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 1528541826
and instance_number = 1
and 22297 < snap_id
and snap_id <= 22299
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1528541826
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = 22297
AND E.SNAP_ID = 22299
AND B.DBID = 1528541826
AND E.DBID = 1528541826
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = ‘session logical reads’
and b.stat_name = ‘session logical reads’) > 0
order by nvl(sqt.logicr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or Total_rate > 1);