Statistics on Partitioned Tables - Part 3

As soon as I'd committed my last post, I knew it wasn't what I'd hoped for and said as much to a couple of people before they'd read it. I knew it would probably just add to any confusion people already had about this subject (something I'm particularly keen to avoid) but I am awash with examples at the moment and trying to pick out the right points to illustrate, in the right order, to the right depth. This is probably more of a White Paper subject, in retrospect, but I'll press on anyway.

To summarise where we are so far, though, and highlight a couple of key points ...

1) As I said in the first post, all of the examples to date are on Oracle 10.2.0.4 but I think you would see similar behaviour on earlier releases that I don't have to hand right now. The default parameters would be different, but the aggregation process goes back a long way. i.e. This is not about any 11g features, at least not yet. That will come later. (I still wish I'd put 10g in the post titles, though, like I did with the Adaptive Thresholds posts.)

2) Partitioned objects have two different kinds of stats - Global Statistics describe a Table or Partition as a whole, including all of it's child structures and Partition Statistics describe individual partitions and subpartitions. In addition, Oracle has the capability to take Partition stats and aggregate them up to generate Aggregated Global Statistics. Hopefully the last post illustrated that some elements of aggregated stats seem reliable but some not, particularly Number of Distinct Values (NDV).

3) So when looking at optimiser stats, it's essential that you look at columns like GLOBAL_STATS and at the HIGH_VALUE, LOW_VALUE and NUM_DISTINCT columns or you might kid yourself into thinking that your stats are better than they really are. I suspect that's what had happened at my current site. Be honest with yourself. Excluding those experts who might read this, how many of you have taken a quick glance at NUM_ROWS and LAST_ANALYZED columns to reassure yourself your stats are ok? You need to be careful with this stuff and Greg's post can help you check.

4) One of the more confusing aspects of the first two posts is that they showed completely different strategies to collecting stats on our tables. The first post covered the Oracle-recommended 10.2 default behaviour of gathering GLOBAL AND PARTITION stats down to the Partition level. The second post showed a completely different strategy we use on many tables of gathering no stats at all at the Table and Partition level, but gathering Subpartition statistics and having Oracle aggregate them up to the higher levels in an attempt to reduce stats gathering activity. That was deliberate, as in the next post I'm going to show you how these two strategies combined in the wrong way can cause trouble.

5) If I posted all of the examples each time, it would become a pretty long post, so I'm going to ask you to refer back to earlier posts if you want to check table definitions and the like. This is a series after all At the end, I might try to tidy everything up and post it all in one script, showing the various examples. Should that ever happen, it will be after the Hotsos Symposium. i.e. Don't hold your breath.

Hopefully that little summary will help us move on to the specific problem that we faced at work and some of the options we're looking at (because, yes folks, I have read other posts and do know some of the options but I'm trying to work my way through them here. Give it time ). It might take two posts though.

---

What went wrong on our current system? Remember that we have traditionally gathered purely at the SUBPARTITION level and allowed Oracle to aggregate those to generate the TABLE and PARTITION stats (i.e. the approach shown in post 2). I'll be honest that it wasn't a strategy I'd seen used almost exclusively across a system before. I'd been lucky enough to find some way of gathering PARTITION stats at the very least. First I'll show you a simulation of what would happen when we start loading data for a new day. First we add a new subpartition (and because we have a subpartition template, the subpartitions are created too) then I'll insert some rows into the different new subpartitions. (Note that we actually use both inserts and partition exchange, depending on the table, but I'll deal with partition exchange later.)

SQL> ALTER TABLE TEST_TAB1
  2  ADD  PARTITION P_20100208 VALUES LESS THAN (20100209);

Table altered.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'GROT', 1000, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'GROT', 30000, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'GROT', 2000, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'GROT', 10000, 'Z');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'GROT', 2400, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'HALO', 500, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'HALO', 700, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'JUNE', 1200, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'WINE', 400, 'P');

1 row created.

SQL> INSERT INTO TEST_TAB1 VALUES (20100208, 'WINE', 600, 'P');

1 row created.

SQL> COMMIT;

Commit complete.

At this stage there are no stats on the new partition or subpartitions and all of the previous stats look the same. (I'll make things more succinct by avoiding the column stats for now.)

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  23-FEB-2010 06:09:55         27

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  23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100201                     NO  23-FEB-2010 06:09:55          8
TEST_TAB1                      P_20100202                     NO  23-FEB-2010 06:09:55          4
TEST_TAB1                      P_20100203                     NO  23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100204                     NO  23-FEB-2010 06:09:55          4
TEST_TAB1                      P_20100205                     NO  23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100206                     NO  23-FEB-2010 06:09:55          7
TEST_TAB1                      P_20100207                     NO  23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100208                     NO

9 rows selected.

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                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_HALO                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_OTHERS              YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100201_GROT                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_HALO                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_JUNE                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_OTHERS              YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_GROT                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_HALO                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100202_OTHERS              YES 23-FEB-2010 06:09:55          0

<<output snipped>>

TEST_TAB1                      P_20100207_GROT                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_HALO                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_OTHERS              YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100208_GROT                NO
TEST_TAB1                      P_20100208_HALO                NO
TEST_TAB1                      P_20100208_JUNE                NO
TEST_TAB1                      P_20100208_OTHERS              NO

36 rows selected.

Which is probably what you expected. Now I'm going to simulate what would happen when one of the source data feeds complete and we'll gather stats on that subpartition now that the data is loaded.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', granularity => 'SUBPARTITION', 
                                        partname => 'P_20100208_GROT');

PL/SQL procedure successfully completed.

SQL> 
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  23-FEB-2010 06:11:39          0
TEST_TAB1                      P_20100201                     NO  23-FEB-2010 06:11:39          8
TEST_TAB1                      P_20100202                     NO  23-FEB-2010 06:11:39          4
TEST_TAB1                      P_20100203                     NO  23-FEB-2010 06:11:39          2
TEST_TAB1                      P_20100204                     NO  23-FEB-2010 06:11:39          4
TEST_TAB1                      P_20100205                     NO  23-FEB-2010 06:11:39          2
TEST_TAB1                      P_20100206                     NO  23-FEB-2010 06:11:39          7
TEST_TAB1                      P_20100207                     NO  23-FEB-2010 06:11:39          0
TEST_TAB1                      P_20100208                     NO

9 rows selected.

Woah! What happened to our Aggregated Global Stats on the TABLE? It looks like it's never had statistics at all! Oh, and why are there no Aggregated Stats on the new partition either, given that I just gathered stats for one of it's subpartitions? Well the problem is that Oracle will only aggregate statistics when all of the components have stats that can be aggregated. The problem here is that, at this stage, P_20100208_GROT is the only subpartition of P_20100208 that has stats. The others haven't been gathered yet.

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                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_HALO                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100131_OTHERS              YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100201_GROT                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_HALO                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_JUNE                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100201_OTHERS              YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_GROT                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_HALO                YES 23-FEB-2010 06:09:55          2
TEST_TAB1                      P_20100202_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100202_OTHERS              YES 23-FEB-2010 06:09:55          0

<<output snipped>>

TEST_TAB1                      P_20100207_GROT                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_HALO                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_JUNE                YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100207_OTHERS              YES 23-FEB-2010 06:09:55          0
TEST_TAB1                      P_20100208_GROT                YES 23-FEB-2010 06:11:39          5
TEST_TAB1                      P_20100208_HALO                NO
TEST_TAB1                      P_20100208_JUNE                NO
TEST_TAB1                      P_20100208_OTHERS              NO

36 rows selected.

Of course, this is all behaving exactly as designed, Oracle keep emphasising that people should gather using the default granularity of 'AUTO' and so anyone who does this is asking for trouble, but the reality is that people are trying this. Look no further than Peter Scott's comment to see that someone else has come across this before now!

Let's look at the stats once the other data feeds complete and we gather the rest of the subpartition stats ....

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', granularity => 'SUBPARTITION', 
                                        partname => 'P_20100208_JUNE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', granularity => 'SUBPARTITION', 
                                        partname => 'P_20100208_HALO');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', granularity => 'SUBPARTITION', 
                                        partname => 'P_20100208_OTHERS');

PL/SQL procedure successfully completed.

SQL>
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  23-FEB-2010 06:31:24         37

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  23-FEB-2010 06:31:24          0
TEST_TAB1                      P_20100201                     NO  23-FEB-2010 06:31:24          8
TEST_TAB1                      P_20100202                     NO  23-FEB-2010 06:31:24          4
TEST_TAB1                      P_20100203                     NO  23-FEB-2010 06:31:24          2
TEST_TAB1                      P_20100204                     NO  23-FEB-2010 06:31:24          4
TEST_TAB1                      P_20100205                     NO  23-FEB-2010 06:31:24          2
TEST_TAB1                      P_20100206                     NO  23-FEB-2010 06:31:24          7
TEST_TAB1                      P_20100207                     NO  23-FEB-2010 06:31:24          0
TEST_TAB1                      P_20100208                     NO  23-FEB-2010 06:31:24         10

9 rows selected.

That looks much better. So, if you are going to use this approach (and I hope this series of blogs helps you decide it's questionable) you should only gather stats on the subpartitions when you have all of the subpartitions populated and gather them all at the same time. In fact, in that case, why not just gather at the PARTITION level and get proper Global Statistics on your partitions?

Believe me, there are more horrors to come ....