Dynamic sampling and set current_schema anomaly

Sometimes when I'm asked to check a particular SQL in a production like environment at first I only get rather limited access, e.g. a read-only user that has the required privileges to perform the SQL statement, but doesn't own the objects.

If I'm now supposed to analyse a rather complex SQL with dozens of table and/or view references I like to use the ALTER SESSION SET CURRENT_SCHEMA command to switch any unqualified object references to the schema set via CURRENT_SCHEMA rather than editing the SQL and add all the object references.

This basically works just fine, but I recently have come across an interesting anomaly in 10.2.0.4: If the optimizer uses dynamic sampling as part of the parsing then it doesn't do so anymore if the CURRENT_SCHEMA has been altered to a non-default schema.

The following simple test case demonstrates the issue in 10.2.0.4 Win32:

SQL>
SQL> drop user dynsamp_test cascade;
drop user dynsamp_test cascade
*
ERROR at line 1:
ORA-01918: user 'DYNSAMP_TEST' does not exist


SQL>
SQL> create user dynsamp_test identified by dynsamp_test;

User created.

SQL>
SQL> grant create session, alter session to dynsamp_test;

Grant succeeded.

SQL>
SQL> drop table dynsamp_test_table purge;
drop table dynsamp_test_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table dynsamp_test_table
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;

Table created.

SQL>
SQL> grant select on dynsamp_test_table to dynsamp_test;

Grant succeeded.

SQL>
SQL> -- this is the plan we get as schema owner
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- It uses obviously dynamic sampling as instructed
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

Note
-----
- dynamic sampling used for this statement

17 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- We get the same from different schema with qualified reference (or valid synonym)
SQL> connect dynsamp_test/dynsamp_test@orcl
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 cbo_test.dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- Still uses dynamic sampling
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 128 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 1 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

Note
-----
- dynamic sampling used for this statement

17 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- Now switch the current_schema
SQL> alter session set current_schema = cbo_test;

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> -- No more dynamic sampling!
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1573551846

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DYNSAMP_TEST_TABLE | 10 | 1280 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='NOT_A_VALID_OWNER_SINCE_TOO_LONG')

13 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> -- Check the 10053 trace file
SQL> -- It will show that the dynamic samping query fails
SQL> -- and therefore no dynamic sampling will be performed
SQL> -- presumably because the current_schema is not obeyed
SQL> -- by the recursively executed query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10053';

Session altered.

SQL>
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> -- Check the 10046 trace file
SQL> -- which confirms a ORA-00942 while parsing the dynamic sampling query
SQL> alter session set tracefile_identifier = 'dynamic_sampling_current_schema_10046';

Session altered.

SQL>
SQL> alter session set sql_trace = true;

Session altered.

SQL>
SQL> explain plan for
2 select /*+ dynamic_sampling(t 4) */
3 *
4 from
5 dynsamp_test_table
6 where
7 owner = 'NOT_A_VALID_OWNER_SINCE_TOO_LONG';

Explained.

SQL>
SQL> alter session set sql_trace = false;

Session altered.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> spool off

So as you can see the simply query uses dynamic sampling as instructed (and in 10.2 would use dynamic sampling by default anyway since the table has no statistics), but when using the CURRENT_SCHEMA trick and then an unqualified reference, the dynamic sampling is not performed and the estimate is based on hardcoded defaults.

Checking the 10053 optimizer trace file confirms that the dynamic sampling is attempted, but the recursive query fails, and the 10046 SQL trace shows that an "ORA-00942 table or view does not exist" error is encountered while parsing the recursive dynamic sampling query.

So be careful when using this (rather unusual I have to admit) setup, the execution plans might be different if you're using a non-default CURRENT_SCHEMA and dynamic sampling is involved.

The issue seems to be fixed in 11.1.0.7 (dynamic sampling is performed as expected when using non-default CURRENT_SCHEMA), however I couldn't identify a corresponding bug on Metalink. If anyone comes across a bug number or document ID this would be helpful.

Locked table statistics and subsequent create index

Just a minor thing to consider: By default in 10g and later index statistics are generated along with an index creation (option COMPUTE STATISTICS in previous releases enabled by default), so a newly created index usually has computed statistics.

10g also introduced the option to lock table statistics.

Now if you lock statistics in 10g in later using DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command. Unfortunately there is no corresponding "FORCE" option in CREATE INDEX available to overwrite that behaviour that I'm aware of so it looks like you're only left with two choices:

1. Use a separate DBMS_STATS.GATHER_INDEX_STATS call with the FORCE=>true option to override the lock on the statistics

2. Temporarily unlock the table statistics before creating the index

The first option can be costly if the index is large, the second option requires additional steps to be taken, and it obviously needs to be ensured that the table statistics are not modified while they are unlocked (e.g. by the default statistics job in 10g and later).

A small testcase run on 10.2.0.4 Win32 follows to demonstrate the issue. I got the same result on 11.1.0.7 Win32.

SQL>
SQL> drop table lock_test purge;

Table dropped.

SQL>
SQL> create table lock_test
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;

Table created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name) compute statistics;
create index lock_test_idx on lock_test (object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked


SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------


SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1


SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> drop index lock_test_idx;

Index dropped.

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> delete from lock_test where rownum <= 500;

500 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter index lock_test_idx rebuild compute statistics;
alter index lock_test_idx rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked


SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
500 21.06.09

SQL>
SQL> analyze index lock_test_idx compute statistics;
analyze index lock_test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked


SQL>
SQL> spool off

The same applies to index rebuilds obviously.

Consequently the hopefully no longer used ANALYZE INDEX ESTIMATE/COMPUTE STATISTICS command can also not be used on locked tables and its indexes.

Temporary Tablespace Groups

Oracle 10g introduced the concept of temporary tablespace groups.

These allow to group multiple temporary tablespaces into a single group and assign a user this group of tablespaces instead of a single temporary tablespace.

This raises some interesting questions, and for some of these I don't find answers in the official documentation. Some of these questions are:

- Can a single workarea execution allocate space from more than one temporary tablespace, e.g. to support large serial sort operations?

A workarea belongs to a single operation of an execution plan. There are several different types of operations that require a workarea, among them are sorts, hash joins, group bys and sort/merge joins.

This workarea can fit into available PGA memory, but can also spill to disk in case there is insufficient memory available to support the operation.

Furthermore this implies that a execution of a single SQL statement can require multiple workareas, e.g. a quite simple statement might need two workareas for two hash joins and a third one for a subsequent sort order by operation.

Note that there are other types of operations that don't require a workarea, e.g. a nested loop join doesn't require a workarea (and therefore will never acquire temporary space).

Details about workareas can be obtained from various dynamic performance views, e.g. V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE and V$SQL_WORKAREA_HISTOGRAM.

- Can multiple workareas of a single session allocate space from different tablespaces?

- According to the documentation different sessions of the same user can use different temporary tablespaces from the group. Is this correct?

- What about parallel execution? The documentation says that the parallel slaves can use different temporary tablespaces. Is this correct?

Here's a simple testcase that generates two small temporary tablespaces and assigns them to the same group. A test user is assigned first a single temporary tablespace and afterwards the group to see if this makes any difference.

It uses a carefully crafted table for which a sort operation doesn't fit into a single small temporary tablespace but is supposed to fit if both temporary tablespaces of the group can be used.

The session is deliberately using a very small sort_area_size in manual workarea policy mode to force the sort operation to spill to disk.

The following are the results from 11.1.0.7 Win32. Similar results can be seen from 10.2.0.4 Win32. The database used a 8KB default block size and a locally managed tablespace with manual segment space management (no ASSM) for the data but that shouldn't matter here much.

SQL>
SQL> drop tablespace temp1_grp including contents and datafiles;

Tablespace dropped.

SQL>
SQL> drop tablespace temp2_grp including contents and datafiles;

Tablespace dropped.

SQL>
SQL> create temporary tablespace temp1_grp
2 tempfile 'C:\ORACLE\ORADATA\ORCL11\TEMP1_GRP.DBF'
3 size 2M reuse uniform size 100K
4 tablespace group temp_grp;

Tablespace created.

SQL>
SQL> create temporary tablespace temp2_grp
2 tempfile 'C:\ORACLE\ORADATA\ORCL11\TEMP2_GRP.DBF'
3 size 2M reuse uniform size 100K
4 tablespace group temp_grp;

Tablespace created.

SQL>
SQL> drop user tempgrp_user cascade;

User dropped.

SQL>
SQL> create user tempgrp_user identified by tempgrp_user;

User created.

SQL>
SQL> grant create session to tempgrp_user;

Grant succeeded.

SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

SQL>
SQL> drop table test_temp_grp purge;

Table dropped.

SQL>
SQL> create table test_temp_grp
2 as
3 select
4 sys.dbms_random.string('U', 140) as object_name
5 from
6 dual
7 connect by
8 rownum <= 10000;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'TEST_TEMP_GRP')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks from dba_tables where table_name = 'TEST_TEMP_GRP';

BLOCKS
----------
205

SQL>
SQL> grant select on test_temp_grp to tempgrp_user;

Grant succeeded.

SQL>
SQL> create or replace synonym tempgrp_user.test_temp_grp for test_temp_grp;

Synonym created.

SQL>
SQL> -- This is the behaviour when using traditional tablespaces
SQL> alter user tempgrp_user temporary tablespace temp1_grp;

User altered.

SQL>
SQL> connect tempgrp_user/tempgrp_user
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> variable r refcursor
SQL>
SQL> variable t refcursor
SQL>
SQL> -- Force sort on disk
SQL> alter session set workarea_size_policy = manual sort_area_size = 65536;

Session altered.

SQL>
SQL> exec open :r for select * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Sort doesn't fit into available temp space
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4


SQL>
SQL> exec open :r for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- This works
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> connect cbo_test/cbo_test
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> -- Switch to tablespace group
SQL> alter user tempgrp_user temporary tablespace temp_grp;

User altered.

SQL>
SQL> connect tempgrp_user/tempgrp_user
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> -- Force sort on disk
SQL> alter session set workarea_size_policy = manual sort_area_size = 65536;

Session altered.

SQL>
SQL> exec open :r for select * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Sort still doesn't fit into available temp space (but it should as you'll see later)
SQL> -- so obviously a single workarea can't use space from different tablespaces
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4


SQL>
SQL> -- What if we have multiple workareas per session
SQL> exec open :r for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- First one works
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> exec open :t for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Second one fails
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :t into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4


SQL>
SQL> -- So it looks like a session is limited to a particular tablespace taken from the group
SQL> -- What happens to a second session of the same user
SQL> -- One tablespace is still in use by cursor R
SQL> -- Check V$SORT_USAGE if interested
SQL>
SQL> -- Run the same query in second session
SQL> -- You might need multiple attempts to get this working
SQL> -- Depending on the tablespace taken from the group
SQL> -- and then close second session to free temp space
SQL> -- Press ENTER to continue
SQL>
SQL> pause

SQL>
SQL> -- This works if the second session is assigned a different tablespace taken from the group
SQL> -- So different sessions of the same user will potentially be assigned to different tablespaces
SQL>
SQL> -- What about parallel execution
SQL> exec close :r

PL/SQL procedure successfully completed.

SQL>
SQL> exec open :r for select /*+ parallel(test_temp_grp, 2) */ * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- This works
SQL> -- So the PX sessions can be assigned to different tablespaces taken from the group
SQL> -- Check V$SORT_USAGE if interested
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> spool off

So in summary the following can be deduced:

1. A single workarea execution can't use space from more a single tablespace even when the user is assigned a temporary tablespace group. Or in other words: A single workarea execution can only allocate a single temporary segment, and segments in general can't span multiple tablespaces in Oracle, i.e. they must fit into a single tablespace.

2. Even multiple workareas for a single session can't use space from the multiple temporary tablespaces available. So this looks like a session attribute, i.e. assigned to the session, and all workareas of that session will use the single temporary tablespace assigned (taken from the group)

3. Multiple sessions of the same user can be assigned to different temporary tablespaces taken from the group.

4. Parallel execution slave sessions can do the same, they can be assigned to different temporary tablespaces taken from the group.

So in order to leverage the power of temporary tablespace groups you need either multiple sessions running serial SQL or the parallel execution option.

Dynamic sampling and partitioned tables

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

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.