How to Monitor and Analyze Oracle SQL Statements with Powerful Queries

Query currently executing SQL statements

select a.program, b.spid, c.sql_text, c.SQL_FULLTEXT, c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;

Query executed (historical) SQL statements

select b.SQL_TEXT, b.FIRST_LOAD_TIME, b.SQL_FULLTEXT
from v$sqlarea b
where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and '2009-10-15/09:24:47'
order by b.FIRST_LOAD_TIME;

(This method allows you to view SQL executed within a specific time window, with SQL_FULLTEXT containing the full statement.)

Query SQL statements that consume the most CPU

select *
from (
  select v.sql_id,
         v.child_number,
         v.sql_text,
         v.elapsed_time,
         v.cpu_time,
         v.disk_reads,
         rank() over (order by v.cpu_time desc) elapsed_rank
  from v$sql v
) a
where elapsed_rank <= 10;

Query SQL statements that consume the most disk I/O

select *
from (
  select v.sql_id,
         v.child_number,
         v.sql_text,
         v.elapsed_time,
         v.cpu_time,
         v.disk_reads,
         rank() over (order by v.disk_reads desc) elapsed_rank
  from v$sql v
) a
where elapsed_rank <= 10;

Query the slowest SQL statements

select *
from (
  select parsing_user_id, executions, sorts,
         command_type, disk_reads, sql_text
  from v$sqlarea
  order by disk_reads desc
) where rownum < 10;

Oracle query for sessions with uncommitted transactions

select a.sid, a.blocking_session, a.last_call_et, a.event,
       object_name,
       dbms_rowid.rowid_create(1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) "rowid",
       c.sql_text, c.sql_fulltext
from v$session a, v$sqlarea c, dba_objects, v$datafile
where a.blocking_session is not null
and a.sql_hash_value = c.hash_value
and ROW_WAIT_OBJ# = object_id
and file# = ROW_WAIT_FILE#;