RE: Wait Event Calculation
Erik, You can detect all the unaccounted-for time in 10046 data (and v$ data too, but not nearly so easily). It turns out that the ability to measure this "gap" is exactly what you need to positively identify the types of problems you've described. See www.hotsos.com/dnloads/1/kevents/unaccounted-for.html for details, including a specific case we've solved with 10046 data that was exactly the "not enough CPU/memory" case. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Jul 9-11 New York City - Hotsos Clinic, Jul 23-25 Chicago - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas -Original Message- Sent: Saturday, June 15, 2002 7:18 AM To: Multiple recipients of list ORACLE-L Erik, The doc may be old but the method still stands ;-) You can dice and slice the wait events in anyway you want. But basically what you are interested in is the wait events for the foreground (system wide), so you do some thing like this: System wide: v$system_event - (v$session_event for the background processes). Then you should consider a couple of events like: SQL*net message from client pipe get etc. Session level The only thing to consider here is the wait events listed before. Now we have the big BUT: What about waits that are not accounted for ? Like OS waits (waiting for a memory page, waiting for CPU, etc). How can you see them ? And how do know where the problem is ? So you will get a level of detail that will contain errors/holes. And as long as the holes are not big enough, it doesn't matter. But what if you are really running out of CPU ? The hole will get bigger and your wait events don't really matter that much. I will make a list of idle events available through oraperf.com. That list is used by the report generator also. Anjo. Erik Williams wrote: > I am trying to break the response time of my system into the wait and > service times. I am able to determine the service time by the "CPU used by > this session" line in the statspack report. The wait time I am having > trouble with. I am working from the 'Yet Another Performance Profiling > Method' document. The document lists a number of events that should not be > included in the calculation because they are caused by background process > waits. I am tuning an 8.1.6 databases and want to be sure I am only counting > the appropriate events in this calculation. How can I get the list of events > to include for my version of the database (the doc is quite old)? > > Thanks > Erik > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Erik Williams > 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: Cary Millsap 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: Wait Event Calculation
Erik, The doc may be old but the method still stands ;-) You can dice and slice the wait events in anyway you want. But basically what you are interested in is the wait events for the foreground (system wide), so you do some thing like this: System wide: v$system_event - (v$session_event for the background processes). Then you should consider a couple of events like: SQL*net message from client pipe get etc. Session level The only thing to consider here is the wait events listed before. Now we have the big BUT: What about waits that are not accounted for ? Like OS waits (waiting for a memory page, waiting for CPU, etc). How can you see them ? And how do know where the problem is ? So you will get a level of detail that will contain errors/holes. And as long as the holes are not big enough, it doesn't matter. But what if you are really running out of CPU ? The hole will get bigger and your wait events don't really matter that much. I will make a list of idle events available through oraperf.com. That list is used by the report generator also. Anjo. Erik Williams wrote: > I am trying to break the response time of my system into the wait and > service times. I am able to determine the service time by the "CPU used by > this session" line in the statspack report. The wait time I am having > trouble with. I am working from the 'Yet Another Performance Profiling > Method' document. The document lists a number of events that should not be > included in the calculation because they are caused by background process > waits. I am tuning an 8.1.6 databases and want to be sure I am only counting > the appropriate events in this calculation. How can I get the list of events > to include for my version of the database (the doc is quite old)? > > Thanks > Erik > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Erik Williams > 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).
RE: Wait Event Calculation
As I mentioned a few weeks ago, IMHO, singling out events to ignore in a system-wide or even session-wide data collection is the wrong thing to do. But you must ignore some events if you first make what I consider to be errors in how you collect your data. If you collect data for only the problem program during only the problem duration, then you should ignore *none* of the events that you see. If you collect the data this way, then *everything* you see will be a component of end-user response time. Any other data collection method is misleading (and time-wasting) in many cases; one example of such a case is the rather longish example I described. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: 2003 Hotsos Symposium on Oracle® System Performance, February 912 Dallas Hotsos Clinic, July 911 New York City Hotsos Clinic, July 2325 Chicago -Original Message- Williams Sent: Friday, June 14, 2002 11:38 AM To: Multiple recipients of list ORACLE-L I am trying to break the response time of my system into the wait and service times. I am able to determine the service time by the "CPU used by this session" line in the statspack report. The wait time I am having trouble with. I am working from the 'Yet Another Performance Profiling Method' document. The document lists a number of events that should not be included in the calculation because they are caused by background process waits. I am tuning an 8.1.6 databases and want to be sure I am only counting the appropriate events in this calculation. How can I get the list of events to include for my version of the database (the doc is quite old)? Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams 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: Cary Millsap 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).
Wait Event Calculation
I am trying to break the response time of my system into the wait and service times. I am able to determine the service time by the "CPU used by this session" line in the statspack report. The wait time I am having trouble with. I am working from the 'Yet Another Performance Profiling Method' document. The document lists a number of events that should not be included in the calculation because they are caused by background process waits. I am tuning an 8.1.6 databases and want to be sure I am only counting the appropriate events in this calculation. How can I get the list of events to include for my version of the database (the doc is quite old)? Thanks Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams 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).