Active Dynamic Sampling (ADS) Trace

Mauro Pagano wrote a brilliant article about persisting ADS results in Oracle 12.2. There’s no point in repeating what he said. Instead, I’d like to focus on correlating the information in the ADS trace with the result persisted in the data dictionary.

In the following trace we can see the SQL_ID of the statement for which the dynamic sampling result was stored:

kkoadsComputeSqlid: sql_id=14211580166815199034: newText=SELECT /* DS_SVC */  NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "A1")  */ 1 AS C1 FROM anonomized_query_text) innerQuery, startPos = 20, stopPos = 177, newTextLen = 372, sqlLen = 528
     SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel  OPT_ESTIMATE(@"innerQuery", TABLE, "A1", ROWS=919624) */ anonymized_query_text) innerQuery (objid = 14211580166815199034)

    SPD: Generating finding id: type = 2, reason = 7, objcnt = 3, obItr = 0, objid = 14211580166815199034, objtyp = 4, vecsize = 0, obItr = 1, objid = 68737, objtyp = 1, vecsize = 0, obItr = 2, objid = 68743, objtyp = 1, vecsize = 0, fid = 14212484559587940016
qksdsExecute(): Used results from directive cache (status = SUCCESS)
qksdsDumpResult(): DS Results: #exps=1, smp obj=T1
qksdsDumpResult():    T.CARD = qksdsDumpResult(): (mid=753750.0, low=753750.0, hig=753750.0)qksdsDumpResult():
qksdsDumpResult(): end dumping results
qksdsDumpStats(): **************************************************************

But the DIRECTIVE_ID is missing. So, we need to query the SQL plan directive (SPD) information in the data dictionary by the SQL_ID. First, however, we need to convert the SQL_ID in the trace from the decimal to the conventional base32 notation.

Converting the SQL_ID to the Base32 Notation

That’s easy with the following SQL, which, by the way, I described in my previous blog post:

define b = 32
column base&&b format a13

define d = 14211580166815199034
column base10 format 9999999999999999999999

select &&d base10,
    listagg(
        substr(
          case &&b when 32 then 
            '0123456789abcdfghjkmnpqrstuvwxyz'
          else
            '0123456789abcdefghjkmnpqrstuvwxyz'
          end,
          mod(
            trunc(&&d/power(&&b,level-1)),
            &&b
          ) + 1 ,
          1
        ) 
    ) within group (order by level desc) base&&b 
  from dual 
  connect by level <= ceil(log(&&b,&&d+1))
;

                 BASE10 BASE32
----------------------- -------------
   14211580166815199034 cafdcvqa03btu

Now that we’ve got the SQL_ID in the conventional notation we can query the SPD.

Slow SPD Query

select * from dba_sql_plan_directives where notes like '%cafdcvqa03btu%' ; 

The query above is simple, though, extremely inefficient. For example, it took around 173 million consistent gets and 40 minutes to execute it on a live system with many SPDs:

Elapsed: 00:40:38.61

Statistics
----------------------------------------------------------
     863799  recursive calls
    7351276  db block gets
  173320029  consistent gets
       4258  physical reads
        652  redo size
       2721  bytes sent via SQL*Net to client
       1378  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
     289951  sorts (memory)
          0  sorts (disk)
          1  rows processed

That’s totally unacceptable.

Fast SPD Query

To find a faster way, let’s analyze the dictionary view DBA_SQL_PLAN_DIRECTIVES. It consists of the join between the following two tables: SYS.OPT_DIRECTIVE$ and SYS.FINDING$. Further, the SQL_ID is contained in the NOTE which in turn is returned by the function SYS.DBMS_SPD_INTERNAL.GET_SPD_TEXT. The question is: Where might this function get the SQL_ID from?

We can explore its dependencies to get a clue:

select owner || '.' || name from dba_dependencies 
  where referenced_owner='SYS' and referenced_name='DBMS_SPD_INTERNAL' 
  order by 1 ;                                                                                                                        

OWNER||'.'||NAME
--------------------------------------------------------------------------------
SYS.DBA_SQL_PLAN_DIRECTIVES
SYS.DBA_SQL_PLAN_DIR_OBJECTS
SYS.DBMS_SPD
SYS.DBMS_SPD_INTERNAL
...

Indeed, the relationship of interest is in SYS.DBA_SQL_PLAN_DIR_OBJECTS. More precisely, the SQL_ID is stored in its column OBJECT_NAME:

column directive_id format 999999999999999999999999

select directive_id from dba_sql_plan_dir_objects 
  where object_type='SQL STATEMENT' and object_name='cafdcvqa03btu' ;

  DIRECTIVE_ID
-------------------------
      5142923332636503583

Finally, we can use the following query to retrieve the entire SPD information:

set long 1000 

select * from dba_sql_plan_directives where directive_id = (
  select directive_id from dba_sql_plan_dir_objects 
    where OBJECT_name = 'cafdcvqa03btu'
) ;

             DIRECTIVE_ID TYPE                    ENA STATE      AUT REASON                               CREATED                                                                     LAST_MODIFIED
------------------------- ----------------------- --- ---------- --- ------------------------------------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------
LAST_USED
---------------------------------------------------------------------------
NOTES
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      5142923332636503583 DYNAMIC_SAMPLING_RESULT YES USABLE     YES VERIFY CARDINALITY ESTIMATE          12-JUL-18 01.33.18.000000 PM
12-JUL-18 01.33.18.000000000 PM

  NEW
  NO
  {(O.T1, num_rows=85736) - (O.T2, num_rows=15793060) - (SQL_ID:cafdcvqa03btu, T.CARD=753750[-2 -2])}



Elapsed: 00:00:01.48

Statistics
----------------------------------------------------------
        843  recursive calls
         24  db block gets
      14280  consistent gets
          2  physical reads
          0  redo size
       2721  bytes sent via SQL*Net to client
       1378  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Conclusion

As you can see, the number of consistent gets dropped from around 173 million to just 14280. As a result, the query completed in less than 2 seconds instead of 40 minutes.