Tag Archives: mark sql id as colored

Colored SQL_ID

How to mark SQL_ID as Colored

Contents
___________________________________________________________________________________________________

1. Overview
2. Mark SQL_ID as colored
3. Unmark Colored SQL_ID
___________________________________________________________________________________________________


1. Overview

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


2. Mark SQL_ID as colored

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> 


3. Unmark Colored SQL_ID

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.

Thank you,
Rajasekhar Amudala