11g 统计信息的审核和预发布

最新推荐文章于 2024-01-18 09:11:32 发布

com20140604

最新推荐文章于 2024-01-18 09:11:32 发布

阅读量82

点赞数

正在审核的统计信息

考虑这样一种情况：您已经仔细收集了关于所有表、索引等的统计信息，同时所有查询都有完善的执行计划。但现在有人参与了统计信息收集工作，执行计划一下子变得截然不同。没错，这些计划可能更好（但也可能更糟，虽然概率低些）。

避免这种灾难的一种方法是使用 SQL 计划管理，您可以用它来创建 SQL 计划基线，从而为 SQL 语句锁定计划。但这并不是一个通用的解决方案。看不到的 SQL 语句不会有 SQL 计划基线，因此不会受到 SQL 计划管理的保护。

另外一个问题是何时收集统计信息。统计信息收集是一个大量占用 CPU 和频繁进行 I/O 的过程，您可能希望在相对安静的时间段（如晚上）完成这一工作。但当统计信息收集完毕后，计划立即会受到影响。您可能不希望一大早就要首先完成一个新的计划。相反，您希望统计信息在夜间就能准备好以供使用。矛盾在于，您需要现在就收集好统计信息而使其在以后生效。

这一难题已在 Oracle 数据库 11g 中得到解决。您可以将表索引的统计信息定义为处于正在审核状态，这意味着新收集的统计信息不会被发布或为优化程序所使用 — 这样您就有机会在发布之前对新的统计信息进行测试。要实现这一目的，您需要通过提供的程序包 dbms_stats 中的 set_table_prefs 过程将表统计信息的 publish 属性设置为 FALSE。

下面的例子说明了如何使 ARUP 模式中 SALGRADE 表的统计信息处于未发布状态：

begin
  dbms_stats.set_table_prefs ( 
    wnname => 'ARUP', 
    tabname => 'SALGRADE', 
    pname   => 'PUBLISH', 
    pvalue  => 'FALSE' 
  );     
end;

一旦您将发布首选项设置为 false ，收集的统计信息就将被标记为正在审核。下面是现在该表的统计信息：

SQL> select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss') 
  2  from user_tables
  3  where table_name = 'SALGRADE';
 
  NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
         6 09/10/07 22:04:37

今天是 2007 年 9 月 21 日，因此统计信息是刚刚收集的。我们再次收集统计信息：

begin
        dbms_stats.gather_table_stats (
                ownname         => 'ARUP',
                tabname         => 'SALGRADE',
                estimate_percent=> 100
);
end;
/

如果您再次查看统计信息：

  NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
         6 09/10/07 22:04:37

您可以看到行数没变，同时 last_analyzed 值也没变。那么，刚收集的统计信息发生了什么变化呢？统计信息被标记为 正在审核 。正在审核的统计信息可在视图 USER_TAB_PENDING_STATS 中看到：

select num_rows, to_char(last_analyzed,'mm/dd/yy hh24:mi:ss')
from user_tab_pending_stats
where table_name = 'SALGRADE';

Here is the output:

  NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
         9 09/21/07 11:03:35

现在表有 9 行，显示正在审核的统计信息。同时显示有收集时间。假定您想让优化程序看到这些正在审核的统计信息。您可以发布它们：

begin
    dbms_stats.publish_pending_stats('ARUP', 'SALGRADE');
end;
/

如果您检查视图 user_tab_pending_stats，您会发现它现在是空的。如果您现在检查 USER_TABLES，您会看到最新的统计信息：

  NUM_ROWS TO_CHAR(LAST_ANAL
---------- -----------------
         9 09/21/07 11:03:35

这种将统计信息的收集和发布相分开的方法也可用于分区的表。假定您正在逐个分区地加载一个表。您不希望只提供部分信息给优化程序，您更希望所有分区的统计信息能够同时被优化程序看到。但是您还想在加载分区后立刻利用这一时间。那么，您可以在加载分区后立即收集它的统计信息，但不发布这些信息。分析完所有分区后，您可以一次性发布这些统计信息。

检查正在审核的统计信息然后发布

对正在审核的统计信息的一个妙用是，收集它们并测试查询，然后发布。要使优化程序使用正在审核的统计信息而非已发布的信息，将会话参数 optimizer_use_pending_statistics 设为 true。

考虑一个名为 RES 的表，同时假定列 STATUS 有一个索引。

SQL> create index in_res_status on res (status);

使用 cascade 选项收集该表的统计信息。

begin
        dbms_stats.gather_table_stats (
                ownname         => 'ARUP',
                tabname         => 'RES',
                estimate_percent=> 100,
                cascade         => true
);
end;

列 STATUS 的值按如下所示分布：

STATUS    COUNT(1)
------- ----------
INVALID          6
VALID        68858

如果您发出一个类似 select res_type from res where status = 'VALID'; 的查询并检查执行计划：

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68858 |  1075K|   290   (3)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| RES  | 68858 |  1075K|   290   (3)| 00:00:04 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(STATUS='VALID')

像预期地那样，此操作会对表 RES 进行全表扫描，这是因为表中有很多的行都有该值。假定这一组合在将来发生了变化：

STATUS    COUNT(1)
------- ----------
CLOSED       68857
OK               6
VALID            1

注意，包含 VALID 的行的个数降为 1。此时，查询不应采用全表扫描，而应使用索引。但是如果您运行查询并检查执行计划：

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 68858 |  1075K|   290   (3)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| RES  | 68858 |  1075K|   290   (3)| 00:00:04 |
--------------------------------------------------------------------------

您将看到该查询仍然会进行全表扫描。原因在于统计信息没有更新（是旧的）。优化程序仍然认为包含值 VALID 的行的数目占大多数，因此会生成一个全表扫描计划。您可以重新生成统计信息，这样可以纠正优化程序之前想好的进程，转而使用索引。但是，这带来一个比较棘手的问题：这会破坏某些其他的查询吗？

先不要猜测，请使用提供的程序包 dbms_stats 中的 set_table_prefs 过程，将表 RES 的 PUBLISH 首选项更改为 false。然后照常收集该表的统计信息。由于现在统计信息正在审核中，优化程序不会使用它们。不过，您可以确定它们对优化程序的影响。一个专用的参数可以使优化程序使用正在审核的统计信息，而非已发布的信息。

SQL> alter session set optimizer_use_pending_statistics = true;

现在，如果您检查该计划：

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    16 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES           |     1 |    16 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_STATUS |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

即使已发布的统计信息已改变，优化程序选择的还是索引。正在审核的统计信息使这一过程变得更好，而非更坏。利用这一方法，您可以检查其他的查询，了解这些正在审核的统计信息所产生的影响。您甚至可以使用 SQL 计划管理的功能，利用正在审核的统计信息为您的查询创建 SQL 计划基线，并在以后使用这些计划，而不管这些基于成本的计划是否改变。当您完全满意后，您可以发布正在审核的统计信息。

如果您决定删除正在审核的统计信息，您可以使用下面的命令：

begin
  dbms_stats.delete_pending_stats('ARUP','RES');
end;

假定您发布了上面的统计信息，但是它们在其他情况下带来了严重的破坏。您会希望自己以前没有发布过这些信息。放心，什么都不会丢失。您可以恢复截止到过去某一时间点的统计信息。Oracle 数据库 11 g 保留收集到的统计信息的历史记录，在视图 DBA_TAB_STATS_HISTORY 中可以看到这些信息。此历史记录的默认保存时间为 31 天。

SQL> select to_char(stats_update_time,'mm/dd/yy hh24:mi:ss')
  2  from dba_tab_stats_history
  3  where wner = 'ARUP'
  4  and table_name = 'RES';
 
TO_CHAR(STATS_UPD
-----------------
09/07/07 11:56:26
09/14/07 11:59:43
09/21/07 13:58:31

它显示了过去一些天中收集的统计信息。假定上一次的收集情况不佳，您希望恢复 9 月 14 日收集的信息。

begin
   dbms_stats.restore_table_stats (
       ownname         => 'ARUP',
       tabname         => 'RES',
       as_of_timestamp => '14-SEP-07 11:59:00 AM'
   );
end;
/

当前的统计信息将被 9 月 14 日收集的信息所替换。

扩展统计信息

基于函数的统计信息

传统的统计信息收集方法是收集并存储有关列的数据模式。思考一个略有不同的情况：关于列值的函数，如 UPPER() 函数。

假定您有一个名为 CUST_NAME 的列，它使用常规的混合大小写格式存储客户名。下面是以这种方式存储的客户名示例：

McArthur
MCArthur
mcArthur
MCARTHUR
mcarthur

同一个名字输入时可能有五种变体。该列的基数是比较高的，因为各个值对 Oracle 而言都是唯一的。如果您使用一个谓词 like where upper(cust_name) = 'MCARTHUR' ，则所有这些值都将合并为一个值：MCARTHUR。该列的基数一下就变低了。如果表里只有五行，每个值都是唯一的，那么选择性为 1/5。但是应用 upper() 函数后将各列转换为同一个值，因此选择性变为 1。

选择性在优化程序的执行计划选择中扮演着重要角色，因此由 upper 函数导致的选择性更改应向优化程序说清楚。在 Oracle 数据库 11g 中，您可以创建关于值表达式（而非仅限于列）的扩展统计信息。

下面是一个示例。首先收集表 CUSTOMERS 的统计信息：

begin
        dbms_stats.gather_table_stats (
                ownname         => 'ARUP',
                tabname         => 'CUSTOMERS',
                estimate_percent=> 100,
                method_opt      => 'FOR ALL COLUMNS SIZE AUTO',
                cascade         => true
);
end;

然后考虑以下查询：

select * from customers where upper(cust_name) = 'MCARTHUR'

下面是执行计划：

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   689 | 65455 |   290   (3)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |   689 | 65455 |   290   (3)| 00:00:04 |
-------------------------------------------------------------------------------

注意查询将会检索的行数的估计值 — 689。我们看一下应当检索多少行：

SQL> select count(1) from customers where upper(cust_name) = 'MCARTHUR'
  2  /
 
  COUNT(1)
----------
     29343

明显地，优化程序低估了行数。估计值应当接近于 29343，而非 689。接下来，重新收集关于表的统计信息，同时收集关于表达式 upper(cust_name) 的扩展统计信息。

begin
  dbms_stats.gather_table_stats (
     ownname    => 'ARUP',
     tabname    => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for columns (upper(cust_name))'
  );
end;
/

这将创建关于客户表的扩展统计信息。现在计划类似于：

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 29570 |  3263K|   291   (3)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 29570 |  3263K|   291   (3)| 00:00:04 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(CUSTOMERS.SYS_STUI2GNVTNUK3O8Y#$YT73GA0J='MCARTHUR')

注意，“Rows”列下方的值 (29570) 更为准确地表示了将要返回的行的数目。优化程序如何获得该数目？它来自您刚刚收集的表中表达式 UPPER(CUST_NAME) 的扩展统计信息。注意，在输出内容的底部，还记录了谓词信息，它显示了一个基于列 SYS_STUI2GNVTNUK3O8Y#$YT73GA0J 的筛选器。这是系统为表达式 UPPER(CUST_NAME) 生成的名称。由于优化程序已收集关于表达式的统计信息，它可以准确地预测返回的行数。

为什么这很重要？由于优化程序生成的执行路径的效率取决于它预测估计的行数的准确程度，因此此表达式统计信息是很有帮助的。

您可以将此模型扩展至各个列存在关联的任何表。例如，通过比较雪地鞋在格棱兰和非洲的销量，可以充分说明如何对数据模式进行配置。其他的模式更为基于事实。康涅狄格州是美国而非德国的一个州，因此优化程序会知道“country='Germany' and state='Connecticut'”不会有任何匹配的值，同时会作出相应的决定。

要了解表达式都收集了自身的哪些统计信息，查看视图 DBA_STAT_EXTENSIONS，如下所示：

SQL> select extension_name, extension
  2> from dba_stat_extensions
  3> where table_name='CUSTOMERS';

EXTENSION_NAME                 EXTENSION
------------------------------ --------------------
SYS_STUI2GNVTNUK3O8Y#$YT73GA0J (UPPER(CUST_NAME))

要删除关于某表达式的扩展统计信息，使用 drop_extended_stats 过程。

begin
   dbms_stats.drop_extended_stats (
      wnname => 'ARUP',
      tabname => 'CUSTOMERS',
      extension => '(UPPER(CUST_NAME))'
   );
end;
/

多列统计信息

考虑一下酒店公司的数据库，它包含一个名为 BOOKINGS 的表，提供房间预定的信息。我们对其中的两个特殊列感兴趣：HOTEL_ID（酒店的唯一标识符）和 RATE_CATEGORY（房间每日租金的代码）。这些列有一个索引。

我们来看一下这两列的数据分布：

  HOTEL_ID RATE_CATEGORY   COUNT(1)
---------- ------------- ----------
        10            11      19943
        10            12      39835
        10            13      20036
        20            21       5106
        20            22      10041
        20            23       5039

仔细检查数据：hotel_id 10 的 rate_category 列仅包含 11、12 和 13，而 hotel_id 20 的该列仅包含 21、22 和 23（11、12 和 13 一个都不包含)。为什么？原因可能与酒店的星级有关。酒店 20 是一家定价较高的酒店，而租金等级 11、12 和 13 是较低的等级，因此它们不适用于一家高收费的酒店。同样地，21、22 和 23 是较高的租金等级，因此它们不适用于酒店 10 这样的经济型酒店。而且，酒店 10 的房间预定数量多于酒店 20。注意这些值的组合：hotel_id = 20 和 RATE_CATEGORY = 21 的行数最少：5106。那么，如果您执行与此类似的查询，会出现什么情况呢？

select min(booking_dt)
from bookings
where hotel_id = 20
and rate_category = 21;

应当使用索引，而非全表扫描。您可以设置自动跟踪对其进行测试，如下所示：

SQL> set autot on
SQL> ... execute the query ...

下面是执行计划：

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    14 |   142   (5)| 00:00:02 |
|   1 |  SORT AGGREGATE    |          |     1 |    14 |            |          |
|*  2 |   TABLE ACCESS FULL| BOOKINGS | 16667 |   227K|   142   (5)| 00:00:02 |
-------------------------------------------------------------------------------

会出现什么情况？执行计划进行的是全表扫描，而非使用索引。注意“Rows”下方的值（16667），它是优化程序预期的表中行数。很明显那是错的。我们应该预期只有 5106 行，毫无疑问，是膨胀的行数诱使优化程序倾向于全表扫描。预期行数高的原因是优化程序对 HOTEL_ID 和 RATE_CATEGORY 列分别进行估算以计算估计的行数，因为它假设这两列之间没有关系。如果优化程序将两列一起进行考虑，它就会明白这一模式并得出一个更好的计划。

我们如何解决这一问题？Oracle 数据库 11g 提供了一种新型的统计信息，称为多列统计信息，它是扩展统计信息的一种。使用这一特性，您现在可以在不同的列之间创建关联（列组），以帮助优化程序作出更好的决定。我们通过下例来看一下该特性的使用。您可以通过两种方式来定义多列统计信息：

第一种方式是定义列组中包含的列。在本例中，我们需要包含 HOTEL_ID 和 RATE_CATEGORY。提供的程序包 dbms_stats 中的新函数 create_extended_stats 可以完成该工作。该函数返回您可能想要显示的列组名。一旦创建了列组，下次在您收集表的统计信息时，将会自动收集您的列组的多列统计信息。
```
SQL> var ret varchar2(2000)
SQL> exec :ret := dbms_stats.create_extended_stats('ARUP', 'BOOKINGS','(HOTEL_ID, RATE_CATEGORY)');
SQL> print ret
```

另一种方法是，您可以将列组定义为 gather statistics 命令的一部分。具体做法是，将这些列放到 dbms_stats 程序包的 gather_table_stats 过程的 method_opt 参数中，如下所示：

begin
   dbms_stats.gather_table_stats (
      ownname         => 'ARUP',
      tabname         => 'BOOKINGS',
      estimate_percent=> 100,
      method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS       (HOTEL_ID,RATE_CATEGORY)',
       cascade         => true
   );
end;
/

收集完多列统计信息后，再次执行查询并检查自动跟踪的输出结果：

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    14 |   132   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE              |                |     1 |    14 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOOKINGS       |  5106 | 71484 |   132   (1)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IN_BOOKINGS_01 |  5106 |       |    13   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access(HOTEL_ID=20 AND RATE_CATEGORY=21)

该输出清晰地显示索引 IN_BOOKINGS_01 已使用。为什么现在使用了索引？注意“Rows”列下方的值 (5106)。优化程序正确地确定了值组合的行数的估计值，而非分开的各个值的行数的估计值。

为确保优化程序仍然能够作出正确的决定，试一下 hotel_id = 10 和 rate_category = 12 构成的组合，此组合应当进行全表扫描：

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    14 |   143   (5)| 00:00:02 |
|   1 |  SORT AGGREGATE    |          |     1 |    14 |            |          |
|*  2 |   TABLE ACCESS FULL| BOOKINGS | 39835 |   544K|   143   (5)| 00:00:02 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(RATE_CATEGORY=12 AND HOTEL_ID=10)

如您所见，优化程序准确地预测了行数，因此选择了正确的全表扫描计划。如果您输入了一个并不存在的组合（hotel_id=10 和 rate_category=21），该预测对优化程序也是有帮助的。

在视图 DBA_STAT_EXTENSIONS 中，您可以看到在数据库中定义的扩展统计信息：

SQL> select extension_name, extension
  2  from dba_stat_extensions
  3  where table_name='BOOKINGS';
 
EXTENSION_NAME                 EXTENSION
------------------------------ ----------------------------
SYS_STUW3MXAI1XLZHCHDYKJ9E4K90 (HOTEL_ID,RATE_CATEGORY)

要删除多列统计信息，使用在扩展统计信息中介绍的相同的方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15779287/viewspace-691039/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15779287/viewspace-691039/

com20140604

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
11g 统计信息的审核和预发布

正在审核的统计信息考虑这样一种情况：您已经仔细收集了关于所有表、索引等的统计信息，同时所有查询都有完善的执行计划。但现在有人参与了统计信息收集工作，执行计划一下子变得截然不同。没错，这些计划可能更好（但也可能更糟，虽然概率低些）...
复制链接

扫一扫