Browsing Current Table and Column Statistics
Submitted by dave on Mon, 01/25/2010 - 14:38
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 ;