By Wissem El Khlifi, Oracle ACE
Introduction
In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a sql plan directive, which is additional information and instructions that the optimizer can use to generate a better execution plan. SQL plan directive may instruct the optimizer to record a column groups and expressions statistics (record extended statistics).
For example, a sql plan directive might instruct the optimizer to:
- Collect missing statistics
- Create column group statistics
- Perform dynamic statistics (Dynamic Sampling).
During sql compilation or execution, the database analyzes the query that is missing statistics or that miss-estimated for cardinality to create a sql plan directive.
When the optimizer generates an execution plan, the directives give the optimizer additional information about objects that are referenced in the plan. SQL plan directives are not tight to a specific statement or specific sql id. Directives can be used and shared between multiple statements:
- The optimizer can use directives that are collected on query expressions.
- Directives are automatically maintained created as needed during compilation or execution (missing statistics, cardinality misestimates) and purged if not used after a year.
- Directives can be monitored using dba_sql_plan_directives and dba_sql_plan_dir_objects dictionary tables.
- Sql plan directives are persisted to disk in the SYSAUX tablespace; a plan directive is created and purged to disk (SYSXAUX) every 15 minutes.
What Are Dynamic Statistics (Dynamic Sampling)?
The Oracle optimizer uses a number of inputs to come with an optimal plan. Those inputs can be: object constraints, statistics on the tables, columns, and indexes involved in the query, system statistics (IO speed, CPU). Those inputs help the optimizer to come up with estimated cardinalities. Those cardinalities serve the optimizer to decide the cost of execution of the query and decide the execution plan. When estimated cardinalities are wrong, the optimizer will choose an inefficient execution plan. When dynamic statistics is used the optimizer may get the right estimated cardinality values or better estimated cardinalities thus the optimizer may come up with a better estimated execution cost and optimal execution plan.
Dynamic statistics is used to compensate for missing or not updated statistics that would lead to a bad execution plan.
Dynamic statistics are automatically used when the optimizer decides it necessary and the resulting statistics are persistent in the statistics repository making them available to other queries.
In Oracle Database 12c dynamic sampling has been enhanced to become dynamic statistics.
Dynamic sampling offers 12 setting levels (0 through 11). The table below shows the Dynamic Statistics Levels.
References: Oracle Database SQL Tuning Guide
|
Level
|
When the Optimizer Uses Dynamic Statistics
|
Sample Size (Blocks)
|
|
0
|
Do not use dynamic statistics
|
n/a
|
|
1
|
Use dynamic statistics for all tables that do not have statistics, but only if the following criteria are met:
- There is at least 1 non partitioned table in the query that does not have statistics.
- This table has no indexes.
- This table has more blocks than the number of blocks that would be used for dynamic statistics of this table.
|
32
|
|
2
|
Use dynamic statistics if at least one table in the statement has no statistics. This is the default setting.
|
64
|
|
3
|
Use dynamic statistics if any of the following conditions is true:
- The statement meets level 2 criteria.
- The statement has one or more expressions used in the WHERE clause predicates, for example, WHERE SUBSTR (CUSTLASTNAME, 1, 3).
|
64
|
|
4
|
Use dynamic statistics if any of the following conditions is true:
- The statement meets level 3 criteria.
- The statement uses complex predicates (an OR or AND operator between multiple predicates on the same table).
|
64
|
|
5
|
Use dynamic statistics if the statement meets level 4 criteria.
|
128
|
|
6
|
Use dynamic statistics if the statement meets level 4 criteria.
|
256
|
|
7
|
Use dynamic statistics if the statement meets level 4 criteria.
|
512
|
|
8
|
Use dynamic statistics if the statement meets level 4 criteria.
|
1024
|
|
9
|
Use dynamic statistics if the statement meets level 4 criteria.
|
4086
|
|
10
|
Use dynamic statistics if the statement meets level 4 criteria.
|
All blocks
|
|
11
|
Use dynamic statistics automatically when the optimizer deems it necessary. The resulting statistics are persistent in the statistics repository, making them available to other queries.
|
Automatically determined
|
There are two ways to use dynamic statistics:
- The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command.
- The DYNAMIC_SAMPLING query hint can be added to specific queries. /*+ DYNAMIC_SAMPLING (4)*/
How SQL Plan Directive Works
To illustrate when Sql Plan Directive can be used; a user issue a query on the database. The query select a list of columns based on columns conditions.
- User submits a query with an SQL ID: 4jm5nz5f3dwbg
|
SELECT CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS_KFV
WHERE CONCATENATED_SEGMENTS = :B1
AND CHART_OF_ACCOUNTS_ID = :B2;
|
- About the SQl ID 4jm5nz5f3dwbg; The Optimizer determines a plan and after execution the cardinality estimation is discovered to be wrong and Dynamic Statistics (Dynamic Sampling) is recommended.
- User submits a similar query: say its SQL ID is 3gr4ca7f2zabd with different values in the where condition.
|
SELECT *
FROM GL_CODE_COMBINATIONS_KFV
WHERE CONCATENATED_SEGMENTS = :B3
AND CHART_OF_ACCOUNTS_ID = :B4;
|
- About the SQL ID 3gr4ca7f2zabd; The Optimizer checks on directives and finds one created on CONCATENATED_SEGMENTS and CHART_OF_ACCOUNTS_ID columns. The optimizer decides about the plan considering the help of the SQL Plan directives. The optimizer adds columns group creation for CONCATENATED_SEGMENTS and CHART_OF_ACCOUNTS_ID columns and gathers their statistics in the next GL_CODE_COMBINATIONS_KFV table gather statistics.
SQL Plan Directive Demonstration
We consider the following example; first we connect to the 12c container database (CDB).
|
12c(bicdb):/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 28 12:07:57 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL>
|
We display the information about Pluggable databases (PDBs) associated with the current CDB instance.
|
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON\_ID DBID NAME OPEN\_MODE
---------- ---------- ------------------------------ ----------
2 3280889553 PDB$SEED READ ONLY
3 211023784 BICLR1 MOUNTED
4 428310707 BIAPPS READ WRITE
|
We connect to the Pluggable database BIAPPS;
|
SQL> alter session set container=BIAPPS;
Session altered.
|
We create a user “Wissem” for the test.
|
SQL> alter user wissem identified by wissem123;
User altered.
|
Let’s create a table called big_table which has different products, channel ids. Note the table has missing statistics; we didn’t gathered any statistics on table and columns.
|
SQL>
SQL> set timi on
SQL> drop table wissem.big_table purge;
drop table wissem.big_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.08
SQL> create table wissem.big_table as
2 select
3 'iPhone' as product,
4 mod(rownum,5) as channel_id,
5 mod(rownum,1000) as cust_id
6 from dual connect by level<=2000000
7 UNION ALL
select
8 9 'Motorola' as product,
10 mod(rownum,5) as channel_id,
11 mod(rownum,1000) as cust_id
12 from dual connect by level<=10
13 UNION ALL
select
14 15 'Nokia' as product,
16 mod(rownum,5) as channel_id,
17 mod(rownum,1000) as cust_id
18 from dual connect by level<=20401
19 UNION ALL
20 select
21 'Samsung' as product,
22 mod(rownum,5) as channel_id,
23 mod(rownum,1000) as cust_id
24 from dual connect by level<=1000000;
Table created.
Elapsed: 00:00:06.77
SQL>
|
We issue a query on big table specifying conditions on both product and channel columns.
|
set autotrace traceonly
select /*+gather_plan_statistics*/
cust_id , channel_id,product
from wissem.big_table where
product = 'Motorola' and channel_id=1
order by product
; |
Note we use gather_plan_statistics hint which tells Oracle to collect execution statistics for a SQL statement. After that we call the function DBMS_XPLAN.DISPLAY_CURSOR to display the execution plan. We must give the FORMAT input parameter the value of 'ALLSTATS LAST' to show the optimizer estimated rows along with the actual rows gathered during the sql execution phase.
|
SQL> select /*+gather_plan_statistics*/
cust_id , channel_id,product
from wissem.big_table where
product = 'Motorola' and channel_id=1
order by product 2 3 4 5
6 ;
CUST_ID CHANNEL_ID PRODUCT
---------- ---------- --------
1 1 Motorola
6 1 Motorola
Elapsed: 00:00:00.05
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
Elapsed: 00:00:01.49
SQL>
SQL>
|
From the above output, we see the estimated row (151K) is significantly different from the actual rows (2). Therefore we need to see a bad estimated plan showing up.
When we query the v$sql dynamic view, we see the is_reoptimizable column value set to YES which means the optimizer will use Automatic reoptimization to fix the suboptimal plan chosen due to incorrect optimizer estimates.
SQL> set linesize 20000
SQL> set pagesize 20000
SQL> col sql_id format a20
SQL> col child_number format 99
SQL> col sql_text format a40
SQL> col is_reoptimizable format a20
SQL> select sql_id, child_number, sql_text, is_reoptimizable from v$sql where sql_text like '%gather_plan_statistics%';
Let’s flush the actual directive to the SYSAUX tablespace. Remember that the plan directives are purged to disk (SYSXAUX) every 15 minutes.
We call the dbms_spd.flush_sql_plan_directive procedure.
|
SQL> exec dbms_spd.flush_sql_plan_directive;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
SQL>
|
We can select the sql plan directives created in the example, but making a join between dba_sql_plan_directives and dba_sql_plan_dir_objects tables.
We specify the owner ; in this case the user Wissem.
|
select to_char(d.directive_id) directive_id,
o.owner as owner,
o.object_name as object_name,
o.subobject_name as subobject_name,
o.object_type,
d.type,
d.state,
d.reason
from dba_sql_plan_directives d,
dba_sql_plan_dir_objects o
where d.directive_id = o.directive_id
and o.owner = 'WISSEM'; |
From the output above you see that the plan directive is created because of cardinality miss estimation for the table big table. Oracle has defined dynamic statistics (dynamic sampling) on both product and channel columns.
So let's run again the query and we would expect to have dynamic statistics (dynamic sampling) used and plan directives help the optimizer to adjust the execution plan.
|
SQL> select /*+gather_plan_statistics*/
cust_id , channel_id,product
from wissem.big_table where
product = 'Motorola'
and channel_id=1
order by product ;
CUST_ID CHANNEL_ID PRODUCT
---------- ---------- --------
1 1 Motorola
6 1 Motorola
Elapsed: 00:00:00.14
|
We see estimated rows are 10 and the actual rows returned are 2. With the help of the Dynamic statistics the optimizer estimated nearly correct cardinalities.
From the output above, you can see one sql plan directive is used the statement and dynamic sampling is set to 2.
We query again the v$sql.
|
SQL> select sql_id, child_number, sql_text, is_reoptimizable from v$sql where sql_text like '%gather_plan_statistics%';
SQL>
|
From the output above we see a different sql id assigned and the is_reoptimizable column value set to YES which means the optimizer will use Automatic reoptimization to fix the suboptimal plan chosen due to incorrect optimizer estimates.
We change now the value of product from Motorola to Nokia and we check if the Optimizer will still use the plan directive and dynamic sampling
|
SQL>
select /*+gather_plan_statistics*/
cust_id , channel_id,product
from wissem.big_table where
product = 'Nokia' and channel_id=1
order by product ;
CUST_ID CHANNEL_ID PRODUCT
---------- ---------- --------
351 1 Nokia
356 1 Nokia
361 1 Nokia
.....
..... => content truncated , too many rows returned
.....
4081 rows selected.
Elapsed: 00:00:01.14
SQL>
|
|
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
|
We can see that the optimizer uses sql plan directive for the new sql statement and dynamic sampling is set to 2.
Conclusion:
In this article, we have see how sql plan directive can help the Oracle optimizer with additional information, column group statistics and instructions on missing statistics, cardinality misestimates so the optimizer can generate a better cardinality estimation and better execution plan.
About the Author
Wissem is a Senior DBA with over 12 years of experience specialized in Oracle HA solutions / Big Data. He works for the “Schneider Electric / APC Global operations”. Wissem has worked as well for several international enterprises leaders in sectors of Banks, Telecommunications, Internet and Energy. Wissem is the first Oracle ACE in Spain and He is OCP DBA. Follow Wissem on the Oracle Community Platform at @"orawiss"