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>';
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), ... )`