Script categories

Tuesday, 15 October 2013

Identify which tables/columns are being queried the most.

select o.owner
      ,o.object_name         as Table_Name
      ,c.column_name         as Column_Name
      ,u.equality_preds

       + u.equijoin_preds
       + u.nonequijoin_preds
       + u.range_preds
       + u.like_preds
       + u.null_preds        as Usage_Total
      ,u.timestamp           as Last_Used
from
       sys.col_usage$        u
      ,dba_objects           o
      ,dba_tab_columns       c
where
       o.object_id           = u.obj#
and    c.table_name          = o.object_name
and    c.column_id           = u.intcol#
and    o.object_type         = 'TABLE'
and    o.owner               = upper('&owner')
order by 4 desc
/