ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g统计信息方面增强(二)

11g统计信息方面增强(二)

原创 Linux操作系统 作者:yangtingkun 时间:2010-10-30 22:11:44 0 删除 编辑

在11g中,Oracle在统计信息方面进行了进一步的增强。

这篇介绍管理PENDING统计信息。

11g统计信息方面增强(一):http://yangtingkun.itpub.net/post/468/507713

 

 

前一篇文章简单介绍了PENDING统计信息,提到了用户可以在确认统计信息无害后,再将统计信息发布到数据字典中,而对于优化器而言,默认情况下是不使用PENDING统计信息的,那么如何才能确认统计信息无害呢,存在两种方法。

第一种就是利用初始化参数OPTIMIZER_USE_PENDING_STATISTICS,在系统级设置这个参数会影响整个数据库,因此要验证PENDING统计信息,可以在当前的会话级设置这个参数,检查当前会话运行业务SQL是否存在性能下降:

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T (ID NUMBER);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

已创建26行。

SQL> COMMIT;

提交完成。

SQL> ALTER TABLE T MODIFY (ID NOT NULL);

表已更改。

SQL> CREATE INDEX IND_T_ID ON T(ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- --------------
T                                      26          5 30-10
月-10

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'T', 'PUBLISH', 'FALSE')

PL/SQL 过程已成功完成。

SQL> DELETE T;

已删除26行。

SQL> INSERT INTO T SELECT ROWNUM FROM ALL_OBJECTS;

已创建69446行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TAB_PENDING_STATS
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- --------------
T                                   69446         54 31-10
月-10

SQL> SET AUTOT ON
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     69446


执行计划
----------------------------------------------------------
Plan hash value: 1615873360

---------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |          |     1 |            |          |
|   2 |   INDEX FULL SCAN| IND_T_ID |    26 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         70  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

会话已更改。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     69446


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 69446 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
        294  recursive calls
          0  db block gets
        113  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到,设置了初始化参数OPTIMIZER_USE_PENDING_STATISTICS为TRUE后,Oracle使用了PENDING统计信息,因此优化器不再使用索引扫描,而选择全表扫描执行计划,从执行计划的返回函数也可以看到,当前的PENDING统计信息已经生效。

另一种方法就是将PENDING统计信息导出,利用EXPORT_PENDING_STATS过程导出PENDING统计信息:

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(USER, 'T_STAT')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.EXPORT_PENDING_STATS(USER, 'T', 'T_STAT')

PL/SQL 过程已成功完成。

SQL> SET AUTOT OFF
SQL> SELECT TYPE, C1, N1 FROM T_STAT;

T C1                                     N1
- ------------------------------ ----------
T T                                  
69446
C T                                   69446
I IND_T_ID                            69446

随后可以将得到的统计信息导入到测试环境中来验证这个统计信息是否会导致系统性能下降。

如果发现收集的PENDING统计信息存在导致性能下降的可能性,不准备发布到数据字典,那么可以利用DELETE_PENDING_STATS过程来删除统计信息:

SQL> EXEC DBMS_STATS.DELETE_PENDING_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TAB_PENDING_STATS
  3  WHERE TABLE_NAME = 'T';

未选定行

 

 

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

上一篇: 新的开始
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795