Logo Background

Oracle DB Monitoring Script

  • By on August 22, 2008 | 2 Comments

    These is a set of script that can be useful when perform an Oracle database health check

    Monitor Oracle DB SQL

    --Active Session Info
    SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser,
    b.machine, b.status,b.logon_time, c.sql_text
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND c.hash_value = b.sql_hash_value
    AND b.status = 'ACTIVE'
    ORDER BY a.spid, c.piece
    --Active Table Locking
    SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
    c.sql_text,b.logon_time, e.owner, e.object_name "Table Lock"
    FROM
    v$session b,
    v$sqltext c,
    v$locked_object d,
    dba_objects e
    WHERE b.sql_address = c.address
    AND b.sid = d.session_id
    AND d.object_id = e.object_id
    AND b.status = 'ACTIVE'
    ORDER BY b.sid, c.piece
    --Gather Statistics Summary
    SELECT owner,TABLE_NAME,last_analyzed,sample_size,num_rows FROM all_tables
    WHERE owner = 'INV'
    ORDER BY last_analyzed DESC
    --Cache Hit Ratio
    SELECT ROUND(((1-(SUM(DECODE(name,
    'physical reads', VALUE,0))/
    (SUM(DECODE(name, 'db block gets', VALUE,0))+
    (SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
    || '%' "Buffer Cache Hit Ratio"
    FROM v$sysstat;
    --Hit Ratio By User
    SELECT Username,
    OSUSER,
    Consistent_Gets,
    Block_Gets,
    Physical_Reads,
    100*( Consistent_Gets + Block_Gets - Physical_Reads)/
    ( Consistent_Gets + Block_Gets ) "Hit Ratio %",
    V$SESSION.SID, serial#, STATUS
    FROM V$SESSION ,V$SESS_IO
    WHERE V$SESSION.SID = V$SESS_IO.SID
    AND ( Consistent_Gets + Block_Gets )>0
    AND username IS NOT NULL
    ORDER BY Username,"Hit Ratio %";
    Previous
    Next
    » Oracle Database Performance Tuning
  1. #1 Jing Hong
    October 11, 2008 9:57 pm

    SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser, b.machine, b.status,b.logon_time, c.sql_text
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND c.hash_value = b.sql_hash_value
    AND b.status = ‘ACTIVE’
    AND a.spid =
    ORDER BY a.spid, c.piece

    You may use this to monitor the Oracle session based on Unix PID value…

    Post ReplyPost Reply
  1. #2 zenux
    November 28, 2008 2:52 am

    Added state and event of the ACTIVE SQL for better troubleshooting

    SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser, b.machine, b.status,b.logon_time, c.sql_text, b.state, b.event
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND c.hash_value = b.sql_hash_value
    AND b.status = ‘ACTIVE’
    ORDER BY a.spid, c.piece

    Post ReplyPost Reply
Leave a Comment