One of the reasons why it’s taken a while to get around to the next 10053 trace file post (apart from the more human reasons I talked about here) is that I’d planned to show how 10053 trace files can show whether the CBO has used Global Statistics on Partitioned Tables or not, which is the example that I used the first few times I gave the 10053 presentation.
The last time was at the OUG Scotland conference and I had an interesting conversation with Tony Hasler afterwards that made me both question one of my assumptions about how the CBO works and also realise I could use a 10053 trace file to prove his argument one way or the other. We were discussing which statistics the Cost Based Optimizer uses on a Partitioned Table when there are no Partition-level stats but there are Global stats. For more on Global and Partition-level stats, see here.
I’ve done so much work with stats on partitioned objects in recent years that I was surprised when Tony came up with a strategy that I thought I must have tried and therefore understood, but it turned out that I hadn’t or I had forgotten (just as likely!). The question was around which stats are used when a query is guaranteed to access only one partition. In that situation, the CBO will usually use the Partition-level stats, but what if they don’t exist and there are only Global stats?
Tony was using this as a strategy that I’d never really thought of and still think is a little unusual because in most cases I’d be able to gather or set suitable Partition Stats fairly easily compared to the work required to maintain Global Stats but I was interested in how it would work.
To summarise a few different possibilities here :-
1) Global Stats / Partition Stats / Query accessing more than one Partition – CBO uses Global Stats
2) Global Stats / Partition Stats / Query accessing a single Partition – CBO uses the Partition Stats
3) Global Stats / No Partition Stats / Query accessing more than one Partition – CBO uses Global Stats
4) Global Stats / No Partition Stats / Query accessing a single Partition – ????
I thought the answer to the last case was that Oracle would perform Dynamic Sampling against the single partition being accessed and ignore the Global Stats. Tonys claim was that the CBO actually switches to using the Global Stats that are available and later on sent me an example to show that he was right by setting Global stats to specific values and then running a query which accessed one partition. The estimated cardinalities showed that the CBO was definitely using the Global Stats which are the only ones available.
SQL> set echo on
SQL> CREATE TABLE t1
2 (
3 n1 NUMBER
4 ,d1 DATE
5 )
6 PARTITION BY RANGE
7 (d1)
8 (
9 PARTITION p1 VALUES LESS THAN (DATE '2000-01-01')
10 ,PARTITION pdefault VALUES LESS THAN (maxvalue));
Table created.
SQL> BEGIN
2 DBMS_STATS.set_table_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
3 ,'T1'
4 ,numrows => 1000000
5 ,numblks => 1000000);
6 DBMS_STATS.set_column_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
7 ,'T1'
8 ,colname => 'D1'
9 ,distcnt => 5
10 ,density => 1 / 5);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT *
4 FROM t1
5 WHERE d1 = SYSDATE;
Explained.
SQL> SET LINES 200 PAGES 0
SQL> SELECT * FROM TABLE (DBMS_XPLAN.display);
Plan hash value: 1258445941
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 4296K| 135K (1)| 00:00:08 | | |
| 1 | PARTITION RANGE SINGLE| | 200K| 4296K| 135K (1)| 00:00:08 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | T1 | 200K| 4296K| 135K (1)| 00:00:08 | KEY | KEY |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D1"=SYSDATE@!)
14 rows selected.
SQL> ALTER SESSION SET tracefile_identifier='TONY';
Session altered.
SQL> ALTER SESSION SET events 'trace [SQL_Compiler.*]';
Session altered.
SQL> SELECT *
2 FROM t1
3 WHERE d1 = SYSDATE;
no rows selected
SQL> ALTER SESSION SET events 'trace [SQL_Compiler.*] off';
Session altered.
Although Tony was able to use clear stats and our shared knowledge of how the CBO works to more or less prove his case (because 1,000,000 rows / 5 distinct values = 200,000 estimated cardinality, the beauty of a 10053 trace file is that we can use it to prove what the CBO actually does. By running his same example and generating a 10053 trace, I was able to look at the following section.
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: T1 Alias: T1 (Using composite stats)
The ‘(Using composite stats)’ is the notification that the CBO is looking at Global Stats. For contrast, here are two examples from the presentation. The first shows case 1) above because it accesses more than one subpartition.
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TEST_TAB1 Alias: TEST_TAB1 (Using composite stats) (making adjustments for partition skews) ORIGINAL VALUES:: #Rows: 11 #Blks: 660 AvgRowLen: 18.00 ChainCnt: 0.00 SUBPARTITIONS:: PRUNED: 2 ANALYZED: 2 UNANALYZED: 0 #Rows: 11 #Blks: 61 AvgRowLen: 18.00 ChainCnt: 0.00
… and the second is for case 2), where the query is able to prune to a single partition and therefore uses the Partition Stats. i.e. There is no mention of using Composite Stats.
*************************************** BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TEST_TAB1 Alias: TEST_TAB1 (making adjustments for partition skews) ORIGINAL VALUES:: #Rows: 0 #Blks: 1 AvgRowLen: 0.00 ChainCnt: 0.00 SUBPARTITIONS:: PRUNED: 1 ANALYZED: 1 UNANALYZED: 0 Partition [23] #Rows: 0 #Blks: 1 AvgRowLen: 0.00 ChainCnt: 0.00 #Rows: 0 #Blks: 1 AvgRowLen: 0.00 ChainCnt: 0.00
Note that all of these examples were executed on 11.2.0.3
Thanks to Tony both for correcting my misunderstanding and giving me another example 😉