RE: Reading/Interpreting 11g Statspack reports

<SNIP>

>> I am open to suggestions as to better tools besides
>> Statspack, keeping in mind that I'm a one-woman shop and
>> can't afford Enterprise software fees and I also don't run
>> Windows unless I absolutely have to. :)

the best way to determine performance troubles on a database is simple, trace the offending part of the application. I'm not on 11g yet, but in my 734, 8i, 9i and 10g databases I use one of the following depending on version:

• alter session set events '10046 trace name context forever, level 12'; (trace on) and alter session set events '10046 trace name context off'; (trace off)
• dbms_support.start_trace(waits => true, binds => true) (trace on) and dbms_support.stop_trace. [Needs you to run the ?/rdbms/admin/dbmssupp.sql script and grant execute, create public synonym etc as usual]

If I can't start a trace in the session, say because of third party code etc, then my plan of attack is:

• dbms_support.start_trace_in_session(sid => ??, serial# => ????, waits => true, binds => true) (trace on) and dbms_support.stop_trace_in_session(sid => ??, serial# => ????)
• create an after logion trigger, owned by SYS, which determines the correct session has logged in and runs 'alter session set events ...' using execute immediate to automagically start tracing when the session logs in. Very handy - make sure it fails safe though, if the trigger fails to execute then the session can't login. Trap exceptions, as in:

CREATE OR REPLACE TRIGGER sys.norms_logon_trigger AFTER LOGON
ON DATABASE
BEGIN
    IF (USER = 'WHATEVER' AND .....) THEN         execute immediate 'alter session set events ''10046 trace name context forever, level 12'' ';

    END IF;
EXCEPTION

• I know, I know, but in this case it is valid. Norm.
• If you find no trace files, comment this exception
• handler and try a manual login - fix the error that appears! WHEN others THEN NULL; END; /

The only problem is the inability to turn tracing off after the error/problem has been detected.

Once you have a trace file, Method R, or similar, will help, or:

grep "^WAIT" whatever.trc to list all the waits, look for large ela= numbers. (1 million = 1 second on 9i onwards)

grep for ^FETCH, ^EXEC, ^PARSE and see where you are spending time.

Two excellent books I would advise adding to your armoury:

Secrets Of The Oracle Database - Norbert Debes which has rather an excellent Perl based trace file analy[sz]er available for free download; Optimi[sz]ing Oracle Performance - Cary Milsap, Jeff Holt.

Both have good advice on using trace files to determine the actual causes of performance problems.

Statspack is fine, but, the figures you see are averaged out over the period between the snapshots, so the longer the period, the flatter the response times get. Also, until 11g, stats for jobs that started between the snapshots are not included in the output if the job finished AFTER the second snapshot - so you see no resource usage for any of those jobs and it might be one of those that is killing your system. Jobs that started before the first snapshot and finished before the second dump their stats into your report as well - so that 24 hour batch job that finished between your snapshots has gone and skewed the figures 'slightly'!

HTH Cheers,
Norman.

Norman Dunbar
Dunbar IT Consultants Ltd
Tel: 0779 3292 984
Tel: 0773 4531 439

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l