|
Data Dictionary Statistics
When you use the ANALYZE command to create statistics for the cost-based optimizer to use, these statistics are inserted into some internal Oracle performance tables. These tables can be queried through several views. Although these views provide essentially the same information, depending on the particular view you choose, the scope of the information changes slightly. The following views are prefixed with the characters:
|
| View
| Description
|
|
| USER_
| This view contains information about the objects owned by the user.
|
| ALL_
| This view contains information about the objects accessible by the user. These are objects owned by the user as well as objects with PUBLIC access.
|
| DBA_
| This view contains information on all objects in the system.
|
|
These views provide information about different parts of the system, such as tables, clusters, indexes, and columns. Following is a brief list of the views available that contain performance information:
- • Table views: USER_TABLES, USER_PART_TABLES, ALL_TABLES, ALL_PART_TABLES, DBA_TABLES, DBA_PART_TABLES
- • Cluster views: USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS
- • Index views: USER_INDEXES, USER_PART_INDEXES, ALL_INDEXES, ALL_PART_INDEXES, DBA_INDEXES, DBA_PART_INDEXES
- • Column views: USER_TAB_COL_STATISTICS, USER_PART_COL_STATISTICS, ALL_TAB_COL_STATISTICS, ALL_PART_COL_STATISTICS, DBA_TAB_COL_STATISTICS, DBA_PART_COL_STATISTICS
The information contained in the internal tables referenced by these views is used by the optimizer to make decisions about which execution plan to take. The decision about which execution plan to take is also based on information about the size of the object and the data contained in the object. Some of the information contained in these tables is presented in Tables 17.2 through 17.7. Note: This is only a sample of the data in those tables;
Table 17.2. Data for tables in USER_TABLES, ALL_TABLES, DBA_TABLES.
|
| Column
| Description of Contents
|
|
| AVG_SPACE
| The average amount of free space in the table
|
| AVG_ROW_LEN
| The average length of a row
|
| BLOCKS
| The number of blocks in the table
|
| CHAIN_CNT
| The number of chained rows
|
| EMPTY_BLOCKS
| The number of blocks that have never been used
|
| NUM_ROWS
| The number of rows in the table
|
|
Table 17.3. Data for tables in USER_PART_TABLES, ALL__PART_TABLES, DBA_PART_TABLES.
|
| Column
| Description of Contents
|
|
| OWNER
| The owner of the object
|
| TABLE_NAME
| The name of the table
|
| PARTITIONING_TYPE
| The partitioning algorithm (RANGE)
|
| PARTITIONING_COUNT
| The number of partitions in the table
|
|
Table 17.4. Data for clusters in USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS.
|
| Column
| Description of Contents
|
|
| AVG_BLOCKS_PER_KEY
| The average number of blocks that have rows that use the same key
|
| CLUSTER_TYPE
| The type of cluster: whether it is an index cluster or a hash cluster
|
| HASHKEYS
| The number of hash keys if it is a hash cluster
|
|
Table 17.5. Data for indexes in USER_INDEXES, ALL_INDEXES, DBA_INDEXES.
|
| Column
| Description of Contents
|
|
| AVG_LEAF_BLOCKS_PER_KEY
| The average number of leaf blocks per key
|
| AVG_DATA_BLOCKS_PER_KEY
| The average number of data blocks per key
|
| BLEVEL
| The level of the B-Tree
|
| CLUSTERING_FACTOR
| The amount of order or disorder in the table the index is referencing
|
| DISTINCT_KEYS
| The number of distinct keys in the index
|
| LEAF_BLOCKS
| The number of leaf blocks (lowest level index blocks) in the index
|
| UNIQUENESS
| States whether the index is UNIQUE or NONUNIQUE
|
|
Table 17.6. Data for indexes in USER_PART_INDEXES, ALL_PART_INDEXES, DBA_PART_INDEXES.
|
| Column
| Description of Contents
|
|
| OWNER
| The owner of the object
|
| TABLE_NAME
| The name of the table
|
| PARTITIONING_TYPE
| The partitioning algorithm (RANGE)
|
|
Table 17.7. Column data in USER_TAB_COL_STATISTICS, ALL_TAB_COL_STATISTICS, DBA_TAB_COL_STATISTICS.
|
| Column
| Description of Contents
|
|
| DENSITY
| The density of the column (rows per data block)
|
| HIGH_VALUE
| The highest value in this column of the table
|
| LOW_VALUE
| The second-lowest value in this column of the table
|
| NUM_DISTINCT
| The number of distinct values in this column of the table
|
|
|
Используются технологии
uCoz