The new Oracle 12c optimizer adaptive features are just great and work well out of the box in most cases.<\/p>\n
Recently, however,\u00a0 I’ve experienced my very first problem with SQL Plan Directives migrating a database to 12c, so I would like to share it.<\/p>\n
Disclaimer 1: this is a specific problem that I found on ONE system. My solution may not fit with your environment, don’t use it if you are not sure about what you’re doing!<\/span><\/p>\n Disclaimer 2: despite I had this problem with a single SPD, I like adaptive features and I encourage to use them!!<\/span><\/p>\n Problem: a query takes a sub-second in 11gR2, in 12c it takes 12 seconds or more.<\/strong><\/p>\n V_TAB_PROP is a very simple view. It just selects a central table “TAB” and then takes different properties by joining\u00a0 a property table “TAB_PROP”.<\/p>\n To do that, it does 11 joins on the same property table.<\/p>\n On the property table, TAB_PROP_ID and PROP_ID are unique (they compose the pk), so nested loops and index unique scans are the best way to get this data. This was the plan in 11g:<\/p>\n In 12c, the plan switches to adaptive, and half of the joins are converted to hash joins \/ full table scans:<\/p>\n However, the inflection point is never reached. The execution keeps the default plan that has half of the joins HJ and the other half NL.<\/p>\n The problem in this case is the SQL Directive. Why?<\/strong><\/p>\n There are to many distinct values for TAB_ID and the data is very skewed.<\/p>\n The histogram on that column is OK and it always leads to the correct plan (with the adaptive features disabled). The Directive instructs the optimizer to do a dynamic sampling, but with a such big and skewed table this is not ok, so the Dynamic sampling result is worse than using the histogram. I can check it by simplifying the query to just one join:<\/p>\n What’s the fix?<\/strong><\/p>\n I’ve tried to drop the directive first, but it reappears as soon as there are new misestimates. I did this on a QAS environment. Off course, the directives can’t be created for objects that do not exist, the import\u00a0 has to be done after the objects migrate to the 12c version.<\/p>\n Because the SQL Plan Directives are tied to specific objects and not specific queries, they can fix many statements at once, but in case like this one, they can compromise several statements!<\/p>\n Monitoring the creation of new directives is an important task as it may indicate misestimates\/lack of statistics on one side or execution plan changes on the other one.<\/p>\n","protected":false},"excerpt":{"rendered":" The new Oracle 12c optimizer adaptive features are just great and work well out of the box in most cases. Recently, however,\u00a0 I’ve experienced my very first problem with SQL Plan Directives migrating a database to 12c, so I would … Continue reading --11gR2\r\nSQL> select * from APPUSER.V_TAB_PROP where TAB_ID = 842300;\r\n\r\n...\r\n\r\n48 rows selected.\r\n\r\nElapsed: 00:00:00.71\r\n\r\n \r\n--12c\r\nSQL> select * from APPUSER.V_TAB_PROP where TAB_ID = 842300;\r\n\r\n...\r\n\r\n48 rows selected.\r\n\r\nElapsed: 00:00:12.74<\/pre>\n
create view ... as\r\nselect ...\r\nfrom TAB li\r\nleft join\r\n(select v.TAB_PROP_ID, v.PROP_VAL as c89 from TAB_PROP v where v.PROP_ID = 89) v89 on li.TAB_PROP_ID = v89.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c88 from TAB_PROP v where v.PROP_ID = 88) v88 on li.TAB_PROP_ID = v88.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c90 from TAB_PROP v where v.PROP_ID = 90) v90 on li.TAB_PROP_ID = v90.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c82 from TAB_PROP v where v.PROP_ID = 82) v82 on li.TAB_PROP_ID = v82.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c84 from TAB_PROP v where v.PROP_ID = 84) v84 on li.TAB_PROP_ID = v84.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c93 from TAB_PROP v where v.PROP_ID = 93) v93 on li.TAB_PROP_ID = v93.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c79 from TAB_PROP v where v.PROP_ID = 79) v79 on li.TAB_PROP_ID = v79.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c81 from TAB_PROP v where v.PROP_ID = 81) v81 on li.TAB_PROP_ID = v81.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c96 from TAB_PROP v where v.PROP_ID = 96) v96 on li.TAB_PROP_ID = v96.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c95 from TAB_PROP v where v.PROP_ID = 95) v95 on li.TAB_PROP_ID = v95.TAB_PROP_ID\r\nleft join \r\n(select v.TAB_PROP_ID, v.PROP_VAL as c94 from TAB_PROP v where v.PROP_ID = 94) v94 on li.TAB_PROP_ID = v94.TAB_PROP_ID\r\n);<\/pre>\n
\nThe table is 1500Mb big and the index 1000Mb.<\/p>\n----------------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n----------------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | 3401 (100)| |\r\n| 1 | NESTED LOOPS OUTER | | 1009 | 218K| 3401 (1)| 00:00:41 |\r\n| 2 | NESTED LOOPS OUTER | | 615 | 123K| 2171 (1)| 00:00:27 |\r\n| 3 | NESTED LOOPS OUTER | | 390 | 73320 | 1391 (1)| 00:00:17 |\r\n| 4 | NESTED LOOPS OUTER | | 248 | 42408 | 894 (0)| 00:00:11 |\r\n| 5 | NESTED LOOPS OUTER | | 160 | 24640 | 574 (0)| 00:00:07 |\r\n| 6 | NESTED LOOPS OUTER | | 104 | 14248 | 366 (0)| 00:00:05 |\r\n| 7 | NESTED LOOPS OUTER | | 68 | 8160 | 230 (0)| 00:00:03 |\r\n| 8 | NESTED LOOPS OUTER | | 44 | 4532 | 142 (0)| 00:00:02 |\r\n| 9 | NESTED LOOPS OUTER | | 29 | 2494 | 84 (0)| 00:00:02 |\r\n| 10 | NESTED LOOPS OUTER | | 19 | 1311 | 46 (0)| 00:00:01 |\r\n| 11 | NESTED LOOPS OUTER | | 13 | 676 | 20 (0)| 00:00:01 |\r\n| 12 | TABLE ACCESS BY INDEX ROWID| TAB | 8 | 280 | 4 (0)| 00:00:01 |\r\n|* 13 | INDEX RANGE SCAN | FK_TAB_PROP | 8 | | 3 (0)| 00:00:01 |\r\n| 14 | TABLE ACCESS BY INDEX ROWID| TAB_PROP | 1 | 17 | 2 (0)| 00:00:01 |\r\n|* 15 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 16 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 17 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 18 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 19 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 20 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 21 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 22 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 23 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 24 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 25 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 26 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 27 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 28 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 29 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 30 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 31 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 32 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 33 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n| 34 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 2 | 34 | 2 (0)| 00:00:01 |\r\n|* 35 | INDEX UNIQUE SCAN | PK_TAB_PROP | 1 | | 1 (0)| 00:00:01 |\r\n----------------------------------------------------------------------------------------------------------\r\n \r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n \r\n 13 - access(\"LI\".\"TAB_ID\"=842300)\r\n 15 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=94)\r\n 17 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=93)\r\n 19 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=79)\r\n 21 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=96)\r\n 23 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=84)\r\n 25 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=95)\r\n 27 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=82)\r\n 29 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=81)\r\n 31 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=88)\r\n 33 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=89)\r\n 35 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=90)<\/pre>\n
---------------------------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows |\r\n---------------------------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 48 |\r\n| * 1 | HASH JOIN RIGHT OUTER | | 1 | 829K| 48 |\r\n| * 2 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n| * 3 | HASH JOIN OUTER | | 1 | 539K| 48 |\r\n|- 4 | NESTED LOOPS OUTER | | 1 | 539K| 48 |\r\n|- 5 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| * 6 | HASH JOIN OUTER | | 1 | 350K| 48 |\r\n|- 7 | NESTED LOOPS OUTER | | 1 | 350K| 48 |\r\n|- 8 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| * 9 | HASH JOIN OUTER | | 1 | 228K| 48 |\r\n|- 10 | NESTED LOOPS OUTER | | 1 | 228K| 48 |\r\n|- 11 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| * 12 | HASH JOIN OUTER | | 1 | 148K| 48 |\r\n|- 13 | NESTED LOOPS OUTER | | 1 | 148K| 48 |\r\n|- 14 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| * 15 | HASH JOIN OUTER | | 1 | 96510 | 48 |\r\n|- 16 | NESTED LOOPS OUTER | | 1 | 96510 | 48 |\r\n|- 17 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| * 18 | HASH JOIN OUTER | | 1 | 62771 | 48 |\r\n|- 19 | NESTED LOOPS OUTER | | 1 | 62771 | 48 |\r\n|- 20 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n|- * 21 | HASH JOIN OUTER | | 1 | 40827 | 48 |\r\n| 22 | NESTED LOOPS OUTER | | 1 | 40827 | 48 |\r\n|- 23 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n|- * 24 | HASH JOIN OUTER | | 1 | 26554 | 48 |\r\n| 25 | NESTED LOOPS OUTER | | 1 | 26554 | 48 |\r\n|- 26 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n|- * 27 | HASH JOIN OUTER | | 1 | 17271 | 48 |\r\n| 28 | NESTED LOOPS OUTER | | 1 | 17271 | 48 |\r\n|- 29 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n|- * 30 | HASH JOIN OUTER | | 1 | 11305 | 48 |\r\n| 31 | NESTED LOOPS OUTER | | 1 | 11305 | 48 |\r\n|- 32 | STATISTICS COLLECTOR | | 1 | | 48 |\r\n| 33 | BATCHED TABLE ACCESS BY INDEX ROWID | TAB | 1 | 9 | 48 |\r\n| * 34 | INDEX RANGE SCAN | FK_TAB_PROP | 1 | 9 | 48 |\r\n| 35 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 1326 | 48 |\r\n| * 36 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n|- * 37 | TABLE ACCESS FULL | TAB_PROP | 0 | 1326 | 0 |\r\n| 38 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 2 | 48 |\r\n| * 39 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n|- * 40 | TABLE ACCESS FULL | TAB_PROP | 0 | 2 | 0 |\r\n| 41 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 2 | 48 |\r\n| * 42 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n|- * 43 | TABLE ACCESS FULL | TAB_PROP | 0 | 2 | 0 |\r\n| 44 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 2 | 48 |\r\n| * 45 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n|- * 46 | TABLE ACCESS FULL | TAB_PROP | 0 | 2 | 0 |\r\n|- 47 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 48 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 49 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n|- 50 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 51 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 52 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n|- 53 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 54 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 55 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n|- 56 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 57 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 58 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n|- 59 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 60 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 61 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n|- 62 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 0 | 2 | 0 |\r\n|- * 63 | INDEX UNIQUE SCAN | PK_TAB_PROP | 0 | | 0 |\r\n| * 64 | TABLE ACCESS FULL | TAB_PROP | 1 | 2486K| 2486K|\r\n---------------------------------------------------------------------------------------------------------------------\r\n \r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n \r\n 1 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 2 - filter(\"V\".\"PROP_ID\"=84)\r\n 3 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 6 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 9 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 12 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 15 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 18 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 21 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 24 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 27 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 30 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\")\r\n 34 - access(\"LI\".\"TAB_ID\"=842300)\r\n 36 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=94)\r\n 37 - filter(\"V\".\"PROP_ID\"=94)\r\n 39 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=89)\r\n 40 - filter(\"V\".\"PROP_ID\"=89)\r\n 42 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=93)\r\n 43 - filter(\"V\".\"PROP_ID\"=93)\r\n 45 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=90)\r\n 46 - filter(\"V\".\"PROP_ID\"=90)\r\n 48 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=79)\r\n 49 - filter(\"V\".\"PROP_ID\"=79)\r\n 51 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=81)\r\n 52 - filter(\"V\".\"PROP_ID\"=81)\r\n 54 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=96)\r\n 55 - filter(\"V\".\"PROP_ID\"=96)\r\n 57 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=95)\r\n 58 - filter(\"V\".\"PROP_ID\"=95)\r\n 60 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=88)\r\n 61 - filter(\"V\".\"PROP_ID\"=88)\r\n 63 - access(\"LI\".\"TAB_PROP_ID\"=\"V\".\"TAB_PROP_ID\" AND \"V\".\"PROP_ID\"=82)\r\n 64 - filter(\"V\".\"PROP_ID\"=82)\r\n \r\nNote\r\n-----\r\n - dynamic statistics used: dynamic sampling (level=2)\r\n - this is an adaptive plan (rows marked '-' are inactive)\r\n - 1 Sql Plan Directive used for this statement<\/pre>\n
-- without adaptive features\r\nSQL> alter session set optimizer_adaptive_features=false ;\r\n \r\nSession altered.\r\n \r\nSQL> select * from APPUSER.V_TAB_PROP where TAB_ID = 842300;\r\n...\r\n48 rows selected.\r\n\r\nElapsed: 00:00:00.23\r\n\r\n\r\n-- with adaptive features\r\nSQL> alter session set optimizer_adaptive_features=true;\r\n \r\nSession altered.\r\nSQL> select * from APPUSER.V_TAB_PROP where TAB_ID = 842300;\r\n...\r\n48 rows selected.\r\n \r\nElapsed: 00:00:13.84<\/pre>\n
\nBut there are still some “minor” misestimates, and the optimizer sometimes decides to create a SQL Plan directive:<\/p>\nSQL> select DIRECTIVE_ID, TYPE, ENABLED, REASON, NOTES from dba_sql_plan_directives where directive_id in (select directive_id from dba_sql_plan_dir_objects where object_name='TAB_PROP');\r\n\r\n DIRECTIVE_ID TYPE ENA REASON \r\nNOTES\r\n-------------------- ---------------- --- ------------------------------------ --------------------------------------------------------------------------------\r\n5347794880142580861 DYNAMIC_SAMPLING YES JOIN CARDINALITY MISESTIMATE \r\n<spd_note><internal_state>PERMANENT<\/internal_state><redundant>NO<\/redundant><spd_text>{F(APPUSER.TAB) - F(APPUSER.TAB_PROP)}<\/spd_text><\/spd_note>\r\n \r\n5473412518742433352 DYNAMIC_SAMPLING YES JOIN CARDINALITY MISESTIMATE \r\n<spd_note><internal_state>HAS_STATS<\/internal_state><redundant>NO<\/redundant><spd_text>{(APPUSER.TAB) - F(APPUSER.TAB) - F(APPUSER.TAB_PROP)}<\/spd_text><\/spd_note>\r\n \r\n14420228120434685523 DYNAMIC_SAMPLING YES JOIN CARDINALITY MISESTIMATE \r\n<spd_note><internal_state>HAS_STATS<\/internal_state><redundant>NO<\/redundant><spd_text>{F(APPUSER.CHAMP) - (APPUSER.TAB) - F(APPUSER.TAB) - F(APPUSER.TAB_PROP)}<\/spd_text><\/spd_note><\/pre>\n-- with dynamic sampling\/sql plan directive:\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows |\r\n-------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 48 |\r\n| 1 | NESTED LOOPS OUTER | | 1 | 11305 | 48 |\r\n| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 9 | 48 |\r\n|* 3 | INDEX RANGE SCAN | FK_TAB_PROP | 1 | 9 | 48 |\r\n| 4 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 1326 | 48 |\r\n|* 5 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n-------------------------------------------------------------------------------------------\r\n \r\n-- without dynamic sampling\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows |\r\n-------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 1 | | 48 |\r\n| 1 | NESTED LOOPS OUTER | | 1 | 13 | 48 |\r\n| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 9 | 48 |\r\n|* 3 | INDEX RANGE SCAN | FK_TAB_PROP | 1 | 9 | 48 |\r\n| 4 | TABLE ACCESS BY INDEX ROWID | TAB_PROP | 48 | 2 | 48 |\r\n|* 5 | INDEX UNIQUE SCAN | PK_TAB_PROP | 48 | 1 | 48 |\r\n-------------------------------------------------------------------------------------------<\/pre>\n
\nThe best solution in my case has been to disable the directive, an operation that can be done easily with the DBMS_SPD package:<\/p>\nBEGIN\r\n FOR rec in (select d.directive_id as did from dba_sql_plan_directives d join dba_sql_plan_dir_objects o on\r\n (d.directive_id=o.directive_id) where o.owner='APPUSER' and o.object_name in ('TAB','TAB_PROP'))\r\n LOOP\r\n DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE ( rec.did, 'ENABLED','NO');\r\n END LOOP;\r\nEND;\r\n\/<\/pre>\n
\nBecause the production system is not migrated to 12c yet, it’s wise to import these disabled directives in production before the optimizer creates and enables them.<\/p>\n-- export from the source\r\nSET SERVEROUTPUT ON\r\nDECLARE\r\n my_list DBMS_SPD.OBJECTTAB := DBMS_SPD.ObjectTab();\r\n dir_cnt NUMBER;\r\nBEGIN\r\n DBMS_SPD.CREATE_STGTAB_DIRECTIVE (table_name => 'TAB_PROP_DIRECTIVES', table_owner=> 'SYSTEM' );\r\n my_list.extend(2);\r\n \r\n -- TAB table\r\n my_list(1).owner := 'APPUSER';\r\n my_list(1).object_name := 'TAB';\r\n my_list(1).object_type := 'TABLE';\r\n -- TAB_PROP table\r\n my_list(2).owner := 'APPUSER';\r\n my_list(2).object_name := 'TAB_PROP';\r\n my_list(2).object_type := 'TABLE';\r\n \r\n dir_cnt :=\r\n dir_cnt :=\r\n DBMS_SPD.PACK_STGTAB_DIRECTIVE(table_name => 'TAB_PROP_DIRECTIVES', table_owner=> 'SYSTEM', obj_list => my_list);\r\n DBMS_OUTPUT.PUT_LINE('dir_cnt = ' || dir_cnt);\r\nEND;\r\n\/\r\n\r\nexpdp directory=data_pump_dir dumpfile=TAB_PROP_DIRECTIVES.dmp logfile=expdp_VAL_LIG_DIRECTIVES.log tables=system.TAB_PROP_DIRECTIVES\r\n\r\n-- import into the destination\r\nimpdp directory=data_pump_dir dumpfile=TAB_PROP_DIRECTIVES.dmp logfile=impdp_VAL_LIG_DIRECTIVES.log\r\n\r\nSELECT DBMS_SPD.UNPACK_STGTAB_DIRECTIVE(table_name => 'TAB_PROP_DIRECTIVES', table_owner=> 'SYSTEM') FROM DUAL;<\/pre>\n