Infrastructure at your Service

Clemens Bleile

SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.

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.

Here is my testcase I did initially run in 12.1.0.2:

First I created a table with 4 columns: A, B, C and D and load it with 1000 rows:


10:25:27 [email protected]> create table DEMO_TABLE as
10:25:27 2 select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
10:25:27 3 from dual connect by level <=1000;
 
10:25:30 [email protected]> select * from DEMO_TABLE;
A B C D
---------- ---------- ---------- ----------
1 1 1 1
...
0 0 0 0
1 1 1 1
0 0 0 0

I.e. there is a correlation between the columns (all columns have a value 0 or all have a value 1).

Due to the new feature of stats gathering during CTAS and INSERT APPEND into empty tables in 12c the table has stats already:


10:28:29 [email protected]> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics
10:28:29 2 where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
------------ ------------ ---------- --------
A 2 .5 10:25:27
B 2 .5 10:25:27
C 2 .5 10:25:27
D 2 .5 10:25:27

Let’s run a query:


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

There is a misestimation of the calculated cardinality (E-Rows) versus actual cardinality (A-Rows) of a factor 8 (63:500).
Oracle detected that and put the cursor on IS_REOPTIMIZABLE=Y:


10:32:49 [email protected]> select sql_id,child_number,is_reoptimizable from v$sql
10:32:49 2 where sql_id='gbwsdbt5usvt6';
 
SQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
------------- ------------ ----------------
gbwsdbt5usvt6 0 Y

Executing the SQL again uses statistics feedback:


10:33:42 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 500 |00:00:00.01 | 44 |
|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 44 |
------------------------------------------------------------------------------------------
 
Note
-----
- statistics feedback used for this statement

The computed cardinality is correct now.

After flushing the SQL Plan Directives to the SYSAUX-tablespace I can see them with the internal_state NEW:


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

Executing a different SQL with the same type of predicate will use the SQL Plan Directive and dynmically gathers stats:


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

The internal_state of the directive changed to MISSING_STATS:


10:37:18 [email protected]> exec dbms_spd.flush_sql_plan_directive;
10:37:20 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ---------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
615512554562037875 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:34:36
<internal_state>MISSING_STATS</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>

The SPD is still being used until fresh stats are gathered. Oracle will create extended (multi column) statistics then:


10:38:27 [email protected]> select /*+ gather_plan_statistics */ distinct a,b,c,d from DEMO_TABLE
10:38:27 2 where a=0 and b=0 and c=0 and d=0;
 
A B C D
---------- ---------- ---------- ----------
0 0 0 0
 
10:38:27 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
 
10:39:42 [email protected]> exec dbms_spd.flush_sql_plan_directive;
10:40:01 [email protected]> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
------------ ------------ ---------- --------
A 2 .5 10:25:27
B 2 .5 10:25:27
C 2 .5 10:25:27
D 2 .5 10:25:27
 
10:40:03 [email protected]> exec dbms_stats.gather_table_stats(user,'DEMO_TABLE',options=>'GATHER AUTO',no_invalidate=>false);
 
10:40:05 [email protected]> select column_name,num_distinct,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT LAST_ANA
------------ ------------ --------
A 2 10:40:04
B 2 10:40:04
C 2 10:40:04
D 2 10:40:04
SYS_STSPJNMI 2 10:40:04
 
10:40:05 [email protected]> select extension_name,extension from user_stat_extensions where table_name='DEMO_TABLE';
 
EXTENSION_NAME
---------------
EXTENSION
----------------------------------------------------------------------------------------------------
SYS_STSPJNMIY_S
("A","B","C","D")

Running a SQL again still dynamically gatheres stats, but will change the internal state to HAS_STATS:


10:40:37 [email protected]> select /*+ gather_plan_statistics */ distinct b,c,d,a from DEMO_TABLE
10:40:37 2 where a=0 and b=0 and c=0 and d=0;
 
B C D A
---------- ---------- ---------- ----------
0 0 0 0
 
10:40:37 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
 
[email protected]> exec dbms_spd.flush_sql_plan_directive;
[email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ---------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
615512554562037875 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:34:36
<internal_state>HAS_STATS</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>
10:40:56 10:40:56

A next execution will use the extended stats instead of the directives:


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

Dropping the extended stats will result in cardinalilty misestimate and setting the directive to PERMANENT:


10:45:58 [email protected]> exec dbms_stats.drop_extended_stats(user,'DEMO_TABLE','("A","B","C","D")');
 
10:46:19 [email protected]> select /*+ gather_plan_statistics */ distinct a from DEMO_TABLE
10:46:19 2 where a=1 and b=1 and c=1 and d=1;
 
A
----------
1
 
10:46:19 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 62 | 500 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------
 
2 - filter(("A"=1 AND "B"=1 AND "C"=1 AND "D"=1))
 
10:47:10 [email protected]> exec dbms_spd.flush_sql_plan_directive;
10:47:10 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ---------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
615512554562037875 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:34:36
<internal_state>PERMANENT</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>

Future executions of such SQL will use the directive again:


10:47:46 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 11 |
|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 500 | 500 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(("A"=1 AND "B"=1 AND "C"=1 AND "D"=1))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement

That’s how it worked in 12.1.0.2. Here’s the behavior of 12.2.0.1:

First of all I have to enable adaptive statistics and switch on the preference to automatically create extended statistics:


10:49:22 [email protected]> alter system set optimizer_adaptive_statistics=true;
 
10:49:22 [email protected]> exec DBMS_STATS.SET_PARAM ('AUTO_STAT_EXTENSIONS','ON');

Creating the table with the appropriate stats is the same as in 12.1.:


10:50:23 [email protected]> create table DEMO_TABLE as
10:50:23 2 select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
10:50:23 3 from dual connect by level <=1000;
 
10:51:02 [email protected]> select * from DEMO_TABLE;
 
A B C D
---------- ---------- ---------- ----------
1 1 1 1
...
1 1 1 1
0 0 0 0
 
10:51:31 [email protected]> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics
10:51:31 2 where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
------------ ------------ ---------- --------
A 2 .5 10:50:23
B 2 .5 10:50:23
C 2 .5 10:50:23
D 2 .5 10:50:23

Let’s run the first query:


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

As in 12.1. we have the misestimate in the computed cardinality (E-Rows) versus actual cardinality (A-Rows).

In my initial testcase I detected a difference to 12.1.: The cursor is not marked as reoptimizable:


10:53:31 [email protected]> select sql_id,child_number,is_reoptimizable from v$sql
10:53:31 2 where sql_id='gbwsdbt5usvt6';
 
SQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
------------- ------------ ----------------
gbwsdbt5usvt6 0 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):


10:54:00 [email protected]> select sql_id,child_number,is_reoptimizable from v$sql
10:54:00 2 where sql_id='gbwsdbt5usvt6';
 
SQL_ID CHILD_NUMBER IS_REOPTIMIZABLE
------------- ------------ ----------------
gbwsdbt5usvt6 0 Y

As in 12.1. statistics feedback kicks in


10:54:20 [email protected]> select /*+ gather_plan_statistics */ * from DEMO_TABLE
10:54:20 2 where a=0 and b=0 and c=0 and d=0;
 
A B C D
---------- ---------- ---------- ----------
0 0 0 0
...
0 0 0 0
 
10:54:30 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 25000 |00:00:00.02 | 1780 |
|* 1 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.02 | 1780 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter(("A"=0 AND "B"=0 AND "C"=0 AND "D"=0))
 
Note
-----
- statistics feedback used for this statement

I can see a new SPD created:


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

And it’s being used:


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

The new feature in 12cR2 is the created Directive of type DYNAMIC_SAMPLING_RESULT:


10:59:31 [email protected]> exec dbms_spd.flush_sql_plan_directive;
10:59:31 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ----------------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:58:35
<internal_state>MISSING_STATS</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>
 
14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
<spd_note> 10:59:08
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
</spd_text>
</spd_note>

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.

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:


11:02:17 [email protected]> select column_name,num_distinct,density,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT DENSITY LAST_ANA
------------ ------------ ---------- --------
A 2 .5 10:50:23
B 2 .5 10:50:23
C 2 .5 10:50:23
D 2 .5 10:50:23
 
11:04:10 [email protected]> exec dbms_stats.gather_table_stats(user,'DEMO_TABLE',options=>'GATHER AUTO',no_invalidate=>false);
 
11:04:11 [email protected]> select column_name,num_distinct,last_analyzed from user_tab_col_statistics where table_name='DEMO_TABLE' order by 1;
 
COLUMN_NAME NUM_DISTINCT LAST_ANA
------------ ------------ --------
A 2 11:04:10
B 2 11:04:10
C 2 11:04:10
D 2 11:04:10
SYS_STSPJNMI 2 11:04:10
 
11:04:11 [email protected]> select extension_name,extension from user_stat_extensions where table_name='DEMO_TABLE';
 
EXTENSION_NAME
---------------
EXTENSION
----------------------------------------------------------------------------------------------------
SYS_STSPJNMIY_S
("A","B","C","D")

Once a query is excecuted again the internal state changes to HAS_STATS (same as in 12.1.):


11:04:33 [email protected]> select /*+ gather_plan_statistics */ distinct b,c,d,a from DEMO_TABLE
11:04:33 2 where a=0 and b=0 and c=0 and d=0;
 
B C D A
---------- ---------- ---------- ----------
0 0 0 0
 
11:04:33 [email protected]> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 120 |
| 1 | SORT UNIQUE NOSORT| | 1 | 1 | 1 |00:00:00.01 | 120 |
|* 2 | TABLE ACCESS FULL| DEMO_TABLE | 1 | 25000 | 25000 |00:00:00.01 | 120 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(("A"=0 AND "B"=0 AND "C"=0 AND "D"=0))
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
 
11:04:35 [email protected]> exec dbms_spd.flush_sql_plan_directive;
11:04:35 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ----------------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
18321513813433659475 DYNAMIC_SAMPLING SUPERSEDED SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:58:35
<internal_state>HAS_STATS</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>
 
14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
<spd_note> 10:59:08
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
</spd_text>
</spd_note>

The next query uses the stats instead of the SPD:


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

When dropping the extended stats the estimated cardinality is wrong again:


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

And, as in 12.1., the internal state changes to PERMANENT:


11:06:38 [email protected]> exec dbms_spd.flush_sql_plan_directive;
11:06:38 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ----------------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:58:35
<internal_state>PERMANENT</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>
 
14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
<spd_note> 10:59:08
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
</spd_text>
</spd_note>

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.


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

I.e. the Plan Directive is used again.


11:07:46 [email protected]> exec dbms_spd.flush_sql_plan_directive;
11:07:46 [email protected]> 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;
 
DIRECTIVE_ID TYPE STATE REASON
----------------------- ----------------------- ---------- ------------------------------------
NOTES CREATED
------------------------------------------------------------------------------------------ --------
LAST_MOD LAST_USE
-------- --------
18321513813433659475 DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
<spd_note> 10:58:35
<internal_state>PERMANENT</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.DEMO_TABLE)[A, B, C, D]}</spd_text>
</spd_note>
 
14490288466401772154 DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
<spd_note> 10:59:08
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.DEMO_TABLE, num_rows=1000) - (SQL_ID:7fp7c6kcgmzux, T.CARD=500[-2 -2])}
</spd_text>
</spd_note>

So in summary there are some changes in 12.2. compared to 12.1.:

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.
2.) 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://blog.dbi-services.com/12cr2-no-cardinality-feedback-for-small-queries/

Thanks to Stefan Köhler 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.

10 Comments

  • Hey Clemens,
    not quite sure about your observations with statistics feedback on 12.2, but in all my test cases (even with your code) – it works like expected (on 2nd execution) and described in the “Optimizer with Oracle Database 12c Release 2” white paper.

    I uploaded my reproducible test case here – you may can run it and check on your system: http://www.soocs.de/public/research/161223_statistics_feedback_122.txt

    The result should be like this:

    ================================
    -- Run test case with disabled optimizer_adaptive_statistics
    -- 1st run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
     
    -- 2nd run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
    g8yr3md0sxrqa 1 N 1 500
     
    -- 3rd run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
    g8yr3md0sxrqa 1 N 2 1000
     
    -- Run same test case with enabled optimizer_adaptive_statistics
    -- 1st run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
     
    -- 2nd run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
    g8yr3md0sxrqa 1 N 1 500
     
    -- 3rd run
    SQL_ID CHILD_NUMBER I EXECUTIONS ROWS_PROCESSED
    ------------- ------------ - ---------- --------------
    g8yr3md0sxrqa 0 Y 1 500
    g8yr3md0sxrqa 1 N 2 1000
    ================================

    May it be possible that heap 6 of your cursor has already been flushed out between your executions?

    Regards
    Stefan

    • Hello Stefan,

      you are right, running your testcase puts the cursor on IS_REOPTIMIZABLE=Y after first execution already (like in 12.1.). The difference in your and my testcase is that I do run a


      select * from DEMO_TABLE;

      just before the

      select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;

      E.g.

      08:13:20 [email protected]> select * from DEMO_TABLE;
       
      A B C D
      ---------- ---------- ---------- ----------
      1 1 1 1
      ...
      0 0 0 0
       
      1000 rows selected.
       
      08:13:20 [email protected]>
      08:13:20 [email protected]> -- 1st run
      08:13:20 [email protected]> select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;
       
      A B C D
      ---------- ---------- ---------- ----------
      0 0 0 0
      ...
      0 0 0 0
       
      500 rows selected.
       
      08:13:20 [email protected]> select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa';
       
      SQL_ID CHILD_NUMBER IS_REOPTIMIZABLE EXECUTIONS ROWS_PROCESSED
      ------------- ------------ ---------------- ---------- --------------
      g8yr3md0sxrqa 0 N 1 500

      But it’s actually not clear what caused the “select * from DEMO_TABLE” to change the statistics feedback in 12.2. for the subsequent select. Anyway, I have to consider a flaw in my testcase and will adjust it and update the blog accordingly.

      Thanks
      Clemens

      • Hey Clemens,
        thanks for clarification.

        Interestingly this behavior occurs only in case of “select * from DEMO_TABLE;” – for example it works as expected with a previous “select count(*) from DEMO_TABLE;”.

        This sounds more like a nasty bug than a wanted behavior to me 🙂

        Regards
        Stefan

  • Hi Clemens

    > But it’s actually not clear what caused the “select * from DEMO_TABLE” to change the statistics
    > feedback in 12.2. for the subsequent select.

    FWIW, I would check whether you see the feedback in v$sql_reoptimization_hints (before and after the additional query). Maybe it’s just that the additional query “flushes” the feedback…

    HTH
    Chris

    • Hi Chris,

      v$sql_reoptimization_hints is empty (before and after the additional query):

      11:04:08 [email protected]> alter system flush shared_pool;
      11:04:12 [email protected]> select * from v$sql_reoptimization_hints;
       
      no rows selected
       
      11:04:16 [email protected]> select * from DEMO_TABLE;
       
      A B C D
      ---------- ---------- ---------- ----------
      1 1 1 1
      ...
      0 0 0 0
       
      1000 rows selected.
       
      11:04:33 [email protected]> select * from v$sql_reoptimization_hints;
       
      no rows selected
       
      11:04:41 [email protected]> select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;
       
      A B C D
      ---------- ---------- ---------- ----------
      0 0 0 0
      ...
      0 0 0 0
       
      500 rows selected.
       
      11:05:04 [email protected]> select * from v$sql_reoptimization_hints;
       
      no rows selected
       
      11:05:08 [email protected]> alter system flush shared_pool;
      11:06:00 [email protected]> select * from v$sql_reoptimization_hints;
       
      no rows selected
       
      11:06:06 [email protected]> select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;
       
      A B C D
      ---------- ---------- ---------- ----------
      0 0 0 0
      ...
      0 0 0 0
       
      500 rows selected.
       
      11:06:13 [email protected]> select * from v$sql_reoptimization_hints;
       
      ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER HINT_ID
      ---------------- ---------- ------------- ------------ ----------
      HINT_TEXT
      -------------------------------------------------------------------
      CLIENT_ID REPARSE CON_ID
      ---------- ---------- ----------
      00000000622399C0 1099882186 g8yr3md0sxrqa 0 1
      OPT_ESTIMATE (@"SEL$1" TABLE "DEMO_TABLE"@"SEL$1" ROWS=500.000000 )
      1 1 0

      Thanks
      Clemens

  • Hi all,

    made a couple of tests: The change in behavior actually is actually introduced with bug fix/enhancement 23596611:


    alter session set "_fix_control"='23596611:OFF';
    drop table demo_table purge;
    create table DEMO_TABLE as
    select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
    from dual connect by level <=1000;
    alter system flush shared_pool;
     
    select * from DEMO_TABLE;
    ...
     
    select /*+ gather_plan_statistics */ * from DEMO_TABLE
    where a=0 and b=0 and c=0 and d=0;
    ...
     
    select sql_id,child_number,is_reoptimizable from v$sql
    where sql_id='gbwsdbt5usvt6';
     
    SQL_ID CHILD_NUMBER I
    ------------- ------------ -
    gbwsdbt5usvt6 0 Y

    There is no information available in MOS about bug 23596611, so the only thing I could find was from the description in v$system_fix_control:


    select description from v$system_fix_control where bugno=23596611;
     
    DESCRIPTION
    ----------------------------------------------------------------
    No feedback for small queries

    So obviously under specific circumstances statistics feedback is switched off for “small queries”. Probably an enhancement to not consider statistics feedback if a table is “small”.
    I could kind of confirm that by an additional test: When my table DEMO_TABLE had a couple of more blocks then the issue did not reproduce. I.e. I created my DEMO_TABLE this time with 10000 rows instead of 1000:


    connect demo/demo
    drop table demo_table purge;
    create table DEMO_TABLE as
    select mod(rownum,2) a ,mod(rownum,2) b ,mod(rownum,2) c ,mod(rownum,2) d
    from dual connect by level <=10000;
    alter system flush shared_pool;
     
    select * from DEMO_TABLE;
    ...
     
    select /*+ gather_plan_statistics */ * from DEMO_TABLE
    where a=0 and b=0 and c=0 and d=0;
    ...
     
    select sql_id,child_number,is_reoptimizable from v$sql
    where sql_id='gbwsdbt5usvt6';
     
    SQL_ID CHILD_NUMBER I
    ------------- ------------ -
    gbwsdbt5usvt6 0 Y

    But the issue still looks like a bug as the query “select * from DEMO_TABLE;” should not be related to the one with the predicate “where a=0 and b=0 and c=0 and d=0”.

    Regards
    Clemens

  • Hi Clemens,

    This is very interesting. I guess that the fact we run the first query makes the second one less expensive (in buffer gets and/or cpu time) because of things in cache already, and then the second query is below ‘small query’ threshold.
    With a larger table, reoptimization always occur.

    My guess that threshold is in buffer gets and/or cpu time comes from:

    $ strings $ORACLE_HOME/bin/oracle | grep "small query"
    kkocfbCheckCardEst: No feedback for small query (BG %f, CPU %f (ms)
    kkocfbCompareExecStats: skipping cost comparision forsmall query (BG %f, CPU %f (ms)

    I would love to know how to trace kkocfb…

    Regards,
    Franck.

    • And the threshold seems to be 100 buffer gets:

      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 95 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 96 cpu_time: 3000
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 96 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 97 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 97 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 98 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 98 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 99 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: N buffer_gets: 99 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 100 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 100 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 101 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 101 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 102 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 102 cpu_time: 1000
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 103 cpu_time: 0
      bq4fc1rdx97av is_reoptimizable: Y buffer_gets: 103 cpu_time: 1000

      This comes from same test case with one row per block and varying number of rows.

      • Andrew Sayer says:

        Your boundary figure of 100 doesn’t seem to relate to the A-Buffers in the post, we have 44 when it decided the cursor is not reoptimizable and 11 when it decided it was. This is the reverse of what you’d expect with a boundary of buffer gets.

        Or am I misunderstanding?

        • Clemens Bleile says:

          Hi Andrew,

          the issue with the 100 touched blocks was actually reproducible for me. You have to be careful though. Due to the fetch size (arraysize in sqlplus) a statement

          select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;

          may touch each block several times (e.g. with 150 rows per block and an arraysize of 15 you touch each block 10 times) to get the result transferred to the client. I.e. a table with 11 blocks may result in > 100 logical IOs and hence the statement is marked reoptimizable. However, a statement

          select distinct a from DEMO_TABLE where a=0 and b=0 and c=0 and d=0;

          may fetch only once and touch only the 11 blocks and the statement is NOT reoptimizable.
          I.e. it’s not about how many blocks you have in the table, but how many times you actually touch a block in your SQL.

          Regards
          Clemens

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clemens Bleile
Clemens Bleile

Technology Leader & Principal Consultant