« 希腊游记 - 雅典 众神的府邸 | Blog首页 | Oracle DataGuard跨平台支持列表 - 2011 »
清除统计信息的内部操作-DELETE_COLUMN_STATS
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/11/delete_column_stats.html
Oracle在col_usage$中,缺省的会自动收集统计信息,其来源取决于SQL的查询监控。链接:https://www.eygle.com/archives/2011/11/delete_column_stats.html
以下是col_usage$中记录的信息:
SQL> create user eygle identified by eygle;当执行多次查询后,可以尝试收集统计信息,Oracle会记录列级别的统计信息:
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
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文章:
>> 2004-11-15文章:
By eygle on 2011-11-15 12:24 | Comments (2) | SQL.PLSQL | 2895 |
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');
- 木匠.
1. 10g开始,缺省的_column_tracking_level=1,应该都是收集的
2.Monitor 复杂啊,我觉得不如10046来得畅快,哈哈