Archive
Trace Files contents for cardinality estimates on list subpartitioned tables with binds on partition, literals on subpartition
Just a litte addition to the previous post. It seems I cannot get much additional value out of generated tracefiles excpet, that pruning does not seem to take place somehow. This is also reflected in the plans, where you can see partition list single, but no entry for Pstart or Pstop, but instead listed as filter criteria.
(and I am sorry for the layout of the plans, but somehow the spacing gets lost all the time, I neither get code or sourcecode tags working as I would like them ….)
with additional subpartition beeing added (as in test script of previous post):
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTSZ Alias: TESTSZ Partition [0]
#Rows: 30 #Blks: 1 AvgRowLen: 111.00
#Rows: 30 #Blks: 1 AvgRowLen: 111.00
Access path analysis for TESTSZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TESTSZ[TESTSZ]
Table: TESTSZ Alias: TESTSZ
Card: Original: 30.000000 Rounded: 30 Computed: 30.00 Non Adjusted: 30.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 15221
Resp_io: 2.00 Resp_cpu: 15221
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 30.00 Bytes: 0
***************************************
============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
| 2 | PARTITION RANGE SINGLE | | 30 | 300 | 2 | 00:00:01 | KEY | KEY |
| 3 | PARTITION LIST SINGLE | | 30 | 300 | 2 | 00:00:01 | | |
| 4 | TABLE ACCESS FULL | TESTSZ | 30 | 300 | 2 | 00:00:01 | KEY | KEY |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - filter(("I"=:B1 AND "J"=0))
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "SZ"."TESTSZ" "TESTSZ" WHERE "TESTSZ"."I"=:B1 AND "TESTSZ"."J"=1
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTSZ Alias: TESTSZ Partition [0]
#Rows: 30 #Blks: 1 AvgRowLen: 111.00
#Rows: 30 #Blks: 1 AvgRowLen: 111.00
Access path analysis for TESTSZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TESTSZ[TESTSZ]
Table: TESTSZ Alias: TESTSZ
Card: Original: 30.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 13771
Resp_io: 2.00 Resp_cpu: 13771
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 10 | 2 | 00:00:01 | KEY | KEY |
| 3 | PARTITION LIST SINGLE | | 1 | 10 | 2 | 00:00:01 | | |
| 4 | TABLE ACCESS FULL | TESTSZ | 1 | 10 | 2 | 00:00:01 | KEY | KEY |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - filter(("J"=1 AND "I"=:B1))
without added subpartition (add subpartition in testcript commented out):
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTSZ Alias: TESTSZ (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 30 #Blks: 1 AvgRowLen: 111.00
SUBPARTITIONS::
PRUNED: 3
ANALYZED: 3 UNANALYZED: 0
Partition [0]
#Rows: 30 #Blks: 1 AvgRowLen: 111.00
#Rows: 30 #Blks: 3 AvgRowLen: 111.00
Access path analysis for TESTSZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TESTSZ[TESTSZ]
Table: TESTSZ Alias: TESTSZ
Card: Original: 30.000000 Rounded: 30 Computed: 30.00 Non Adjusted: 30.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 23474
Resp_io: 3.00 Resp_cpu: 23474
Best:: AccessPath: TableScan
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 30.00 Bytes: 0
***************************************
============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
| 2 | PARTITION RANGE SINGLE | | 30 | 300 | 3 | 00:00:01 | KEY | KEY |
| 3 | PARTITION LIST SINGLE | | 30 | 300 | 3 | 00:00:01 | 1 | 1 |
| 4 | TABLE ACCESS FULL | TESTSZ | 30 | 300 | 3 | 00:00:01 | KEY | KEY |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - filter("I"=:B1)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTSZ Alias: TESTSZ (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 90 #Blks: 2 AvgRowLen: 112.00
SUBPARTITIONS::
PRUNED: 3
ANALYZED: 3 UNANALYZED: 0
Partition [1]
#Rows: 90 #Blks: 2 AvgRowLen: 112.00
#Rows: 90 #Blks: 5 AvgRowLen: 112.00
Access path analysis for TESTSZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TESTSZ[TESTSZ]
Table: TESTSZ Alias: TESTSZ
Card: Original: 90.000000 Rounded: 90 Computed: 90.00 Non Adjusted: 90.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 45817
Resp_io: 3.00 Resp_cpu: 45817
Best:: AccessPath: TableScan
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 90.00 Bytes: 0
***************************************
============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE SINGLE | | 90 | 990 | 3 | 00:00:01 | KEY | KEY |
| 3 | PARTITION LIST SINGLE | | 90 | 990 | 3 | 00:00:01 | 2 | 2 |
| 4 | TABLE ACCESS FULL | TESTSZ | 90 | 990 | 3 | 00:00:01 | KEY | KEY |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - filter("I"=:B1)
Oracle Episodes 