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:
- FEATURE seems like the piece of optimizer code considered (same as V$SQL_HINT.SQL_FEATURE)
- 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)
- 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.
Mauro Pagano's Blog 
July 30, 2017 at 12:13 pm
nice
>
LikeLike
July 31, 2017 at 4:44 pm
cool finding
LikeLike
Pingback: Unnesting of coalesced subqueries | Mohamed Houri’s Oracle Notes
May 8, 2020 at 6:23 pm
Amazing
LikeLike