Color / Colour the SQL

We use AWR and ASH to troubleshoot performance ( provided we licensed diagostic pack option )  .
Typically , only the top consumers ( SQLs ) is displayed in AWR. 

You can color / colour the SQL to  see the execution plans and other metrics of a SQL , 
even if the SQL does not qualify as top consumer ( if the SQL is in the cache at the snapshot time) . 
The utility is ADD_COLORED_SQL  in DBMS_WORKLOAD_REPOSITORY.  

As a side note , I found a documentation bug in oracle 11g documentation . It was wrongly documented as DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML , instead of ADD_COLORED_SQL.  But this is corrected in Oracle 12c documentation. 

  
Here is an example. 

I am using "ZAHIR_COLOR_SQL" as comment just for clarity . You need not comment to get the SQL id. 


Session 1 :
---------

SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Go to another session and determine the SQL ID of this SQL and color it. 

SQL> select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dfjudnqgk5c11
select sql_id , sql_text  from v$sql where sql_text like '%ZAHIR_COLOR%'

drfh6qd1ctrb7
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp


SQL> exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id  => 'drfh6qd1ctrb7');

PL/SQL procedure successfully completed.


SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.


We can look at the execution plan using dbms_xplan or via AWR from Enterprise Manager or AWR  by executing awrrpt.sql . 

Here is an example of getting the execution plan from AWR as shown below. 


SQL> select * from table(dbms_xplan.display_awr('drfh6qd1ctrb7'))
  2  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID drfh6qd1ctrb7
--------------------
Select /* ZAHIR_COLOR_SQL */ empno , ename from  emp

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.



Comments Welcome.