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)