Re: Shared Pool v$sgastat problem

gbenet_at_intracom.gr wrote:
> Hello ,
> there i a shared pool problem ( ORA 4031 )
>
> version 8.1.7.4
> OPS
> HP-UX
> Shared Pool : 256 MB
>
> We checked v$sgastat view and we had the following results:
>
> POOL NAME MB %
> shared pool free memory 75.5220108 6.56
> shared pool miscellaneous 6.819778442 0.59
> shared pool dlm shared memory 145.9559135 12.69
> shared pool processes 1.659393311 0.14
> shared pool KGFF heap 0.066619873 0.01
> shared pool table columns 0.017677307 0.00
> shared pool db_block_buffers 120 10.43
> shared pool ktlbk state objects 0.930328369 0.08
> shared pool table definiti 0.000144958 0.00
> shared pool type object de 0.002250671 0.00
> shared pool PX msg pool 0.178649902 0.02
> shared pool fixed allocation callback 0.002510071 0.00
> shared pool PLS non-lib hp 0.002037048 0.00
> shared pool DML locks 1.602172852 0.14
> shared pool sessions 3.838500977 0.33
> shared pool State objects 516.6697388 44.91
> shared pool gc_* 70 6.08
> shared pool db_block_hash_buckets 16.78135681 1.46
> shared pool partitioning d 0.232208252 0.02
> shared pool PL/SQL MPCODE 0.459846497 0.04
> shared pool enqueue_resources 1.190185547 0.10
> shared pool KGK heap 0.011299133 0.00
> shared pool dictionary cache 4.535545349 0.39
> shared pool PL/SQL DIANA 0.382865906 0.03
> shared pool dlm process array 1.305496216 0.11
> shared pool PX subheap 0.008338928 0.00
> shared pool branches 0.500152588 0.04
> shared pool library cache 36.08283997 3.14
> shared pool trigger inform 0.000213623 0.00
> shared pool sql area 133.1006927 11.57
> shared pool db_handles 1.258850098 0.11
> shared pool kclnb 3.000663757 0.26
> shared pool KQLS heap 0.262527466 0.02
> shared pool transactions 2.638244629 0.23
> shared pool event statistics per sess 5.47996521 0.48
>
> Totals 1150.49902
>
>
> State objects is over >44% .Is that ok ? Is there something wrong ?
> Can we do something in order to change this value ?
>
> thanx

You experience ORA-04031 errors, yet you fail to report WHICH of the pools is at fault. This information is found in the error message itself, and it would be helpful for you to report the ENTIRE error message and not simply the error number.

You should be looking at V$SHARED_POOL_RESERVED to see a short history of requests generating ORA-04031 errors:

REQUEST_FAILURES
 Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred)

LAST_FAILURE_SIZE
 Request size of the last failed request (that is, the request size for the last ORA-04031 error)

ABORTED_REQUEST _THRESHOLD
 Minimum size of a request which signals an ORA-04031 error without flushing objects

ABORTED_REQUESTS
 Number of requests that signalled an ORA-04031 error without flushing objects

LAST_ABORTED_SIZE
 Last size of the request that returned an ORA-04031 error without flushing objects from the LRU list

And you should also be looking at WHICH pool is causing your errors. It is not necessarily the shared pool, it could be the large pool or the java pool. Also see if shared_pool_reserved_size is set; by default five percent of the shared_pool_size is reserved for transactions requiring large areas of contiguous memory. This may not be sufficient for your system, thus the rest of the columns in V$SHARED_POOL_RESERVED should be examined. Should no data exist except in the columns listed above the parameter is not set. This parameter can be set to almost any value; constraining this is the value for one-half of the shared_pool_size as the maximum. The documentation still liste the obsolete parameter shared_pool_reserved_min_alloc as the low limit. Using the columns listed above can help you set this parameter.

Until you post the entire error message, and do some of your own research, it's difficult to tell you what to change. You've now been given some guidelines and have, hopefully, been pointed in the correct direction. You now need to evaluate your system; we are here to help, when we are given sufficient information. Provide such and we will do our best to help you provide a proper solution.

David Fitzjarrell Received on Thu Jul 21 2005 - 10:07:34 CDT