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. 

How to retrieve the underlying SQL for a view.

Some time , you run into performance tuning tasks , where non-performant query uses views . Those views in turn uses views one after the other . 
Though , this is not a recommended practice , it is widely (ab)used . You need to reverse engineer the view to get the actual base tables and its filters and so on. 
Most of the time , the query performs better when directly accessed with the base table ( it depends on the query , and other factors) . 

In any case , Oracle 12c has introduced a new API ( dbms_utility.EXPAND_SQL_TEXT ) to get the underlying base table structures for the views .
This is in one of the most underutilized package , in my opinion ( dbms_utility ). 

Let us get to the example . 



SQL> CREATE OR REPLACE VIEW dept_sales_v
  2  AS
  3    SELECT
  4      deptno AS DepartmentNumber ,
  5      dname  AS DepartmentName ,
  6      loc    AS Location
  7    FROM
  8      scott.dept
  9    WHERE
 10      deptno = 30 ;

View created.


Now , Let us use this new API to get the underlying DDL for the view. 
You may notice that the generated DDL is conceptually the same , syntactically Oracle has created sub query and reference that sub query in the outer query.

SQL> SET serveroutput ON
SQL> SET LONG 10000
SQL> DECLARE
  2    expsql CLOB ;
  3    inpsql CLOB ;
  4  BEGIN
  5    inpsql := 'Select * From dept_sales_v' ;
  6    dbms_utility.EXPAND_SQL_TEXT ( input_sql_text => inpsql, output_sql_text =>
  7    expsql) ;
  8    dbms_output.put_line( expsql) ;
  9  END ;
 10  /
SELECT "A1"."DEPARTMENTNUMBER" "DEPARTMENTNUMBER","A1"."DEPARTMENTNAME"
"DEPARTMENTNAME","A1"."LOCATION" "LOCATION" FROM  (SELECT "A2"."DEPTNO"
"DEPARTMENTNUMBER","A2"."DNAME" "DEPARTMENTNAME","A2"."LOC" "LOCATION" FROM
"SCOTT"."DEPT" "A2" WHERE "A2"."DEPTNO"=30) "A1"

PL/SQL procedure successfully completed.


Now , let us create a view based on this view. 


SQL> CREATE OR REPLACE VIEW EmpSales_v
  2  AS
  3    SELECT
  4      v.DEPARTMENTNAME ,
  5      v.location ,
  6      e.EMPNO AS EmployeeCode,
  7      e.ENAME AS EmployeeName
  8    FROM
  9      DEPT_SALES_V v ,
 10      emp e
 11    WHERE
 12      v.DEPARTMENTNUMBER = e.deptno ;

View created.

Here is the SQL generated by Oracle for this view. 

SQL>     set serveroutput on
SQL> set long 10000
SQL> declare
  2  expsql clob ;
  3  inpsql clob ;
  4  begin
  5  inpsql := 'Select * From EmpSales_v'  ;
  6  dbms_utility.EXPAND_SQL_TEXT  ( input_sql_text => inpsql, output_sql_text => expsql) ;
  7  dbms_output.put_line( expsql) ;
  8  end ;
  9  /
SELECT "A1"."DEPARTMENTNAME" "DEPARTMENTNAME","A1"."LOCATION"
"LOCATION","A1"."EMPLOYEECODE" "EMPLOYEECODE","A1"."EMPLOYEENAME" "EMPLOYEENAME"
FROM  (SELECT "A3"."DEPARTMENTNAME" "DEPARTMENTNAME","A3"."LOCATION"
"LOCATION","A2"."EMPNO" "EMPLOYEECODE","A2"."ENAME" "EMPLOYEENAME" FROM  (SELECT
"A4"."DEPTNO" "DEPARTMENTNUMBER","A4"."DNAME" "DEPARTMENTNAME","A4"."LOC"
"LOCATION" FROM "SCOTT"."DEPT" "A4" WHERE "A4"."DEPTNO"=30) "A3",SCOTT."EMP"
"A2" WHERE "A3"."DEPARTMENTNUMBER"="A2"."DEPTNO") "A1"

PL/SQL procedure successfully completed.

Here is the SQL that has been formatted for Clarity.

Comments Welcome. 

Real Time Statistics of a SQL statement in Oracle

To get real time statistics ( well , close to ) of a SQL statement , we can use the view v$sql_monitor.
This view is available only in Oracle 11g .

Here is an example .


SQL> SELECT *

2 FROM
3 (SELECT sid ,
4 sql_id ,
5 status ,
6 cpu_time ,
7 buffer_gets ,
8 disk_reads ,
9 SQL_PLAN_HASH_VALUE
10 FROM v$sql_monitor
11 order by cpu_time desc
12 )
13 WHERE rownum <= 5 ;


SID    SQL_ID STATUS CPU_TIME BUFFER_GETS DISK_READS SQL_PLAN_HASH_VALUE
---------- ------------- ------------------- ---------- ----------- ---------- -------------------
139    5zruc4v6y32f9    DONE   184656250   2877026   77594 0
154    401xxgh4h55g3    DONE    94843750   3258120    7137 0
152    401xxgh4h55g3    DONE    92531250   3254389    7550 0
36     401xxgh4h55g3    DONE    92109375   3239036    8009 0
141    401xxgh4h55g3    DONE    21687500    988400    3190 0

5 rows selected.

You can see the documentation of this view at
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3048.htm