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.

Sebastian Roth
  • 1,356
  • 3
  • 15
  • 23

4 Answers4

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.

Thorsten Kettner
  • 193
  • 1
  • 6
Sebastian Roth
  • 1,356
  • 3
  • 15
  • 23
  • 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
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

Sathyajith Bhat
  • 1,504
  • 3
  • 18
  • 36
  • 1
    Out of curiosity - AWR is a tool available to all Oracle licenses & could I use it via command line only? – Sebastian Roth Jan 04 '11 at 05: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 04 '11 at 05: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 05 '11 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.

gabrielp
  • 116
  • 3
2

For recent SQL:

select * from v$sql

For history:

select * from dba_hist_sqltext
grokster
  • 121
  • 3