Mauro Pagano's Blog

SQL Diag Repository

3 Comments

Every once in a while I look around the list of new (fixed) views in the instance trying to find useful / interesting things to add to SQLd360 and recently I stumbled into V$SQL_DIAG_REPOSITORY / V$SQL_DIAG_REPOSTIORY_REASON.

SQL> desc v$sql_diag_repository
 Name               Null?    Type
 ------------------ -------- -------------
 ADDRESS                     RAW(8)
 CON_ID                      NUMBER
 HASH_VALUE                  NUMBER
 SQL_ID                      VARCHAR2(13)
 CHILD_NUMBER                NUMBER
 SQL_DIAG_REPO_ID            NUMBER
 TYPE                        VARCHAR2(20)
 PLAN_ID                     NUMBER
 FEATURE                     VARCHAR2(50)
 STATE                       VARCHAR2(10)

SQL> desc v$sql_diag_repository_reason
 Name                  Null?    Type
 --------------------- -------- -------------
 ADDRESS                        RAW(8)
 CON_ID                         NUMBER
 HASH_VALUE                     NUMBER
 SQL_ID                         VARCHAR2(13)
 CHILD_NUMBER                   NUMBER
 SQL_DIAG_REPO_ID               NUMBER
 FEATURE                        VARCHAR2(50)
 REASON                         VARCHAR2(64)
 COMPILATION_ORIGIN             VARCHAR2(1)
 EXECUTION_ORIGIN               VARCHAR2(1)
 SLAVE_ORIGIN                   VARCHAR2(1)

These views have been introduced in 12.1 but if you google them there isn’t much about the views beside just the reference as “new”.
The views don’t seem to store any data (at least in my instances) aka they seem to belong to a feature that is disabled by default.

DISCLAIMER1: Considering the very specific nature of this functionality (details coming) I can understand why this functionality is disabled by default, probably just Oracle Development / Support would make an effective use of it. Anyway in the spirit of sharing what we learn here comes the details 🙂

Looking from hidden parameters there seem to be two controlling this feature

SQL> @hparam _sql_diag

NAME                   DESCRIPTION                                          SESSION_VA SYSTEM_VAL
---------------------- ---------------------------------------------------- ---------- ----------
_sql_diag_repo_origin  duarations where sql diag repository are retained    all        all
_sql_diag_repo_retain  retain sql diag repository to cursor or not                                 

Parameter _sql_diag_repo_origin accepts one from [all, execution, compilation, none], let’s keep it to “all”.
Parameter _sql_diag_repo_retain has an uncommon (IMHO) value of NULL, usually features have TRUE/FALSE value, let’s turn it on setting it to TRUE.

SQL> alter session set "_sql_diag_repo_retain" = true;
SQL> select count(*) from t1;
SQL> select a.child_number, a.type, a.sql_diag_repo_id, a.feature, a.state, b.reason  
  from v$sql_diag_repository a, 
       v$sql_diag_repository_reason b 
 where a.sql_id = b.sql_id 
   and a.child_number = b.child_number 
   and a.sql_diag_repo_id = b.sql_diag_repo_id 
   and a.sql_id = '5bc0v4my7dvr5';

CHILD_NUMBER TYPE     SQL_DIAG_REPO_ID FEATURE                  STATE      REASON
------------ -------- ---------------- ------------------------ ---------- -----------------------------------------
           0 ctxdef                  0 QKSFM_ACCESS_PATH        accept     cost
           0 ctxdef                  1 QKSFM_FULL               final      execution plan
           0 ctxdef                  2 QKSFM_PQ                 bypass     Parameter
           0 ctxdef                  3 QKSFM_ALL_ROWS           final      execution plan
           0 qbcdef                  4 QKSFM_STAR_TRANS         bypass     star transformation parameter is FALSE
           0 qbcdef                  5 QKSFM_CVM                bypass     Outer query contains no views
           0 qbcdef                  6 QKSFM_SET_TO_JOIN        check      checking
           0 qbcdef                  7 QKSFM_UNNEST             check      checking
           0 qbcdef                  8 QKSFM_PRED_MOVE_AROUND   bypass     Outer query contains no views
           0 qbcdef                  9 QKSFM_CBQT               bypass     invalidated
           0 qbcdef                 10 QKSFM_FULL               final      execution plan
           0 qbcdef                 11 QKSFM_TABLE_EXPANSION    bypass     No partitioned table in query block
           0 qbcdef                 12 QKSFM_JOINFAC            bypass     not a UNION or UNION-ALL query block
           0 qbcdef                 13 QKSFM_VECTOR_AGG         bypass     No group-by clause
           0 frodef                 14 QKSFM_ACCESS_PATH        accept     cost

Looking at the output I see several interest things:

  1. FEATURE seems like the piece of optimizer code considered (same as V$SQL_HINT.SQL_FEATURE)
  2. STATE seems to be the output of that piece of optimizer code when applied to our SQL and REASON includes a more verbose explanation (probably, just guessing)
  3. TYPE seems to be the “scope” of that feature in this specific case (context, query block, frodef, etc)

It seems to be a (kind of) summarized version of a 10053 trace from a higher point of view. Not that useful for this SQL to be honest but at least it’s something.
Let’s try with another SQL

SQL> select count(*) from t1 where object_type in (select object_type from t1);

-------------------------------------------------------------------------------------
| Id  | Operation		    | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	    |	    |  1049 (100)|	    |
|   1 |  SORT AGGREGATE 	    |	   |	  1 |	 10 |		 |	    |
|*  2 |   HASH JOIN SEMI	    |	   |  97109 |	948K|  1049   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL| T1   |  97109 |	474K|	524   (1)| 00:00:01 |
|*  4 |    TABLE ACCESS STORAGE FULL| T1   |  97109 |	474K|	524   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select a.child_number, a.type, a.sql_diag_repo_id, a.feature, a.state, b.reason, b.compilation_origin, b.execution_origin, b.slave_origin 
  from v$sql_diag_repository a, 
       v$sql_diag_repository_reason b 
 where a.sql_id = b.sql_id 
   and a.child_number = b.child_number 
   and a.sql_diag_repo_id = b.sql_diag_repo_id 
   and a.sql_id = '9qnqssz6t90n7';

CHILD_NUMBER TYPE        SQL_DIAG_REPO_ID FEATURE                   STATE      REASON                                                           C E S
------------ ----------- ---------------- ------------------------- ---------- ---------------------------------------------------------------- - - -
           0 ctxdef                     0 QKSFM_JPPD                accept     all check pass and valid                                         Y
           0 ctxdef                     1 QKSFM_UNNEST              final      execution plan                                                   Y
           0 ctxdef                     2 QKSFM_ACCESS_PATH         accept     cost                                                             Y
           0 ctxdef                     3 QKSFM_FULL                final      execution plan                                                   Y
           0 ctxdef                     4 QKSFM_USE_HASH            final      execution plan                                                   Y
           0 ctxdef                     5 QKSFM_JOIN_ORDER          final      execution plan                                                   Y
           0 ctxdef                     6 QKSFM_PQ                  bypass     Parameter                                                        Y
           0 ctxdef                     7 QKSFM_ALL_ROWS            final      execution plan                                                   Y
           0 qbcdef                     8 QKSFM_STAR_TRANS          bypass     star transformation parameter is FALSE                           Y
           0 qbcdef                     9 QKSFM_CVM                 bypass     Outer query contains no views                                    Y
           0 qbcdef                    10 QKSFM_JPPD                accept     all check pass and valid                                         Y
           0 qbcdef                    11 QKSFM_SET_TO_JOIN         check      checking                                                         Y
           0 qbcdef                    12 QKSFM_UNNEST              valid      passed validity checks                                           Y
           0 qbcdef                    13 QKSFM_PRED_MOVE_AROUND    bypass     Outer query contains no views                                    Y
           0 qbcdef                    14 QKSFM_CBQT                valid      passed validity checks                                           Y
           0 qbcdef                    15 QKSFM_FULL                final      execution plan                                                   Y
           0 qbcdef                    16 QKSFM_USE_HASH            final      execution plan                                                   Y
           0 qbcdef                    17 QKSFM_JOIN_ORDER          final      execution plan                                                   Y
           0 qbcdef                    18 QKSFM_TABLE_EXPANSION     bypass     No partitioned table in query block                              Y
           0 qbcdef                    19 QKSFM_JOINFAC             bypass     not a UNION or UNION-ALL query block                             Y
           0 qbcdef                    20 QKSFM_VECTOR_AGG          bypass     No group-by clause                                               Y
           0 qbcdef                    21 QKSFM_STAR_TRANS          bypass     star transformation parameter is FALSE                           Y
           0 qbcdef                    22 QKSFM_CVM                 check      checking                                                         Y
           0 qbcdef                    23 QKSFM_UNNEST              accept     all check pass and valid                                         Y
           0 qbcdef                    24 QKSFM_TABLE_EXPANSION     bypass     No partitioned table in query block                              Y
           0 qbcdef                    25 QKSFM_JOINFAC             bypass     not a UNION or UNION-ALL query block                             Y
           0 qbcdef                    26 QKSFM_VECTOR_AGG          bypass     No group-by clause                                               Y
           0 qcUnknown                 27 QKSFM_CVM                 check      checking                                                         Y
           0 qcUnknown                 28 QKSFM_UNNEST              valid      passed validity checks                                           Y
           0 qcUnknown                 29 QKSFM_UNNEST              accept     all check pass and valid                                         Y
           0 frodef                    30 QKSFM_ACCESS_PATH         accept     cost                                                             Y
           0 frodef                    31 QKSFM_ACCESS_PATH         accept     cost                                                             Y

Considering the execution plan and the output from V$SQL_DIAG_REPOSITORY I think it’s interesting that the only transformation applied (at least the only “large” one), subquery unnesting, is reported as “valid” in the STATE column.
This should allow to summarize what happened (from a CBO transformation perspective) to large SQL statements with just a simple SQL query after the parse is over

SQL> SELECT e1.email, jh.job_id
  FROM employees e1,
       job_history jh
 WHERE e1.employee_id = jh.employee_id
   AND jh.start_date > '01-JAN-01'
   AND e1.salary > (SELECT /*+ QB_NAME(SQ1) */ AVG(e2.salary)
                      FROM employees e2
                     WHERE e1.department_id = e2.department_id)
   AND e1.department_id IN (SELECT /*+ QB_NAME(SQ2) */ d.department_id 
                              FROM departments d,
                                   locations l
                             WHERE d.location_id = l.location_id
                               AND l.country_id = 'US');
SQL> ... (same SQL as before on V$SQL_DIAG_REPOSITORY/REASON)
CHILD_NUMBER TYPE       SQL_DIAG_REPO_ID FEATURE                  STATE      REASON                   C E S
------------ ---------- ---------------- ------------------------ ---------- ------------------------ - - -
           0 qbcdef                   19 QKSFM_PRED_MOVE_AROUND   valid      checking                 Y
           0 qbcdef                   41 QKSFM_PRED_MOVE_AROUND   valid      passed validity checks   Y
           0 qbcdef                   42 QKSFM_CBQT               valid      passed validity checks   Y
           0 qcUnknown                57 QKSFM_CVM                valid      default reason           Y
           0 qcUnknown                64 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown                70 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown                71 QKSFM_JPPD               valid      passed validity checks   Y
           0 qcUnknown                73 QKSFM_JPPD               valid      passed validity checks   Y
           0 qcUnknown                92 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown                93 QKSFM_CVM                valid      default reason           Y
           0 qcUnknown                98 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown               103 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown               104 QKSFM_JPPD               valid      passed validity checks   Y
           0 qcUnknown               116 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown               119 QKSFM_CVM                valid      default reason           Y
           0 qcUnknown               122 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown               124 QKSFM_JPPD               valid      passed validity checks   Y
           0 qcUnknown               130 QKSFM_UNNEST             valid      passed validity checks   Y
           0 qcUnknown               134 QKSFM_CVM                valid      default reason           Y
           0 qcUnknown               138 QKSFM_CVM                valid      default reason           Y
           0 qcUnknown               145 QKSFM_JPPD               valid      passed validity checks   Y

In this specific case the only transformation that was accepted and used by the SQL was the subquery unnesting of QB1 (for details of why, check here) but the output of the SQL on V$SQL_DIAG_REPOSITORY seems to show all the transformations that have been considered (details in the same previous link).

One interesting consequence of turning on this feature is the specific SQL Plan Directives used during execution are recorded too (in the OTHER_XML), just like they are when you issue an EXPLAIN PLAN FOR

SQL> alter session set "_sql_diag_repo_retain" = true;
SQL> select count(*) from tab1 where f1 = 1 and f2 = 1;
SQL> select * from table(dbms_xplan.display_cursor('gx8kuzk9q2m7z',0,'ADVANCED'));

.... 

Sql Plan Directive information:
-------------------------------
  Used directive ids:
    5379930755864887256

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

As usual, corrections, suggestions, feedbacks, etc are very much welcome!

DISCLAIMER2: I’m not sure of the overhead / risk associated (memory corruption?) with using this feature in the wild so to stay on the safe side I would probably use it just in a lower environment.

3 thoughts on “SQL Diag Repository

  1. Pingback: Unnesting of coalesced subqueries | Mohamed Houri’s Oracle Notes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s