12c Database : Automatic Reoptimization

In continuation of previous post,

Further to adaptive plans, Optimizer uses automatic reoptimization using
1) Statistics feedback (Cardinality feedback)
2) Dynamic statistics (Dynamic sampling)

Let's take a closer look

Case 5: Adaptive reoptimization using Dynamic statistics

	1) Deleted the statistics for table Product information and ORDER_ITEMS
	2) Deleted few rows from the order_items
	
	so that optimizer mis estimates comparing to the object statistics.
	
	Upon first execution,
	
	SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADAPTIVE ALL ALLSTATS'));

	PLAN_TABLE_OUTPUT
	------------------------------------------------------------- 
	SQL_ID  8fuhzh9f2k54q, child number 0
	-------------------------------------
	SELECT /*+ gather_plan_statistics */ o.order_id, v.product_name FROM
	orders o,        ( SELECT order_id, product_name          FROM
	order_items o, product_information p          WHERE  p.product_id =
	o.product_id AND list_price < 50          AND    min_price < 100 ) v
	WHERE o.order_id = v.order_id
	
	Plan hash value: 1332280512
	
	-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	|   Id  | Operation                              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
	-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	|     0 | SELECT STATEMENT                       |                     |      1 |        |       |     8 (100)|          |    319 |00:00:00.01 |      70 |      |        |          |
	|     1 |  NESTED LOOPS                          |                     |      1 |    324 | 31104 |     8   (0)| 00:00:01 |    319 |00:00:00.01 |      70 |      |        |          |
	|  *  2 |   HASH JOIN                            |                     |      1 |    324 | 29808 |     8   (0)| 00:00:01 |    319 |00:00:00.01 |      45 |  1281K|  1281K|     1/0/0|
	|-    3 |    NESTED LOOPS                        |                     |      1 |    324 | 29808 |     8   (0)| 00:00:01 |     90 |00:00:00.01 |      16 |      |        |          |
	|-    4 |     STATISTICS COLLECTOR               |                     |      1 |        |       |            |          |     90 |00:00:00.01 |      16 |      |        |          |
	|  *  5 |      TABLE ACCESS FULL                 | PRODUCT_INFORMATION |      1 |     90 |  5940 |     5   (0)| 00:00:01 |     90 |00:00:00.01 |      16 |      |        |          |
	|-    6 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS         |      0 |      4 |   104 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      |        |          |
	|- *  7 |      INDEX RANGE SCAN                  | ITEM_PRODUCT_IX     |      0 |    300 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      |        |          |
	|     8 |    TABLE ACCESS FULL                   | ORDER_ITEMS         |      1 |    300 | 17290 |     3   (0)| 00:00:01 |    665 |00:00:00.01 |      29 |      |        |          |
	|  *  9 |   INDEX UNIQUE SCAN                    | ORDER_PK            |    319 |      1 |     4 |     0   (0)|          |    319 |00:00:00.01 |      25 |      |        |          |
	-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	Note
	-----
	   - this is an adaptive plan (rows marked '-' are inactive)

	SQL> select sql_id,child_number,plan_hash_value,is_resolved_adaptive_plan,
	is_reoptimizable from v$sql where sql_id='8fuhzh9f2k54q';
	
	SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I
	------------- ------------ --------------- - -
	8fuhzh9f2k54q            0      1332280512 Y Y
	
	
	Notes:-
	1) Order items estimated rows were 300 but actual returned was 665 and vice versa for product information
	2) Optimizer picks sub optimal plan and uses adaptive technique and resolves an adaptive plan
	3) Optimizer upon completion of execution it compares the estimated and actual rows that 
	returned and marked this statement can still optimized
	marked as is_reoptimizable as Y
	4) So, The next execution will kick off the statistics feedback feature.

Second execution of same statement

	
 	SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ADAPTIVE ALL ALLSTATS'));

	SQL_ID  8fuhzh9f2k54q, child number 1
	-------------------------------------
	SELECT /*+ gather_plan_statistics */ o.order_id, v.product_name FROM
	orders o,        ( SELECT order_id, product_name          FROM
	order_items o, product_information p          WHERE  p.product_id =
	o.product_id AND list_price < 50          AND    min_price < 100 ) v
	WHERE o.order_id = v.order_id
	
	Plan hash value: 1332280512
	
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	|   Id  | Operation                      | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	|     0 | SELECT STATEMENT               |                        |      2 |        |       |     8 (100)|          |     40 |00:00:00.01 |      60 |       |      |           |
	|     1 |  NESTED LOOPS                  |                        |      2 |     20 |  1920 |     8   (0)| 00:00:01 |     40 |00:00:00.01 |      60 |       |      |           |
	|  *  2 |   HASH JOIN                    |                        |      2 |     20 |  1840 |     8   (0)| 00:00:01 |     40 |00:00:00.01 |      50 |  1696K|  1696K|     2/0/0|
	|-    3 |    NESTED LOOPS                |                        |      2 |     20 |  1840 |     8   (0)| 00:00:01 |    134 |00:00:00.01 |      14 |       |      |           |
	|-    4 |     STATISTICS COLLECTOR       |                        |      2 |        |       |            |          |    134 |00:00:00.01 |      14 |       |      |           |
	|     5 |      TABLE ACCESS FULL         | ORDER_ITEMS            |      2 |     67 |  1742 |     3   (0)| 00:00:01 |    134 |00:00:00.01 |      14 |       |      |           |
	|- *  6 |     TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |    66 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |      |           |
	|- *  7 |      INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |        |       |            |          |      0 |00:00:00.01 |       0 |       |      |           |
	|  *  8 |    TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      2 |     90 |  5940 |     5   (0)| 00:00:01 |    180 |00:00:00.01 |      36 |       |      |           |
	|  *  9 |   INDEX UNIQUE SCAN            | ORDER_PK               |     40 |      1 |     4 |     0   (0)|          |     40 |00:00:00.01 |      10 |       |      |           |
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

	Note
	-----
	   - statistics feedback used for this statement
	   - this is an adaptive plan (rows marked '-' are inactive)

	SQL> select sql_id,child_number,plan_hash_value,is_resolved_adaptive_plan,
	is_reoptimizable from v$sql where sql_id='8fuhzh9f2k54q';
	
	SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I
	------------- ------------ --------------- - -
	8fuhzh9f2k54q            0      1332280512 Y Y
	8fuhzh9f2k54q            1      1332280512 Y N
	
	SQL> select sql_id,child_number,use_feedback_stats from 
	v$sql_shared_cursor where sql_id='8fuhzh9f2k54q';
	
	SQL_ID        CHILD_NUMBER U
	------------- ------------ -
	8fuhzh9f2k54q            0 N
	8fuhzh9f2k54q            1 Y


	Notes:- 
	1) First execution made optimizer to see actual difference in estimated rows and a
	   ctual rows, so it marked the statement as is_optimizable=Y
	2) Upon second execution of same statement the optimizer uses the previous 
	   execution statistics (statistics feedback in note section)
	   and marked the is_reoptimizable=N determining that no further improvements can be made.

Case 6:- Refinement to estimation by using Dynamic statistics.

	
	SQL> Alter system set optimizer_dynamic_sampling=11;
	system altered.
	
	SQL> Alter system flush shared_pool;
	
	Execution of first time
	
	SQL_ID  9pamqb0nqdrpq, child number 1
	-------------------------------------
	SELECT o.order_id, v.product_name FROM   orders o,        ( SELECT
	order_id, product_name          FROM   order_items o,
	product_information p          WHERE  p.product_id = o.product_id AND
	list_price < 50          AND    min_price < 100 ) v WHERE o.order_id =
	v.order_id
	
	Plan hash value: 1332280512
	
	-------------------------------------------------------------------------------------------
	| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
	-------------------------------------------------------------------------------------------
	|   0 | SELECT STATEMENT    |                     |       |       |     8 (100)|          |
	|   1 |  NESTED LOOPS       |                     |    20 |  1920 |     8   (0)| 00:00:01 |
	|*  2 |   HASH JOIN         |                     |    20 |  1840 |     8   (0)| 00:00:01 |
	|   3 |    TABLE ACCESS FULL| ORDER_ITEMS         |    67 |  1742 |     3   (0)| 00:00:01 |
	|*  4 |    TABLE ACCESS FULL| PRODUCT_INFORMATION |    90 |  5940 |     5   (0)| 00:00:01 |
	|*  5 |   INDEX UNIQUE SCAN | ORDER_PK            |     1 |     4 |     0   (0)|          |
	-------------------------------------------------------------------------------------------
	
	Predicate Information (identified by operation id):
	---------------------------------------------------
	
	   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
	   4 - filter(("LIST_PRICE"<50 AND "MIN_PRICE"<100))
	   5 - access("O"."ORDER_ID"="ORDER_ID")
	
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=AUTO)
	   - this is an adaptive plan


	SQL> select sql_id,child_number,plan_hash_value,is_resolved_adaptive_plan,
	is_reoptimizable from v$sql where sql_id='9pamqb0nqdrpq';
	
	SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE I I
	------------- ------------ --------------- - -
	9pamqb0nqdrpq            0      1332280512 Y Y
	9pamqb0nqdrpq            1      1332280512 Y N
	
	SQL>
	
	Notes:-
	1) As you see the setting dynamic sampling to level 11 makes optimizer 
	   to sample auto, you need not to worry about level now
	2) Upon first execution the optimizer marked its adaptive and 
	   further can be reoptimized
	3) Upon second execution the optimizer enhanced it by using dynamic 
	    statistics (dynamic sampling) and then provided the optimal plan
	4) Once optimized and executed optimizer see's there's no difference 
	   than actual to estimated marked the reoptimzed as NO.

-Thanks
Geek DBA