Performance Monitoring SQL

It will Show sessions along with operations which is doing full table scan or any sort operation which may consume lots of resources.

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, START_TIME,

ROUND(SOFAR/TOTALWORK*100,2) COMPLETE

FROM   V$SESSION_LONGOPS

WHERE SOFAR <> TOTALWORK;

Through the below sql we need to find out the queries which is currently running for more than 60 seconds.

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

       SID,  

       MACHINE,

       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT

  FROM V$SESSION SES,  

       V$SQLtext_with_newlines SQL

 where SES.STATUS = 'ACTIVE'

   and SES.USERNAME is not null

   and SES.SQL_ADDRESS    = SQL.ADDRESS

   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

order by runt desc, 1,sql.piece;

Show sessions holding a TX lock:

    SELECT * FROM v$lock vl, v$session vs, v$sqltext vt

     WHERE vl.TYPE = 'TX'

       AND vl.lmode > 0

       AND vl.sid = vs.sid

       AND vs.SQL_ADDRESS    = vt.ADDRESS

       AND vs.SQL_HASH_VALUE = vt.HASH_VALUE

Show sessions waiting for a TX lock:

    SELECT * FROM v$lock vl, v$session vs, v$sqltext vt

     WHERE vl.TYPE = 'TX'

       AND vl.request > 0

       AND vl.sid = vs.sid

       AND vs.SQL_ADDRESS    = vt.ADDRESS

       AND vs.SQL_HASH_VALUE = vt.HASH_VALUE