Friday, June 27, 2008

Cost-Based Oracle - Fundamentals: Test cases applied against 10.2.0.4 and 11.1.0.6 (Part 1)

I'm starting here a loose series of applying the test cases used in the book "Cost-Based Oracle - Fundamentals" by Jonathan Lewis to 10.2.0.4 and 11.1.0.6.

The book covers 8i (8.1.7.4), 9iR2 (9.2.0.6) and 10gR1 (10.1.0.4), so I was keen on running the same tests on 10gR2 and 11gR1.

In the course of doing this I'll attempt to point out if there are any results that are noteworthy.

Before I begin here are the preparation steps and configuration details required to reproduce the test cases.

The INIT.ORA parameters relevant to the optimizer used for 10.2.0.4 and 11.1.0.6 have been taken from the sample INIT.ORAs provided as part of test case code depot. The INIT.ORAs used look like the following:

#10.2.0.4
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_16k_cache_size=8388608
*.db_2k_cache_size=8388608
*.db_4k_cache_size=8388608
*.db_cache_size=67108864
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.java_pool_size=8388608
*.large_pool_size=8388608
*.pga_aggregate_target=209715200
*.shared_pool_size=134217728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
*.db_file_multiblock_read_count=8
*.db_keep_cache_size=8388608
*.parallel_max_servers=40
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.db_recycle_cache_size=8388608
*.query_rewrite_enabled='false'
*.sql_trace=FALSE
*.global_names=FALSE

#11.1.0.6
*.audit_file_dest='C:\oracle\admin\orcl11\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='C:\oracle\oradata\orcl11\control01.ctl','C:\oracle\oradata\orcl11\control02.ctl','C:\oracle\oradata\orcl11\control03.ctl'
*.db_16k_cache_size=8388608
*.db_2k_cache_size=8388608
*.db_4k_cache_size=8388608
*.db_cache_size=67108864
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl11'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11XDB)'
*.java_pool_size=8388608
*.large_pool_size=8388608
*.pga_aggregate_target=327155712
*.shared_pool_size=134217728
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_multiblock_read_count=8
*.db_keep_cache_size=8388608
*.parallel_max_servers=40
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.db_recycle_cache_size=8388608
*.query_rewrite_enabled='false'
*.sql_trace=FALSE
*.global_names=FALSE

The required tablespaces have also been created as provided:

create tablespace test_2k
blocksize 2K
datafile '_your_path_goes_here\test_2k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_4k
blocksize 4K
datafile '_your_path_goes_here\test_4k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_8k
blocksize 8K
datafile '_your_path_goes_here\test_8k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_8k_assm
blocksize 8K
datafile '_your_path_goes_here\test_8k_assm.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management auto
;

create tablespace test_16k
blocksize 16K
datafile '_your_path_goes_here\test_16k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

An appropriate user has also been created that will host the test case objects:

create user cbo_test identified by cbo_test;

grant dba to cbo_test;


alter user cbo_test default tablespace test_8k;

I'm starting off with the test cases provided as part of the introduction and chapter one - "What do you mean by cost?".

These two chapters use only three scripts, and here are the results when applying them to 10.2.0.4 and 11.1.0.6.

The first script comes from the preface, is called "in_list.sql" and shows an error of the 8i optimizer that has been fixed from 9i on.

rem
remִScript:ִִִin_list.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִSeptִ2003
remִPurpose:ִִDemonstrationִscriptִforִCostִBasedִOracle.
rem
remִVersionsִtested
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִNotes:
remִTheִ"direִwarning".
remִAnִupgradeִfromִ8ִtoִ9ִchangesִtheִin-listִcardinality
rem
remִWeִhaveִaִtableִwhereִeveryִvalueִforִcolumnִN1ִreturns
remִ100ִrows.ִUnderִ9iִandִ10g,ִaִlistִofִtwoִvaluesִproduces
remִaִcardinalityִofִ200ִrows.ִUnderִ8i,ִtheִestimatedִcardinality
remִisִonlyִ190ִrows.ִThisִisִanִerrorִinִtheִoptimizerִcode.
rem
remִTheִin-listִisִconvertedִtoִanִ'OR'ִlist
remִִִn1ִ=ִ1ִORִn1ִ=ִ2
rem
remִUnfortunately,ִ8iִthenִtreatsִtheִtwoִpredicatesִasִindependent,
remִsoִtheִcalculatedִcardinalityִis
remִִִestimateִofִrowsִwhereִn1ִ=ִ1ִ(oneִinִ10ִ=ִ100)ִplus
remִִִestimateִofִrowsִwhereִn1ִ=ִ2ִ(oneִinִ10ִ=ִ100)ִminus
remִִִestimateִofִrowsִwhereִ'n1ִ=ִ1ִandִn1ִ=ִ2'ִ...ִoneִinִ100ִ=ִ10.
rem
remִSeeִtheִChapterִ3ִ"BasicִSelectivity"ִforִmoreִdetails
rem

startִsetenv

dropִtableִt1;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1
as
select
ִִtrunc((rownum-1)/100)ִn1,
ִִrpad('x',100)ִִִpadding
from
ִִall_objects
where
ִִrownumִ<=ִ1000
;


begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

setִautotraceִtraceonlyִexplain


spoolִin_list

select
ִִ*
fromִִt1
where
ִִn1ִinִ(1,2)
;


setִautotraceִoff

spoolִoff


setִdocִoff
doc

Underִ8i,ִtheִcardinalityִofִanִin-listִisִtooִlow.

ExecutionִPlanִ(8.1.7.4ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=3ִCard=190ִBytes=19570)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(Cost=3ִCard=190ִBytes=19570)


ExecutionִPlanִ(9.2.0.6ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=4ִCard=200ִBytes=20600)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(Cost=4ִCard=200ִBytes=20600)


ExecutionִPlanִ(10.1.0.4ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=4ִCard=200ִBytes=20600)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(TABLE)ִ(Cost=4ִCard=200ִBytes=20600)


#

Here is the result of 10.2.0.4:

Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3617692013

----------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִ|ִִִִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
|*ִִ1ִ|ִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
----------------------------------------------------------

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

ִִִ1ִ-ִfilter("N1"=1ִORִ"N1"=2)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

And we get exactly the same from 11.1.0.6:

Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3617692013

----------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִ|ִִִִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
|*ִִ1ִ|ִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
----------------------------------------------------------

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

ִִִ1ִ-ִfilter("N1"=1ִORִ"N1"=2)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

So no surprises here, works as expected, and the results correspond to those of 9iR2 and 10gR1.

The next script comes from chapter 1 ("What do you mean by cost?") and is called "first_rows.sql". It shows potential problems that may arise from the approach the optimizer takes when using the (deprecated) FIRST_ROWS optimizer mode.

rem
remִScript:ִִִfirst_rows.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִJuneִ2002
remִPurpose:ִִProblemsִwithִFIRST_ROWSִoptimisation
rem Adapted to 10g and 11g by Randolf Geist
rem
remִLastִtestedִ
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִTheִFIRST_ROWSִoptimizerִdoesִnotִhaveִaִsensibleִstrategy
remִforִdealingִwithִoptimisationִofִanִORDERִBYִ(andִpossibly
remִotherִvariantsִsuchִasִGROUPִBY)ִwhenִitִfindsִitִcanִuse
remִanִindexִtoִdoִtheִORDERִBYִ"freeִofִcharge".
rem
remִThisִexampleִdemonstratesִtheִpoint.ִWithoutִtheִin-line
remִview,ִOracleִdoesִaִfullִscanִonִtheִprimaryִkeyִindexִto
remִreturnִtheִ100ִrequiredִrowsִinִorder,ִatִaִtotalִcost
remִandִtotalִexecutionִtimeִthatִisִmuchִhigherִthanִthatִof
remִusingִtheִrequiredִindex.
rem
remִOfִcourse,ִitִisִarguablyִtheִcaseִthatִthisִisִcorrect
remִbehaviourִifִweִassumeִthatִtheִtimeִtoִtheִfirstִrowִis
remִimportant,ִandִthereforeִweִavoidִcollectingִaִlargeִnumber
remִofִrowsִandִsortingִthem.ִִInִpractice,ִthisִisִperhapsִnot
remִreallyִlikelyִtoִbeִtheִcase.
rem
remִBugִnumberִ670328ִapplies
rem
remִParameterִ_sort_elimination_cost_ratioִaffectsִtheִbreak
remִpointִbetweenִoptimisingִforִtheִWHEREִclauseִandִoptimising
remִforִtheִORDERִBYִclause.
rem
remִIfִtheִparameterִisִzero,ִtheִORDERִBYִclauseִtakesִprecedence
remִIfִtheִparameterִisִnon-zero,ִthenִtheִindexִisִusedִifִtheִcostִ
remִofִusingִitִisִlessִthanִtheִcostִofִdoingִtheִsortִmultipliedִ
remִbyִtheִvalueִofִtheִparameter.
rem
remִSpecialִnoteִforִ10g:
remִTheִparameterִstillִhasִtheִsameִeffectִinִgeneral,ִbutִzero
remִnowִmeansִzero,ִnotִ(effectively)ִinfinity.ִִTheִdefaultִof
remִzeroִwillִnowִeffectivelyִignoreִtheִindexִoptionִunlessִit
remִisִactuallyִcheaperִthanִtheִnon-indexִoption.ִAִnon-zero
remִvalueִwillִbehaveִasִitִalwaysִusedִto
rem

startִsetenv

dropִtableִt1ִcascadeִconstraints;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִִִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1ִas
select
ִִrownumִִִִִִid,
--ִִmod(rownum,100)ִִִmodded,
ִִmod(rownum,300)ִִִmodded,
ִִlpad(rownum,1000)ִpadding
from
ִִall_objects
where
ִִrownumִ<=ִ10000
;

alterִtableִt1ִaddִconstraintִt1_pkִprimaryִkey(id);
createִindexִt1_modִonִt1(modded);

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

alterִsessionִsetִoptimizer_mode=first_rows;

setִautotraceִtraceonlyִexplain

spoolִfirst_rows

prompt
promptִִGetִaִbase-lineִcostִandִplanִforִacquiringִtheִrows
prompt

selectִִ*
fromִִt1
whereִmoddedִ=ִ0
;

prompt
promptִִSeeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.
prompt

selectִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

rem
remִBlockִtheִPKִindexִfromִbeingִusedִforִordering,ִandִsee
remִthatִOracleִcouldִnotionallyִgetִaִbetterִpath.ִStrangely
remִtheִcostִvariesִdependingִonִtheִstrategyִusedִforִblocking
remִtheִindex.ִOnִmyִ9.2.0.6ִtest,ִtheִno_mergeִhintִmanagedִto
remִreduceִtheִlengthsִofִtheִrowsִtoִbeִsorted,ִandִtherefore
remִcalculatedִaִsmallerִcost.
rem

prompt
promptִִBlockִtheִindexִwithִaִno_mergeִhint
prompt

selectִ*ִfromִ(
selectִִ/*+ִno_mergeִ*/ִ
ִִ*
fromִִt1
whereִmoddedִ=ִ0
)
orderִbyִ
ִִid
;


prompt
promptִִBlockִtheִindexִwithִaִno_indexִhint
prompt

selectִִ/*+ִno_index(t1,t1_pk)ִ*/
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

setִautotraceִoff

rem
remִTheִcostsִunderִ10gִare:
remִִִUsingִtheִPKִindexִtoִavoidִtheִsort:ִִִִִ1,450
remִִִBlockִtheִPKִindexִ(no_index)ִandִsorting:ִִ59
rem
remִCostִratio:ִ1450/59ִ=ִ24.58,ִsoִweִtestִ
remִִִ_sort_elimination_cost_ratioִatִ24ִandִ25
rem
remִAtִ24:ִִ59ִ*ִ24ִ=ִ1,416:ִִsoִtheִPKִnosortִshouldִbeִignored
remִAtִ25:ִִ59ִ*ִ25ִ=ִ1,475:ִִsoִtheִPKִnosortִfallsִinsideִtheִlimit.
rem

setִautotraceִtraceonlyִexplainִ

alterִsessionִsetִ"_sort_elimination_cost_ratio"ִ=ִ25;

prompt
promptִִCostִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted
prompt

select
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

alterִsessionִsetִ"_sort_elimination_cost_ratio"ִ=ִ24;

prompt
promptִִCostִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive
prompt

select
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

setִautotraceִoff

spoolִoff

Here is the result when running in on 10.2.0.4:


Getִaִbase-lineִcostִandִplanִforִacquiringִtheִrows


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3052882459

----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|ִִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ2ִ|ִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
----------------------------------------------------------------------

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

ִִִ2ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Seeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_mergeִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ1453079715

------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ35ִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ4ִ|ִִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
------------------------------------------------------------------------

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

ִִִ4ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_indexִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

We get the same from 11.1.0.6:


Getִaִbase-lineִcostִandִplanִforִacquiringִtheִrows


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3052882459

----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|ִִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ2ִ|ִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
----------------------------------------------------------------------

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

ִִִ2ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Seeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_mergeִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ1453079715

------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ35ִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ4ִ|ִִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
------------------------------------------------------------------------

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

ִִִ4ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_indexִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

The results in general correspond to those of the previous versions, but one interesting oddity showed up when I did the first test runs. The cost reported by my 10gR2 and 11gR1 systems for the query variations performing an ORDER BY was significantly higher than what is shown in the results that have been provided by Jonathan as part of the code depot.

It looks like the difference comes from the higher cost estimated for the ORDER BY sort. Further investigations showed that Jonathan probably performed the test runs using manual workarea_size_policy with 1M sort_area_size whereas I did the first test runs using the instance default setting of an pga_aggregate_target of 200M (see INIT.ORA parameters above).

The odd thing is that when turning CPU costing off ("_optimizer_cost_model"="io") then for that particular test case performing that tiny sort operation the cost of the sort is actually increasing when the amount of memory available for sorting is increasing. That means in other words, the higher the amount memory available the higher the cost of the small sort operation, due to the fact that the "IO cost per pass" in the optimizer trace file (10053 event) is higher if more memory is available and therefore the final IO cost of the sort is, too.

I was able to reproduce this strange behaviour in 9.2.0.8, 10.2.0.4 and 11.1.0.6. Due to the higher total cost when running the test case with more memory available I had to adjust the "_sort_elimination_cost_ratio" parameter accordingly used in the "first_rows.sql" example to get the same results. See the script for further explanation of this parameter.

All other parameters that I tried (using manual/automatic pga sizing, "all_rows" optimizer_mode instead of "first_rows") didn't change the outcome. When I was using "io" cost model I got in this particular case a consistent increase in I/O cost per pass when increasing the memory available for sorting.

I won't put further effort on this issue for the time being as there is a whole chapter (chapter 13) coming up later that covers sorting related issues in depth, so I'll see if I'm able to dig further there.

The final script from chapter 1 is called "view_merge_01.sql" and demonstrates the complex view merging transformation that can happen to your statement as part of the optimisation steps.

rem
remִScript:ִִִview_merge_01.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִSeptemberִ2002
remִPurpose:ִִDemonstrateִcomplexִviewִmerging.
rem
remִLastִtestedִ
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִThisִshowsִtheִchangeִinִexecutionִpathִavailableִwhenִOracle
remִisִallowedִtoִmergeִpredicatesִintoִ'complex'ִviews.
rem
remִTheִmanualִ(9.2ִPerfִGuideִp.2-37)ִpointsִoutִthatִ
remִwhenִaִviewִcontainsִoneִofִtheִfollowingִstructures,ִ
remִitִcanִbeִmergedִintoִaִreferencingִqueryִblockִonlyִifִ
remִcomplexִviewִmergingִisִenabled:
remִִִAִGROUPִBYִclause
remִִִAִDISTINCTִoperatorִinִtheִselectִlist
rem
remִInִthisִexampleִwithִ9.2ִweִsee:
remִִִWithִ_complex_view_mergingִ=ִtrue,ִִweִjoinִthenִgroupִby
remִִִWithִ_complex_view_mergingִ=ִfalse,ִweִgroupִbyִthenִjoin
rem
remִTheִdefaultִforִOracleִ8ִisִFALSE
remִTheִdefaultִforִOracleִ9ִisִTRUE
remִTheִdefaultִforִOracleִ10ִisִTRUE,ִbutִOracleִ10ִworks
remִoutִtheִcostִofִtheִtwoִdifferentִapproachesִandִwill
remִchooseִtheִcheaperִoption.ִ(Runִwithִ10053ִtraceִtoִsee
remִtheִdifferentִoptionsִthatִappearִinִtheִtraceִfiles).
rem

startִsetenv

dropִtableִt2;
dropִtableִt1;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִִִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1ִ(
ִִid_parִִִִnumber(6)ִnotִnull,
ִִvc1ִִִvarchar2(32)ִִnotִnull,
ִִvc2ִִִvarchar2(32)ִִnotִnull,
ִִpaddingִִִvarchar2(100)
);

alterִtableִt1ִaddִconstraintִt1_pkִprimaryִkeyִ(id_par);

createִtableִt2ִ(
ִִid_chִִִnumber(6)ִnotִnull,
ִִid_parִִִִnumber(6)ִnotִnull,
ִִvalִִִnumber(6,2),
ִִpaddingִִִvarchar2(100)
);

alterִtableִt2ִaddִconstraintִt2_pkִprimaryִkeyִ(id_ch);
alterִtableִt2ִaddִconstraintִt2_fk_t1ִforeignִkeyִ(id_par)ִreferencesִt1;

insertִintoִt1
selectִ
ִִrownum,
ִִvc1,
ִִvc2,
ִִrpad('x',100)
from
ִִ(
ִִִִselectִ
ִִִִִִlpad(trunc(sqrt(rownum)),32)ִִvc1,
ִִִִִִlpad(rownum,32)ִִִִִvc2
ִִִִfromִall_objects
ִִִִwhereִrownumִ<=ִ32
ִִ)
;

commit;

insertִintoִt2
select
ִִrownum,
ִִd1.id_par,
ִִrownum,
ִִrpad('x',100)
from
ִִt1ִִd1,
ִִt1ִִd2
;

commit;

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't2',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

createִorִreplaceִviewִavg_val_viewִAS
selectִ
ִִid_par,ִavg(val)ִavg_val_t1ִ
fromִִt2
groupִby
ִִid_par;


spoolִview_merge_01

setִautotraceִtraceonlyִexplain

prompt
promptִִBaselineִexample
promptִִDefaultִvalueִforִ_complex_view_merging
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;

alterִsessionִsetִ"_complex_view_merging"=true;

prompt
promptִִComplexִviewִmergingִenabled
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;

prompt
promptִִComplexִviewִmergingִenabled
promptִִno_mergeִhintִapplied
prompt

select
ִִ/*+ִno_mergeִ(avg_val_view)ִ*/
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


alterִsessionִsetִ"_complex_view_merging"=false;

prompt
promptִִComplexִviewִmergingִdisabled
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


prompt
promptִִComplexִviewִmergingִdisabled
promptִִQueryִhintedִtoִmergeִ-ִdoesn'tִwork
prompt

select
ִִ/*+ִmerge(avg_val_view)ִ*/
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


setִautotraceִoff

alterִsessionִsetִ"_complex_view_merging"=true;

spoolִoff

Here is the result from 10.2.0.4:

Baselineִexample
Defaultִvalueִforִ_complex_view_merging
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִenabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִenabled
no_mergeִhintִapplied


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִdisabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִdisabled
Queryִhintedִtoִmergeִ-ִdoesn'tִwork


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.

And we get the same from 11.1.0.6:

Baselineִexample
Defaultִvalueִforִ_complex_view_merging
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִenabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִenabled
no_mergeִhintִapplied


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִdisabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִdisabled
Queryִhintedִtoִmergeִ-ִdoesn'tִwork


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.

The results correspond to those from the previous versions.

Conclusion for part 1: For first three scripts I couldn't detect any significant differences to the previous versions. A small oddity showed up regarding the I/O cost of a tiny sort operation when disabling the cpu_costing, but that could also be reproduced in 9.2.0.8, so that's not an actual difference to previous versions.

Because I have to use this silly dot character instead of space in order to keep a reasonable formatting in this blogger environment, I'll provide an updated code depot that contains the scripts and results I used for this series.

You can download it from here: My homepage (SQLTools++, an open source lightweight SQL Oracle GUI for Windows)

I'll update it from time to time when I do significant changes, and each time I add a new part to the series you'll find the updated code depot there as well.

The original code depot (still maintained by Jonathan) can be found here.

Sunday, June 1, 2008

Exchange partition and aggregated statistics - 10g, 11g documentation bugs

Aggregated statistics on partition or global level are by definition of worse quality than actually gathered statistics, mainly because you can not derive each and every statistic by aggregation (e.g. number of distinct column values), nevertheless there might be applications where they are sufficient and maintaining them is far less resource intensive than actually gathering them.

Aggregated statistics are only maintained when calling DBMS_STATS.GATHER_*_STATS if two conditions are met: For all underlying (sub-)partition objects statistics are available (either gathered or aggregated themselves in case of subpartitioning) and there are no actual statistics gathered on the upper level. In case actual statistics have been gathered, these take always precedence over aggregated statistics which means they are never overwritten by aggregation. You can distinguish between aggregated and gathered statistics by checking the column "GLOBAL_STATS" e.g. in the *_STATISTICS dictionary views. If it says "YES", the statistics are gathered and won't be overwritten by aggregation. In case of "NO" the statistics are based on aggregation and can be updated by aggregating the underlying statistics (provided that for all underlying objects statistics are available).

Up to 10gR2 the documentation of ALTER TABLE EXCHANGE PARTITION mentiones that "All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. Oracle Database recalculates the aggregate statistics of the table receiving the new partition."

This is only correct for the first part, the second part regarding the automatic aggregation of statistics is a documentation bug. In the beginning of partitioning support with Oracle8 according to Oracle this was true but due to customer complaints that this automatic re-aggregation took a significant amount of time (probably in case there were numerous partitions or subpartitions) it has been removed from the default code. There is a (more or less) documented (internal) parameter "_MINIMAL_STATS_AGGREGATION" that has been introduced in Oracle 8i to control the aggregation of statistics. According to the documentation available the following applies: "If the parameter is FALSE, the aggregate statistics will be recomputed. If the parameter is TRUE, the statistics will not be recomputed.". See here the documentation. So you have to be a bit cautious when using this parameter as the setting is counter-intuitive. You have to set it to the non-default value of FALSE to activate the aggregation when exchanging partitions.

While coming across this issue a documentation bug has been raised with Oracle that should correct the last part of the first quoted paragraph regarding the default aggregation rule (so that the documentation correctly mentions that no aggregation is performed by default).

Now looking at the latest available documentation of 11gR1, the particular paragraph reads like this:

"Statistics and histograms on the table or partition are not exchanged. Use the DBMS_STATS package to reaggregate statistics or create a histogram for the table receiving the new partition."

This is again quite interesting as now both parts have been modified. It says now not only that no aggregation takes place but the statistics are not exchanged at all. This would mean a significant change in functionality in 11gR1 compared to pre-11 versions.

I think it is a good idea at this point to come up with a simple test case to check what the actual behaviour is in the current releases 10gR2 Patch Set 3 (10.2.0.4) and 11gR1 (11.1.0.6).

Test case applied against 10.2.0.4:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table exchange_test;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- The exchange table now holds twice the data of subpartition pkey_1_101
SQL>
SQL> -- Now we get rid of global stats and use instead aggregated statistics on partition and global level
SQL> -- Otherwise exchanging subpartitions will never affect the partition or global level stats
SQL> -- Gathered statistics on partition or global level take precedence over aggregated statistics, check the column GLOBAL_STATS
SQL> -- If it shows YES, then the statistics have been gathered, if it shows NO then these are aggregated statistics
SQL> -- Aggregated stats are created/updated whenever all partitions/subpartitions of the lower level have statistics
SQL> -- and one of the lower level partitions/subpartitions are analyzed AND the parent level does not have global stats
SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_0', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_1', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_0_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_1_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'exchange_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Now perform an exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have not been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Undo the exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Now enable aggregated stats update
SQL> alter session set "_minimal_stats_aggregation" = false;

Session altered.

SQL>
SQL> -- And do the same again
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Finally revert to the previous state
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- This is were we started from
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Back to default value
SQL> alter session set "_minimal_stats_aggregation" = true;

Session altered.

SQL>
SQL> spool off

Same test case applied against 11.1.0.6:

SQL>
SQL> drop table partition_test;

Table dropped.

SQL>
SQL> create table partition_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_0 values less than (1)
10 (
11 subpartition pkey_0_xxx values (' xxx '),
12 subpartition pkey_0_001 values ('001')
13 ),
14 partition pkey_1 values less than (2)
15 (
16 subpartition pkey_1_xxx values (' xxx '),
17 subpartition pkey_1_001 values ('001'),
18 subpartition pkey_1_101 values ('101')
19 )
20 );

Table created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '001', seq
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> insert into partition_test (x_pkey, x_slice, data1)
2 select 1, '101', seq
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(ownname=>USER,tabname=>'PARTITION_TEST',granularity=>'ALL'); end;
2 /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table exchange_test;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> insert into exchange_test
2 select * from partition_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- The exchange table now holds twice the data of subpartition pkey_1_101
SQL>
SQL> -- Now we get rid of global stats and use instead aggregated statistics on partition and global level
SQL> -- Otherwise exchanging subpartitions will never affect the partition or global level stats
SQL> -- Gathered statistics on partition or global level take precedence over aggregated statistics, check the column GLOBAL_STATS
SQL> -- If it shows YES, then the statistics have been gathered, if it shows NO then these are aggregated statistics
SQL> -- Aggregated stats are created/updated whenever all partitions/subpartitions of the lower level have statistics
SQL> -- and one of the lower level partitions/subpartitions are analyzed AND the parent level does not have global stats
SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_0', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.delete_table_stats(user, 'partition_test', partname=>'pkey_1', cascade_parts=>false)

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_0_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'partition_test', partname=>'pkey_1_xxx', granularity=>'subpartition')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'exchange_test')

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Now perform an exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have not been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Undo the exchange
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Now enable aggregated stats update
SQL> alter session set "_minimal_stats_aggregation" = false;

Session altered.

SQL>
SQL> -- And do the same again
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> -- Statistics have been exchanged for the subpartition affected, aggregated stats have been updated
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

SQL>
SQL> -- Finally revert to the previous state
SQL> alter table partition_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- This is were we started from
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'PARTITION_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
PARTITION_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ1010
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_0ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_0_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_XXXִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ0
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_001ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ10
PARTITION_TESTִִִִִִִִִִִִִִִִִPKEY_1ִִִִִִִִִִִִִִִִִִִִִִִִִPKEY_1_101ִִִִִִִִִִִִִִִִִִִִִִִִִִִ1000

8 rows selected.

SQL>
SQL> select table_name, partition_name, subpartition_name, num_rows from user_tab_statistics where table_name = 'EXCHANGE_TEST';

TABLE_NAMEִִִִִִִִִִִִִִִִִִִִִPARTITION_NAMEִִִִִִִִִִִִִִִִִSUBPARTITION_NAMEִִִִִִִִִִִִִִִִNUM_ROWS
------------------------------ִ------------------------------ִ------------------------------ִ----------
EXCHANGE_TESTִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ2000

SQL>
SQL> -- Back to default value
SQL> alter session set "_minimal_stats_aggregation" = true;

Session altered.

SQL>
SQL> spool off


This simple test shows that the documentation is unfortunately still or again wrong since it is obvious that 11g still behaves like 10gR2 including the option to aggregate the statistics when setting the "_minimal_stats_aggregation" parameter.

So in my opinion the mentioned paragraph in the official documentation could be written like the following:
"All statistics of the table and partition are exchanged, including table, column, index statistics, and histograms. Oracle Database does not recalculate any aggregated statistics of the table receiving the new partition by default. If you want to have this aggregation to happen automatically each time you exchange a partition, set the parameter '_minimal_stats_aggregation' to FALSE. The default setting of the parameter is TRUE which means that it prevents the aggregation from happening."