RE: cache buffer chains contention
Title: RE: cache buffer chains contention Would it seem likely for Oracle to be doing ANYTHING for a full 30 seconds without hitting another wait? Matt Adams - GE Appliances - [EMAIL PROTECTED] Reason is 6/7ths of treason. - The Xtals -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: cache buffer chains contention seconds_in_wait increments every three seconds, so if State is anything other than WAITING the column tells you how much time has passed since the last wait completed. (Contrary to the urban legend that says the value is meaningless). There are various anomalies and oddities about wait events, though, and if you session REALLY is doing nothing for 30 seconds, then you've hit one; however normally I would say that your session is busy doing something (from Oracle's perspective) in that 30 seconds. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 10 April 2002 20:56 |Ok, some guru please explain this to me. | |process is waiting for 'latch free' according to |v$session_wait and the value of P2 = 26 |which is the cache buffer chains latch. |(while holding the HW enqueue, with a bunch of |processes waiting on HW) | |State = 'WAITED KNOWN TIME', so I look at |WAIT_TIME, which is 2. | |However, the process does not appear to be doing |ANYTHING and the seconds in the SECONDS_IN_WAIT |column is increasing! This continues for about |30 seconds, when finally it finishes whatever |it was doing and allows the next process to |grab the HW enqueue | |Why would SECONDS_IN_WAIT be increasing when it supposed to be |done waiting already? | | | | |Matt Adams - GE Appliances - [EMAIL PROTECTED] |Reason is 6/7ths of treason. - The Xtals | -- 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: cache buffer chains contention
Yep, Using CPU or being re-scheduled by the OS. Anjo. "Adams, Matthew (GEA, 088130)" wrote: Would it seem likely for Oracle to be doing ANYTHING for a full 30 seconds without hitting another wait? Matt Adams - GE Appliances - [EMAIL PROTECTED] Reason is 6/7ths of treason. - The Xtals -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 6:54 PM To: Multiple recipients of list ORACLE-L Subject: Re: cache buffer chains contention seconds_in_wait increments every three seconds, so if State is anything other than WAITING the column tells you how much time has passed since the last wait completed. (Contrary to the urban legend that says the value is meaningless). There are various anomalies and oddities about wait events, though, and if you session REALLY is doing nothing for 30 seconds, then you've hit one; however normally I would say that your session is busy doing something (from Oracle's perspective) in that 30 seconds. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]> Date: 10 April 2002 20:56 |Ok, some guru please explain this to me. | |process is waiting for 'latch free' according to |v$session_wait and the value of P2 = 26 |which is the cache buffer chains latch. |(while holding the HW enqueue, with a bunch of |processes waiting on HW) | |State = 'WAITED KNOWN TIME', so I look at |WAIT_TIME, which is 2. | |However, the process does not appear to be doing |ANYTHING and the seconds in the SECONDS_IN_WAIT |column is increasing! This continues for about |30 seconds, when finally it finishes whatever |it was doing and allows the next process to |grab the HW enqueue | |Why would SECONDS_IN_WAIT be increasing when it supposed to be |done waiting already? | | | | |Matt Adams - GE Appliances - [EMAIL PROTECTED] |Reason is 6/7ths of treason. - The Xtals | -- 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: cache buffer chains contention
It's certainly possible for Oracle to be pushed into very heavy CPU usage, particularly for PX slaves, but even in serial queries. Two common 'causes' are queries with correlated sub-queries against small tables; and queries which have been over-indexed and hinted to avoid table-scans. Bottom line, though, is that if the session does not appear to be in an Oracle-recorded wait state, it is either using (or scheduled to use) CPU, or you've hit a wait state that isn't instrumented properly. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -- 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: cache buffer chains contention
seconds_in_wait increments every three seconds, so if State is anything other than WAITING the column tells you how much time has passed since the last wait completed. (Contrary to the urban legend that says the value is meaningless). There are various anomalies and oddities about wait events, though, and if you session REALLY is doing nothing for 30 seconds, then you've hit one; however normally I would say that your session is busy doing something (from Oracle's perspective) in that 30 seconds. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 10 April 2002 20:56 |Ok, some guru please explain this to me. | |process is waiting for 'latch free' according to |v$session_wait and the value of P2 = 26 |which is the cache buffer chains latch. |(while holding the HW enqueue, with a bunch of |processes waiting on HW) | |State = 'WAITED KNOWN TIME', so I look at |WAIT_TIME, which is 2. | |However, the process does not appear to be doing |ANYTHING and the seconds in the SECONDS_IN_WAIT |column is increasing! This continues for about |30 seconds, when finally it finishes whatever |it was doing and allows the next process to |grab the HW enqueue | |Why would SECONDS_IN_WAIT be increasing when it supposed to be |done waiting already? | | | | |Matt Adams - GE Appliances - [EMAIL PROTECTED] |Reason is 6/7ths of treason. - The Xtals | -- 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: cache buffer chains contention
Have a look of the hot blocks. The most probable reason for cache buffer chains are SQLs with bad performance. Don't discard contention on higher level latches as redo allocation. So to look for those hot blocks, use one of the S.Adams scripts: hot_hash_blocks.sql ( www.ixora.com.au) and these one too: select hladdr "LATCH ADDRESS", dbafil "FILE#", dbablk "BLOCK#", state, gets, lc.sleeps from x$bh bh, v$latch_children lc where lc.addr=bh.hladdr and state!=0 and sleeps1 order by sleeps; Then search for the objects in the dba_extents and then run after the SQLs with top_ten stmts similar to the app of materialdreams or the ones from S.Adams site. Again from S.Adams site you can read some answers of these problems. Regards. -Mensaje original- De: elain he [SMTP:[EMAIL PROTECTED]] Enviado el: mircoles 28 de febrero de 2001 0:31 Para: Multiple recipients of list ORACLE-L Asunto: cache buffer chains contention Does anyone have any advice on reducing cache buffer chains latch contention? From v$latch_children, I found the children with the highest sleeps and narrowed that to two tables. A method of reducing the contention is probably to reduce the number of records per block but that would also mean that now there will be more blocks to read. Is there any other ways of reducing the contention? I've tried increasing/reducing _db_block_hash_buckets but that did not help. db_block_buffers=40960 _db_block_hash_buckets=db_block_buffers/4 db_block_size=8K tables' pctfree=30, pctused=40 thanks. elain _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: elain he 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: Trassens, Christian 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).
