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)