# ORACLE 11g
SELECT ROWNUM NO
, PARSING_SCHEMA_NAME
, to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.99 ) 평균실행시간
, executions 실행횟수
, SQL_TEXT 쿼리
, SQL_FULLTEXT
FROM V$SQL
WHERE LAST_ACTIVE_TIME > SYSDATE-(1/24*2)
-- AND LAST_ACTIVE_TIME BETWEEN to_Date('20111226163000','YYYYMMDDHH24MISS') AND to_Date('20111226170000','YYYYMMDDHH24MISS')
AND ELAPSED_TIME >= 1 * 1000000 * decode(executions,null,1,0,1,executions)
ORDER BY 평균실행시간 DESC, 실행횟수 DESC
# ORACLE 9i
SELECT ROWNUM NO,to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.99 ) 평균실행시간
, executions 실행횟수
, SQL_TEXT 쿼리
,FIRST_LOAD_TIME
FROM V$SQL
WHERE FIRST_LOAD_TIME BETWEEN '2011-02-08/18:13:00' AND '2011-02-08/19:13:00'
AND ELAPSED_TIME >= 0 * 1000000 * decode(executions,null,1,0,1,executions)
ORDER BY 평균실행시간 DESC, 실행횟수 DESC;
