Re: statspack wait events

From: Tim Gorman <tim_at_sagelogix.com>
Date: Fri, 06 Feb 2004 08:53:36 -0700
Message-ID: <BC490690.FC70%tim@sagelogix.com>

Raghu,

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 statement.

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 = a
specific business process:

Using the HASH_VALUE captured along with the SQL statement, one can research usage patterns of the SQL statement over time, using the history of information captured in the STATS$SQL_SUMMARY table. Using the SQL*Plus script "sphistory.sql" posted at "http://www.EvDBT.com/tools.htm" can help with this technique...
Again, using HASH_VALUE, query for matches in the V$SESSION view column SQL_HASH_VALUE or PREV_HASH_VALUE. Any hits should lead one to examine the session involved much more closely as to the identity of the user or program module.

These will help one identify the source of problems related to a SQL statement. For problems related to a specific wait-event identified as "to= p
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 "spreport.sql" script altogether. Don't look at it. Don=B9t 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 "http://www.oraperf.com" website. Use the ASCII text file from the standar= d
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 using the YAPP method with STATSPACK reports, please review the white papers available at "http://www.oraperf.com/whitepapers.html"...

Hope this helps...

-Tim

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

> Excellent answer Cary, Thank you so much! I will do further =3D
> investigation what's happening in my system. I got more inspired by =3D
> reading some of recent discussions by members, you and Tim(Capacity =3D
> Planner from OEM VS Statspack). Thanks KL.

>=20

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
> Sent: Wednesday, February 04, 2004 2:51 PM
> To: oracle-l_at_freelists.org
> Subject: RE: statspack wait events

>=20
>=20

> Raghu,

>=20

> This kind of situation is what I meant by my "worse than useless" posts
> 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.)

>=20

> 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 spends
> 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
> http://www.oreilly.com/catalog/optoraclep/index.html.=3D20

>=20

> Somebody will surely recommend that you consult listing of "top SQL" in
> 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 that
> the guidance you'll receive from this Statspack report won't help you at
> all.

>=20
>=20

> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *

>=20

> Upcoming events:
> - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle
> - SQL Optimization 101: 2/16 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...

>=20


> ----------------------------------------------------------------

> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 06 2004 - 09:53:36 CST