ITPub博客

首页 > 数据库 > Oracle > 测试收集统计信息内部行为

测试收集统计信息内部行为

原创 Oracle 作者:liiinuuux 时间:2014-01-28 17:21:18 0 删除 编辑
创建测试数据
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可以发现,如果不定期收集统计信息的话,一些基表会无限制增长。

























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

请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    309077