Database Performance FAQ

Database Performance FAQ

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

**********************************************************************************************************

INVESTIGATING A DATABASE PERFORMANCE ISSUE

To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-

Is the performance problem constant or does it occur at certain times of the day ?

CONSTANT – Gather a few statspack reports for small periods of time when the problem occurs
CERTAIN TIMES – Gather a statspack report for a period of time when the problem exists

ADDITIONALLY gather a statspack report for a similar period of time when the problem does not occur for comparison.

NOTE:- As much as possible statspacks reports should be minimum 10 minutes, maximum 30 minutes. Longer periods can distort the infomation and reports should
be re-gathered usign a shorter time period.

Does the problem only affect one session, several sessions or all sessions ?

ONE SESSION – Gather 10046 trace for the session.
SEVERAL SESSIONS – Gather 10046 trace for one or two of the problem sessions
ALL SESSIONS – Gather statspack reports

Goes the database ”actually” hang or just ”appear” to hang?

(ie do sessions never complete their tasks (HANG or SPIN?) or do they it eventually finish (SLOW) )
HANG – Take some systemstates as well as a statspack report
SPIN? – See: Note 68738.1 No Response from the Server, Does it Hang or Spin?
SLOW – Gather 10046 for a selection of slow sessions.

Is the CPU usage high for one or more sessions when things run slowly ?

YES – Take some errorstacks from the suspect CPU process.
(* If unable to gather errorstacks then gather pstack reports)
**************************************************************************************************************

DIAGNOSTICS

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;
Also see:

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

INTERPRETING THE RESULTS/TRACES

Statspack reports – look at the Top 5 waiters section and work to reduce the time spent in the top waiter first, then regather a
statspack report and see what effect that has had.The following assumptions hold true:-

o Top waiter is IO/CPU -> Main issue is likely to be SQL tuning
o Top waiter is any other event -> Database performance issue

10046 traces – Run the 10046 trace through tkprof and look at the total time spent in SQL, then search back through the tkprof
report looking for a SQL Statement which takes up the most proportion of the report. Then look at the breakdown of time and wait
events for that SQL. Always remember that the ‘number of executions’ is important as although the time for a statement may be high
this may be accompanied by an equally high execution count. Assume the following:-

o If most of the time is spent in parsing there may be a parsing issue
o If the number of physical IOs is high then look at changing the access path of the query to do less work or increasing the
buffer cache to get buffers from memory rather than blocks from disk.
o If the wait events are enqueue related then generally this is an application design issue.
Determine the enqueue which is being waited for and address appropriately.

For further assistance see:-

Note 21154.1 EVENT 10046 “enable SQL statement tracing (including binds/waits)”
Note 39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
Systemstates – These should be sent to Oracle Support Services to interpret

Hanganalyze – These should be sent to Oracle Support Services to interpret

Errorstacks – These should be sent to Oracle Support Services to interpret

**************************************************************************************************************

TOP DATABASE PERFORMANCE ISSUES/PROBLEMS AND HOW TO RESOLVE THEM

Library Cache/Shared Pool Latch waits

Typically Library Cache/Shared Pool Latch waits is a contention problem caused by unshared SQL (in the case of the library cache latch), or exhaustion of
space in the shared pool (for the shared pool latch). For the shared pool latch, while new space allocations will require the latch it is typically the freeing
AND allocation of space through too small a shared pool which causes problem.

Note 62143.1 Understanding and Tuning the Shared Pool

High Version Counts

High version counts occur when there are multiple copies of the ‘same’ statement in the shared pool, but some factor prevents them from being shared wasting space
and causing latch contention.

Log File Sync waits

Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.

See: Note 34592.1 WAITEVENT: “log file sync” Reference Note

Buffer Busy waits/Cache Buffers Chains Latch waits

Buffer Busy waits occur when a session wants to access a database block in the buffer cache but it cannot as the buffer
is “busy”
Cache Buffers Chains Latch waits are caused by contention where multiple sessions waiting to read the same block.
Typical solutions are to look for SQL that accesses the blocks in question and determine if the repeated reads are
necessary.

Note 34405.1 WAITEVENT: “buffer busy waits” Reference Note
Note 42152.1 LATCH: CACHE BUFFERS CHAINS
Note 155971.1 Ext/Pub Resolving Intense and “Random” Buffer Busy Wait Performance Problems:
Note 163424.1 Ext/Pub How To Identify a Hot Block Within The Database Buffer Cache.:
Enqueue waits

TX – Note 62354.1 TX Transaction locks – Example wait scenarios
TM – Note 33453.1 REFERENTIAL INTEGRITY AND LOCKING

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

This Issue occurs when the database detects that a waiter had waited for a resource for longer than a particular
threshold. The message “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!” appears in the alert log and trace and
systemstates are dumped.

Typically this is caused by two (or more) incompatible operations being run simltaneously.
Note 278316.1 Potential reasons for “WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! ”
ORA-60 DEADLOCK DETECTED/enqueue hash chains latch

Note 62365.1 What to do with “ORA-60 Deadlock Detected” Errors
The reason ‘enqueue hash chains latch waits’ are here is that, typically, during deadlock detection (ie the
routine Oracle uses to determine if a deadlock actually exists), there is a heavy need for the latch which can
cause issues for other sessions.
If there is a problem with this latch, check if a trace file is generated for the ORA-60 and resolve that issue.

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em AWR, BUFFERS, FAST DIAG, PLSQL SCRIPTS, TRACE e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s