查询共享池中占用内存超大的 SQL 语句

jiawang

SELECT substr(sql_text,1,100) "Stmt",
       count(*),
       sum(sharable_mem) "Mem",
       sum(users_opening) "Open",
       sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,100)
HAVING sum(sharable_mem) > 1000000
ORDER BY sum(sharable_mem) DESC;

按内存占用量降序排列，用于定位ORA-04031 共享池内存耗尽、硬解析过高、SQL版本过多等性能问题的根源 SQL。

登录/注册后可看大图