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: Disable the dynamic sampling.
Level 1: At least one non-partitioned table in the statement has no statistics. Sample Size: 32 Blocks
Level 2: One or more tables in the statement have no statistics. This is DEFAULT setting. Sample Size: 64 Blocks
Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus table used condition in where clause e.g a+b, substring(a,2,4) etc. Sample Size: 64 Blocks
Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus use any statement that has complex predicates eg OR or AND operator. Sample size: 64 Blocks
Levels 5, 6, 7, 8, and 9: Any statement that meets level 4 criteria and sample block size are 128,256,512,1024 and 4086 Blocks
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table. Sample Size: all blocks
Level 11: The database determines automatically if dynamic statistics are required.

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!

2 thoughts on “Dynamic sampling performance issue

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

  2. Pingback: _optimizer_ads_use_result_cache parameter use in Oracle | 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.