Dynamic sampling performance issue

Dynamic sampling performance issue on 11g

Dynamic sampling is used by optimizer to fetch the low cost execution plan.
Default value is 2.

Show parameter OPTIMIZER_DYNAMIC_SAMPLING

NAME                       TYPE     VALUE
-------------------------- -------- -------
optimizer_dynamic_sampling integer  2

Its has different level, if we want to disable use of dynamic_sampling =0
Level 0: Do not use dynamic sampling.
Level 1: Sample all tables that have not been analyzed
1. at least 1 unanalyzed table in the query;
2. unanalyzed table is in joined condition
3. this unanalyzed table has no indexes;
4. this unanalyzed table has more blocks than the number of blocks(i.e 32) that would be used for dynamic sampling of this table.
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate.
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. T
Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

Problem:
Multiple execution of query found in SP or AWR Report:
Dynamic sampling is used in the database, it caused the performance issue in the database by consuming CPU at runtime gather stats. Muliple run caused the performance issue.

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO

Solution
For disable it, we need to change the parameter value to zero.
Alter system set optimizer_dynamic_sampling=0 scope=both

Note: Optimizer will have behavior to decide the dynamic sampling will be used or what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables and partitioned table.
Where as we expect parallel statements to be more resource intensive, so the additional overhead at compile time is worth it to ensure we can be best execution plan.

Plan hash value: 3061943406
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop|
------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 563| 106 (0)| 00:00:02 | | |
| 1 |PARTITION RANGE SINGLE | | 1 | 563| 106 (0)| 00:00:02 | 262 | 262 |
|* 2 |TABLE ACCESS BY LOCAL INDEX ROWID | TRAN | 1 |563|106(0)|00:00:02|262|262|
|* 3 |INDEX RANGE SCAN | 1TRAN_PRIMARY_INDEX | 7 | |69(0)|00:00:01|262|262|
Note
-----
- dynamic sampling used for this statement (level=7)

In 12c
Note:
------
dynamic statistics used: dynamic sampling (level=AUTO)

Problem: (Bug 11841491 : POOR PERFORMANCE WITH FIX 7452863)
optimizer_dynamic_sampling is set to 2, and table has statistics.
But dynamic sampling is automatically involved by optimizer in few cases.

To fix this issue “Oracle cover this in bug 7452863”

ALTER SESSION SET "_fix_control"='7452863:0';
Note: you can disable it by zero parameter as above do.
Alter system set optimizer_dynamic_sampling=0 scope=both;

Note:Dynamic statistics has been enhanced in Oracle 12c, but may cause longer parse time during parsing. If you face any issue of slowness then first trace the SQL statements and check if they used dynamic sampling then try to use above bug fix parameter. May your performance issue is fixed. Thanks!

Advertisements

One thought on “Dynamic sampling performance issue

  1. Pingback: Adaptive feature caused performance in 12c | Smart way of Technology

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.