Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: col_usage$ question

Re: col_usage$ question

From: Wolfgang Breitling <>
Date: Tue, 18 Nov 2003 12:59:32 -0800
Message-ID: <>

At 11:04 AM 11/18/2003, you wrote:
>If you describe this table then you see that this table stores column
>usage information in filter and join predicates for database objects. From
>describe, you see there are several filter and join conditions tracked for
>an object's (obj#) columns (intcol#). You can join them to col$ table for
>During shutdown, the session executing shutdown, writes the column usage
>statistics to col_usage$. During normal operations, it's SMON who's doing
>that over regular intervals.
>You can disable collecting these statistics by setting
>_column_tracking_level to 0.
>I don't really see where CBO could use those statistics for speeding up
>statement execution, because during execution CBO knows all the predicates
>& statement structure anyway. But it is probably useful for various 10g's
>advisories, which can make you recommendations based on how the tables
>(columns) are used. Also, it might help automatic statistics gathering to
>determine which stats need to be updated or not (this gather stale stuff).

Which statistics may be stale is tracked by SYS.MON_MODS$. col_usage$ tracks the use of columns as predicates, not any updates. It is currently (Oracle 9) used to decide if i might be worth gathering histogram information for a column - together with the determination if the data in the column is sufficiently skewed. No point in gathering histograms on non-skewed data or on data that is never referenced in a predicate. I can imagine that Oracle 10 will use that data to recommend indexes - as Daniel suggested.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
Author: Wolfgang Breitling

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Nov 18 2003 - 14:59:32 CST

Original text of this message