清除统计信息的内部操作-DELETE_COLUMN_STATS

Oracle在col_usage$中，缺省的会自动收集统计信息，其来源取决于SQL的查询监控。

以下是col_usage$中记录的信息：

SQL> create user eygle identified by eygle;

User created.

SQL> grant connect,resource,dba to eygle;

Grant succeeded.

SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_tables;

Table created.
SQL> select object_name,object_id,data_object_id from dba_objects where object_name='EYGLE';

OBJECT_NAME          OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
EYGLE              21134      21134

SQL> select count(*) from eygle where owner='SYS';

  COUNT(*)
----------
       950

SQL> select count(*) from eygle where owner='SYS';

  COUNT(*)
----------
       950
SQL> select count(*) from eygle where tablespace_name='USERS';

  COUNT(*)
----------
    18

SQL> select count(*) from eygle where tablespace_name='USERS';

  COUNT(*)
----------
    18

当执行多次查询后，可以尝试收集统计信息，Oracle会记录列级别的统计信息：

SQL> exec dbms_stats.gather_table_stats(user,'EYGLE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'EYGLE');

PL/SQL procedure successfully completed.

SQL> select * from sys.col_usage$ where obj#=21134;

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
     21134        1           2          0            0        0       0          0 15-NOV-11
     21134        3           2          0            0        0       0          0 15-NOV-11

使用delete_column_stats可以清除掉这些信息，跟踪一下后台操作：

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> exec dbms_stats.delete_column_stats(user,'EYGLE','OWNER');

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Session altered.

我们看看核心的内部操作，主要就是从hist_head$和histogram$中删除相关字段的统计信息内容：

********************************************************************************

SQL ID: 6dy6jp6fmwnzf Plan Hash: 875224318

delete from hist_head$
where
 obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          5           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          5           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  HIST_HEAD$ (cr=3 pr=0 pw=0 time=470 us)
         1          1          1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=26 us cost=1 size=11 card=1)(object id 427)

********************************************************************************

SQL ID: 9awczgxd4s98v Plan Hash: 1844266620

delete from histgrm$
where
 obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3         48          12
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3         48          12

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  HISTGRM$ (cr=3 pr=0 pw=0 time=2045 us)
        12         12         12   TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=67 us cost=2 size=66 card=6)
         1          1          1    INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=18 us cost=1 size=0 card=1)(object id 422)

********************************************************************************

-The End-