Cardinality Feedback (CFB) is a feature of the Oracle Optimizer for correcting cardinality estimation (i.e., number of rows returned by each operation) in execution plans. Rather than going to endless complications to determine the right cardinality, we just wait for the result of each step in the execution plan, store it in the shared pool and reference it on subsequent executions, in the hope that the information will give us a good idea of how well we did the last time.
CFB is a feature of Oracle 11gR2. For more details read the following post.
In this post, we will show CFB in action.
We will run a query (with CFB enabled) for which the optimizer fails (to a great extent) to estimate the cardinality of certain operations. This results in a suboptimal plan chosen by the optimizer. The next time we run the query, the optimizer uses CFB to improve its estimation and generates another (hopefully better) plan. This continues for the next executions until finally the plan stabilizes. Then we flush the shared pool (in order to wipe out all the relevant cursors for this query) and we disable the CFB feature and rerun the query. This time we see that the plan does not change, no matter how many times we execute the query. Lets see it in action:
This is our query:
------------------------------------------------------------------------------------------------------------
We have run the query and we show above the execution plan. If you check the estimated rows (E-Rows) versus the actual rows (A-Rows) it is obvious that for certain operations (e.g., for the group by operation (4)) the cardinality estimation is by far wrong. This means that the plan (plan hash value 2264303152) probably is not the best one. This calls for CFB to kick in. Lets rerun the query.
------------------------------------------------------------------------------------------------------------
Check out the plan after the second run. We see that a new child cursor has been created (child number 1) and a new execution plan (corresponding to a new plan hash value). This time the estimated rows for the grouping operation (1) has been greatly improved (E-Rows = 38 versus A-Rows = 22). However there are other operations with a great gap between estimated and actual. This means that CFB will continue its work in the next executions also. Also see at the "Note" section of the plan that it is explicitly stated that cardinality feedback has been used by the optimizer.
We will continue to run the query several times. Each time we run the query and CFB kicks in, a new child cursor is created with a new plan. The old cursors are marked as "not shareable", which means that are garbage waiting to be aged out from the shared pool with the next garbage collection.
Lets see the corresponding cursors for the SQL_ID after we have run the query for 10 more times.
------------------------------------------------------------------------------------------------------------
Each line corresponds to a different child cursor (the script uses V$SQL), which has a different execution plan denoted by the PLAN_HASH column. The EXEC column denotes how many time this cursor has been executed. The column IS_SHAREABLE denotes if the cursor is shared ('Y') or not, which means that it is not used any more. From the first 5 rows we infer that it took 5 executions for the optimizer to stabilize the plan. And indeed it is the last row (child number 5) which relates to the final plan. So you see, CFB does not run forever. At some point it stabilizes to a specific plan.
Now, if we disable the CFB feature then we can see that for the same query, the optimizer from the very first execution, sticks with a single execution plan. We can disable cardinality feedback by setting the hidden parameter _OPTIMIZER_USE_FEEDBACK. Lets see it in action:
First we flush the shared pool in order to wipe out all the available cursors.
------------------------------------------------------------------------------------------------------------
We have disabled CFB and then executed the query 12 times. We query V$SQL and this time we see only a single child cursor and only one execution plan. This is how CFB works!
Enjoy!
Oracle Learner
CFB is a feature of Oracle 11gR2. For more details read the following post.
In this post, we will show CFB in action.
We will run a query (with CFB enabled) for which the optimizer fails (to a great extent) to estimate the cardinality of certain operations. This results in a suboptimal plan chosen by the optimizer. The next time we run the query, the optimizer uses CFB to improve its estimation and generates another (hopefully better) plan. This continues for the next executions until finally the plan stabilizes. Then we flush the shared pool (in order to wipe out all the relevant cursors for this query) and we disable the CFB feature and rerun the query. This time we see that the plan does not change, no matter how many times we execute the query. Lets see it in action:
This is our query:
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> get mysql2
1 SELECT /*+ gather_plan_statistics */ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Arbuckle 1999-02 241.2
Indirect Quartzhill 1999-01 987.3
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Cloverdale 1999-02 266.28
Indirect Pescadero 1999-02 298.44
Indirect Pescadero 1999-01 26.32
Indirect Legrand 1999-01 26.32
Indirect Arbuckle 1999-01 270.08
Indirect Montara 1999-01 289.07
Indirect Los Angeles 1999-02 2128.59
Indirect San Mateo 1999-01 8754.59
Indirect El Sobrante 1999-02 3744.03
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect San Francisco 1999-02 11257
Indirect Cloverdale 1999-01 52.64
Indirect Montara 1999-02 1618.01
Indirect San Mateo 1999-02 21399.42
Indirect San Francisco 1999-01 3058.27
Indirect Pala 1999-01 3263.93
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.27
nikos@NIKOSDB> @xplan
Enter value for sql_id:
Enter value for child_number:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 8rhhgss531vn9, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ ch.channel_class, c.cust_city,
t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales
s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND ch.channel_desc in
('Internet','Catalog') AND t.calendar_quarter_desc IN
('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 2264303152
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 22 |
| 2 | LOAD AS SELECT | | 1 | | 0 |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3060 | 3341 |
| 4 | HASH GROUP BY | | 1 | 1241 | 22 |
|* 5 | HASH JOIN | | 1 | 2009 | 964 |
|* 6 | TABLE ACCESS FULL | TIMES | 1 | 181 | 181 |
|* 7 | HASH JOIN | | 1 | 2009 | 964 |
|* 8 | TABLE ACCESS FULL | CHANNELS | 1 | 2 | 2 |
|* 9 | HASH JOIN | | 1 | 2009 | 964 |
| 10 | PARTITION RANGE SUBQUERY | | 1 | 2008 | 964 |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 2008 | 964 |
| 12 | BITMAP CONVERSION TO ROWIDS | | 2 | | 964 |
| 13 | BITMAP AND | | 2 | | 2 |
| 14 | BITMAP MERGE | | 2 | | 2 |
| 15 | BITMAP KEY ITERATION | | 2 | | 2 |
| 16 | BUFFER SORT | | 2 | | 4 |
|* 17 | TABLE ACCESS FULL | CHANNELS | 1 | 2 | 2 |
|* 18 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | 4 | | 2 |
| 19 | BITMAP MERGE | | 2 | | 2 |
| 20 | BITMAP KEY ITERATION | | 2 | | 181 |
| 21 | BUFFER SORT | | 2 | | 362 |
|* 22 | TABLE ACCESS FULL | TIMES | 1 | 181 | 181 |
|* 23 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | 362 | | 181 |
| 24 | BITMAP MERGE | | 2 | | 2 |
| 25 | BITMAP KEY ITERATION | | 2 | | 403 |
| 26 | BUFFER SORT | | 2 | | 6682 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66B5_C5E4FF | 1 | 3060 | 3341 |
|* 28 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | 6682 | | 403 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66B5_C5E4FF | 1 | 3060 | 3341 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("S"."TIME_ID"="T"."TIME_ID")
6 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
7 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
8 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
9 - access("S"."CUST_ID"="C0")
17 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
23 - access("S"."TIME_ID"="T"."TIME_ID")
28 - access("S"."CUST_ID"="C0")
Note
-----
- star transformation used for this statement
67 rows selected.
Elapsed: 00:00:00.32
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> get mysql2
1 SELECT /*+ gather_plan_statistics */ ch.channel_class, c.cust_city, t.calendar_quarter_de
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Arbuckle 1999-02 241.2
Indirect Quartzhill 1999-01 987.3
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Cloverdale 1999-02 266.28
Indirect Pescadero 1999-02 298.44
Indirect Pescadero 1999-01 26.32
Indirect Legrand 1999-01 26.32
Indirect Arbuckle 1999-01 270.08
Indirect Montara 1999-01 289.07
Indirect Los Angeles 1999-02 2128.59
Indirect San Mateo 1999-01 8754.59
Indirect El Sobrante 1999-02 3744.03
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-01 5392.34
Indirect San Francisco 1999-02 11257
Indirect Cloverdale 1999-01 52.64
Indirect Montara 1999-02 1618.01
Indirect San Mateo 1999-02 21399.42
Indirect San Francisco 1999-01 3058.27
Indirect Pala 1999-01 3263.93
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.12
nikos@NIKOSDB> @xplan
Enter value for sql_id:
Enter value for child_number:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 8rhhgss531vn9, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ ch.channel_class, c.cust_city,
t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales
s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND ch.channel_desc in
('Internet','Catalog') AND t.calendar_quarter_desc IN
('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
Plan hash value: 798384265
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 |
| 1 | HASH GROUP BY | | 1 | 38 | 22 |
|* 2 | HASH JOIN | | 1 | 38 | 964 |
|* 3 | HASH JOIN | | 1 | 38 | 14296 |
|* 4 | HASH JOIN | | 1 | 38 | 14296 |
|* 5 | TABLE ACCESS FULL | CHANNELS | 1 | 2 | 2 |
| 6 | PARTITION RANGE SUBQUERY | | 1 | 38 | 14296 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2 | 38 | 14296 |
| 8 | BITMAP CONVERSION TO ROWIDS | | 2 | | 14296 |
| 9 | BITMAP AND | | 2 | | 2 |
| 10 | BITMAP MERGE | | 2 | | 2 |
| 11 | BITMAP KEY ITERATION | | 2 | | 2 |
| 12 | BUFFER SORT | | 2 | | 4 |
|* 13 | TABLE ACCESS FULL | CHANNELS | 1 | 2 | 2 |
|* 14 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | 4 | | 2 |
| 15 | BITMAP MERGE | | 2 | | 2 |
| 16 | BITMAP KEY ITERATION | | 2 | | 181 |
| 17 | BUFFER SORT | | 2 | | 362 |
|* 18 | TABLE ACCESS FULL | TIMES | 1 | 181 | 181 |
|* 19 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | 362 | | 181 |
|* 20 | TABLE ACCESS FULL | TIMES | 1 | 181 | 181 |
|* 21 | TABLE ACCESS FULL | CUSTOMERS | 1 | 3060 | 3341 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
3 - access("S"."TIME_ID"="T"."TIME_ID")
4 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
5 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
13 - filter(("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet'))
14 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
18 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
19 - access("S"."TIME_ID"="T"."TIME_ID")
20 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
21 - filter("C"."CUST_STATE_PROVINCE"='CA')
Note
-----
- star transformation used for this statement
- cardinality feedback used for this statement
59 rows selected.
Elapsed: 00:00:00.22
We will continue to run the query several times. Each time we run the query and CFB kicks in, a new child cursor is created with a new plan. The old cursors are marked as "not shareable", which means that are garbage waiting to be aged out from the shared pool with the next garbage collection.
Lets see the corresponding cursors for the SQL_ID after we have run the query for 10 more times.
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> @fs
Enter value for sql_text:
Enter value for sql_id: 8rhhgss531vn9
PARSING_SCHEMA_NAME SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO IS_SHAREABLE
------------------------------ ------------- ------ ---------- ---------- ------------- ------------ ------------
SH 8rhhgss531vn9 0 2264303152 1 .21 20,068 N
SH 8rhhgss531vn9 1 798384265 1 .07 2,641 N
SH 8rhhgss531vn9 2 3691748271 1 .17 16,211 N
SH 8rhhgss531vn9 3 54399499 1 .08 16,211 N
SH 8rhhgss531vn9 4 4028491232 1 .07 16,211 N
SH 8rhhgss531vn9 5 1742562322 7 .07 16,116 Y
Each line corresponds to a different child cursor (the script uses V$SQL), which has a different execution plan denoted by the PLAN_HASH column. The EXEC column denotes how many time this cursor has been executed. The column IS_SHAREABLE denotes if the cursor is shared ('Y') or not, which means that it is not used any more. From the first 5 rows we infer that it took 5 executions for the optimizer to stabilize the plan. And indeed it is the last row (child number 5) which relates to the final plan. So you see, CFB does not run forever. At some point it stabilizes to a specific plan.
Now, if we disable the CFB feature then we can see that for the same query, the optimizer from the very first execution, sticks with a single execution plan. We can disable cardinality feedback by setting the hidden parameter _OPTIMIZER_USE_FEEDBACK. Lets see it in action:
First we flush the shared pool in order to wipe out all the available cursors.
------------------------------------------------------------------------------------------------------------
sys@NIKOSDB> alter system flush shared_pool;
System altered.
nikos@NIKOSDB> alter session set "_optimizer_use_feedback"=FALSE;
Session altered.
-- We execute the query several (12) times
...
nikos@NIKOSDB> @fs
Enter value for sql_text:
Enter value for sql_id: 8rhhgss531vn9
PARSING_SCHEMA_NAME SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO IS_SHAREABLE
------------------------------ ------------- ------ ---------- ---------- ------------- ------------ ------------
SH 8rhhgss531vn9 0 4091383804 12 .08 16,431 Y
Enjoy!
Oracle Learner
No comments:
Post a Comment