obsolete or deprecated parameter(s) specified for RDBMS instanc

我不是女神ヾ 2022-05-28 02:29 195阅读 0赞

[4031\_diag\_script.zip][4031_diag_script.zip]

1. SGA中的内存池包含不同大小的内存块。当数据库启动时，就有一个大的内存块分配并被hush buckets 里的空闲列表追踪。随着时间推移，随着内存的分配和释放，内存块被按照大小在不同的hush buckets间移动。当SGA里任何一个内存池里出现不能满足内部分配请求的情况时，ORA-04031就出现了。  
shared pool共享池的管理方式不同于其它的内存池。。共享池存放与数据字典和library cache有关的信息。但是，这些内存区域根据空闲列表和最近使用算法(LRU)管理。当在共享池的所有搜索结束后，从LRU列表清除所有的可能清除的对象， 多次扫描空闲列表后，仍没有找到内存块，ORA-04031就出现了。这意味着ORA-04031很难预测。

2. 对共享池的监测，可以看它是否包含许多类似的SQL,只有文字不同。 这种情况会占用更多的共享池内存并引共享池碎片，过多的共享池碎片(fragment)会导致虽然共享池中仍有大量的free memory，但都是尺寸较小的内存块(chunk)，当Oracle进程申请一些较大的连续内存空间(memory chunk)时，虽然共享池中的free memory大小远大于申请的连续空间大小，仍会引发ORA-4031错误。使用绑定变量可以使SQL 共享。使用本文所附的脚本可以查出内存中是否有许多类似SQL。  
即使使用了绑定变量后，仍然可能存在高version count（子指针）的情况。为了使子指针共享，CURSOR\_SHARING参数可能需要调整。metalink 文档Note 296377.1 和 261020.1可以提供详细信息。若造成4031的原因是由于未绑定变量或者游标无法共享导致的过度硬解析(Hard Parse)，则应当调整应用绑定变量或者调整初始化参数。

3.  9i中开始引入shared pool subpool子池技术。设计多个共享池子池的目的是分散单个shared pool LRU Latch的并行压力。 ，每一个子池都包含自有的LRU LIST和保留区域等其他内存结构。 subpools子池的数量会在Oracle实例启动(startup nomount)时根据SGA\_MAX\_SIZE(或11g中的memory\_max\_target)以及服务器上的CPU数量而自动决定。子池数量最多为7个，在Card系统中为4个。  
每一个subpool都是一个”迷你型”的共享池，其包括自有的Freelist、内存结构记录和LRU LIST。subpool子池技术是对shared pool并发扩张能力的增强，且每一个子池现在都使用独立的shared pool child latch来保护。这意味着不再像9i以前的版本那样因为只有一个shared pool latch而剧烈争用了。  
但是在实际使用中发现版本9i中仍存在一些BUG，可能导致在子池之间的内存使用分布不平衡均匀，这可能导致虽然部分子池使用率不高，但是个别子池内存过度分配，从而导致ORA-4031错误。一般把这种现象称作”subpool imbalance”。

This issue could occur if in the “Memory Utilization of Subpool” sections in your trace file, there is skewed distribution of allocations across subpools, i.e. allocation size for one area in a subpool is much larger than the same area in another subpool.

Starting in 9i, the SGA can be divided into subpools. Multiple Subpools are designed to relieve pressure on a single LRU latch in the Shared Pool. Each subpool will include its own LRU list, Reserved Area, etc. The number of subpools is computed by an algorithm using the SGA\_MAX\_SIZE (or memory\_max\_target in 11g), and the number of CPU’s on the server. The maximum number of subpools is 7.

The shared pool and its associated reserved area, as well as the large pool, participate in subpooling. Each subpool is a “mini” shared pool, having its own set of Free Lists, memory structure entries, and LRU list. This was a scalability change made to the Shared Pool/Large Pool to increase the throughput of these pools in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared/Large Pool for a single latch as in earlier versions.

If the distribution of memory usage is not balanced somewhat equally among the subpools, we can get an ORA-4031 as one subpool is over allocated while others remain under allocated. This condition is known as a subpool imbalance.

By analyzing the uploaded file, we have found the following symptoms that may have caused the issue:  
\*\* In your trace file, there is evidence of subpool imbalance:  
sql area in subpool 0 has 64536 bytes, while in subpool 4 it has 271924816 bytes, which is 4213 times larger.

Recommended Solution

1) Decrease the number of subpools in use, or increase SHARED\_POOL\_SIZE, depending on the current number of subpools.(To find the current number of subpools, see REFERENCE below)

If the current number of subpools is…  
2…………….increase the parameter SHARED\_POOL\_SIZE by 15%  
Note: If you decrease the subpools to one, you limit the Shared Pool to only one Least Recently Used (LRU) latch. One very large subpool maintains a long LRU list and memory operations in the Shared Pool can impact performance on the database. The level of impact is dependent on many factors. Testing of this change in your environment is the only reliable method to determine the cost of making this change in your database.  
3…………….reduce the number of subpools to 2  
4…………….reduce the number of subpools to 2  
5…………….reduce the number of subpools to 3  
6…………….reduce the number of subpools to 3  
7…………….reduce the number of subpools to 3

Note: Decreasing the number of subpools (>1) may help alleviate the imbalance problem without affecting performance as heavily. Again testing of the change in your environment is the only reliable method to determine the performance impacts in your database.  
You can change the number of subpools by:  
SQL> alter system set “\_kghdsidx\_count”=(desired value of number of subpools) scope=spfile;

Note: \_kghdsidx\_count in the above mentioned SQL is a hidden parameter. However, changing this particular hidden parameter will not have side-effects within the database and can be modified as a workaround. This is not intended to be a long term fix.

2) Restart the database for the change to take effect.

3) If ORA-04031 errors persist, please review the following notes for known subpool imbalance issues.  
NOTE:811974.1 – ORA-4031 In Partition Maintenance Job From Subpool Imbalance (Doc ID 811974.1)  
NOTE:835176.1 – Ora-4031 Errors using Full Outer Joins  
NOTE:6271590.8 – Bug 6271590 – SGA subheap imbalance with lots of free memory in a few subheaps  
NOTE:4184298.8 – Bug 4184298 – Subpool imbalance for “session parameters” can lead to ORA-4031

o Instructions for capturing a heapdump from the next occurrence of an ORA-04031 error are shown below.  
alter system set events ’4031 trace name HEAPDUMP level 2′;  
o Turn this event off using  
alter system set events ’4031 trace name HEAPDUMP off’;

4) If the latest patchset recommended by the above notes have been applied and ORA-04031 errors persist, please submit a new Service Request to have the issue investigated by Oracle Support.  
NOTE: If you captured any heapdump please upload to the Service Request to expedite the resolution time on the Service Request.

REFERENCE: To check the current number of subpools, use the following query:

**\[sql\]** [view plain][] [copy][view plain]

1.  set pages 1000 lines 120  
2.   col name for a60  
3.  col value for a30  
4.   spool diagnosis1.lst  
5.    
6.  SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;  
7.    
8.   SELECT substr(sql\_text,1,90) "SQL",count(\*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5  
9.  GROUP BY substr(sql\_text,1,90) HAVING count(\*) > 30 ORDER BY 2;  
10.   
11. select \* from v$sgastat where pool like 'shared%' order by bytes;  
12.   
13. select sa.sql\_text,sa.version\_count ,ss.\* from v$sqlarea sa,v$sql\_shared\_cursor ss where sa.address=ss.ADDRESS and  
14. sa.version\_count > 50 order by sa.version\_count ;  
15.   
16. select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and  
17. nam.ksppinm like '%shared%' order by 1;  
18.   
19. col free\_space for 999,999,999,999 head "TOTAL FREE"  
20. col avg\_free\_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"  
21. col free\_count for 999,999,999,999 head "COUNT"  
22. col reqeust\_misses for 999,999,999,999 head "REQUEST|MISSES"  
23. col reqeust\_failures for 999,999,999,999 head "REQUEST|FAILURES"  
24. col max\_free\_size for 999,999,999,999 head "LARGEST CHUNK"  
25.   
26. select free\_space, avg\_free\_size, free\_count, max\_free\_size, request\_misses, request\_failures,LAST\_FAILURE\_SIZE from v$shared\_pool\_reserved  
27.   
28. col Parameter format a25  
29. col "Session Value" format a15  
30. col "Instance Value" format a15  
31.   
32. select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"  
33. from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c  
34. where a.indx = b.indx and a.indx = c.indx and a.ksppinm in  
35. ('\_\_shared\_pool\_size','\_\_db\_cache\_size','\_\_large\_pool\_size','\_\_java\_pool\_size','\_\_streams\_pool\_size');  
36.   
37. set pagesize 100  
38. select \* from v$sga\_dynamic\_components;  
39.   
40. col requests for 999,999,999  
41. col last\_failure\_size for 999,999,999 head "LAST FAILURE| SIZE "  
42. col last\_miss\_size for 999,999,999 head "LAST MISS|SIZE "  
43. col pct for 999 head "HIT|% "  
44. col request\_failures for 999,999,999,999 head "FAILURES"  
45. select requests,  
46. decode(requests,0,0,trunc(100-(100\*(request\_misses/requests)),0)) PCT, request\_failures, last\_miss\_size, last\_failure\_size  
47. from v$shared\_pool\_reserved;  
48.   
49. select p.inst\_id, p.free\_space, p.avg\_free\_size, p.free\_count,  
50.   p.max\_free\_size, p.used\_space, p.avg\_used\_size, p.used\_count, p.max\_used\_size,  
51.   s.requests, s.request\_misses, s.last\_miss\_size, s.max\_miss\_size,  
52.   s.request\_failures, s.last\_failure\_size, s.aborted\_request\_threshold,  
53.   s.aborted\_requests, s.last\_aborted\_size  
54.   from (select avg(x$ksmspr.inst\_id) inst\_id,  
55.   sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free\_space,  
56.   avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg\_free\_size,  
57.   sum(decode(ksmchcls,'R-free',1,0)) free\_count,  
58.   max(decode(ksmchcls,'R-free',ksmchsiz,0)) max\_free\_size,  
59.   sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used\_space,  
60.   avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg\_used\_size,  
61.   sum(decode(ksmchcls,'R-free',0,1)) used\_count,  
62.   max(decode(ksmchcls,'R-free',0,ksmchsiz)) max\_used\_size from x$ksmspr  
63.   where ksmchcom not like '%reserved sto%') p,  
64.     (select sum(kghlurcn) requests, sum(kghlurmi) request\_misses,  
65.     max(kghlurmz) last\_miss\_size, max(kghlurmx) max\_miss\_size,  
66.     sum(kghlunfu) request\_failures, max(kghlunfs) last\_failure\_size,  
67.     max(kghlumxa) aborted\_request\_threshold, sum(kghlumer) aborted\_requests,  
68.     max(kghlumes) last\_aborted\_size from x$kghlu) s;  
69.   
70. SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,  
71. To\_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"  
72. FROM X$KSMSP GROUP BY KSMCHCLS;  
73.   
74. set pagesize 80  
75. set verify off  
76. set heading off  
77. set feedback off  
78. set termout off  
79.   
80. col sp\_size     format          999,999,999 justify right  
81. col x\_sp\_used   format          999,999,999 justify right  
82. col sp\_avail    format          999,999,999 justify right  
83. col sp\_sz\_pins format           999,999,999 justify right  
84. col sp\_no\_pins format           999,999 justify right  
85. col sp\_no\_obj format            999,999 justify right  
86. col sp\_sz\_obj format            999,999 justify right  
87. col sp\_no\_stmts format          999,999 justify right  
88. col sp\_sz\_kept\_chks format      999,999,999 justify right  
89. col sp\_no\_kept\_chks format      999,999 justify right  
90.   
91. col val2 new\_val x\_sp\_size noprint  
92. select value val2  
93. from   v$parameter  
94. where  name='shared\_pool\_size'  
95. /  
96. col val2 new\_val x\_sp\_used noprint  
97. col val3 new\_val x\_sp\_no\_stmts noprint  
98. select sum(sharable\_mem+persistent\_mem+runtime\_mem) val2, count(\*) val3  
99. from   v$sqlarea  
100. /  
101. col val2 new\_val x\_sp\_no\_obj noprint  
102. col val3 new\_val x\_sp\_sz\_obj noprint  
103. select decode(count(\*),'',0,count(\*)) val2,  
104.        decode(sum(sharable\_mem),'',0,sum(sharable\_mem)) val3  
105. from v$db\_object\_cache  
106. where type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')  
107. /  
108. col val2 new\_val x\_sp\_avail noprint  
109. select sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'  
110. /  
111. col val2 new\_val x\_sp\_no\_kept\_chks noprint  
112. col val3 new\_val x\_sp\_sz\_kept\_chks noprint  
113. select decode(count(\*),'',0,count(\*)) val2,  
114.        decode(sum(sharable\_mem),'',0,sum(sharable\_mem)) val3  
115. from   v$db\_object\_cache  
116. where  kept='YES'  
117. /  
118. col val2 new\_val x\_sp\_no\_pins noprint  
119. select count(\*) val2  
120. from v$session a, v$sqltext b  
121. where a.sql\_address||a.sql\_hash\_value = b.address||b.hash\_value  
122. /  
123. col val2 new\_val x\_sp\_sz\_pins noprint  
124. select sum(sharable\_mem+persistent\_mem+runtime\_mem) val2  
125. from   v$session a,  
126.        v$sqltext b,  
127.        v$sqlarea c  
128. where  a.sql\_address||a.sql\_hash\_value = b.address||b.hash\_value and  
129.        b.address||b.hash\_value = c.address||c.hash\_value  
130. /  
131.   
132. set termout on  
133. set heading off  
134.   
135. ttitle -  
136.   center  'Shared Pool''s Library Cache Information'  skip 2  
137. select  'Size                                    : '  
138.                 ||&x\_sp\_size sp\_size,  
139.         'Number of shared cursors                : '  
140.                 ||&x\_sp\_no\_stmts sp\_no\_stmts,  
141.         'Used by shared cursors                  : '  
142.                 ||&x\_sp\_used,  
143.         'Number of programmatic constructs       : '  
144.                 ||&x\_sp\_no\_obj sp\_no\_obj,  
145.         'Used by programmatic constructs         : '  
146.                 ||&x\_sp\_sz\_obj sp\_sz\_obj,  
147.         'Available                               : '  
148.                 ||&x\_sp\_avail sp\_avail,  
149.         'Kept object chunks                      : '  
150.                 ||&x\_sp\_no\_kept\_chks sp\_no\_kept\_chks,  
151.         'Kept object chunks size                 : '  
152.                 ||&x\_sp\_sz\_kept\_chks sp\_sz\_kept\_chks,  
153.         'Pinned statements                       : '  
154.                 ||&x\_sp\_no\_pins sp\_no\_pins,  
155.         'Pinned statements size                  : '  
156.                 ||&x\_sp\_sz\_pins sp\_sz\_pins  
157. from    dual  
158. /  
159.   
160. ttitle off  
161. set heading on  
162. set feedback on  
163.   
164. set pages 1000 lines 120  
165.  col name for a60  
166. col value for a30  
167.   
168. select \* from v$sgastat where pool like 'shared%' and name='free memory';  
169.   
170. select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10\*trunc(KSMCHSIZ/10) "From",   
171. count(\*) "Count" , max(KSMCHSIZ) "Biggest",   
172. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"   
173. from x$ksmsp   
174. where KSMCHSIZ<140   
175. and KSMCHCLS='free'   
176. group by KSMCHCLS, KSMCHIDX, 10\*trunc(KSMCHSIZ/10)   
177. UNION ALL   
178. select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20\*trunc(KSMCHSIZ/20) ,   
179. count(\*) , max(KSMCHSIZ) ,   
180. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"   
181. from x$ksmsp   
182. where KSMCHSIZ between 140 and 267   
183. and KSMCHCLS='free'   
184. group by KSMCHCLS, KSMCHIDX, 20\*trunc(KSMCHSIZ/20)   
185. UNION ALL  
186. select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50\*trunc(KSMCHSIZ/50) ,   
187. count(\*) , max(KSMCHSIZ) ,   
188. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"   
189. from x$ksmsp   
190. where KSMCHSIZ between 268 and 523   
191. and KSMCHCLS='free'   
192. group by KSMCHCLS, KSMCHIDX, 50\*trunc(KSMCHSIZ/50)   
193. UNION ALL   
194. select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500\*trunc(KSMCHSIZ/500) ,   
195. count(\*) , max(KSMCHSIZ) ,   
196. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"   
197. from x$ksmsp   
198. where KSMCHSIZ between 524 and 4107   
199. and KSMCHCLS='free'   
200. group by KSMCHCLS, KSMCHIDX, 500\*trunc(KSMCHSIZ/500)   
201. UNION ALL   
202. select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000\*trunc(KSMCHSIZ/1000) ,   
203. count(\*) , max(KSMCHSIZ) ,   
204. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"   
205. from x$ksmsp   
206. where KSMCHSIZ >= 4108   
207. and KSMCHCLS='free'   
208. group by KSMCHCLS, KSMCHIDX, 1000\*trunc(KSMCHSIZ/1000);  
209.   
210. SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,  
211. To\_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"  
212. FROM X$KSMSP GROUP BY KSMCHCLS;  
213.   
214. SELECT alloc\_type, alloc\_size, num\_objs\_flushed, object\_loaded  
215.   FROM (SELECT ksmlrcom alloc\_type,  
216.                ksmlrsiz alloc\_size,  
217.                ksmlrnum num\_objs\_flushed,  
218.                ksmlrhon object\_loaded,  
219.                RANK() OVER(ORDER BY ksmlrsiz DESC) AS order\_ranking  
220.           FROM x$ksmlru  
221.          WHERE inst\_id = USERENV('INSTANCE')  
222.            AND ksmlrsiz > 0)  
223.  WHERE order\_ranking  400)  
224.  WHERE order\_ranking  0  
225.            AND o.type LIKE 'JAVA%')  
226.  WHERE order\_ranking  0  
227.            AND o.type in  
228.                ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE'))  
229.  WHERE order\_ranking  0  
230.             AND o.type = 'CURSOR'  
231. )  
232. WHERE order\_ranking  
233.   
234.

set pages 1000 lines 120
     col name for a60
    col value for a30
     spool diagnosis1.lst
    
    SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
    
     SELECT substr(sql_text,1,90) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5
    GROUP BY substr(sql_text,1,90) HAVING count(*) > 30 ORDER BY 2;
    
    select * from v$sgastat where pool like 'shared%' order by bytes;
    
    select sa.sql_text,sa.version_count ,ss.* from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.ADDRESS and
    sa.version_count > 50 order by sa.version_count ;
    
    select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and
    nam.ksppinm like '%shared%' order by 1;
    
    col free_space for 999,999,999,999 head "TOTAL FREE"
    col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"
    col free_count for 999,999,999,999 head "COUNT"
    col reqeust_misses for 999,999,999,999 head "REQUEST|MISSES"
    col reqeust_failures for 999,999,999,999 head "REQUEST|FAILURES"
    col max_free_size for 999,999,999,999 head "LARGEST CHUNK"
    
    select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZE from v$shared_pool_reserved
    
    col Parameter format a25
    col "Session Value" format a15
    col "Instance Value" format a15
    
    select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
    from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
    where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
    ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size');
    
    set pagesize 100
    select * from v$sga_dynamic_components;
    
    col requests for 999,999,999
    col last_failure_size for 999,999,999 head "LAST FAILURE| SIZE "
    col last_miss_size for 999,999,999 head "LAST MISS|SIZE "
    col pct for 999 head "HIT|% "
    col request_failures for 999,999,999,999 head "FAILURES"
    select requests,
    decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size
    from v$shared_pool_reserved;
    
    select p.inst_id, p.free_space, p.avg_free_size, p.free_count,
      p.max_free_size, p.used_space, p.avg_used_size, p.used_count, p.max_used_size,
      s.requests, s.request_misses, s.last_miss_size, s.max_miss_size,
      s.request_failures, s.last_failure_size, s.aborted_request_threshold,
      s.aborted_requests, s.last_aborted_size
      from (select avg(x$ksmspr.inst_id) inst_id,
      sum(decode(ksmchcls,'R-free',ksmchsiz,0)) free_space,
      avg(decode(ksmchcls,'R-free',ksmchsiz,0)) avg_free_size,
      sum(decode(ksmchcls,'R-free',1,0)) free_count,
      max(decode(ksmchcls,'R-free',ksmchsiz,0)) max_free_size,
      sum(decode(ksmchcls,'R-free',0,ksmchsiz)) used_space,
      avg(decode(ksmchcls,'R-free',0,ksmchsiz)) avg_used_size,
      sum(decode(ksmchcls,'R-free',0,1)) used_count,
      max(decode(ksmchcls,'R-free',0,ksmchsiz)) max_used_size from x$ksmspr
      where ksmchcom not like '%reserved sto%') p,
        (select sum(kghlurcn) requests, sum(kghlurmi) request_misses,
        max(kghlurmz) last_miss_size, max(kghlurmx) max_miss_size,
        sum(kghlunfu) request_failures, max(kghlunfs) last_failure_size,
        max(kghlumxa) aborted_request_threshold, sum(kghlumer) aborted_requests,
        max(kghlumes) last_aborted_size from x$kghlu) s;
    
    SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
    To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
    FROM X$KSMSP GROUP BY KSMCHCLS;
    
    set pagesize 80
    set verify off
    set heading off
    set feedback off
    set termout off
    
    col sp_size     format          999,999,999 justify right
    col x_sp_used   format          999,999,999 justify right
    col sp_avail    format          999,999,999 justify right
    col sp_sz_pins format           999,999,999 justify right
    col sp_no_pins format           999,999 justify right
    col sp_no_obj format            999,999 justify right
    col sp_sz_obj format            999,999 justify right
    col sp_no_stmts format          999,999 justify right
    col sp_sz_kept_chks format      999,999,999 justify right
    col sp_no_kept_chks format      999,999 justify right
    
    col val2 new_val x_sp_size noprint
    select value val2
    from   v$parameter
    where  name='shared_pool_size'
    /
    col val2 new_val x_sp_used noprint
    col val3 new_val x_sp_no_stmts noprint
    select sum(sharable_mem+persistent_mem+runtime_mem) val2, count(*) val3
    from   v$sqlarea
    /
    col val2 new_val x_sp_no_obj noprint
    col val3 new_val x_sp_sz_obj noprint
    select decode(count(*),'',0,count(*)) val2,
           decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
    from v$db_object_cache
    where type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
    /
    col val2 new_val x_sp_avail noprint
    select sum(ksmchsiz) val2 from x$ksmsp where ksmchcls = 'free'
    /
    col val2 new_val x_sp_no_kept_chks noprint
    col val3 new_val x_sp_sz_kept_chks noprint
    select decode(count(*),'',0,count(*)) val2,
           decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
    from   v$db_object_cache
    where  kept='YES'
    /
    col val2 new_val x_sp_no_pins noprint
    select count(*) val2
    from v$session a, v$sqltext b
    where a.sql_address||a.sql_hash_value = b.address||b.hash_value
    /
    col val2 new_val x_sp_sz_pins noprint
    select sum(sharable_mem+persistent_mem+runtime_mem) val2
    from   v$session a,
           v$sqltext b,
           v$sqlarea c
    where  a.sql_address||a.sql_hash_value = b.address||b.hash_value and
           b.address||b.hash_value = c.address||c.hash_value
    /
    
    set termout on
    set heading off
    
    ttitle -
      center  'Shared Pool''s Library Cache Information'  skip 2
    select  'Size                                    : '
                    ||&x_sp_size sp_size,
            'Number of shared cursors                : '
                    ||&x_sp_no_stmts sp_no_stmts,
            'Used by shared cursors                  : '
                    ||&x_sp_used,
            'Number of programmatic constructs       : '
                    ||&x_sp_no_obj sp_no_obj,
            'Used by programmatic constructs         : '
                    ||&x_sp_sz_obj sp_sz_obj,
            'Available                               : '
                    ||&x_sp_avail sp_avail,
            'Kept object chunks                      : '
                    ||&x_sp_no_kept_chks sp_no_kept_chks,
            'Kept object chunks size                 : '
                    ||&x_sp_sz_kept_chks sp_sz_kept_chks,
            'Pinned statements                       : '
                    ||&x_sp_no_pins sp_no_pins,
            'Pinned statements size                  : '
                    ||&x_sp_sz_pins sp_sz_pins
    from    dual
    /
    
    ttitle off
    set heading on
    set feedback on
    
    set pages 1000 lines 120
     col name for a60
    col value for a30
    
    select * from v$sgastat where pool like 'shared%' and name='free memory';
    
    select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From", 
    count(*) "Count" , max(KSMCHSIZ) "Biggest", 
    trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 
    from x$ksmsp 
    where KSMCHSIZ<140 
    and KSMCHCLS='free' 
    group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) 
    UNION ALL 
    select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) , 
    count(*) , max(KSMCHSIZ) , 
    trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 
    from x$ksmsp 
    where KSMCHSIZ between 140 and 267 
    and KSMCHCLS='free' 
    group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) 
    UNION ALL
    select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) , 
    count(*) , max(KSMCHSIZ) , 
    trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 
    from x$ksmsp 
    where KSMCHSIZ between 268 and 523 
    and KSMCHCLS='free' 
    group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) 
    UNION ALL 
    select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) , 
    count(*) , max(KSMCHSIZ) , 
    trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 
    from x$ksmsp 
    where KSMCHSIZ between 524 and 4107 
    and KSMCHCLS='free' 
    group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) 
    UNION ALL 
    select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) , 
    count(*) , max(KSMCHSIZ) , 
    trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" 
    from x$ksmsp 
    where KSMCHSIZ >= 4108 
    and KSMCHCLS='free' 
    group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
    
    SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
    To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
    FROM X$KSMSP GROUP BY KSMCHCLS;
    
    SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
      FROM (SELECT ksmlrcom alloc_type,
                   ksmlrsiz alloc_size,
                   ksmlrnum num_objs_flushed,
                   ksmlrhon object_loaded,
                   RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
              FROM x$ksmlru
             WHERE inst_id = USERENV('INSTANCE')
               AND ksmlrsiz > 0)
     WHERE order_ranking  400)
     WHERE order_ranking  0
               AND o.type LIKE 'JAVA%')
     WHERE order_ranking  0
               AND o.type in
                   ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE'))
     WHERE order_ranking  0
                AND o.type = 'CURSOR'
    )
    WHERE order_ranking

> <table> 
>  <tbody> 
>   <tr> 
>    <td><strong>NB</strong></td> 
>    <td><strong>Bug</strong></td> 
>    <td><strong>Fixed</strong></td> 
>    <td><strong>Description</strong></td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>16816103</td> 
>    <td>11.2.0.4, 12.1.0.1.1, 12.1.0.2, 12.2.0.0</td> 
>    <td>XIN aborts with ORA-4031 due to memory leak in OCIXStreamInCommit</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>16710753</td> 
>    <td>11.2.0.4, 12.1.0.2, 12.2.0.0</td> 
>    <td>Excess shared pool memory use / ORA-4031 from DROP EDITION</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>16571785</td> 
>    <td>11.2.0.4, 12.1.0.2, 12.2.0.0</td> 
>    <td>High “KTC latch subh” SGA memory use due to “lobs commit callback”</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13951456</td> 
>    <td>12.1.0.2, 12.2.0.0</td> 
>    <td>ASMCMD does not use bind variables in its SQL – affects shared pool</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>16009158</td> 
>    <td>11.2.0.3.BP19, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 / excess shared pool memory using Securefiles</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>15931756</td> 
>    <td>11.2.0.3.BP21, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8673737</td> 
>    <td>11.2.0.4, 12.1.0.1</td> 
>    <td>Diagnostic enhancement to summarize sub-pool allocations in ORA-4031 trace files</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>14053298</td> 
>    <td>11.1.0.6</td> 
>    <td>“kksss-heap” memory leak from PMON cleanup (ORA-4031) when audit enabled</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>15881004</td> 
>    <td>11.2.0.4, 12.1.0.1</td> 
>    <td>Excessive Memory usage with Extended Cursor Sharing</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>14292825</td> 
>    <td>11.2.0.3.BP10, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 in ASM as default memory parameters values for 11.2 ASM instances low</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>14029050</td> 
>    <td>11.2.0.2.BP18, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1</td> 
>    <td>MMAN failing to donate granules despite unused PGA (possible ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>14003090</td> 
>    <td>11.2.0.3.8, 11.2.0.3.BP20, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 with high memory utilization in the ‘init_heap_kfsg’ subheap / “ASM map headers” under ‘init_heap_kfsg’</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13814739</td> 
>    <td>11.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1</td> 
>    <td>Excessive KQR X PO” allocations in a RAC environment (can cause ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13536971</td> 
>    <td>12.1.0.1</td> 
>    <td>ORA-4031 with large partition tables – superseded</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13456573</td> 
>    <td>11.2.0.4, 12.1.0.1</td> 
>    <td>Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13430938</td> 
>    <td>11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 with high “KTC latch subh” memory usage</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13255388</td> 
>    <td>11.2.0.4, 12.1.0.1</td> 
>    <td>Slow parsing of large number of subqueries eligible for coalescing.</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13250244</td> 
>    <td>11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3.4, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1</td> 
>    <td>Shared pool leak of “KGLHD” memory when using multiple subpools</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13072654</td> 
>    <td>11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1</td> 
>    <td>Unnecessary ORA-4031 for “large pool”,”PX msg pool” from PQ slaves</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>12387969</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>get_geometry() does not use bind variables internally leading to shared pool fragmentation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>11782790</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>Excess CPU &amp; memory use / ORA-4030 / ORA-4031 parsing deeply nested queries</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>11651810</td> 
>    <td>11.2.0.2.3, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1</td> 
>    <td>ORA-4031 or Excess shared pool use can be seen by FileOpenBlock objects</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>11063191</td> 
>    <td>11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3.2, 11.2.0.3.BP04, 11.2.0.4, 12.1.0.1</td> 
>    <td>ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10634096</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>excessive memory usage with grouping sets and temp table transformation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10359631</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>High elapsed time and shared pool usage during parse with virtual columns</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10325953</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>ORA-600[733] / ORA-4031 allocating memory for hash join – superceded</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10308906</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>ORA-4031 using structured binary XMLIndex</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10089333</td> 
>    <td>11.2.0.2.6, 11.2.0.2.BP15, 11.2.0.3, 12.1.0.1</td> 
>    <td>“init_heap_kfsg” memory leaks in SGA of db instance using ASM</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10082277</td> 
>    <td>11.2.0.1.BP12, 11.2.0.2.3, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1</td> 
>    <td>Excessive allocation in PCUR or KGLH0 heap of “kkscsAddChildNo” (ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10042937</td> 
>    <td>11.2.0.3, 12.1.0.1</td> 
>    <td>High memory group in ges_cache_ress and ORA-4031 errors in RAC</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9578670</td> 
>    <td>11.2.0.1.BP11, 11.2.0.2.5, 11.2.0.2.BP09, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1</td> 
>    <td>ORA-4031 from frequent Partition Maintenance Operation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9403026</td> 
>    <td>11.2.0.2, 12.1.0.1</td> 
>    <td>Startup or PQ can report ORA-4031 errors requesting large “PX msg pool” chunks of memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9320130</td> 
>    <td>10.2.0.5.3, 11.2.0.2, 12.1.0.1</td> 
>    <td>ORA-4031 for “temporary tabl” using temporary table transformations (STAR / WITH)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9015983</td> 
>    <td>11.2.0.1.1, 11.2.0.2, 12.1.0.1</td> 
>    <td>ORA-4031 for “temporary tabl” on query with star transformation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8934457</td> 
>    <td>11.2.0.2, 12.1.0.1</td> 
>    <td>ORA-4031 during EXPLAIN PLAN or ORA-4030 during SELECT</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8835591</td> 
>    <td>11.2.0.2, 12.1.0.1</td> 
>    <td>Excess memory use parsing invalid SQL with COUNT() in subquery select list</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8813366</td> 
>    <td>11.1.0.7.3, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1</td> 
>    <td>ORA-4031 due to over large granule size</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8537544</td> 
>    <td>11.2.0.2, 12.1.0.1</td> 
>    <td>Excess memory use / ORA-600 / dumps parsing SQL with many nested outer joins / ANSI joins</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7509451</td> 
>    <td>11.2.0.3.7, 11.2.0.3.BP19, 11.2.0.4, 12.1.0.1</td> 
>    <td>SGA memory leak possible for “KSN WaitID” memory when using shared servers</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13548390</td> 
>    <td>11.2.1.8.8</td> 
>    <td>Error 4031 in kgttusec – plsql memory &gt; 1g unreliable</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9026008</td> 
>    <td>11.2.0.1.BP02, 11.2.0.2, 12.1.0.1</td> 
>    <td>“GES_RESOURCES” may increase over time with high enqueue activity</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>9058900</td> 
>    <td>11.2.0.1.BP10, 11.2.0.2</td> 
>    <td>OERI [kcbi_get_bhs_4] / “Shared IO Pool” takes too much SGA memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8599477</td> 
>    <td>11.1.0.7.3, 11.2.0.1</td> 
>    <td>Large “kcbi io desc” shared pool allocations can lead to ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8528171</td> 
>    <td>10.2.0.5.6, 11.1.0.7.10, 11.2.0.1</td> 
>    <td>High shared pool use due to frequent ADD/EXCHANGE partition operations</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8371884</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>Dumps/Errors/Excessive Parse Time and Memory allocation with Star transformation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8244734</td> 
>    <td>10.2.0.5, 11.1.0.7.8, 11.2.0.1</td> 
>    <td>NUMA Pool misconfigured at startup (ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>8221425</td> 
>    <td>11.1.0.7.2, 11.2.0.1</td> 
>    <td>ORA-4031 with kgs-heap overusing one subpool</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7462463</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>R-tree Index based spatial query on large table fails with ORA-4031 / ORA-7445 [kghualloc]</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7443808</td> 
>    <td>11.2.0.1</td> 
>    <td>Excessive Shared Memory utilization during Query Parsing</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7340448</td> 
>    <td>10.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1</td> 
>    <td>Excess shared pool memory use / ORA-4031 from REGEXP_LIKE – superceded</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7250182</td> 
>    <td>11.2.0.1</td> 
>    <td>Excess shared pool memory for AQ (“kwqiccns: notification st” chunks)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7207921</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>State object leak / ORA-4031 from stale guess DBAs on secondary IOT index</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7031622</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>Spin on kkojnp / ORA-600 [15160] with Star transformation</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6868080</td> 
>    <td>10.2.0.5, 11.1.0.7, 11.2.0.1</td> 
>    <td>ORA-4031 with NUMA</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6858062</td> 
>    <td>10.2.0.4.1, 10.2.0.5, 11.1.0.7.5, 11.2.0.1</td> 
>    <td>Shared pool memory leak when services created / deleted</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6800507</td> 
>    <td>10.2.0.4.1, 10.2.0.5, 11.1.0.7, 11.2.0.1</td> 
>    <td>Shared pool memory leak (“ksws service *” memory) using EXPDP often</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6730567</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>ORA-4031 / OERI [17137] with NUMA optimization enabled</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6624011</td> 
>    <td>11.1.0.7, 11.2.0.1</td> 
>    <td>ORA-4030 / ORA-4031 on startup with &gt; 4Gb SGA_TARGET</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6530861</td> 
>    <td>10.2.0.5, 11.2.0.1</td> 
>    <td>ORA-4301 when using stored Java</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6334552</td> 
>    <td>10.2.0.4, 11.1.0.7, 11.2.0.1</td> 
>    <td>Hang / ORA-4031 / OERI:kfrcsoDelete_3 on rollback of ASM file resize</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6271590</td> 
>    <td>10.2.0.4.2, 10.2.0.5, 11.1.0.7, 11.2.0.1</td> 
>    <td>SGA subheap imbalance with lots of free memory in a few subheaps</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6086162</td> 
>    <td>11.2.0.1</td> 
>    <td>Slow parsing / Excessive Memory Usage with Star Transformation and Query Rewrite</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6126415</td> 
>    <td>11.1.0.7</td> 
>    <td>OERI [kjccgmb:1] during shutdown instance</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6011182</td> 
>    <td>10.2.0.4, 11.1.0.7</td> 
>    <td>Parsing of large query takes long time / memory leak / ORA-4030 /4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5386986</td> 
>    <td>10.2.0.4, 11.1.0.7</td> 
>    <td>Leak / ORA-4031 leak when DROP UNUSED COLUMN issued on large partitioned table</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>13888380</td> 
>    <td>&nbsp;</td> 
>    <td>ORA-4031 in DB instance when using ASM due to high use of “init_heap_kfsg” memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>10363436</td> 
>    <td>&nbsp;</td> 
>    <td>Memory leak / ORA-4031 using TDE column encryption with PKI keys</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>12808696</td> 
>    <td>11.1.0.6</td> 
>    <td>Shared pool memory leak of “hng: All sessi” memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>7306915</td> 
>    <td>10.2.0.4.4, 10.2.0.5, 11.1.0.6</td> 
>    <td>EXCHANGE PARTITION leaks “KGL handles” shared pool memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6043052</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>Leak in perm allocations with “library cache” comments (ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6001617</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>LCK may accumulate CPU time / ORA-4031 possible</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5950708</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>‘gcs resources’ and ‘gcs shadows’ are imbalanced across shared pool subpools</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5618049</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>“mvobj part des” leaked memory after partition DDL (ORA-4031)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5573238</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5548510</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>_FIX_CONTROL parameter leaks memory in the shared pool</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5548389</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>Library cache allocation for ‘column mapping’ not using uniform sized extents</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5508505</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>ORA-4031 while shared heap still has unused reserved extents</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5479172</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>ORA-4031 with multiple partially-allocated permanent chunks</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5377973</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>Register schema fails with ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>5240264</td> 
>    <td>11.1.0.6</td> 
>    <td>ORA-4030 / ORA-4031 / Excessive Memory Usage with view merging and large IN LISTs.</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>4994956</td> 
>    <td>10.2.0.3, 11.1.0.6</td> 
>    <td>Number of configured shared pool subpools not correct</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>4467058</td> 
>    <td>10.2.0.4, 11.1.0.6</td> 
>    <td>IO requests can flush the pool / signal a hidden ORA-4031 error</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3519807</td> 
>    <td>9.2.0.8, 10.1.0.5, 10.2.0.2, 11.1.0.6</td> 
>    <td>ORA-4031 querying V$SEGSTAT</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3291723</td> 
>    <td>10.2.0.3, 11.1.0.6</td> 
>    <td>DBWR may crash instance if shared pool memory low</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>6347725</td> 
>    <td>&nbsp;</td> 
>    <td>“obj stat mem” leak when global temporary tables used</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>4195175</td> 
>    <td>9.2.0.7, 10.1.0.5, 10.2.0.1</td> 
>    <td>Create MVIEW with large WHERE clause may dump / ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>4184298</td> 
>    <td>10.1.0.5, 10.2.0.1</td> 
>    <td>Subpool imbalance for “session parameters” can lead to ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3948384</td> 
>    <td>9.2.0.7, 10.1.0.4, 10.2.0.1</td> 
>    <td>ORA-4031 possible using LGWR ASYNC for archiving</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3513427</td> 
>    <td>10.1.0.3, 10.2.0.1</td> 
>    <td>ORA-4031 can cause many SGA heapdumps to occur causing shared pool latch contention</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3419396</td> 
>    <td>10.2.0.1</td> 
>    <td>Restore spfile with dummy instance from ASM fails with RMAN-20001 / ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3405237</td> 
>    <td>9.2.0.6, 10.1.0.3, 10.2.0.1</td> 
>    <td>ORA-4031 / excessing SGA consumption due to large IN LIST and NOT IN list</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3356492</td> 
>    <td>10.2.0.1</td> 
>    <td>DBMS_LOB.LOADFROMFILE leaks library cache locks / shared pool “perm” memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3299407</td> 
>    <td>9.2.0.6, 10.1.0.3, 10.2.0.1</td> 
>    <td>Invalidating cursors frequently can fragment the SGA and leak “kglau” memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3046725</td> 
>    <td>10.2.0.1</td> 
>    <td>ORA-4031 due to shared_pool fragmented with high ges resources &amp; enqueues</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2953931</td> 
>    <td>9.2.0.4, 10.2.0.1</td> 
>    <td>ORA-4031 parsing query with numerous groupings</td> 
>   </tr> 
>   <tr> 
>    <td>*</td> 
>    <td>4658188</td> 
>    <td>9.2.0.8</td> 
>    <td>Long waits for “ksu process alloc latch yield”</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>4231921</td> 
>    <td>9.2.0.8, 10.1.0.2</td> 
>    <td>Truncate partition tables leaks SGA memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3232401</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>ORA-4031 with Streams apply site</td> 
>   </tr> 
>   <tr> 
>    <td>+</td> 
>    <td>3150705</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>Fix to help reduce the chances of an ORA-4031 with high OPEN_CURSORS</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3150680</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>Excessive shared pool permanent memory use / ORA-4031 possible with SESSION_CACHED_CURSORS</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3083818</td> 
>    <td>10.1.0.2</td> 
>    <td>Reloaded cursors can leak shared pool memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>3077651</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>LOB manipulation can leak “buffer” state objects causing hangs/shared pool problems</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2846138</td> 
>    <td>10.1.0.2</td> 
>    <td>Dump (under opiosq0) from SVRMGRL sessions when shared pool is full</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2817728</td> 
>    <td>9.2.0.4, 10.1.0.2</td> 
>    <td>Shared pool memory leak / ORA-4031 when collecting segment statistics</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2804724</td> 
>    <td>9.2.0.4, 10.1.0.2</td> 
>    <td>Out of memory in JAVA POOL shows an ORA-4031 “shared pool” message</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2798851</td> 
>    <td>9.2.0.4, 10.1.0.2</td> 
>    <td>ORA-4031 possible from SMON during SHUTDOWN or STARTUP</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2793038</td> 
>    <td>9.2.0.4, 10.1.0.2</td> 
>    <td>Session may die with ORA-4031 if shared pool exhausted when storing object level statistics</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2786968</td> 
>    <td>9.2.0.4, 10.1.0.2</td> 
>    <td>Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2607029</td> 
>    <td>9.2.0.3, 10.1.0.2</td> 
>    <td>High memory use optimizing SQL with functional indexes and many expressions</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2586762</td> 
>    <td>9.2.0.3, 10.1.0.2</td> 
>    <td>Direct LOB reads can result in ORA-4031 / excessive shared pool usage</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2563301</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>ORA-4031 possible in RAC environment under load</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2561115</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>SGA memory growth using outbound TCP/IP database links from shared servers</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2523151</td> 
>    <td>9.2.0.3, 10.1.0.2</td> 
>    <td>ORA-7445[updexe] with AUDIT_TRAIL=DB with stressed shared pool</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2501591</td> 
>    <td>9.2.0.3, 10.1.0.2</td> 
>    <td>ORA-928 / ORA-4031 on “ALTER VIEW &lt;sys_view&gt; COMPILE” if REMOTE objects exist with same name</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2486954</td> 
>    <td>9.2.0.3, 10.1.0.2</td> 
>    <td>ORA-4031 / Dump from concurrent DGURIServlet access to SQLX XMLType view</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2351854</td> 
>    <td>9.0.1.4, 9.2.0.2, 10.1.0.2</td> 
>    <td>Dispatchers may hang if ORA-4031 occurs</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1782381</td> 
>    <td>9.2.0.5, 10.1.0.2</td> 
>    <td>Excessive SGA use / ORA-4031 possible with large number of JOBS</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2805335</td> 
>    <td>9.2.0.4</td> 
>    <td>Excessive shared pool memory use for parse of certain statments using functional indexes</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2442042</td> 
>    <td>9.2.0.5</td> 
>    <td>ORA-4031 possible using STREAMS on multi-cpu machine</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2472541</td> 
>    <td>9.2.0.2</td> 
>    <td>PMON ORA-7445 [kghxfr] after shared server gets ORA-4031</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2244789</td> 
>    <td>9.0.1.4, 9.2.0.1</td> 
>    <td>ORA-4031 / excessive shared pool usage from query with full outer join / union</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2244642</td> 
>    <td>9.0.1.4, 9.2.0.1</td> 
>    <td>ALTER VIEW operations may encounter ORA-4031 errors</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2208570</td> 
>    <td>8.1.7.4, 9.0.1.4, 9.2.0.1</td> 
>    <td>ORA-4030 / ORA-4031 / spin during query optimization with STAR TRANSFORMATION and unmergable view</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2185294</td> 
>    <td>9.2.0.1</td> 
>    <td>Shared server may dump (koklhfr) after ORA-4031 in “LARGE POOL”</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2167326</td> 
>    <td>9.0.1.3, 9.2.0.1</td> 
>    <td>ORA-4031 possible during patch set upgrade scripts</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2143637</td> 
>    <td>9.0.1.3, 9.2.0.1</td> 
>    <td>ORA-4031 possible starting the cache advisory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2129178</td> 
>    <td>8.1.7.4, 9.0.1.3, 9.2.0.1</td> 
>    <td>Using NULL or an empty string (”) in an INLIST can cause ORA-4031 / waste shared pool memory</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>2104071</td> 
>    <td>8.1.7.4, 9.0.1.3, 9.2.0.1</td> 
>    <td>ORA-4031 / excessive “miscellaneous” shared pool use possible (many PINS)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1865917</td> 
>    <td>8.1.7.3, 9.0.1.2, 9.2.0.1</td> 
>    <td>ORA-4031 / high CPU from OR expansion of negated predicates with RBO</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1333526</td> 
>    <td>8.1.7.3, 9.0.1.2, 9.2.0.1</td> 
>    <td>ORA-4031 / Poor performance using persistent Object data types</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1642964</td> 
>    <td>9.0.1.0</td> 
>    <td>ORA-4031 / sga leak from IOT as inner table in NESTED LOOP</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1640583</td> 
>    <td>8.1.7.1, 9.0.1.0</td> 
>    <td>ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access</td> 
>   </tr> 
>   <tr> 
>    <td>+</td> 
>    <td>1397603</td> 
>    <td>8.1.7.2, 9.0.1.0</td> 
>    <td>ORA-4031 / SGA memory leak of PERMANENT memory for buffer handles</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1396675</td> 
>    <td>8.1.7.2, 9.0.1.0</td> 
>    <td>PLSQL may hang/spin/ORA-4031 with SQL WHERE (a,b) in ( (c1,d1),(c2,d2)… )</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1340718</td> 
>    <td>8.1.7.2, 9.0.1.0</td> 
>    <td>OERI:KCBSTC_PARENT_AB possible in CKPT following an ORA-4031 error</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1348501</td> 
>    <td>8.1.6.3, 8.1.7.0</td> 
>    <td>MV refresh unnecessarily invalidates shared cursors</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1328709</td> 
>    <td>8.0.6.2, 8.1.6.3, 8.1.7.0</td> 
>    <td>OERI:15428 can be raised when ORA-4031 is hit internally</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1256011</td> 
>    <td>8.0.6.2, 8.1.6.2, 8.1.7.0</td> 
>    <td>MTS servers may OERI:1113 after an ORA-4031 (OERI:730 on 8i)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>1092621</td> 
>    <td>8.1.6.2, 8.1.7.0</td> 
>    <td>ORA-4031 when DROPPING a PARTITION</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>898798</td> 
>    <td>8.1.5.1, 8.1.6.0</td> 
>    <td>ORA-4031 on startup from large DB_BLOCK_BUFFERS (or GC_RELEASABLE_LOCKS)</td> 
>   </tr> 
>   <tr> 
>    <td>&nbsp;</td> 
>    <td>431303</td> 
>    <td>7.3.3.1, 7.3.4.0</td> 
>    <td>Startup gives ORA-4031 with GC_DB_LOCKS=157K</td> 
>   </tr> 
>  </tbody> 
> </table>

===================================================================================================================================

ANSWER IN CHINESE  
======  
1.SGA 里的内池包含不同大小的内存块。当数据库启动时，就有一个大的内存块分配并被hush buckets 里的空闲列表追踪。  
随着时间推移，随着内存的分配和释放。内存块被按照大小在不同的hush buckets间移动。当SGA里任何一个内存池里出现  
不能满足内部分配请求的情况时，ORA-04031就出现了。  
  
共享池的管理方式不同于其它的内存池。共享池存放与数据字典和library cache有关的信息。但是，这些  
内存区域根据空闲列表和最近使用算法(LRU)管理的。当在共享池的所有搜索结束后，从LRU列表清除所有  
的可能清除的对象， 多次扫描空闲列表后，仍没有找到内存块，ORA-04031就出现了。这意味着ORA-04031很难预测。  
ORA-04031有很多种可能原因，错误产生时间的跟踪信息提供的是内存情况的"受害会话", 不是原因。  
metalink 文档Note 396940.1 可以提供详细信息。  
  
  
2. 对共享池的监测，可以看它是否包含许多类似的SQL,只有文字不同。 这种情况会占用更多的共享池内存  
并引起碎片。这两种情况都会引起ORA-4031错误。使用绑定变量可以使SQL 共享。使用以下的命令可以查出  
内存中是否有许多类似SQL。  
  
即使使用了绑定变量后，仍然可能存在高version count（子指针）的情况。为了使子指针共享，CURSOR\_SHARING  
参数需要调整。  
metalink 文档Note 296377.1 和 261020.1可以提供详细信息。  
  
3. 造成4031的原因不是bug或产品缺陷。 是需要根据要求来调整一些参数来控制共享池的使用。

===================================================================================================================================

**\[sql\]** [view plain][] [copy][view plain]

1.  comm diag script:  
2.    
3.  1. Upload the output for:  
4.  > opatch lsinventory -detail  
5.    
6.  2. Please upload following trace and incidents files associated with occurrences of 4031 errors.  
7.    
8.    
9.  3. Upload the initialization parameter file for this database.  
10.   
11. 4. How many times the error has been reported? Is it one-time or many ?  
12.   
13. 5. Does the error happen around the same time?  
14.   
15. 6. What has changed? Any parameter changed?  
16.   
17. 7. Has the application changed or the workload increased?  
18.   
19. 8. Kindly describe your environment.Is it RAC or standalone environment? Is ASM used ?  
20.   
21. 9. what actions have been done during the occurrence of errors?  
22.   
23. 10. Are you able to reproduce this issue at will?  
24.   
25. 11. Provide the output for :  
26. connect user/password  
27. select sql\_id, sql\_text, version\_count from v$sqlstats  
28. where version\_count > 5  
29. order by version\_count desc;  
30.   
31. 12. Provide the output for :  
32. connect user/password  
33. select \* from v$sga\_dynamic\_components;  
34. select \* from V$MEMORY\_DYNAMIC\_COMPONENTS;  
35.   
36. select \* from V$SHARED\_POOL\_RESERVED;  
37.   
38. select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val  
39. where nam.indx = val.indx and nam.ksppinm like '%shared%' order by 1;  
40.   
41. select a.ksppinm "Parameter",  
42. b.ksppstvl "Session Value",  
43. c.ksppstvl "Instance Value"  
44. from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c  
45. where a.indx = b.indx and a.indx = c.indx  
46. and a.ksppinm like '%kghdsidx%';  
47.   
48. select PQ\_SLAVE\_MISMATCH , PX\_MISMATCH from V$SQL\_SHARED\_CURSOR;  
49.   
50. set linesize 90  
51. set pagesize 60  
52. column component format a25  
53. column Final format 99,999,999,999  
54. column STARTED format A25  
55.   
56. SELECT COMPONENT ,OPER\_TYPE,FINAL\_SIZE Final,to\_char(start\_time,'dd-mon hh24:mi:ss') STARTED FROM V$SGA\_RESIZE\_OPS;  
57.   
58. SELECT COMPONENT,OPER\_TYPE,COUNT(1) FROM V$SGA\_RESIZE\_OPS GROUP BY COMPONENT,OPER\_TYPE;  
59.   
60. SELECT A.BEGIN\_INTERVAL\_TIME,A.END\_INTERVAL\_TIME, B.BYTES  
61. FROM WRM$\_SNAPSHOT A, DBA\_HIST\_SGASTAT B  
62. WHERE A.SNAP\_ID = B.SNAP\_ID  
63. AND B.POOL = 'SHARED POOL' AND B.NAME = 'KGH: NO ACCESS' ORDER BY 1;  
64.   
65. show parameter session\_cached\_cursors;

===================================================================================================================================

1) by checking the share pool usage  
  
  
Shared Pool Usage  
===========================================================  
R-free - Reserved List  
R-freea - Reserved List  
free - Free Memory  
freeabl - Memory for user / system processing  
perm - Memory allocated to the system  
recr - Memory for user / system processing  
  
what exactly perm is referring to ? is there any indication for showing the healthiness of the share pool by referring to this statistics ?  
we found that , every time there was a ora-4031 occur, the value of perm with a high value.  
  
  
2) doc 146599.1, is it useful to to monitor X$KSMSP View , is it Similar to Heapdump Information ?  
  
  
3) doc 146599.1, is it Useful to track allocations in the shared pool that cause other objects in the shared pool to be aged?  
SELECT \* FROM X$KSMLRU WHERE ksmlrsiz > 0;  
  
4)Is REQUEST\_MISSES valid when init para SHARED\_POOL\_RESERVED\_MIN\_ALLOC not set?  
  
  
  
1.) PERM allocations refer to PERMANENT allocations. Permanent allocations are never freed. They live till lifetime of instance. That is why those allocations keep on growing. For 10.2.0.4 are you using Automatic Shared Memory Management ?  
  
2.) It is similar to heapdump information but If you are using Automatic shared\_memory management it may not give exact information.  
  
3.) It is useful in case you are seeing lots of invalidations/reloads. Query v$librarycache to get that information.  
  
4.) By default, \_shared\_pool\_reserved\_min\_alloc is set to 4400 bytes. So, request misses are valid in that case as well.  
  
Please note that frequently querying x$ views is not recommended. Queries on these views are lock intensive and may impact performance.  
  
a.) Permanent allocations cannot be flushed by using 'alter system flush shared\_pool' command. Permanent allocations get released only after instance restart. There is no specified ratio/percentage of permanent chunks in the shared\_pool space.  
  
b.) The first point to start is  
  
1. Monitor v$sgastat. Check for any component that is growing abnormally. For example, if 'KGH: NO ACCESS' component has grown abnormally, you can check in metalink for known bugs. In case the 'SQL area' is growing abnormally, it may be a cause of concern as application may not be bind variables. Then taking heapdump may reveal if that is cause of fragmentation.  
  
2. Querying v$librarycache is of help to understand the HIT ratio, reloads and invalidations that have happened since instance startup.  
  
3. In severe cases or for diagnostics, heapdumps may be taken along with errorstack.  
  
'sql area' is a component that can be seen as a part of output from v$sgastat.  
Suppose we have shared\_pool sized to 500M and as the time passes. As the time progresses, in case you see this component growing to 200-250M, it is likely that application is not using bind variables. To check if application is using bind variables or not, you can gather AWR reports and check sections 'sql ordered by version count' This section would give SQLs that didn't get shared due to some reasons. After that SR can be raised for more understanding.  
  
In case ORA-04031 error gets reported, you can refer the following document and upload the information to the SR for analysis.  
  
Article-ID: Note 430473.1  
Title: ORA-4031 Common Analysis/Diagnostic Scripts  
  
1.) Library cache overview gives the following:  
  
\*\*\* High level breakdown of memory \*\*\*  
  
sharable : 39.93M  
persistent : 32.11M  
runtime : 30.27M  
  
SQL Memory Usage (total) : 102.31M (80%)  
  
Shareable memory refers to memory that can be shared. Example: if an sql is using bind variables, it is likely that the parse tree for that statement would be shared with next session executing the same sql.  
  
Persistent memory is something similar to Permanent memory that would remain till lifetime.  
Execution/runtime memory refers to the memory that is required during execution of a cursor/sql  
  
There should be less number of SQLs that are executed just once. Because, if sql's are not shared, each SQL would be having its own execution plan and would get executed independently... in other words, multiple unshareable statements just one execution.  
On the other hand, if SQLs are shared, there would be one statement with multiple executions.  
  
2.) There should be less misses in the library cache. The more the hit percentage, more is the probability that a shareable sql is found in library cache. In case, we are not able to find the misses in library cache would increase and we would require reload of parse tree in the library cache.  
  
3.) Resize operations are done internally by oracle when using ASMM using feedback mechanisms from advisories. In case there are lots of resize operations and there are lots of shrinks and growth of components happenning in the SGA, you may need to set the values of those components to some value and as such those components would not shrink below the specified value.  
  
4.) Versions HWM: Gives The highest number of children that are present in the cache under a parent for an SQL statement, from the time when instance was last started.  
  
I believe that you are trying to set these warnings and alert using EM.  
Anyways, there is no rule of considering the permanent memory growth as Threshold and critical.  
You can set 60% as threshold and 75% as critical.  
  
Please note that 200M is very low for a production database to work. With 200M of shared\_pool size, It is highly likely to receive ORA-04031 in this case.  
Minimum size of shared\_pool for 10g database is recommended as 500M based on empirical analysis. In case the permanent chunks are increasing abnormally, we would consider taking a heapdump incase the problem is occurring again and again.  
  
When Automatic Shared Memory is in use, we have a concept of shared pool durations. In memory there is different lifetime for every memory data structure. Some data structures (memory chunks) remain till lifetime of instance (instance duration or permanent chunks) and some live just till the time session is there (session duration) and so forth. With ASMM enabled, permanent chunks don't scan LRU list to get memory. They directly contact granule manager for more memory. In case if granule manager is not able to release that memory, ORA-04031 gets reported.  
  
In such cases, we would consider disabling ASMM (setting sga\_target to zero and manually setting values of db\_cache\_size, shared\_pool\_size).  
  
In case you have disabled ASMM and still seeing abnormal growth of permanent chunks, we may have to take heapdump for analysis and check in case it is a memory leak/bug. However, to alleviate the problem, DB restart is the only option because, permanent chunks would not get released using 'alter system flush shared\_pool' command.  
  
Regarding RAC and shared\_pool\_size:  
  
RAC specific memory is allocated at the time of SGA creation, mostly in the shared pool. However, the memory does not count in the SHARED\_POOL\_SIZE as defined in the init.ora parameter file. Therefore, when migrating Oracle from single instance to RAC, you do not need to adjust the SHARED\_POOL\_SIZE parameter to accommodate the additional memory that RAC uses; Oracle does that automatically. Note that the memory for the KCL global cache lock elements will be allocated in the buffer cache, and not in the shared pool.  
  
The note: 455179.1 clearly talks about how to calculate number of subpools allocated at startup. What is your question ?  
  
Shared\_pool reserved is for large requests greater than or equal to 4400 bytes. So, any request that wants shared pool space of 4400 bytes or more would search normal shared\_pool space. In case it is not able to find the same sized chunk, only then it would go and scan the reserved pool for free chunks. So, only in case of large requests, shared\_pool\_reserved would come into picture.  
  
In the view v$shared\_pool\_reserved, we generally look at "REQUEST\_FAILURES" to check 'Number of times that no memory was found to satisfy a request.' In case this keeps on increasing, it may be a signal of impending ORA-04031 error.  
  
It is the same question that you asked earlier as well.  
  
Yes, the number of misses and number of objects executed once has to be monitored.  
How much is the threshold value for that is something that we cannot easily suggest. Ideally, misses should be zero. That means, whatever we are trying to find is already present in the library cache. As suggested earlier in the output of 'LibCacheOverview.sql' , Ideal percentages for 1 time executions is 20% or lower.  
  
The REQUEST\_MISSES you are referring to the view from v$shared\_pool\_reserved. That is within the reserved area. You may be seeing lots of misses in the normal shared\_pool area. Query v$librarycache to get the number of misses in library cache. And apart from Misses there are lots of other reasons to receive ORA-04031 error.  
  
There are lots of queries that may have different version counts. Suppose:  
  
SQL> select \* from emp, may have 4 different children.  
  
Another SQL may be having version count of 12. So, if 12 is the highest version count for the instance, Version HWM would report the highest version count for a sql in instance.  
  
Largest memory object refers to the max. size of object that was loaded in shared pool. In your case it is 408,084 bytes.  
  
REQUEST\_MISSES is related to RESERVED POOL. Reserved pool is meant for large allocations like PL/SQL compilations or trigger compilation. Only requests that are larger than 4400 bytes can go and check for free space in reserved pool.  
  
Please note that allocations below 4400 bytes would not go to reserved area. You may get ORA-04031 error while requesting for 64 bytes.  
So, in this case REQUEST\_MISSES parameter would not mean anything. It may be zero or any positive number. This is because a 64 bytes request would never go and check RESERVED POOL.

===================================================================================================================================

got BUG 9578670 - ORA-4031 WHILE DROP PARTITIONS so we need a patch for the bug  
Bug 9578670: ORA-4031 WHILE DROP PARTITIONS, SOLUTION FROM DOC ID: 419551.1 DIDN'T HELP  
Product Version 11.1.0.7  
Fixed in Product Version 12.1  
Fix By 11.2.0.3  
No backport available for 11.2.0.2  
  
REDISCOVERY INFORMATION:  
If you are running frequent partition maintenance operations, have multiple  
subpools in the shared pool, and encounter ORA-4031 out of memory errors due  
to many "mvobj part des" allocations, you could be hitting this issue.  
  
WORKAROUND:  
Set "\_kghdsidx\_count"=1.  
  
Alert file  
\----------  
  
Wed Feb 16 04:26:22 2011  
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_dw00\_13679.trc  (incident=36418):  
ORA-04031: unable to allocate 4128 bytes of shared memory ("shared pool","UPDATE\_CREATE","PRTMV^3a844b88","kkpomSort hashed kpn's")  
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir\_36418/jasper\_dw00\_13679\_i36418.trc  
Wed Feb 16 04:26:44 2011  
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_ora\_31713.trc  (incident=36578):  
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^edb30de5","kglHeapInitialize:temp")  
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir\_36578/jasper\_ora\_31713\_i36578.trc  
Wed Feb 16 04:26:47 2011  
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_dw0e\_6924.trc  (incident=36466):  
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^56d22d85","kglHeapInitialize:temp")  
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir\_36466/jasper\_dw0e\_6924\_i36466.trc  
Wed Feb 16 04:26:47 2011  
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_cjq0\_9684.trc  (incident=36362):  
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KGLH0^bd2af6d6","kglHeapInitialize:temp")  
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir\_36362/jasper\_cjq0\_9684\_i36362.trc  
Wed Feb 16 04:26:47 2011  
Errors in file /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_dw01\_3033.trc  (incident=36666):  
ORA-04031: unable to allocate 3952 bytes of shared memory ("shared pool","SELECT ","pacdHds\_kkpaco","kggec.c.kggfa")  
Incident details in: /u01/app/oracle/diag/rdbms/jasper/jasper/incident/incdir\_36666/jasper\_dw01\_3033\_i36666.trc  
Wed Feb 16 04:26:47 2011  
Dumping diagnostic data in directory=\[cdmp\_20110216042647\], requested by (instance=1, osid=31713), summary=\[incident=36578\].  
...  
  
Trace files  
\-----------  
Not provided  
  
Incident trace files  
\--------------------  
  
jasper\_dw01\_3033\_i36666.trc  
  
\*\*\* ACTION NAME:(SYS\_IMPORT\_TABLE\_03) 2011-02-16 04:26:47.535  
  
Dump continued from file: /u01/app/oracle/diag/rdbms/jasper/jasper/trace/jasper\_dw01\_3033.trc  
ORA-04031: unable to allocate 3952 bytes of shared memory ("shared pool","SELECT ","pacdHds\_kkpaco","kggec.c.kggfa")  
  
========= Dump for incident 36666 (ORA 4031) ========  
  
\*\*\* 2011-02-16 04:26:47.535  
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)  
\----- Current SQL Statement for this session (sql\_id=4ujz53z0x60rn) -----  
INSERT /\*+ SYS\_DL\_CURSOR \*/ INTO RELATIONAL("GDR"."AVG\_ARH" NOT XMLTYPE) PARTITION ("D110120\_22") ("TIMESTAMP","TEID","DST\_IP","DST\_PORT","SRC\_IP","SRC\_PORT","BYTES") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL)  
\----- PL/SQL Stack -----  
\----- PL/SQL Call Stack -----  
0x3614e5dd0        58  package body SYS.KUPD$DATA\_INT  
0x361a817d0      3443  package body SYS.KUPD$DATA  
0x3647ec9f0     14258  package body SYS.KUPW$WORKER  
0x3647ec9f0      4535  package body SYS.KUPW$WORKER  
0x3647ec9f0      9096  package body SYS.KUPW$WORKER  
0x3647ec9f0      1688  package body SYS.KUPW$WORKER  
  
\---> HEAP DUMP heap name="SQLA^c1d302f4"  desc=0x75dfc6b0  
             Type           Count             Sum         Average  
             ~~~~           ~~~~~             ~~~         ~~~~~~~  
         freeable              30           47528         1584.27  
         recreate               1            4056         4056.00  
             perm               1              80           80.00  
             free               1            1040         1040.00  
  
BreakDown  
~~~~~~~~~  
             Type           Count             Sum         Average Percent  
             ~~~~           ~~~~~             ~~~         ~~~~~~~ ~~~~~~~  
  TCHK^c1d302f4                12           48672         4056.00  92.35  
  perm                          1              80           80.00   0.15  
  Free(heap.awk)                1            1040         1040.00   1.97  
...  
Total = 52704 bytes           51.47k            0.05MB  
  
Descriptor 36de851e8 was the largest with size 48672  
This is 92.35 % of the total heap  
ds=0x36de851e8 has size    44616 (% of heap=84.65)  
Memory used by descriptors with more than 10240 bytes:  
Total = 44616 (% of heap=84.65)  
Total PERMANENT memory actually used is 80  
Largest CONTIGUOUS free memory (NORMAL)   was       1040 (     1.02k)  
Largest descriptor count was 12  
  
\---> HEAP DUMP heap name="KGLH0^c1d302f4"  desc=0x381acb728  
             Type           Count             Sum         Average  
             ~~~~           ~~~~~             ~~~         ~~~~~~~  
             perm               2            2648         1324.00  
             free               1             776          776.00  
         freeable               4             608          152.00  
  
BreakDown  
~~~~~~~~~  
             Type           Count             Sum         Average Percent  
             ~~~~           ~~~~~             ~~~         ~~~~~~~ ~~~~~~~  
  perm                          2            2648         1324.00  65.67  
  Free(heap.awk)                1             776          776.00  19.25  
  kgltbtab                      4             608          152.00  15.08  
  
Total = 4032 bytes            3.94k            0.00MB  
  
Total PERMANENT memory actually used is 2464  
Largest CONTIGUOUS free memory (NORMAL)   was        776 (     0.76k)  
  
Free List Bucket Summary :  
Bucket   0 \[size=0         \] Count=    2 Av.Size=       388 Max=       776  
  
Opatch  
\------  
There are no Interim patches installed in this Oracle Home.  
  
RDA  
\----  
db\_cache\_size                0  
shared\_pool\_size        0  
streams\_pool\_size 234881024  
memory\_target      13555990528  
  
Minimum and Maximum Component Final Size  
Component        Lowest        Low (MiB)  Highest High (MiB)  
DEFAULT buffer cache 1375731712 1312   2717908992 2592  
java pool 67108864 64 67108864 64  
large pool 33554432 32 67108864 64  
shared pool 2382364672 2272 2919235584 2784  
streams pool 234881024 224 301989888 288  
  
  
Trace files  
\-----------  
  
Trace file jasper\_dw01\_3033.trc not provided.  
  
jasper\_dw01\_3033\_i36666.trc  
  
\---> HEAP DUMP heap name="SQLA^c1d302f4"  desc=0x75dfc6b0  
  
BreakDown  
~~~~~~~~~  
              Type           Count             Sum         Average Percent  
              ~~~~           ~~~~~             ~~~         ~~~~~~~ ~~~~~~~  
   TCHK^c1d302f4                12           48672         4056.00  92.35  
   Free(heap.awk)                1            1040         1040.00   1.97  
  
Total = 52704 bytes           51.47k            0.05MB  
  
Descriptor 36de851e8 was the largest with size 48672  
This is 92.35 % of the total heap  
ds=0x36de851e8 has size    44616 (% of heap=84.65)  
Memory used by descriptors with more than 10240 bytes:  
Total = 44616 (% of heap=84.65)  
Total PERMANENT memory actually used is 80  
Largest CONTIGUOUS free memory (NORMAL)   was       1040 (     1.02k)  
Largest descriptor count was 12  
  
This does not include a complete heapdump.  
  
alter system set events ‘4031 trace name heapdump level 536870914’;  
reproduce the problem and provide the next generated trace files so that we can check the complete memory allocation when the problem is raised?

===================================================================================================================================

Primary Issue:  
Unexpectedly High Subpool Allocation in Shared Pool  
  
The issue could occur if in the "Memory Utilization of Subpool" sections of your the trace file, largest allocation size over all subpools in total is NOT one of the following expected names:  
  
"free memory",  
"sql area",  
"SQLA",  
"library cache",  
"KGH: NO ACCESS",  
"ges resource",  
"gcs resource",  
"ksfd.\*I/O",  
"\*db\_block\_buffers\*",  
"\*pcur\*",  
"\*ccur\*",  
  
Another condition that this issue could occur is that if "free memory" is the largest, check whether the second largest allocation name is NOT in the above list,  
  
Another condition that this issue could occur is that the top allocation is one of the following, and its allocation size is above 50% of the total subpool size:  
  
"sql area", "SQLA", "library cache", "\*pcur\*", "\*ccur\*".  
  
Certain areas of the Shared Pool tend to grow aggressively and stabilize. Areas associated with the Library Cache and cursor objects for example. In your case, Further investigation is required to diagnose the large memory allocations in your Shared Pool that do not match up with expected areas of growth, and it would provide better information for the cause of ORA-04031.  
By analyzing the uploaded file, we have found the following symptoms that may have caused the issue:  
  
    \*\* In your trace file, Top 5 Allocation in all subpools:  
    AllocationName........Size  
    KGLH0........58063984 bytes  
    private strands........33911808 bytes  
    event statistics per s........29735936 bytes  
    ksunfy : SSO free list........27718400 bytes  
    dbktb: trace buffer........24576000 bytes  
  
  
Recommended Solution  
  
1. Please create a new Service Request to further investigate the issue. Provide the following diagnostics with the new SR:  
  
a) Review NOTE:430473.1 - ORA-4031 Common Analysis/Diagnostic Scripts \[Video\]  
  
b) Look in the section of the article titled 'Software Requirements/Prerequisites'. You can download the zip file clicking on the 'Click here' link. Run the scripts below and provide the output for analysis  
  
TrendSGAstats.sql -- spools output to trends.out (run on 10g and 11g)  
ReservedAnalysis.sql -- spools output to reserved.out (runs on 8i and higher)  
  
2) Upload the diagnostics output from these scripts as well as an AWR report for the hour time frame prior to the ORA-4031 errors with your new Service Request.

[4031_diag_script.zip]: http://www.oracledatabase12g.com/wp-content/uploads/2011/06/4031_diag_script.zip
[view plain]: https://blog.csdn.net/sunraty/article/details/18019929#