RE: Reading/Interpreting 11g Statspack reports
- From: "Dunbar, Norman" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <janine@xxxxxxxxxx>, "oracle-l L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Mar 2010 09:29:25 -0000
Hi Janine,
<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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
