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

colored sql_ids will be populated in dba_hist_colored_sql

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.

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