Diagnosing Oracle Database Performance

      RDBMS has been my area of interest for the last couple of years and mainly I have been working on improving performance of oracle database by tuning queries, writing sql scripts for load monitoring and killing blocking locks. I joined my project as a novice in database before two years and faced lot of challenges in maintaining a database centric application called Metasolv. I have compiled the steps involved in troubleshooting and diagnosing performance of an oracle database (DB).

Identify Users With Maximum Active Sessions

     Applications use to create sessions with database using drivers like JDBC/ODBC for executing the statements( queries, DDLs, DMLs etc). “Users” are DB objects used for creating sessions. We can identify the number of active sessions for every users connected to the DB using the following query.

select substr(s.username, 1, 18) username,
       substr(s.program, 1, 15) program,
       count(1) session_count
  from v$session     s,
       v$process     p,
       v$sql         q
where s.paddr = p.addr and
          s.status = 'ACTIVE' and
       s.sql_address = q.address
       and p.BACKGROUND is null -- exclude oracle process
group by substr(s.username, 1, 18) ,substr(s.program, 1, 15)

USERNAME             PROGRAM           SESSION_COUNT
------------------    ---------------         ----------------
KARTHI                sqlplusw.exe                1
MSLV                  DbUser                       8
REPORTS             busobj.exe                  2

      “PROGRAM” column gives us the client used by users for connection.”DbUser “ indicates  session created within the db itself. Pick the user with erratically high session count and drill down into minute details by following these steps.

Identifying Time Consuming Queries

   This step involves measurement of time taken by queries, which are executed by user we identified in the previous step. From result-set identify the maximum time consuming queries.

For example, lets take the “REPORTS” user for proceeding further.

select distinct v.sid,
                v.serial#,
                s.sql_text,
                trunc(v.last_call_et / 60, 2) minutes,
                trunc(v.last_call_et / 3600, 2) || 'Hr' Hours,
                v.logon_time
from   v$session v,
       v$sql     s
where  v.username = 'REPORTS' and
       v.status = 'ACTIVE' and
       v.sql_address = s.address
order  by 4 desc;

SID    SERIAL#  SQL_TEXT   MINUTES        HOURS          LOGON_TIME

----    -------      ---------      -------          -------       ---------------

940    19336        VIEW_SQL    0.15            0Hr     12/5/2008 12:01:27 PM

      The output shows the list of SQLs and the time, i.e how long the SQL is executing and the logon time. Please note the time in minutes and hour are same time but displayed in different units. I replaced the actual SQL by VIEW_SQL text to make the output legible. Pick the query with maximum minutes and tune it using different optimization techniques.

Performance from Lock Perspective

      Performance of databases can also be affected due to unavailability of resources. Exclusive lock is the primary reason for unavailability of resources. You can see a descriptive view on oracle locks, an older post from my blog. Still I would like to write down the method of finding waiting locks and its associated details like SQL, users involved, time taken, client details etc.

Identify the waiting locks using this query.

select s.osuser,  s.machine,  s.sid,s.serial#,  s.username, s.LOCKWAIT,
       trunc(s.last_call_et / 60, 3) minutes
from   (SELECT /*DECODE(request,0,'Holder: ','Waiter:') ||*/
         sid sess,id1,id2,lmode,request, type
        FROM   gv$lock
        WHERE  (id1, id2, type) IN (SELECT id1,
                                           id2,
                                           type
                                    FROM   gv$lock
                                    WHERE  request > 0)
        ORDER  BY id1,
                  request) inn,
       v$session s
where  inn.sess = s.sid;

       OSUSER    MACHINE     SID    SERIAL#    USERNAME    LOCKWAIT               MINUTES

-----------    ----------     ---    ------      ---------      --------------                   -------
    KARTHI   01HW165315     15     6463      MSLV                                         0.183
    KARTHI    01HW165315    471    19501    MSLV    7000001788E9010            0.133

      The result set shows that the session with sid 15 is locking an object which cause the session with sid 471 to wait for the same object. The session (15) is holding the object for 0.183 minutes and the session (471) is waiting for 0.133 minutes.

You can get more details of the session (SID) using the following query.

select do.object_name,
       dbms_rowid.rowid_create(1,
                               ROW_WAIT_OBJ#,
                               ROW_WAIT_FILE#,
                               ROW_WAIT_BLOCK#,
                               ROW_WAIT_ROW#) "rowid"
from   v$session   s,
       dba_objects do
where  s.sid in (15) and
       s.ROW_WAIT_OBJ# = do.object_id;

OBJECT_NAME                       rowid

-------------------         ------------------
ONE_STATUS_SERV    AAARRlAATAAAAAAAAA

      Using the object name and the rowid you can find out the row which has been locked. Reasoning the locks and its types will give us insight of the application’s architecture. After understanding the root cause of the lock you can make the necessary code or architecture changes to avoid locks and there by giving assured performance.

Note: I recon readers to refer oracle documentation for description of all data dictionary views. Also I request the readers to leave their comments on my post which would be a feedback (positive or negative) for me.