27

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.

Improve this question
1

4 Answers 4

Reset to default
17

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.

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

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
3
  • 1
    Out of curiosity - AWR is a tool available to all Oracle licenses & could I use it via command line only?
    – Sebastian Roth
    Commented Jan 4, 2011 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
    Commented Jan 4, 2011 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
    Commented Jan 5, 2011 at 15:06
4

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
2

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 and acknowledge you have read our privacy policy.

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