Oracle queries the average SQL Execution time (Code instance) through the v $ SQL view, oraclesql

Oracle queries the average SQL Execution time (Code instance) through the v $ SQL view, oraclesql
Oracle queries the average SQL Execution time (Code instance) through the v $ SQL View)

SELECT SQL _TEXT, EXECUTIONS total EXECUTIONS, ELAPSED_TIME/1000 total elapsed time (seconds), ELAPSED_TIME/nvl (EXECUTIONS, 1)/1000 average elapsed time (seconds), parse_cils hard parsing times, DISK_READS physical reads, BUFFER_GETS read cache times FROM v $ SQL where SQL _TEXT LIKE '% SELECT NAME FROM EMP WHERE EMPNO = 1% ';

The fields in the v $ SQL view are described as follows:

SQL _TEXT: The first 1000 characters of SQL text

SHARABLE_MEM: size of the occupied shared memory (unit: byte)

PERSISTENT_MEM: Fixed memory size during the life cycle (unit: byte)

RUNTIME_MEM: Fixed memory size during the execution period

SORTS: number of completed sorting tasks

LOADED_VERSIONS: displays whether the context heap is loaded, and 1 is 0 No

OPEN_VERSIONS: displays whether the Sub-cursor is locked, and 1 is 0 No

USERS_OPENING: Number of users executing statements

FETCHES: the number of FETCHES of SQL statements.

EXECUTIONS: number of EXECUTIONS since it was loaded into the cache Library

USERS_EXECUTING: Number of users executing statements

LOADS: number of times an object has been loaded

FIRST_LOAD_TIME: The first loading time.

INVALIDATIONS: Number of invalid times

Parse_cils: Number of resolution calls

DISK_READS: Number of disk reads

BUFFER_GETS: Number of read cache segments

ROWS_PROCESSED: parses the total number of columns returned by the SQL statement.

COMMAND_TYPE: Command type code

OPTIMIZER_MODE: optimizer model of SQL statements

OPTIMIZER_COST: the query cost provided by the optimizer.

PARSING_USER_ID: The first resolved user ID

PARSING_SCHEMA_ID: Plan ID of the first resolution

KEPT_VERSIONS: indicates whether the current sub-cursor is marked as resident memory using the DBMS_SHARED_POOL package

ADDRESS: current cursor parent handle ADDRESS

TYPE_CHK_HEAP: description of current heap type check

HASH_VALUE: the Hash value of the parent statement in the cache database.

PLAN_HASH_VALUE: Number of execution plans.

CHILD_NUMBER: Number of child cursors

MODULE: In the first parsing, the MODULE name set by DBMS_APPLICATION_INFO.SET_MODULE is called.

ACTION: The ACTION name set by DBMS_APPLICATION_INFO.SET_ACTION is called for the first parsing of this statement.

SERIALIZABLE_ABORTS: Number of times the transaction failed to be serialized

OUTLINE_CATEGORY: If outline is applied during the interpretation of cursor, this column displays all types of outline. Otherwise, this column is empty.

CPU_TIME: CPU usage time (unit: milliseconds) such as resolution, execution, and retrieval)

ELAPSED_TIME: Time consumed for parsing, execution, and retrieval (unit: milliseconds)

OUTLINE_SID: outline session ID

CHILD_ADDRESS: subcursor address

SQLTYPE: indicates the SQL language version used by the current statement.

REMOTE: indicates whether the cursor is a REMOTE image (Y/N)

OBJECT_STATUS: Object status (VALID or INVALID)

IS_OBSOLETE: when there are too many sub-game targets, it indicates whether the cursor is discarded (Y/N)