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 NULL 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.

See Also:

  • “DBA_TAB_STATISTICS”

  • “USER_TAB_STATISTICS”

  • Oracle Database PL/SQL
    Packages and Types Reference
    for more information about the DBMS_STATS package


数据运维技术 » Oracle 视图 ALL_TAB_STATISTICS 官方解释,作用,如何使用详细说明