Browsing Current Table and Column Statistics

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 ;