oracle硬解析过多,ORACLE未绑定变量和硬解析过多问题处理

可以增加共享池大小，

如果从根本解决问题，还需要增加硬件资源。升级oracle版本，优化应用部署等才能根本解决。另外：

1、查询分配情况

SELECT 'shared pool (' || NVL (DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx),'Total') || '):' subpool,

SUM (ksmsslen) BYTES, ROUND (SUM (ksmsslen) / 1048576, 2) mb

FROM x$ksmss WHERE ksmsslen > 0

GROUP BY ROLLUP (ksmdsidx) ORDER BY subpool ASC；

2、 查询一下各个子池的剩余内存

SELECT subpool, NAME, SUM (BYTES), ROUND (SUM (BYTES) / 1048576, 2) mb

FROM (SELECT 'shared pool (' || DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx)

|| '):' subpool, ksmssnam NAME, ksmsslen BYTES

FROM x$ksmss WHERE ksmsslen > 0 AND LOWER (ksmssnam) LIKE LOWER ('%free memory%'))

GROUP BY subpool, NAME ORDER BY subpool ASC, SUM (BYTES) DESC

/

3、通过以下查询可以详细列举不同子池的Free内存块情况

SQL> SELECT ksmchidx "SubPool", 'sga heap(' || ksmchidx || ',0)' sga_heap,

2 ksmchcom chunkcomment,

3 DECODE (ROUND (ksmchsiz / 1000),

4 0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',

5 6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K'

6 ) "size",

7 COUNT (*), ksmchcls status, SUM (ksmchsiz) BYTES

8 FROM x$ksmsp WHERE ksmchcom = 'free memory'

9 GROUP BY ksmchidx, ksmchcls, 'sga heap(' || ksmchidx || ',0)',ksmchcom, ksmchcls,

10 DECODE (ROUND (ksmchsiz / 1000),

11 0, '0-1K', 1, '1-2K',2, '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',

12 6, '6-7k', 7, '7-8k',8, '8-9k',9, '9-10k', '> 10K' );

如果发现大的内存请求在剩余》10k中很小的话 ，就有可能报4031错误

show parameter cursor

SQL> select a.*,b.name

from v$sesstat a , v$statname b

where a.statistic#=b.statistic#

and a.sid=(select distinct sid from v$mystat)

and b.name like '%parse%'

SELECT substr(sql_text,1,40) "SQLTXT",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2 DESC

新方法：

SELECT FORCE_MATCHING_SIGNATURE，COUNT(*)

FROM V$SQL

GROUP BY FORCE_MATCHING_SIGNATURE

ORDER BY 2 DESC

SQL> select '硬解析' SQL解析类型,

2  ((SELECT value from v$sysstat where name like 'parse count (hard)')/(SELECT value from v$sysstat where name lik