Mauro Pagano wrote a brilliant article<\/a> 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. <\/p>\n In the following trace we can see the SQL_ID of the statement for which the dynamic sampling result was stored:<\/p>\n 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. <\/p>\n That’s easy with the following SQL, which, by the way, I described in my previous blog post<\/a>:<\/p>\n Now that we’ve got the SQL_ID in the conventional notation we can query the SPD.<\/p>\n 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:<\/p>\n That’s totally unacceptable. <\/p>\n 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? <\/p>\n We can explore its dependencies to get a clue:<\/p>\n 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:<\/p>\n Finally, we can use the following query to retrieve the entire SPD information:<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" Correlating an active dynamic sampling trace with the data dictionary Continue Reading kkoadsComputeSqlid: sql_id=14211580166815199034<\/span>: 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\r\n 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)\r\n\r\n 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\r\nqksdsExecute(): Used results from directive cache (status = SUCCESS)\r\nqksdsDumpResult(): DS Results: #exps=1, smp obj=T1\r\nqksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=753750.0, low=753750.0, hig=753750.0)qksdsDumpResult():\r\nqksdsDumpResult(): end dumping results\r\nqksdsDumpStats(): **************************************************************<\/code><\/pre>\nConverting the SQL_ID to the Base32 Notation<\/h1>\n
define b = 32\r\ncolumn base&&b format a13\r\n\r\ndefine d = 14211580166815199034<\/span>\r\ncolumn base10 format 9999999999999999999999\r\n\r\nselect &&d base10,\r\n listagg(\r\n substr(\r\n case &&b when 32 then \r\n '0123456789abcdfghjkmnpqrstuvwxyz'\r\n else\r\n '0123456789abcdefghjkmnpqrstuvwxyz'\r\n end,\r\n mod(\r\n trunc(&&d\/power(&&b,level-1)),\r\n &&b\r\n ) + 1 ,\r\n 1\r\n ) \r\n ) within group (order by level desc) base&&b \r\n from dual \r\n connect by level <= ceil(log(&&b,&&d+1))\r\n;\r\n\r\n BASE10 BASE32\r\n----------------------- -------------\r\n 14211580166815199034 cafdcvqa03btu<\/span><\/code><\/pre>\nSlow SPD Query<\/h1>\n
select * from dba_sql_plan_directives where notes like '%cafdcvqa03btu<\/span>%' ; <\/code><\/pre>\nElapsed: 00:40:38.61<\/span>\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 863799 recursive calls\r\n 7351276 db block gets\r\n 173320029 consistent gets<\/span>\r\n 4258 physical reads\r\n 652 redo size\r\n 2721 bytes sent via SQL*Net to client\r\n 1378 bytes received via SQL*Net from client\r\n 6 SQL*Net roundtrips to\/from client\r\n 289951 sorts (memory)\r\n 0 sorts (disk)\r\n 1 rows processed<\/code><\/pre>\nFast SPD Query<\/h1>\n
select owner || '.' || name from dba_dependencies \r\n where referenced_owner='SYS' and referenced_name='DBMS_SPD_INTERNAL' \r\n order by 1 ; \r\n\r\nOWNER||'.'||NAME\r\n--------------------------------------------------------------------------------\r\nSYS.DBA_SQL_PLAN_DIRECTIVES\r\nSYS.DBA_SQL_PLAN_DIR_OBJECTS\r\nSYS.DBMS_SPD\r\nSYS.DBMS_SPD_INTERNAL\r\n...<\/code><\/pre>\ncolumn directive_id format 999999999999999999999999\r\n\r\nselect directive_id from dba_sql_plan_dir_objects \r\n where object_type='SQL STATEMENT' and object_name='cafdcvqa03btu<\/span>' ;\r\n\r\n DIRECTIVE_ID\r\n-------------------------\r\n 5142923332636503583<\/code><\/pre>\nset long 1000 \r\n\r\nselect * from dba_sql_plan_directives where directive_id = (\r\n select directive_id from dba_sql_plan_dir_objects \r\n where OBJECT_name = 'cafdcvqa03btu<\/span>'\r\n) ;\r\n\r\n DIRECTIVE_ID TYPE ENA STATE AUT REASON CREATED LAST_MODIFIED\r\n------------------------- ----------------------- --- ---------- --- ------------------------------------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------\r\nLAST_USED\r\n---------------------------------------------------------------------------\r\nNOTES\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n 5142923332636503583 DYNAMIC_SAMPLING_RESULT YES USABLE YES VERIFY CARDINALITY ESTIMATE 12-JUL-18 01.33.18.000000 PM\r\n12-JUL-18 01.33.18.000000000 PM\r\nConclusion<\/h1>\n