| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: shared pool memory issue on OPS (non-MTS)
Check out the following two values:
Resource Current Max Initial Limit Name Utilization Utilization Allocation Value ---------- ---------- ---------- ---------- -------- lm_ress 252143 256732 177599 UNLIMITED lm_locks 278106 288642 189208 UNLIMITED
Notice that "Current/Max Utilization" is much higher than "Initial Allocation"?
This means that (messages to alert.log or not), the DLM is overflowing its allocated space in the SGA and invading the Shared Pool. Increase your LM_RESS and LM_LOCKS parameter values...
> Here is the results from v$resource limit on the node with the problem.
>
> RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
> INITIAL_AL LIMIT_VALU
> ------------------------------ ------------------- ---------------
> ---------- ----------
> processes 52 54
> 800 800
> sessions 52 56
> 885 885
> enqueue_locks 233 242
> 11081 11081
> enqueue_resources 233 261
> 7870 7870
> _lm_procs 52 53
> 801 801
> lm_ress 252143 256732
> 177599 UNLIMITED
> lm_locks 278106 288642
> 189208 UNLIMITED
> lm_cache_ress 25822 25920
> 0 UNLIMITED
> dml_locks 0 42
> 7250 7250
> temporary_table_locks 0 2
> UNLIMITED UNLIMITED
> transactions 0 9
> 973 973
>
> RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
> INITIAL_AL LIMIT_VALU
> ------------------------------ ------------------- ---------------
> ---------- ----------
> sort_segment_locks 0 5
> UNLIMITED UNLIMITED
> max_rollback_segments 9 9
> 195 195
> distributed_transactions 0 0
> 5 5
> mts_max_servers 0 0
> 20 20
> parallel_max_servers 1 1
> 6 6
>
> 16 rows selected.
>
> Please disregard previous message with the contents of v$resource table.
>
>
> -----Original Message-----
> Sent: Thursday, May 02, 2002 4:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What does V$RESOURCE_LIMIT say? You can probably post that as a reply...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, May 02, 2002 2:27 PM
>
>
> > No messages in the alert log about dynamic lock allocation, the node
that
> is
> > having the issue has a small load on it right now and the memory is
still
> > being used up.
> >
> > Thanks
> >
> > -----Original Message-----
> > Sent: Thursday, May 02, 2002 3:29 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Any messages in the alert file, like dynamic lock allocation or resource
> > allocation ?
> > In Oracle8 locks and resources for OPS are dynamically allocated from
the
> > shared pool after the initial values of _LM* are used.
> > You can monitor that in v$resource_limit.
> >
> > Anjo.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, May 02, 2002 9:03 PM
> >
> >
> > > Hello,
> > > We are working on (2) HP 9000 64 bit HP-UX 11.0 in an OPS cluster (non
> > mts)
> > > on version 8.1.7.2.1 32 bit, we were encountering ora-4031 errors on
> one
> > of
> > > the instances, so we increased the amount of shared pool memory from
> 100mb
> > > to 200mb on both nodes in the cluster. Since the shared pool memory
was
> > > increased the instance on the node that had the error is no longer
> > > encountering it. But now the instance on the other node that was not
> > having
> > > any issue, is able to keep only 1% of the shared pool memory free and
> has
> > > hung 2 times. We are working with Oracle, but would appreciate any
> ideas.
> > We
> > > know that we have 'bind variable' issues, but not enough to get us
into
> > the
> > > situation that we now face.
> > >
> > >
> > > Thank you,
> > >
> > > Paul Sherman
> > > DBA Elcom, Inc.
> > > voice - 781-501-4143 (direct #)
> > > fax - 781-278-8341 (secure)
> > > email - psherman_at_elcom.com
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Sherman, Paul R.
> > > INET: PSherman_at_elcom.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Anjo Kolk
> > INET: anjo_at_oraperf.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Rodrigues, Bryan
> > INET: BRodrigues_at_elcom.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rodrigues, Bryan
> INET: BRodrigues_at_elcom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu May 02 2002 - 17:29:17 CDT
 |
 |