Statistics

Submitted by dave on Tue, 11/10/2009 - 17:49

Table/Histogram Summary
This can be useful if you have a specific list of tables you are interested in.

SELECT OWNER,
TABLE_NAME,
(SELECT COUNT(*) FROM DBA_HISTOGRAMS WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB1,
(SELECT COUNT(*) FROM DBA_HISTOGRAMS@TMOTST1 WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB2
FROM DBA_TABLES
WHERE OWNER = UPPER('&OWNER_NAME.')
ORDER BY 1,2;

Table Column Histogram Detail
Needs work!

SELECT OWNER,
TABLE_NAME,
COLUMN_NAME
-- ,
-- (SELECT COUNT(*) FROM DBA_HISTOGRAMS WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB1,
-- (SELECT COUNT(*) FROM DBA_HISTOGRAMS@TMOTST1 WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB2
FROM DBA_TAB_COLUMNS
WHERE OWNER = UPPER('&OWNER_NAME.')
ORDER BY 1,2,DBA_TAB_COLUMNS.COLUMN_ID;