Re: shared pool memory issue on OPS (non-MTS)
What do the v$resource_limit numbers look like on the other node ? Is there any other way that the bad node could have become the master for all the dictionary cache information ? Does one node start up a few minutes before the other ? Is there anything that makes one node the preferred not for user access ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 03 May 2002 14:40 |Jonathon, | |Currently we do not pin anything in the shared pool. Still nothing from |Oracle on this yet. | |Thank you, | |Paul Sherman |DBAElcom, Inc. |voice - 781-501-4143 (direct #) |fax- 781-278-8341 (secure) |email - [EMAIL PROTECTED] | | |-Original Message- |Sent: Thursday, May 02, 2002 7:43 PM |To: Multiple recipients of list ORACLE-L | | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: shared pool memory issue on OPS (non-MTS)
Jonathon, Currently we do not pin anything in the shared pool. Still nothing from Oracle on this yet. Thank you, Paul Sherman DBAElcom, Inc. voice - 781-501-4143 (direct #) fax- 781-278-8341 (secure) email - [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 02, 2002 7:43 PM To: Multiple recipients of list ORACLE-L That certainly sounds like the solution to the immediate problem. (Mind you, those initial numbers look like generated values, not manual settings). However, it doesn't answer the question of why one of the machines has the problem. Do you think it;s possible that lots of material gets loaded and KEEP'ed in this instance before the other instance starts up, making this instance the resource master for a very large dictionary cache ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 02 May 2002 22:44 |Check out the following two values: | |Resource Current MaxInitial Limit |Name Utilization UtilizationAllocation Value |---- ---- |lm_ress252143 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... | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Sherman, Paul R. INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: shared pool memory issue on OPS (non-MTS)
That certainly sounds like the solution to the immediate problem. (Mind you, those initial numbers look like generated values, not manual settings). However, it doesn't answer the question of why one of the machines has the problem. Do you think it;s possible that lots of material gets loaded and KEEP'ed in this instance before the other instance starts up, making this instance the resource master for a very large dictionary cache ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 02 May 2002 22:44 |Check out the following two values: | |Resource Current MaxInitial Limit |Name Utilization UtilizationAllocation Value |---- ---- |lm_ress252143 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... | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: shared pool memory issue on OPS (non-MTS)
Bryan, There have been issues with this hanging problem with OPS in some releases. I am not sure if this is the problem, but applications that heavily use the rowcache in an OPS environment can have performance problems if the shared_pool is loaded and cause sudden hangs when the row cache object is being flushed from the shared pool. This problems should have been fixed with 8171 but it appears that the fix is in 8173. Hope this helps, Scott --- "Rodrigues, Bryan" <[EMAIL PROTECTED]> wrote: > 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" > <[EMAIL PROTECTED]> > 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 > > DBAElcom, Inc. > > voice - 781-501-4143 (direct #) > > fax- 781-278-8341 (secure) > > email - [EMAIL PROTECTED] > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Sherman, Paul R. > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing li
Re: shared pool memory issue on OPS (non-MTS)
Check out the following two values: Resource Current MaxInitial Limit Name Utilization UtilizationAllocation Value ---- ---- lm_ress252143 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... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 02, 2002 3:58 PM > 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 > 800800 > sessions52 56 > 885885 > enqueue_locks 233 242 > 11081 11081 > enqueue_resources 233 261 > 7870 7870 > _lm_procs 52 53 > 801801 > lm_ress 252143 256732 > 177599 UNLIMITED > lm_locks278106 288642 > 189208 UNLIMITED > lm_cache_ress25822 25920 > 0 UNLIMITED > dml_locks0 42 > 7250 7250 > temporary_table_locks0 2 > UNLIMITED UNLIMITED > transactions 0 9 > 973973 > > RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION > INITIAL_AL LIMIT_VALU > -- --- --- > -- -- > sort_segment_locks 0 5 > UNLIMITED UNLIMITED > max_rollback_segments9 9 > 195195 > 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" <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> > > 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 > > > DBAElcom, Inc. > > > voice - 781-501-4143 (direct #) > > > fax- 781-278-8341 (secure) > > > email - [EMAIL PROTECTED] > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Sherman, Paul R. > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (
Re: shared pool memory issue on OPS (non-MTS)
V$RESOURCE_LIMIT not V$RESOURCE... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 02, 2002 3:37 PM > Here is the results of looking at the v$resource table for the node with the > issue. > > select * from v$resource; > > ADDR TYID1ID2 > > -- -- -- > > D4F7C2F8 TS 3 692077582 > > D4F7C340 TS 38 268439158 > > D4F7C388 RT 2 0 > > D4F7C3D0 PI 1 0 > > D4F7C418 RT 2 1 > > D4F7C460 DM 1 0 > > D4F7C4A8 MR 1 0 > > D4F7C4F0 MR 2 0 > > D4F7C538 MR 3 0 > > D4F7C580 MR 4 0 > > D4F7C5C8 MR 5 0 > > D4F7C610 MR 6 0 > > D4F7C658 MR 7 0 > > D4F7C6A0 MR 8 0 > > D4F7C6E8 MR 9 0 > > D4F7C730 MR 10 0 > > D4F7C778 MR 11 0 > > D4F7C7C0 MR 12 0 > > D4F7C808 MR 13 0 > > D4F7C850 MR 14 0 > > D4F7C898 MR 15 0 > > D4F7C8E0 MR 16 0 > > D4F7C928 MR 17 0 > > D4F7C970 MR 18 0 > > D4F7C9B8 MR 19 0 > > D4F7CA00 MR 20 0 > > D4F7CA48 MR 21 0 > > D4F7CA90 MR 22 0 > > D4F7CAD8 MR 23 0 > > D4F7CB20 MR 24 0 > > D4F7CB68 MR 25 0 > > D4F7CBB0 MR 26 0 > > D4F7CBF8 MR 27 0 > > D4F7CC40 MR 28 0 > > D4F7CC88 MR 29 0 > > D4F7CCD0 MR 30 0 > > D4F7CD18 MR 31 0 > > D4F7CD60 MR 32 0 > > D4F7CDA8 MR 33 0 > > D4F7CDF0 MR 34 0 > > D4F7CE38 MR 35 0 > > D4F7CE80 MR 36 0 > > D4F7CEC8 MR 37 0 > > D4F7CF10 MR 38 0 > > D4F7CF58 MR 39 0 > > D4F7CFA0 MR 40 0 > > D4F7CFE8 MR 41 0 > > > ADDR TYID1ID2 > > -- -- -- > > D4F7D030 MR 42 0 > > D4F7D078 MR 43 0 > > D4F7D0C0 MR 44 0 > > D4F7D108 MR 45 0 > > D4F7D150 MR 46 0 > > D4F7D198 MR 47 0 > > D4F7D1E0 MR 48 0 > > D4F7D228 MR 49 0 > > D4F7D270 MR 50 0 > > D4F7D2B8 MR 51 0 > > D4F7D300 MR 52 0 > > D4F7D348 MR 53 0 > > D4F7D390 MR 54 0 > > D4F7D3D8 MR 55 0 > > D4F7D420 MR 56 0 > > D4F7D468 MR 57 0 > > D4F7D4B0 MR 58 0 > > D4F7D4F8 MR 59 0 > > D4F7D540 MR 60 0 > > D4F7D588 MR 61 0 > > D4F7D5D0 MR 62 0 > > D4F7D618 MR 63 0 > > D4F7D660 MR 64 0 > > D4F7D6A8 MR 65 0 > > D4F7D6F0 MR 66 0 > > D4F7D738 MR 67 0 > > D4F7D780 MR 68 0 > > D4F7D7C8 MR 69 0 > > D4F7D810 MR 70 0 > > D4F7D858 MR 71 0 > > D4F7D8A0 MR 72 0 > > D4F7D8E8 MR 79 0 > > D4F7D930 MR 80 0 > > D4F7D978 MR 81 0 > > D4F7D9C0 MR 82 0 > > D4F7DA08 MR 83 0 > > D4F7DA50 MR 84 0 > > D4F7DA98 MR 85 0 > > D4F7DAE0 MR 86 0 > > D4F7DB28 MR 87 0 > > D4F7DB70 MR 88 0 > > D4F7DBB8 MR 89 0 > > D4F7DC00 MR 90 0 > > D4F7DC48 MR 91 0 > > D4F7DC90 MR 92 0 > > D4F7DCD8 MR 93 0 > > D4F7DD20 MR 94 0 > > > ADDR TYID1ID2 > > -- -- -- > > D4F7DD68 MR 95 0 > > D4F7DDB0 MR 96 0 > > D4F7DDF8 MR 97 0 > > D4F7DE40 MR 98 0 > > D4F7DE88 MR101 0 > > D4F7DED0 MR102 0 > > D4F7DF18 MR105 0 > > D4F7DF60 MR106 0 > > D4F7DFA8 MR107 0 > > D4F7DFF0 MR108 0 > > D4F7E038 MR109 0 > > D4F7E080 MR110 0 > > D4F7E0C8 MR111 0 > > D4F7E110 MR112 0 > > D4F7E158 MR113 0 > > D4F7E1A0 MR114 0 > > D4F7E1E8 MR115 0 > > D4F7E230 MR116 0 > > D4F7E278 MR117 0 > > D4F7E2C0 MR118 0 > > D4F7E308 MR119 0 > > D4F7E350 MR120 0 > > D4F7E398 MR121 0 > > D4F7E3E0 MR122
Re: shared pool memory issue on OPS (non-MTS)
Hmmm, Not v$resource but v$resource_limit .. Anjo - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 02, 2002 11:37 PM > Here is the results of looking at the v$resource table for the node with the > issue. > > select * from v$resource; > > ADDR TYID1ID2 > > -- -- -- > > D4F7C2F8 TS 3 692077582 > > D4F7C340 TS 38 268439158 > > D4F7C388 RT 2 0 > > D4F7C3D0 PI 1 0 > > D4F7C418 RT 2 1 > > D4F7C460 DM 1 0 > > D4F7C4A8 MR 1 0 > > D4F7C4F0 MR 2 0 > > D4F7C538 MR 3 0 > > D4F7C580 MR 4 0 > > D4F7C5C8 MR 5 0 > > D4F7C610 MR 6 0 > > D4F7C658 MR 7 0 > > D4F7C6A0 MR 8 0 > > D4F7C6E8 MR 9 0 > > D4F7C730 MR 10 0 > > D4F7C778 MR 11 0 > > D4F7C7C0 MR 12 0 > > D4F7C808 MR 13 0 > > D4F7C850 MR 14 0 > > D4F7C898 MR 15 0 > > D4F7C8E0 MR 16 0 > > D4F7C928 MR 17 0 > > D4F7C970 MR 18 0 > > D4F7C9B8 MR 19 0 > > D4F7CA00 MR 20 0 > > D4F7CA48 MR 21 0 > > D4F7CA90 MR 22 0 > > D4F7CAD8 MR 23 0 > > D4F7CB20 MR 24 0 > > D4F7CB68 MR 25 0 > > D4F7CBB0 MR 26 0 > > D4F7CBF8 MR 27 0 > > D4F7CC40 MR 28 0 > > D4F7CC88 MR 29 0 > > D4F7CCD0 MR 30 0 > > D4F7CD18 MR 31 0 > > D4F7CD60 MR 32 0 > > D4F7CDA8 MR 33 0 > > D4F7CDF0 MR 34 0 > > D4F7CE38 MR 35 0 > > D4F7CE80 MR 36 0 > > D4F7CEC8 MR 37 0 > > D4F7CF10 MR 38 0 > > D4F7CF58 MR 39 0 > > D4F7CFA0 MR 40 0 > > D4F7CFE8 MR 41 0 > > > ADDR TYID1ID2 > > -- -- -- > > D4F7D030 MR 42 0 > > D4F7D078 MR 43 0 > > D4F7D0C0 MR 44 0 > > D4F7D108 MR 45 0 > > D4F7D150 MR 46 0 > > D4F7D198 MR 47 0 > > D4F7D1E0 MR 48 0 > > D4F7D228 MR 49 0 > > D4F7D270 MR 50 0 > > D4F7D2B8 MR 51 0 > > D4F7D300 MR 52 0 > > D4F7D348 MR 53 0 > > D4F7D390 MR 54 0 > > D4F7D3D8 MR 55 0 > > D4F7D420 MR 56 0 > > D4F7D468 MR 57 0 > > D4F7D4B0 MR 58 0 > > D4F7D4F8 MR 59 0 > > D4F7D540 MR 60 0 > > D4F7D588 MR 61 0 > > D4F7D5D0 MR 62 0 > > D4F7D618 MR 63 0 > > D4F7D660 MR 64 0 > > D4F7D6A8 MR 65 0 > > D4F7D6F0 MR 66 0 > > D4F7D738 MR 67 0 > > D4F7D780 MR 68 0 > > D4F7D7C8 MR 69 0 > > D4F7D810 MR 70 0 > > D4F7D858 MR 71 0 > > D4F7D8A0 MR 72 0 > > D4F7D8E8 MR 79 0 > > D4F7D930 MR 80 0 > > D4F7D978 MR 81 0 > > D4F7D9C0 MR 82 0 > > D4F7DA08 MR 83 0 > > D4F7DA50 MR 84 0 > > D4F7DA98 MR 85 0 > > D4F7DAE0 MR 86 0 > > D4F7DB28 MR 87 0 > > D4F7DB70 MR 88 0 > > D4F7DBB8 MR 89 0 > > D4F7DC00 MR 90 0 > > D4F7DC48 MR 91 0 > > D4F7DC90 MR 92 0 > > D4F7DCD8 MR 93 0 > > D4F7DD20 MR 94 0 > > > ADDR TYID1ID2 > > -- -- -- > > D4F7DD68 MR 95 0 > > D4F7DDB0 MR 96 0 > > D4F7DDF8 MR 97 0 > > D4F7DE40 MR 98 0 > > D4F7DE88 MR101 0 > > D4F7DED0 MR102 0 > > D4F7DF18 MR105 0 > > D4F7DF60 MR106 0 > > D4F7DFA8 MR107 0 > > D4F7DFF0 MR108 0 > > D4F7E038 MR109 0 > > D4F7E080 MR110 0 > > D4F7E0C8 MR111 0 > > D4F7E110 MR112 0 > > D4F7E158 MR113 0 > > D4F7E1A0 MR114 0 > > D4F7E1E8 MR115 0 > > D4F7E230 MR116 0 > > D4F7E278 MR117 0 > > D4F7E2C0 MR118 0 > > D4F7E308 MR119 0 > > D4F7E350 MR120 0 > > D4F7E398 MR121 0 > > D4F7E3
RE: shared pool memory issue on OPS (non-MTS)
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 800800 sessions52 56 885885 enqueue_locks 233 242 11081 11081 enqueue_resources 233 261 7870 7870 _lm_procs 52 53 801801 lm_ress 252143 256732 177599 UNLIMITED lm_locks278106 288642 189208 UNLIMITED lm_cache_ress25822 25920 0 UNLIMITED dml_locks0 42 7250 7250 temporary_table_locks0 2 UNLIMITED UNLIMITED transactions 0 9 973973 RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU -- --- --- -- -- sort_segment_locks 0 5 UNLIMITED UNLIMITED max_rollback_segments9 9 195195 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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 > > DBAElcom, Inc. > > voice - 781-501-4143 (direct #) > > fax- 781-278-8341 (secure) > > email - [EMAIL PROTECTED] > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Sherman, Paul R. > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
RE: shared pool memory issue on OPS (non-MTS)
Here is the results of looking at the v$resource table for the node with the issue. select * from v$resource; ADDR TYID1ID2 -- -- -- D4F7C2F8 TS 3 692077582 D4F7C340 TS 38 268439158 D4F7C388 RT 2 0 D4F7C3D0 PI 1 0 D4F7C418 RT 2 1 D4F7C460 DM 1 0 D4F7C4A8 MR 1 0 D4F7C4F0 MR 2 0 D4F7C538 MR 3 0 D4F7C580 MR 4 0 D4F7C5C8 MR 5 0 D4F7C610 MR 6 0 D4F7C658 MR 7 0 D4F7C6A0 MR 8 0 D4F7C6E8 MR 9 0 D4F7C730 MR 10 0 D4F7C778 MR 11 0 D4F7C7C0 MR 12 0 D4F7C808 MR 13 0 D4F7C850 MR 14 0 D4F7C898 MR 15 0 D4F7C8E0 MR 16 0 D4F7C928 MR 17 0 D4F7C970 MR 18 0 D4F7C9B8 MR 19 0 D4F7CA00 MR 20 0 D4F7CA48 MR 21 0 D4F7CA90 MR 22 0 D4F7CAD8 MR 23 0 D4F7CB20 MR 24 0 D4F7CB68 MR 25 0 D4F7CBB0 MR 26 0 D4F7CBF8 MR 27 0 D4F7CC40 MR 28 0 D4F7CC88 MR 29 0 D4F7CCD0 MR 30 0 D4F7CD18 MR 31 0 D4F7CD60 MR 32 0 D4F7CDA8 MR 33 0 D4F7CDF0 MR 34 0 D4F7CE38 MR 35 0 D4F7CE80 MR 36 0 D4F7CEC8 MR 37 0 D4F7CF10 MR 38 0 D4F7CF58 MR 39 0 D4F7CFA0 MR 40 0 D4F7CFE8 MR 41 0 ADDR TYID1ID2 -- -- -- D4F7D030 MR 42 0 D4F7D078 MR 43 0 D4F7D0C0 MR 44 0 D4F7D108 MR 45 0 D4F7D150 MR 46 0 D4F7D198 MR 47 0 D4F7D1E0 MR 48 0 D4F7D228 MR 49 0 D4F7D270 MR 50 0 D4F7D2B8 MR 51 0 D4F7D300 MR 52 0 D4F7D348 MR 53 0 D4F7D390 MR 54 0 D4F7D3D8 MR 55 0 D4F7D420 MR 56 0 D4F7D468 MR 57 0 D4F7D4B0 MR 58 0 D4F7D4F8 MR 59 0 D4F7D540 MR 60 0 D4F7D588 MR 61 0 D4F7D5D0 MR 62 0 D4F7D618 MR 63 0 D4F7D660 MR 64 0 D4F7D6A8 MR 65 0 D4F7D6F0 MR 66 0 D4F7D738 MR 67 0 D4F7D780 MR 68 0 D4F7D7C8 MR 69 0 D4F7D810 MR 70 0 D4F7D858 MR 71 0 D4F7D8A0 MR 72 0 D4F7D8E8 MR 79 0 D4F7D930 MR 80 0 D4F7D978 MR 81 0 D4F7D9C0 MR 82 0 D4F7DA08 MR 83 0 D4F7DA50 MR 84 0 D4F7DA98 MR 85 0 D4F7DAE0 MR 86 0 D4F7DB28 MR 87 0 D4F7DB70 MR 88 0 D4F7DBB8 MR 89 0 D4F7DC00 MR 90 0 D4F7DC48 MR 91 0 D4F7DC90 MR 92 0 D4F7DCD8 MR 93 0 D4F7DD20 MR 94 0 ADDR TYID1ID2 -- -- -- D4F7DD68 MR 95 0 D4F7DDB0 MR 96 0 D4F7DDF8 MR 97 0 D4F7DE40 MR 98 0 D4F7DE88 MR101 0 D4F7DED0 MR102 0 D4F7DF18 MR105 0 D4F7DF60 MR106 0 D4F7DFA8 MR107 0 D4F7DFF0 MR108 0 D4F7E038 MR109 0 D4F7E080 MR110 0 D4F7E0C8 MR111 0 D4F7E110 MR112 0 D4F7E158 MR113 0 D4F7E1A0 MR114 0 D4F7E1E8 MR115 0 D4F7E230 MR116 0 D4F7E278 MR117 0 D4F7E2C0 MR118 0 D4F7E308 MR119 0 D4F7E350 MR120 0 D4F7E398 MR121 0 D4F7E3E0 MR122 0 D4F7E428 MR123 0 D4F7E470 MR124 0 D4F7E4B8 MR125 0 D4F7E500 MR126 0 D4F7E548 MR127 0 D4F7E590 MR128 0 D4F7E5D8 MR129 0 D4F7E620 MR130 0 D4F7E668 MR131 0 D4F7E6B0 MR132 0 D4F7E6F8 MR133 0 D4F7E740 MR134 0 D4F7E788 MR135 0 D4F7E7D0 MR136 0 D4F7E818 MR137 0 D4F7E860 MR
Re: shared pool memory issue on OPS (non-MTS)
Trivial checks that you've probably gone through, but are there messages for Dynamic resource allocation Dynamic lock allocation in the alert log. Are you using partitioned views Do you see lots of waits for "DFS Lock handle" on the instance that isn't having the memory problem Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 02 May 2002 19:22 |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 |DBAElcom, Inc. |voice - 781-501-4143 (direct #) |fax- 781-278-8341 (secure) |email - [EMAIL PROTECTED] | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Sherman, Paul R. | INET: [EMAIL PROTECTED] | |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: [EMAIL PROTECTED] (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: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: shared pool memory issue on OPS (non-MTS)
What does V$RESOURCE_LIMIT say? You can probably post that as a reply... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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 > > DBAElcom, Inc. > > voice - 781-501-4143 (direct #) > > fax- 781-278-8341 (secure) > > email - [EMAIL PROTECTED] > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Sherman, Paul R. > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: shared pool memory issue on OPS (non-MTS)
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" <[EMAIL PROTECTED]> 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 > DBAElcom, Inc. > voice - 781-501-4143 (direct #) > fax- 781-278-8341 (secure) > email - [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sherman, Paul R. > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: shared pool memory issue on OPS (non-MTS)
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" <[EMAIL PROTECTED]> 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 > DBAElcom, Inc. > voice - 781-501-4143 (direct #) > fax- 781-278-8341 (secure) > email - [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sherman, Paul R. > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
