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

On Sunday, December 15, 2013 3:14:27 PM UTC-7, vsevolod afanassiev wrote:
> Thanks for responding.
>
>
>
> Yes, I know about disabling subpools. Several year ago 9.2 database experiences similar issue: growth of 'miscellaneous' sub-heap of the shared pool, also in one subpool. At that time we didn't know how to monitor size of sub-heaps in each subpool, so we disabled sub-pools and size of 'miscellaneous' was monitored using V$SGASTAT. The issue was caused by bug related to object statistics, so STATISTICS_LEVEL was set to BASIC and this solved the issue.
>
>
>
> In 11.1. database the number of subpools was set by Oracle automatically. We debated disabling subpools, I had some doubts as subpools were introduced in 9.2, but at the end it was decided to go ahead and disable, so currently we have only 1.
>
>
>
> *._kghdsidx_count=1
>
>
>
> Values of memory-related parameters:
>
>
>
> memory_max_target big integer 8G
>
> memory_target big integer 0
>
>
>
> sga_max_size big integer 8G
>
> sga_target big integer 6656M
>
>
>
> pga_aggregate_target big integer 1536M
>
>
>
> db_cache_size big integer 0
>
>
>
> shared_pool_reserved_size big integer 53687091
>
> shared_pool_size big integer 0
>
>
>
> Initially we had memory_target = memory_max_size = 8 GB.
>
>
>
> We raised service request with Oracle Support, they were not able to identify bug that causes this issue.

You might get more information if you set the 4031 event:

• begin traces: alter system set events '4031 trace name errorstack level 3: 4031 trace name HEAPDUMP level 536870914 ';
• Stop traces: alter system set events '4031 trace name HEAPDUMP off ';

Setting the event with the first command will generate trace files for every occurrence of an ORA-04031 error which may provide more information for you to diagnose the problem.

David Fitzjarrell Received on Mon Dec 16 2013 - 03:43:25 CET