Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 19 March 2013

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;
------ ---   ------ ------------------------    --------  ------- ----------
HRMS   53     6,581  SQL*Net message from client0         870   WAITED KNOWN TIME
HRMS   22     47,542 SQL*Net message from client0     ‎    633   WAITED KNOWN TIME
HRMS   18     21,757 SQL*Net message from client0         24   WAITED KNOWN TIME
HRMS   36     18,360 enq:TX - row lock contention0         12   WAITING
HRMS   34     18,633 SQL*Net message from client0         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 Locks : Blockers
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. 


  1. Really Good One.

    Mujtaba Khan

    1. Thanks for your appreciation.

    2. Hello Shahid,
      really your work is appreciated, I need one help from your side. Could you write the AWR report document? means how to read the AWR report? what are the parameter we need to check with nominal percentage and if percentage is low or high what should be done..
      your help will be appreciated.


    3. Dear,

      I have already written related documents on this topic. Try to use the search option on the blog. For your convenient
      check the below link:
      If you search you will find some more related article.

    4. Hello Shahid,
      Thanks a lot providing me the valuable resource, In my case oracle CPU is 100% from AWR report it seems everything is fine. I just wanna know, which area I should see to find the cause.