DOITLIKEITSYOURJOB - Whats Running?

Whats Running?

SET LINESIZE 200SET PAGESIZE 50
SELECT s.sid, s.serial#, s.username, s.program, p.spid AS os_process_id, s.logon_time, s.status, s.state, s.event, s.blocking_session, s.cpu_time / 1000 AS cpu_time_in_sec, s.block_gets, s.consistent_gets, s.physical_reads, s.sql_id, sql.sql_textFROM v$session sJOIN v$process pON s.paddr = p.addrLEFT JOIN v$sql sqlON s.sql_id = sql.sql_idWHERE s.type = 'USER' -- Exclude background processesORDER BY s.logon_time DESC;

WTF is going on...

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, sql_exec_start, q.sql_fulltext, b.name AS bind_variable_name, b.value_string AS bind_valueFROM v$session sJOIN v$sqlarea q ON s.sql_id = q.sql_idLEFT JOIN v$sql_bind_capture b ON s.sql_id = b.sql_idWHERE 1=1--AND s.username = '' --AND s.osuser <> ''--AND s.type = 'USER' --AND s.status = 'ACTIVE' --AND s.osuser = '' )ORDER BY s.sid, b.position;

1. View Active User Sessions

This query shows details about currently active user sessions, including session ID, username, machine, and the SQL text being executed:

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, q.sql_textFROM v$session sJOIN v$sql q ON s.sql_id = q.sql_idWHERE 1=1 AND s.type = 'USER' AND s.status = 'ACTIVE'ORDER BY s.sid;

2.Full Text

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, q.sql_fulltextFROM v$session sJOIN v$sqlarea qON s.sql_id = q.sql_idWHERE 1=1 AND s.type = 'USER' AND s.status = 'ACTIVE'ORDER BY s.sid;

2.Bind Capture

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, q.sql_fulltext, b.name AS bind_variable_name, b.value_string AS bind_valueFROM v$session sJOIN v$sqlarea q ON s.sql_id = q.sql_idLEFT JOIN v$sql_bind_capture b ON s.sql_id = b.sql_idWHERE 1=1 AND s.type = 'USER' AND s.status = 'ACTIVE'ORDER BY s.sid, b.position;

Track Long-Running Operations

This query shows the progress of long-running operations such as materialized view refreshes. It calculates the percentage of work completed.

SELECT sid, serial#, opname, context, sofar, totalwork, ROUND((sofar / totalwork) * 100, 2) AS "%_COMPLETE"FROM v$session_longopsWHERE 1=1 AND opname LIKE 'REFRESH MATERIALIZED VIEW%' AND totalwork > 0 AND sofar != totalworkORDER BY "%_COMPLETE" DESC;

SELECT sid, serial#, context, sofar, totalwork, ROUND((sofar/totalwork)*100,2) "%_COMPLETE"FROM v$session_longopsWHERE opname LIKE 'REFRESH MATERIALIZED VIEW%'AND totalwork != sofar;

--STATS
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'SCHEMA_NAME', cascade => TRUE );END;/