RE: ROW CACHE HIGH - Priority 1
Hi, There are two BUGS 2605240 and 2683906 which got fixed in 9.2.0.4.0. I will be applying the patch to fix the problem. Thanks for your input. Muqthar -Original Message- Sent: Friday, September 26, 2003 9:55 AM To: Multiple recipients of list ORACLE-L SQL parsing would also be reflected in rowcache because, to parse successfully, you need dictionary and parse locks. -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Sam Roberts > Sent: Friday, September 26, 2003 2:20 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: ROW CACHE HIGH - Priority 1 > > > sql parsing would be reflected in Library cache not rowcache > > Basic stuff > > Sam > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Wednesday, September 24, 2003 1:14 PM > > > > General answer: upgrade to 9.2.0.4 and hope that the bug has been > > fixed. Row cache locks are data dictionary locks. You can see the > > contents of row cache by inspecting v$rowcache. You may need to > > increase shared pool. Last but not least, how fast is your private > > network connection between > the > > two nodes? 100mbit/sec is not nearly fast enough. You need > at least a > > gigabit switch. Also check your SQL for hard parsing (see the > > executions and invalidations in > > v$sqlarea), ad-hoc DDL and that kind of stuff. > > And no, it's not my priority 1. > > > > > > -- > > Mladen Gogala > > Oracle DBA > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > > > Of Muqthar Ahmed > > > Sent: Wednesday, September 24, 2003 3:50 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: ROW CACHE HIGH - Priority 1 > > > > > > > > > Hi, > > > > > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > Currently I am > > > seeing very high number for ROW CACHE LOCK in statspack. > > > > > > Top 5 Timed Events > > > ~~ > > > % Total > > > Event Waits > > > Time (s) Ela Time > > > > > > --- > > > row cache lock11,310 5,441 > > > 86.97 > > > CPU time > > > 522 8.34 > > > global cache cr request32,513 71 > > >1.14 > > > global cache null to x 21,507 57 > > > 91 > > > log file sync22,689 49 > > > .78 > > > > > > - > > > > > > Get > > > Spin & > > > Latch Name Requests Misses > > > SleepsSleeps 1->4 > > > -- -- > > > --- --- > > > library cache 7,094,208 31,499 > > > 1,480 30031/1456/12/0/0 > > > shared pool 2,385,408 6,739 > > > 5276212/527/0/0/0 > > > ges enqueue table freelist 1,492,275 1,903 > > > 1241780/122/1/0 /0 > > > library cache pin 3,201,008 1,437 > > > 1301307/130/0/0/0 > > > row cache objects1,400,498 1,020 > > > 56 964/56/0/0/0 > > > row cache enqueue latch 1,292,843 715 > > > 19 696/19/0/0/0 > > > > > > It is holding row cache lock (v$session_wait), other > sessions are in > > > the queuedue to this, number of concurrent sessions will > > > increase from 100 to 350 sessions on each node. In less than one > > > minute everything will be cleared ( OLTP database) > > > > > > I have already opened a TAR with Priority 1. Do you have any > > > suggestions. > > > > > > Thanks > > > Muqthar Ahmed > > > DBA > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Muqthar Ahmed > > > INET: [EMAIL PROTECTED] >
RE: ROW CACHE HIGH - Priority 1
SQL parsing would also be reflected in rowcache because, to parse successfully, you need dictionary and parse locks. -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Sam Roberts > Sent: Friday, September 26, 2003 2:20 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: ROW CACHE HIGH - Priority 1 > > > sql parsing would be reflected in Library cache not rowcache > > Basic stuff > > Sam > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Wednesday, September 24, 2003 1:14 PM > > > > General answer: upgrade to 9.2.0.4 and hope that the bug has been > > fixed. Row cache locks are data dictionary locks. You can see the > > contents of row cache by inspecting v$rowcache. You may need to > > increase shared pool. Last but not least, how fast is your private > > network connection between > the > > two nodes? 100mbit/sec is not nearly fast enough. You need > at least a > > gigabit switch. Also check your SQL for hard parsing (see the > > executions and invalidations in > > v$sqlarea), ad-hoc DDL and that kind of stuff. > > And no, it's not my priority 1. > > > > > > -- > > Mladen Gogala > > Oracle DBA > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > > > Of Muqthar Ahmed > > > Sent: Wednesday, September 24, 2003 3:50 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: ROW CACHE HIGH - Priority 1 > > > > > > > > > Hi, > > > > > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > Currently I am > > > seeing very high number for ROW CACHE LOCK in statspack. > > > > > > Top 5 Timed Events > > > ~~ > > > % Total > > > Event Waits > > > Time (s) Ela Time > > > > > > --- > > > row cache lock11,310 5,441 > > > 86.97 > > > CPU time > > > 522 8.34 > > > global cache cr request32,513 71 > > >1.14 > > > global cache null to x 21,507 57 > > > 91 > > > log file sync22,689 49 > > > .78 > > > > > > - > > > > > > Get > > > Spin & > > > Latch Name Requests Misses > > > SleepsSleeps 1->4 > > > -- -- > > > --- --- > > > library cache 7,094,208 31,499 > > > 1,480 30031/1456/12/0/0 > > > shared pool 2,385,408 6,739 > > > 5276212/527/0/0/0 > > > ges enqueue table freelist 1,492,275 1,903 > > > 1241780/122/1/0 /0 > > > library cache pin 3,201,008 1,437 > > > 1301307/130/0/0/0 > > > row cache objects1,400,498 1,020 > > > 56 964/56/0/0/0 > > > row cache enqueue latch 1,292,843 715 > > > 19 696/19/0/0/0 > > > > > > It is holding row cache lock (v$session_wait), other > sessions are in > > > the queuedue to this, number of concurrent sessions will > > > increase from 100 to 350 sessions on each node. In less than one > > > minute everything will be cleared ( OLTP database) > > > > > > I have already opened a TAR with Priority 1. Do you have any > > > suggestions. > > > > > > Thanks > > > Muqthar Ahmed > > > DBA > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Muqthar Ahmed > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web > hosting services > > > > --
RE: ROW CACHE HIGH - Priority 1
Hi, Thanks for your suggestions. Usually Friday we have low traffic on our website. I will run the database with one node to check the row cache problem. Muqthar -Original Message- Sent: Friday, September 26, 2003 7:15 AM To: Multiple recipients of list ORACLE-L > sql parsing would be reflected in Library cache not rowcache > > Basic stuff Not that basic. Rowcache will get hit during any hard parse, since you need to get information about objects from DD. And if objects information isn't cached yet, it has to be loaded into row cache. Btw, row cache locks might come from LMT extent (de)allocation. Now, if both RAC instances start allocating or deallocating extents in a datafile, they need to access the same bitmap blocks, which results in GC traffic or pings, maybe that's the problem? Tanel. > > Sam > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Wednesday, September 24, 2003 1:14 PM > > > > General answer: upgrade to 9.2.0.4 and hope that the bug has been fixed. > > Row cache locks are data dictionary locks. You can see the contents of row > > cache > > by inspecting v$rowcache. You may need to increase shared pool. > > Last but not least, how fast is your private network connection between > the > > two nodes? 100mbit/sec is not nearly fast enough. You need at least a > > gigabit > > switch. Also check your SQL for hard parsing (see the executions and > > invalidations in > > v$sqlarea), ad-hoc DDL and that kind of stuff. > > And no, it's not my priority 1. > > > > > > -- > > Mladen Gogala > > Oracle DBA > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > > Behalf Of Muqthar Ahmed > > > Sent: Wednesday, September 24, 2003 3:50 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: ROW CACHE HIGH - Priority 1 > > > > > > > > > Hi, > > > > > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > > > Currently I am seeing very high number for ROW CACHE LOCK in > > > statspack. > > > > > > Top 5 Timed Events > > > ~~ > > > % Total > > > Event Waits > > > Time (s) Ela Time > > > > > > --- > > > row cache lock11,310 5,441 > > > 86.97 > > > CPU time > > > 522 8.34 > > > global cache cr request32,513 71 > > >1.14 > > > global cache null to x 21,507 57 > > > 91 > > > log file sync22,689 49 > > > .78 > > > > > > - > > > > > > Get > > > Spin & > > > Latch Name Requests Misses > > > SleepsSleeps 1->4 > > > -- -- > > > --- --- > > > library cache 7,094,208 31,499 > > > 1,480 30031/1456/12/0/0 > > > shared pool 2,385,408 6,739 > > > 5276212/527/0/0/0 > > > ges enqueue table freelist 1,492,275 1,903 > > > 1241780/122/1/0 /0 > > > library cache pin 3,201,008 1,437 > > > 1301307/130/0/0/0 > > > row cache objects1,400,498 1,020 > > > 56 964/56/0/0/0 > > > row cache enqueue latch 1,292,843 715 > > > 19 696/19/0/0/0 > > > > > > It is holding row cache lock (v$session_wait), other sessions > > > are in the queuedue to this, number of concurrent > > > sessions will increase from 100 to 350 sessions on each node. > > > In less than one minute everything will be cleared ( OLTP database) > > > > > > I have already opened a TAR with Priority 1. Do you have any > > > suggestions. > > > > > > Thanks > > > Muqthar Ahmed > > > DBA > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Muqthar
Re: ROW CACHE HIGH - Priority 1
> sql parsing would be reflected in Library cache not rowcache > > Basic stuff Not that basic. Rowcache will get hit during any hard parse, since you need to get information about objects from DD. And if objects information isn't cached yet, it has to be loaded into row cache. Btw, row cache locks might come from LMT extent (de)allocation. Now, if both RAC instances start allocating or deallocating extents in a datafile, they need to access the same bitmap blocks, which results in GC traffic or pings, maybe that's the problem? Tanel. > > Sam > - Original Message - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Wednesday, September 24, 2003 1:14 PM > > > > General answer: upgrade to 9.2.0.4 and hope that the bug has been fixed. > > Row cache locks are data dictionary locks. You can see the contents of row > > cache > > by inspecting v$rowcache. You may need to increase shared pool. > > Last but not least, how fast is your private network connection between > the > > two nodes? 100mbit/sec is not nearly fast enough. You need at least a > > gigabit > > switch. Also check your SQL for hard parsing (see the executions and > > invalidations in > > v$sqlarea), ad-hoc DDL and that kind of stuff. > > And no, it's not my priority 1. > > > > > > -- > > Mladen Gogala > > Oracle DBA > > > > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > > Behalf Of Muqthar Ahmed > > > Sent: Wednesday, September 24, 2003 3:50 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: ROW CACHE HIGH - Priority 1 > > > > > > > > > Hi, > > > > > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > > > Currently I am seeing very high number for ROW CACHE LOCK in > > > statspack. > > > > > > Top 5 Timed Events > > > ~~ > > > % Total > > > Event Waits > > > Time (s) Ela Time > > > > > > --- > > > row cache lock11,310 5,441 > > > 86.97 > > > CPU time > > > 522 8.34 > > > global cache cr request32,513 71 > > >1.14 > > > global cache null to x 21,507 57 > > > 91 > > > log file sync22,689 49 > > > .78 > > > > > > - > > > > > > Get > > > Spin & > > > Latch Name Requests Misses > > > SleepsSleeps 1->4 > > > -- -- > > > --- --- > > > library cache 7,094,208 31,499 > > > 1,480 30031/1456/12/0/0 > > > shared pool 2,385,408 6,739 > > > 5276212/527/0/0/0 > > > ges enqueue table freelist 1,492,275 1,903 > > > 1241780/122/1/0 /0 > > > library cache pin 3,201,008 1,437 > > > 1301307/130/0/0/0 > > > row cache objects1,400,498 1,020 > > > 56 964/56/0/0/0 > > > row cache enqueue latch 1,292,843 715 > > > 19 696/19/0/0/0 > > > > > > It is holding row cache lock (v$session_wait), other sessions > > > are in the queuedue to this, number of concurrent > > > sessions will increase from 100 to 350 sessions on each node. > > > In less than one minute everything will be cleared ( OLTP database) > > > > > > I have already opened a TAR with Priority 1. Do you have any > > > suggestions. > > > > > > Thanks > > > Muqthar Ahmed > > > DBA > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Muqthar Ahmed > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting services > > > --
Re: ROW CACHE HIGH - Priority 1
sql parsing would be reflected in Library cache not rowcache Basic stuff Sam - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, September 24, 2003 1:14 PM > General answer: upgrade to 9.2.0.4 and hope that the bug has been fixed. > Row cache locks are data dictionary locks. You can see the contents of row > cache > by inspecting v$rowcache. You may need to increase shared pool. > Last but not least, how fast is your private network connection between the > two nodes? 100mbit/sec is not nearly fast enough. You need at least a > gigabit > switch. Also check your SQL for hard parsing (see the executions and > invalidations in > v$sqlarea), ad-hoc DDL and that kind of stuff. > And no, it's not my priority 1. > > > -- > Mladen Gogala > Oracle DBA > > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > Behalf Of Muqthar Ahmed > > Sent: Wednesday, September 24, 2003 3:50 PM > > To: Multiple recipients of list ORACLE-L > > Subject: ROW CACHE HIGH - Priority 1 > > > > > > Hi, > > > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > > Currently I am seeing very high number for ROW CACHE LOCK in > > statspack. > > > > Top 5 Timed Events > > ~~ > > % Total > > Event Waits > > Time (s) Ela Time > > > > --- > > row cache lock11,310 5,441 > > 86.97 > > CPU time > > 522 8.34 > > global cache cr request32,513 71 > >1.14 > > global cache null to x 21,507 57 > > 91 > > log file sync22,689 49 > > .78 > > > > - > > > > Get > > Spin & > > Latch Name Requests Misses > > SleepsSleeps 1->4 > > -- -- > > --- --- > > library cache 7,094,208 31,499 > > 1,480 30031/1456/12/0/0 > > shared pool 2,385,408 6,739 > > 5276212/527/0/0/0 > > ges enqueue table freelist 1,492,275 1,903 > > 1241780/122/1/0 /0 > > library cache pin 3,201,008 1,437 > > 1301307/130/0/0/0 > > row cache objects1,400,498 1,020 > > 56 964/56/0/0/0 > > row cache enqueue latch 1,292,843 715 > > 19 696/19/0/0/0 > > > > It is holding row cache lock (v$session_wait), other sessions > > are in the queuedue to this, number of concurrent > > sessions will increase from 100 to 350 sessions on each node. > > In less than one minute everything will be cleared ( OLTP database) > > > > I have already opened a TAR with Priority 1. Do you have any > > suggestions. > > > > Thanks > > Muqthar Ahmed > > DBA > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Muqthar Ahmed > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > 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). > > > > > > > Note: > This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipien
RE: ROW CACHE HIGH - Priority 1
Oh, it's not me, I was just replying to somebody. My company is not running RAC. I know what clustering means, I have 5+ years of experience with OPS on both versions 7 and 8 plus I used to teach people VMS and how to tune it. Yeah, you're absolutely right clustering is flustering, don't do it unless that is what you really, really want & need. Especially need. -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Mogens Nørgaard > Sent: Wednesday, September 24, 2003 7:35 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: ROW CACHE HIGH - Priority 1 > > > I HAVE to ask this question: Is it possible for you to turn > off RAC and > thereby completely avoiding this issue? > > Yeah, that's kind of funny. Except it isn't, really. Invalidations of > objects, drops, in general breaking of breakable parse locks > - they will > all need to be communicated to all the other instances. But > this kind of > activity is often forgotten in lieu of the "normal" buffer > cache activity. > > Undskyld. But the best optimisation is not to do it :-). > > Mogens > > Mladen Gogala wrote: > > >General answer: upgrade to 9.2.0.4 and hope that the bug has been > >fixed. Row cache locks are data dictionary locks. You can see the > >contents of row cache by inspecting v$rowcache. You may need to > >increase shared pool. Last but not least, how fast is your private > >network connection between the two nodes? 100mbit/sec is not nearly > >fast enough. You need at least a gigabit > >switch. Also check your SQL for hard parsing (see the executions and > >invalidations in > >v$sqlarea), ad-hoc DDL and that kind of stuff. > >And no, it's not my priority 1. > > > > > >-- > >Mladen Gogala > >Oracle DBA > > > > > > > > > > > >>-Original Message- > >>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > >>Behalf Of Muqthar Ahmed > >>Sent: Wednesday, September 24, 2003 3:50 PM > >>To: Multiple recipients of list ORACLE-L > >>Subject: ROW CACHE HIGH - Priority 1 > >> > >> > >>Hi, > >> > >>I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > >>Currently I am seeing very high number for ROW CACHE LOCK in > >>statspack. > >> > >>Top 5 Timed Events > >>~~ > >> % Total > >>Event Waits > >>Time (s) Ela Time > >> > >>--- > >>row cache lock11,310 5,441 > >> 86.97 > >>CPU time > >>522 8.34 > >>global cache cr request32,513 71 > >> 1.14 > >>global cache null to x 21,507 57 > >> 91 > >>log file sync22,689 49 > >> .78 > >> > >>- > >> > >> Get > >>Spin & > >>Latch Name Requests Misses > >> SleepsSleeps 1->4 > >>-- -- > >>--- --- > >>library cache 7,094,208 31,499 > >> 1,480 30031/1456/12/0/0 > >>shared pool 2,385,408 6,739 > >> 5276212/527/0/0/0 > >>ges enqueue table freelist 1,492,275 1,903 > >>1241780/122/1/0 /0 > >>library cache pin 3,201,008 1,437 > >>1301307/130/0/0/0 > >>row cache objects1,400,498 1,020 > >> 56 964/56/0/0/0 > >>row cache enqueue latch 1,292,843 715 > >>19 696/19/0/0/0 > >> > >>It is holding row cache lock (v$session_wait), other sessions > >>are in the queuedue to this, number of concurrent > >>sessions will increase from 100 to 350 sessions on each node. > >> In less than
Re: ROW CACHE HIGH - Priority 1
I HAVE to ask this question: Is it possible for you to turn off RAC and thereby completely avoiding this issue? Yeah, that's kind of funny. Except it isn't, really. Invalidations of objects, drops, in general breaking of breakable parse locks - they will all need to be communicated to all the other instances. But this kind of activity is often forgotten in lieu of the "normal" buffer cache activity. Undskyld. But the best optimisation is not to do it :-). Mogens Mladen Gogala wrote: General answer: upgrade to 9.2.0.4 and hope that the bug has been fixed. Row cache locks are data dictionary locks. You can see the contents of row cache by inspecting v$rowcache. You may need to increase shared pool. Last but not least, how fast is your private network connection between the two nodes? 100mbit/sec is not nearly fast enough. You need at least a gigabit switch. Also check your SQL for hard parsing (see the executions and invalidations in v$sqlarea), ad-hoc DDL and that kind of stuff. And no, it's not my priority 1. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Muqthar Ahmed Sent: Wednesday, September 24, 2003 3:50 PM To: Multiple recipients of list ORACLE-L Subject: ROW CACHE HIGH - Priority 1 Hi, I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. Currently I am seeing very high number for ROW CACHE LOCK in statspack. Top 5 Timed Events ~~ % Total Event Waits Time (s) Ela Time --- row cache lock11,310 5,441 86.97 CPU time 522 8.34 global cache cr request32,513 71 1.14 global cache null to x 21,507 57 91 log file sync22,689 49 .78 - Get Spin & Latch Name Requests Misses SleepsSleeps 1->4 -- -- --- --- library cache 7,094,208 31,499 1,480 30031/1456/12/0/0 shared pool 2,385,408 6,739 5276212/527/0/0/0 ges enqueue table freelist 1,492,275 1,903 1241780/122/1/0 /0 library cache pin 3,201,008 1,437 1301307/130/0/0/0 row cache objects1,400,498 1,020 56 964/56/0/0/0 row cache enqueue latch 1,292,843 715 19 696/19/0/0/0 It is holding row cache lock (v$session_wait), other sessions are in the queuedue to this, number of concurrent sessions will increase from 100 to 350 sessions on each node. In less than one minute everything will be cleared ( OLTP database) I have already opened a TAR with Priority 1. Do you have any suggestions. Thanks Muqthar Ahmed DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ:
RE: ROW CACHE HIGH - Priority 1
General answer: upgrade to 9.2.0.4 and hope that the bug has been fixed. Row cache locks are data dictionary locks. You can see the contents of row cache by inspecting v$rowcache. You may need to increase shared pool. Last but not least, how fast is your private network connection between the two nodes? 100mbit/sec is not nearly fast enough. You need at least a gigabit switch. Also check your SQL for hard parsing (see the executions and invalidations in v$sqlarea), ad-hoc DDL and that kind of stuff. And no, it's not my priority 1. -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Muqthar Ahmed > Sent: Wednesday, September 24, 2003 3:50 PM > To: Multiple recipients of list ORACLE-L > Subject: ROW CACHE HIGH - Priority 1 > > > Hi, > > I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. > Currently I am seeing very high number for ROW CACHE LOCK in > statspack. > > Top 5 Timed Events > ~~ > % Total > Event Waits > Time (s) Ela Time > > --- > row cache lock11,310 5,441 > 86.97 > CPU time > 522 8.34 > global cache cr request32,513 71 >1.14 > global cache null to x 21,507 57 > 91 > log file sync22,689 49 > .78 > > - > > Get > Spin & > Latch Name Requests Misses > SleepsSleeps 1->4 > -- -- > --- --- > library cache 7,094,208 31,499 > 1,480 30031/1456/12/0/0 > shared pool 2,385,408 6,739 > 5276212/527/0/0/0 > ges enqueue table freelist 1,492,275 1,903 > 1241780/122/1/0 /0 > library cache pin 3,201,008 1,437 > 1301307/130/0/0/0 > row cache objects1,400,498 1,020 > 56 964/56/0/0/0 > row cache enqueue latch 1,292,843 715 > 19 696/19/0/0/0 > > It is holding row cache lock (v$session_wait), other sessions > are in the queuedue to this, number of concurrent > sessions will increase from 100 to 350 sessions on each node. > In less than one minute everything will be cleared ( OLTP database) > > I have already opened a TAR with Priority 1. Do you have any > suggestions. > > Thanks > Muqthar Ahmed > DBA > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Muqthar Ahmed > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > 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). > Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Service
ROW CACHE HIGH - Priority 1
Hi, I have Oracle 9.2.0.2.0 RAC (two nodes) on IBM AIX. Currently I am seeing very high number for ROW CACHE LOCK in statspack. Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- row cache lock11,310 5,44186.97 CPU time 522 8.34 global cache cr request32,513 711.14 global cache null to x 21,507 5791 log file sync22,689 49.78 - Get Spin & Latch Name Requests Misses SleepsSleeps 1->4 -- -- --- --- library cache 7,094,208 31,499 1,480 30031/1456/12/0/0 shared pool 2,385,408 6,739 527 6212/527/0/0/0 ges enqueue table freelist 1,492,275 1,903 1241780/122/1/0 /0 library cache pin 3,201,008 1,437 130 1307/130/0/0/0 row cache objects1,400,498 1,020 56 964/56/0/0/0 row cache enqueue latch 1,292,843 715 19 696/19/0/0/0 It is holding row cache lock (v$session_wait), other sessions are in the queuedue to this, number of concurrent sessions will increase from 100 to 350 sessions on each node. In less than one minute everything will be cleared ( OLTP database) I have already opened a TAR with Priority 1. Do you have any suggestions. Thanks Muqthar Ahmed DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
