monitor column usage or index usage?

  • From: Michael Thomas <mhthomas@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 6 May 2004 13:22:02 -0700 (PDT)

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
-----------------------------------------------------------------

Other related posts: