oracle清除掉统计量,Oracle 11g 数据统计量Pending处理

5、Pending统计量的后续处理

在对pending统计量进行合理评估之后，DBA是可以做出删除还是发布统计量的决定的。具体操作如下：

--删除pending信息

SQL> exec dbms_stats.delete_pending_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select count(*) from user_tab_pending_stats;

COUNT(*)

----------

0

--重新收集pending统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

---------- ------------ ---------- ---------- -----------

0           0         0         0          0

--发布pending统计量

SQL> exec dbms_stats.publish_pending_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

---------- ------------ ---------- ---------- -----------

72202        1028         0         0         96

单发布完统计量之后，就可以在正常的情况下使用统计量生成执行计划了。

SQL> show parameter optimizer_use_pen

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_pending_statistics    boolean    FALSE

SQL> alter session set optimizer_use_pending_statistics=false;

会话已更改。

已用时间: 00: 00: 00.01

SQL> select * from t where wner='SYS';

已选择58799行。

已用时间: 00: 00: 04.33

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------

|  0 | SELECT STATEMENT |     | 58794 | 5511K|  281  (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T   | 58794 | 5511K|  281  (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

统计信息

----------------------------------------------------------

426 recursive calls

0 db block gets

4975 consistent gets

0 physical reads

0 redo size

2392962 bytes sent via SQL*Net to client

43520 bytes received via SQL*Net from client

3921 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

58799 rows processed

6、结论

在11g中提出的pending statistic的方法，可以在生产运维和稳定优化执行计划方面，给我们提供帮助。