Statistics on Partitioned Tables - Part 5

Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some systems, the likelihood is that in a near-real-time DW you will be using partition exchange at some point. Which means we need to understand how the stats might be gathered and then aggregated up to the partition and table-level stats.

Although there might be other approaches, I'd say that there are two distinct approaches you are likely to use.

1) Create a temporary load table, load it with data, gather statistics on it and then exchange it with the relevant subpartition in the real table.

2) Create a temporary load table, load it with data, exchange it with the relevant subpartition and then gather stats on the subpartition.

Pete Scott left a comment on a previous post stating that he rarely uses approach 1 so no doubt he'll leave another comment here expanding on his reasons What I want to show you is what happens if you do use approach 1 and introduce the _minimal_stats_aggregation hidden parameter that's been kicking around since Oracle 8i. The default setting of the parameter is TRUE, which means that Oracle minimises automatic stats aggregation activity. Let's see that in action.

First of all I'll recreate TEST_TAB1 as it was at the start of the series and add a new partition (and, by implication, the related subpartitions) and create a seperate table that I'll load the data into.

SQL> ALTER TABLE TEST_TAB1
  2  ADD  PARTITION P_20100209 VALUES LESS THAN (20100210);

Table altered.

SQL> DROP TABLE LOAD_TAB1;

Table dropped.

SQL> CREATE TABLE LOAD_TAB1
  2  AS SELECT * FROM TEST_TAB1 WHERE 1=0;

Table created.

SQL> CREATE UNIQUE INDEX LOAD_TAB1_IX1 ON LOAD_TAB1
  2  (REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID)
  3  NOPARALLEL COMPRESS 1;

Index created.

Now I'll use LOAD_TAB1 to repeat the same process for the four different subpartitions - INSERT data into LOAD_TAB1, gather stats on it and then exchange it with the relevant subpartition of TEST_TAB1.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z');

1 row created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_GROT WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION  P_20100209_GROT REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> TRUNCATE TABLE LOAD_TAB1;

Table truncated.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U');

1 row created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION  P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> TRUNCATE TABLE LOAD_TAB1;

Table truncated.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N');

1 row created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_HALO WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION  P_20100209_HALO REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> TRUNCATE TABLE LOAD_TAB1;

Table truncated.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P');

1 row created.

SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z');

1 row created.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_OTHERS WITH TABLE load_tab1;

Table altered.

SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION  P_20100209_OTHERS REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

All of the P_20100209 subpartitions have stats that were swapped in as part of the partition exchange operation so hopefully there'll be some aggregated global statistics.

SQL> select  table_name, global_stats, last_analyzed, num_rows
  2  from dba_tables
  3  where table_name='TEST_TAB1'
  4  and owner='TESTUSER'
  5  order by 1, 2, 4 desc nulls last;

TABLE_NAME                     GLO LAST_ANALYZED          NUM_ROWS                                              
------------------------------ --- -------------------- ----------                                              
TEST_TAB1                      NO                                                                               

SQL> select  table_name, partition_name, global_stats, last_analyzed, num_rows
  2  from dba_tab_partitions
  3  where table_name='TEST_TAB1'
  4  and table_owner='TESTUSER'
  5  order by 1, 2, 4 desc nulls last;

TABLE_NAME                     PARTITION_NAME                 GLO LAST_ANALYZED          NUM_ROWS               
------------------------------ ------------------------------ --- -------------------- ----------               
TEST_TAB1                      P_20100131                     NO                                                
TEST_TAB1                      P_20100201                     NO                                                
TEST_TAB1                      P_20100202                     NO                                                
TEST_TAB1                      P_20100203                     NO                                                
TEST_TAB1                      P_20100204                     NO                                                
TEST_TAB1                      P_20100205                     NO                                                
TEST_TAB1                      P_20100206                     NO                                              
TEST_TAB1                      P_20100207                     NO                                                  
TEST_TAB1                      P_20100209                     NO                                                   

9 rows selected.

Oh, well, that doesn't seem to have worked. Maybe the LOAD_TAB1 stats weren't gathered correctly or didn't appear as part of the subpartition exchange operation?

SQL> select  table_name, subpartition_name, global_stats, last_analyzed, num_rows
  2  from dba_tab_subpartitions
  3  where table_name='TEST_TAB1'
  4  and table_owner='TESTUSER'
  5  order by 1, 2, 4 desc nulls last;

TABLE_NAME                     SUBPARTITION_NAME              GLO LAST_ANALYZED          NUM_ROWS                                   
------------------------------ ------------------------------ --- -------------------- ----------                                    
TEST_TAB1                      P_20100131_GROT                NO                                                         
TEST_TAB1                      P_20100131_HALO                NO                                                                    
TEST_TAB1                      P_20100131_JUNE                NO                                                        
TEST_TAB1                      P_20100131_OTHERS              NO                                                

<<output snipped>>

TEST_TAB1                      P_20100209_GROT                NO  28-FEB-2010 21:41:47          3                
TEST_TAB1                      P_20100209_HALO                NO  28-FEB-2010 21:41:49          3                
TEST_TAB1                      P_20100209_JUNE                NO  28-FEB-2010 21:41:49          3                
TEST_TAB1                      P_20100209_OTHERS              NO  28-FEB-2010 21:41:50          3                

36 rows selected.

The subpartition stats are ok, then, but the aggregation process hasn't happened and that's because _miminal_stats_aggregation is set to TRUE (the default) which instructs Oracle to minimise aggregation operations and one of the ways it does so is to not aggregate statistics as a result of a partition exchange operation but to leave you to do that manually by gathering stats on the table partition. If we were to modify the parameter to a non-default value (and, being an underscore parameter, that's your own choice at your own risk ...), we would see different behaviour. I ran the same script, but with this small addition that changes the parameter setting at the session level.

SQL> alter session set "_minimal_stats_aggregation"=FALSE;

Session altered.

Which will change the end result to this ...

SQL> select  table_name, global_stats, last_analyzed, num_rows
  2  from dba_tables
  3  where table_name='TEST_TAB1'
  4  and owner='TESTUSER'
  5  order by 1, 2, 4 desc nulls last;

TABLE_NAME                     GLO LAST_ANALYZED          NUM_ROWS                                             
------------------------------ --- -------------------- ----------                                             
TEST_TAB1                      NO                                                                              

SQL>
SQL> select  table_name, partition_name, global_stats, last_analyzed, num_rows
  2  from dba_tab_partitions
  3  where table_name='TEST_TAB1'
  4  and table_owner='TESTUSER'
  5  order by 1, 2, 4 desc nulls last;

TABLE_NAME                     PARTITION_NAME                 GLO LAST_ANALYZED          NUM_ROWS              
------------------------------ ------------------------------ --- -------------------- ----------              
TEST_TAB1                      P_20100131                     NO                                               
TEST_TAB1                      P_20100201                     NO                                               
TEST_TAB1                      P_20100202                     NO                                               
TEST_TAB1                      P_20100203                     NO                                               
TEST_TAB1                      P_20100204                     NO                                               
TEST_TAB1                      P_20100205                     NO                                               
TEST_TAB1                      P_20100206                     NO                                               
TEST_TAB1                      P_20100207                     NO                                               
TEST_TAB1                      P_20100209                     NO  28-FEB-2010 21:41:53         12              

9 rows selected.

Note that there are still no statistics at the table level because not all of the partitions have stats yet, so aggregation can't take place, but there are aggregated statistics on the P_20100209 partition, because all of the relevant subpartitions do have stats.

All you need to remember is that the default setting of _minimal_stats_aggregation means that, unless you explicitly gather statistics on the partitions you've just exchanged, aggregation will not take place! Actually, copying stats will also invoke the aggregation process too, but I'll deal with that in the next post. (Updated later. That last sentence might not be true. I've just tried something at home and I'm seeing different results at work, so more investigation needed.)

Oh, and there's much more on this subject over on Randolf Geist's blog post.