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:

     FROM gv$session s    
        , gv$sql sq
      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