To manually perform a SQL tuning for a specific SQL ID which cursor may not exist in the memory currently, we have to check the time interval of snapshot ID where it does exist.
To know the most recent AWR snapshot ID, we query DBA_HIST_SNAPSHOT like the statement below.
SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> column begin_interval_time format a30;
SQL> column end_interval_time format a30;
SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot where instance_number = 1 and end_interval_time > sysdate - 3 order by snap_id desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
45910 2024-08-14 12:00:12 2024-08-14 13:00:16
45909 2024-08-14 11:00:07 2024-08-14 12:00:12
45908 2024-08-14 10:00:03 2024-08-14 11:00:07
45907 2024-08-14 09:00:00 2024-08-14 10:00:03
45906 2024-08-14 08:00:54 2024-08-14 09:00:00
45905 2024-08-14 07:00:50 2024-08-14 08:00:54
45904 2024-08-14 06:00:45 2024-08-14 07:00:50
45903 2024-08-14 05:00:41 2024-08-14 06:00:45
45902 2024-08-14 04:00:37 2024-08-14 05:00:41
45901 2024-08-14 03:00:32 2024-08-14 04:00:37
...
In this case, we list the most recent snapshot ID in 3 days with their intervals. Normally, there should be 72 rows selected in the result.