Home > Oracle > Oracle: Historical Elapsed time by Queries

Oracle: Historical Elapsed time by Queries

SELECT SQL_TEXT,ROUND(X.ELAPSED_TIME) FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.ELAPSED_TIME_DELTA/greatest(DHSS.EXECUTIONS_DELTA,1)/1000000) ELAPSED_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE
BEGIN_INTERVAL_TIME > to_date(’10/26/2011 08:59:00′,’mm/dd/yyyy hh24:mi:ss’) AND
END_INTERVAL_TIME < to_date('10/26/2011 10:01:00','mm/dd/yyyy hh24:mi:ss')

)
GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.ELAPSED_TIME DESC;

You can get the snapshot id using following query.
select * from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME desc

Advertisements
Categories: Oracle Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: