查看下系统中是否存在大量硬解析以及未使用绑定变量的语句。

>> 系统中硬解析的数量：

SELECT a.*, SYSDATE - b.startup_time days_old
FROM v$sysstat a, v$instance b
WHERE a.NAME LIKE 'parse%'

>> 发现当前会话中的硬解析

SELECT a.sid,
c.username, c.program,
b.NAME, a.VALUE,
round((SYSDATE - c.logon_time) * 24) hours_connected
FROM v$sesstat a, v$statname b, v$session c
WHERE c.sid = a.sid
AND a.statistic# = b.statistic#
AND a.VALUE > 0 AND b.NAME = 'parse count (hard)'

>> 查找未绑定变量SQL语句

SELECT hash_value, substr(sql_text, 1, 80)
FROM v$sqlarea
WHERE substr(sql_text, 1, 40) IN
(SELECT substr(sql_text, 1, 40)
FROM v$sqlarea
HAVING COUNT(*) > 4
GROUP BY substr(sql_text, 1, 40))