•DBA_TABLES and DBA_OBJECT_TABLES
•DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
•DBA_TAB_HISTOGRAMS
•DBA_TAB_COLS
•DBA_COL_GROUP_COLUMNS
•DBA_INDEXES and DBA_IND_STATISTICS
•DBA_CLUSTERS
•DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS
•DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS
•DBA_PART_COL_STATISTICS
•DBA_PART_HISTOGRAMS
•DBA_SUBPART_COL_STATISTICS
•DBA_SUBPART_HISTOGRAMS

Example 13-1 Viewing Height-Balanced Histogram Statistics
BEGIN
DBMS_STATS.GATHER_table_STATS (
    OWNNAME    => 'OE',
    TABNAME    => 'INVENTORIES',
    METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND';

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
ORDER BY ENDPOINT_NUMBER;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
The database automatically creates frequency histograms instead of height-balanced histograms under the following conditions:
•The number of distinct values is less than or equal to the number of histogram buckets specified (up to 254).
•It is not true that each column value repeats only once.

Example 13-2 Viewing Frequency Histogram Statistics
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
    OWNNAME    => 'OE',
    TABNAME    => 'INVENTORIES',
    METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id' );
END;
/

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER INDEX MONITORING USAGE;

ITPub博客

视图杂记5

最新文章