SQL plan directives and excessive dynamic sampling

Part of the Adaptive Execution plan, part of the adaptive statistics.

Notice here,
Adaptive plans– was changed from default plan to adaptive plan the first time it was executed.
Automatic re-optimization-it was at the second execution.
Statistics Feedback formerly called cardinality Feedback
Dynamic Statistics formerly called Dynamic sampling
SQL Plan directives (SPD) so far as 12.1, you can think of the persistence of dynamic sampling

Well, the topic we're discussing today is the last one, SQL plan directives (SPD).

Let's look at the SPD together. In the June 2013 Oracle's official white paper, "Optimizer in Oracle Database 12c", mentions:

The SPD is automatically created based on information obtained from automatic optimization. SQL Plan directives are additional information that the optimizer uses to generate better execution plans. For example, when joining two tables with data deviations in their join columns, the SQL plan instruction instructs the optimizer to obtain a more accurate join cardinality estimate using dynamic statistics.

Therefore, when the SQL first run, Oracle found that the value of statistical information estimates and the actual implementation of the value of the discovery Gap is large (misestimate), need to be optimized, will generate SPD. That is, if we see V$sql's is_reoptimizable field as Y, the statement needs to be tuned, and the SPD intervenes at the second execution, or similar to SQL execution. In 12.1, the only type of SPD is the dynamic sampling (dynamical sampling).

Oracle will get the SPD involved in the misestimate situation. From the information collected at present, the following base is not allowed, will let Oracle think Misestimate.
Single Table Cardinality misestimate
Join Cardinality Misestimate
Query block cardinality misestimate
GROUP BY cardinality misestimate
having cardinality misestimate

Let's look at an example of this.
1. I create a table and generate some data to collect statistical information.

--Initialization
Conn Test/test
drop table big_table;

CREATE TABLE big_table as
Select ' IPhone ' as product,
MoD (rownum, 5) as channel_id,
MoD (rownum, 1000) as cust_id
From dual
Connect by Level <= 2000000
UNION All
Select ' Motorola ' as product,
MoD (rownum, 5) as channel_id,
MoD (rownum, 1000) as cust_id
From dual
Connect by Level <= 10
UNION All
Select ' Nokia ' as product,
MoD (rownum, 5) as channel_id,
MoD (rownum, 1000) as cust_id
From dual
Connect by Level <= 20401
UNION All
Select ' Samsung ' as product,
MoD (rownum, 5) as channel_id,
MoD (rownum, 1000) as cust_id
From dual
Connect by level <= 1000000;


exec dbms_stats.gather_table_stats (user, ' big_table ', cascade=>true);

Exit

2. Remove all existing SPD and empty shared pool:

Sqlplus-s "/As SYSDBA"
Set pages 0
Set Line 10000
Set echo off
Set Feedback off
Set Heading off
Set Trimspool on
Spool Drop_spd.sql
Select ' EXEC dbms_spd. Drop_sql_plan_directive (' | | | | directive_id| | ' | '; From Dba_sql_plan_directives;
Spool off
Exit

Sqlplus "/as sysdba"
@drop_spd
alter system flush Shared_pool;

3. Execute this SQL for the first time:

--testtime 1:
Conn Test/test
Set Line 1000
Set pages 1000
Set Termout off
Select/*+gather_plan_statistics*/
cust_id, channel_id, product
From big_table
where product = ' Motorola '
and channel_id = 1
ORDER BY Product
/

select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));
P1

We see that even when statistics are collected, the e-rows and A-rows are far from being executed.

We check the V$sql is_reoptimizable field and we can see that Y.

Col directive_id for 999999999999999999999
Col OWNER for A10
Col object_name for A10
Col Subobject_name for A10
Col Sql_text for A90
Col Spd_text for A45
Col internal_state for A30
Select sql_id, Child_number, Sql_text, is_reoptimizable from V$sql where Sql_text like '%+gather_plan_statistics%big_ Table% '
/
P2

Let's take a look at the SPD information at this point, we used 2 views: Dba_sql_plan_dir_objects and Dba_sql_plan_directives. Note Because the SPD is every 15 minutes before the brush into the data dictionary persisted, we manually carry out flush once, we do not have to wait 15 minutes, you can directly see.

Conn/as SYSDBA
EXEC dbms_spd. flush_sql_plan_directive;

Select Aa.directive_id,aa.owner,aa.object_name,aa.subobject_name,aa.object_type,
State
Extract (Bb.notes, '/spd_note/internal_state/text () ') Internal_state,
Extract (Bb.notes, '/spd_note/spd_text/text () ') as Spd_text,
Bb.type,bb.reason from Dba_sql_plan_dir_objects aa,dba_sql_plan_directives bb
where aa.directive_id=bb.directive_id and Aa.object_name in (' big_table ')
ORDER BY 10,1
/
P3

As you can see (dot image magnification), the SPD is targeting the object level, which is my table big_table and its fields product and channel_id. The conditions used were: where product = ' Motorola ' and channel_id = 1, by actual execution, Oracle thought that this table, and the 2 fields of information is not allowed.

A few of the fields in the above illustration are slightly explained:
A.spd_text is {EC (TEST). big_table) [channel_id, PRODUCT]}. Oracle thinks you used these 2 fields for querying, and these 2 fields lack federated statistics. Here the E and C, as well as the other characters that may appear, are interpreted as follows:
E–equality_predicates_only
C–simple_column_predicates_only
J–index_access_by_join_predicates
F–filter_on_joining_object
For example, usually such a condition would consider the following information misestimate:

From Demo_table where A=1 and B=1 and C=1 and d=1;
{ec (DEMO. demo_table) [A, B, C, D]}
Missing_stats, has_stats with extended statistics

from Demo_table where a+b=c+d;
{e (DEMO. demo_table) [A, B, C, D]}
Missing_stats, permanent as no statistics can help

From DEMO1 join DEMO2 using (KEY) where demo2.a=1;
{(DEMO. DEMO1) –f (DEMO. DEMO2)}

B.internal_state is new, which means that for the first time, we can see how the second execution will be.
new–1st Pass
Missing_stats–needs Extended STATS (gathered automagically)
Has_stats–extended STATS have now been gathered (intermediate state–new statements may still need SPD ' s)
Permanent–extended stats have now been gathered (but SPD still needed because of!= predicates)

C. Type is dynamic_sampling, which means that the next time it executes, dynamic sampling is performed if this SPD is involved.

D.reason said why Oracle thought the statement required SPD intervention. Because Oracle considers single TABLE cardinality misestimate.
The values for this field are:
Single Table Cardinality misestimate
Join Cardinality Misestimate
Query block cardinality misestimate
GROUP BY cardinality misestimate
having cardinality misestimate
This field should be combined with the notes after extract, which is the spd_text above.

OK, let's take a look at this, there is no dynamic sampling intervention. Let's take a look at the v$sql in the DS_SVC hint sql:

Select Sql_text from V$sql where sql_text like '%ds_svc% '
/
1
2
Select Sql_text from V$sql where sql_text like '%ds_svc% '
/
P4
We see. There is no dynamic sampling intervention at this time.

4. We will continue to test, the same SQL statement, the second execution of the situation:
P5
We see that this time the e-rows has been the same as a-rows, that is, at this time again, cursor in the cache, the direct use of statistics feedback, generate child number 1 cursor. Note that dynamic sampling is still not involved at this time.
P7

5. If we flush shared pool, let's see what happens:

Conn/as SYSDBA
alter system flush Shared_pool;

--testtime 1:
Conn Test/test
Set Line 1000
Set pages 1000
Set Termout off
Select/*+gather_plan_statistics*/
cust_id, channel_id, product
From big_table
where product = ' Motorola '
and channel_id = 1
ORDER BY Product
/

select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));
P8
We see that when the same SQL takes place hard parsing, the SPD intervenes to perform dynamic sampling.

Let's take a look at the dynamic sampling information in the V$sql:
P9
You can see that there are already a lot of statements about ds_svc dynamic sampling.

Note that the dynamic sampling here is not caused by ads (Automatic dynamic Statistics), but by the SPD. So, 12c dynamic sampling is much more than 11g, to a large extent, is caused by the SPD.

11g tables If statistics are collected, they are no longer dynamically sampled. In 12c, even if the table collects statistics, it will be triggered by the SPD for dynamic sampling.

Let's take a look at the information in the SPD:
P10
Note that the internal state here has changed from new to Missing_stats.

Here, you may have realized that the SPD will cause more than 11g of dynamic sampling, but does it just affect the same SQL?

As I said before, the SPD is object-level, not SQL-level, so when I have a similar SQL trigger, the first time I parse it, the SPD gets involved.

6. I'm going to run a "similar" SQL:

--testtime 2:
Conn Test/test
Set Line 1000
Set pages 1000
Set Termout off
Select/*+gather_plan_statistics*/
cust_id, channel_id, product
From big_table
where product = ' Nokia '
and channel_id = 1
Order BY Product;

select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));
P11
As you can see, the SPD,SPD guidance optimizer is also triggered to perform dynamic sampling again.

And because of the first run, the dynamic sampling, and e-rows and a-rows gap between the small, oracle that it does not need to is_reoptimizable:
P12

7. Run another "similar" sql:

--testtime 3:
Conn Test/test
Set Line 1000
Set pages 1000
Set Termout off
Select/*+gather_plan_statistics*/
cust_id, channel_id, product
From big_table
where product = ' IPhone '
and channel_id = 4
Order BY Product;

select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats last '));
In the same way, the SPD intervenes to perform dynamic sampling, but Is_reoptimizable is n.
P13

P14

However, the ds_svc at this time, because of these "similar" SQL dynamic sampling, in the v$sql gradually become more.
P15

Note: "Similar" refers to statements where Product = ' Motorola ' and channel_id = 1 are also in the statement. As long as it is such a statement, the SPD will intervene.

I just ran 4, and there were 15 recursive SQL for dynamic sampling, which is more serious in a production environment, such as a total of more than 90,000 SQL in V$sql, of which more than 70,000 are such SQL with DS_SVC.

And because each dynamic sampling in 12c need to be result cache, this time will appear result cache latch contention. See document 2002089.1 High Latch free Waits on ' result cache:rc Latch ' in 12C when result_cache_mode = MANUAL

From the above test can be seen, the SPD intervention should be compared to the former, when the same statement is executed again, if the cache has been cached, the use of statistics Feedback, if there is no cache, at the beginning of the hard parse intervened in the SPD, If the SPD information is still missing, request dynamic sampling, then in the subsequent execution, all walked the dynamic sampling; If the SPD missing information has been collected, then the SPD from usable update to superseded, at this time do not take dynamic sampling;

In addition, the same is true when a "similar" statement enters.

Using the flowchart, the basic is the Green box section of the following figure: (The whole picture is Adaptive query optimization)
P16

So, what kind of situation will let the SPD think that the information has been collected, the State column from usable into superseded,internal_state column from Missing_stats to Has_stats. Judging from my test, the conditions are very strict. Not only do you collect histograms for all columns, but also collect (channel_id, PRODUCT) extended stats. After the collection, again hard parsing, will not take the dynamic sampling.

Select
Dbms_stats.create_extended_stats
(' TEST ', ' big_table ', ' (channel_id,product) ')
from dual;

EXEC dbms_stats.gather_table_stats (' TEST ', ' big_table ', method_opt => ' for all columns size 254 ');

Select Table_name,column_name,histogram from Dba_tab_col_statistics where table_name= ' big_table ';

alter system flush Shared_pool;

--testtime 2:
Conn Test/test
Set Line 1000
Set pages 1000
Set Termout off
Select/*+gather_plan_statistics*/
cust_id, channel_id, product
From big_table
where product = ' Nokia '
and channel_id = 1
Order BY Product;
P17

Check the state of the SPD and you can see that it becomes as follows:
P18

At this point, the dynamic sampling is no longer performed. (E-rows is exactly equal to a-rows due to very accurate statistics collected)
P19

So since the SPD is so easy to create dynamic sampling, and dynamic sampling is easy to have result cache latch (can modify the _optimizer_ads_use_result_cache=false to allow dynamic sampling not into the result cache), And even if the dynamic sampling of result cache is turned off, or is it easy to accumulate a large number of DS_SVC hint recursive SQL in the V$sql, how can we solve the dynamic sampling caused by the SPD by consuming the shared pool?

Solution:

1. Disable adaptive query optimization. Optimizer_adaptive_features = FALSE, this is the largest total switch.
2. Disable SPD to generate new Directive:_sql_plan_directive_mgmt_control = 0 (Note also to change the original existing directive to disable or drop)
3. Disable dynamic sampling of SPD: _optimizer_dsdir_usage_control = 0