eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 希腊游记 - 雅典 众神的府邸 | Blog首页 | Oracle DataGuard跨平台支持列表 - 2011 »

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

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-


历史上的今天...
    >> 2017-11-15文章:
    >> 2012-11-15文章:
    >> 2010-11-15文章:
    >> 2009-11-15文章:
    >> 2008-11-15文章:
    >> 2006-11-15文章:
    >> 2005-11-15文章:
           iSCSI节点名称定义及其他
           NetAPP iSCSI性能测试
    >> 2004-11-15文章:
           使用热备份进行分时恢复

By eygle on 2011-11-15 12:24 | Comments (2) | SQL.PLSQL | 2895 |

2 Comments

1) 问个问题, 没有表统计信息的时候, 是不是就不收集查询条件历史到col_usage$ ?

2) 换换清新空气, 退休10046, 到了使用DBMS_MONITOR的时候了. :)

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 524, serial_num => 20509, waits => TRUE, binds => True, plan_stat=>'ALL_EXECUTIONS');

* traces the current user session including waits and binds
EXECUTE sys.DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>TRUE, binds=>True, plan_stat=>'ALL_EXECUTIONS');

- 木匠.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com