---逻辑读最多的SQL
SELECT *
FROM (SELECT buffer_gets, sql_text
FROM v$sqlarea
WHERE buffer_gets > 500000
ORDER BY buffer_gets DESC)
WHERE ROWNUM
-- 执行次数多的SQL
SELECT sql_text, executions
FROM (SELECT sql_text, executions
FROM v$sqlarea
ORDER BY executions DESC)
WHERE ROWNUM
-- 读硬盘多的SQL
SELECT sql_text, disk_reads
FROM (SELECT sql_text, disk_reads
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM
-- 排序多的SQL
SELECT sql_text, sorts
FROM (SELECT sql_text, sorts
FROM v$sqlarea
ORDER BY sorts DESC)
WHERE ROWNUM
--分析的次数太多，执行的次数太少，要用绑变量的方法来写sql
SELECT SUBSTR (sql_text, 1, 80) "sql", COUNT (*),
SUM (executions) "totexecs"
FROM v$sqlarea
WHERE executions 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;
SELECT *
FROM (SELECT buffer_gets, sql_text
FROM v$sqlarea
WHERE buffer_gets > 500000
ORDER BY buffer_gets DESC)
WHERE ROWNUM
-- 执行次数多的SQL
SELECT sql_text, executions
FROM (SELECT sql_text, executions
FROM v$sqlarea
ORDER BY executions DESC)
WHERE ROWNUM
-- 读硬盘多的SQL
SELECT sql_text, disk_reads
FROM (SELECT sql_text, disk_reads
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM
-- 排序多的SQL
SELECT sql_text, sorts
FROM (SELECT sql_text, sorts
FROM v$sqlarea
ORDER BY sorts DESC)
WHERE ROWNUM
--分析的次数太多，执行的次数太少，要用绑变量的方法来写sql
SELECT SUBSTR (sql_text, 1, 80) "sql", COUNT (*),
SUM (executions) "totexecs"
FROM v$sqlarea
WHERE executions 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;
