{"id":9602,"date":"2016-12-21T18:04:24","date_gmt":"2016-12-21T17:04:24","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/"},"modified":"2016-12-21T18:04:24","modified_gmt":"2016-12-21T17:04:24","slug":"sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/","title":{"rendered":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1."},"content":{"rendered":"

During the Frankfurter IT Tage I provided a presentation about SQL Plan Directives and how to reproduce a testcase in a test environment for a plan with used SQL Plan Directives (SPD). In that context I did a couple of tests with 12cR2 (12.2.0.1) and wanted to blog about the change in behavior in that release. <\/p>\n

Here is my testcase I did initially run in 12.1.0.2:<\/p>\n

First I created a table with 4 columns: A, B, C and D and load it with 1000 rows:<\/p>\n


\n10:25:27 demo@GEN12102> create table DEMO_TABLE as
\n10:25:27 2 select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
\n10:25:27 3 from dual connect by level <=1000;
\n 
\n10:25:30 demo@GEN12102> select * from DEMO_TABLE;
\n A B C D
\n---------- ---------- ---------- ----------
\n 1 1 1 1
\n...
\n 0 0 0 0
\n 1 1 1 1
\n 0 0 0 0
\n<\/code><\/p>\n

I.e. there is a correlation between the columns (all columns have a value 0 or all have a value 1).<\/p>\n

Due to the new feature of stats gathering during CTAS and INSERT APPEND into empty tables in 12c the table has stats already:<\/p>\n


\n10:28:29 demo@GEN12102> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics
\n10:28:29 2 where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
\n------------ ------------ ---------- --------
\nA 2 .5 10:25:27
\nB 2 .5 10:25:27
\nC 2 .5 10:25:27
\nD 2 .5 10:25:27
\n<\/code><\/p>\n

Let’s run a query:<\/p>\n


\n10:29:40 demo@GEN12102> select \/*+ gather_plan_statistics *\/ * from DEMO_TABLE
\n10:29:40 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n...
\n 0 0 0 0
\n 0 0 0 0
\n 
\n10:29:40 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 500 |00:00:00.01 | 44 |
\n|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 63 | 500 |00:00:00.01 | 44 |
\n------------------------------------------------------------------------------------------
\n<\/code><\/p>\n

There is a misestimation of the calculated cardinality (E-Rows) versus actual cardinality (A-Rows) of a factor 8 (63:500).
\nOracle detected that and put the cursor on IS_REOPTIMIZABLE=Y:<\/p>\n


\n10:32:49 demo@GEN12102> select sql_id,child_number,is_reoptimizable from v$sql
\n10:32:49 2 where sql_id='gbwsdbt5usvt6';
\n 
\nSQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
\n------------- ------------ ----------------
\ngbwsdbt5usvt6 0 Y
\n<\/code><\/p>\n

Executing the SQL again uses statistics feedback:<\/p>\n


\n10:33:42 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 500 |00:00:00.01 | 44 |
\n|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 44 |
\n------------------------------------------------------------------------------------------
\n 
\nNote
\n-----
\n - statistics feedback used for this statement
\n<\/code><\/p>\n

The computed cardinality is correct now.<\/p>\n

After flushing the SQL Plan Directives to the SYSAUX-tablespace I can see them with the internal_state NEW:<\/p>\n


\n10:34:37 demo@GEN12102> exec dbms_spd.flush_sql_plan_directive;
\n10:34:39 demo@GEN12102> select directive_id,type,state,reason,notes,created,last_modified,last_used
\n10:34:39 2 from dba_sql_plan_directives where directive_id in(
\n10:34:39 3 select directive_id from dba_sql_plan_dir_objects where owner='DEMO'
\n10:34:39 4 ) order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ---------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 615512554562037875 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:34:36
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

Executing a different SQL with the same type of predicate will use the SQL Plan Directive and dynmically gathers stats:<\/p>\n


\n10:35:54 demo@GEN12102> select \/*+ gather_plan_statistics *\/ count(*) from DEMO_TABLE
\n10:35:54 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n COUNT(*)
\n----------
\n 500
\n 
\n10:35:54 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n<\/code><\/p>\n

The internal_state of the directive changed to MISSING_STATS:<\/p>\n


\n10:37:18 demo@GEN12102> exec dbms_spd.flush_sql_plan_directive;
\n10:37:20 demo@GEN12102> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in( select directive_id from dba_sql_plan_dir_objects where owner='DEMO' ) order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ---------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 615512554562037875 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:34:36
\n <internal_state>MISSING_STATS<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

The SPD is still being used until fresh stats are gathered. Oracle will create extended (multi column) statistics then:<\/p>\n


\n10:38:27 demo@GEN12102> select \/*+ gather_plan_statistics *\/ distinct a,b,c,d from DEMO_TABLE
\n10:38:27 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n 
\n10:38:27 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n 
\n10:39:42 demo@GEN12102> exec dbms_spd.flush_sql_plan_directive;
\n10:40:01 demo@GEN12102> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
\n------------ ------------ ---------- --------
\nA 2 .5 10:25:27
\nB 2 .5 10:25:27
\nC 2 .5 10:25:27
\nD 2 .5 10:25:27
\n 
\n10:40:03 demo@GEN12102> exec dbms_stats.gather_table_stats(user,'DEMO_TABLE',options=>'GATHER AUTO',no_invalidate=>false);
\n 
\n10:40:05 demo@GEN12102> select column_name,num_distinct,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT LAST_ANA
\n------------ ------------ --------
\nA 2 10:40:04
\nB 2 10:40:04
\nC 2 10:40:04
\nD 2 10:40:04
\nSYS_STSPJNMI 2 10:40:04
\n 
\n10:40:05 demo@GEN12102> select extension_name,extension from user_stat_extensions where table_name='DEMO_TABLE';
\n 
\nEXTENSION_NAME
\n---------------
\nEXTENSION
\n----------------------------------------------------------------------------------------------------
\nSYS_STSPJNMIY_S
\n(\"A\",\"B\",\"C\",\"D\")
\n<\/code><\/p>\n

Running a SQL again still dynamically gatheres stats, but will change the internal state to HAS_STATS:<\/p>\n


\n10:40:37 demo@GEN12102> select \/*+ gather_plan_statistics *\/ distinct b,c,d,a from DEMO_TABLE
\n10:40:37 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n B C D A
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n 
\n10:40:37 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n 
\ndemo@GEN12102> exec dbms_spd.flush_sql_plan_directive;
\ndemo@GEN12102> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO') order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ---------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 615512554562037875 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:34:36
\n <internal_state>HAS_STATS<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n10:40:56 10:40:56
\n<\/code><\/p>\n

A next execution will use the extended stats instead of the directives:<\/p>\n


\n10:45:10 demo@GEN12102> select \/*+ gather_plan_statistics *\/ distinct a,b,c,d from DEMO_TABLE
\n10:45:10 2 where a=1 and b=1 and c=1 and d=1;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 1 1 1 1
\n 
\n10:45:10 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=1 AND \"B\"=1 AND \"C\"=1 AND \"D\"=1))
\n<\/code><\/p>\n

Dropping the extended stats will result in cardinalilty misestimate and setting the directive to PERMANENT:<\/p>\n


\n10:45:58 demo@GEN12102> exec dbms_stats.drop_extended_stats(user,'DEMO_TABLE','(\"A\",\"B\",\"C\",\"D\")');
\n 
\n10:46:19 demo@GEN12102> select \/*+ gather_plan_statistics *\/ distinct a from DEMO_TABLE
\n10:46:19 2 where a=1 and b=1 and c=1 and d=1;
\n 
\n A
\n----------
\n 1
\n 
\n10:46:19 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 62 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n--------------------------------------------------
\n 
\n 2 - filter((\"A\"=1 AND \"B\"=1 AND \"C\"=1 AND \"D\"=1))
\n 
\n10:47:10 demo@GEN12102> exec dbms_spd.flush_sql_plan_directive;
\n10:47:10 demo@GEN12102> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO') order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ---------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 615512554562037875 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:34:36
\n <internal_state>PERMANENT<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

Future executions of such SQL will use the directive again:<\/p>\n


\n10:47:46 demo@GEN12102> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=1 AND \"B\"=1 AND \"C\"=1 AND \"D\"=1))
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n<\/code><\/p>\n

That’s how it worked in 12.1.0.2. Here’s the behavior of 12.2.0.1:<\/p>\n

First of all I have to enable adaptive statistics and switch on the preference to automatically create extended statistics:<\/p>\n


\n10:49:22 demo@GEN12201> alter system set optimizer_adaptive_statistics=true;
\n 
\n10:49:22 demo@GEN12201> exec DBMS_STATS.SET_PARAM ('AUTO_STAT_EXTENSIONS','ON');
\n<\/code><\/p>\n

Creating the table with the appropriate stats is the same as in 12.1.:<\/p>\n


\n10:50:23 demo@GEN12201> create table DEMO_TABLE as
\n10:50:23 2 select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
\n10:50:23 3 from dual connect by level <=1000;
\n 
\n10:51:02 demo@GEN12201> select * from DEMO_TABLE;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 1 1 1 1
\n...
\n 1 1 1 1
\n 0 0 0 0
\n 
\n10:51:31 demo@GEN12201> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics
\n10:51:31 2 where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
\n------------ ------------ ---------- --------
\nA 2 .5 10:50:23
\nB 2 .5 10:50:23
\nC 2 .5 10:50:23
\nD 2 .5 10:50:23
\n<\/code><\/p>\n

Let’s run the first query:<\/p>\n


\n10:51:42 demo@GEN12201> select \/*+ gather_plan_statistics *\/ * from DEMO_TABLE
\n10:51:42 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n..
\n 0 0 0 0
\n 0 0 0 0
\n 
\n10:51:42 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 500 |00:00:00.01 | 44 |
\n|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 63 | 500 |00:00:00.01 | 44 |
\n------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n--------------------------------------------------
\n 
\n 1 - filter((\"A\"=0 AND \"B\"=0 AND \"C\"=0 AND \"D\"=0))
\n<\/code><\/p>\n

As in 12.1. we have the misestimate in the computed cardinality (E-Rows) versus actual cardinality (A-Rows).<\/p>\n

In my initial testcase I detected a difference to 12.1.: The cursor is not marked as reoptimizable:<\/p>\n


\n10:53:31 demo@GEN12201> select sql_id,child_number,is_reoptimizable from v$sql
\n10:53:31 2 where sql_id='gbwsdbt5usvt6';
\n 
\nSQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
\n------------- ------------ ----------------
\ngbwsdbt5usvt6 0 N
\n<\/code><\/p>\n

REMARK: That happens only when the query touches less than 100 Blocks. It turned out that the change in behavior is caused by fix\/enhancement 23596611. More details in the comments after the blog. The test continues here with a bigger DEMO-table (>100 Blocks):<\/p>\n


\n10:54:00 demo@GEN12201> select sql_id,child_number,is_reoptimizable from v$sql
\n10:54:00 2 where sql_id='gbwsdbt5usvt6';
\n 
\nSQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
\n------------- ------------ ----------------
\ngbwsdbt5usvt6 0 Y
\n<\/code><\/p>\n

As in 12.1. statistics feedback kicks in<\/p>\n


\n10:54:20 demo@GEN12201> select \/*+ gather_plan_statistics *\/ * from DEMO_TABLE
\n10:54:20 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n...
\n 0 0 0 0
\n 
\n10:54:30 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 25000 |00:00:00.02 | 1780 |
\n|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.02 | 1780 |
\n------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 1 - filter((\"A\"=0 AND \"B\"=0 AND \"C\"=0 AND \"D\"=0))
\n 
\nNote
\n-----
\n - statistics feedback used for this statement
\n<\/code><\/p>\n

I can see a new SPD created:<\/p>\n


\n10:58:37 demo@GEN12201> exec dbms_spd.flush_sql_plan_directive;
\n10:58:39 demo@GEN12201> select directive_id,type,state,reason,notes,created,last_modified,last_used
\n10:58:39 2 from dba_sql_plan_directives where directive_id in(
\n10:58:39 3 select directive_id from dba_sql_plan_dir_objects where owner='DEMO'
\n10:58:39 4 ) order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ----------------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:58:35
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

And it’s being used:<\/p>\n


\n10:59:08 demo@GEN12201> select \/*+ gather_plan_statistics *\/ count(*) from DEMO_TABLE
\n10:59:08 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n COUNT(*)
\n----------
\n 500
\n 
\n10:59:08 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
\n| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 120 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.01 | 120 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=0 AND \"B\"=0 AND \"C\"=0 AND \"D\"=0))
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n<\/code><\/p>\n

The new feature in 12cR2 is the created Directive of type DYNAMIC_SAMPLING_RESULT:<\/p>\n


\n10:59:31 demo@GEN12201> exec dbms_spd.flush_sql_plan_directive;
\n10:59:31 demo@GEN12201> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in( select directive_id from dba_sql_plan_dir_objects where owner='DEMO' ) order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ----------------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:58:35
\n <internal_state>MISSING_STATS<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n 
\n 14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
\n<spd_note> 10:59:08
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
\n<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

I.e. the result of the dynamically sampled data is stored in the repository. The SQL_ID 7fp7c6kcgmzux if a stripped Dynamic Sampling query is stored, so that the result can be reused by other queries in future. I.e. Oracle just has to generate the SQL_ID of the Dynamic Sampling query and can use a prior result if it finds it in the repository.<\/p>\n

As the internal state of the directive is on MISSING_STATS and the DBMS_STATS-preference AUTO_STAT_EXTENSIONS is set to ON, Oracle will create extended stats when gathering stats next time:<\/p>\n


\n11:02:17 demo@GEN12201> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
\n------------ ------------ ---------- --------
\nA 2 .5 10:50:23
\nB 2 .5 10:50:23
\nC 2 .5 10:50:23
\nD 2 .5 10:50:23
\n 
\n11:04:10 demo@GEN12201> exec dbms_stats.gather_table_stats(user,'DEMO_TABLE',options=>'GATHER AUTO',no_invalidate=>false);
\n 
\n11:04:11 demo@GEN12201> select column_name,num_distinct,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
\n 
\nCOLUMN_NAME NUM_DISTINCT LAST_ANA
\n------------ ------------ --------
\nA 2 11:04:10
\nB 2 11:04:10
\nC 2 11:04:10
\nD 2 11:04:10
\nSYS_STSPJNMI 2 11:04:10
\n 
\n11:04:11 demo@GEN12201> select extension_name,extension from user_stat_extensions where table_name='DEMO_TABLE';
\n 
\nEXTENSION_NAME
\n---------------
\nEXTENSION
\n----------------------------------------------------------------------------------------------------
\nSYS_STSPJNMIY_S
\n(\"A\",\"B\",\"C\",\"D\")
\n<\/code><\/p>\n

Once a query is excecuted again the internal state changes to HAS_STATS (same as in 12.1.):<\/p>\n


\n11:04:33 demo@GEN12201> select \/*+ gather_plan_statistics *\/ distinct b,c,d,a from DEMO_TABLE
\n11:04:33 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n B C D A
\n---------- ---------- ---------- ----------
\n 0 0 0 0
\n 
\n11:04:33 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 120 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.01 | 120 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=0 AND \"B\"=0 AND \"C\"=0 AND \"D\"=0))
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n 
\n11:04:35 demo@GEN12201> exec dbms_spd.flush_sql_plan_directive;
\n11:04:35 demo@GEN12201> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO') order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ----------------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 18321513813433659475 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:58:35
\n <internal_state>HAS_STATS<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n 
\n 14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
\n<spd_note> 10:59:08
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
\n<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

The next query uses the stats instead of the SPD:<\/p>\n


\n11:05:23 demo@GEN12201> select \/*+ gather_plan_statistics *\/ distinct a,b,c,d from DEMO_TABLE
\n11:05:23 2 where a=1 and b=1 and c=1 and d=1;
\n 
\n A B C D
\n---------- ---------- ---------- ----------
\n 1 1 1 1
\n 
\n11:05:23 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 120 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.01 | 120 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=1 AND \"B\"=1 AND \"C\"=1 AND \"D\"=1))
\n<\/code><\/p>\n

When dropping the extended stats the estimated cardinality is wrong again:<\/p>\n


\n11:05:49 demo@GEN12201> exec dbms_stats.drop_extended_stats(user,'DEMO_TABLE','(\"A\",\"B\",\"C\",\"D\")');
\n 
\n11:05:57 demo@GEN12201> select \/*+ gather_plan_statistics *\/ distinct a from DEMO_TABLE
\n11:05:57 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n A
\n----------
\n 1
\n 
\n11:05:57 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 120 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 3125 | 25000 |00:00:00.01 | 120 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=1 AND \"B\"=1 AND \"C\"=1 AND \"D\"=1))
\n<\/code><\/p>\n

And, as in 12.1., the internal state changes to PERMANENT:<\/p>\n


\n11:06:38 demo@GEN12201> exec dbms_spd.flush_sql_plan_directive;
\n11:06:38 demo@GEN12201> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO') order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ----------------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:58:35
\n <internal_state>PERMANENT<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n 
\n 14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
\n<spd_note> 10:59:08
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
\n<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

REMARK: If the query would have touched less than 100 blocks then the state would have remeained on “HAS_STATS” and dynamic statistics wouldn’t be activated.<\/p>\n


\n11:07:16 demo@GEN12201> select \/*+ gather_plan_statistics *\/ distinct b from DEMO_TABLE
\n11:07:16 2 where a=0 and b=0 and c=0 and d=0;
\n 
\n B
\n----------
\n 1
\n 
\n11:07:16 demo@GEN12201> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
\n 
\n-------------------------------------------------------------------------------------------
\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
\n-------------------------------------------------------------------------------------------
\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
\n| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 120 |
\n|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.01 | 120 |
\n-------------------------------------------------------------------------------------------
\n 
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n 
\n 2 - filter((\"A\"=0 AND \"B\"=0 AND \"C\"=0 AND \"D\"=0))
\n 
\nNote
\n-----
\n - dynamic statistics used: dynamic sampling (level=2)
\n - 1 Sql Plan Directive used for this statement
\n<\/code><\/p>\n

I.e. the Plan Directive is used again.<\/p>\n


\n11:07:46 demo@GEN12201> exec dbms_spd.flush_sql_plan_directive;
\n11:07:46 demo@GEN12201> select directive_id,type,state,reason,notes,created,last_modified,last_used from dba_sql_plan_directives where directive_id in(select directive_id from dba_sql_plan_dir_objects where owner='DEMO') order by created;
\n 
\n DIRECTIVE_ID TYPE STATE REASON
\n----------------------- ----------------------- ---------- ------------------------------------
\nNOTES CREATED
\n------------------------------------------------------------------------------------------ --------
\nLAST_MOD LAST_USE
\n-------- --------
\n 18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
\n<spd_note> 10:58:35
\n <internal_state>PERMANENT<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}<\/spd_text>
\n<\/spd_note>
\n 
\n 14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
\n<spd_note> 10:59:08
\n <internal_state>NEW<\/internal_state>
\n <redundant>NO<\/redundant>
\n <spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
\n<\/spd_text>
\n<\/spd_note>
\n<\/code><\/p>\n

So in summary there are some changes in 12.2. compared to 12.1.:<\/p>\n

1.) When executing a query which can use a SQL Plan Directive the first time, a new SQL Plan Directive of type DYNAMIC_SAMPLING_RESULT will be created. Future queries can use that result.
\n2.) If a query touches less than 100 blocks then statistics feedback (cardinality feedback in 11g) does NOT happen. See Franck’s Blog about that new feature: http:\/\/dbi-services.com\/blog\/12cr2-no-cardinality-feedback-for-small-queries\/<\/p>\n

Thanks to Stefan K\u00f6hler a strange behavior of my initial testcase in the context of statistics feedback was detected. Some more infos about that topic are available in the comments of this Blog.<\/p>\n","protected":false},"excerpt":{"rendered":"

During the Frankfurter IT Tage I provided a presentation about SQL Plan Directives and how to reproduce a testcase in a test environment for a plan with used SQL Plan Directives (SPD). In that context I did a couple of tests with 12cR2 (12.2.0.1) and wanted to blog about the change in behavior in that […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[224,1007,958,1008,715,1009],"type_dbi":[],"class_list":["post-9602","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-12-1-0-2","tag-12-2-0-1","tag-12cr2","tag-sql-plan-directive","tag-sql-plan-directives","tag-statistics-feedback"],"acf":[],"yoast_head":"\nSQL Plan Directives in 12cR2. Behavior is different than in 12cR1. - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.\" \/>\n<meta property=\"og:description\" content=\"During the Frankfurter IT Tage I provided a presentation about SQL Plan Directives and how to reproduce a testcase in a test environment for a plan with used SQL Plan Directives (SPD). In that context I did a couple of tests with 12cR2 (12.2.0.1) and wanted to blog about the change in behavior in that […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-21T17:04:24+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"18 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.\",\"datePublished\":\"2016-12-21T17:04:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\"},\"wordCount\":741,\"commentCount\":0,\"keywords\":[\"12.1.0.2\",\"12.2.0.1\",\"12cR2\",\"SQL Plan Directive\",\"SQL Plan Directives\",\"statistics feedback\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\",\"name\":\"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1. - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-12-21T17:04:24+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1. - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/","og_locale":"en_US","og_type":"article","og_title":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.","og_description":"During the Frankfurter IT Tage I provided a presentation about SQL Plan Directives and how to reproduce a testcase in a test environment for a plan with used SQL Plan Directives (SPD). In that context I did a couple of tests with 12cR2 (12.2.0.1) and wanted to blog about the change in behavior in that […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/","og_site_name":"dbi Blog","article_published_time":"2016-12-21T17:04:24+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"18 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.","datePublished":"2016-12-21T17:04:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/"},"wordCount":741,"commentCount":0,"keywords":["12.1.0.2","12.2.0.1","12cR2","SQL Plan Directive","SQL Plan Directives","statistics feedback"],"articleSection":["Database Administration & Monitoring","Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/","name":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1. - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-12-21T17:04:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Plan Directives in 12cR2. Behavior is different than in 12cR1."}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9602"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9602\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9602"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}