Session Performance issue in Oracle DB
Most of the time end user complaining that the database is
slow as high performance is common expectation for end user. The database
itself is never slow or fast in most of the case session connected to the
database slow down when they receive unexpected hit. To resolve session
performance issue you need to identify unexpected hit and remove it. As we know
an oracle database is always one of the 3 states:
Idle: Waiting for the task.
Processing: Doing some useful task.
Waiting: Waiting for something, a block to come from
disk or lock to be released.
Sometimes the situation is session is waiting for resource
and another session trying to update that record and many other such scenarios.
Our goal is to find and eliminate that type of session.
Update pay_employee_personal_info
Set amount = 4000
Where employee_number = 5205;
Do not issue a commit after this update operation. That
means you are forcing the session to get and hold a lock on the first row of
the ‘pay_employee_personal_info’ table.
Now if you try the below update statement on the second
session. The statement will hang! The question why?
Update pay_employee_personal_info
Set amount = 5000
Where employee_number = 5205;
This is due to the first session holds a lock on the row,
which cause the second session to hang and the user to complain that the
session is slow.
To know exactly what the second session is doing join your query
with v$session_wait.
--Displays information on particular user session
waits.
SELECT
NVL(s.username, '(oracle)') AS username,
s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.sid = sw.sid and s.username = 'HRMS'
ORDER BY sw.seconds_in_wait DESC;
USERN SID SERIAL#EVENT WAIT_TIME
SECONDS STATE
------
--- ------ ------------------------ -------- ------- ----------
HRMS 53 6,581 SQL*Net message from
client 0 870 WAITED KNOWN TIME
HRMS 22 47,542 SQL*Net message from
client 0 633
WAITED KNOWN TIME
HRMS 18 21,757 SQL*Net message from
client 0 24 WAITED KNOWN TIME
HRMS 36 18,360 enq:TX - row lock contention0 12 WAITING
HRMS 34 18,633 SQL*Net message from
client 0 9 WAITING
If you don’t know exactly which user or Terminal causing
issues you can run your query to ask whole database session waits information
then gradually move for particular user or Terminal session wait information.
From the output you can see the users are connected with different application
such as Payroll software (HRMS), Oracle Financial software (ORAFIN), EDSS,
ITGFIN.
--Displays information on all database session
waits.
SELECT
NVL(s.username, '(oracle)') AS username,
s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.sid = sw.sid
ORDER
BY
sw.seconds_in_wait
DESC;
Select
SID,
osuser, machine, terminal,
logon_time, last_call_et
from v$session
where username = 'HRMS' AND TERMINAL = 'HR-RAFEQ';
If you study the output carefully for SID (53, 22, and 18)
which shows that it waited for some known amount of time earlier but now it is
working properly where as SID (36, 34) indicates that it is waiting for
something therefore it is not working. Why it is waiting for, you can check the
reason in EVENT column of the output. The EVENT column not only shows the
current waiting situation, also shows an EVENT session waited for earlier.
From the SID 36 output shows that session is waiting right
now for transaction level lock on row and session is still waiting to lock one
or more rows, but another session has already placed locks on the rows. Unless
that other session commits or rolls back its transaction, SID 36 will not
release the lock. You can also view the time since the session is waiting. A
very long wait usually indicates some sorts of performance bottleneck.
From the above output you can also see the session 34 is
idle but any complain regarding this session is not related to the session
performance. Check the other aspects of performance troubleshooting why it is
going through an infinite loop or high CPU consumption on the application
server.
From the below query you can get the information of system
identification as well as user information along with logon_time. It is
important for you to know which user or system is creating this issue.
--Displays system and user details with logon_time
for database sessions
SELECT
NVL(s.username, '(oracle)') AS username,
s.osuser, s.sid,
s.serial#, p.spid, s.lockwait,s.status,
s.module,s.machine, TO_CHAR(s.logon_Time,'DD-MON-YYYY
HH24:MI:SS') AS logon_time
FROM v$session s, v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;
Once you find the issue ‘a session is waiting for row lock’
it is important for you to find which session holds that lock.
To identify the locked row:
Select
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=36;
To
identify the lock object:
Select
owner, object_type, object_name, data_object_id
from dba_objects
where object_id = 145425;
To
find Lock session Text:
select
s.sid,
q.sql_text
from
v$sqltext q, v$session s
where
q.address
=
s.sql_address
and s.sid = &sid
order
by
piece;
Follow the link to find query on database
locks or who is blocking the session or blocker session details:
Once you find which session blocking the lock or which
session holds the lock, you need to find the SQL statements which cause issue.
--To find Lock session Text
select
address, s.sid, q.sql_text from v$sqltext q, v$session s
where
q.address
=
s.sql_address
and s.sid = &sid
order
by
piece;
Select
sql_text from v$sql
where address = '4AC67EE4';
Locking is not only the cause to effects the performance.
Another major case of contention is disk I/O. When a session retrieves data
from the database datafiles on disk to the buffer cache, it has to wait until
the disk sends the data. The wait event shows up for the session as “db file
sequential read” (for index scan) or “db file scattered read” (for full table
scan). You can find more related query on event details and I/O details by
clicking on the link: DB
Locks
When you see the event, you know that the session is waiting
for I/O from the disk to complete. To improve session performance, you have to
reduce that waiting period. There are several ways to reduce the wait. The exact
step depends on specific situation, but the first technique “reducing the
number of blocks retrieved by a SQL statement” almost always works.
–
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.
–
Place the tables used in the SQL
statement on a faster part of the disk.
–
Consider increasing the buffer cache to
see if the expanded size will accommodate the additional blocks, therefore
reducing the I/O and the wait.
–
Tune the I/O subsystem to return data
faster.
