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

本站中文解释

Oracle视图DBA_ADVISOR_SQLSTATS显示SQL优化器性能统计信息,可用于发现SQL优化器中的性能问题,并做出改进措施。它使用静态变量来保存特定的优化者行为,其中包括给定的SQL的次数,执行次数,优化次数,最小执行时间,最大执行时间,平均执行时间,平均优化时间,缓冲区命中次数,直接I/O次数,CPU使用等等。要使用这个视图,可以使用如下语句:
SELECT * FROM dba_advisor_sqlstats;
这将返回用于该步骤的SQL的统计数据的完整列表。另外,也可以使用WHERE子句来缩小结果集,使用AND条件来定位来自特定操作的SQL。

官方英文解释

DBA_ADVISOR_SQLSTATS displays execution statistics for the test-execution of different SQL plans during the advisor analysis.

Related View

USER_ADVISOR_SQLSTATS displays execution statistics owned by the current user for the test-execution of different SQL plans during the advisor analysis.

Column Datatype NULL Description

TASK_NAME

VARCHAR2(128)

Advisor task name in which the SQL statement was executed (see DBA_ADVISOR_TASKS)

TASK_ID

NUMBER(38)

NOT NULL

Advisor task ID in which the SQL statement was executed (see DBA_ADVISOR_TASKS)

EXECUTION_NAME

VARCHAR2(128)

NOT NULL

Advisor task execution in which the SQL statement was executed (see DBA_ADVISOR_EXECUTIONS)

EXECUTION_TYPE

VARCHAR2(128)

Type of the advisor task execution in which the SQL statement was executed (see DBA_ADVISOR_EXECUTIONS)

OBJECT_ID

NUMBER(38)

NOT NULL

Advisor object ID identifying the relevant SQL statement (see DBA_ADVISOR_OBJECTS)

PLAN_ID

NUMBER

NOT NULL

Plan ID number generated to uniquely identify a plan for a particular SQL statement (foreign key to DBA_ADVISOR_SQLPLANS)

SQL_ID

VARCHAR2(13)

NOT NULL

Identifier for the SQL statement executed

PLAN_HASH_VALUE

NUMBER

NOT NULL

Hash value of the SQL execution plan

ATTR1

NUMBER

For internal use only

CON_DBID

NUMBER

The database ID of the pluggable database (PDB)

PARSE_TIME

NUMBER

Parse time (in microseconds) measured for the SQL

ELAPSED_TIME

NUMBER

Elapsed time (in microseconds) to execute the SQL and fetch all of its rows, after parsing

CPU_TIME

NUMBER

CPU time (in microseconds) to execute the SQL and fetch all of its rows, after parsing

USER_IO_TIME

NUMBER

I/O time (in microseconds) to execute the SQL and fetch all of its rows, after parsing

BUFFER_GETS

NUMBER

Number of buffer gets measured for executing the SQL and fetching all of its rows

DISK_READS

NUMBER

Number of disk reads measured for executing the SQL and fetching all of its rows

DIRECT_WRITES

NUMBER

Number of direct writes measured for executing the SQL and fetching all of its rows

PHYSICAL_READ_REQUESTS

NUMBER

Number of physical read I/O requests issued by the monitored SQL

PHYSICAL_WRITE_REQUESTS

NUMBER

Number of physical write I/O requests issued by the monitored SQL

PHYSICAL_READ_BYTES

NUMBER

Number of bytes read from disks by the monitored SQL

PHYSICAL_WRITE_BYTES

NUMBER

Number of bytes written to disks by the monitored SQL

ROWS_PROCESSED

NUMBER

Number of rows returned by the SQL execution

FETCHES

NUMBER

Number of fetches for the SQL execution

EXECUTIONS

NUMBER

Execution count for the SQL. This column will always have a value of 1 or 0.

END_OF_FETCH_COUNT

NUMBER

Indicates whether the SQL was executed to end-of-fetch (1) or not (0)

OPTIMIZER_COST

NUMBER

Optimizer cost for the execution plan

OTHER

CLOB

For internal use only

TESTEXEC_TOTAL_EXECS

NUMBER

Total number of executions during test execute

IO_INTERCONNECT_BYTES

NUMBER

Number of I/O bytes exchanged between Oracle Database and the storage system

TESTEXEC_FIRST_EXEC_IGNORED

VARCHAR2(1)

Indicates whether the first execution in test execute is ignored (Y) or not (N)

CON_DBID

NUMBER

The database ID of the PDB

ATTR2

NUMBER

For internal use only

ATTR3

NUMBER

For internal use only

CACHED_GETS

NUMBER

The total of the db block gets from cache statistic and the consistent gets from cache statistic

DIRECT_GETS

NUMBER

The total of the db block gets direct statistic and the consistent gets direct statistic

EXECUTIONS_ORIGINAL

NUMBER

 

Number of original executions from the SQL tuning set, independent of trial executions

OBJECT_FLAGS

NUMBER

 

Value of the flags from the execution of the SQL, such as timeout or error

See Also:

  • “USER_ADVISOR_SQLSTATS”

  • “Statistics Descriptions” for more information about statistics


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