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

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!