20140321]查看大量消耗资源的sql语句.txt

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