$col_usage何时被清空~

zergduan · 发表于 2008-12-5 07:09

使用columns size auto来收集直方图时，oracle将更具$col_usage来判断何时来收集skew列的直方图，请问有什么条件可以清空$col_usage表？有文档说不要在刚刚重启动后使用columns size auto。但是我试验的结果是$col_usage并没有在重启动oracle后被清空，请问什么条件下这个$col_usage会被清空呢？

Yong Huang · 发表于 2008-12-5 13:38

This is supposed to do it:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

But I tried it in 10.2.0.1. It only slightly reduces the number of rows in col_usage$.

Make sure you write the table name correctly. And always tell us Oracle version.

Yong Huang

zergduan · 发表于 2008-12-5 17:20

谢谢，斑竹
9.2.0.8

这个表和sys.dba_tab_modifications一样来自于share_pool,都是用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
来刷新~，但是我问得不是如何刷新这个表，我想问如何清空这个纪录

这个$col_usage是用来判断倾斜列，是否需要收集直方图。

比如

有一个表A的列a有3个值 x y z,共1000行，x占500行，那么这个列是一个倾斜列~ 如果用for column a size skewonly来收集，肯定会产生一个高度直方图。但是如果使用for column a size auto来收集，oracle不一定要在上面建立直方图，因为skewonly 和auto判断是有区别的。

auto需要列倾斜且列有workload才会收集直方图。

所谓的workload就记录在$col_usage这个表中

它记录着这个列在sql中当作条件的使用方法~

具体请看我的下面的帖子~

但是，现在的问题是，如何清空这个表，重新记录workload呢？

[ 本帖最后由 zergduan 于 2008-12-5 17:21 编辑 ]

zergduan · 发表于 2008-12-5 17:27

SQL> create table histest as select rownum a from test;

Table created

SQL> desc histest;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
A NUMBER Y

SQL> drop table histest;

Table dropped

SQL> create table histest as select rownum a from test;

Table created

SQL> select count(*) from histest;

  COUNT(*)
----------
   6235

SQL> delete from histest where a between 1001 and 6234;

5234 rows deleted

SQL> select count(*) from histest;

  COUNT(*)
----------
   1001

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      76 A

--HISTEST.A是由1至1000连续数字和一个数字6235组成，当使用for all columns size skewonly收集histograms时,oracle认为a列是倾斜的用了75个桶收集了柱状图.

--但是用for all columns size auto确是另一个结果；

SQL> EXEC DBMS_STATS.DELETE_table_stats(ownname => 'MYDB',tabname => 'HISTEST');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      2 A
--oracle不认为histest.a倾斜，没有收集柱状图....

--oracle online文档上写着:
AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns.
可以看到两个方式的区别是auto把workload考虑进去了.

对两种收集方式作10046 trace

有意思的是我发现两个trac文件中都有sql_3
SELECT /*+ rule */ C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETFORM COL_CSF,
  C.DEFAULT$ COL_DEF, C.NULL$ COL_NULL, C.PROPERTY COL_PROP, C.COL# COL_UNUM,
  C.INTCOL# COL_INUM, C.OBJ# COL_OBJ, C.SCALE COL_SCALE, H.BUCKET_CNT H_BCNT,
  H.DISTCNT H_PNDV, C.LENGTH COL_LEN, CU.TIMESTAMP CU_TIME, CU.EQUALITY_PREDS
  CU_EP, CU.EQUIJOIN_PREDS CU_EJP, CU.RANGE_PREDS CU_RP, CU.LIKE_PREDS CU_LP,
  CU.NONEQUIJOIN_PREDS CU_NEJP, CU.NULL_PREDS NP
FROM
SYS.USER$ U, SYS.OBJ$ O, SYS.COL$ C, SYS.COL_USAGE$ CU, SYS.HIST_HEAD$ H
  WHERE U.NAME = :B2 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O.NAME = :B1
  AND O.OBJ# = C.OBJ# AND C.OBJ# = CU.OBJ#(+) AND C.INTCOL# = CU.INTCOL#(+)
  AND C.OBJ# = H.OBJ#(+) AND C.INTCOL# = H.INTCOL#(+)
里面有一个表SYS.COL_USAGE$

在网上找到了这个解释
col_usage$
This table allows to monitor the usage of predicates on columns in select statements. It is updated (if _column_tracking_level is set to 1) at intervalls by smon, so it might be a little out of date. Also, dbms_stats will make use of that info when deciding if it needs to create a histogram on a column.

看样子这个应该就是workload of columns的来源~
select
  o.name,
  c.name,
  u.equality_preds,
  u.equijoin_preds,
  u.nonequijoin_preds,
  u.range_preds,
  u.like_preds,
  u.null_preds
from
      sys.col_usage$ u
  join  sys.obj$    o    on u.obj# = o.obj#
  join  sys.col$    c    on u.obj# = c.obj# and u.intcol# = c.col#;

SQL> select
  2 x.ksppinm  name,
  3 y.ksppstvl  value,
  4 y.ksppstdf  isdefault,
  5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
  7  from
  8 sys.x$ksppi x,
  9 sys.x$ksppcv y
10  where
11 x.inst_id = userenv('Instance') and
12 y.inst_id = userenv('Instance') and
13 x.indx = y.indx and
14 x.ksppinm like '_column_tracking_level'
15  /

NAME                         VALUE    ISDEFAULT ISMOD    ISADJ
------------------------------ ---------- --------- ---------- -----
_column_tracking_level       1       TRUE    FALSE    FALSE

这个参数默认就是1，也就是说oracle应该是默认就收集column的workload~

下面建立一个肯定包含倾斜列a的表histest2来测试

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as MYDB

SQL> create table histest2 as select rownum a from dba_objects;

Table created

SQL> update histest2 set a=3;

6242 rows updated

SQL> update histest2 set a=2 where rownum<2000;

1999 rows updated

SQL> update histest2 set a=1 where rownum<20;

19 rows updated

SQL> commit;

Commit complete

SQL> select count(*),a from histest2 group by a;

  COUNT(*)       A
---------- ----------
      19       1
   1980       2
   4243       3

SQL>
SQL> SELECT /*+ rule */
  2 C.NAME             COL_NAME,
  3 C.TYPE#             COL_TYPE,
  4 C.CHARSETFORM       COL_CSF,
  5 C.DEFAULT$          COL_DEF,
  6 C.NULL$             COL_NULL,
  7 C.PROPERTY          COL_PROP,
  8 C.COL#             COL_UNUM,
  9 C.INTCOL#          COL_INUM,
10 C.OBJ#             COL_OBJ,
11 C.SCALE             COL_SCALE,
12 H.BUCKET_CNT       H_BCNT,
13 H.DISTCNT          H_PNDV,
14 C.LENGTH          COL_LEN,
15 CU.TIMESTAMP       CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS    CU_RP,
19 CU.LIKE_PREDS       CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS       NP
22 FROM SYS.USER$    U,
23       SYS.OBJ$    O,
24       SYS.COL$    C,
25       SYS.COL_USAGE$ CU,
26       SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28    AND O.OWNER# = U.USER#
29    AND O.TYPE# = 2
30    AND O.NAME = 'HISTEST2'
31    AND O.OBJ# = C.OBJ#
32    AND C.OBJ# = CU.OBJ#(+)
33    AND C.INTCOL# = CU.INTCOL#(+)
34    AND C.OBJ# = H.OBJ#(+)
35    AND C.INTCOL# = H.INTCOL#(+)
36    AND C.NAME='A'
37  /

COL_NAME                      COL_TYPE COL_CSF COL_DEF                                                                         COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ  COL_SCALE    H_BCNT    H_PNDV COL_LEN CU_TIME       CU_EP    CU_EJP    CU_RP    CU_LP CU_NEJP       NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A                                     2       0                                                                                        0       0       1       1    6563                                        22

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST2',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST2' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      2 A

--没有收集直方图

SQL> SELECT COUNT(*) FROM HISTEST2 WHERE A=1;

  COUNT(*)
----------
      19
--做一个等值查询

--然后到sys用户下用oradebug wakeup唤醒一下smon,不过唤醒了也等了1分钟后才有下面的结果
SQL> SELECT /*+ rule */
  2 C.NAME             COL_NAME,
  3 C.TYPE#             COL_TYPE,
  4 C.CHARSETFORM       COL_CSF,
  5 C.DEFAULT$          COL_DEF,
  6 C.NULL$             COL_NULL,
  7 C.PROPERTY          COL_PROP,
  8 C.COL#             COL_UNUM,
  9 C.INTCOL#          COL_INUM,
10 C.OBJ#             COL_OBJ,
11 C.SCALE             COL_SCALE,
12 H.BUCKET_CNT       H_BCNT,
13 H.DISTCNT          H_PNDV,
14 C.LENGTH          COL_LEN,
15 CU.TIMESTAMP       CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS    CU_RP,
19 CU.LIKE_PREDS       CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS       NP
22 FROM SYS.USER$    U,
23       SYS.OBJ$    O,
24       SYS.COL$    C,
25       SYS.COL_USAGE$ CU,
26       SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28    AND O.OWNER# = U.USER#
29    AND O.TYPE# = 2
30    AND O.NAME = 'HISTEST2'
31    AND O.OBJ# = C.OBJ#
32    AND C.OBJ# = CU.OBJ#(+)
33    AND C.INTCOL# = CU.INTCOL#(+)
34    AND C.OBJ# = H.OBJ#(+)
35    AND C.INTCOL# = H.INTCOL#(+)
36    AND C.NAME='A'
37  /

COL_NAME                      COL_TYPE COL_CSF COL_DEF                                                                         COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ  COL_SCALE    H_BCNT    H_PNDV COL_LEN CU_TIME       CU_EP    CU_EJP    CU_RP    CU_LP CU_NEJP       NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A                                     2       0                                                                                        0       0       1       1    6563                   1       3       22 2008-11-22          1       0       0       0       0       0
--CU_EP增加了1

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST2',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST2' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      3 A
--重新用auto分析，发现生成了直方图。

////////////////////////////////////////////////////

看样子auto的确是靠col_usage$来判断列的workload的.

/////////////////////////////////////////////////////////////

histest上的直方图应该是一个频率是方图，这个直方图应该应用于<or>连接的查询，所以col_usage$中必须range_preds有值才算有workload~

SQL> drop table histest;

Table dropped

SQL> create table histest as select rownum a from test;

Table created

SQL> delete from histest where a between 1001 and 6234;

5234 rows deleted

SQL> select count(*) from histest;

  COUNT(*)
----------
   1001

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      2 A

SQL>
SQL> SELECT /*+ rule */
  2 C.NAME             COL_NAME,
  3 C.TYPE#             COL_TYPE,
  4 C.CHARSETFORM       COL_CSF,
  5 C.DEFAULT$          COL_DEF,
  6 C.NULL$             COL_NULL,
  7 C.PROPERTY          COL_PROP,
  8 C.COL#             COL_UNUM,
  9 C.INTCOL#          COL_INUM,
10 C.OBJ#             COL_OBJ,
11 C.SCALE             COL_SCALE,
12 H.BUCKET_CNT       H_BCNT,
13 H.DISTCNT          H_PNDV,
14 C.LENGTH          COL_LEN,
15 CU.TIMESTAMP       CU_TIME,
16 CU.EQUALITY_PREDS CU_EP,
17 CU.EQUIJOIN_PREDS CU_EJP,
18 CU.RANGE_PREDS    CU_RP,
19 CU.LIKE_PREDS       CU_LP,
20 CU.NONEQUIJOIN_PREDS CU_NEJP,
21 CU.NULL_PREDS       NP
22 FROM SYS.USER$    U,
23       SYS.OBJ$    O,
24       SYS.COL$    C,
25       SYS.COL_USAGE$ CU,
26       SYS.HIST_HEAD$ H
27 WHERE U.NAME = 'MYDB'
28    AND O.OWNER# = U.USER#
29    AND O.TYPE# = 2
30    AND O.NAME = 'HISTEST'
31    AND O.OBJ# = C.OBJ#
32    AND C.OBJ# = CU.OBJ#(+)
33    AND C.INTCOL# = CU.INTCOL#(+)
34    AND C.OBJ# = H.OBJ#(+)
35    AND C.INTCOL# = H.INTCOL#(+)
36    AND C.NAME='A'
37  /

COL_NAME                      COL_TYPE COL_CSF COL_DEF                                                                         COL_NULL COL_PROP COL_UNUM COL_INUM COL_OBJ  COL_SCALE    H_BCNT    H_PNDV COL_LEN CU_TIME       CU_EP    CU_EJP    CU_RP    CU_LP CU_NEJP       NP
------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
A                                     2       0                                                                                        0       0       1       1    6568                   1    1001       22

SQL> select count(*) from histest where a<900;

  COUNT(*)
----------
   899

SQL> exec dbms_stats.gather_table_stats(ownname => 'MYDB',tabname => 'HISTEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed

SQL> SELECT COUNT(*) ,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME='HISTEST' GROUP BY COLUMN_NAME;

  COUNT(*) COLUMN_NAME
---------- --------------------------------------------------------------------------------
      76 A

--有直方图了~,看样子auto还是非常智能的~

ps：col_usage$的值同样使用EXEC DBMS_STATS.flush_database_monitoring_info;来刷新就可以~

这个是我在ｏｒａｃｌｅｆａｎｓ中的一个帖子，遗留了２个问题。

1。如何清空$col_usage来重新收集workload(有可能是修改隐含参数_column_tracking_level =false)
2。为何skewonly的10046中还需要查询$col_usage？按理说他不需要workload来判断。

如果要看完整的帖子请看

http://www.ｏｒａｃｌｅｆａｎｓ.cn/forum/showtopic.jsp?rootid=9652&CPages=1

Yong Huang · 发表于 2008-12-6 02:07

Please STOP CALLING THE TABLE $col_usage. IT IS col_usage$!!!

Setting _column_tracking_level to false stops inserting new rows to the table. But it doesn't delete existing rows.

In v$sql% view(s), you'll see a SQL like
delete from sys.col_usage$ c where not exists (select 1 from sys.obj$ o where o.obj# = c.obj# )

I believe SMON is doing that. It's a SQL run during database shutdown or when anybody runs DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. It deletes all rows in col_usage$ for which the table object no longer exists (not even exists in recyclebin if > 9i).

There's no documented method to purge col_usage$. We can't delete rows directly because it may corrupt row cache. We can indirectly delete rows by dropping the table (with purge option in 10g) and call DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO.

Yong Huang

[ 本帖最后由 Yong Huang 于 2008-12-5 22:53 编辑 ]

zergduan · 发表于 2008-12-6 11:25

原帖由 Yong Huang 于 2008-12-6 02:07 发表
Please STOP CALLING THE TABLE $col_usage. IT IS col_usage$!!!

Setting _column_tracking_level to false stops inserting new rows to the table. But it doesn't delete existing rows.

In v$sql% view(s), you'll see a SQL like
delete from sys.col_usage$ c where not exists (select 1 from sys.obj$ o where o.obj# = c.obj# )

I believe SMON is doing that. It's a SQL run during database shutdown or when anybody runs DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. It deletes all rows in col_usage$ for which the table object no longer exists (not even exists in recyclebin if > 9i).

There's no documented method to purge col_usage$. We can't delete directly because it may corrupt row cache.

Yong Huang

谢谢斑竹~

sqysl · 发表于 2008-12-6 14:44

2。为何skewonly的10046中还需要查询$col_usage？按理说他不需要workload来判断。

感谢两位深入精彩的论述，针对第二个问题，在skewonly情况下，表col_usage$中的数据是否也会对生成的直方图有影响呢？
也就是说，虽然col_usage$表中有没有数据都会生成直方图，但是否会是这样一种情况：col_usage$表中有数据会生成一个直方图，而没数据会生成另外一个不一样的直方图，甚至col_usage$中数据的不同会造成直方图的不同。

zergduan · 发表于 2008-12-7 10:36

原帖由 sqysl 于 2008-12-6 14:44 发表
2。为何skewonly的10046中还需要查询$col_usage？按理说他不需要workload来判断。

感谢两位深入精彩的论述，针对第二个问题，在skewonly情况下，表col_usage$中的数据是否也会对生成的直方图有影响呢？
也就是说，虽然col_usage$表中有没有数据都会生成直方图，但是否会是这样一种情况：col_usage$表中有数据会生成一个直方图，而没数据会生成另外一个不一样的直方图，甚至col_usage$中数据的不同会造成直方图的不同。

先明确两点
1。col_usage$只对auto是否生成直方图其作用。
2。skewonly只是查询了col_usage$，并没有因为里面的值而对产生的直方图有影响~

所以如果col_usage$里有特定的数据，使用auto生成的直方图类型和skewonly生成的直方图类型有可能是不一样的~
生成什么直方图是由distinct值决定的，skewonly默认使用75，也就是说如果你的列distinc值超过75 但是小于255，那么auto和skewony生成的直方图是不一样的，如果小于75，那就是一样的~

Yong Huang · 发表于 2008-12-7 23:25

Another guess why col_usage$ is queried with skewonly method_opt is that Oracle kernel developers simply don't want to add more code to check. It's not a big deal to leave that light-weight query in the Oracle code. Why bother? After all, of 1 million Oracle 10g or 11g databases installed in the world, 99% of them are running with auto option.

Yong Huang

[ 本帖最后由 Yong Huang 于 2008-12-7 09:41 编辑 ]

heuet032606 · 发表于 2014-7-7 22:35

大家都对ORACLE了解到这个程度了。。。