​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Useful V$ (dynamic performance) views in Oracle


The V$ family of views that Oracle maintains gives an insight into what is happening in the database at any one time. These views are dynamic which means that Oracle is updating them the whole time that the instance is running. A side effect of this is that there is loads of important information contained in the views that are extremely useful . Some examples are shown below.

 

1)      List what SQL is running now

 

select v$session.sid

                ,v$session.serial#

                ,v$session.username

                ,v$session.sql_id

                ,v$session.sql_child_number

                ,optimizer_mode

                ,hash_value

                ,address

                ,sql_text

       from   v$sqlarea

                ,v$session

      where  v$session.sql_hash_value = v$sqlarea.hash_value

      and    v$session.sql_address    = v$sqlarea.address

     and    v$session.username       is not null

 

2)      List long running queries


SELECT s.username,

       sl.sid,

       sq.executions,

       sl.last_update_time,

       sl.sql_id,

       sl.sql_hash_value,

       opname,

       target,

       elapsed_seconds,

       time_remaining,

       sq.sql_fulltext

  FROMv$session_longopssl

 INNER JOIN v$sqlsqONsq.sql_id=sl.sql_id

 INNER JOIN v$session s ONsl.SID=s.SIDANDsl.serial# =s.serial#

 WHEREtime_remaining>0

 

    3)      Find locked objects

SELECT

        DECODE(vl.request,0,'Holder: ','Waiter: ') || vl.sidsess,

        vs.osuser,

        vs.machine

        substr(vp.spid,1,9) pid,

        o.object_name

FROM 

        V$LOCK vl

        v$sessionvs,

        v$processvp,

        dba_objects o

WHERE 

        (vl.id1, vl.id2, vl.type) IN (

  SELECT id1, id2, type FROM V$LOCK WHERE request>0) 

and

        o.object_id  = vl.id1 

and

        vp.addr=vs.paddr

and␊

        vs.sid=vl.sid

ORDER BY ␊

        vl.id1, vl.request

/


    4)      List what SQL run at some point in the past


If the query text has not been aged out of the shared pool the following is useful

 

   selectv.SQL_TEXT,

            v.PARSING_SCHEMA_NAME,

            v.FIRST_LOAD_TIME,

            v.DISK_READS,

            v.ROWS_PROCESSED,

            v.ELAPSED_TIME,

            v.service

       fromv$sql v

 whereto_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss') between

 to_date('10-mar-14 15:09','dd-mon-yy hh24:mi')

and

to_date('10-mar-14 15:19','dd-mon-yy hh24:mi')

 

   

     5)      List Blocking sessions


SELECT s1.username ||'@'|| s1.machine
    ||' ( SID='|| s1.sid ||' )  is blocking '
    || s2.username ||'@'|| s2.machine ||' ( SID='|| s2.sid ||' ) 'ASblocking_status
    FROMv$lock l1,v$session s1,v$lock l2,v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1AND l2.request >0
    AND l1.id1 = l2.id1
    AND l2.id2 =l2.id2 ;