Growth of CCursor sub-heap of the shared pool
Date: Sun, 8 Dec 2013 16:09:26 -0800 (PST)
Message-ID: <135a1233-a707-4f69-a5bd-b9c98e7da22a_at_googlegroups.com>
Oracle 11.1.0.7 on Sun SPARC Solaris 10
We observe following:
- Growth of shared pool due to growth of CCursor sub-heap, mostly in one sub-pool (ouf of 4). In 12 hours size of CCursor increased from 130 MB to 500 MB.
- V$SQL shows that some SQL statements experience growth of sharable memory (V$SQL.SHARABLE_MEM) while number of versions remains constant and small (1 - 2) (V$SQL.LOADED_VERSIONS, V$SQL.OPEN_VERSIONS). The SQL statements are simple.
- Flushing shared pool doesn't help
History of the issue:
- the application was implemented several years ago and was running fine. It executes simple SQL statements, most accessing just one table, nothing fancy - no LOBs,
no LONG columns, no nested tables, etc.
- A few weeks ago the database started reporting ORA-04031 and in about 20 min it became very slow and had to be restarted
- Trace files were showing that subpool #3 didn't look normal
Subpool#1:
- free memory: 35 MB
- sql area: 13 MB
- CCursor: 13 MB
Subpool#2:
- free memory: 42 MB
- sql area: 14 MB
- CCursor: 170 MB
Subpool#3:
- free memory: 375 MB
- sql area: 40KB (40 KB, not MB)
- CCursor: -1.2 GB (negative value)
Subpool#4:
- free memory: 43 MB
- sql area: 14 MB
- CCursor: 10 MB
In my experience size of 'free memory' is misleading, it is size of 'sql area' that matters, if it falls below certain threshold the database starts reporting ORA-04031.
Looking at size of CCursor over time (from STATS$SGASTAT) we noticed that it was stable at approx 100 MB, two weeks before first ORA-04031 it started growing and has grown to 2 GB. Looking at STATS$SQL_SUMMARY we noticed that sum(loaded_versions) was approx 1,500 and remained stable; sum(version_count) was also stable at 2,000. However sum(sharable_mem) experienced linear growth from 200 MB to 2 GB.
This behaviour has been reported earlier (bugs 10399563, 11056048), however there is no patch/workaround.
We have taken two heapdumps and run heapdump analyzer:
- Heapdump Analyzer v1.
Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason
- ------- ------------ ----------------- ----------------- ----------------- 136467976 122723 1112 , sga heap(3,2), freeable, CCursor 81179344 75727 1072 , CCursor, perm, perm 28431616 25568 1112 , sga heap(2,2), freeable, CCursor 16409784 14757 1112 , sga heap(1,2), freeable, CCursor
We are looking at upgrading to 11.2, however this will take some time. Any idea what could help to prevent/reduce growth of CCursor? Received on Mon Dec 09 2013 - 01:09:26 CET