Column Monitoring

How have columns been used in SQL statements? The types of joins done on a column and how it is involved in the WHERE clause of queries may affect CBO behavior.

SELECT * FROM (
    SELECT (SELECT username FROM dba_users WHERE user_id = o.owner#) AS owner, 
           o.NAME AS table_name, 
           c.NAME AS column_name, 
           o.obj# AS table_object_id,
           cu.intcol# AS intcol#, 
           cu.equality_preds AS equality, 
           cu.equijoin_preds AS equijoin,
           cu.nonequijoin_preds AS noneequijoin, 
           cu.range_preds AS RANGE, 
           cu.like_preds AS "LIKE", 
           cu.null_preds AS "NULL",
           cu.TIMESTAMP
        FROM SYS.col$ c, SYS.col_usage$ cu, SYS.obj$ o, SYS.user$ u
        WHERE c.obj# = cu.obj#(+) AND c.intcol# = cu.intcol#(+) AND c.obj# = o.obj# AND o.owner# = u.user#) 
      WHERE OWNER='&OWNERNAME.' AND TABLE_NAME='&TABLENAME.';

Table Statistics

SELECT * 
  FROM DBA_TAB_STATISTICS 
 WHERE owner='&OWNERNAME.'
 ORDER BY TABLE_NAME;

Partition Statistics

SELECT * 
  FROM DBA_TAB_STATISTICS 
 WHERE OWNER='&OWNER.' 
   AND PARTITION_NAME IS NOT NULL 
   AND TABLE_NAME NOT LIKE 'BIN$%'
 ORDER BY table_name, partition_position;

Column Statistics

SELECT *
  FROM DBA_TAB_COL_STATISTICS tcs 
 WHERE tcs.owner = '&OWNERNAME.' AND tcs.table_name = '&TABLENAME.'
 ORDER BY 1,2,3;

SELECT TABLE_NAME,  
       COLUMN_NAME, 
       NUM_DISTINCT, 
       (SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME=tcs.TABLE_NAME) as NUM_ROWS, 
       SAMPLE_SIZE,
       HISTOGRAM,
       NUM_BUCKETS
  FROM USER_TAB_COL_STATISTICS tcs 
 WHERE lower(tcs.table_name) IN ('')
 ORDER BY 1,2,3;

Are histograms in use?

SELECT TABLE_NAME, 
       COLUMN_NAME, 
       COUNT(*) 
  FROM USER_TAB_HISTOGRAMS 
 GROUP BY TABLE_NAME, 
          COLUMN_NAME 
HAVING COUNT(*) > 2 ;