Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

The degree of sampling is not being honored by the DYNAMIC_SAMPLING hint

User_A7RKTNov 1 2018 — edited Nov 4 2018

Hi guys,

It is Oracle 12c R2 on Linux.

In the following test case, according to the SQL*Plus output, the provided degree of sampling in the DYNAMIC_SAMPLING hint is not being honored.

Can you explain please?

-- the parameter is set to its default value

SQL> SHOW PARAMETER OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_dynamic_sampling           integer     2

-- the query by default doesn't use any dynamic statistics

SET AUTOT TRACE EXP

SELECT

* FROM SOE.CUSTOMERS S WHERE NLS_LANGUAGE = 'us' AND NLS_TERRITORY='AMERICA';

SQL> SQL>   2

Execution Plan

----------------------------------------------------------

Plan hash value: 2008213504

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     6 |   678 |   822   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CUSTOMERS |     6 |   678 |   822   (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NLS_LANGUAGE"='us' AND "NLS_TERRITORY"='AMERICA')

-- when the hint is being used, the dynamic sampling works on the provided degree (2)

SELECT /*+ DYNAMIC_SAMPLING(S 2) */

* FROM SOE.CUSTOMERS S WHERE NLS_LANGUAGE = 'us' AND NLS_TERRITORY='AMERICA';

Execution Plan

----------------------------------------------------------

Plan hash value: 2008213504

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           | 21862 |  2412K|   822   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 21862 |  2412K|   822   (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NLS_LANGUAGE"='us' AND "NLS_TERRITORY"='AMERICA')

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

-- when the provided degree is 4, the output still indicates that the sampling level is 2.

--  this is true with all the levels from 3 to 10.

SQL> SELECT /*+ DYNAMIC_SAMPLING(S 4) */

* FROM SOE.CUSTOMERS S WHERE NLS_LANGUAGE = 'us' AND NLS_TERRITORY='AMERICA';

Execution Plan

----------------------------------------------------------

Plan hash value: 2008213504

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           | 19268 |  2126K|   822   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 19268 |  2126K|   822   (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NLS_LANGUAGE"='us' AND "NLS_TERRITORY"='AMERICA')

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

This post has been answered by Jonathan Lewis on Nov 2 2018
Jump to Answer
Comments
Post Details
Added on Nov 1 2018
6 comments
376 views