Oracle教程 2023-02-28

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

本站中文解释

Oracle视图DBA_HIST_SEG_STAT用于报告记录在AWR（自自动工作负荷重构）收集的所有数据段的性能统计信息。它显示各种时间段（秒，分，小时，天等）及其属性的数据段的性能统计信息，如平均I / O等待时间，平均活动会话数等。

使用DBA_HIST_SEG_STAT视图的方法：

1.首先，收集现有性能数据，使用以下查询查看视图的数据：

SELECT * FROM DBA_HIST_SEG_STAT;

2.选择所需字段，例如：

SELECT avg_wait_time, avg_act_sess FROM DBA_HIST_SEG_STAT;

3.对查询结果添加约束以缩小数据段：

SELECT avg_wait_time, avg_act_sess FROM DBA_HIST_SEG_STAT WHERE snap_id BETWEEN 1000 AND 1010;

4.可以按需添加筛选条件，例如OBJECT_NAME：

SELECT avg_wait_time, avg_act_sess FROM DBA_HIST_SEG_STAT WHERE snap_id BETWEEN 1000 AND 1010 AND object_name = ‘MY_TABLE’;

官方英文解释

DBA_HIST_SEG_STAT displays historical information about segment-level statistics.

This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

Column	Datatype	NULL	Description
`SNAP_ID`	`NUMBER`	`NOT NULL`	Unique snapshot ID
`DBID`	`NUMBER`	`NOT NULL`	Database ID for the snapshot
`INSTANCE_NUMBER`	`NUMBER`	`NOT NULL`	Instance number for the snapshot
`TS#`	`NUMBER`	`NOT NULL`	Tablespace number
`OBJ#`	`NUMBER`	`NOT NULL`	Dictionary object number
`DATAOBJ#`	`NUMBER`	`NOT NULL`	Data object number
`LOGICAL_READS_TOTAL`	`NUMBER`		Cumulative value for logical reads
`LOGICAL_READS_DELTA`	`NUMBER`		Delta value for logical reads
`BUFFER_BUSY_WAITS_TOTAL`	`NUMBER`		Cumulative value for buffer busy waits
`BUFFER_BUSY_WAITS_DELTA`	`NUMBER`		Delta value for buffer busy waits
`DB_BLOCK_CHANGES_TOTAL`	`NUMBER`		Cumulative value for db block changes (in blocks)
`DB_BLOCK_CHANGES_DELTA`	`NUMBER`		Delta value for db block changes (in blocks)
`PHYSICAL_READS_TOTAL`	`NUMBER`		Cumulative value for physical reads (in blocks)
`PHYSICAL_READS_DELTA`	`NUMBER`		Delta value for physical reads (in blocks)
`PHYSICAL_WRITES_TOTAL`	`NUMBER`		Cumulative value for physical writes (in blocks)
`PHYSICAL_WRITES_DELTA`	`NUMBER`		Delta value for physical writes (in blocks)
`PHYSICAL_READS_DIRECT_TOTAL`	`NUMBER`		Cumulative value for physical reads direct (in blocks)
`PHYSICAL_READS_DIRECT_DELTA`	`NUMBER`		Delta value for physical reads direct (in blocks)
`PHYSICAL_WRITES_DIRECT_TOTAL`	`NUMBER`		Cumulative value for physical writes direct (in blocks)
`PHYSICAL_WRITES_DIRECT_DELTA`	`NUMBER`		Delta value for physical writes direct (in blocks)
`ITL_WAITS_TOTAL`	`NUMBER`		Cumulative value for ITL waits
`ITL_WAITS_DELTA`	`NUMBER`		Delta value for ITL waits
`ROW_LOCK_WAITS_TOTAL`	`NUMBER`		Cumulative value for row lock waits
`ROW_LOCK_WAITS_DELTA`	`NUMBER`		Delta value for row lock waits
`GC_CR_BLOCKS_SERVED_TOTAL`	`NUMBER`		Cumulative value for global cache CR blocks served
`GC_CR_BLOCKS_SERVED_DELTA`	`NUMBER`		Delta value for global cache CR blocks served
`GC_CU_BLOCKS_SERVED_TOTAL`	`NUMBER`		Cumulative value for global cache current blocks served
`GC_CU_BLOCKS_SERVED_DELTA`	`NUMBER`		Delta value for global cache current blocks served
`GC_BUFFER_BUSY_TOTAL`	`NUMBER`		Cumulative value for global cache buffer busy
`GC_BUFFER_BUSY_DELTA`	`NUMBER`		Delta value for global cache buffer busy
`GC_CR_BLOCKS_RECEIVED_TOTAL`	`NUMBER`		Cumulative value for global cache CR blocks received
`GC_CR_BLOCKS_RECEIVED_DELTA`	`NUMBER`		Delta value for global cache CR blocks received
`GC_CU_BLOCKS_RECEIVED_TOTAL`	`NUMBER`		Cumulative value for global cache current blocks received
`GC_CU_BLOCKS_RECEIVED_DELTA`	`NUMBER`		Delta value for global cache current blocks received
`SPACE_USED_TOTAL`	`NUMBER`		Number of bytes used by user data
`SPACE_USED_DELTA`	`NUMBER`		Delta value for space used by user data (in bytes). A negative value indicates the number of bytes deleted in the segment.
`SPACE_ALLOCATED_TOTAL`	`NUMBER`		The number of bytes that are allocated
`SPACE_ALLOCATED_DELTA`	`NUMBER`		Delta value for the space allocated (in bytes). A negative value indicates the number of bytes deallocated to the tablespace.
`TABLE_SCANS_TOTAL`	`NUMBER`		Cumulative value for table scans
`TABLE_SCANS_DELTA`	`NUMBER`		Delta value for table scans
`CHAIN_ROW_EXCESS_TOTAL`	`NUMBER`		Cumulative value of number of chained row pieces that can be eliminated by table reorganization
`CHAIN_ROW_EXCESS_DELTA`	`NUMBER`		Delta value of number of chained row pieces that can be eliminated by table reorganization
`PHYSICAL_READ_REQUESTS_TOTAL`	`NUMBER`		Cumulative value of number of physical read I/O requests issued for the monitored segment
`PHYSICAL_READ_REQUESTS_DELTA`	`NUMBER`		Delta value of number of physical read I/O requests issued for the monitored segment
`PHYSICAL_WRITE_REQUESTS_TOTAL`	`NUMBER`		Cumulative value of number of physical write I/O requests issued for the monitored segment
`PHYSICAL_WRITE_REQUESTS_DELTA`	`NUMBER`		Delta value of number of physical write I/O requests issued for the monitored segment
`OPTIMIZED_PHYSICAL_READS_TOTAL`	`NUMBER`		Cumulative value of number of physical reads from Database Smart Flash Cache for the monitored segment
`OPTIMIZED_PHYSICAL_READS_DELTA`	`NUMBER`		Delta value of number of physical reads from Database Smart Flash Cache for the monitored segment
`IM_SCANS_TOTAL`	`NUMBER`		Count of segment statistics
`IM_SCANS_DELTA`	`NUMBER`		Delta values for in-memory scans
`POPULATE_CUS_TOTAL`	`NUMBER`		Count of compression units (CUs) populated per segment
`POPULATE_CUS_DELTA`	`NUMBER`		Delta value for compression unit (CU) populate operations
`REPOPULATE_CUS_TOTAL`	`NUMBER`		Count of CUs repopulated per segment
`REPOPULATE_CUS_DELTA`	`NUMBER`		Delta value for compression unit (CU) repopulate operations
`IM_DB_BLOCK_CHANGES_TOTAL`	`NUMBER`		The total number of changes that were part of an update or delete operation that were made to segment blocks
`IM_DB_BLOCK_CHANGES_DELTA`	`NUMBER`		Delta value for database block changes
`CON_DBID`	`NUMBER`		The database ID of the PDB for the sampled session
`CON_ID`	`NUMBER`		The ID of the container that `CON_DBID` identifies. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data

本站中文解释

官方英文解释

相关推荐