Re: col_usage$ question

At 11:04 AM 11/18/2003, you wrote:
>Hi!
>
>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
>example.
>
>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
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).