Check the Information of Statistics table in Oracle

Check the Information of Statistics table in Oracle

Following are the list of Statistics table present in the Oracle:

DBA_TABLES
DBA_TAB_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUB_PARTITIONS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTIONS
DBA_TAB_HISTOGRAMS
DBA_PART_HISTOGRAMS
DBA_SUBPART_HISTOGRAMS

Explaination of table columns used for stats
DBA_TABLES:

NUM_ROWS: Number of rows present in object.
BLOCKS: Number of used blocks
SAMPLE_SIZE: Sample size used in gather stats
LAST_ANALYZED : Date when last stats gather or analyzed.
GLOBAL_STATS: GLOBAL_STATS will be YES if statistics are gathered or incrementally maintained, otherwise it will be NOUSER_STATS: user entered stats
USER_STATS: If user entered stats then value will be YES otherwise NO.

DBA_TAB_STATISTICS:

NUM_ROWS: Number of rows present in object.
BLOCKS: Number of used blocks
SAMPLE_SIZE: Sample size used in gather stats
LAST_ANALYZED : Date when last stats gather or analyzed.
GLOBAL_STATS: GLOBAL_STATS will be YES if statistics are gathered or incrementally maintained, otherwise it will be NOUSER_STATS: user entered stats
USER_STATS: If user entered stats then value will be YES otherwise NO.
STATTYPE_LOCKED: IT means stats are locked or not if locked stats not gathered.
STALE_STATS : % change in stats after gathered. thne marked as stale stats.

DBA_TAB_COL_STATISTICS:

NUM_DISTINCT: Number of distinct values in the column
LOW_VALUE: Lowest value in the column
HIGH_VALUE Highest value in the column
DENSITY: 1/NUM_DISTINCT % of table retrieved for any specific value
NUM_NULLS: Number of nulls in the column
SAMPLE_SIZE: Sample size used in analyzing this column
HISTOGRAM: Type of histogram on the column
NUM_BUCKETS: Number of buckets in histogram for the column

DBA_IND_STATISTICS:


NUM_ROWS: Number of rows in the index
LEAF_BLOCKS: Number of leaf blocks in the index = I/O
DISTINCT_KEYS: % of index to be used (NUM_ROWS/DISTINCT_KEYS)
AVG_LEAF_BLOCKS_PER_KEY: Average number of leaf blocks per key (LEAF_BLOCKS/DISTINCT_KEYS)
AVG_DATA_BLOCKS_PER_KEY: Average number of data blocks per key
CLUSTER_FACTOR: Indicate the amount of order of the rows in the table based on value of index
If CF value is close to #table-blocks=good index.
If CF is close to #table-rows =no-so-good index.
Then changed by reorganizing and reordering the table.
SAMPLE_SIZE: Sample size used in analyzing this column
LAST_ANALYZED : Date when last stats gather or analyzed.
GLOBAL_STATS: GLOBAL_STATS will be YES if statistics are gathered or incrementally maintained, otherwise it will be NO
USER_STATS: If user entered stats then value will be YES otherwise NO.
</code

DBA_TAB_HISTOGRAMS:
Describe histogram of all tables and views.

ENDPOINT_NUMBER: Histogram bucket number
ENDPOINT_VALUE : Normalized endpoint value for this bucket
ENDPOINT_ACTUAL_VALUE: Actual (not normalized) string value of the endpoint for this bucket

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.