Update January 2010: A thread on OTN mentioned this blog post and another, actually contradicting blog post by Asif Momen.
So why are these two blog posts coming to different conclusions regarding Dynamic Sampling and partitions with missing statistics?
This is the good thing with documented test cases - I reproduced what Asif has done and found out that the significant difference between these two test cases is the existence of global level statistics.
In my test case below, I have explicitly gathered statistics only on partition level, and there are no statistics on global level/table (which can be seen from the output of the query against user_tab_statistics below).
Asif has actually gathered statistics on global/table level which can be seen from his blog post.
So the conclusion seems to be: If you prune to a single partition, but this partition has no statistics, then Dynamic sampling will be used if no global/table level statistics are available. If global/table level statistics are available, the optimizer won't perform dynamic sampling and revert to these global/table level statistics instead.
Oddly this obviously doesn't apply to the subpartition/partition level case: Repeating a similar setup with subpartitions having no statistics, but statistics on partition level are available, Dynamic Sampling still was used (tested on 11.1.0.7 Win32).
------------------------------------------------------------------------------
Dynamic sampling for tables with missing statistics is enabled by default from Oracle 10g on (OPTIMIZER_DYNAMIC_SAMPLING = 2). You can get the same behaviour in Oracle 9i by increasing the default dynamic sampling level of 1 to at least 2, by the way, at system, session or statement level (OPTIMIZER_DYNAMIC_SAMPLING parameter or the DYNAMIC_SAMPLING hint at statement level). For more information, see the documentation.
It's an interesting question what happens if you have a partitioned table but only for some of the partitions or subpartitions statistics are missing, and some others have statistics gathered.
Does dynamic sampling selectively kick in depending on which partition accessed or is it simply checking if the table itself has statistics or not?
The following testcase which works only on 11.1 and later since it's using list/range composite partitioning for the subpartition specific tests shows the results of 11.1.0.7 on Win32:
SQL> exec dbms_random.seed(0)
PL/SQL procedure successfully completed.
SQL>
SQL> -- Range partitioning testcase
SQL> CREATE TABLE wr_test
2 ( test_id
3 , trade_date
4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local)
5 PARTITION BY RANGE (trade_date)
6 ( PARTITION p_jan VALUES LESS THAN (DATE '2009-02-01')
7 , PARTITION p_feb VALUES LESS THAN (DATE '2009-03-01')
8 , PARTITION p_mar VALUES LESS THAN (DATE '2009-04-01') )
9 AS
10 SELECT ROWNUM AS test_id
11 , DATE '2009-02-01' + trunc(dbms_random.value(0, 59)) as trade_date
12 FROM dual
13 connect by level <= 1000;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_feb', granularity=>'partition')
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 partition_name
3 , num_rows
4 from
5 user_tab_statistics
6 where
7 table_name = 'WR_TEST';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P_JAN
P_FEB 491
P_MAR
4 rows selected.
SQL>
SQL> -- Dynamic sampling is selectively used on partitions without statistics
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-03-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1136113187
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 352 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 16 | 352 | 2 (0)| 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | WR_TEST | 16 | 352 | 2 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TRADE_DATE"=TO_DATE(' 2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL>
SQL> -- No dynamic sampling with statistics in place
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3091737428
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 198 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 18 | 198 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | INDEX RANGE SCAN | TEST_PK | 18 | 198 | 2 (0)| 00:00:01 | 2 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 rows selected.
SQL>
SQL> drop table wr_test purge;
Table dropped.
SQL>
SQL> exec dbms_random.seed(0)
PL/SQL procedure successfully completed.
SQL>
SQL> -- composite partitioning testcase
SQL> CREATE TABLE wr_test
2 ( test_id
3 , trade_date
4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local)
5 partition by list (test_id)
6 SUBPARTITION BY RANGE (trade_date)
7 (
8 partition p_default values (default)
9 ( SUBPARTITION p_jan VALUES LESS THAN (DATE '2009-02-01')
10 , SUBPARTITION p_feb VALUES LESS THAN (DATE '2009-03-01')
11 , SUBPARTITION p_mar VALUES LESS THAN (DATE '2009-04-01') )
12 )
13 AS
14 SELECT ROWNUM AS test_id
15 , DATE '2009-02-01' + trunc(dbms_random.value(0, 59)) as trade_date
16 FROM dual
17 connect by level <= 1000;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_feb', granularity=>'subpartition')
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 partition_name
3 , subpartition_name
4 , num_rows
5 from
6 user_tab_statistics
7 where
8 table_name = 'WR_TEST';
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
P_DEFAULT
P_DEFAULT P_JAN
P_DEFAULT P_FEB 491
P_DEFAULT P_MAR
5 rows selected.
SQL>
SQL> -- Dynamic sampling also is selectively used on SUBpartitions without statistics
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-03-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1060835009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 352 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 16 | 352 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 16 | 352 | 2 (0)| 00:00:01 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | WR_TEST | 16 | 352 | 2 (0)| 00:00:01 | 3 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TRADE_DATE"=TO_DATE(' 2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
SQL> -- No dynamic sampling with statistics in place
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1060835009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 198 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 18 | 198 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 18 | 198 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | TABLE ACCESS FULL | WR_TEST | 18 | 198 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected.
SQL>
SQL> -- Different treatment of subpartitions in pre-10.2.0.4
SQL> alter session set optimizer_features_enable = '10.2.0.3';
Session altered.
SQL>
SQL> -- Now uses partition-level statistics
SQL> -- These are missing
SQL> -- Therefore dynamic sampling
SQL> -- Although the subpartition accessed has statistics
SQL> -- Bet these are not used by pre-10.2.0.4 optimizer code
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1060835009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 506 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 23 | 506 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | TABLE ACCESS FULL | WR_TEST | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
SQL> -- Gathering statistics on partition level
SQL> exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_default', granularity=>'partition', method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.
SQL>
SQL> -- No longer using dynamic sampling
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3944471208
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 187 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 17 | 187 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 17 | 187 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | TEST_PK | 17 | 187 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected.
SQL>
So as can be seen in 11.1 dynamic sampling is selectively used, depending on what kind of partition pruning is recognized by the optimizer at parse time and if statistics have been gathered for that partition, and this also applies to the subpartition level. The same can be seen in 10.2.0.4, apart from the severe bug regarding single subpartition pruning in the 10.2.0.4 patch set release as shown here.
As already mentioned a couple of times here on my blog, the optimizer code of pre-10.2.0.4 versions doesn't use subpartition level statistics even when pruned to a single subpartition and always reverts to the partition level.
Hi Randolf,
ReplyDeletePost is very nice.
Just to share I am having a different behaviour on 11.1.0.6 (actually same behaviour with 10.2.0.3)
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> select
2 partition_name
3 , subpartition_name
4 , num_rows
5 from
6 user_tab_statistics
7 where
8 table_name = 'WR_TEST';
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
P_DEFAULT
P_DEFAULT P_JAN
P_DEFAULT P_FEB 491
P_DEFAULT P_MAR
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3944471208
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 506 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 23 | 506 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | TEST_PK | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
19 rows selected.
---after statistic gathering on partition level
ReplyDeleteSQL> exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_default', granularity=>'partition', method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3944471208
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 187 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 17 | 187 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 17 | 187 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | TEST_PK | 17 | 187 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected.
Coskan,
ReplyDeletethanks for sharing your findings.
That's odd, in 11.1 the optimizer is supposed to use the subpartition statistics and it did in my previous tests with 11.1.0.6.
Unfortunately I don't have a unpatched 11.1.0.6 at hand, and running the testcase with optimizer_features_enable = '11.1.0.6' on 11.1.0.7 shows the expected behaviour thus I can't reproduce.
What happens if you try this with a range/list composite partitioned table instead of the list/range used in the testcase?
Here's one you could use:
drop table wr_test purge;
exec dbms_random.seed(0)
CREATE TABLE wr_test
( test_id
, trade_date
, CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local)
partition by range (test_id)
SUBPARTITION BY LIST (trade_date)
(
partition p_default values less than (maxvalue)
( SUBPARTITION p_jan VALUES (DATE '2009-01-01')
, SUBPARTITION p_feb VALUES (DATE '2009-02-01')
, SUBPARTITION p_mar VALUES (DATE '2009-03-01') )
)
AS
SELECT ROWNUM AS test_id
, add_months(DATE '2009-02-01', trunc(dbms_random.value(0, 2))) as trade_date
FROM dual
connect by level <= 1000;
exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_feb', granularity=>'subpartition')
select
partition_name
, subpartition_name
, num_rows
from
user_tab_statistics
where
table_name = 'WR_TEST';
-- Dynamic sampling also is selectively used on SUBpartitions without statistics
explain plan for
select * from wr_test where trade_date = date '2009-03-01';
select * from table(dbms_xplan.display);
-- No dynamic sampling with statistics in place
explain plan for
select * from wr_test where trade_date = date '2009-02-01';
select * from table(dbms_xplan.display);
Regards,
Randolf
Hi Randolf Thanks for the update and ready- to run case. Here is the output from my WinXP 32bit 11.1.0.6 environmen. Unfortunatelly behaviour is same
ReplyDeleteSQL> CREATE TABLE wr_test
2 ( test_id
3 , trade_date
4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local)
5 partition by range (test_id)
6 SUBPARTITION BY LIST (trade_date)
7 (
8 partition p_default values less than (maxvalue)
9 ( SUBPARTITION p_jan VALUES (DATE '2009-01-01')
10 , SUBPARTITION p_feb VALUES (DATE '2009-02-01')
11 , SUBPARTITION p_mar VALUES (DATE '2009-03-01') )
12 )
13 AS
14 SELECT ROWNUM AS test_id
15 , add_months(DATE '2009-02-01', trunc(dbms_random.value(0, 2))) as trade_date
16 FROM dual
17 connect by level <= 1000;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'WR_TEST', partname=>'p_feb', granularity=>'subpartition')
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 partition_name
3 , subpartition_name
4 , num_rows
5 from
6 user_tab_statistics
7 where
8 table_name = 'WR_TEST';
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
P_DEFAULT
P_DEFAULT P_JAN
P_DEFAULT P_FEB 514
P_DEFAULT P_MAR
SQL>
SQL> -- Dynamic sampling also is selectively used on SUBpartitions without statistics
ReplyDeleteSQL> explain plan for
2 select * from wr_test where trade_date = date '2009-03-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 1808310657
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 486 | 10692 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 486 | 10692 | 3 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 486 | 10692 | 3 (0)| 00:00:01 | 3 | 3 |
| 3 | TABLE ACCESS FULL | WR_TEST | 486 | 10692 | 3 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
14 rows selected.
SQL>
SQL> -- No dynamic sampling with statistics in place
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 1808310657
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 514 | 11308 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 514 | 11308 | 3 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 514 | 11308 | 3 (0)| 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | WR_TEST | 514 | 11308 | 3 (0)| 00:00:01 | 2 | 2 |
--------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
14 rows selected.
Coskan,
ReplyDeletevery interesting.
I could imagine that 11.1.0.6 is affected by the same bug as 10.2.0.4 when pruning to a single subpartition.
Can you follow this blog note:
http://oracle-randolf.blogspot.com/2009/03/partition-oddities-part-1-severe-bug-in.html
and generate a 10053 trace file of the explain plan where we would expect that the subpartition statistics get used, but instead dynamic sampling gets used?
In 10.2.0.4 I get the following output:
1. Without partition level stats
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 2000 #Blks: 100 AvgRowLen: 100.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [0] (NOT ANALYZED)
#Rows: 8169 #Blks: 100 AvgRowLen: 100.00
(NOT ANALYZED)
#Rows: 2000 #Blks: 2 AvgRowLen: 100.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
*** 2009-06-09 12:41:07.458
** Performing dynamic sampling initial checks. **
Column (#2): TRADE_DATE(DATE) Part#: 0 NO STATISTICS (using defaults)
AvgLen: 9.00 NDV: 63 Nulls: 0 Density: 0.016
Column (#2): TRADE_DATE(DATE) NO STATISTICS (using defaults)
AvgLen: 9.00 NDV: 63 Nulls: 0 Density: 0.016
2. With partition level stats (and still dynamic sampling taking place which seems to be different from your 11.1.0.6 behaviour):
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 2000 #Blks: 100 AvgRowLen: 100.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [0]
#Rows: 1000 #Blks: 4 AvgRowLen: 11.00
(NOT ANALYZED)
#Rows: 2000 #Blks: 2 AvgRowLen: 100.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
*** 2009-06-09 14:06:33.900
** Performing dynamic sampling initial checks. **
Column (#2): TRADE_DATE(DATE) Part#: 0 NO STATISTICS (using defaults)
AvgLen: 9.00 NDV: 63 Nulls: 0 Density: 0.016
Column (#2): TRADE_DATE(DATE) NO STATISTICS (using defaults)
AvgLen: 9.00 NDV: 63 Nulls: 0 Density: 0.016
May be you can spot similar output in the 11.1.0.6 trace file.
Regards,
Randolf
Hi Randolf,
ReplyDeleteLast 4 hours I am trying to generate 10053 trace file but I couldnt. I did everything I can however Oracle 11.1.0.6 without any patch doesnt generate any 10053 trace.
I think I am having the same issue as the one here
http://www.mydatabasesupport.com/forums/oracle-server/379681-oracle-11-10053-trace-doesn-t-produce-any-output.html
tried to contact with the OP, if he gives me a solution other than upgrading 11.1.0.7 I will update with my findings
Finally;
ReplyDeleteThis 10053 output is from 11.1.0.6 EE Running on OEL 5 for the test case you send as comment. We can say it is smilar behaviour 10.2.0.4
---no stats
****************
QUERY BLOCK TEXT
****************
select * from wr_test where trade_date = date '2009-03-01'
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 8169 #Blks: 100 AvgRowLen: 100.00
SUBPARTITIONS::
PRUNED: 1
Read extent maps from 1 subpartitions found 5 blocks
Partition [0] (NOT ANALYZED)
#Rows: 8169 #Blks: 100 AvgRowLen: 100.00
(NOT ANALYZED)
#Rows: 8169 #Blks: 5 AvgRowLen: 100.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 4.00
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 4.00
Access path analysis for WR_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for WR_TEST[WR_TEST]
*** 2009-06-09 18:50:56.187
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TEST_PK, blocks=13
** Dynamic sampling index access candidate : TEST_PK
** Dynamic sampling updated table stats.: blocks=5
---WITH SUBPARTITION LEVEL STATS
****************
QUERY BLOCK TEXT
****************
select * from wr_test where trade_date = date '2009-02-01'
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 8169 #Blks: 100 AvgRowLen: 100.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [0] (NOT ANALYZED)
#Rows: 8169 #Blks: 100 AvgRowLen: 100.00
(NOT ANALYZED)
#Rows: 8169 #Blks: 5 AvgRowLen: 100.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 4.00
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 4.00
Access path analysis for WR_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for WR_TEST[WR_TEST]
*** 2009-06-09 18:51:05.412
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TEST_PK, blocks=13
** Dynamic sampling index access candidate : TEST_PK
** Dynamic sampling updated table stats.: blocks=5
I also send the all output to your mail
Coskan,
ReplyDeletethanks for sending the output.
What I'm not sure about is the following: I read from the results provided that in both cases you've tested dynamic sampling got used.
But in your initial post you said that after analyzing the partition level no dynamic sampling got used in 11.1.0.6.
Also you say now "no stats" and "with subpartition level stats".
But the original issue was about having subpartition stats in place and adding partition level stats afterwards.
I'm not trying to be picky here but in order to understand the issue could we try to clarify this?
I would expect two different 10053 traces, one showing dynamic sampling being used (no partition level stats) and a second one without dynamic sampling (with partition level stats), according to your initial comment.
Regards,
Randolf
I think because of the large outputs and blogspot limits something is confusing about my comments. (you can delete my old comments to make things clear)
ReplyDeleteLet me explain my issue from the beginning For composite partitioning issue on 11.1.0.6, I see dynamic sampling like below but you dont see dynamic sampling on 11.1.0.7. ( which is optimizer_features_enabled=11.1.0.6 has the same bahaviour as optimizer_features_enabled=10.2.0.3)
SQL> select
2 partition_name
3 , subpartition_name
4 , num_rows
5 from
6 user_tab_statistics
7 where
8 table_name = 'WR_TEST';
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
P_DEFAULT
P_DEFAULT P_JAN
P_DEFAULT P_FEB 491
P_DEFAULT P_MAR
SQL> explain plan for
2 select * from wr_test where trade_date = date '2009-02-01';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3944471208
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 506 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 23 | 506 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION RANGE SINGLE| | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | TEST_PK | 23 | 506 | 2 (0)| 00:00:01 | 2 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TRADE_DATE"=TO_DATE(' 2009-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
Coskan,
ReplyDeleteyes, exactly. That's what we're discussing.
Therefore I expected two 10053 traces: The case that you've now posted again that uses dynamic sampling (and which I couldn't reproduce in 11.1.0.7) when having subpartition level statistics but no partition level statistics, and the second one when you've gathered partition level statistics and don't get dynamic sampling anymore (which corresponds to the 10.2.0.3 behaviour as you said).
Regards,
Randolf
Thank god I got it now :)
ReplyDelete---SUBPARTITION LEVEL 10053 --DYNAMIC SAMPLING
****************
QUERY BLOCK TEXT
****************
select * from wr_test where trade_date = date '2009-02-01'
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=71008 hint_alias="WR_TEST"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEED: 1263 millions instructions/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 8169 #Blks: 100 AvgRowLen: 100.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [0] (NOT ANALYZED)
#Rows: 8169 #Blks: 100 AvgRowLen: 100.00
(NOT ANALYZED)
#Rows: 8169 #Blks: 5 AvgRowLen: 100.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 104.00
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 104.00
Access path analysis for WR_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for WR_TEST[WR_TEST]
*** 2009-06-09 20:44:32.315
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: TEST_PK, blocks=13
** Dynamic sampling index access candidate : TEST_PK
** Dynamic sampling updated table stats.: blocks=5
---PARTITION LEVEL 10053 NO DYNAMIC SAMPLING
ReplyDelete****************
QUERY BLOCK TEXT
****************
select * from wr_test where trade_date = date '2009-02-01'
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=71008 hint_alias="WR_TEST"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEED: 1263 millions instructions/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: WR_TEST Alias: WR_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 1000 #Blks: 10 AvgRowLen: 11.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [0]
#Rows: 1000 #Blks: 10 AvgRowLen: 11.00
#Rows: 1000 #Blks: 5 AvgRowLen: 11.00
Index Stats::
Index: TEST_PK Col#: 2 1 PARTITION [0]
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 104.00
LVLS: 1 #LB: 4 #DK: 1000 LB/K: 1.00 DB/K: 1.00 CLUF: 104.00
Access path analysis for WR_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for WR_TEST[WR_TEST]
Table: WR_TEST Alias: WR_TEST
Card: Original: 1000.000000 Rounded: 17 Computed: 16.95 Non Adjusted: 16.95
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 145607
Resp_io: 3.00 Resp_cpu: 145607
Access Path: index (index (FFS))
Index: TEST_PK
resc_io: 3.00 resc_cpu: 198486
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 3.01 Resp: 3.01 Degree: 1
Cost_io: 3.00 Cost_cpu: 198486
Resp_io: 3.00 Resp_cpu: 198486
Access Path: index (skip-scan)
SS sel: 0.016949 ANDV (#skips): 16.949153
SS io: 16.949153 vs. index scan io: 3.000000
Skip Scan rejected
Access Path: index (IndexOnly)
Index: TEST_PK
resc_io: 2.00 resc_cpu: 17643
ix_sel: 0.016949 ix_sel_with_filters: 0.016949
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: TEST_PK
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 16.95 Bytes: 0
Coskan,
ReplyDeletethanks for bearing with me. So finally, this is a bit puzzling, your 11.1.0.6 trace really looks like the 10.2.0.3 behaviour...
It's different from 10.2.0.4 though, since 10.2.0.4 even with partition level statistics in place screws it up (compare e.g. the "Index Stats" lines, which always show up with zeros in the 10.2.0.4 trace).
Very interesting indeed, I was under the impression that 11.1.0.6 was supposed to behave like 11.1.0.7 regarding this subpartition pruning issue.
Regards,
Randolf
it was a great pleasure, i wish my every day is full of these kind of stuff.
ReplyDeleteI think I can spare my tomorrow for testing other partition related bugs you mentioned before.
Thank you again for your help