Statistics Feedback (Previously as Cardinality Feedback)
Automatic Reoptimization
In automatic reoptimization, the optimizer changes a plan on subsequent executions after the initial execution.
Adaptive query plans are not feasible for all kinds of plan changes.
For example, a query with an inefficient join order might perform suboptimally, but adaptive query plans do not support adapting the join order during execution.
At the end of the first execution of a SQL statement, the optimizer uses the information gathered during execution to determine whether automatic reoptimization has a cost benefit.
If execution information differs significantly from optimizer estimates, then the optimizer looks for a replacement plan on the next execution.
The optimizer uses the information gathered during the previous execution to help determine an alternative plan.
The optimizer can reoptimize a query several times, each time gathering additional data and further improving the plan.
Reoptimization: Statistics Feedback
A form of reoptimization known as statistics feedback (formerly known as cardinality feedback) automatically improves plans for repeated queries that have cardinality misestimates.
The optimizer can estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates.
The basic process of reoptimization using statistics feedback is as follows:
1. During the first execution of a SQL statement, the optimizer generates an execution plan.
The optimizer may enable monitoring for statistics feedback for the shared SQL area in the following cases:
1-> Tables with no statistics
2-> Multiple conjunctive or disjunctive filter predicates on a table
3-> Predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates
2. At the end of the first execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution.
3. If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use.
The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution.
If the query executes again, then the optimizer uses the corrected cardinality estimates instead of its usual estimates.
The OPTIMIZER_ADAPTIVE_STATISTICS initialization parameter does not control all features of automatic reoptimization.
Specifically, this parameter controls statistics feedback for join cardinality only in the context of automatic reoptimization.
For example, setting OPTIMIZER_ADAPTIVE_STATISTICS to FALSE disables statistics feedback for join cardinality misestimates, but it does not disable statistics
feedback for single-table cardinality misestimates.
Demonstration
SQL> show parameter optimizer_ADAPTIVE_STATISTICS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_statistics boolean FALSE
SQL>
SQL> show parameter PARALLEL_DEGREE_POLICY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SQL>
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL>
Creating table
SQL> create table SPD_TECH as
select
'TESLA' as product,
mod(rownum,5) as shop_id,
mod(rownum,1000) as cons_id
from dual connect by level<=20000
UNION ALL
select
'AMAZON' as product,
mod(rownum,5) as shop_id,
mod(rownum,1000) as cons_id
from dual connect by level<=90
UNION ALL
select
'GOOGLE' as product,
mod(rownum,5) as shop_id,
mod(rownum,1000) as cons_id
from dual connect by level<=2000
UNION ALL
select
'MICROSOFT' as product,
mod(rownum,5) as shop_id,
mod(rownum,1000) as cons_id
from dual connect by level<=10000;
Table created.
Check last analyzed for this table
SQL> ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';
COL TABLE_NAME FOR A20
SET LINES 5000
SELECT TABLE_NAME,LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='SPD_TECH';SQL>
Session altered.
TABLE_NAME LAST_ANALYZED
-------------------- --------------------
SPD_TECH 20-dec-2021 00:50:49 <<<<<< Table got auto analyzed due to Bulk operations
Checking If Histogram exists
SQL > SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'SPD_TECH'
ORDER BY 1;
SQL>
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------
CONS_ID 1000 NONE
PRODUCT 4 NONE
SHOP_ID 5 NONE
Checking If Column Group Extension Exists
SQL> SELECT * FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='SPD_TECH';
no rows selected
Checking If Auto Column Group Extension Exists (SQL PLAN DIRECTIVES)
SQL> SET LONG 100000
SET LINES 120
SET PAGES 0
SELECT DBMS_STATS.REPORT_COL_USAGE('ABHI_TEST', 'SPD_TECH')
FROM DUAL;SQL> SQL> SQL> 2
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR ABHI_TEST.SPD_TECH
..........................................
###############################################################################
SQL>
Let we query from table
SQL> select cons_id , shop_id,product from SPD_TECH where product = 'TESLA' and shop_id=1 order by product ;
1 1 TESLA
6 1 TESLA
11 1 TESLA
16 1 TESLA
21 1 TESLA
26 1 TESLA
31 1 TESLA
36 1 TESLA
41 1 TESLA
46 1 TESLA
…
991 1 TESLA
996 1 TESLA
4000 rows selected.
Check E-Rows and A-Rows and this surggest that this SQL Statement is candidate for AUTO-REOPTIMIZATION
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
SQL_ID 861zhqzds4yw6, child number 0
-------------------------------------
select cons_id , shop_id,product from SPD_TECH where product =
'TESLA' and shop_id=1 order by product
Plan hash value: 4294636396
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4000 |00:00:00.01 | 355 | 85 |
|* 1 | TABLE ACCESS FULL| SPD_TECH | 1 | 1605 | 4000 |00:00:00.01 | 355 | 85 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='TESLA'))
19 rows selected.
Let we check if this SQL is REOPTIMIZABLE as per below Query
SQL> col SQL_ID for a20
col CHILD_NUMBER FOR 9
COL SQL_TEXT FOR A50
COL IS_REOPTIMIZABLE FOR A30
set lines 500
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE '%TESLA%' AND SQL_TEXT NOT LIKE '%V$SQL%' AND SQL_TEXT NOT LIKE '%create%';SQL> SQL> SQL> SQL> SQL> 2 3
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE
-------------------- ------------ -------------------------------------------------- ------------------------------
861zhqzds4yw6 0 select cons_id , shop_id,product from SPD_TECH Y
where product = 'TESLA' and shop_id=1 order by p
roduct
Execute Query again
SQL> select cons_id , shop_id,product from SPD_TECH where product = 'TESLA' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- ---------
1 1 TESLA
6 1 TESLA
11 1 TESLA
16 1 TESLA
21 1 TESLA
26 1 TESLA
….
…..
….
….
991 1 TESLA
996 1 TESLA
4000 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------
SQL_ID 861zhqzds4yw6, child number 2
-------------------------------------
select cons_id , shop_id,product from SPD_TECH where product =
'TESLA' and shop_id=1 order by product
Plan hash value: 4294636396
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4000 |00:00:00.01 | 355 |
|* 1 | TABLE ACCESS FULL| SPD_TECH | 1 | 4000 | 4000 |00:00:00.01 | 355 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='TESLA'))
Note
-----
- statistics feedback used for this statement
23 rows selected.
Now we can see E-Rows and A-Rows are as expected i.e 4000
Statistics feedback monitors and feeds back the following kinds of cardinalities:
• Single table cardinality (after filter predicates are applied)
• Index cardinality (after index filters are applied)
• Cardinality produced by a group by or distinct operator
• Cardinality produced by joins (in Oracle Database 12c when optimizer adaptive features/statistics = TRUE)
The improved estimates used by cardinality feedback are not persisted.
For this reason, it’s always preferable to use other techniques to get cardinality estimates right the first time every time, e.g. extended statistics, dynamic sampling, or SQL profiles.
But for cases where these techniques do not apply, cardinality feedback can provide some relief.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444