| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: statspack wait events
Wow!!Thanks very much.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tim Gorman
Sent: Friday, February 06, 2004 10:54 AM
To: oracle-l_at_freelists.org
Subject: Re: statspack wait events
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 =3D
a
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 =
"to=3D
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=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
"http://www.oraperf.com" website. Use the ASCII text file from the =
standar=3D
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 =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. >=3D20 > -----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 >=3D20 >=3D20 > Raghu, >=3D20 > This kind of situation is what I meant by my "worse than useless" =posts
> so frequently is what inspires great passion within me about this > subject.) >=3D20 > 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.=3D3D20 >=3D20 > 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
> all. >=3D20 >=3D20 > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * >=3D20 > 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... >=3D20 > ---------------------------------------------------------------- > 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
-- 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 Tue Feb 10 2004 - 15:24:01 CST
 |
 |