SELECT STAT.SQL_ID,
SQL_TEXT,
PLAN_HASH_VALUE,
PARSING_SCHEMA_NAME,
ELAPSED_TIME_DELTA / 1000000,
STAT.SNAP_ID,
SS.END_INTERVAL_TIME
FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
WHERE STAT.SQL_ID = TXT.SQL_ID
AND STAT.DBID = TXT.DBID
AND SS.DBID = STAT.DBID
AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
AND STAT.SNAP_ID = SS.SNAP_ID
--AND STAT.DBID = ?
-- AND STAT.INSTANCE_NUMBER in (1,2,3)
AND SS.BEGIN_INTERVAL_TIME >= SYSDATE - 7
AND lower (STAT.SQL_ID) = 'cg7rb2tqp3ww5'
ORDER BY ELAPSED_TIME_DELTA DESC
or
SELECT SQL_ID,
SQL_FULLTEXT,
PLAN_HASH_VALUE,
PARSING_SCHEMA_NAME,
ELAPSED_TIME
FROM gV$SQL
WHERE UPPER (SQL_ID) = 'cg7rb2tqp3ww5'
ORDER BY ELAPSED_TIME DESC;