Smon process and maintenance of col_usage$ in CBO

Starting with Oracle9i, an object col_usage$ is introduced to record column usage information at run time to monitor column usage information.

This part of the information is maintained by Smon, so don't be surprised when you see Smon the associated deadlock and error, Smon is getting more and more mixed up.

In oracle10g, the structure of the table is as follows:

CREATE TABLE col_usage$

(

obj# number,/* Object Number * *

intcol# number,/* Internal Column number * *

Equality_preds number,/* Equality predicates * *

Equijoin_preds number,/* equijoin predicates * *

Nonequijoin_preds number,/* nonequijoin predicates * *

Range_preds number,/* RANGE predicates * *

Like_preds number,/* (not) like predicates * *

Null_preds number,/* (NOT) NULL predicates */

Timestamp Date/* Timestamp of last time this row is changed * *

)

Storage (initial 200K next 100k maxextents Unlimited pctincrease 0)

/

Create unique index i_col_usage$ on col_usage$ (obj#,intcol#)

Storage (Maxextents Unlimited)

/

Note that each of these options is meaningful, such as Maxextents Unlimited because col_usage$ tables may be overly extensible in space design.

Today, in a busy database of customers, you see the SQL of operations on this table, and the number of executions is very frequent, following are 3 sql:

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# =: Objn and intcol# =: Coln;

INSERT into sys.col_usage$

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, 0, 0, 1),: time);

of the following 1-hour reports, 3 SQL was executed thousands of times:

Parse callsexecutions% Total Parsessql idsql modulesql Text

9864,0750.713c1kubcdjnppq

Update sys.col_usage$ Set EQ ...

986690.7153btfq0dt9bs9

Insert INTO sys.col_usage$ val ...

9869860.71b2gnxm5z6r51n

Lock table sys.col_usage$ in E ...

The associated maintenance SQL is also:

Delete from sys.col_usage$ C where isn't exists (select 1 from sys.obj$ o where o.obj# = c.obj#)

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/