Oracle教程 2023-02-23

Oracle 视图 ALL_TAB_STATISTICS 官方解释，作用，如何使用详细说明

本站中文解释

Oracle视图ALL_TAB_STATISTICS是一个系统视图，用于实现表统计信息的查询。该视图包含关于每个用户或用户拥有的表的TEMP空间统计，例如平均行长度、行数、分区扫描次数等。

使用该视图可以实现对表的统计信息的查询，可以查询表的每个分区的统计数据，以及表的空间使用率，可以为表空间优化提供重要参考数据。

使用示例：

查询test_table表中每个分区的统计数据

SELECT partition_name, num_rows, avg_row_len FROM all_tab_statistics WHERE table_name = ‘TEST_TABLE’;

官方英文解释

ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user.

Related Views

DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	Description
`OWNER`	`VARCHAR2(128)`	Owner of the object
`TABLE_NAME`	`VARCHAR2(128)`	Name of the table
`PARTITION_NAME`	`VARCHAR2(128)`	Name of the partition
`PARTITION_POSITION`	`NUMBER`	Position of the partition within the table
`SUBPARTITION_NAME`	`VARCHAR2(128)`	Name of the subpartition
`SUBPARTITION_POSITION`	`NUMBER`	Position of the subpartition within the partition
`OBJECT_TYPE`	`VARCHAR2(12)`	Type of the object: `TABLE` `PARTITION` `SUBPARTITION`
`NUM_ROWS`	`NUMBER`	Number of rows in the object
`BLOCKS`	`NUMBER`	Number of used blocks in the object
`EMPTY_BLOCKS`	`NUMBER`	Number of empty blocks in the object
`AVG_SPACE`	`NUMBER`	Average available free space in the object
`CHAIN_CNT`	`NUMBER`	Number of chained rows in the object
`AVG_ROW_LEN`	`NUMBER`	Average row length, including row overhead
`AVG_SPACE_FREELIST_BLOCKS`	`NUMBER`	Average freespace of all blocks on a freelist
`NUM_FREELIST_BLOCKS`	`NUMBER`	Number of blocks on the freelist
`AVG_CACHED_BLOCKS`	`NUMBER`	Average number of blocks in the buffer cache
`AVG_CACHE_HIT_RATIO`	`NUMBER`	Average cache hit ratio for the object
`IM_IMCU_COUNT`	`NUMBER`	Number of In-Memory Compression Units (IMCUs) in the table
`IM_BLOCK_COUNT`	`NUMBER`	Number of In-Memory blocks in the table
`IM_STAT_UPDATE_TIME`	`TIMESTAMP(9)`	The timestamp of the most recent update to the In-Memory statistics
`SCAN_RATE`	`NUMBER`	Scan rate for the object in megabytes per second. This statistic is only relevant or meaningful for external tables.
`SAMPLE_SIZE`	`NUMBER`	Sample size used in analyzing the table
`LAST_ANALYZED`	`DATE`	Date of the most recent time the table was analyzed
`GLOBAL_STATS`	`VARCHAR2(3)`	`GLOBAL_STATS` will be `YES` if statistics are gathered or incrementally maintained, otherwise it will be `NO`
`USER_STATS`	`VARCHAR2(3)`	Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`STATTYPE_LOCKED`	`VARCHAR2(5)`	Type of statistics lock: `DATA` `CACHE` `ALL`
`STALE_STATS`	`VARCHAR2(7)`	Indicates whether statistics for the object are stale (`YES`) or not (`NO`)
`NOTES`	`VARCHAR2(25)`	Describes some additional properties of the statistics. For example, a value of `STATS_ON_CONVENTIONAL_LOAD` indicates that the statistics are obtained by online statistics gathering for conventional DML.
`SCOPE`	`VARCHAR2(7)`	The value is `SHARED` for statistics gathered on any table other than global temporary tables. For a global temporary table, the possible values are: `SESSION` – Indicates that the statistics are session-specific `SHARED` – Indicates that the statistics are shared across all sessions See Oracle Database PL/SQL Packages and Types Reference for information about using the `GLOBAL_TEMP_TABLE_STATS` preference of the `DBMS_STATS` package to control whether to gather session or shared statistics for global temporary tables.

本站中文解释

官方英文解释

相关推荐