| |||||
|
Memory Leak in 11.2.0.1 on AIX: Shared Cursors Are Not Behaving By: David Fitzjarrell
A "blast from the past" was recently updated by Oracle Support; it involves a subpool memory leak in Oracle 11.2.0.1 and shared cursors. Originally submitted in 2010 this issue was updated this year to reflect the status of 'Fixed in Product Version 12.1'. There have been several bug fixes to the 11.2.0 series of releases addressing this and similar issues, but I suspect Oracle Support waited for it's declaration of 'fixed' until 12.1.0.2 was released. Let's look at the symptoms and how you can investigate the shared pool subpools without generating a trace file. The issue finally becomes apparent when an ORA-04031 error is thrown: ORA-4031: unable to allocate ... bytes of shared memory ("shared pool","STANDARD","PLMCD^e93dbe75","BAMIMA: Bam Buffer") In the original submission a trace file for the 4031 error, generated by the database, was submitted which revealed the offending portion of subpool 2: Memory Utilization of Subpool 1 ================================ "free memory " 25488576 "SQLA " 134248904 "PCUR " 25094632 Memory Utilization of Subpool 2 ================================ "free memory " 127129872 "PCUR " 2427488432 Notice that the PCUR area in subpool 2 is almost 100 times the size of the same area in subpool 1. Unfortunately an AWR report shows only the Shared Pool Size, not a breakdown of the various subpools in the Shared Pool. Fortunately Tanel Poder has provided a tool to print out the various memory segments in the subpools called sgastatx.sql that can be used to see how much memory is allocated to each subpool by reporting on all of the memory areas of interest. Looking at an example of how the script is run: SQL> @sgastatx % -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (0 - Unused): 83886080 80 shared pool (1): 184549376 176 shared pool (Total): 268435456 256 -- Allocations matching "%": SUBPOOL NAME SUM(BYTES) MB ------------------------------ -------------------------- ---------- ---------- shared pool (0 - Unused): free memory 83886080 80 shared pool (1): free memory 41599128 39.67 SQLA 14559048 13.88 KGLH0 12547880 11.97 row cache 8641136 8.24 KGLS 7616536 7.26 db_block_hash_buckets 5836800 5.57 KGLSG 5267216 5.02 dbwriter coalesce buffer 4194816 4 KCB Table Scan Buffer 4194816 4 kglsim hash table bkts 4194304 4
Other Related Articles ... to read more DBA articles, visit http://dba.fyicenter.com/article/ |
||||