How to find the latest SQL statements within the database?

  • 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.

  • 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='<ENTER-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.

    Just small note: Oracle supports (no idea from which version) `in` operator with tuples, so from example above `... AND (s.inst_id, s.sid) in ( (:id1, :sid1), (:id2, :sid2), ... )`

    'inst_id' stands for the instance Id, itdoes not meaninstallation id.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM