August 6, 2007

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 –

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

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:

** 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:

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:

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

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.