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.

| improve this answer | |
  • 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), ... ) – Betlista Jun 18 '13 at 13:45
  • 1
    'inst_id' stands for the instance Id, itdoes not meaninstallation id. – miracle173 May 11 '15 at 16:34

Oracles Enterprise Monitor console shows a whole wealth of information about which SQL queries are taking the max CPU, bottlenecks, top activity in the database, blocking SQLs et al.

For a historical approach, you can use Oracle's AWR reports to pin point areas concerning you.

alt text

enter image description here

| improve this answer | |
  • 1
    Out of curiosity - AWR is a tool available to all Oracle licenses & could I use it via command line only? – Sebastian Roth Jan 4 '11 at 5:17
  • 2
    Sorry I should have mentioned it - from what I know, AWR requires a separate licensing - the Oracle Tuning & Diagnostic Pack. I prefer to use AWR from the Enterprise Manager console - I've been blessed with the privilege(!) to utilize the Enterprise Manager console. I also found that you can use SQL Developer to monitor SQLs, but that requires the above licensing – Sathyajith Bhat Jan 4 '11 at 5:46
  • 1
    Oracle Enterprise Manager (OEM) is very nice. We can monitor our database health in near real-time with auto-refreshed graphs. We've got a big monitor hanging on a wall showing OEM for our primary databases 24x7 and its hugely beneficial in identifying problems as they occur. – ScottCher Jan 5 '11 at 15:06

You can also use V$SQL, there are several interesting columns RUNTIME_MEM, EXECUTIONS, DISK_READS, SORTS, ELAPSED_TIME, SQL_FULLTEXT etc.

This would give you top 10 statements by disk read (note - this is cumulative for all executions):

select sql_id,child_number from
select sql_id,child_number from v$sql
order by disk_reads desc
where rownum<11

If the statement is still in V$SQL_PLAN you can get an actual explain plan for the query:

select * from table(dbms_xplan.display_cursor('sql_id',child_number));

I also like to use V$SQL_PLAN as it contains good info. If your statistics_level=ALL you can use V$SQL_PLAN_STATISTICS.

| improve this answer | |

For recent SQL:

select * from v$sql

For history:

select * from dba_hist_sqltext
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.