Skip Headers
Oracle® Database Reference
10g Release 2 (10.2)

Part Number B14237-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

DBA_HIST_SQLSTAT

DBA_HIST_SQLSTAT displays historical information about SQL statistics. This view captures the top SQL statements based on a set of criteria and captures the statistics information from V$SQL. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

This view is used with the DBA_HIST_OPTIMIZER_ENV, DBA_HIST_SQLTEXT, and DBA_HIST_SQL_PLAN views to provide a complete picture of historical SQL statistics.

Column Datatype NULL Description
SNAP_ID NUMBER   Unique snapshot ID
DBID NUMBER   Database ID for the snapshot
INSTANCE_NUMBER NUMBER   Instance number for the snapshot
SQL_ID VARCHAR2(13)   SQL identifier of the parent cursor in the library cache
PLAN_HASH_VALUE NUMBER   Numerical representation of the SQL plan for the cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
OPTIMIZER_COST NUMBER   Cost of the query given by the optimizer
OPTIMIZER_MODE VARCHAR2(10)   Mode under which the SQL statement is executed
OPTIMIZER_ENV_HASH_VALUE NUMBER   Hash Value for the optimizer environment
SHARABLE_MEM NUMBER   Amount of shared memory used by the child cursor (in bytes)
LOADED_VERSIONS NUMBER   Indicates whether the context heap is loaded (1) or not (0)
VERSION_COUNT NUMBER   Number of children associated with the cursor
MODULE VARCHAR2(64)   Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_MODULE
ACTION VARCHAR2(64)   Contains the name of the action that was executing at the time that the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_ACTION
SQL_PROFILE VARCHAR2(64)   Name of the applied SQL Profile
FORCE_MATCHING_SIGNATURE NUMBER   The signature used when the CURSOR_SHARING parameter is set to FORCE
PARSING_SCHEMA_ID NUMBER   Schema ID that was used to originally build the child cursor
PARSING_SCHEMA_NAME VARCHAR2(30)   Schema name that was used to originally build the child cursor
FETCHES_TOTAL NUMBER   Cumulative number of fetches associated with the SQL statement
FETCHES_DELTA NUMBER   Delta number of fetches associated with the SQL statement
END_OF_FETCH_COUNT_TOTAL NUMBER   Cumulative number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of the EXECUTIONS column.
END_OF_FETCH_COUNT_DELTA NUMBER   Delta number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed.
SORTS_TOTAL NUMBER   Cumulative number of sorts that were done for this child cursor
SORTS_DELTA NUMBER   Delta number of sorts that were done for this child cursor
EXECUTIONS_TOTAL NUMBER   Cumulative number of executions that took place on this object since it was brought into the library cache
EXECUTIONS_DELTA NUMBER   Delta number of executions that took place on this object since it was brought into the library cache
PX_SERVERS_EXECS_TOTAL NUMBER   Cumulative number of PX server executions
PX_SERVERS_EXECS_DELTA NUMBER   Delta number of PX server executions
LOADS_TOTAL NUMBER   Cumulative number of times the object was either loaded or reloaded
LOADS_DELTA NUMBER   Delta number of times the object was either loaded or reloaded
INVALIDATIONS_TOTAL NUMBER   Cumulative number of times this child cursor has been invalidated
INVALIDATIONS_DELTA NUMBER   Delta number of times this child cursor has been invalidated
PARSE_CALLS_TOTAL NUMBER   Cumulative number of parse calls for this child cursor
PARSE_CALLS_DELTA NUMBER   Delta number of parse calls for this child cursor
DISK_READS_TOTAL NUMBER   Cumulative number of disk reads for this child cursor
DISK_READS_DELTA NUMBER   Delta number of disk reads for this child cursor
BUFFER_GETS_TOTAL NUMBER   Cumulative number of buffer gets for this child cursor
BUFFER_GETS_DELTA NUMBER   Delta number of buffer gets for this child cursor
ROWS_PROCESSED_TOTAL NUMBER   Cumulative number of rows the parsed SQL statement returns
ROWS_PROCESSED_DELTA NUMBER   Delta number of rows the parsed SQL statement returns
CPU_TIME_TOTAL NUMBER   Cumulative value of CPU time (in microseconds) used by this cursor for parsing/executing/fetching
CPU_TIME_DELTA NUMBER   Delta value of CPU time (in microseconds) used by this cursor for parsing/executing/fetching
ELAPSED_TIME_TOTAL NUMBER   Cumulative value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
ELAPSED_TIME_DELTA NUMBER   Delta value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
IOWAIT_TOTAL NUMBER   Cumulative value of user I/O wait time
IOWAIT_DELTA NUMBER   Delta value of user I/O wait time
CLWAIT_TOTAL NUMBER   Cumulative value of cluster wait time
CLWAIT_DELTA NUMBER   Delta value of cluster wait time
APWAIT_TOTAL NUMBER   Cumulative value of application wait time
APWAIT_DELTA NUMBER   Delta value of application wait time
CCWAIT_TOTAL NUMBER   Cumulative value of concurrency wait time
CCWAIT_DELTA NUMBER   Delta value of concurrency wait time
DIRECT_WRITES_TOTAL NUMBER   Cumulative value of direct writes
DIRECT_WRITES_DELTA NUMBER   Delta value of direct writes
PLSEXEC_TIME_TOTAL NUMBER   Cumulative value of PL/SQL Execution Time
PLSEXEC_TIME_DELTA NUMBER   Delta value of PL/SQL Execution Time
JAVEXEC_TIME_TOTAL NUMBER   Cumulative value of Java Execution Time
JAVEXEC_TIME_DELTA NUMBER   Delta value of Java Execution Time
BIND_DATA RAW(2000)   Bind data