I will the information you asked for .
My question is if this huge percentage (>40%) for shared objects could
be the cause of the problem.
fitzjarrell_at_cox.net wrote:
> 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 - 15:01:37 CDT
