Thinking about which is better, monitor column usage or index usage? Maybe column usage allows me to build indexes that perform better. Whereas, index usage might save space to drop an unused index. Thoughts? I lucked into finding this site: www.adp-gmbh.ch/ora/misc/sys_tables.html Which referenced a method to monitor column usage (_column_tracking_level set 1) where statistics are updated at intervals by SMON. Tweaking their query I came up with this: -- set pages 9999 feedb on serverout on -- col username heading "user|name" format a5 col oname format a15 col cname format a15 col equality_preds heading "equal|preds" format 9999 col equijoin_preds heading "ejoin|preds" format 9999 col nonequijoin_preds heading "nonej|preds" format 9999 col range_preds heading "range|preds" format 9999 col like_preds heading "like|preds" format 9999 col null_preds heading "null|preds" format 9999 col when format a10 -- prompt. prompt -- monitor usage predicates on columns in select statements prompt -- updated (_column_tracking_level set 1) at intervals by SMON prompt -- used by DBMS_STATS for histograms prompt. SELECT a.username, o.name oname, c.name cname, u.equality_preds, u.equijoin_preds, u.nonequijoin_preds, u.range_preds, u.like_preds, u.null_preds, to_char(u.timestamp,'yyyy-mm-dd hh24:mi:ss') when FROM sys.col_usage$ u, sys.obj$ o, sys.col$ c, all_users a WHERE a.user_id = o.owner# AND u.obj# = o.obj# AND u.obj# = c.obj# AND u.intcol# = c.col# AND a.username in ('HR') ORDER BY a.username, o.name, c.name ; prompt. prompt -- set _column_tracking_level to 1 (x$ksppi) prompt. select * from x$ksppi where substr(ksppinm,1,1) = '_' and ksppinm in ('_column_tracking_level') ; 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?)? Regards, Mike Thomas __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------