Dynamic Statistics in Oracle 12c

Dynamic statistics allow the optimizer to augment existing statistics to get more accurate cardinality estimates for not only single table accesses but also for joins and group-by predicates.

The results of the dynamic sampling queries will be persisted in the cache, as dynamic statistics, allowing other SQL statements to share these statistics.With dynamic sampling oracle decide whether to go for dynamic sampling or not.

Basic Functionality:
In Oracle Database 12c dynamic sampling has been enhanced to become dynamic statistics.

Pre 11g Dynamic Sampling levels

level

A new level, 11 has been introduced for the initialization parameter OPTIMIZER_DYNAMIC_SAMPLING.
Level 11 enables the optimizer to automatically decide to use dynamic statistics for any SQL statement, even if all basic table statistics exist.

With OPTIMIZER_DYNAMIC_SAMPLING =11.

It implies that dynamic sampling will be AUTO. Based on optimizer intelligence it will choose whether too use dynamic sampling or not.
If yes then
1. for what sample size.
2. upto what level
3. for which conditions.

Case Data:

SQL Query with LIKE predicates and wildcards

Suppose At DB level , dynamic sampling level is set as 0.

With “optimizer_dynamic_sampling = 2”

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING =2;

For Below SQL,
SELECT COUNT(*) FROM TBL_TEST_OBJ WHERE object_name LIKE '%K%';
Output: 2811.
Plan
SELECT STATEMENT Cost: 82 Cardinality: 1 
 2 SORT AGGREGATE Cardinality: 1 
  1 TABLE ACCESS FULL TABLE PERF12C.TBL_TEST_OBJ Cost: 82 Cardinality: 962

Cardinality estimate is 3X offset with actual records.

With “optimizer_dynamic_sampling =11”

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING =11;

For same SQL, Plan as below.
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 82 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS FULL| TBL_TEST_OBJ | 2811 | 56220 | 82 (0)| 00:00:01 |
----------------------------------------------------------------------
Note 
- dynamic statistics used: dynamic sampling (level=AUTO)

Cardinality estimate is more accurate.
It automatically decide to enhance stats by dynamic sampling for better estimations.

But i personally believe to avoid any overhead during SQL parsing , best practice would be to set the same at session levels IFF needed and not as Global database params.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s