OTN Appreciation Day: Find performance issue for user session

In this post I would first like to thank OTN Community for helping me in connecting people and making every one get connected and learn. Also thanks to Tim Hall for setting all of us up for sharing some content with Oracle Community. So let me start my sharing. #ThanksOTN for all the tweets across OTN Appreciation Day.

In this post I am going to speak about the frequent issue which we have in a large database. We always used to see some long running PL/SQL programs or from a client session. Following are some of the ease of use dynamic views and queries to identify those.

1. In case of PL/SQL we first need to figure out which query or operation is taking long time.

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username       = USER; --Gets the current user details (Or application userid)

You can also check the progress of any long running quries

SELECT s.sid,

       s.serial#,

       s.machine,

       sl.opname,

       sl.target,

       sl.elapsed_seconds,

       sl.time_remaining,

       sl.sofar/sl.totalwork*100, 2) progress_percent

FROM   v$session s,

       v$session_longops sl

WHERE  s.sid     = sl.sid

AND    s.serial# = sl.serial#

AND    s.username = USER;  --Gets the current user details (Or application userid)

These 2 queries have helped me many times for tracking which issue is running long.