Perth, Australia
+ (61) 417713124

Performance Tuning Tips and Techniques

  • Posted by Gavin Soorma
  • On July 17, 2009
  • hang, iostat, lock, Performance Tuning, sar, slow response, top, top sql, v$lock, vmstat, wait event

Checks to be performed at the machine level (note the example is Red Hat Linux specific)

run queue should be ideally not more than the number of CPU’s on the machine

At the maximum it should never be more than twice the number of CPU’s.

This is denoted by the column ‘r’ in the vmstat output shown below

vmstat – 5

face:/opt/oracle>vmstat 5
procs                      memory      swap          io     system         cpu
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 4  1 488700 245704 178276 12513572    0    1    10    17   48  1365 40 12 43  5
 2  0 488700 302568 178312 12514904    0    0   157   686 3354  4342 55 22 22  1
 3  1 488700 257500 178508 12517896    0    0   616   221 1352  2132 21  7 64  9
 2  2 488700 232348 178820 12525392    0    0  1550   274 3632  6091 29  9 42 20
 3  0 488700 225040 178880 12527336    0    0   346   452 2494  3300 45 13 38  4
 5  0 488700 225680 178916 12527884    0    0    79   269 2005  3847 29 11 58  2

CPU idle% < 10 ( id column) could indicate a machine that is having CPU resource issues

Note: How to find number of CPU’s on a LINUX machine?

cat /proc/cpuinfo |grep -w “processor” |wc –l

Swap columns si and so should ideally be 0 to indicate no swapping activity

face:/opt/oracle>free -m
             total       used       free     shared    buffers     cached
Mem:         15635      15476        159          0        173      12196
-/+ buffers/cache:       3106      12528
Swap:         3999        477       3522

We should be looking at the free and used values in the row denoted by
“-/+ buffers/cache”

The ‘top’ command will help us identify the load average on the machine as well as any process that is consuming excessive CPU

09:04:01  up 243 days, 13:01, 10 users,  load average: 2.80, 3.14, 3.13
813 processes: 797 sleeping, 8 running, 8 zombie, 0 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total   40.7%    0.0%   12.7%   0.0%     0.3%    2.3%   43.7%
           cpu00   42.8%    0.0%   13.4%   0.3%     1.1%    1.9%   40.0%
           cpu01   37.3%    0.0%   12.6%   0.0%     0.0%    1.7%   48.2%
           cpu02   47.5%    0.0%   14.1%   0.0%     0.1%    1.1%   36.9%
           cpu03   35.1%    0.0%   10.5%   0.0%     0.0%    4.5%   49.7%

Mem:  16010560k av, 15808688k used,  201872k free,       0k shrd,  181324k buff
                   11368660k actv, 2257548k in_d,  265628k in_c
Swap: 4095984k av,  488700k used, 3607284k free                 12551192k cache

24062 oracle    18   0 1342M 1.3G 1328M S    14.0  8.5 370:13   2 oracle
 6129 oracle    18   0  958M 953M  923M R    12.3  6.0 172:14   0 oracle
10077 oracle    25   0  149M 149M  145M R     4.5  0.9   0:00   2 oracle
 9924 oracle    16   0  156M 155M  150M S     2.6  0.9   0:00   2 oracle
10038 oracle    17   0  140M 140M  136M S     2.5  0.8   0:00   1 oracle
10055 oracle    24   0  136M 135M  132M S     2.4  0.8   0:00   0 oracle
25529 oracle    17   0  782M 782M  775M S     1.7  5.0   3:58   0 oracle
 8245 oracle    16   0  274M 274M  268M S     1.6  1.7   0:03   1 oracle
 9751 oracle    16   0 70484  67M 62952 S     1.3  0.4   0:01   2 oracle
31879 oracle    16   0  830M 828M  815M R     1.0  5.2  46:06   3 oracle
 9210 oracle    15   0  229M 229M  225M S     0.7  1.4   0:01   3 oracle

A load average greater than 5 or 10 could indicate a heavily utilized machine

CPU information for each CPU is also provided via the top command as well as information on the physical as well as virtual Memory that is available on the machine.

Information on the top CPU as well as Memory consuming processes is also provided along with the Process ID (PID). In a later section we will use this PID as a parameter for a SQL query to identify the SQL being executed by the same CPU consuming process.

Identify if any single PID is constantly appearing in the top output

The iowait column can also help us identify if there s any resource contention from the IO perspective.

A value above 40-50% would indicate I/O resource issues and require further investigation into the process that is causing this high I/O – or it could indicate a case of inefficient disk sub system or file layout in the database.

We can also view the state of the machine at a particular time of the day by running the sar command which will provide the system utilisation report since 12:00 AM on that particular day.

We can use the sar command to identify the machine state even for a particular day of the month

For example sar -f /var/log/sa/sa03 will report for the 3rd of the month

Checks to be performed at the database level

Identify with the user if the problem is a slow response or a hanging situation.

Establish a connection via SQL*NET using a non SYSDBA account to confirm that the listener is accepting client connections and the hanging is not due to the archive area getting 100% full.

Examine the alert log file for ‘Checkpoint Not Completed’ errors recorded at the time the performance problem is reported – this could indicate an I/O contention issue or inadequately sized redo log files which can also cause an application hang while the checkpoint completes.

Ensure that the mount point on which the Oracle software is not 100% full or the disks holding the controlfiles are also not 100% full.

Check the listener.log file if it exists and ensure that it is not > 2GB – on some Operating Systems like LINUX, there is a file size upper limit for the listener.log file after which client connections will not be accepted by the listener.

Check for locked sessions (see script check_lock.sql).

If a PID has been identified as a top CPU consuming process, check the SQL being executed by that particular PID (see script check_pid_sql.sql)

If the user provides a particular SID where a possible performance issue exists, check the SQL being executed by that SID ( see script check_sid_sql.sql)

If the user provides a particular Oracle username where a possible performance issue exists, check the SQL being executed by that Oracle user ( see script check_username_sql.sql)

Very Important – check the major wait events in the database (see script wait_events.sql)

Check the SID along with the events that each SID is waiting on (see script wait_events_sid.sql) – based on the SID, the SQL being executed by the waiting sessions can be obtained as well ( see script check_sid_sql.sql)

Refer document Resolving common Oracle Wait Events using the Wait Interface for details on important wait events along with their possible causes and resolution.

Check for any sessions continuously waiting for on a particular latch (see script check_latch.sql) and also refer document “Resolving common Oracle Wait Events using the Wait Interface.doc”.

What has changed?

Is there a measurable baseline regarding the “problem” query – when did it last perform well?

Has the database been upgraded recently?

Has any modifications been done to the database in terms of init.ora parameters?

Have any new indexes been added to the table or has the table structure changed?

Has the platform or database version changed?

Is this a period of unusual business activity? – like a monthly data load or one-off batch job



set long 500000
set head off
       s.username su,
       substr(sa.sql_text,1,540) txt
from v$process p,
     v$session s,
     v$sqlarea sa
where    p.addr=s.paddr
and      s.username is not null
and      s.sql_address=sa.address(+)
and      s.sql_hash_value=sa.hash_value(+)
and spid=;


PROMPT Current SQL statement this session executes
col sql_text for a70 hea "Current SQL"
select q.sql_text
from v$session s
,    v$sql     q
WHERE s.sql_address = q.address
and   s.sql_hash_value + DECODE
                 (SIGN(s.sql_hash_value), -1, POWER( 2, 32), 0) = q.hash_value
AND   s.sid= ;


set long 500000
       s.username su,
       substr(sa.sql_text,1,540) txt
from v$process p,
     v$session s,
     v$sqlarea sa
where    p.addr=s.paddr
and      s.username is not null
and      s.sql_address=sa.address(+)
and      s.sql_hash_value=sa.hash_value(+)
and s.username=upper('&username');


set linesize 500
col waiting_session format 99999 heading 'Waiting|Session'
col holding_session format 99999 heading 'Holding|Session'
col mode_held format a20 heading 'Mode|Held'
col mode_requested format a20 heading 'Mode|Requested'
col lock_type format a20 heading 'Lock|Type'
prompt blocked objects from V$LOCK and SYS.OBJ$

set lines 132
col BLOCKED_OBJ format a35 trunc

select /*+ ORDERED */
,   l.lmode
,   TRUNC(l.ctime/60) min_blocked
,||'.'||o.NAME blocked_obj
from (select *
      from v$lock
      where type='TM'
      and sid in (select sid
                  from v$lock
                  where block!=0)) l
,     sys.obj$ o
,     sys.user$ u
where o.obj# = l.ID1
and   o.OWNER# = u.user#

prompt blocked sessions from V$LOCK

select /*+ ORDERED */
   blocker.sid blocker_sid
,  blocked.sid blocked_sid
,  TRUNC(blocked.ctime/60) min_blocked
,  blocked.request
from (select *
      from v$lock
      where block != 0
      and type = 'TX') blocker
,    v$lock        blocked
where blocked.type='TX'
and blocked.block = 0
and blocked.id1 = blocker.id1

prompt blockers session details from V$SESSION

set lines 132
col username format a10 trunc
col osuser format a12 trunc
col machine format a15 trunc
col process format a15 trunc
col action format a50 trunc

,      serial#
,      username
,      osuser
,      machine
FROM v$session
WHERE sid IN (select sid
      from v$lock
      where block != 0
      and type = 'TX')


SELECT count(*), event FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN
('smon timer','pmon timer','rdbms ipc message',
'SQL*Net message from client')


col username format a12
col sid format 9999
col state format a15
col event format a45
col wait_time format 99999999
set pagesize 800
set linesize 800
select s.sid, s.username, se.event
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by 3;


select count(*), name latchname from v$session_wait, v$latchname
where event='latch free' and state='WAITING' and p2=latch#
group by name order by 1 desc;



Leave Reply

Your email address will not be published. Required fields are marked *