snippetModerate

How to find the latest SQL statements within the database?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thelatesthowsqlstatementswithindatabasefind

Problem

I like to get the latest executed statements within my database, along with performance indicators.

As such, I like to know, which SQL statements were most CPU/DISK intensive.

Solution

Here is the SQL to do the job. Open for trial.

Step 1: Determine the installatin IDs & user IDs.

SELECT inst_id,sid FROM gv$session WHERE username='';

Step 2:

SELECT 
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s    
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  AND s.inst_id = :inst_id -- replace with instID from above
  AND s.sid = :sid -- replace with ID from above
  AND sq.inst_id = s.inst_id

There might be multiple Ids & instance Ids returned. So it's up to a users' choice on how to use this data in a web interface etc.

Code Snippets

SELECT inst_id,sid FROM gv$session WHERE username='<ENTER-USERNAME>';

SELECT 
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s    
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  AND s.inst_id = :inst_id -- replace with instID from above
  AND s.sid = :sid -- replace with ID from above
  AND sq.inst_id = s.inst_id

Context

StackExchange Database Administrators Q#81, answer score: 17

Revisions (0)

No revisions yet.