oracle top sql given time

Top SQLs Elaps time and CPU time in a given time range..

SELECT SQL_TEXT,X.CPU_TIME
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME<=TO_DATE('01-may-2010 16:00', 'dd-mon-yyyy hh24:mi')) GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

More.. --X.ELAPSED_TIME/1000000 => From Micro second to second

--X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA => How many times the sql ran

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) CPU_TIME_SEC
, EXECUTIONS_DELTA
,X.ELAPSED_TIME
,X.CPU_TIME
,X.EXECUTIONS_DELTA
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME >= TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('01-may-2010 16:00', 'dd-mon-yyyy hh24:mi'))
GROUP BY DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

********

For specific owners sql history for given time ..

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,3) CPU_TIME_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('01-may-2010 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('01-may-2010 14:00', 'dd-mon-yyyy hh24:mi'))
AND DHSS.parsing_schema_name='SCOTT'
GROUP BY DHSS.SQL_ID ) X
WHERE X.SQL_ID = DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

or

with sql id

SELECT X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,3) CPU_TIME_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('08-feb-2011 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('08-feb-2011 14:00', 'dd-mon-yyyy hh24:mi'))
-- AND DHSS.parsing_schema_name='SCOTT'
GROUP BY DHSS.SQL_ID ) X
WHERE X.SQL_ID = DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC;

*********


How many Times a query executed?

Per Hour sql execution growth:
Begin_interval_time means snapshot interval time.
As we fixed this 60 min so here you can see the execution growth (per hour).
The execution delta will show you the no of queries execution (per hour).

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql_id='&sqlid'
and s.snap_id = SQL.snap_id
and s.begin_interval_time> TO_date('01-may-2010 14:00', 'dd-mon-yyyy hh24:mi')
and s.begin_interval_time< TO_date('01-may-2010 18:30', 'dd-mon-yyyy hh24:mi')
order by s.begin_interval_time;