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