Database Performance FAQ – DIAGNOSTICS Part

Below is a summary from one of my favorite metalink notes; Note:402983.1 – Database Performance FAQ Last Revision Date: 15-MAR-2007. Purpose of this document is a number of Frequently Asked Database Performance Questions for all Oracle DBAs. And below is the full content of the note –

Database Performance FAQ
* INVESTIGATING A DATABASE PERFORMANCE ISSUE
* DIAGNOSTICS
o AWR reports/Statspack reports
o 10046 Trace
o Querying V$Session_wait
o System State Dumps
o Errorstack
o PSTACK
o PLSQL Profiler
o Hanganalyze
* INTERPRETING THE RESULTS/TRACES
* TOP DATABASE PERFORMANCE ISSUES/PROBLEMS AND HOW TO RESOLVE THEM
o Library Cache/Shared Pool Latch waits
o High Version Counts
o Log File Sync waits
o Buffer Busy waits/Cache Buffers Chains Latch waits
o Enqueue waits
o WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
o ORA-60 DEADLOCK DETECTED and enqueue hash chains latch

AWR reports/Statspack reports

AWR/Statspack reports provide a method for evaluating the relative performance of a database. In 10G, to check for general performance issues use the Automatic Workload Repository (AWR) and specifically the Automatic Database Diagnostic Monitor (ADDM) tool for assistance. This is covered in the following article: Note 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES

Note: If uploading reports to support, please ensure that they are in Text format
For 9i and 8i, statspack, rather than AWR, reports should be gathered. To gather a statspack report, please refer to: Note 94224.1 FAQ- Statspack Complete Reference

To interpret statspack output refer to:
http://www.oracle.com/technology/deploy/performance/pdf/statspack_tuning_otn_new.pdf

10046 Trace

10046 trace gathers tracing information about a session.

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events ‘10046 trace name context forever,level 12’;

— run the statement(s) to be traced —

select * from dual;
exit;

Querying V$Session_wait

The view V$Session_wait can show useful information about what a session is waiting for.
Multiple selects from this view can indicate if a session is moving or not. When wait_time=0 the session is waiting, any other value indicates CPU activity:

set lines 132 pages 999
column event format a30

select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;

See: Note 43718.1 VIEW “V$SESSION_WAIT” Reference Note

** Important ** – v$session_wait is often misinterpreted. Often people will assume we are waiting because see an event and seconds_in_wait is rising. It should be remembered that seconds_in_wait only applies to a current wait if wait_time =0 , otherwise it is actually “seconds since the last wait completed”. The other column of use to clear up the misinterpretation is state which will be WAITING if we are waiting and WAITED% if we are no longer waiting

Finding session id

This select is useful for finding the current session information for tracing later:

select p.pid,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.audsid = userenv(‘SESSIONID’)
/

System State Dumps

If the database is hung then we need to gather systemstate dumps to try to determine what is happening. At least 3 dumps should be taken as follows:

Login to sqlplus as the internal user:

sqlplus “/ as sysdba”

rem — set trace file size to unlimited:

alter session set max_dump_file_size = unlimited;

alter session set events ‘10998 trace name context forever, level 1’;
alter session set events ‘immediate trace name systemstate level 10’;
alter session set events ‘immediate trace name systemstate level 10’;
alter session set events ‘immediate trace name systemstate level 10’;

or

sqlplus “/ as sysdba”

alter session set max_dump_file_size = unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
alter session set events ‘immediate trace name systemstate level 266’;
alter session set events ‘immediate trace name systemstate level 266’;

If no connection is possible at all then please refer to the following article which describes how to collect systemstates in that situation: Note 121779.1 – Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.

Errorstack

Errorstack traces are Oracle Call Stack dumps that can be used to gather stack information for a process. Attach to the process and gather at least 3 errorstacks:

login to SQL*Plus:

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3
oradebug dump errorstack 3
oradebug dump errorstack 3

PSTACK

Pstack is an operating system tool that can be used to gather stack information on some unix platforms. Attach to the process and gather about 10 pstacks while the job is running.

% script pstacks.txt
% /usr/proc/bin/pstack pid
% exit

The PID is the o/s process id of the process to be traced. Repeat the pstack command about 10 times to capture possible stack changes. Further details of pstack are in: Note 70609.1 How To Display Information About Processes on SUN Solaris

PLSQL Profiler

The PL/SQL profiler provides information abour PL/SQL code with regard to CPU usage and other resource usage information. See: Note 243755.1 Implementing and Using the PL/SQL Profiler

Hanganalyze

Hanganalyze is often gathered for hang situations. Typically systemstates are more useful. The following describes how to gather hanganalyze dumps: Note 175006.1 Steps to generate HANGANALYZE trace files.

Advertisement