---邏輯讀最多的SQL
SELECT *
FROM (SELECT buffer_gets, sql_text
         FROM v$sqlarea
         WHERE buffer_gets > 500000
      ORDER BY buffer_gets DESC)
WHERE ROWNUM <= 30;

-- 執行次數多的SQL
SELECT sql_text, executions
FROM (SELECT sql_text, executions
         FROM v$sqlarea
      ORDER BY executions DESC)
WHERE ROWNUM < 81;

-- 讀硬碟多的SQL
SELECT sql_text, disk_reads
FROM (SELECT sql_text, disk_reads
         FROM v$sqlarea
      ORDER BY disk_reads DESC)
WHERE ROWNUM < 21;

-- 排序多的SQL
SELECT sql_text, sorts
FROM (SELECT sql_text, sorts
         FROM v$sqlarea
      ORDER BY sorts DESC)
WHERE ROWNUM < 21;
--分析的次數太多，執行的次數太少，要用綁變數的方法來寫sql
SELECT SUBSTR (sql_text, 1, 80) "sql", COUNT (*),
SUM (executions) "totexecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY SUBSTR (sql_text, 1, 80)
HAVING COUNT (*) > 30
ORDER BY 2;

---找到比較長的SQL語句
SELECT a.sql_id, COUNT (a.piece)
FROM v$sqltext a
GROUP BY a.sql_id
HAVING COUNT (a.piece) > 4;

常用的SQL 語句

相關文章