Tag Archives: 12c ReOptimization

Adaptive Query Optimization – Adaptive ReOptimization

Adaptive plans can choose between multiple operations during the execution of a sql to pottentially choose better access methods. But it cannot change the join orders. 11gR2 had cardinality feedback which allowed oracle to choose a better plan based on the information gathered at the end of the execution. In 12c Cardinality feedback has been renamed as Statistics Feedback.

Statistics Feedback

During the first execution of the statement, the optimizer generates an execution plan and decides if it should enable statistics feedback monitoring for the cursor. Statistics feedback is enabled in the following cases.

  • Table with no statistics
  • Multiple Conjunctive or Disjunctive predicates
  • Predicates containing complex operators for which optimizer cannot accurately compute cardinality estimates.

At the end of the excution if the actual cardinalities are different from the estimated cardinalities, the actual cardinalities are stored for subsequent use. It also creates sql plan directives, so other sql statements can benefit from this information. The statement is marked as ReOptimizable and will be Re-Parsed at the next execution and creates a new child cursor. The actual cardinalities are used during the Re-Parsing to determine a good execution plan.

Lets look at an example

SQL_ID  faca0yc2h1cdr, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'CA'  AND    country_id=52790                                                                                                                                                     
                                                                                                                                                                                                        
Plan hash value: 296924608                                                                                                                                                                              
                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1458 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1458 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   1115 | 17840 |   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1458 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
                                                                                                                                                                                                        
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
                                                                                                                                                                                                        
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
                                                                                                                                                                                                        
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
                                                                                                                                                                                                        
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)               

Let us check the details of the cursor

select plan_hash_value phv,child_number,parse_calls,executions,is_bind_sensitive ibs
      ,is_bind_aware iba,is_reoptimizable ropt,is_resolved_adaptive_plan aplan 
from v$sql where sql_id = 'faca0yc2h1cdr'

       PHV CHILD_NUMBER PARSE_CALLS EXECUTIONS I I R A
---------- ------------ ----------- ---------- - - - -
 296924608	      0 	  1	     1 N N Y

So we can see that the cursor has been flagged as ReOptimizable.

Let’s Execute the Same Sql again.

SQL_ID  faca0yc2h1cdr, child number 1                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'CA'  AND    country_id=52790                                                                                                                                                     
                                                                                                                                                                                                        
Plan hash value: 296924608                                                                                                                                                                              
                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------                                                                              
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
|   0 | SELECT STATEMENT   |           |      1 |        |       |   405 (100)|          |      1 |00:00:00.01 |    1457 |                                                                              
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    16 |            |          |      1 |00:00:00.01 |    1457 |                                                                              
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   3341 | 53456 |   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1457 |                                                                              
--------------------------------------------------------------------------------------------------------------------------                                                                              
                                                                                                                                                                                                        
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
                                                                                                                                                                                                        
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
                                                                                                                                                                                                        
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
                                                                                                                                                                                                        
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)                                                                                                                                                                                     
                                                                                                                                                                                                        
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - statistics feedback used for this statement                                                                                                                                                        
                                                     

Let’s check the details of the cursor

       PHV CHILD_NUMBER PARSE_CALLS EXECUTIONS I I R A
---------- ------------ ----------- ---------- - - - -
 296924608	      0 	  1	     1 N N Y
 296924608	      1 	  1	     1 N N N

We can see that a new child cursor has been created.
The notes secton indicates that Statistics feedback has been used for this statement.

Performance Feedback

When AutoDOP is enabled in Adaptive mode, during first execution of the statement, the optimizer determines whether the statement should execute in parallel, and what DOP should be used. After the first execution, the execution stats is used to compute a DOP. If the computed DOP is different from the original DOP the statement is marked for re-optimization and the execution statistics is stored. When the statement is executed again the executio statistics is used to compute a new DOP.