Re: Freelist Contention
Jonathan, OK, what am I missing? All of the PX sessions have to access the segment header in order to get blocks off the free lists so can't that result in buffer busy waits on the segment header unless it was created with more than one free list group? -- Anita --- Jonathan Lewis [EMAIL PROTECTED] wrote: Not only that, PX slaves do direct reads anyway, bypassing the buffer cache. It is possible, though, that the flush that has to take place before the PX scan can read back is sufficient to cause other processes to wait for write complete waits, and I guess that you could also get extra rbs header waits as the flushed and scanned blocks are rolled back by the PX slaves for read consistency. Excessive serial tablescans, on the other hand can easily cause significant buffer busy waits. Jonathan Lewis 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 See http://www.jlcomp.demon.co.uk/book_rev.html For latest news of public appearances See http://www.jlcomp.demon.co.uk Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 21 August 2001 18:32 |On Tuesday 21 August 2001 08:21, Johnson Poovathummoottil wrote: | Bufer busy waits can also be caused by parallel query | servers trying to read the same buffer block at the | same time. | |Just off the top of my head, and without giving it a lot |of thought, this doesn't sound right. | |PQS divvy up the blocks to read, and should not be trying |to access the same block. | |Got any documentation or evidence to back that up? | |Jared -- 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). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: Freelist Contention
The original comment was about PQ slaves reading data blocks - not PX slaves running parallel update/inserts. When PX slaves do parallel inserts the processing is usually split to avoid contention - e.g. each slaves gets one partition of a partitioned object; or each slave gets a new extent above the HWM of an existing data segment; so I don't think PX slaves would have that issue. (which can, of course, be an issue with 'ordinary' highly concurrent processes). Jonathan Lewis 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 See http://www.jlcomp.demon.co.uk/book_rev.html For latest news of public appearances See http://www.jlcomp.demon.co.uk Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 23 August 2001 13:42 |Jonathan, | |OK, what am I missing? All of the PX sessions have to |access the segment header in order to get blocks off |the free lists so can't that result in buffer busy |waits on the segment header unless it was created with |more than one free list group? | |-- Anita | |--- Jonathan Lewis [EMAIL PROTECTED] |wrote: | | Not only that, PX slaves do direct reads | anyway, bypassing the buffer cache. | | It is possible, though, that the flush | that has to take place before the PX | scan can read back is sufficient to | cause other processes to wait for | write complete waits, and I guess that | you could also get extra rbs header | waits as the flushed and scanned blocks | are rolled back by the PX slaves for | read consistency. | -- 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: Freelist Contention
Raja: You will know you have freelist contention if you have a significant buffer busy waits ratio (5%). Jon Walthour -Original Message- Luthra Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Jon Walthour 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: Freelist Contention
Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. So does it always indicate a freelist problem? . --- Jon Walthour [EMAIL PROTECTED] wrote: Raja: You will know you have freelist contention if you have a significant buffer busy waits ratio (5%). Jon Walthour -Original Message- Luthra Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Jon Walthour 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: Freelist Contention
You have to learn to listen to your database, grasshoppa'. A good alternative to the Buddhist and kung fu techniques is the v$waitstat table. If you see accumulating time for 'extent map' or 'free list', then you know that you have to rebuild the table with more free lists. To see the actual segments you'll have to use v$session_wait, v$access, v$bh and some other beautiful v$ tables. -Original Message- From: Viraj Luthra [mailto:[EMAIL PROTECTED]] Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Subject: Freelist Contention Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Gogala, Mladen 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: Freelist Contention - PQ slaves
Title: RE: Freelist Contention - PQ slaves I thought that parallel query slaves were smart enough to divide up the work between them so there was no overlap? What am I missing? Can you elaborate? -Original Message- From: Johnson Poovathummoottil [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 11:21 To: Multiple recipients of list ORACLE-L Subject: RE: Freelist Contention Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. So does it always indicate a freelist problem? . --- Jon Walthour [EMAIL PROTECTED] wrote: Raja: You will know you have freelist contention if you have a significant buffer busy waits ratio (5%). Jon Walthour -Original Message- Luthra Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Jon Walthour 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: RE: Freelist Contention
No. Just because you have a high bbw ratio, that doesn't indicate strictly a freelist problem. However, having problems with freelists often results in a high bbw ratio. Jon Walthour From: Johnson Poovathummoottil [EMAIL PROTECTED] Date: 2001/08/21 Tue AM 11:21:02 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Freelist Contention Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. So does it always indicate a freelist problem? . --- Jon Walthour [EMAIL PROTECTED] wrote: Raja: You will know you have freelist contention if you have a significant buffer busy waits ratio (5%). Jon Walthour -Original Message- Luthra Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Jon Walthour 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: Jon Walthour 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: Freelist Contention
On Tuesday 21 August 2001 08:21, Johnson Poovathummoottil wrote: Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. Just off the top of my head, and without giving it a lot of thought, this doesn't sound right. PQS divvy up the blocks to read, and should not be trying to access the same block. Got any documentation or evidence to back that up? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Freelist Contention - PQ slaves
Another thing that comes to mind is incorrectly tuned DBWR, which cannot keep up. -Original Message- From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 2:31 PM To: Multiple recipients of list ORACLE-L Subject: RE: Freelist Contention - PQ slaves Lisa is right. PQ slaves operate on different set of blocks and would not be contending for the same blocks. - Kirti -Original Message- From: Koivu, Lisa [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 11:42 AM To: Multiple recipients of list ORACLE-L Subject:RE: Freelist Contention - PQ slaves I thought that parallel query slaves were smart enough to divide up the work between them so there was no overlap? What am I missing? Can you elaborate? -Original Message- From: Johnson Poovathummoottil [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 11:21 To: Multiple recipients of list ORACLE-L Subject:RE: Freelist Contention Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. So does it always indicate a freelist problem? . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Gogala, Mladen 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: Freelist Contention
Not only that, PX slaves do direct reads anyway, bypassing the buffer cache. It is possible, though, that the flush that has to take place before the PX scan can read back is sufficient to cause other processes to wait for write complete waits, and I guess that you could also get extra rbs header waits as the flushed and scanned blocks are rolled back by the PX slaves for read consistency. Excessive serial tablescans, on the other hand can easily cause significant buffer busy waits. Jonathan Lewis 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 See http://www.jlcomp.demon.co.uk/book_rev.html For latest news of public appearances See http://www.jlcomp.demon.co.uk Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 21 August 2001 18:32 |On Tuesday 21 August 2001 08:21, Johnson Poovathummoottil wrote: | Bufer busy waits can also be caused by parallel query | servers trying to read the same buffer block at the | same time. | |Just off the top of my head, and without giving it a lot |of thought, this doesn't sound right. | |PQS divvy up the blocks to read, and should not be trying |to access the same block. | |Got any documentation or evidence to back that up? | |Jared -- 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: Freelist Contention - PQ slaves
What happens when you join a large parllelized table with a very small table (contained in just 10 or 12 blocks) the retrieval of rows from the large table looks up the small table causing buffer busy waits on the blocks of the small table. Keeping the small table in keep pool removes the buffer busy waits. --- Koivu, Lisa [EMAIL PROTECTED] wrote: I thought that parallel query slaves were smart enough to divide up the work between them so there was no overlap? What am I missing? Can you elaborate? -Original Message- From: Johnson Poovathummoottil [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 11:21 To: Multiple recipients of list ORACLE-L Subject:RE: Freelist Contention Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. So does it always indicate a freelist problem? . --- Jon Walthour [EMAIL PROTECTED] wrote: Raja: You will know you have freelist contention if you have a significant buffer busy waits ratio (5%). Jon Walthour -Original Message- Luthra Sent: Monday, August 20, 2001 11:45 PM To: Multiple recipients of list ORACLE-L Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: Jon Walthour 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: Freelist Contention
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 21, 2001 9:48 AM On Tuesday 21 August 2001 08:21, Johnson Poovathummoottil wrote: Bufer busy waits can also be caused by parallel query servers trying to read the same buffer block at the same time. Just off the top of my head, and without giving it a lot of thought, this doesn't sound right. PQS divvy up the blocks to read, and should not be trying to access the same block. Got any documentation or evidence to back that up? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: arunc 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: Freelist Contention
Yeah, like I said, I wasn't thinking too hard about it. :) Jared Jonathan Lewis [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mon.co.ukcc: Sent by: Subject: Re: Freelist Contention [EMAIL PROTECTED] 08/21/01 11:46 AM Please respond to ORACLE-L Not only that, PX slaves do direct reads anyway, bypassing the buffer cache. It is possible, though, that the flush that has to take place before the PX scan can read back is sufficient to cause other processes to wait for write complete waits, and I guess that you could also get extra rbs header waits as the flushed and scanned blocks are rolled back by the PX slaves for read consistency. Excessive serial tablescans, on the other hand can easily cause significant buffer busy waits. Jonathan Lewis 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 See http://www.jlcomp.demon.co.uk/book_rev.html For latest news of public appearances See http://www.jlcomp.demon.co.uk Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 21 August 2001 18:32 |On Tuesday 21 August 2001 08:21, Johnson Poovathummoottil wrote: | Bufer busy waits can also be caused by parallel query | servers trying to read the same buffer block at the | same time. | |Just off the top of my head, and without giving it a lot |of thought, this doesn't sound right. | |PQS divvy up the blocks to read, and should not be trying |to access the same block. | |Got any documentation or evidence to back that up? | |Jared -- 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: 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: Freelist Contention
Raja, You will only have freelist contention on conventional insert operations only. Insert operations from direct loads won't cause freelist contention because of the way direct inserts are done. You can check for freelist contention by checking V$SESSION_WAIT and look for the Event 'Buffer Busy Waits'. If you see you have these waits you will need to look at the P1, P2 and P3 columns to determine what block is causing the wait as well as the type of wait. P1 - Absolute file # P2 - Block # P3 - ID ID's 8.1.6 =8.1.6 00 Block being read 1003100 Want to new the block but is being read (maybe for undo) 1007200 Want to new the block but another session is holding it (possibly changing the block) 1010230 Trying to get a cr on the buffer but a session modifying the buffer has not completed. 1012 A modification is happening on SCUR or XCUR (OPS or RAC) but has not completed 1012231 dup of 1010 1013130 block is being read another session and no other suitable image was found so we wait until the read is complete. 1014110 We want the current block but someone else is currently reading into the cache. 1014120 duplicate 1016210 The session wants the block in SCUR or XCUR mode. 1016220 Duplicate of 1014 If you join p1 and p2 to dba_segments columns Header_file and Header_block and you get match on the join chances are you have a freelist problem. If p1 and p2 are datablocks then the block could be waiting on reads. If the waits are for reads chances are you are scanning unselective indexes. If the waits are for modifications then you need to add more inittrans or reduce the number of rows in the block. Reducing the number of rows in the block can be achieved by using pctfree/pctused or min records per block(8i feature). Data block waits could also be caused by multiple process inserting into an index at the index value. If it is undo header then add more rollback segments. If it is undo blocks then you might try to make the rollback segment bigger. BBW's are cause by a session waiting for another session to complete a read or the buffer is in incompatible mode. The blocker may be modifying the block we want or may be waiting on another wait operation (typically IO). The ID above should help show which. If we are in a buffer busy wait state and there is no other session which is causing this wait then possible causes are spinning process with buffer lock. Oracle will normally wait 1 second and if it has to wait again for an exclusive buffer it will wait 3 seconds. Scott --- Viraj Luthra [EMAIL PROTECTED] wrote: Hello folks, How do I come to know that there is a contention going on in the freelists? Is there any v$ table or this type of contention you derive from some other info. like a ripple effect from some parameter? TIA. raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
