第2章　統(tǒng)計信息

2.1　什么是統(tǒng)計信息

前面提到，只有大表才會產(chǎn)生性能問題，那么怎么才能讓優(yōu)化器知道某個表多大呢？這就需要對表收集統(tǒng)計信息。我們在第一章提到的基數(shù)、直方圖、集群因子等概念都需要事先收集統(tǒng)計信息才能得到。

統(tǒng)計信息類似于戰(zhàn)爭中的偵察兵，如果情報工作沒有做好，打仗就會輸?shù)魬?zhàn)爭。同樣的道理，如果沒有正確地收集表的統(tǒng)計信息，或者沒有及時地更新表的統(tǒng)計信息，SQL的執(zhí)行計劃就會跑偏，SQL也就會出現(xiàn)性能問題。收集統(tǒng)計信息是為了讓優(yōu)化器選擇最佳執(zhí)行計劃，以最少的代價（成本）查詢出表中的數(shù)據(jù)。

統(tǒng)計信息主要分為表的統(tǒng)計信息、列的統(tǒng)計信息、索引的統(tǒng)計信息、系統(tǒng)的統(tǒng)計信息、數(shù)據(jù)字典的統(tǒng)計信息以及動態(tài)性能視圖基表的統(tǒng)計信息。

關于系統(tǒng)的統(tǒng)計信息、數(shù)據(jù)字典的統(tǒng)計信息以及動態(tài)性能視圖基表的統(tǒng)計信息本書不做討論，本書重點討論表的統(tǒng)計信息、列的統(tǒng)計信息以及索引的統(tǒng)計信息。

表的統(tǒng)計信息主要包含表的總行數(shù)（num_rows）、表的塊數(shù)（blocks）以及行平均長度（avg_row_len），我們可以通過查詢數(shù)據(jù)字典DBA_TABLES獲取表的統(tǒng)計信息。

現(xiàn)在我們創(chuàng)建一個測試表T_STATS。

SQL> create table t_stats as select * from dba_objects;

Table created.

我們查看表T_STATS常用的表的統(tǒng)計信息。

SQL> select owner, table_name, num_rows, blocks, avg_row_len
  2    from dba_tables
  3   where owner = 'SCOTT'
  4     and table_name = 'T_STATS';

OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT           T_STATS

因為T_STATS是新創(chuàng)建的表，沒有收集過統(tǒng)計信息，所以從DBA_TABLES查詢數(shù)據(jù)是空的。

現(xiàn)在我們來收集表T_STATS的統(tǒng)計信息。

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
  3                                  tabname          => 'T_STATS',
  4                                  estimate_percent => 100,
  5                                  method_opt       => 'for all columns size auto',
  6                                  no_invalidate    => FALSE,
  7                                  degree           => 1,
  8                                  cascade          => TRUE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

我們再次查看表的統(tǒng)計信息。

SQL> select owner, table_name, num_rows, blocks, avg_row_len
  2    from dba_tables
  3   where owner = 'SCOTT'
  4     and table_name = 'T_STATS';

OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT           T_STATS              72674       1061          97

從查詢中我們可以看到，表T_STATS一共有72 674行數(shù)據(jù)，1 061個數(shù)據(jù)塊，平均行長度為97字節(jié)。

列的統(tǒng)計信息主要包含列的基數(shù)、列中的空值數(shù)量以及列的數(shù)據(jù)分布情況（直方圖）。我們可以通過數(shù)據(jù)字典DBA_TAB_COL_STATISTICS查看列的統(tǒng)計信息。

現(xiàn)在我們查看表T_STATS常用的列統(tǒng)計信息。

SQL> select column_name, num_distinct, num_nulls, num_buckets, histogram
  2    from dba_tab_col_statistics
  3   where owner = 'SCOTT'
  4     and table_name = 'T_STATS';

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM
--------------- ------------ ---------- ----------- --------------------
EDITION_NAME               0      72674           0 NONE
NAMESPACE                 21          1           1 NONE
SECONDARY                  2          0           1 NONE
GENERATED                  2          0           1 NONE
TEMPORARY                  2          0           1 NONE
STATUS                     2          0           1 NONE
TIMESTAMP               1592          1           1 NONE
LAST_DDL_TIME           1521          1           1 NONE
CREATED                 1472          0           1 NONE
OBJECT_TYPE               45          0           1 NONE
DATA_OBJECT_ID          7796      64833           1 NONE
OBJECT_ID              72673          1           1 NONE
SUBOBJECT_NAME           140      72145           1 NONE
OBJECT_NAME            44333          0           1 NONE
OWNER                     31          0           1 NONE

15 rows selected.

上面查詢中，第一個列表示列名字，第二個列表示列的基數(shù)，第三個列表示列中NULL值的數(shù)量，第四個列表示直方圖的桶數(shù)，最后一個列表示直方圖類型。

在工作中，我們經(jīng)常使用下面腳本查看表和列的統(tǒng)計信息。

SQL> select a.column_name,
  2         b.num_rows,
  3         a.num_nulls,
  4         a.num_distinct Cardinality,
  5         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
  6         a.histogram,
  7         a.num_buckets
  8    from dba_tab_col_statistics a, dba_tables b
  9   where a.owner = b.owner
 10     and a.table_name = b.table_name
 11     and a.owner = 'SCOTT'
 12     and a.table_name = 'T_STATS';

COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME         72674      72674           0           0 NONE                  0
NAMESPACE            72674          1          21         .03 NONE                  1
SECONDARY            72674          0           2           0 NONE                  1
GENERATED            72674          0           2           0 NONE                  1
TEMPORARY            72674          0           2           0 NONE                  1
STATUS               72674          0           2           0 NONE                  1
TIMESTAMP            72674          1        1592        2.19 NONE                  1
LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
CREATED              72674          0        1472        2.03 NONE                  1
OBJECT_TYPE          72674          0          45         .06 NONE                  1
DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
OBJECT_ID            72674          1       72673         100 NONE                  1
SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
OBJECT_NAME          72674          0       44333          61 NONE                  1
OWNER                72674          0          31         .04 NONE                  1

15 rows selected.

索引的統(tǒng)計信息主要包含索引blevel（索引高度-1）、葉子塊的個數(shù)（leaf_blocks）以及集群因子（clustering_factor）。我們可以通過數(shù)據(jù)字典DBA_INDEXES查看索引的統(tǒng)計信息。

我們在OBJECT_ID列上創(chuàng)建一個索引。

SQL> create index idx_t_stats_id on t_stats(object_id);

Index created.

創(chuàng)建索引的時候會自動收集索引的統(tǒng)計信息，運行下面腳本查看索引的統(tǒng)計信息。

SQL> select blevel, leaf_blocks, clustering_factor,status
  2    from dba_indexes
  3   where owner = 'SCOTT'
  4     and index_name = 'IDX_T_STATS_ID';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- ----------------
         1         161              1127 VALID

如果要單獨對索引收集統(tǒng)計信息，可以使用下面腳本收集。

SQL> BEGIN
  2    DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
  3                                  indname => 'IDX_T_STATS_ID');
  4  END;
  5  /

PL/SQL procedure successfully completed.

在本書第6章中，我們會詳細介紹表的統(tǒng)計信息、列的統(tǒng)計信息以及索引的統(tǒng)計信息是如何被應用于成本計算的。