Tuesday 24 March 2015

Oracle Queries to See Executing SQL

A couple of queries to see what is executing on the database.

The first uses v$sqltext to query executing SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vst.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) runtime FROM v$session vs, v$sqltext vst WHERE vs.status = 'ACTIVE' AND vs.username IS NOT NULL AND vs.sql_address = vst.address AND vs.sql_hash_value = vst.hash_value AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username, vst.piece ;
This one uses v$sqlarea and will get PL/SQL and SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vsa.optimizer_mode, vsa.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) last_call FROM v$session vs, v$sqlarea vsa WHERE vs.sql_hash_value = vsa.hash_value AND vs.sql_address = vsa.address AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username ;
Done.