Hi,
I don't understand why Oracle use dynamic sampling (and why at level 5).
The parameter optimizer_dynamic_sampling is set to 2.
Statistics on table is computed (default Oracle stat)
select num_rows,blocks,sample_size,last_analyzed from all_tables where owner [...];
NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ----------- -------------
1468526 147355 1468526 02-SEP-15
The parallel option is set at table level.
When i read
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF95254 Oracle should not use dynamic sampling for this query, i don't set optimizer_dynamic_sampling to 11 at system or session level.
Why dynamic sampling is enable and how he choose the level ?
Oracle Release :
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Parameter
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
optimizer_dynamic_sampling integer 2
explain plan for
SELECT XXX.SER, TO_CHAR(XXX.DATE_VYX, 'YYYY/MM/DD HH24:MI'), COUNT(1)
FROM SCHEMA.THE_TABLE XXX
WHERE ((XXX.DATE_VYX >= TRUNC((SYSDATE - 10), 'MI')) AND (XXX.DATE_VYX < TRUNC((SYSDATE - 2/1440), 'MI')))
GROUP BY TO_CHAR(XXX.DATE_VYX, 'YYYY/MM/DD HH24:MI'), XXX.SERVICE
ORDER BY TO_CHAR(XXX.DATE_VYX, 'YYYY/MM/DD HH24:MI')
;
[...]
Plan hash value: 3530581192
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1464K| 16M| | 568K (1)| 00:18:04 | | | |
|* 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1464K| 16M| | 568K (1)| 00:18:04 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1464K| 16M| 28M| 568K (1)| 00:18:04 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1464K| 16M| | 568K (1)| 00:18:04 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1464K| 16M| | 568K (1)| 00:18:04 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1464K| 16M| 28M| 568K (1)| 00:18:04 | Q1,00 | PCWP | |
|* 7 | FILTER | | | | | | | Q1,00 | PCWC | |
| 8 | PX BLOCK ITERATOR | | 1464K| 16M| | 568K (1)| 00:18:04 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| THE_TABLE | 1464K| 16M| | 568K (1)| 00:18:04 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-.001388888888888888888888888888888888888889,'fmmi')>TRUNC(SYSDATE@!-10,'fmmi'))
7 - filter(TRUNC(SYSDATE@!-.001388888888888888888888888888888888888889,'fmmi')>TRUNC(SYSDATE@!-10,'fmmi'))
9 - filter("XXX"."DATE_VYX">=TRUNC(SYSDATE@!-10,'fmmi') AND
"XXX"."DATE_VYX"<TRUNC(SYSDATE@!-.001388888888888888888888888888888888888889,'fmmi'))
Note
-----
- dynamic sampling used for this statement (level=5)