分类： Oracle
2012-12-05 17:33:12
参考col_usage$中的predicate columns记录:
Cksxm.c
Monitor Modification Hash Table Base
modification hash table entry
modification hash table chunk
monitoring column usage element
ksxmlock_1
lock table sys.col_usage$ in exclusive mode
lock table sys.col_usage$ in exclusive mode nowait
update sys.col_usage$
set equality_preds = equality_preds +
decode(bitand(:flag, 1), 0, 0, 1),
equijoin_preds = equijoin_preds +
decode(bitand(:flag, 2), 0, 0, 1),
nonequijoin_preds = nonequijoin_preds +
decode(bitand(:flag, 4), 0, 0, 1),
range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1),
like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1),
null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1),
timestamp = :time
where obj# = :ob jn
and intcol# = :coln
insert into sys.col_usage$
(obj#,
intcol#,
equality_preds,
equijoin_preds,
nonequijoin_preds,
range_preds,
like_preds,
null_preds,
timestamp)
values
(:objn,
:coln,
decode(bitand(:flag, 1), 0, 0, 1),
decode(bitand(:flag, 2), 0, 0, 1),
decode(bitand(:flag, 4), 0, 0, 1),
decode(bitand(:flag, 8), 0, 0, 1),
decode(bitand(:flag, 16), 0, 0, 1),
decode(bitand(:flag, 32), 0, 0, 1),
:time)
使用dbms_stats的’SIZE AUTO’模式收集表上的统计信息会首先参考col_usage$中的predicate columns记录:
SQL> begin
2
3 dbms_stats.gather_table_stats(ownname => 'SYS',
4 tabname => 'MACLEAN',
5 method_opt => 'FOR ALL COLUMNS SIZE AUTO');
6 end;
7 /
PL/SQL procedure successfully completed.
============10046 level 12 trace content======================
SELECT /*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2)
index(ci_obj#) index(cu i_col_usage$)
index(h i_hh_obj#_intcol#) */
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,
(T.ROWCNT - H.NULL_CNT) / GREATEST(H.DISTCNT, 1) H_PFREQ,
C.LENGTH COL_LEN,
CU.TIMES TAMP 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.USE R$ U,
SYS.OBJ$ O,
SYS.TAB$ T,
SYS.COL$ C,
SYS.COL_USAGE$ CU,
SYS.HIST_HEAD$ H
WHERE :B3 = '0'
AND U.NAME = :B2
AND O.OWNER# = U.USER#
AND O.TYPE# = 2
AND O.NAME = :B1
AND O.OBJ# = T.OBJ#
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#(+)
UNION ALL
SELECT /*+
ordered use_nl(c) */
C.KQFCONAM COL_NAME,
C.KQFCODTY COL_TYPE,
DECODE(C.KQFCODTY, 1, 1, 0) COL_CSF,
NULL COL_DEF,
0 COL_NULL,
0 COL_PROP,
C.KQFCOCNO COL_UNUM,
C.KQFCOC NO COL_INUM,
O.KQFTAOBJ COL_OBJ,
DECODE(C.KQFCODTY, 2, -127, 0) COL_SCALE,
H.BUCKET_CNT H_BCNT,
(ST.ROWCNT - NULL_CNT) / GREATEST(H.DISTCNT, 1) H_PFREQ,
DECODE(C.KQFCODTY, 2, 22, C.KQFCOSIZ) 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.X$KQFTA O,
SYS.TAB_STATS$ ST,
SYS.X$KQFCO C,
SYS.COL_USAGE$ CU,
SYS.HIST_HEAD$ H
WHERE :B3 != '0'
AND :B2 = 'SYS'
AND O.KQFTANAM = :B1
AND O.KQFTAOBJ = ST.OBJ#(+)
AND O.KQFTAOBJ = C.KQFCOTOB
AND C.KQFCOTOB = CU.OBJ#(+)
AND C.KQFCOCNO = CU.INTCOL#(+)
AND C.KQFCOTOB = H.OBJ#(+)
AND C.KQFCOCNO = H.INTCO L#(+) oracle培训http://www.cuug.com/
