Re: monitor column usage or index usage?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 May 2004 09:35:11 +0100
Message-ID: <009e01c4340e$3a2ab380$7102a8c0@Primary>

What do you mean by 'working' ?

Column usage monitoring is enabled by
default. It doesn't "do" anything, it simply exists so that the AUTO option for generating histograms has some data of which columns might be appropriate for histograms.

If I recall correctly, the col_usage$ table is another of those things handled by smon every 5 minutes. If a hard parse of an sql statement has included a column in its where clause since the previous update to col_usage$, then the relevant usage column is incremented.

Oracle 9 has a procedure

dbms_stats.flush_database_monitoring_info to force a flush (on col_usage$ and mon_mod$), so that you can see the effect in close to real time.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Michael Thomas" <mhthomas_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Thursday, May 06, 2004 9:22 PM Subject: monitor column usage or index usage?

Thinking about which is better, monitor column usage or index usage?

So, this appears interesting, but I haven't got it quite working yet in my own schemas.

My question is: Does anyone know how to get this technique working (what am I missing?)?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri May 07 2004 - 03:32:12 CDT