如果你曾经接触过那些使用分区或子分区的大型数据库，你会意识到，维护最新/适当的统计信息是一个重要的挑战，我们在近期的工作中就遇到了几个问题。而且，我认为把一些相关主题的基础知识放到一起，成为一个系列贴，会是一个不错的主意。因为你很快就会发现，你自己会在阅读官方文档 ，Oracle的支持说明，相关博客，论坛的贴子和其它的一些内容时转圈，不知道该如何是好。
我会避免从高深的角度，而是会花费一些时间在简单和实用例子的展示上，这可能对大部分新人（比如我）更有用。我也十分确定，我要写的这些内容，已经被像Jonathan Lewis, Christian Antognini, Randolf Geist, Martin Widlake和其它一些人写过了，但是，我想用我自己可以理解的方式去写。有时，我有一种感觉，当我要发表博客，并计划去讨论一些对于有经验的人来说显而易见的事情时，但我并不确信绝大多数的人也是十分理解的。因为我没有计划，所以，我也不知道这个系列会有多少篇，但是我知道如果全放在一篇中，其内容是太多了。
后记–当我看到Martin的博客的链接时（http://mwidlake.wordpress.com/2010/02/16/stats-need-stats-to-gather-stats/），我注意到他正计划做一个DBMS_STATS的完整系列。哎，我会保持关注，因为他会一如既往地深入。而我这里会继续遵循简单。
这一切都是在LINUX上运行的Oracle 10.2.0.4上完成的，尽管我们打了几个和统计信息相关的补丁（后面也许会更多）。我也可能在我们自己后来安装的11.2.0.1上运行相同的测试，以便找出任何差异。
所有的示例均基于以下的表定义：
SQL> CREATE TABLE TEST_TAB1
(
REPORTING_DATE NUMBER NOT NULL,
SOURCE_SYSTEM VARCHAR2(30 CHAR) NOT NULL,
SEQ_ID NUMBER NOT NULL,
STATUS VARCHAR2(1 CHAR) NOT NULL
)
PARTITION BY RANGE (REPORTING_DATE)
SUBPARTITION BY LIST (SOURCE_SYSTEM)
SUBPARTITION TEMPLATE
(SUBPARTITION GROT VALUES ('GROT') TABLESPACE TEST_DAT01,
SUBPARTITION JUNE VALUES ('JUNE') TABLESPACE TEST_DAT01,
SUBPARTITION HALO VALUES ('HALO') TABLESPACE TEST_DAT01,
SUBPARTITION OTHERS VALUES (DEFAULT) TABLESPACE TEST_DAT01)
(
PARTITION P_20100131 VALUES LESS THAN (20100201) NOLOGGING NOCOMPRESS,
PARTITION P_20100201 VALUES LESS THAN (20100202) NOLOGGING NOCOMPRESS,
PARTITION P_20100202 VALUES LESS THAN (20100203) NOLOGGING NOCOMPRESS,
PARTITION P_20100203 VALUES LESS THAN (20100204) NOLOGGING NOCOMPRESS,
PARTITION P_20100204 VALUES LESS THAN (20100205) NOLOGGING NOCOMPRESS,
PARTITION P_20100205 VALUES LESS THAN (20100206) NOLOGGING NOCOMPRESS,
PARTITION P_20100206 VALUES LESS THAN (20100207) NOLOGGING NOCOMPRESS,
PARTITION P_20100207 VALUES LESS THAN (20100208) NOLOGGING NOCOMPRESS
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Table created.
SQL> CREATE UNIQUE INDEX TEST_TAB1_IX1 ON TEST_TAB1
(REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID)
LOCAL NOPARALLEL COMPRESS 1;
Index created.
该表为每个REPORTING_DATE一个分区，而每个分区又根据发送数据的SOURCE_SYSTEM列划分了子分区。可能需要指出的是，在这个阶段的表定义和测试数据与我工作中使用的系统并不一致，但相似度足以说明问题，并且和我过去使用的几个系统也是十分近似的。说到测试数据，我最好插入一些。
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'GROT', 1000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100202, 'GROT', 30000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100203, 'GROT', 2000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100204, 'GROT', 1000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100205, 'GROT', 2400, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'JUNE', 500, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'HALO', 700, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100202, 'HALO', 1200, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'WINE', 400, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100206, 'WINE', 600, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100204, 'WINE', 700, 'P');
1 row created.
SQL> COMMIT;
Commit complete.
有了创建的表和数据，我将使用默认选项收集统计信息，并且，这里我可能要说的是，我接触过的Oracle的每一个人，都希望人们从默认选项开始。而原因可能是显而易见的。
SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', GRANULARITY => 'DEFAULT');
PL/SQL procedure successfully completed.
让我们看看都收集了哪些统计信息，现在重点关注简单的NUM_ROWS信息。
SQL> select table_name, global_stats, last_analyzed, num_rows
2 from user_tables
3 where table_name='TEST_TAB1'
4 order by 1, 2, 4 desc nulls last;
TABLE_NAME GLO LAST_ANALYZED NUM_ROWS
------------------------------ --- -------------------- ----------
TEST_TAB1 YES 10-FEB-2010 16:31:17 11
SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows
2 from user_tab_partitions
3 where table_name='TEST_TAB1'
4 order by 1, 2, 4 desc nulls last;
TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ --- -------------------- ----------
TEST_TAB1 P_20100131 YES 10-FEB-2010 16:31:17 0
TEST_TAB1 P_20100201 YES 10-FEB-2010 16:31:17 4
TEST_TAB1 P_20100202 YES 10-FEB-2010 16:31:17 2
TEST_TAB1 P_20100203 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100204 YES 10-FEB-2010 16:31:17 2
TEST_TAB1 P_20100205 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100206 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100207 YES 10-FEB-2010 16:31:17 0
8 rows selected.
SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows
2 from user_tab_subpartitions
3 where table_name='TEST_TAB1'
4 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
TEST_TAB1 P_20100201_GROT NO
TEST_TAB1 P_20100201_HALO NO
<输出被截断…有很多子分区，并且所有数据都丢失了！>
目前，行数看起来很准确，表和表的分区上均有全局统计信息，而在所有的子分区上没有统计信息。首先，让我们说一说全局统计信息。有几个好的资料描述了全局统计信息，但我这里只列出了2个。我总是喜欢参考官方的文档，虽然这是一个11.2的文档 ，故而有些内容对于10g是不正确的。在13.3.1.3（Database Performance Tuning Guide11g Release 2 (11.2)）中的前两段中，简单提到了全局统计信息–全局统计信息是描述整个表的的统计信息，除了底层分区的统计信息。重要的一点是，优化器有时使用全局统计信息，有时是分区上的统计信息，有时两者均有，这取决于查询。对于具有支持访问权限的用户，文档236935.1将提供更多详细信息。
然而，我们的例子由于我们有了子分区而变得复杂。所以在这个阶段，我们有全局统计信息数据来描述整个表（包括所有底层分区），每个分区上的全局统计信息数据描述该分区（及其所有底层子分区）。在这个阶段，让我们假设拥有全局统计信息数据是“一件好事”，以致于Oracle的默认选项就是在表和分区级别收集它们。在下一篇文章中，我将介绍它们为什么重要。
那为什么没有子分区的统计信息呢？好吧，优化器只会在能够保证使用上单个子分区的情况下，使用子分区的统计信息数据，而这可能和你想象的不太一样。Oracle默认情况下不会收集这些统计信息数据，但也能够使用更高级别的分区统计数据来猜测子分区级别上发生了什么。但是，如果您确实认为您的查询能够有效地深入到特定的子分区，那么您也可以选择收集子分区统计信息。但要注意的是，据我所知，在10.2.0.4之前，优化器根本不会使用子分区统计信息数据，因此，如果您运行的是早期版本，那么额外的开销没有任何好处。
在下一篇中，我会看一看为何全局统计信息有利有弊。。。
原文内容：
If you’ve ever worked on large databases that use partitioned and subpartitioned tables, you’ll be aware that there are significant challenges in maintaining up-to-date/appropriate statistics. We’ve encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the basics of what can become quite an involved topic because it’s not difficult to find yourself going round in circles reading the documentation, Oracle Support Notes, blog posts, forum threads and the rest until you don’t know whether you’re coming or going!
I’ll steer clear of any remotely advanced angle and try to take some time to show simple, practical examples that might be useful to the great unwashed masses (like me). I’m pretty certain that everything I’m going to post has already been written about by the likes of Jonathan Lewis, Christian Antognini, Randolf Geist, Martin Widlake and others, but I want to write it in my own way that I can understand 😉 Sometimes I have a feeling when I write certain blog posts that I’m going to be discussing things which are apparently obvious to experienced people but I’m not convinced most people quite understand. I’ve no idea how many parts there might be because there’s no plan here, but I know it’s going to end up being too much for one post.
Added later - whilst digging out a link to Martin’s blog, I noticed that he’s planning a whole DBMS_STATS series soon. Sigh. Keep an eye out for that, because it will be as in-depth as always. I’ll stick to the simple stuff here!
This is all on Oracle 10.2.0.4 running on Linux although we have several stats-related patches applied (probably more on those later) and I’ll probably run the same tests on my own 11.2.0.1 installation later to identify any differences.
All of the examples will be based on the following table definition
SQL> CREATE TABLE TEST_TAB1
(
REPORTING_DATE NUMBER NOT NULL,
SOURCE_SYSTEM VARCHAR2(30 CHAR) NOT NULL,
SEQ_ID NUMBER NOT NULL,
STATUS VARCHAR2(1 CHAR) NOT NULL
)
PARTITION BY RANGE (REPORTING_DATE)
SUBPARTITION BY LIST (SOURCE_SYSTEM)
SUBPARTITION TEMPLATE
(SUBPARTITION GROT VALUES ('GROT') TABLESPACE TEST_DAT01,
SUBPARTITION JUNE VALUES ('JUNE') TABLESPACE TEST_DAT01,
SUBPARTITION HALO VALUES ('HALO') TABLESPACE TEST_DAT01,
SUBPARTITION OTHERS VALUES (DEFAULT) TABLESPACE TEST_DAT01)
(
PARTITION P_20100131 VALUES LESS THAN (20100201) NOLOGGING NOCOMPRESS,
PARTITION P_20100201 VALUES LESS THAN (20100202) NOLOGGING NOCOMPRESS,
PARTITION P_20100202 VALUES LESS THAN (20100203) NOLOGGING NOCOMPRESS,
PARTITION P_20100203 VALUES LESS THAN (20100204) NOLOGGING NOCOMPRESS,
PARTITION P_20100204 VALUES LESS THAN (20100205) NOLOGGING NOCOMPRESS,
PARTITION P_20100205 VALUES LESS THAN (20100206) NOLOGGING NOCOMPRESS,
PARTITION P_20100206 VALUES LESS THAN (20100207) NOLOGGING NOCOMPRESS,
PARTITION P_20100207 VALUES LESS THAN (20100208) NOLOGGING NOCOMPRESS
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Table created.
SQL> CREATE UNIQUE INDEX TEST_TAB1_IX1 ON TEST_TAB1
(REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID)
LOCAL NOPARALLEL COMPRESS 1;
Index created.
So there is a partition per REPORTING_DATE which is sub-partitioned depending on the SOURCE_SYSTEM that sent the data. It’s probably worth pointing out at this stage that the table definition and test data does not match that used in the system I’m working on, but is similar enough to illustrate the issues and is pretty similar to several other systems I’ve seen or worked on in the past. Speaking of test data, I’d better insert some.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'GROT', 1000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100202, 'GROT', 30000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100203, 'GROT', 2000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100204, 'GROT', 1000, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100205, 'GROT', 2400, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'JUNE', 500, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'HALO', 700, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100202, 'HALO', 1200, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100201, 'WINE', 400, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100206, 'WINE', 600, 'P');
1 row created.
SQL> INSERT INTO TEST_TAB1 VALUES (20100204, 'WINE', 700, 'P');
1 row created.
SQL> COMMIT;
Commit complete.
With table and data created, I’ll gather some statistics using default options and it’s probably worth pointing out at this stage that everyone I’ve spoken to at Oracle is very keen that people should start off with the default options for reasons that will hopefully become apparent.
SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST_TAB1', GRANULARITY => 'DEFAULT');
PL/SQL procedure successfully completed.
So let’s see what statistics have been gathered and focus on the simple NUM_ROWS for now.
SQL> select table_name, global_stats, last_analyzed, num_rows
2 from user_tables
3 where table_name='TEST_TAB1'
4 order by 1, 2, 4 desc nulls last;
TABLE_NAME GLO LAST_ANALYZED NUM_ROWS
------------------------------ --- -------------------- ----------
TEST_TAB1 YES 10-FEB-2010 16:31:17 11
SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows
2 from user_tab_partitions
3 where table_name='TEST_TAB1'
4 order by 1, 2, 4 desc nulls last;
TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS
------------------------------ ------------------------------ --- -------------------- ----------
TEST_TAB1 P_20100131 YES 10-FEB-2010 16:31:17 0
TEST_TAB1 P_20100201 YES 10-FEB-2010 16:31:17 4
TEST_TAB1 P_20100202 YES 10-FEB-2010 16:31:17 2
TEST_TAB1 P_20100203 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100204 YES 10-FEB-2010 16:31:17 2
TEST_TAB1 P_20100205 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100206 YES 10-FEB-2010 16:31:17 1
TEST_TAB1 P_20100207 YES 10-FEB-2010 16:31:17 0
8 rows selected.
SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows
2 from user_tab_subpartitions
3 where table_name='TEST_TAB1'
4 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
TEST_TAB1 P_20100201_GROT NO
TEST_TAB1 P_20100201_HALO NO
<output snipped ....there are a lot of subpartitions, all missing stats!>
So at the moment the row counts look spot-on and there are Global Statistics on both the Table and the Partitions of the table and no statistics at all on the Subpartitions. First, let’s talk about global statistics. There are several good resources kicking around describing global stats so I’ll list just a couple here. I always like a documentation reference and although this is the 11.2 documentation and so some of it isn’t correct for 10g, I like the very simple mention of global stats given in the first two paragraphs on 13.3.1.3 - global stats are statistics on the table that describe the table as a whole, in addition to the stats on the underlying partitions. The important point is that sometimes the optimiser will use the global stats, sometimes the partition stats and sometimes both, depending on the query. For those of you with Support access, Note 236935.1 goes into more detail.
However, our example is complicated by the fact that we have subpartitions too. So at this stage we have global stats that describe the table as a whole (including all of the underlying partitions) and global stats on each partition that describe that partition (and all of its underlying subpartitions). At this stage, let’s just assume that having global stats is ‘a good thing’ which is why Oracle’s default option is to gather them at the Table and Partition levels. In the next post I’ll look at why they’re important.
Why no Subpartition stats, then? Well, the optimiser is only going to use stats on subpartitions when it can guarantee that it’s going to use a single subpartition and as that’s probably less likely than you think, Oracle doesn’t collect those stats by default, but is able to use higher level partition stats to guess what’s going on at the subpartition level too. However, if you do think your queries are going to be able to drill down to a specific subpartition effectively, you can choose to gather subpartition statistics too. Beware though that, as far as I’m aware, the optimiser won’t use subpartition stats at all, prior to 10.2.0.4 so there’s no benefit to the additional overhead if you’re running an earlier version.
In the next post I’ll look at why global stats are both a good and bad thing …
