In this blog post I’ll be describing a case with wrong join cardinality estimation caused by adaptive dynamic sampling (DS). As indicated in the title, the issue has been introduced in the release 12.2.<\/p>\n
First of all, the test case is based on the assumption that optimizer_dynamic_sampling is set to 2:<\/p>\n
NAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling integer 2<\/code><\/pre>\nAlso, before running the test I’m verifying that optimizer adaptive statistics have been enabled:<\/p>\n
show parameter optimizer_adaptive_statistics\n\nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_adaptive_statistics boolean TRUE<\/code><\/pre>\nThen, I’m creating three tables – t1, t2 and t3.<\/p>\n
create table t1 ( n1 number ) ;\n\ninsert into t1\n select trunc(level\/4)+1\n from dual connect by level <= 320000 ;\ncommit ;\n\nexec dbms_stats.gather_table_stats( null, 'T1' ) ;\n\ncreate table t2 ( n1 number , n2 number ) ; \ncreate table t3 ( n1 number , n2 number ) ; \n\nexec dbms_stats.gather_table_stats( null, 'T2' ) ;\nexec dbms_stats.gather_table_stats( null, 'T3' ) ;\n\ninsert into t2\n select level, level\n from dual connect by level <=150000 ;\n\ninsert into t3\n select level, trunc(level\/3) \n from dual connect by level <=450000 ;\n\ncommit ;<\/code><\/pre>\nIt’s worth noting that I intentionally gathered statistics on the empty tables t2 and t3 before<\/i> loading any data. By doing so, I’m misleading the optimizer to come up with wrong join cardinalities. As a consequence of this shameless manipulation, SQL plan directives (SPD) for DS will be created at the next execution, like for example:<\/p>\n--execute two times \nselect distinct t3.n1\nfrom t3 \n join t2 on t3.n2 = t2.n2 \nwhere t2.n1 = 1 ;\n\nexec dbms_spd.flush_sql_plan_directive ;\n\nset long 1000\nSELECT type,reason, state, d.notes\n FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o\n WHERE d.directive_id=o.directive_id\n AND o.object_name in ( 'T2', 'T3' ) ;\n\nTYPE REASON STATE\n----------------------- ------------------------------------ ----------\nNOTES\n--------------------------------------------------------------------------------\nDYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE USABLE\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{(TEST.T3)}<\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE<\/span> USABLE\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text><{F(TEST.T2) - (TEST.T3)}<\/span><\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE USABLE\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{F(TEST.T2) - (TEST.T3)}<\/spd_text>\n<\/spd_note><\/code><\/pre>\nAs you can see, SPDs for DS were indeed generated. As a matter of fact, the SPDs mentioned will kick in during the next execution inspite of the freshly updated statistics.<\/p>\n
EXEC dbms_stats.gather_table_stats( null, 'T2' );\nEXEC dbms_stats.gather_table_stats( null, 'T3' );\n\nSELECT \/*+ gather_plan_statistics *\/ t3.n1\n FROM t3 JOIN t2 ON t3.n2 = t2.n2\n WHERE t2.n1 IN (SELECT \/*+ no_unnest *\/ n1 FROM t1 ) ;\n\n-----------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 240K|00:06:03.69 | 54M| | | |\n|* 1 | FILTER | | 1 | | 240K|00:06:03.69 | 54M| | | |\n|* 2 | HASH JOIN | | 1 | 1 | 449K<\/span>|00:00:00.49 | 1317 | 9268K| 2474K| 8131K (0)|\n| 3 | TABLE ACCESS FULL| T2 | 1 | 150K| 150K|00:00:00.01 | 317 | | | |\n| 4 | TABLE ACCESS FULL| T3 | 1 | 450K| 450K|00:00:00.02 | 997 | | | |\n|* 5 | TABLE ACCESS FULL | T1 | 150K| 1 | 80271 |00:18:27.73 | 54M| | | |\n-----------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 1 - filter( IS NOT NULL)\n 2 - access(\"T3\".\"N2\"=\"T2\".\"N2\")\n 5 - filter(\"N1\"=:B1)\n\nNote\n-----\n - dynamic statistics used: dynamic sampling (level=2)\n - 1 Sql Plan Directive used for this statement<\/span><\/code><\/pre>\nIf you pay a closer attention to the estimated cardinality in the step 2 in the execution plan above, you’ll notice a huge discrepancy between the estimated and actual number of rows. What I mean by that is the optimizer estimated just one row while 449K were returned. In fact, a one-row estimation is often a cue for a completely wrong calculation.<\/p>\n
In contrast, this error doesn’t happen at all when DS is not being used:<\/p>\n
-----------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 240K|00:06:47.81 | 54M| | | |\n|* 1 | FILTER | | 1 | | 240K|00:06:47.81 | 54M| | | |\n|* 2 | HASH JOIN | | 1 | 444K| 449K<\/span>|00:00:00.50 | 1320 | 9268K| 2474K| 8123K (0)|\n| 3 | TABLE ACCESS FULL| T2 | 1 | 150K| 150K|00:00:00.01 | 317 | | | |\n| 4 | TABLE ACCESS FULL| T3 | 1 | 450K| 450K|00:00:00.02 | 997 | | | |\n|* 5 | TABLE ACCESS FULL | T1 | 150K| 1 | 80264 |00:18:13.30 | 54M| | | |\n-----------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nBut what exactly went on here?<\/p>\n
To answer this question I’m going to use information generated in two types of traces. One is the well-known CBO 10053 trace. The other is DS trace which I became aware of after having read Stefan Koehler’s blog post<\/a>. So, this is how I activated both of the traces:<\/p>\nALTER SESSION SET EVENTS 'trace[RDBMS.SQL_DS] disk=high';\nALTER SESSION SET EVENTS='10053 trace name context forever, level 1';<\/code><\/pre>\nBy looking at the trace file we can indeed confirm that DS produced a fairly accurate guess:<\/p>\n
qksdsScaleResult(): Dumping scaled result (status = SUCCESS)\nqksdsDumpResult(): DS Results: #exps=1, smp obj=T3\nqksdsDumpResult(): T.CARD = qksdsDumpResult(): (mid=449810.0<\/span>, low=449810.0, hig=449810.0)qksdsDumpResult(): \nqksdsDumpResult(): end dumping results \n SPD: qosdGetFObjKeyFromStmt: sqlText = SELECT \/* DS_SVC *\/ \/*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel *\/ NVL(SUM(C1),0) FROM (SELECT \/*+ qb_name(\"innerQuery\")< NO_INDEX_FFS( \"T3#1\") *\/ 1 AS C1 FROM \"T3\" \"T3#1\", \"T2\" \"T2#0\" WHERE (\"T3#1\".\"N2\"=\"T2#0\".\"N2\")) innerQuery (objid = 1800385981567557396)<\/code><\/pre>\nAs a matter of fact, it even generated a finding for future reference:<\/p>\n
SPD: Generating finding id: type = 2, reason = 7, objcnt = 3, obItr = 0, objid = 1800385981567557396, objtyp = 4, vecsize = 0, obItr = 1, objid = 94576, objtyp = 1, vecsize = 0, obItr = 2, objid = 94577, objtyp = 1, vecsize = 0, fid = 4662502719207033285\n SPD: qosdCreateDir4DSResult retCode = CREATED, fid = 0<\/pre>\nFurthermore, it can easily be verified that this finding is persisted as an SPD. Moreover, those newly created SPDs containing the result of DS will supersede the SPDs for DS itself, and in doing so completely eliminate DS in future optimizations:<\/p>\n
SELECT type,reason, state, d.notes\n FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o\n WHERE d.directive_id=o.directive_id\n 4 AND o.object_name in ( 'T2', 'T3' ) ;\n\nTYPE REASON STATE\n----------------------- ------------------------------------ ----------\nNOTES\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\nDYNAMIC_SAMPLING SINGLE TABLE CARDINALITY MISESTIMATE USABLE\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{(TEST.T3)}<\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE SUPERSEDED\n<spd_note>\n <internal_state>HAS_STATS<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{F(TEST.T2) - (TEST.T3)}<\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING JOIN CARDINALITY MISESTIMATE SUPERSEDED<\/span>\n<spd_note>\n <internal_state>HAS_STATS<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{F(TEST.T2) - (TEST.T3)}<\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING_RESULT VERIFY CARDINALITY ESTIMATE USABLE<\/span>\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{(TEST.T2, num_rows=150000) - (TEST.T3, num_rows=450000) - (SQL_ID:1jz21hbnyursn, \nT.CARD=450347<\/span>[-2 -2])}<\/spd_text>\n<\/spd_note>\n\nDYNAMIC_SAMPLING_RESULT VERIFY CARDINALITY ESTIMATE USABLE\n<spd_note>\n <internal_state>NEW<\/internal_state>\n <redundant>NO<\/redundant>\n <spd_text>{(TEST.T2, num_rows=150000) - (TEST.T3, num_rows=450000) - (SQL_ID:1jz21hbnyursn, \nT.CARD=450347[-2 -2])}<\/spd_text>\n<\/spd_note><\/code><\/pre><\/code><\/pre>\nAnyway, given the optimizer estimated the cardinality perfectly and those estimates are successfully being applied to the subsequent executions, the following question inevitably arises:<\/p>\n
What went wrong with the on-going optimization process?<\/p>\n
We can find a cue by further examining the trace:<\/p>\n
>> Join Card adjusted from 444734.345351 to 0.000000 due to adaptive dynamic sampling<\/span>, prelen=2\nAdjusted Join Cards: adjRatio=0.000000 cardHjSmj=0.000000 cardHjSmjNPF=0.000000 cardNlj=0.000000 cardNSQ=0.000000 cardNSQ_na=444734.345351\nJoin Card - Rounded: 1 Computed: 0.000000<\/code><\/pre>\nSurprisingly, the cardinality was adjusted to zero after DS, which explains the estimated cardinality of “1” in the execution plan. In my opinion, it is a bug. However, I couldn’t find any information on Metalink about it.<\/p>\n
As far as previous releases are concerned, the same test case runs correctly on 12.1. The main difference between both releases with regard to this issue is that 12.2 persists estimated cardinalities after doing DS (see Mauro Pagano’s blog posts<\/a> for more information). It is undoubtedly a significant enhancement compared to the previous release, where the information was stored in the result cache. Unfortunately, some boundary conditions apparently haven’t been handled properly since implementing this change. Consequently, you might observe some catastrophically wrong join cardinality estimates (and bad execution plans too) when dynamic sampling is performed.<\/p>\nUpdates<\/h1>\nMarch 20, 2018<\/h2>\nMore Realistic Test Case<\/h3>\n
Meanwhile, I published another test case<\/a> which is a bit closer to reality. As you can see there, the execution plan changes for the better with the second execution. Also, the difference between the good and the bad plan is dramatic. Besides that, I reengineered the test case, so that no hint is used any more to prevent unnesting. Instead, the optimizer decides himself not to do the transformation as a result of a wrong cardinality estimate which, in turn, is caused by the adjustment-to-zero issue.<\/p>\nWorkaround<\/h3>\n
The problem was apparently introduced with the bug fix “22817465 : AUTODOP CALCULATES DOP OF 1 FOR SPECIFIC QUERY”. So, switching off the bug fix resolves the issue:<\/p>\n
alter session set \"_fix_control\"='21802552:off';\n\n-----------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 240K|00:06:35.51 | 54M| | | |\n|* 1 | FILTER | | 1 | | 240K|00:06:35.51 | 54M| | | |\n|* 2 | HASH JOIN | | 1 | 458K| 449K<\/span>|00:00:00.50 | 1295 | 9268K| 2474K| 8130K (0)|\n| 3 | TABLE ACCESS FULL| T2 | 1 | 150K| 150K|00:00:00.01 | 314 | | | |\n| 4 | TABLE ACCESS FULL| T3 | 1 | 450K| 450K|00:00:00.02 | 978 | | | |\n|* 5 | TABLE ACCESS FULL | T1 | 150K| 1 | 80001 |00:19:23.80 | 54M| | | |\n-----------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nAlso, it can be easily verified by looking at the optimizer trace that the adjustement now is being done correctly:<\/p>\n
Join Card adjusted from 444734.345351 to 458393.000000<\/span> due to adaptive dynamic sampling, prelen=2<\/code><\/pre>\nBig thanks to Nigel Bayliss, the product manager for the Oracle Optimizer, for reaching out after reading this blog post and persevering in finding out the root cause and the workaround.<\/p>\n
Bug number – May 9, 2018<\/h2>\n
The following bug has been raised for the problem: Bug 27989222 : WRONG CARDINALITY ESTIMATION WITH STALE DIRECTIVES WHEN FIX 21802552 IS ENABLED.<\/p>\n
Workaround side-effects – July 5, 2019<\/h2>\n
I changed “alter system” to “alter session” in the workaround, as switching off the fix globally can produce side-effects elsewhere. Thanks to Patrick Jolliffe<\/a> for pointing that out.
\nOf course, you can also set it only for a SQL by hint.<\/p>\n19c – October 28, 2020<\/h2>\n
The bug ist still here – tested on 19.7.0.0.200414. Continue to be careful with the workaround, as it can cause problems elsewhere. We switched off the bug fix 21802552 globally and have been enabling it on a case-by-case basis.<\/p>\n
21c – September 2, 2021<\/h2>\n
The bug isn’t fixed yet.<\/p>\n","protected":false},"excerpt":{"rendered":"
Wrong join cardinality estimate due to adaptive dynamic sampling Continue Reading