Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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

share|improve this answer
    
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 –  Sathya 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
add comment
up vote 6 down vote accepted

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=:id -- replace with ID from above
  AND s.sid = :sid -- replace with instID from above
  AND sq.inst_id= s.inst_id

There might be multiple Ids & Installation Ids returned. So it's up to a users' choice on how to use this data in a web interface etc.

share|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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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