Oracle教程 2023-02-24

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

本站中文解释

DBA_WORKLOAD_DIV_SUMMARY视图是Oracle数据库中的系统定义视图，主要用于描述当前数据库工作负载的汇总信息。该视图提供了当前数据库中每一类作业占用的CPU时间、当前服务请求的状态数以及各类作业占用空间等数据。另外，还提供了类似于Oracle中存储过程、调度程序以及触发器等流程、程序占用CPU时间的信息。通过查看这些综合数据及信息，可以得出系统的整体性能，从而帮助数据库管理员更好地管理系统。

使用方法：

1、首先，运行下面的查询语句，查看当前的工作负载汇总情况：

SELECT * FROM DBA_WORKLOAD_DIV_SUMMARY;

2、可以使用以下查询语句来查看某一类型作业所占用的比例：

SELECT JOB_TYPE, RELATIVE_CPU_TIME FROM DBA_WORKLOAD_DIV_SUMMARY WHERE JOB_TYPE = ‘XXX’;

其中XXX为某一类型作业的名称。

3、最后，也可以使用以下查询语句来查看当前系统中每一类作业占用的空间：

SELECT JOB_TYPE, OCCUPIED_MB FROM DBA_WORKLOAD_DIV_SUMMARY WHERE JOB_TYPE = ‘XXX’;

其中XXX为某一类型作业的名称。

官方英文解释

DBA_WORKLOAD_DIV_SUMMARY displays a summary of the replay divergence information in the DBA_WORKLOAD_REPLAY_DIVERGENCE view. DBA_WORKLOAD_REPLAY_DIVERGENCE may have duplicate entries, while DBA_WORKLOAD_DIV_SUMMARY keeps only one entry and tracks the number of occurrences of each duplicate entry.

Starting with Oracle Database 12.2.0.1, the replay report is generated from DBA_WORKLOAD_DIV_SUMMARY instead of from DBA_WORKLOAD_REPLAY_DIVERGENCE, which results in faster generation of the replay report.

Column	Datatype	NULL	Description
`REPLAY_ID`	`NUMBER`	`NOT NULL`	ID (key) for the workload replay
`DIVERGENCE_TYPE`	`NUMBER`	`NOT NULL`	Reserved for future use
`IS_QUERY_DATA_DIVERGENCE`	`VARCHAR2(1)`		Indicates whether the data divergence is from the number of rows fetched by SELECT queries (`Y`) or not (`N`)
`IS_DML_DATA_DIVERGENCE`	`VARCHAR2(1)`		Indicates whether the divergence is from the number of rows affected by INSERT, UPDATE, or DELETE SQL statements (`Y`) or not (`N`)
`IS_ERROR_DIVERGENCE`	`VARCHAR2(1)`		Indicates whether the divergence is from errors seen during capture or replay (`Y`) or not (`N`)
`IS_THREAD_FAILURE`	`VARCHAR2(1)`		Indicates whether the divergence is from sessions that failed during replay (`Y`) or not (`N`)
`IS_DATA_MASKED`	`VARCHAR2(1)`		Indicates whether the SQL call contains masked bind data (`Y`) or not (`N`). If data masking technology is used at the replay database, the workload capture files need to be masked. Otherwise, SQL statements generated from capture files that contain sensitive bind data will not match the database. When the replay client sends masked bind data to the server, it turns on the `IS_DATA_MASKED` flag for the current SQL call.
`IS_CLIENT_FAILURE`	`VARCHAR2(1)`		Indicates whether the workload replay client failed during replay (`Y`) or not (`N`)
`STREAM_ID`	`NUMBER`	`NOT NULL`	Stream ID of the session that reported the divergence
`SQL_ID`	`VARCHAR2(13)`		SQL ID of the SQL that reported the divergence
`EXPECTED_ERROR#`	`NUMBER`		Error number that was seen during capture (`0` if the capture ran successfully)
`EXPECTED_ERROR_MESSAGE`	`VARCHAR2(4000)`		Text of the error message whose number appears in the `EXPECTED_ERROR#` column
`OBSERVED_ERROR#`	`NUMBER`		Actual error number seen during replay (`0` if the replay ran successfully, `15566` (corresponding to ORA-15566) if the captured call could not be replayed)
`OBSERVED_ERROR_MESSAGE`	`VARCHAR2(4000)`		Text of the error message whose number appears in the `OBSERVED_ERROR#` column
`SERVICE`	`VARCHAR2(64)`		Service name of the session that reported the divergence
`MODULE`	`VARCHAR2(64)`		Module name of the session that reported the divergence
`INSTANCE_NUMBER`	`NUMBER`		The number of the instance that reported the divergence
`WRC_ID`	`NUMBER`		The identifier of the workload replay client
`OCCURRENCES`	`NUMBER`		Number of times the divergence occurred during replay

Example

The following query prints the top 3 SQL statements that got error divergence during replay. This query shows the captured error number and the actual error number seen during replay.

SQL> SELECT * FROM  
 (SELECT occurrences, sql_id, expected_error#, observed_error#   
  FROM    dba_workload_div_summary
  WHERE   replay_id = 123     
   AND    is_error_divergence = 'Y'
  ORDER BY occurrences DESC) 
WHERE ROWNUM <= 3;  

OCCURRENCES SQL_ID        EXPECTED_ERROR# OBSERVED_ERROR# 
----------- ------------- --------------- ---------------           
          8 0xrm2wjdqv17m               0               1           
          4 8bzwdnnznspjd            1422               0
          3 6d8rwrac8dsk7               1            1400

SQL>

本站中文解释

官方英文解释

相关推荐