November 12, 2010
I read a message thread on the Oracle-L list that caused me to pause for a moment…
If you display an execution plan and see a message stating “Dynamic sampling used”, you might be left wondering what caused the dynamic sampling. From the Oracle Database 11.2 documentation, the various levels of dynamic sampling:
| Level | When the Optimizer Uses Dynamic Sampling | Sample Size (Blocks) |
| 0 | Do not use dynamic sampling | n/a |
| 1 | Use dynamic sampling for all tables that have not been analyzed, but only if the following criteria are met:
|
32 |
| 2 | Use dynamic sampling if least one table in the statement has no statistics. | 64 |
| 3 | Use dynamic sampling if any of the following conditions is true:
|
64 |
| 4 | Use dynamic sampling if any of the following conditions is true:
|
64 |
| 5 | Use dynamic sampling if the statement meets level 4 criteria. | 128 |
| 6 | Use dynamic sampling if the statement meets level 4 criteria. | 256 |
| 7 | Use dynamic sampling if the statement meets level 4 criteria. | 512 |
| 8 | Use dynamic sampling if the statement meets level 4 criteria. | 1024 |
| 9 | Use dynamic sampling if the statement meets level 4 criteria. | 4086 |
| 10 | Use dynamic sampling for all statements. | All blocks |
The same page in the documentation also states the following:
“You can disable the feature by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to 9.2.0 or by setting OPTIMIZER_DYNAMIC_SAMPLING=0.”
The above is an interesting quote, if we check the documentation for Oracle Database 9.2 we find that dynamic sampling is a new feature in Oracle Database 9.2:
“You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10.
- A value of 0 means dynamic sampling will not be done.
- A value of 1 (the default) means dynamic sampling will be performed if all of the following conditions are true:
- There is more than one table in the query.
- Some table has not been analyzed and has no indexes.
- The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table.
- Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.
Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled.
The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.0.2″
It appears that there is a little bit of disagreement between the documentation for the two Oracle Database releases regarding the value of OPTIMIZER_FEATURES_ENABLE that disables dynamic sampling. But wait, it appears that there are even more differences between the two releases… and just about as many changes between 11.1.0.7 and 11.2.0.2. A quick trip through Metalink (MOS) finds the following:
- Dynamic sampling level used for a SQL statement is different from what is expected, Metalink ID 1102413.1. See this blog article for an example of the dynamic sampling level being automatically adjusted from 2 to 5 for a query executed with a parallel degree (my article pre-dates the Metalink article by a couple of months – I remember posting somewhere early in 2010, that I was surprised to see the dynamic sampling level automatically incease from 2 to either 5 or 8 for a query).
- Occasional wrong results in a RAC environment for parallel queries caused by dynamic sampling in Oracle Database 11.1.0.7 through 11.2.0.1, see Bug 9705349.
- User statistics on columns ignored due to dynamic sampling in Oracle Database 11.2.0.1 and below, see Bug 9272549.
- Dynamic sampling is not used for a query when the CURRENT_SCHEMA is set for a session in Oracle Database 11.1.0.7 and below, see Bug 5586604.
- Dynamic sampling does not work for query blocks with OR expansion in Oracle Database 11.1.0.7 and below, see Bug 6956212.
- Dynamic sampling causes poor join cardinality in Oracle Database 11.2.0.1 and below, see Bug 8767442.
- Dynamic sampling causes poor cardinality estimates when multiple columns are used to join tables in Oracle Database 11.2.0.1 and below, see Bug 8784639
- Dynamic sampling causes poor cardinaility estimates for indexes on partitioned tables in Oracle Database 11.2.0.1 and below, see Bug 6408301.
From the above short list, we see that there are a lot of potential changes to dynamic sampling behavior following an upgrade. According to the documentation, setting OPTIMIZER_FEATURES_ENABLE to 9.2.0 in Oracle Database 11.2.0.1 and above will disable dynamic sampling, while the 9.2 documentation indicates that it should be enabled at level 1 (where level 1 apparently means something slightly different from what it means in 11.2.0.1, see the test scripts below to see what really happens).
Getting back to the Oracle-L thread, I modified one of my earlier test case scripts to not collect statistics for one of four tables:
CREATE TABLE T1 (
C1 NUMBER,
C2 NUMBER,
C3 VARCHAR2(100),
C4 VARCHAR2(500),
PRIMARY KEY (C1));
INSERT INTO T1
SELECT
ROWNUM,
MOD(ROWNUM,100),
RPAD('A',100,'A'),
RPAD('A',500,'A')
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000),
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000);
CREATE TABLE T2 (
C1 NUMBER,
C2 NUMBER,
C3 VARCHAR2(100),
C4 VARCHAR2(500),
PRIMARY KEY (C1));
INSERT INTO T2
SELECT
ROWNUM,
MOD(ROWNUM,100),
RPAD('A',100,'A'),
RPAD('A',500,'A')
FROM
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000),
(SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=1000);
CREATE TABLE T3 AS
SELECT
ROWNUM C1
FROM
DUAL
CONNECT BY
LEVEL<=2;
CREATE TABLE T4 AS
SELECT
ROWNUM C1
FROM
DUAL
CONNECT BY
LEVEL<=2;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)
In the above, notice that I did not collect statistics on table T1. Now, take a moment to imagine that instead of having the 4 tables above involved in a SQL statement, that there are 50 or more tables involved in a SQL statement. It would be time consuming to check each table to see which one is missing statistics (in some cases it might not be desirable to have statistics on all tables). Let’s try a quick test to see if we are able to determine which of the tables does not have statistics (on 11.2.0.1):
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T1_10053-TEST';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
SET AUTOTRACE TRACEONLY EXPLAIN
SET LINESIZE 140
SET PAGESIZE 1000
SET TRIMSPOOL ON
SELECT /*+ FIND_ME */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Execution Plan
----------------------------------------------------------
Plan hash value: 2884158292
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10101 | 1903K| 11538 (60)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI | | 10101 | 1903K| 11538 (60)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10101 | 1874K| 11532 (60)| 00:00:02 |
|* 4 | HASH JOIN RIGHT SEMI| | 10101 | 1104K| 5790 (60)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1000K| 103M| 5419 (57)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 952K| 70M| 5385 (57)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C2"="C1")
3 - access("T1"."C1"="T2"."C1")
4 - access("T2"."C2"="C1")
Note
-----
- dynamic sampling used for this statement (level=2)
Note that the execution plan shows that dynamic sampling was used at level 2, which is the default starting with Oracle Database 10.1. Now let’s try a couple of experiments. First, enabling parallel:
SELECT /*+ FIND_ME PARALLEL(8) */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 1390077178
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10101 | 1903K| 26185 (1)| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 10101 | 1903K| 26185 (1)| 00:00:03 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN RIGHT SEMI | | 10101 | 1903K| 26185 (1)| 00:00:03 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 2 | 6 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | | | | | Q1,02 | PCWP | |
| 9 | NESTED LOOPS | | 10101 | 1874K| 26182 (1)| 00:00:03 | Q1,02 | PCWP | |
|* 10 | HASH JOIN RIGHT SEMI | | 10101 | 1104K| 24800 (1)| 00:00:03 | Q1,02 | PCWP | |
| 11 | PX RECEIVE | | 2 | 6 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10001 | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 13 | PX BLOCK ITERATOR | | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 1000K| 103M| 24751 (1)| 00:00:03 | Q1,02 | PCWC | |
| 16 | TABLE ACCESS FULL | T2 | 1000K| 103M| 24751 (1)| 00:00:03 | Q1,02 | PCWP | |
|* 17 | INDEX UNIQUE SCAN | SYS_C0024756 | 1 | | 0 (0)| 00:00:01 | Q1,02 | PCWP | |
| 18 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 0 (0)| 00:00:01 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C2"="C1")
10 - access("T2"."C2"="C1")
17 - access("T1"."C1"="T2"."C1")
Note
-----
- dynamic sampling used for this statement (level=4)
- Degree of Parallelism is 8 because of hint
Interesting, the dynamic sampling level was changed from the default of 2 to 4 for this SQL statement. I wonder which tables were affected – we will check later, first a couple of more tests:
SELECT /*+ FIND_ME OPTIMIZER_FEATURES_ENABLE('9.0.1') */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 3748566283
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 386 | 175 (5)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 2 | 386 | 175 (5)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 2 | 380 | 174 (5)| 00:00:01 |
| 3 | NESTED LOOPS | | 82 | 15334 | 172 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 82 | 6396 | 2 (50)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 109 | 3 (34)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0024757 | 1 | | 2 (50)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (50)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (50)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C2"="C1")
2 - access("T2"."C2"="C1")
6 - access("T1"."C1"="T2"."C1")
No note about dynamic sampling with OPTIMIZER_FEATURES_ENABLE at 9.0.1, let’s try 9.2.0:
SELECT /*+ FIND_ME OPTIMIZER_FEATURES_ENABLE('9.2.0') */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 2710410714
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 193 | 15 (20)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 193 | 15 (20)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 2 | 168 | 11 (28)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 2 | 162 | 9 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 82 | 6396 | 3 (34)| 00:00:01 |
| 5 | BUFFER SORT | | 2 | 6 | | |
| 6 | SORT UNIQUE | | 2 | 6 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T4 | 2 | 6 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T3 | 2 | 6 | 3 (34)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 109 | 3 (34)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0024757 | 1 | | 2 (50)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C2"="C1")
9 - filter("T2"."C2"="C1")
10 - access("T1"."C1"="T2"."C1")
Still no note about dynamic sampling, but now we see a Cartesian join in the execution plan (note that this may not be a bad/inappropriate Cartesian join). Let’ s jump up to 10.1.0:
SELECT /*+ FIND_ME OPTIMIZER_FEATURES_ENABLE('10.1.0') */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 888444315
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 3769K| | 13001 (57)| 00:00:02 |
|* 1 | HASH JOIN | | 20000 | 3769K| 4968K| 13001 (57)| 00:00:02 |
|* 2 | HASH JOIN RIGHT SEMI | | 40000 | 4492K| | 5940 (61)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T4 | 2 | 6 | | 2 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN| | 2000K| 213M| | 5200 (56)| 00:00:01 |
| 5 | SORT UNIQUE | | 2 | 6 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 2 | 6 | | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1000K| 103M| | 5198 (56)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T2 | 1000K| 103M| | 5197 (56)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 952K| 70M| | 5175 (55)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="C1")
2 - access("T2"."C2"="C1")
Note
-----
- dynamic sampling used for this statement (level=2)
The dynamic sampling note has returned, and we still have a Cartesian join (just with a different set of tables). Trying again at 10.2.0.1:
SELECT /*+ FIND_ME OPTIMIZER_FEATURES_ENABLE('10.2.0.1') */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 2884158292
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10101 | 1903K| 11538 (60)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI | | 10101 | 1903K| 11538 (60)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10101 | 1874K| 11532 (60)| 00:00:02 |
|* 4 | HASH JOIN RIGHT SEMI| | 10101 | 1104K| 5790 (60)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1000K| 103M| 5419 (57)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 952K| 70M| 5385 (57)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C2"="C1")
3 - access("T1"."C1"="T2"."C1")
4 - access("T2"."C2"="C1")
Note
-----
- dynamic sampling used for this statement (level=2)
We are still seeing the dynamic sampling note, and the Cartesian join is no longer present. One more test at 11.1.0.7:
Plan hash value: 2884158292
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10101 | 1903K| 11538 (60)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI | | 10101 | 1903K| 11538 (60)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10101 | 1874K| 11532 (60)| 00:00:02 |
|* 4 | HASH JOIN RIGHT SEMI| | 10101 | 1104K| 5790 (60)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1000K| 103M| 5419 (57)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 952K| 70M| 5385 (57)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C2"="C1")
3 - access("T1"."C1"="T2"."C1")
4 - access("T2"."C2"="C1")
Note
-----
- dynamic sampling used for this statement (level=2)
The above did not change from the test at 10.2.0.1. For comparision with the parallel test that was near the start, with a hinted OPTIMIZER_FEATURES_ENABLE set to 11.1.0.7:
SELECT /*+ FIND_ME OPTIMIZER_FEATURES_ENABLE('11.1.0.7') PARALLEL(8) */
T1.C1,
T2.C1,
T1.C3,1,10 T1_C3,
T2.C3 T2_C3
FROM
T1,
T2
WHERE
T1.C1=T2.C1
AND T1.C2 IN (
SELECT
C1
FROM
T3)
AND T2.C2 IN (
SELECT
C1
FROM
T4);
Plan hash value: 1390077178
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10101 | 1903K| 26185 (1)| 00:00:03 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 10101 | 1903K| 26185 (1)| 00:00:03 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN RIGHT SEMI | | 10101 | 1903K| 26185 (1)| 00:00:03 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 2 | 6 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | T3 | 2 | 6 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | | | | | Q1,02 | PCWP | |
| 9 | NESTED LOOPS | | 10101 | 1874K| 26182 (1)| 00:00:03 | Q1,02 | PCWP | |
|* 10 | HASH JOIN RIGHT SEMI | | 10101 | 1104K| 24800 (1)| 00:00:03 | Q1,02 | PCWP | |
| 11 | PX RECEIVE | | 2 | 6 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10001 | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 13 | PX BLOCK ITERATOR | | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | T4 | 2 | 6 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 1000K| 103M| 24751 (1)| 00:00:03 | Q1,02 | PCWC | |
| 16 | TABLE ACCESS FULL | T2 | 1000K| 103M| 24751 (1)| 00:00:03 | Q1,02 | PCWP | |
|* 17 | INDEX UNIQUE SCAN | SYS_C0024756 | 1 | | 0 (0)| 00:00:01 | Q1,02 | PCWP | |
| 18 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 0 (0)| 00:00:01 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C2"="C1")
10 - access("T2"."C2"="C1")
17 - access("T1"."C1"="T2"."C1")
Note
-----
- dynamic sampling used for this statement (level=2)
- Degree of Parallelism is 8 because of hint
This time, instead of the dynamic sampling level automatically adjusting to a value of 4, it remained at a value of 2. In this case, the change did not affect the execution plan.
Let’s take a look inside the 10053 trace file for the first execution on 11.2.0.1 to see if we are able to determine which tables were affected by dynamic sampling, searching the trace file for dynamic sampling, we find the following:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
*** 2010-11-12 10:43:30.507
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: SYS_C0024756, blocks=1881
** Dynamic sampling updated table stats.: blocks=90911
*** 2010-11-12 10:43:30.507
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
THEN 1 ELSE 0 END),0) FROM (SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ 1 AS C1, 1 AS C2, "T1"."C1" AS C3
FROM "T1" SAMPLE BLOCK (0.069299 , 1) SEED (1) "T1") SAMPLESUB
*** 2010-11-12 10:43:30.522
** Executed dynamic sampling query:
level : 2
sample pct. : 0.069299
actual sample size : 660
filtered sample card. : 660
orig. card. : 82
block cnt. table stat. : 90911
block cnt. for sampling: 90911
max. sample block cnt. : 64
sample block cnt. : 63
ndv C3 : 660
scaled : 952400.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : -1.00000000
** Dynamic sampling col. stats.:
Column (#1): C1( Part#: 0
AvgLen: 22 NDV: 952400 Nulls: 0 Density: 0.000001
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
Scaled NDVs using cardinality = 952401.
** Using dynamic sampling card. : 952401
** Dynamic sampling updated table card.
Table: T1 Alias: T1
Card: Original: 952400.952381 Rounded: 952401 Computed: 952400.95 Non Adjusted: 952400.95
Access Path: TableScan
Cost: 5385.22 Resp: 5385.22 Degree: 0
Cost_io: 2333.00 Cost_cpu: 828373422
Resp_io: 2333.00 Resp_cpu: 828373422
Best:: AccessPath: TableScan
Cost: 5385.22 Degree: 1 Resp: 5385.22 Card: 952400.95 Bytes: 0
Access path analysis for T2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T2[T2]
Table: T2 Alias: T2
Card: Original: 1000000.000000 Rounded: 1000000 Computed: 1000000.00 Non Adjusted: 1000000.00
Access Path: TableScan
Cost: 5418.55 Resp: 5418.55 Degree: 0
Cost_io: 2333.00 Cost_cpu: 837417232
Resp_io: 2333.00 Resp_cpu: 837417232
Best:: AccessPath: TableScan
Cost: 5418.55 Degree: 1 Resp: 5418.55 Card: 1000000.00 Bytes: 0
***************************************
In the above, we are able to see that dynamic sampling was performed for table T1, but not for table T2. Let’s check the 10053 trace for the first parallel execution:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T4[T4]
*** 2010-11-12 10:45:01.533
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T4 Alias: T4
Card: Original: 2.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 300
Resp_io: 2.00 Resp_cpu: 42
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 8 Resp: 2.00 Card: 2.00 Bytes: 0
Access path analysis for T3
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T3[T3]
*** 2010-11-12 10:45:01.533
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T3 Alias: T3
Card: Original: 2.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 300
Resp_io: 2.00 Resp_cpu: 42
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 8 Resp: 2.00 Card: 2.00 Bytes: 0
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]
*** 2010-11-12 10:45:01.533
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 4).
** Dynamic sampling updated index stats.: SYS_C0024756, blocks=1881
** Dynamic sampling updated table stats.: blocks=90911
*** 2010-11-12 10:45:01.533
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL
THEN 1 ELSE 0 END),0) FROM (SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ 1 AS C1, 1 AS C2, "T1"."C1" AS C3
FROM "T1" SAMPLE BLOCK (0.069299 , 1) SEED (1) "T1") SAMPLESUB
*** 2010-11-12 10:45:01.533
** Executed dynamic sampling query:
level : 4
sample pct. : 0.069299
actual sample size : 660
filtered sample card. : 660
orig. card. : 82
block cnt. table stat. : 90911
block cnt. for sampling: 90911
max. sample block cnt. : 64
sample block cnt. : 63
ndv C3 : 660
scaled : 952400.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : -1.00000000
** Dynamic sampling col. stats.:
Column (#1): C1( Part#: 0
AvgLen: 22 NDV: 952400 Nulls: 0 Density: 0.000001
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
Scaled NDVs using cardinality = 952401.
** Using dynamic sampling card. : 952401
** Dynamic sampling updated table card.
Table: T1 Alias: T1
Card: Original: 952400.952381 Rounded: 952401 Computed: 952400.95 Non Adjusted: 952400.95
Access Path: TableScan
Cost: 2999.75 Resp: 24746.43 Degree: 0
Cost_io: 2333.00 Cost_cpu: 180956190
Resp_io: 24653.83 Resp_cpu: 25132804
Best:: AccessPath: TableScan
Cost: 24746.43 Degree: 8 Resp: 24746.43 Card: 952400.95 Bytes: 0
Access path analysis for T2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T2[T2]
*** 2010-11-12 10:45:01.533
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T2 Alias: T2
Card: Original: 1000000.000000 Rounded: 1000000 Computed: 1000000.00 Non Adjusted: 1000000.00
Access Path: TableScan
Cost: 3033.07 Resp: 24751.06 Degree: 0
Cost_io: 2333.00 Cost_cpu: 190000000
Resp_io: 24653.83 Resp_cpu: 26388889
Best:: AccessPath: TableScan
Cost: 24751.06 Degree: 8 Resp: 24751.06 Card: 1000000.00 Bytes: 0
Again, we see that dynamic sampling was performed only for table T1 (at level 4). The potentially useful bit from this exercise is this:
** Generated dynamic sampling query: query text : SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB)...
We could check the library cache to find all similar SQL statements to determine on which tables dynamic sampling was performed:
SELECT
SQL_TEXT
FROM
V$SQL
WHERE
SQL_TEXT LIKE 'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE %';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALL
EL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("Q") FULL("Q") NO_PARALLEL_
INDEX("Q") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "MGMT_TASK_QTABLE" "Q") SAMPLESUB
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALL
EL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :
"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("QT") FULL("QT") NO_PARALLEL_INDEX("QT") */ :"SY
S_B_5" AS C1, CASE WHEN "QT"."STATE"<>:"SYS_B_6" OR "QT"."STATE"<>:"SYS_B_7" THEN :"SYS_B_8" ELSE :"SYS_B_9" END AS C2, "QT"."MSGID" AS C3 F
ROM SYSMAN."MGMT_NOTIFY_QTABLE" "QT") SAMPLESUB
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALL
EL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :
"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */ :"SYS_B_5" AS C1, :"SYS_
B_6" AS C2, "T1"."C1" AS C3 FROM "T1" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "T1") SAMPLESUB
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALL
EL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0) FR
OM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("QT") FULL("QT") NO_PARALLEL_INDEX("QT") */ 1 AS C1, CASE WHEN "QT"."Q_NAME"=:B1 AND "QT"."ST
ATE"=:B2 THEN 1 ELSE 0 END AS C2, "QT"."Q_NAME" AS C3 FROM SYSMAN."MGMT_TASK_QTABLE" "QT") SAMPLESUB
The above is a little hard to read, so we might try something like this to pick out just the table names:
SELECT SUBSTR(TAB,1,INSTR(TAB,' ')-1) TAB FROM (SELECT SUBSTR(SQL_TEXT,INSTR(SQL_TEXT,'FROM ',1,2)+5) TAB FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE %'); TAB --------------------------- "MGMT_TASK_QTABLE" SYSMAN."MGMT_NOTIFY_QTABLE" "T1" SYSMAN."MGMT_TASK_QTABLE"
So, with the above, we are able to see on which tables dynamic sampling was performed, but we do not see the full picture of why, and at what level (was the dynamic sampling level 1 or 10 – the OTHER_XML column will indicate the sampling level for a single query, as seen below), without a 10053 trace enabled. From the 10053 trace file:
Content of other_xml column
===========================
dop_reason : hint
dop : 8
db_version : 11.2.0.1
parse_schema : TESTUSER
dynamic_sampling: 2
plan_hash : 1390077178
plan_hash_2 : 1881449145
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.2.0.1')
ALL_ROWS
SHARED(8)
OUTLINE_LEAF(@"SEL$CC7EC59E")
UNNEST(@"SEL$2")
UNNEST(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$CC7EC59E" "T2"@"SEL$1")
FULL(@"SEL$CC7EC59E" "T4"@"SEL$3")
INDEX(@"SEL$CC7EC59E" "T1"@"SEL$1" ("T1"."C1"))
FULL(@"SEL$CC7EC59E" "T3"@"SEL$2")
LEADING(@"SEL$CC7EC59E" "T2"@"SEL$1" "T4"@"SEL$3" "T1"@"SEL$1" "T3"@"SEL$2")
USE_HASH(@"SEL$CC7EC59E" "T4"@"SEL$3")
USE_NL(@"SEL$CC7EC59E" "T1"@"SEL$1")
NLJ_BATCHING(@"SEL$CC7EC59E" "T1"@"SEL$1")
USE_HASH(@"SEL$CC7EC59E" "T3"@"SEL$2")
PQ_DISTRIBUTE(@"SEL$CC7EC59E" "T4"@"SEL$3" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$CC7EC59E" "T1"@"SEL$1" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$CC7EC59E" "T3"@"SEL$2" NONE BROADCAST)
SWAP_JOIN_INPUTS(@"SEL$CC7EC59E" "T4"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$CC7EC59E" "T3"@"SEL$2")
END_OUTLINE_DATA
*/
Perhaps there is another database view that exposes such information?
More resources:
Charles Hooper's Oracle Notes 
Recent Comments