20140321]查看大量消耗资源的sql语句.txt
昨天看Apress.Oracle.Database.12c.Performance.Tuning.Recipes.Dec.2013.pdf,发现P394提高一个
sql语句查看Resource-Intensive SQL in Memory很有意思,做一个记录:
Viewing Resource-Intensive SQL in Memory P394
SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;
--我修改一下,实际上引号内可以加入许多条件。
SELECT sql_id,
sql_text,
SUBSTR (sql_text, 1, 80) sql_text,
disk_reads,
cpu_time,
elapsed_time,
buffer_gets,
ROUND (buffer_gets / executions, 0) buffer_gets_per_exec
FROM TABLE (
DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
'buffer_gets/executions > 1000 and executions>0'))
ORDER BY 8 DESC