Session Performance issue in Oracle tuning

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 contention‎0‎         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: 

Find 

1. Locks 
2. Blockers
3. Finding oracle Database locks

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:

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.