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.
RSS
Email