Beginning Performance
Tuning
Resolve session performance issues in Oracle Database.
ORACLE DATABASE
It’s the middle of the night, and you get a frantic call from someone complaining
that the database is slow. The caller demands
to know why—and what you’re going to do
about it. Sound familiar? If it does, you are not
alone. High performance is a common expectation of database system users: they get very
unhappy when they don’t get it, and they are
usually not shy about letting you know. What
should you do next? In this article, you will
learn some techniques for troubleshooting
Oracle Database performance issues.
To use the scripts in this article, you need
to create some tables in a test schema and
access some dynamic performance views.
The database user SYS has all privileges to
access the views, so you need the password
for the SYS user. The script for setting up
the example tables is available in the online
version of this article, at bit.ly/Iv wqLE.
SESSION STATE
Before you start troubleshooting why a database is slow, you have to first understand that
the database itself is never slow or fast—it
has a constant speed. The sessions connected
to the database, however, slow down when
they hit a bump in the road. To resolve a
session performance issue, you need to identify the bump and remove it. Fortunately, it’s
very easy to do this in most cases.
The first step to resolving a session performance issue is to ascertain what that database session is doing now. An Oracle Database
session is always in one of three states:
1. Idle. Not doing anything—just waiting to
be given some work.
2. Processing. Doing something useful—
running on the CPU.
3. Waiting. Waiting for something, such
as a block to come from disk or a lock to
be released.
If a session is waiting to be given work
(idle), it’s really not slow at all—it just has
nothing to do. If a session is waiting for
some resource, such as a block or a lock, it
has stopped processing. Until it gets that
resource, the session will continue to wait.
When it gets that resource, it does some
processing and then moves on to the next
resource it needs, waits for that to be avail-
able, and starts processing . . . and the cycle
goes on until the session has nothing else to
do. If it waits for resources often, the session
will appear slow. But it’s not really slow—it’s
just following a pattern of go, stop, go again,
stop again, and so on. Your mission is to find
and eliminate the “stop” issues in the session.
update t1
set col2 = 'x' where col1 = 1;
The output will show “ 1 row updated,”
indicating that the row was updated. Do
not issue a COMMIT after the statement. By
not committing, you will force the session
to get and hold a lock on the first row of the
T1 table. Now go to the second session and
issue the following SQL statement:
update t1
set col2 = 'y'
where col1 = 1;
This statement will hang. Why? The
answer is simple: the first session holds a lock
on the row, which causes the second session
to hang and the user to complain that the
session is slow. To know what the second
session is doing, the first thing you need to
check is the S TATE column in V$SESSION:
select sid, state
from v$session
where username = 'ARUP';
SID STATE
————— ———————————————————————
3346 WAITING
2832 WAITED KNOWN TIME
Study the output carefully. Session 3346
(in the SID column) indicates that it is
waiting for something—and therefore not
working. That should be your first clue that
the session is experiencing one of those performance bumps in the road. But before you
can determine what the session is waiting
for, let’s study the state of session 2832 in
the output, which shows that it waited for
some known amount of time earlier. The
important point is that session 2832 is not
waiting for anything right now, meaning
that it’s working productively.
Next, let’s see what the second session
(3346) is waiting for. That information is
readily available in the EVENT column in the
same V$SESSION view. The EVENT column
not only shows an event a session is waiting
for currently, but also shows an event a session
has waited for earlier. The query against
V$SESSION in Listing 1 displays information
from the EVENT column for both sessions.
