By default, only top sql_ids are captured in database snapshots.

If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities  (so that it does not have to be a TOP SQL).

Mark an sql_id as colored

execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>'b62q7nc33gzwx');

PL/SQL procedure successfully completed.

colored sql_ids will be populated in dba_hist_colored_sql

select * from DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TI     CON_ID
---------- ------------- --------- ----------
2155718794 b62q7nc33gzwx 04-FEB-18          0

NOTE – Only 100 sql_ids can be marked as colored sql_id. Trying to add more sql_id will result below error.

ERROR at line 1:
ORA-13534: Current SQL count(100) reached maximum allowed (100)
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 751

We can uncolor sql_ids from the snapshot.

execute DBMS_WORKLOAD_REPOSITORY.remove_COLORED_SQL(sql_id=>'b62q7nc33gzwx');

PL/SQL procedure successfully completed.

select * from DBA_HIST_COLORED_SQL;

no rows selected

Information will be captured in each snapshot for sqls marked using DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL. All the information related to the specific sql (sql plan, executions statistics, etc) will be stored in the AWR tables. However the SQLs will not neccesarily appear in the AWR Report sections related to SQLS unless they are indeed amongst the top SQLs for that sections as determined by the setting for DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS