Oracle教程 2023-04-07

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

本站中文解释

Oracle视图DBA_ADVISOR_SQLW_STMTS用于分析和收集SQL Workload Manager（SQL Workload Manager）会话的信息(AVGSECS_CUMM, SQLWORKLOAD_CUMM)。此视图报告有关特定SQL语句或性能分析时间段中应用程序会话的信息，包括每个会话应用的累计/平均SQL处理时间。该视图可用于优化控件SQL Workload Manager决策、调查性能问题或监视环境。

使用此视图，可以查询一段时间内所有会话的累计处理时间，以及该期间内每个会话的平均处理时间，以及SQL语句的应用情况和执行次数。

例如，可以使用以下查询来查询特定时间段内最耗时的会话：

SELECT
SID,
sum(avgsecs_cumm) AS AVG_TIME
FROM
dba_advisor_sqlw_stmts
WHERE
startdate_time BETWEEN ’20-05-01′ AND ’20-05-14′
GROUP BY
SID
ORDER BY
AVG_TIME DESC;

官方英文解释

DBA_ADVISOR_SQLW_STMTS displays rows that correspond to all statements in the workload.

All columns are guaranteed to be non-null.

Related View

USER_ADVISOR_SQLW_STMTS displays rows that correspond to the statements in the workload owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(128)`		Owner of the workload object
`WORKLOAD_ID`	`NUMBER`	`NOT NULL`	Unique identifier number of the workload object
`WORKLOAD_NAME`	`VARCHAR2(128)`		Name of the workload
`SQL_ID`	`NUMBER`	`NOT NULL`	Generated identifier of the statement
`HASH_VALUE`	`NUMBER`		Hash value for the parent statement in the cache
`USERNAME`	`VARCHAR2(128)`		Name of the user executing the statement
`MODULE`	`VARCHAR2(64)`		Name of the module issuing the statement
`ACTION`	`VARCHAR2(64)`		Module action for the statement
`CPU_TIME`	`NUMBER`		Total CPU count (in seconds) of the executing statement
`BUFFER_GETS`	`NUMBER`		Total number of buffer gets for the statement
`DISK_READS`	`NUMBER`		Total disk-read I/O count for the statement
`ELAPSED_TIME`	`NUMBER`		Total elapsed time (in seconds) of the executing statement
`ROWS_PROCESSED`	`NUMBER`		Total number of rows processed by the statement
`EXECUTIONS`	`NUMBER`		Total number of times the statement was executed
`OPTIMIZER_COST`	`NUMBER`		Cost of executing the statement in the workload prior to the recommendations
`LAST_EXECUTION_DATE`	`DATE`		Date on which the statement was last executed
`PRIORITY`	`NUMBER`		Priority of the statement: `1` – High `2` – Medium `3` – Low
`COMMAND_TYPE`	`NUMBER`		Type of the command
`STAT_PERIOD`	`NUMBER`		Unused
`SQL_TEXT`	`CLOB`		Text of the SQL statement
`VALID`	`NUMBER`		Indicates whether the statement is valid for analysis: `0` – Statement will not be analyzed by the `EXECUTE_TASK` procedure. Typically, the statement references one or more tables that do not have valid statistics. To correct this problem, ensure that the tables have valid statistics and execute the `RESET_SQLWKLD` procedure on the current workload. `1` – Statement is eligible for analysis by the `EXECUTE_TASK` procedure.

本站中文解释

官方英文解释

相关推荐