How to mark SQL_ID as Colored
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). 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
SQL> select * from DBA_HIST_COLORED_SQL; no rows selected SQL> execute DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id=>'1gyx4vasq9yv0'); PL/SQL procedure successfully completed. SQL> select * from DBA_HIST_COLORED_SQL; DBID SQL_ID CREATE_TI ---------- ------------- --------- 3554487085 1gyx4vasq9yv0 25-JUL-18 <----- SQL>
SQL> execute DBMS_WORKLOAD_REPOSITORY.remove_COLORED_SQL(sql_id=>'1gyx4vasq9yv0'); PL/SQL procedure successfully completed. SQL> select * from DBA_HIST_COLORED_SQL; no rows selected <------ SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.