Hi,
Sometimes customer ask you that why spesific sql is running long time or consuming lots of resource.
Oracle DBA should find this sql’s SQL_ID in the Oracle database and monitor this SQL how it is running.
To find any SQL’s SQL_ID and other SQL informations in the cache, use following SQL. Following query will find if related SQL exists in the cache, if not exists it will not find.
select * from gv$sql where sql_text like '%DEVECI%';
To search any SQL historical in Oracle database you may use following SQL. Just change begin Interval time column and SQL_TEXT column.
SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA, STAT.SNAP_ID, SS.END_INTERVAL_TIME, EXECUTIONS_DELTA 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 SS.BEGIN_INTERVAL_TIME >= sysdate-31 AND UPPER(SQL_TEXT) LIKE '%DEVECI%' ORDER BY ELAPSED_TIME_DELTA DESC;