创建测试数据

conn scott/tiger

CREATE TABLE PART_TEST
(
OWNER     VARCHAR2(30),
OBJECT_NAME  VARCHAR2(30),
SUBOBJECT_NAME      VARCHAR2(30),
OBJECT_ID   NUMBER,
DATA_OBJECT_ID      NUMBER,
OBJECT_TYPE         VARCHAR2(19),
CREATED    DATE,
LAST_DDL_TIME DATE,
TIMESTAMP           VARCHAR2(19),
STATUS              VARCHAR2(7),
TEMPORARY           VARCHAR2(1),
GENERATED           VARCHAR2(1),
SECONDARY           VARCHAR2(1)
)
PARTITION BY HASH(OWNER) PARTITIONS 3;
ALTER TABLE PART_TEST  ADD (PRIMARY KEY(OBJECT_ID));

insert into scott.part_test select * from all_objects;
commit;
alter session set events '10046 trace name context forever, level 12';
exec dbms_stats.gather_table_stats('SCOTT', 'PART_TEST', 'SYS_P41');
alter session set events '10046 trace name context off';

分析trace文件

trace中有大量insert，少量update和delete。

这些DML及操作的表：

[oracle@host3 tmp]$ < prod_ora_17952.trc grep -nE "update|insert|delete"  | awk '{print $1,$2,$3}'
4182:delete from sys.mon_mods$
4327:delete from sys.mon_mods_all$
4488:delete from sys.mon_mods$
4503:delete from sys.mon_mods_all$
4745:insert into sys.wri$_optstat_tab_history(obj#,rowcnt,blkcnt,avgrln,
4857:delete from superobj$
4872:delete from tab_stats$
4887:update tab$ set
5083:delete from tab_stats$
5098:update tabpart$ set
5566:insert into sys.wri$_optstat_histhead_history(obj#,intcol#,savtime,flags,
5571:insert into sys.wri$_optstat_histhead_history
5964:insert into sys.wri$_optstat_histgrm_history
6560:insert into hist_head$(obj#,
6758:insert into hist_head$(obj#,
6859:insert into hist_head$(obj#,
6960:insert into hist_head$(obj#,
7063:insert into hist_head$(obj#,
7166:insert into hist_head$(obj#,
7267:insert into hist_head$(obj#,
7370:insert into hist_head$(obj#,
7471:insert into hist_head$(obj#,
7572:insert into hist_head$(obj#,
7676:insert into hist_head$(obj#,
7779:insert into hist_head$(obj#,
7881:insert into hist_head$(obj#,
7982:insert into hist_head$(obj#,
8083:insert into hist_head$(obj#,
8184:insert into hist_head$(obj#,
8287:insert into hist_head$(obj#,
8390:insert into hist_head$(obj#,
8491:insert into hist_head$(obj#,
8593:insert into hist_head$(obj#,
8694:insert into hist_head$(obj#,
8795:insert into hist_head$(obj#,
8896:insert into hist_head$(obj#,
8997:insert into hist_head$(obj#,
9102:insert into hist_head$(obj#,
9203:insert into hist_head$(obj#,
11402:insert into sys.wri$_optstat_ind_history(obj#,rowcnt,leafcnt,distkey,
11485:delete from superobj$
11500:delete from tab_stats$
11515:update tab$ set
11790:delete from ind_stats$
11805:update ind$ set
12166:delete from ind_online$

下面通过这些DML，简单分析oracle的行为：

1 oracle首先要从mon_mods$和mon_mods_all$中删除obj#为482、484、52797。

即：删除了mon_mods$和mon_mods_all$本身相关记录和PART_TEST表相关记录。

从oracle售后人员的博客查到，这两张表由SMON维护，用来记录数据表自上一次收集统计信息后发生DML的次数：SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$,同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中，并清空原MON_MODS$中的数据。

desc mon_mods_all$
Name                                           Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
OBJ#                                                 NUMBER
INSERTS                                              NUMBER
UPDATES                                              NUMBER
DELETES                                              NUMBER
TIMESTAMP                                            DATE
FLAGS                                                NUMBER
DROP_SEGMENTS                                        NUMBER

2 然后oracle向wri$_optstat_tab_history中插入了一条数据：

insert into sys.wri$_optstat_tab_history(obj#,rowcnt,blkcnt,avgrln, analyzetime,samplesize,cachedblk,cachehit,logicalread,savtime,flags) values  (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)

这条语句只有第一个参数绑定了变量obj#=52798（上面分析的PART_TEST.SYS_P41分区），其它的值都绑定空值。

3 从superobj$删除obj#=52797（PART_TEST表）

这张表的定义在sql.bsq中：

[oracle@host3 admin]$ pwd
/home/oracle/product/11_2/db_1/rdbms/admin

[oracle@host3 admin]$ grep -in "create table superobj" *
i0801070.sql:109:create table superobj$           /* stores info about table/view hierarchies */
sql.bsq:1825:create table superobj$        /* stores info about table/view hierarchies */

具体建表语句：

create table superobj$        /* stores info about table/view hierarchies */
( subobj#         number not null,            /* object number of sub object */
  superobj#       number not null)          /* object number of super object */
/

这张表一般不是空的，就是只有一两条记录，可见只是临时存放一些数据用的。

4 从tab_stats$删除PART_TEST表相关记录

5 更新tab$表中PART_TEST表相关记录

6 从tab_stats$表删除obj#52798（PART_TEST.SYS_P41分区）相关记录

7 更新tabpart$表中obj#52798（PART_TEST.SYS_P41分区）相关记录

8 用insert into wri$_optstat_histhead_history select .... from hist_head$的形式向wri$_optstat_histhead_history插入了数据，10046没有抓到绑定变量。

9 向wri$_optstat_histhead_history中插入obj#=253（柱状图基表HISTGRM$）相关数据

10 向wri$_optstat_histgrm_history插入数据，绑定变量为52798（PART_TEST.SYS_P41分区），1和空值

insert into sys.wri$_optstat_histgrm_history (obj#,intcol#,savtime,bucket, endpoint,epvalue) select hg.obj#,hg.intcol#,:3,hg.bucket,hg.endpoint, hg.epvalue from sys.histgrm$ hg where hg.obj# = :1 and hg.intcol# = :2

11 向hist_head$插入多条记录。

这些语句的第一个绑定变量为obj#，除最后一条没有抓到绑定变量外，其它语句全是关于52797（PART_TEST表）和52798（PART_TEST.SYS_P41分区）的

12 向sys.wri$_optstat_ind_history插入一条数据，关于52801（表PART_TEST的主键索引）

13 delete from superobj$ where subobj# = 52797（PART_TEST表）

14 delete from tab_stats$ where obj#= 52797（PART_TEST表）

15 更新tab$中52797（PART_TEST表）相关数据

16 delete from ind_stats$ where obj#=52801（表PART_TEST的主键索引）

17 更新ind$中52801（表PART_TEST的主键索引）相关信息

18 delete from ind_online$ where obj#=52801（表PART_TEST的主键索引）

ind_online$字典基表记录了索引在线创建/重建的历史

统一看一下上面DML中的obj#都代表什么：

select object_id, object_type, object_name, subobject_name from dba_objects
where object_id in (253, 482, 484, 52797, 52798, 52801);

OBJECT_ID OBJECT_TYPE            OBJECT_NAME                SUBOBJECT_NAME
---------- ------------------- ------------------------------ ------------------------------
       484 TABLE            MON_MODS_ALL$
       482 TABLE            MON_MODS$
       253 TABLE            HISTGRM$
     52801 INDEX            SYS_C005500
     52798 TABLE PARTITION  PART_TEST                SYS_P41
     52797 TABLE            PART_TEST

6 rows selected.

select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where index_name = 'SYS_C005500';

INDEX_NAME                 TABLE_NAME               COLUMN_NAME
-------------------------- ------------------------ ------------------------------
SYS_C005500                PART_TEST                OBJECT_ID

另外，根据上面的dml可以发现，如果不定期收集统计信息的话，一些基表会无限制增长。