Query to help find past execution, Please change "and trunc(sn.begin_interval_time) = trunc(sysdate-1)" predicate as per your requirement.
set colsep "|"
set pages 9999 line 9999
set TAB off
col PARSING_SCHEMA_NAME for a20
col sql_text for a50 wrap
col BEGIN_INTERVAL_TIME for a25
col end_INTERVAL_TIME for a25
select PARSING_SCHEMA_NAME,
dhss.sql_id,
dhss.executions_delta,
dhss.executions_total,
dhss.ELAPSED_TIME_TOTAL,
dhss.CPU_TIME_TOTAL,
sn.begin_interval_time,
sn.end_interval_time,
sq.sql_text
from
dba_hist_sqlstat dhss,
dba_hist_snapshot sn,
dba_hist_sqltext sq
where dhss.snap_id = sn.snap_id
and sq.sql_id = dhss.sql_id
and trunc(sn.begin_interval_time) = trunc(sysdate-1)
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM');
PARSING_SCHEMA_NAME |SQL_ID |EXECUTIONS_DELTA|EXECUTIONS_TOTAL|ELAPSED_TIME_TOTAL|CPU_TIME_TOTAL|BEGIN_INTERVAL_TIME |END_INTERVAL_TIME |SQL_TEXT
--------------------|-------------|----------------|----------------|------------------|--------------|-------------------------|-------------------------|--------------------------------------------------
REPL1 |fcbh6d0cbmmca| 1| 1| 1877| 0|01-NOV-21 10.00.37.854 AM|01-NOV-21 11.00.39.270 AM|begin SYS.DBMS_UTILITY.NAME_RESOLVE(:1 ,:2 ,:3 ,:4
| | | | | | | | , :5 ,:6 ,:7 ,:8 ); end;
GGADMIN |1pgp558xspvaa| 6| 12794| 280148| 296875|01-NOV-21 10.00.53.298 PM|01-NOV-21 11.00.54.622 PM|SELECT PROPERTY FROM SYS.TAB$ WHERE OBJ# = :B1
GGADMIN |1pgp558xspvaa| 7| 12788| 279907| 296875|01-NOV-21 09.00.52.071 PM|01-NOV-21 10.00.53.298 PM|SELECT PROPERTY FROM SYS.TAB$ WHERE OBJ# = :B1
GGADMIN |1pgp558xspvaa| 17| 12786| 281039| 296875|01-NOV-21 11.00.39.270 AM|01-NOV-21 12.00.40.887 PM|SELECT PROPERTY FROM SYS.TAB$ WHERE OBJ# = :B1
GGADMIN |1pgp558xspvaa| 15| 12769| 280343| 296875|01-NOV-21 10.00.37.854 AM|01-NOV-21 11.00.39.270 AM|SELECT PROPERTY FROM SYS.TAB$ WHERE OBJ# = :B1
GGADMIN |5xny833wv89yc| 3| 22| 4109| 0|01-NOV-21 10.00.53.298 PM|01-NOV-21 11.00.54.622 PM|SELECT COUNT(*) FROM SYS.COL$ WHERE COL# = 0 AND S
| | | | | | | |EGCOL# > 0 AND OBJ# = :B1
