RE: what do I tune?
-Original Message- Sent: Tuesday, July 24, 2001 1:22 PM To: Multiple recipients of list ORACLE-L Gene: It all depends on a couple of factors: (1) what are the average wait times for each of the wait events, (2) which one(s) have the highest wait times. I would ignore any waits less than 1 centisecond for the most part. Buffer Busy Waits occur when a session is waiting for a buffer to become available. This is because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is in the buffer cache, but in an incompatible mode(that is, some other session is changing the buffer). There are several courses of action here, depending on what type of block it is: -- If it is a data block, change the pctfree and pctused or, in the case of an index, check for right-hand indexing or increase initrans. The key is to reduce the number of rows/leaves per block to reduce contention. -- If it is a segment header, increase the number of freelists or use freelist groups. -- If it is a freelist block, increase the number of freelists. -- If it is an undo header block, add more rollback segments when in exclusive mode and consider setting transactions per rbs = 1. -- If it is an undo block, add more rollback segments when in exclusive mode or make the segments you have larger. DB file sequential read waits indicate that either (a) an index lookup is being performed or (b) a controlfile is being rebuilt or (c) datafile headers are being dumped or retrieved. In your case, it's probably the first one. Enqueue waits are waits for locks to be released. Taking care of those can be quite complex depending on the types of locks being held and those being requested and on what structures, etc. Latch free waits are waits for another to release a latch on a given resource. The presence of latch free waits of any significant magnitude may indicate a bottleneck within the SGA. It seems obvious that when you turned up the degree of parallelism on the query, the database as you currently have it set up could not handle the load. All these wait events would be consistent with that. Hope this helps. Jon Walthour --- Original Message --- From: Gene Gurevich [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 7/24/01 11:56:29 AM Hi all: I am trying to tune some queries. I ran them all in one stream (in sequence) and then ran them in several (up to 64) parallel streams. I got a snapshot of the system for each run. I see some of the waits went up significantly when I switched from 2 parallel streams to 64: buffer busy wait, db file seq read, enqueue waits, latch free waits and many more. Now some of this increases may be OK, some may be not. My question is how do I decide which of these waits are a problem and should be looked into and which are normal and can be safely ignored. Are there any quantative rules that I could use? thank you for any insight Gene = __ 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: Gene Gurevich 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Horne, Joe 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
RE: what do I tune?
What do you want to achieve? The more parallel you run the more some of your waits will go up, but the elapsed time may drop a little or a lot. Also you need to balance it with the total load on your server. If it is a query that is run a lot when there are a lot of users on the system you could max out your server. If it is during a down time for batch, that may not be a problem. Also keep track of your explain plan as you bump up parallelism, it can change. -Original Message- From: Gene Gurevich [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject: what do I tune? Hi all: I am trying to tune some queries. I ran them all in one stream (in sequence) and then ran them in several (up to 64) parallel streams. I got a snapshot of the system for each run. I see some of the waits went up significantly when I switched from 2 parallel streams to 64: buffer busy wait, db file seq read, enqueue waits, latch free waits and many more. Now some of this increases may be OK, some may be not. My question is how do I decide which of these waits are a problem and should be looked into and which are normal and can be safely ignored. Are there any quantative rules that I could use? thank you for any insight Gene = __ 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: Gene Gurevich 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: Page, Bruce 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: what do I tune?
Gene: It all depends on a couple of factors: (1) what are the average wait times for each of the wait events, (2) which one(s) have the highest wait times. I would ignore any waits less than 1 centisecond for the most part. Buffer Busy Waits occur when a session is waiting for a buffer to become available. This is because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is in the buffer cache, but in an incompatible mode(that is, some other session is changing the buffer). There are several courses of action here, depending on what type of block it is: -- If it is a data block, change the pctfree and pctused or, in the case of an index, check for right-hand indexing or increase initrans. The key is to reduce the number of rows/leaves per block to reduce contention. -- If it is a segment header, increase the number of freelists or use freelist groups. -- If it is a freelist block, increase the number of freelists. -- If it is an undo header block, add more rollback segments when in exclusive mode and consider setting transactions per rbs = 1. -- If it is an undo block, add more rollback segments when in exclusive mode or make the segments you have larger. DB file sequential read waits indicate that either (a) an index lookup is being performed or (b) a controlfile is being rebuilt or (c) datafile headers are being dumped or retrieved. In your case, it's probably the first one. Enqueue waits are waits for locks to be released. Taking care of those can be quite complex depending on the types of locks being held and those being requested and on what structures, etc. Latch free waits are waits for another to release a latch on a given resource. The presence of latch free waits of any significant magnitude may indicate a bottleneck within the SGA. It seems obvious that when you turned up the degree of parallelism on the query, the database as you currently have it set up could not handle the load. All these wait events would be consistent with that. Hope this helps. Jon Walthour --- Original Message --- From: Gene Gurevich [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 7/24/01 11:56:29 AM Hi all: I am trying to tune some queries. I ran them all in one stream (in sequence) and then ran them in several (up to 64) parallel streams. I got a snapshot of the system for each run. I see some of the waits went up significantly when I switched from 2 parallel streams to 64: buffer busy wait, db file seq read, enqueue waits, latch free waits and many more. Now some of this increases may be OK, some may be not. My question is how do I decide which of these waits are a problem and should be looked into and which are normal and can be safely ignored. Are there any quantative rules that I could use? thank you for any insight Gene = __ 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: Gene Gurevich 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: what do I tune?
Hi, itrprof SQL Analyzer can do what you asked. itrprof: http://www.unal-bilisim.com/products/itrprof/itrprof.html 24/7/01 06:56:29, Gene Gurevich [EMAIL PROTECTED] wrote: Hi all: I am trying to tune some queries. I ran them all in one stream (in sequence) and then ran them in several (up to 64) parallel streams. I got a snapshot of the system for each run. I see some of the waits went up significantly when I switched from 2 parallel streams to 64: buffer busy wait, db file seq read, enqueue waits, latch free waits and many more. Now some of this increases may be OK, some may be not. My question is how do I decide which of these waits are a problem and should be looked into and which are normal and can be safely ignored. Are there any quantative rules that I could use? thank you for any insight Gene = __ 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: Gene Gurevich 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: Danisment Gazi Unal (Unal Bilisim) 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).
