Search

Top 60 Oracle Blogs

Recent comments

SQL Diag Repository

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 </p />
</p></div>

    	  	<div class=