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,
from v$session s,
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,
trunc(v.last_call_et / 60, 2) minutes,
trunc(v.last_call_et / 3600, 2) || 'Hr' Hours,
from v$session v,
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
WHERE (id1, id2, type) IN (SELECT id1,
WHERE request > 0)
ORDER BY id1,
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.
from v$session s,
where s.sid in (15) and
s.ROW_WAIT_OBJ# = do.object_id;
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.