Archive

Posts Tagged ‘Query performance’

Oracle: Historical Elapsed time by Queries

October 26, 2011 Leave a comment

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

Categories: Oracle Tags: