Re: Growth of CCursor sub-heap of the shared pool

On Sunday, December 8, 2013 5:09:26 PM UTC-7, vsevolod afanassiev wrote:
> 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.00 by Tanel Poder ( http://www.tanelpoder.com )
>
>
>
> 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?

As far as I know the only way around this is to upgrade to 11.2.0.x; this is likely why there is no patch/workaround for this bug (it's fixed in a later release).

David Fitzjarrell Received on Mon Dec 09 2013 - 19:24:57 CET