monitor column usage or index usage?

From: Michael Thomas <mhthomas_at_yahoo.com>
Date: Thu, 6 May 2004 13:22:02 -0700 (PDT)
Message-ID: <20040506202202.71972.qmail@web50606.mail.yahoo.com>

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_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 Thu May 06 2004 - 15:20:51 CDT