Growth of CCursor sub-heap of the shared pool

From: vsevolod afanassiev <>
Date: Sun, 8 Dec 2013 16:09:26 -0800 (PST)
Message-ID: <>

Oracle 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

- free memory: 35 MB

  • sql area: 13 MB
  • CCursor: 13 MB

- free memory: 42 MB

  • sql area: 14 MB
  • CCursor: 170 MB

- free memory: 375 MB

  • sql area: 40KB (40 KB, not MB)
  • CCursor: -1.2 GB (negative value)

- 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:

  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

Original text of this message