Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: statspack wait events

RE: statspack wait events

From: Raghu Kota (WBTQ) <>
Date: Tue, 10 Feb 2004 16:24:01 -0500
Message-ID: <>

Wow!!Thanks very much.

-----Original Message-----
[]On Behalf Of Tim Gorman Sent: Friday, February 06, 2004 10:54 AM To:
Subject: Re: statspack wait events


In general, while STATSPACK (and the V$ views from which it derives it's data) cannot directly identify the program modules which submitted the = SQL
identified in a "top 10" list. In general...

Oracle E-Business Suites (a.k.a. Oracle Apps) is one of the few (if not = the
only?) packaged applications that make use of the built-in DBMS_APPLICATION_INFO package to "register" SQL statements to specific programs. The idea is that, upon entry into a program, it should call = the
procedure DBMS_APPLICATION_INFO.SET_MODULE, which can be used to set the value of the columns MODULE (and optionally ACTION) in the V$SESSION and V$SQL views. The values of these columns are completely free-format -- = a
developer may set whatever string into them as they choose. The obvious intention is that MODULE would indicate the program module and that = ACTION
would indicate a "step" or "subprogram" within that program module. = When I
write stored procedures, I try to remember to always set a value into MODULE; if it is a large complex procedure, then I try to set ACTION at intervals throughout as well.

Oracle E-Business Suites programs and forms only set the MODULE value = and
(for some reason) do not set the ACTION value. Still, MODULE is duly recorded into the V$SQL view when SQL is parsed by the program module = and,
in due course of time, is captured into the STATSPACK repository when STATSPACK.SNAP is sampling the V$ views. Subsequently, it will then be displayed into the standard STATSPACK report along with the text of the = SQL

So, depending on whether DBMS_APPLICATION_INFO is in use in the = application
code, one might in fact be able to relate the SQL statement to a program module. From that information, one should be able to determine exactly which business process a captured SQL statement belongs.

Even without this direct link, it is possible to observe an "offensive" = or
resource-consuming SQL statement and use several techniques to research exactly which program module or user is executing it, to relate it back = to =3D
specific business process:

These will help one identify the source of problems related to a SQL statement. For problems related to a specific wait-event identified as =

5" in the STATSPACK report, one can likewise query for large values associated with that wait-event in the V$SESSION_EVENT view, in order to identify sessions (and therefore users or program modules) generates = large
amounts of that wait-event.

But perhaps the most important thing about using STATSPACK is to = completely
*disregard* reviewing the standard report generated by the =

script altogether. Don't look at it. Don=3DB9t waste time with it. As = Cary
has mentioned, this report can cause more damage than benefit because it presents huge amounts of information without any hint of priority or organization.

Instead, I strongly recommend that one generate the standard STATSPACK report *only* as input for the YAPP processor available on the
"" website. Use the ASCII text file from the =
STATSPACK report only to upload to OraPerf, to generate the excellent = YAPP
report in HTML. Then, spend time to review the HTML report which = organizes
the vast amount of information in an excellent manner according to the
"response-time analysis" YAPP methodology. For more information on =
the YAPP method with STATSPACK reports, please review the white papers available at ""...

Hope this helps...


on 2/4/04 1:19 PM, Raghu Kota (WBTQ) at wrote:

> Excellent answer Cary, Thank you so much! I will do further =3D3D > investigation what's happening in my system. I got more inspired by = =3D3D
> reading some of recent discussions by members, you and Tim(Capacity = =3D3D

> Planner from OEM VS Statspack). Thanks KL.
> -----Original Message-----
> From:
> []On Behalf Of Cary Millsap
> Sent: Wednesday, February 04, 2004 2:51 PM
> To:
> Subject: RE: statspack wait events
> Raghu,
> This kind of situation is what I meant by my "worse than useless" =
> last week... What you're showing *may* be an indication that you have > some SQL that would benefit from a reduction in OS read calls. = However,
> it is entirely possible that you also might *not* benefit from such a > reduction. If that's your situation, then you have an extra problem = now:
> getting the idea that you "have an I/O bottleneck" out of your head. = If
> your system does not have an I/O problem (and you can't tell by = looking
> at what you've shown here), then getting the wrong idea out of your = head
> is going to be an extra project step. In my experience, it's a project > step that sometimes takes MONTHS to execute. (Seeing this kind of = thing
> so frequently is what inspires great passion within me about this
> subject.)
> Your next step is to find some user action that's running for longer
> than the business needs it to run. Find out why that program is taking
> so long. Fix the program by reducing its demand for the thing it =
> the most time using. Once you've done that, if the program still isn't
> fast enough for the business, then reduce the demand that its
> competitors are generating for the resource your program is spending
> most of its time consuming. Check Chapter 1 of "Optimizing Oracle
> Performance" online (free) at
> Somebody will surely recommend that you consult listing of "top SQL" =
> the Statspack report you're looking at. By doing this, you'll probably
> find something to work on. However, Statspack has no idea how to sort
> your system's SQL statement by business priority, so it's possible =
> the guidance you'll receive from this Statspack report won't help you = at
> all.
> Cary Millsap
> Hotsos Enterprises, Ltd.
> * Nullius in verba *
> Upcoming events:
> - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 =
> - SQL Optimization 101: 2/16 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit for schedule details...
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> ----------------------------------------------------------------
> To unsubscribe send email to:
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> FAQ is at
> -----------------------------------------------------------------

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Feb 10 2004 - 15:24:01 CST

Original text of this message