Category Archives: Performance

12c Adaptive Query Optimization – Getting Started

In Oracle Database 12c, adaptive query optimization is a set of capabilities that enables the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. Adaptive optimization is helpful when existing statistics are not sufficient to generate an optimal plan.

aqo

Below are a series of blog posts that I have written, that explains the Basics of how these features work.

Dynamic Statistics – Some Notes

Adaptive Plans – Adaptive Joins

Adaptive Plans – Adaptive Parallel Distribution Methods

AQO – Dynamic Statistics Examples

AQO – ReOptimization

AQP – SQL Plan Directives

 

Below are some oracle white papers and Mos notes that you could read, to familiarize yourselves with these new features.

Understanding Optimizer Statistics with Oracle 12c

Best practices for gathering statistics with Oracle 12c

What to expect with the optimizer, with Oracle 12c.

Adaptive Query Optimization – Mos Note : 2031605.1

Automatic Dynamic Statistics – Mos Note : 2002108.1

Adaptive Execution Plans – Mos Note : 1409636.1

How to Approach Issues Related to Adaptive Optimizations – Mos Note : 2068807.1

Optimizer Statistics – Master Note – Mos Note : 1369591.1

Resolving Issues of Library Cache Pin or Cursor Pin S Wait on X – Mos Note : 1476663.1

Troubleshooting Cursor Pin S Wait on X – Mos Note : 1349387.1

Cursor Pin S Wait on X, Finding Blocker – Mos Note : 786507.1

Parameter Recommendations for Oracle 12c – Part 1 (Upgrade PM Blog)

Parameter Recommendations for Oracle 12c – Part 2 (Upgrade PM Blog)

Things to consider before upgrading to 12.1.0.2 (Upgrade PM Blog)

Adaptive Query Optimization configuration : Parameters, Preferences and Fix controls (Christian Antognini Blog)

Adaptive Query Optimization – SQL Plan Directives

Sql plan directives are automatically created based on information learned via Automatic Reoptimization. The optimizer uses Sql Plan Directives to generate a more optimal execution plan. For eg: When joining two tables that have a data skew in their join columns, a Sql plan directive can direct the optimizer to use dynamic statistics to obtain a more accurate join cardinality estimate.

Key thing to note is that SPD’s are not created on Queries or Objects. They are created on query expressions. So the SPD’s generated by one sql statement can be used by multiple sql statements. It is possible for one Sql statement to use more than one SPD. SPD’s are persisted in the SYSAUX tablespace. Any SPD that is unused for 53 weeks will be automatically purged.

After a SQL directive is used the optimizer decides if the cardinality missestimate could be resolved with a column group. If so it will create the column group, the next time statistics are gathered on the appropriate table.

Let us take a look at an example.

We are using the same sql statement from the example in my post about Adaptive ReOptimization

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)

Next I will flush the plan directive

BEGIN
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
END;
/

Lets check the plan directives that have been created

DIR_ID                                   OWNER OBJECT_NAME               COL_NAME             OBJECT_TYPE               TYPE                    STATE      INST
---------------------------------------- ----- ------------------------- -------------------- ------------------------- ----------------------- ---------- -------------
REASON                               DC                          LU                         
------------------------------------ --------------------------- ---------------------------
1581183467014446692                      SH    CUSTOMERS                 COUNTRY_ID           COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22                                     
 
1581183467014446692                      SH    CUSTOMERS                 CUST_STATE_PROVINCE  COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22                                     
 
1581183467014446692                      SH    CUSTOMERS                                      TABLE                     DYNAMIC_SAMPLING        USABLE     NEW           
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22

Then let us a execute a new statement, by just changing one of the predicates.

SQL_ID  aj2w592n68s4s, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   customers  WHERE                                                                                                                                  
cust_state_province = 'FL'  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 |   2549 | 40784 |   405   (1)| 00:00:01 |   2438 |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"='FL' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
   2 - (rowset=906)                                                                                                                                                                                     
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=2)                                                                                                                                                
   - 1 Sql Plan Directive used for this statement

In the notes section we can see that the plan directive has been used by this new statement.

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.

12c Adaptive Query Optimization – Dynamic Statistics – Examples

Dynamic sampling is used to compensate for missing or insufficient statistics, that would otherwise lead to a bad plan. In earlier versions, dynamic sampling used to kick in for tables that had missing statistics or stale statistics. Dynamic sampling has been enhanced in 12c and is now called dynamic statistics (And most of the functionality, including the new level 11 has been back ported to 11.2.0.4). The optimizer bases its decision to use dynamic statistics, on the complexity of the predicates used, existing base statistics, total execution time expected,Statistics feedback, Sql plan directives etc. The optimizer automatically decides whether dynamic statistics are useful, and what level to use.

Automatic dynamic statistics are enabled when optimizer_dynamic_statistics=2 (Default) or 11.

I have outlined some nuances of Dynamic Statistics in this previous article.

In this article i will show you examples of Dynamic Statistics kicking in for the following situations.

Missing statistics
Stale statistics
Parallel query
Complex where clause
Group by clause

In this Blog post I am not addressing the scenario where dynamic statistics is used , when Sql plan directives are present. I will address that in a future post regarding sql plan directives.

Heads up, this will be a long blog post.

Missing Statistics

When a new table is created, and statistics was not gathered on the table, then oracle will perform dynamic sampling during the optimization of the query, so it can choose a good execution plan.

In the example below, I am creating a new table with data in it, Creating an index on it, and then deleting the statistics on the table.

create table t1
as
select
         *
from
         customers
;
 
create index t1_idx1 on t1 (cust_state_province)
;
 
exec dbms_stats.delete_table_stats(upper('SH'),upper('T1'));
commit;
 
select /*+ gather_plan_statistics */ count(*) from t1 where cust_state_province  = 'CA'

Then I execute a query against the table.

SQL_ID  3azum47vccux0, child number 0                                                                                                                                                                   
-------------------------------------   
select /*+ gather_plan_statistics */ count(*) from t1 where                                                                                                                                             
cust_state_province  = 'CA'                                                                                                                                                                             
 
Plan hash value: 3695297570                                                                                                                                                                             
 
--------------------------------------------------------------------------------------------------------------------------------                                                                        
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                        
|   0 | SELECT STATEMENT  |         |      1 |        |       |    12 (100)|          |      1 |00:00:00.01 |       9 |      2 |                                                                        
|   1 |  SORT AGGREGATE   |         |      1 |      1 |    22 |            |          |      1 |00:00:00.01 |       9 |      2 |                                                                        
|*  2 |   INDEX RANGE SCAN| T1_IDX1 |      1 |   4054 | 89188 |    12   (0)| 00:00:01 |   3341 |00:00:00.01 |       9 |      2 |                                                                        
--------------------------------------------------------------------------------------------------------------------------------                                                                        
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / T1@SEL$1                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - access("CUST_STATE_PROVINCE"='CA')                                                                                                                                                               
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=2)

In the Notes section of the execution plan, we can see that dynamic statistics was used with level 2.Since there was no statistics on the table or index, the optimizer performed dynamic sampling, to get estimates. When we review the 10046 trace file and its tkprof output we can see that there are 2 statements executed which has the hint /*+ OPT_DYN_SAMP */ that tells us that dynamic sampling has been performed.

Stale Statistics

In the example below, in order to simulate the behavior of stale statistics, I have done the following

  • Created a table, gathered stats
  • Run a query, and flushed the usage statistics.
  • Inserted more rows into the table.
  • Run a new query, on the table whose statistics is now stale.
  • In order to get the dynamic sampling to kick in, i had to set the optimizer_dynamic_sampling to 11. (Does not kick in when level is 2)

Here is the setup for the example.

create table t1
compress
as
select
         *
from
         customers
;
 
create index t1_idx1 on t1 (cust_state_province)
;
 
exec dbms_stats.gather_table_stats(upper('SH'),upper('T1'));
commit;
 
select /*+ gather_plan_statistics */ count(*) from t1 where cust_state_province  = 'CA'
;
 
connect sh/sh@10.1.1.202:1521/rkpdb1
 
insert into t1 select * from t1;
commit;
 
connect system/manager@10.1.1.202:1521/rkpdb1
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
connect sh/sh@10.1.1.202:1521/rkpdb1
alter session set optimizer_dynamic_sampling=11
;
@traceon
select /*+ gather_plan_statistics q2 */ count(*) from t1 where cust_state_province  = 'CA'
;

Here is the execution plan for the statement

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1gwst4v211h86, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select /*+ gather_plan_statistics q2 */ count(*) from t1 where                                                                                                                                          
cust_state_province  = 'CA'                                                                                                                                                                             
 
Plan hash value: 3695297570                                                                                                                                                                             
 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                                 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
|   0 | SELECT STATEMENT  |         |      1 |        |       |    21 (100)|          |      1 |00:00:00.01 |      20 |                                                                                 
|   1 |  SORT AGGREGATE   |         |      1 |      1 |    11 |            |          |      1 |00:00:00.01 |      20 |                                                                                 
|*  2 |   INDEX RANGE SCAN| T1_IDX1 |      1 |   6591 | 72501 |    21   (0)| 00:00:01 |   6682 |00:00:00.01 |      20 |                                                                                 
-----------------------------------------------------------------------------------------------------------------------                                                                                 
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / T1@SEL$1                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   2 - access("CUST_STATE_PROVINCE"='CA')                                                                                                                                                               
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                           
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Now you can see in the note section that dynamic sampling with level AUTO, has been utilized for this query. If you examine the 10046  trace file you will see that there are 2 queries that are executed with the DS_SVC hint in it.

Parallel Query

The following parallel query selects from the tables in the SSB  (Star Schema Benchmark) schema. The execution plan is rather large, so I am not including it below. I am only including the Notes section.

 

SQL_ID  6af5d5js0m4wg, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics parallel(4) */                                                                                                                                                        
d_month,s_region,c_region,sum(lo_quantity) FROM                                                                                                                                                         
ssb.lineorder,ssb.date_dim,ssb.customer,ssb.supplier,ssb.part  where                                                                                                                                    
lo_orderdate = d_datekey and    lo_custkey   = c_custkey and                                                                                                                                            
lo_suppkey   = s_suppkey and    lo_partkey   = p_partkey and    d_year                                                                                                                                  
     = 1997 and    p_mfgr       = 'MFGR#1' and    p_category   =                                                                                                                                        
'MFGR#11' and    p_brand1     = 'MFGR#1110' and    s_nation     =                                                                                                                                       
'UNITED STATES' and    c_nation     = 'CHINA' group by                                                                                                                                                  
d_month,s_region,c_region                                                                                                                                                                               
 
Plan hash value: 1014051437                                                                                                                                                                             
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---                                                                                                                                                                                                     
 
| Id  | Operation                          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M 
  |                                                                                                                                                                                                     
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---                                                                                                                                                                                                     
 
|   0 | SELECT STATEMENT                   |           |      1 |        |       | 14066 (100)|          |        |      |            |      3 |00:00:24.64 |      56 |      0 |       |       |        
  |                                                                                                                                                                                                     
 
|   1 |  PX COORDINATOR                    |           |      1 |        |       |            |          |        |      |            |      3 |00:00:24.64 |      56 |      0 | 73728 | 73728 |        
 
...
...
...
Note                                                                                                                                                                                                    
 
PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=5)                                                                                                                                                
   - Degree of Parallelism is 4 because of hint

As you can see in the Note section, the query has performed dynamic sampling. If you remove the parallel hint and run the query then dynamic sampling does not occur.
Here I did not have to modify the optimizer_dynamic_sampling from the default, dynamic statistics still kicks in because it is a parallel query.

Complex Where Clause

Below is a sql with conjunctive predicates in the where clause. This causes oracle to collect dynamic statistics for this query.

 

SELECT /*+ gather_plan_statistics */ *  FROM   customers  WHERE                                                                                                                                         
cust_state_province='CA'  AND    country_id=52790                                                                                                                                                       
 
Plan hash value: 2008213504                                                                                                                                                                             
 
-------------------------------------------------------------------------------------------------------------------------                                                                               
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                               
-------------------------------------------------------------------------------------------------------------------------                                                                               
|   0 | SELECT STATEMENT  |           |      1 |        |       |   405 (100)|          |   3341 |00:00:00.01 |    1677 |                                                                               
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |   3354 |   592K|   405   (1)| 00:00:01 |   3341 |00:00:00.01 |    1677 |                                                                               
-------------------------------------------------------------------------------------------------------------------------                                                                               
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
 
   1 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))                                                                                                                                      
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - "CUSTOMERS"."CUST_ID"[NUMBER,22], "CUSTOMERS"."CUST_FIRST_NAME"[VARCHAR2,20],                                                                                                                    
       "CUSTOMERS"."CUST_LAST_NAME"[VARCHAR2,40], "CUSTOMERS"."CUST_GENDER"[CHARACTER,1],                                                                                                               
       "CUSTOMERS"."CUST_YEAR_OF_BIRTH"[NUMBER,22], "CUSTOMERS"."CUST_MARITAL_STATUS"[VARCHAR2,20],                                                                                                     
       "CUSTOMERS"."CUST_STREET_ADDRESS"[VARCHAR2,40], "CUSTOMERS"."CUST_POSTAL_CODE"[VARCHAR2,10],                                                                                                     
       "CUSTOMERS"."CUST_CITY"[VARCHAR2,30], "CUSTOMERS"."CUST_CITY_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40],                                                                                 
       "CUSTOMERS"."CUST_STATE_PROVINCE_ID"[NUMBER,22], "COUNTRY_ID"[NUMBER,22],                                                                                                                        
       "CUSTOMERS"."CUST_MAIN_PHONE_NUMBER"[VARCHAR2,25], "CUSTOMERS"."CUST_INCOME_LEVEL"[VARCHAR2,30],                                                                                                 
       "CUSTOMERS"."CUST_CREDIT_LIMIT"[NUMBER,22], "CUSTOMERS"."CUST_EMAIL"[VARCHAR2,30],                                                                                                               
       "CUSTOMERS"."CUST_TOTAL"[VARCHAR2,14], "CUSTOMERS"."CUST_TOTAL_ID"[NUMBER,22],                                                                                                                   
       "CUSTOMERS"."CUST_SRC_ID"[NUMBER,22], "CUSTOMERS"."CUST_EFF_FROM"[DATE,7], "CUSTOMERS"."CUST_EFF_TO"[DATE,7],                                                                                    
       "CUSTOMERS"."CUST_VALID"[VARCHAR2,1]                                                                                                                                                             
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Here we can see that oracle performed dynamic sampling with level Auto.
I had to set optimizer_dynamic_sampling=11 for this query to perform dynamic sampling.

Group By Clause

The statement below performs dynamic sampling because the groupby clause causes a cardinality misestimate.

 

SQL_ID  8at4un9zmq38c, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ gather_plan_statistics */ count(*)  FROM   sh.customers                                                                                                                                      
group by COUNTRY_ID,CUST_STATE_PROVINCE                                                                                                                                                                 
 
Plan hash value: 1577413243                                                                                                                                                                             
 
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                   
| 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 |        |       |   407 (100)|          |    145 |00:00:00.01 |    1457 |       |       |          |                                                   
|   1 |  HASH GROUP BY     |           |      1 |    145 |  2320 |   407   (1)| 00:00:01 |    145 |00:00:00.01 |    1457 |  1016K|  1016K|     1/0/0|                                                   
|   2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |  55500 |   867K|   405   (1)| 00:00:01 |  55500 |00:00:00.01 |    1457 |       |       |          |                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                   
 
Query Block Name / Object Alias (identified by operation id):                                                                                                                                           
-------------------------------------------------------------                                                                                                                                           
 
   1 - SEL$1                                                                                                                                                                                            
   2 - SEL$1 / CUSTOMERS@SEL$1                                                                                                                                                                          
 
Column Projection Information (identified by operation id):                                                                                                                                             
-----------------------------------------------------------                                                                                                                                             
 
   1 - "COUNTRY_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40], COUNT(*)[22]                                                                                                                        
   2 - (rowset=256) "CUST_STATE_PROVINCE"[VARCHAR2,40], "COUNTRY_ID"[NUMBER,22]                                                                                                                         
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - dynamic statistics used: dynamic sampling (level=AUTO)

Here you can see that dynamic sampling was used with level Auto.
Once you execute this query, if you check the sql plan directives, you will see sql plan directives on this table with Reason of GROUP BY MISSESTIMATE.
I had to set optimizer_dynamic_sampling=11 to get dynamic statistics to kick in.

DIR_ID                                   OWNER OBJECT_NAME               COL_NAME             OBJECT_TYPE               TYPE                    STATE      INST          REASON
---------------------------------------- ----- ------------------------- -------------------- ------------------------- ----------------------- ---------- ------------- ------------------------------------
DC                          LU                         
--------------------------- ---------------------------
13371066972565290282                     SH    CUSTOMERS                 COUNTRY_ID           COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58                                     
 
13371066972565290282                     SH    CUSTOMERS                 CUST_STATE_PROVINCE  COLUMN                    DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58                                     
 
13371066972565290282                     SH    CUSTOMERS                                      TABLE                     DYNAMIC_SAMPLING        USABLE     NEW           GROUP BY CARDINALITY MISESTIMATE     
20-SEP-16 06:39:58

The examples above can give you some working knowledge of when Dynamic Statistics comes into play with Oracle 12c.

12c Adaptive Query Optimization – Adaptive Plans – Adaptive Parallel Distribution Methods

In oracle 11gR2, oracle uses one of broadcast, hash, round robin, range or key distribution methods, to redistribute data among  parallel query servers. If the cardinality estimation at a given step happened to be incorrect (eg: the estimate was about a 100 rows and in reality there was 100,000 rows), oracle could end up broadcasting a lot of rows to all the receiving  parallel query servers. Another issue frequently encountered was that there could be skew in the popular values, resulting in hash distribution sending most of the values to one or a small subset of the receiving pq servers (Causing them to do most of the work).

To address such situations in 12cR1, as part of the new Adaptive Query Optimization techniques, oracle introduced the ability to switch the distribution method from hash to broadcast, if the actual number of rows was less than the threshold .  In 12.1 the threshold was 2X degree of parallelism .  This new functionality is called Adaptive Parallel Distribution Methods.

If you check the execution plan you will see that a “Statistics Collector” has been inserted before the send operation , and the send operation is called “PX Send Hybrid Hash”.

Let us look at an example of a simple hybrid hash distribution.

SQL_ID  d1qmm37g288qz, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select /*+ full(emp) full(dept) parallel(4) */                                                                                                                                                          
dept.deptno,dept.dname,emp.empno,emp.ename from dept,emp where                                                                                                                                          
dept.deptno = emp.deptno                                                                                                                                                                                
 
Plan hash value: 717794318                                                                                                                                                                              
 
------------------------------------------------------------------------------------------------------------------------------------------------                                                        
| Id  | Operation                  | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |                                                        
------------------------------------------------------------------------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT           |          |        |       |     4 (100)|          |        |      |            |       |       |          |                                                        
|   1 |  PX COORDINATOR            |          |        |       |            |          |        |      |            | 73728 | 73728 |          |                                                        
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 |     14 |   770 |     4   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |       |       |          |                                                        
|*  3 |    HASH JOIN BUFFERED      |          |     14 |   770 |     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |  3105K|  2823K|     4/0/0|                                                        
|   4 |     PX RECEIVE             |          |      4 |    88 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |                                                        
|   5 |      PX SEND HYBRID HASH   | :TQ10000 |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|       |       |          |                                                        
|   6 |       STATISTICS COLLECTOR |          |        |       |            |          |  Q1,00 | PCWC |            |       |       |          |                                                        
|   7 |        PX BLOCK ITERATOR   |          |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |       |       |          |                                                        
|*  8 |         TABLE ACCESS FULL  | DEPT     |      4 |    88 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |       |       |          |                                                        
|   9 |     PX RECEIVE             |          |     14 |   462 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |                                                        
|  10 |      PX SEND HYBRID HASH   | :TQ10001 |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|       |       |          |                                                        
|  11 |       PX BLOCK ITERATOR    |          |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |       |       |          |                                                        
|* 12 |        TABLE ACCESS FULL   | EMP      |     14 |   462 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |       |       |          |                                                        
------------------------------------------------------------------------------------------------------------------------------------------------

In the execution plan you can see that in steps 5 and 10 the operation is “PX SEND HYBRID HASH”. During execution, the statistics collector  in step 6 buffers the rows being retrieved from DEPT. If the number of rows are below a threshold, then all rows from DEPT are broadcast to each of the PQ servers that perform the join. If the number of rows exceed a threshold then the rows from DEPT are send using a HASH distribution to the PQ servers that perform the join.

Immediately after the query above is executed, from the same session, you can execute the script pqstat to see how the rows were distributed among the producers and consumers.

SQL> @pqstat
 
DFO_NUMBER      TQ_ID SERVER_TYPE                              PROCESS                    NUM_ROWS      BYTES      WAITS   TIMEOUTS AVG_LATENCY
---------- ---------- ---------------------------------------- ------------------------ ---------- ---------- ---------- ---------- -----------
         1          0 Producer                                 P004                              0         96          0          0           0
                                                               P005                             16        324          0          0           0
                                                               P006                              0         96          0          0           0
                                                               P007                              0         96          0          0           0
                      Consumer                                 P000                              4        153        125        119           0
                                                               P001                              4        153        127        119           0
                                                               P002                              4        153        127        119           0
                                                               P003                              4        153        125        119           0
                    2 Producer                                 P000                              0         24          0          0           0
                                                               P001                             14        375          1          0           0
                                                               P002                              0         24          0          0           0
                                                               P003                              0         24          0          0           0
                      Consumer                                 QC                               14        447          2          0           0

 

Now let us take a look at a variation of the hybrid hash distribution operation , which is denoted as “PX SEND HYBRID HASH (SKEW)”

SQL_ID  20fky17tasvn9, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT /*+ parallel(4) full(e) full(d) gather_plan_statistics */                                                                                                                                        
department_name, sum(salary)   FROM   employees e, departments d                                                                                                                                        
WHERE  d.department_id=e.department_id   GROUP BY department_name                                                                                                                                       
 
Plan hash value: 2940813933                                                                                                                                                                             
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                          | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                   |             |      1 |        |       |     5 (100)|          |        |      |            |     11 |00:00:00.04 |      24 |       |       |          |    
|   1 |  PX COORDINATOR                    |             |      1 |        |       |            |          |        |      |            |     11 |00:00:00.04 |      24 | 73728 | 73728 |          |    
|   2 |   PX SEND QC (RANDOM)              | :TQ10003    |      0 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |          |    
|   3 |    HASH GROUP BY                   |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | PCWP |            |     11 |00:00:00.01 |       0 |  1071K|  1071K|     4/0/0|    
|   4 |     PX RECEIVE                     |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,03 | PCWP |            |     12 |00:00:00.01 |       0 |       |       |          |    
|   5 |      PX SEND HASH                  | :TQ10002    |      0 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,02 | P->P | HASH       |      0 |00:00:00.01 |       0 |       |       |          |    
|   6 |       HASH GROUP BY                |             |      4 |     27 |   621 |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |     12 |00:00:00.03 |      18 |  1063K|  1063K|     4/0/0|    
|*  7 |        HASH JOIN                   |             |      4 |    106 |  2438 |     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |    106 |00:00:00.03 |      18 |  1572K|  1572K|     4/0/0|    
|   8 |         PX RECEIVE                 |             |      4 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |    108 |00:00:00.02 |       0 |       |       |          |    
|   9 |          PX SEND HYBRID HASH       | :TQ10000    |      0 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|      0 |00:00:00.01 |       0 |       |       |          |    
|  10 |           STATISTICS COLLECTOR     |             |      4 |        |       |            |          |  Q1,00 | PCWC |            |     27 |00:00:00.01 |       9 |       |       |          |    
|  11 |            PX BLOCK ITERATOR       |             |      4 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |     27 |00:00:00.01 |       9 |       |       |          |    
|* 12 |             TABLE ACCESS FULL      | DEPARTMENTS |      1 |     27 |   432 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |     27 |00:00:00.01 |       9 |       |       |          |    
|  13 |         PX RECEIVE                 |             |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |    107 |00:00:00.01 |      18 |       |       |          |    
|  14 |          PX SEND HYBRID HASH (SKEW)| :TQ10001    |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|    107 |00:00:00.01 |      18 |       |       |          |    
|  15 |           PX BLOCK ITERATOR        |             |      4 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |    107 |00:00:00.01 |      18 |       |       |          |    
|* 16 |            TABLE ACCESS FULL       | EMPLOYEES   |      2 |    107 |   749 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |    107 |00:00:00.01 |      18 |       |       |          |    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In the execution plans you can see the steps “PX SEND HYBRID HASH” (Step 9) and “PX SEND HYBRID HASH (SKEW)” (Step 14).

“PX SEND HYBRID HASH (SKEW)” is a technique used by 12c parallel query to avoid hash distribution of skewed popular key values. During optimization of the query, if histograms are present on the join expression, oracle finds and keeps the popular values in the cursor. At execution time , when the build table is being read, all values with matches in the popular values, are BROADCAST to all receiving pq serves  and non matching values are distributed by HASH. When the probe table is being read all values with matches in the popular values are send ROUND ROBIN/RANDOM to the receiving pq serves and the non matching values are distributed by HASH.

Please read Randolf Geist’s excellent Articles Linked below for indepth info on this topic.

Parallel Execution Skew – 12c Hybrid Hash Distribution with Skew Detection

12c Hybrid Hash Distribution with Skew Detection / Handling.

Oracle Exadata Statistics in AWR report – Part 2 (Outliers)

This blog post is a continuation of the previous blog post titled , Oracle Exadata Statistics in AWR report – Part 1 (Basics). In this post we continue on to describe the performance details displayed in the section “Exadata Outlier Summary”.

Outlier Summary Cell Level

awex3-1

This section displays cells that have performance outliers. The Awr Views DBA_HIST_CELL_DISK_SUMMARY, and DBA_HIST_CELL_GLOBAL_SUMMARY contains samples for each cell, disk and flash card.
The individual sample values, the number of samples, the average, the square of the value are all stored. Using this data the mean and the standard deviation are calculated and the range is defined as the average + or – standard deviation. Cells that have values that are above the mean + standard deviation are displayed.

This section will help us identify cells that have performance metrics, which are outside of the standard operating norms of that cell.

Outlier Summary – Disk Level

awex3-2

This section displays Disks that have performance outliers. The Awr Views DBA_HIST_CELL_DISK_SUMMARY contains this info.The individual sample values, the number of samples, the average, the square of the value are all stored. Using this data the mean and the standard deviation are calculated and the range is defined as the average + or – standard deviation. Disks that have values that are above the mean + standard deviation are displayed.

This section will help us identify Disks (Flash or Hard disk) that have performance metrics, which are outside of the standard operating norms of that Disk.

Exadata OS IO Statistics – Outlier Cells

awex4-1

This section displays cells that have IO statistics that are outliers. Per Cells averages, Per Disk Mean, Standard Deviation, Range’s of the IOPS and IO MBPS information is displayed. Averages exceeding the maximum stated capacity of the disk or cell are shown in Dark red.

This section helps identify whether there are cells or disks that exceed their stated capacities.

Exadata OS IO Statistics – Outlier Disks

awex4-2

This section displays disks (Flash and Hard disk) that have IO statistics that are outliers. Per Disk Mean, Standard Deviation, Range’s of the IOPS, IO MBPS and Disk utilization percentage information is displayed. Averages exceeding the Normal Ranges are shown in Dark red.

This section helps identify whether there are disks that are outside of the standard operating norms of that disk.

Exadata OS IO Latency – Outlier Cells

awex4-3
This section displays cells (Flash and Hard disk) that have IO latencies that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Average Serice times and Average Wait Times are displayed.
If there are cells Averages that exceed the Normal Range, they are displayed as outliers.

This section helps us identify whether there are cells that have I/O latencies that are outside of the standard operating norms for cells in this system.

Exadata OS IO Latency – Outlier Disks

awex5-1
This section displays disks (Flash and Hard disk) that have IO latencies that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Average Serice times and Average Wait Times are displayed.
If there are disks whose Averages that exceed the Normal Range of the cells, they are displayed as outliers.

This section helps us identify whether there are disks that have I/O latencies that are outside of the standard operating norms for disks in this system.
Exadata OS CPU Statistics – Outlier Cells

awex5-2
This section displays cells that have Cpu utilization that are outliers.
Aggregated Across all cells, the Mean, Standard Deviation, Range’s of Cpu utilization is displayed.
If there are cells whose Average Cpu utilization that exceed the Normal Cpu utilization Range of the cells, they are displayed as outliers.

 

Oracle Exadata Statistics in AWR report – Part 1 (Basic Info)

Starting with Exadata storage server 12.1.2.1.0 , used in combination with Oracle Database release 12.1.0.2, there are new sections which have been added to the Oracle AWR (Automatic Worload repository) report, that displays statistics at the Exadata storage level.

This is a really valuable enhancement, which helps with drilling down from database level statistics to cell level statistics, to identify and analyze the workload profile.
You can click on the URL’s in the section “Exadata Configuration and Statistics” to access this part of the report.

There are a few AWR history tables that store this information.

DBA_HIST_CELL_CONFIG
DBA_HIST_CELL_CONFIG_DETAIL
DBA_HIST_CELL_DB
DBA_HIST_CELL_DISKTYPE
DBA_HIST_CELL_DISK_NAME
DBA_HIST_CELL_DISK_SUMMARY
DBA_HIST_CELL_GLOBAL
DBA_HIST_CELL_GLOBAL_SUMMARY
DBA_HIST_CELL_IOREASON
DBA_HIST_CELL_IOREASON_NAME
DBA_HIST_CELL_METRIC_DESC
DBA_HIST_CELL_NAME
DBA_HIST_CELL_OPEN_ALERTS

The description of these views can be found in the Exadata Storage Server Users Guide.

The section starts off by showing the cell configuration information. Then it displays the Kernel  and the Cell Image version’s.

awex1-1

This information comes from the awr view DBA_HIST_CELL_DISK_SUMMARY.

The next section titled “Exadata Storage Information” storage information shows the number of disks and flash cards in each cell and the entire rack.

awex1-2
The first row of the output shows the amount of flash cache in each cell, The size of the smart flash log, Number of hard disks in a cell, Number of flash cards in each cell, and the number of Grid Disks in each cell.
The second row shows the above columns aggregated for all cells in the rack.

The next section titled “Exadata Griddisks” shows the grid disk names, Number of Grid disks in each cell, the Grid Disk size and The type of Drive

awex1-3
The next section titled “Exadata Cell Disks” shows the Disk type, Size of the cell disk, Number of disks .

awex2-1
The next section “ASM disksgroups” shows the diskgroups used by this database.

awex2-2
It shows the diskgroup name,Total size of the diskgroup,Used space, Number of disks in the diskgroup and the redundancy type.

This is followed by a section “Exadata Server Health Report”, which has 3 sub sections Exadata Alerts Summary,Exadata Alerts Detail,Exadata Non-Online Disks which displays information regarding alerts on the cells and any offline disks.
The remaining sections of Exadata performance statistics in the AWR report, display a great deal of Exadata cell performance numbers.

Before we venture much into those sections, it is important to understand some cell level concepts and how they are captured in Awr.

At the cell level if you list the following attributes (On a x5-2 cell with HD drives)

list cell attributes maxpdiops,maxpdmbps,maxfdiops,maxfdmbps you get the following values

167 111 8929 343

These values are collected and stored in the confval column in DBA_HIST_CELL_CONFIG_DETAIL in an XML format.

These base values are used to calculate the maximum capacities of the cells and disks in the sections that follow.

 

Adaptive Query Optimization – Adaptive Plans – Adaptive Joins

Based on the available statistics on the objects, the Oracle optimizer could incorrectly estimate the cardinality of certain row sources, resulting in a sub-optimal plan. Adaptive plans are a technique used by the Oracle 12c optimizer to adapt a plan, based on information learned during execution, to pick a better plan.

There are two types of optimizations oracle 12c can do under adaptive plans. One is a technique called “Adaptive Joins” and the second is called “Adaptive Parallel Distribution Methods”. This article deals with “Adaptive Joins”

For eg: If the optimizer estimated the where clause item_id = 20, is going to generate 10 rows and in reality if there were 100,000 rows in the table with item_id = 20, the optimizer might have chosen a nested loops join to this table. With adaptive plans, the optimizer gets the opportunity to switch this nested loops to a hash join, during the execution of this statement.

When the sql statement is parsed, the optimizer creates what is called a “default plan”. If there are some incorrect cardinality estimates performed by the optimizer, then it is probable that it has picked an execution plan with wrong join methods. An adaptive plan, contains multiple pre-determined “Sub plan’s”. A subplan is a portion of the plan that the optimizer can switch to as an alternative at runtime. The optimizer cannot change the whole execution plan at Execution time, it can adapt portions of it.

There are multiple “Statistics Collector’s”, inserted as rowsource’s, at key points in the execution plan.

During execution the statistics collector buffers some rows received by the subplan. Based on the information observed by the collector, the optimizer chooses a specific subplan.

Based on the observations of the statistics collectors a “Final plan” is then chosen by the optimizer and executed. After the first execution, any subsequent execution of this statement, uses this “final plan”. ie it does not incur the overhead of the statistics collection at execution time.

Let us take a look at the following sql statement and its execution plan.

SELECT product_name
FROM order_items o, product_information p
WHERE o.unit_price = 15
AND quantity > 1
AND p.product_id = o.product_id
/

This is a sql where the oracle 12c optimizer, decides to use Adaptive optimization.
If we use dbms_xplan.display_cursor to show the execution plan, it shows the following. This is the “final plan”

SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
o.product_id                                                                                                                                                               
 
Plan hash value: 1553478007                                                                                                                                                
 
------------------------------------------------------------------------------------------                                                                                 
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                 
------------------------------------------------------------------------------------------                                                                                 
|   0 | SELECT STATEMENT   |                     |       |       |     7 (100)|          |                                                                                 
|*  1 |  HASH JOIN         |                     |     4 |   128 |     7   (0)| 00:00:01 |                                                                                 
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     4 |    48 |     3   (0)| 00:00:01 |                                                                                 
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     1   (0)| 00:00:01 |                                                                                 
------------------------------------------------------------------------------------------                                                                                 
 
Outline Data                                                                                                                                                               
-------------                                                                                                                                                              
 
  /*+                                                                                                                                                                      
      BEGIN_OUTLINE_DATA                                                                                                                                                   
      FULL(@"SEL$1" "P"@"SEL$1")                                                                                                                                           
      USE_HASH(@"SEL$1" "P"@"SEL$1")                                                                                                                                       
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                          
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')                                                                                                                                
      DB_VERSION('12.1.0.2')                                                                                                                                               
      ALL_ROWS                                                                                                                                                             
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                               
      FULL(@"SEL$1" "O"@"SEL$1")                                                                                                                                           
      LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")                                                                                                                            
      END_OUTLINE_DATA                                                                                                                                                     
  */                                                                                                                                                                       
 
Predicate Information (identified by operation id):                                                                                                                        
---------------------------------------------------                                                                                                                        
 
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
 
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - this is an adaptive plan

Since the Notes section says that this is an adaptive plan, we can use dbms_xplan to show the “full plan”, which includes the “default plan” and the “sub plan’s”
We have to use the ADAPTIVE keyword in dbms_xplan to display the ‘full plan’.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('971cdqusn06z9',0,'ALLSTATS LAST +ADAPTIVE'))
 
SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
-------------------------------------                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
o.product_id                                                                                                                                                               
 
Plan hash value: 1553478007                                                                                                                                                
 
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
|   Id  | Operation                     | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |                   
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
|     0 | SELECT STATEMENT              |                        |      1 |        |     13 |00:00:00.01 |      24 |     20 |       |       |          |                   
|  *  1 |  HASH JOIN                    |                        |      1 |      4 |     13 |00:00:00.01 |      24 |     20 |  2061K|  2061K|  445K (0)|                   
|-    2 |   NESTED LOOPS                |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    3 |    NESTED LOOPS               |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    4 |     STATISTICS COLLECTOR      |                        |      1 |        |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      1 |      1 |    288 |00:00:00.01 |      17 |     14 |       |       |          |                   
--------------------------------------------------------------------------------------------------------------------------------------------------------                   
 
Predicate Information (identified by operation id):                                                                                                                        
---------------------------------------------------                                                                                                                        
 
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
   6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
 
Note                                                                                                                                                                       
-----                                                                                                                                                                      
   - this is an adaptive plan (rows marked '-' are inactive)

In the plan we can see the statistics collector row source. We can see that the Index access “Access Method” was evaluated and discarded by the optimizer. You can see that the optimizer estimated 1 row from product_information and actually it returned 288 rows. The optimizer calcluates an inflection point after which the nested loops operation becomes less efficient and chooses the full scans followed by the hash join.

As indicated in the Note section, the lines that have a – at the beginning of the line, are inactive in the “Final Plan”.

Let us check the flags from v$sql

select is_reoptimizable,is_resolved_adaptive_plan
from v$sql where
sql_id = '971cdqusn06z9'
 
I I
- -
Y Y

The output indicates that the statement is re-optimizable and that it was resolved by using an adaptive plan.

Every step in the ‘full plan’ is stored in v$sql_plan, the information regarding which steps are ‘on’ or ‘off’ in the ‘final plan’ is stored in the column other_xml, under the xml element, display_map.

This is how it looks for sqlid 971cdqusn06z9

xml2

The op= property in the xml, maps to the id column in v$sql_plan (ie the step number). The skp= property indicates whether the step is active in the final plan or not. A value of 1 indicates that, that row is skipped in the final plan. You can display it in a row format with the following sql

select * from
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',    
      dis NUMBER PATH '@dis',   
      par NUMBER PATH '@par',   
      prt NUMBER PATH '@prt',   
      dep NUMBER PATH '@dep',   
      skp NUMBER PATH '@skp' )  
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
/
 
      OP        DIS        PAR        PRT        DEP        SKP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          1          0
         2          1          1          0          1          1
         3          1          1          0          1          1
         4          1          1          0          1          1
         5          2          1          0          2          0
         6          2          1          0          1          1
         7          2          1          0          1          1
         8          3          1          0          2          0

This output can be joined with v$sql_plan to produce an output of the “final plan” .

SELECT
  NVL(map.dis,0) id,
  map.par         parent,
  map.dep         depth,
  lpad(' ',map.dep*1,' ')
  || sp.operation AS operation,
  sp.OPTIONS,
  sp.object#,
  sp.object_name
FROM v$sql_plan sp,
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',
      dis NUMBER PATH '@dis',
      par NUMBER PATH '@par',
      prt NUMBER PATH '@prt',
      dep NUMBER PATH '@dep',
      skp NUMBER PATH '@skp' )
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   is not null
  ) map
WHERE sp.id             = map.op(+)
AND sp.sql_Id           = '&sql_id'
AND sp.child_number     = &sql_child
AND nvl(map.skp,0)     != 1
ORDER BY nvl(map.dis,0)

If you look at the 10053 trace for the sql, you can see how the optimizer calculates an inflection point.

References

What’s new in 12c

https://martincarstenbach.wordpress.com/2015/01/13/adaptive-plans-and-vsql_plan-and-related-views/

Adaptive Plans Inflection points

Dynamic Statistics – Oracle 12c – Some notes

Adaptive Query optimization, was a set of new capabilities, introduced in oracle 12c, to allow the optimizer to discover additional information regarding statistics and make run-time adjustments to execution plans to make them better. This is a major change in the optimizer behaviour from 11g.

I would recommend anyone who is planning an upgrade to 12c, that they make themselves familiar with the following white papers from oracle.

Understanding Optimizer Statistics with Oracle 12c

Best practices for gathering statistics with Oracle 12c

What to expect with the optimizer, with Oracle 12c.

In this article, i want to talk about some of the important concepts behind Dynamic Statistics, which is one of the components of Adaptive query optimizations.

In the section’s that are following, i show some commands to turn some of these features off. I want to be clear that I am not recommending that you turn anything off. I would prefer that customer’s adopt these new features,that are designed to improve the execution plans. Also keep in mind that the following are accurate (Afaik) on 12.1.0.2 as off the time of writing of this article, and are subject to change.

Dynamic statistics has two interesting effects, that DBA’s tend to notice initially.
– Ever so slightly, longer parse times for queries.
– Execution plan changes (Compared to what they had before upgrading) for the same query. (Sometimes unwelcome changes, especially for deployments that value stability more than performance gains).

Dynamic sampling was introduced by oracle in 9i Release 2 to improve the optimizer’s functioning. The amount of dynamic sampling done , and when it kicks in, is controlled by the parameter optimizer_dynamic_sampling. With 12c there is a new concept of Dynamic Statistics. Dynamic Statistics is different from the pre-12c traditional dynamic sampling, in the following aspects.

– Dynamic Statistics could kick in even when optimizer_dynamic_sampling is set to 2.

– Especially for parallel queries on large tables.

– Dynamic Statistics  kicks in when optimizer_dynamic_sampling is set to 11.
– Dynamic Statistics  issues more queries than dynamic sampling used to do.

– The traditional dynamic sampling, used to issue, atmost, 1 query per table SQL.
– It is not uncommon to see 10’s of Dynamic Statistics  queries being issued for a     single SQL. Multiple dynamic sampling queries for the same table.(It dpends on the volume of data, number of indexed columns, complexity of     the predicates etc).
– If you run a 10046 trace on the query, you will see a lot of additional queries in there that     have the DS_SVC hint in them, which are the queries issued by Dynamic Statistics.

Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Dynamic Statistics.

You can set Optimizer_Dynamic_Sampling = 0 to turn Dynamic Statistics  off. However this would be like throwing the baby out with the bath water. Setting Optimizer_Dynamic_Sampling=0 completely sets dynamic sampling off (Including the old style pre-12c dynamic sampling).

You can do an ALTER SESSION SET “_fix_control”=’7452863:0′; to turn just Dynamic Statistics off, if you so desire.

Dynamic Statistics  also uses oracle Results Cache. If results cache is enabled in the database (usually by setting result_cache_max_size to a value > 0), then Dynamic Statistics  uses the results cache to store the results that it queries up. This is done so that, if there are multiple query parses that have to do dynamic sampling, and they are looking at the same data, the optimizer can just look that value up from the results cache (As opposed to having to query the tables again and again).

If you have a system, that has a lot of hard parses (Due to not using bind variables), you could pottentialy see latch free waits on “Results Cache: rc latch”. Please refer to Mos note 2002089.1, that suggest’s setting “_optimizer_ads_use_result_cache” = FALSE; to work around this. Keep in mind that setting this parameter does not prevent the optimizer from using Dynamic Statistics . All it does it prevent the Dynamic Statistics  from using the results cache.

Examples of Dynamic Statistics usage.

The following Mos note’s and a presentation from Trivadis, have a lot of great information in this regard.

Automatic Dynamic Statistics (Doc ID 2002108.1)

Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level Specified (Doc ID 1102413.1)

High Waits for ‘cursor: pin S wait on X’ due to Dynamic Sampling Against Parallel Queries (Doc ID 2006145.1)

Adaptive Dynamic Sampling – Trivadis

AWR – Profiling Database I/O

Oracle Awr (Automatic Workload Repository) statistics, captures and stores fine grained information about file reads and writes (aka i/o), that the database performed, during the course of execution of, application generated database workloads. When analyzing the read and write patterns of the database, it helps a lot to understand what type of activity is generating the reads and writes. With this stored information we can get an indepth understanding of the distribution of random and sequential reads and writes.

I use this information for getting a better understanding of the I/O profile, for my Exadata sizing exercises.

This information can be used to understand clearly how much of the i/o is from Temp activity, Datafile reads and writes, Archivelog writes, log writes, and whether these are small or large reads and writes.

To the best of my understanding the small reads and writes are those < 128k and the large reads and writes are those > 128k.

This information is contained mainly in two awr Views.

Dba_Hist_Iostat_FileType
Dba_Hist_Iostat_Function

Dba_Hist_Iostat_FileType

This view displays the historical i/o statistics by file type. The main filetypes are the following

Archive Log
Archive Log Backup
Control File
Data File
Data File Backup
Data File Copy
Data File Incremental Backup
Data Pump Dump File
Flashback Log
Log File
Other
Temp File

Dba_Hist_Iostat_Function

This view displays the historical i/o statistics by i/o function. The main i/o functions are the following

Recovery
Buffer Cache Reads
Others
RMAN
Streams AQ
Smart Scan
Data Pump
XDB
Direct Writes
DBWR
LGWR
Direct Reads
Archive Manager
ARCH

From everything i have seen sofar, these reads and writes can be directly co-related to the “Physical read total IO requests” and “Physical write total IO requests” system level statistics.

I have written a script that displays information from the above mentioned views and gives a detailed breakdown of i/o gendrated from different aspects of the database activities.
In order to fit in the computer screen real estate, i have actually limited the columns the script displays (So it displays only the file types i am frequently interested in). Please feel free to take the script and modify it to add columns that you want to display.

The full version of the script  awrioftallpct-pub.sql can be found here.

The script accepts the following inputs
– A begin snap id for a snapid range you want to report for
– A End snap id for a snapid range you want to report for
– A Dbid for the database
– The snap interval in seconds (If you have a 30 minute interval input 1800 seconds)

A description of all the column names in the output, broken down by section, is provided in the header section of the script.

There are 6 sections to this script

1) Total Reads + Writes
2) Total Reads
3) Total Writes
4) Read write breakdown for datafiles
5) Data File – Direct Path v/s Buffered Read Write breakdown
6) Read write breakdown for tempfiles

1) Total Reads + Writes

This section displays the number of reads+writes by filetype, and a percentage of reads+writes for each file type, as a percentage of total reads+writes. The last column displays the total reads+writes for all file types. The column DTDP shows the i/o that bypasses flash cache by default and goes directly to spinning disk on Exadata (Temp+Archivelogs+Flashback Logs).

io1-rw

 

Click on the image to see a larger version

2) Total Reads

This section displays the number of reads by filetype, and a percentage of reads for each file type, as a percentage of total reads. The last column displays the total reads for all file types.

io1-r

Click on the image to see a larger version

3) Total Writes

This section displays the number of writes by filetype, and a percentage of writes for each file type, as a percentage of total writes. The last column displays the total writes for all file types.

io1-w

Click on the image to see a larger version

4) Read write breakdown for datafiles

This section displays the I/O information only pertaining to datafile i/o. It displays the small and large reads and writes and a percentage they constitute of the total reads+writes to datafiles, and a percentage they constitute of the total reads or writes to datafiles. It also displays the total small and large reads and writes and a percentage they constitute of the total reads+writes to datafiles.

io1-dfrw

Click on the image to see a larger version

5) Data File – Direct Path v/s Buffered Read Write breakdown

This section provides a breakdown of I/O by function (As opposed to i/o by filetype in the previous sections). The output shows columns that display the direct path small and large reads and writes, buffered small reads and writes, smart scan small and large reads and other small and large reads and writes.

io3-bf

Click on the image to see a larger version

6) Read write breakdown for tempfiles

This section displays the I/O information only pertaining to tempfile i/o. It displays the small and large reads and writes and a percentage they constitute of the total reads+writes to tempfiles, and a percentage they constitute of the total reads or writes to tempfiles. It also displays the total small and large reads and writes and a percentage they constitute of the total reads+writes to tempfiles.

io3-tf

Click on the image to see a larger version

The full version of the script  awrioftallpct-pub.sql can be found here.