which means that the session made a SQL
call 18 seconds ago.
The other sessions are waiting. SID 3089
is waiting for a row lock. From the output,
you can see that the session has been
waiting for 146 seconds and that it also
made its last SQL call 146 seconds ago. This
indicates that the session has been waiting
for that particular lock ever since it made
that SQL call.
Finally, session 2832 is also waiting; in this
case, it is waiting with a “SQL*Net message
from client” event, which means it is idle,
waiting to be given some work. The session
issued its last SQL statement 152 seconds ago
and has been idle for 151 seconds.
Armed with this information, you can
diagnose performance issues very accurately.
You can tell the complaining user that of the
three sessions connected from the appsvr1
application server, one session is idle, one
is working, and one is waiting for a lock. The
user is probably referring to the slowness of
this last session. Now you know the reason
and how you can rectify it.
GET TING THE SQL
Another key piece of performance tuning
information is the SQL statement a session is
executing, which will provide more insights
into the workings of the session. The same
V$SESSION view also shows the SQL statement information. The SQL_ID column in
the V$SESSION view shows the ID of the
last SQL statement executed. You can get
the text of that SQL statement from the
V$SQL view, using the SQL_ID value. Here
is an example of how I have identified the
SQL statement executed by the session that
appears slow to the user.
select sql_id
from v$session
where sid = 3089;
SQL_ID
—————————————————
g0uubmuvk4uax
set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
—————————————————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1
DATA ACCESS ISSUES
I have used row-level locking as the cause of
a slowdown in this article. Although locking-related contention is a very common cause,
it is not the only cause of performance problems. Another major cause of contention is
disk I/O. When a session retrieves data from
the database data files on disk to the buffer
cache, it has to wait until the disk sends the
data. This wait shows up for that session as
“db file sequential read” (for index scans) or
“db file scattered read” (for full-table scans)
in the EVENT column, as shown below:
select event
from v$session
where sid = 3011;
EVENT
———————————————————————————————
db file sequential read
When you see this event, you know that
the session is waiting for I/O from the disk to
complete. To make the session go faster, you
have to reduce that waiting period. There are
several ways to reduce the wait:
1. Reduce the number of blocks retrieved
by the SQL statement. Examine the SQL
statement to see if it is doing a full-table
scan when it should be using an index,
if it is using a wrong index, or if it can be
rewritten to reduce the amount of data
it retrieves.
2. Place the tables used in the SQL statement on a faster part of the disk.
3. Consider increasing the buffer cache
to see if the expanded size will accommodate the additional blocks, therefore
reducing the I/O and the wait.
4. Tune the I/O subsystem to return data
faster.
The online article at bit.ly/IvwqLE includes
additional information on how to address
performance issues related to disk I/O.
CONCLUSION
In summary, this article presented the fol-
lowing steps for starting a successful perfor-
mance tuning session:
Arup Nanda (arup@
proligence.com) has been
an Oracle DBA for more
than 16 years, handling
all aspects of database
administration, from
performance tuning to security and disaster
recovery. He was Oracle Magazine’s DBA of the
Year in 2003.
NEXT STEPS
READ online-only article content
bit.ly/IvwqLE
READ more about performance tuning
Oracle Database 2 Day + Performance Tuning
Guide 11g Release 2 ( 11. 2)
bit.ly/IlXslg
Oracle Database Performance Tuning Guide 11g
Release 2 ( 11. 2)
bit.ly/IBPsBW
