Performance Tuning Tips and Techniques
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
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
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
Appendix
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 face:/opt/oracle>top
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
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
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
Appendix
check_pid_sql.sql
SET PAGESIZE 500
set long 500000
set head off
select
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=;
check_sid_sql.sql
SET PAGESIZE 500
PROMPT=============================================================
PROMPT Current SQL statement this session executes
PROMPT=============================================================
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= ;
check_username_sql.sql
set long 500000
SET PAGESIZE 500
select
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');
check_lock.sql
set linesize 500
SET PAGESIZE 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.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||'.'||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
SELECT sid
, serial#
, username
, osuser
, machine
FROM v$session
WHERE sid IN (select sid
from v$lock
where block != 0
and type = 'TX')
;
wait_events.sql
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')
GROUP BY event ORDER BY 1 DESC
;
wait_events_sid.sql
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;
check_latch.sql
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;
