9.76 V$SQLSTATS
V$SQLSTATS displays basic performance statistics for SQL cursors and contains one row per SQL statement (that is, one row per unique value of SQL_ID). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.
| Column | Datatype | Description |
|---|---|---|
|
|
|
First thousand characters of the SQL text for the current cursor |
|
|
|
Full text for the SQL statement exposed as a |
|
|
|
SQL identifier of the parent cursor in the library cache |
|
|
|
Last time the statistics of a contributing cursor were updated |
|
|
|
Address of the contributing cursor that last updated these statistics |
|
|
|
Numeric representation of the current SQL plan for this cursor. Comparing one |
|
|
|
Number of parse calls for all cursors with this SQL text and plan |
|
|
|
Number of disk reads for all cursors with this SQL text and plan |
|
|
|
Number of direct writes for all cursors with this SQL text and plan |
|
|
|
Number of direct reads for all cursors with this SQL text and plan |
|
|
|
Number of buffer gets for all cursors with this SQL text and plan |
|
|
|
Total number of rows the parsed SQL statement returns |
|
|
|
Number of times the transaction failed to serialize, producing |
|
|
|
Number of fetches associated with the SQL statement |
|
|
|
Number of executions that took place on this object since it was brought into the library cache |
|
|
|
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 |
|
|
|
Number of times the object was either loaded or reloaded |
|
|
|
number of cursors present in the cache with this SQL text and plan |
|
|
|
Number of times this child cursor has been invalidated |
|
|
|
Total number of executions performed by parallel execution servers ( |
|
|
|
CPU time (in microseconds) used by this cursor for parsing, executing, and fetching |
|
|
|
Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching. If the cursor uses parallel execution, then |
|
|
|
Average hard parse time (in microseconds) used by this cursor |
|
|
|
Application wait time (in microseconds) |
|
|
|
Concurrency wait time (in microseconds) |
|
|
|
Cluster wait time (in microseconds). This value is specific to Oracle RAC. It shows the total time spent waiting for all waits that are categorized under the cluster class of wait events. The value is this column is an accumulated wait time spent waiting for Oracle RAC cluster resources. |
|
|
|
User I/O wait time (in microseconds) |
|
|
|
PL/SQL execution time (in microseconds) |
|
|
|
Java execution time (in microseconds) |
|
|
|
Number of sorts that were done for the child cursor |
|
|
|
Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan |
|
|
|
Total shared memory (in bytes) occupied by all cursors with this SQL text and plan if they were to be fully loaded in the shared pool (that is, cursor size) |
|
|
|
Typecheck memory |
|
|
|
Number of I/O bytes which can be filtered by the Exadata storage system See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
|
Number of I/O bytes exchanged between Oracle Database and the storage system. Typically used for Cache Fusion or parallel queries. |
|
|
|
Number of physical read I/O requests issued by the monitored SQL. The requests may not be disk reads. |
|
|
|
Number of bytes read from disks by the monitored SQL |
|
|
|
Number of physical write I/O requests issued by the monitored SQL |
|
|
|
Number of bytes written to disks by the monitored SQL |
|
|
|
Signature used when the |
|
|
|
Signature used when the |
|
|
|
Number of uncompressed bytes (that is, size after decompression) that are offloaded to the Exadata cells See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
|
Number of bytes that are returned by Exadata cell through the regular I/O path See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
|
Number of parse calls for the cursor since the last Automatic Workload Repository (AWR) snapshot See Also: Oracle Database Concepts for an introduction to AWR |
|
|
|
Number of disk reads for the cursor since the last AWR snapshot |
|
|
|
Number of direct writes for the cursor since the last AWR snapshot |
|
|
|
Number of direct reads for the cursor since the last AWR snapshot |
|
|
|
Number of buffer gets for the cursor since the last AWR snapshot |
|
|
|
Number of rows returned by the cursor since the last AWR snapshot |
|
|
|
Number of fetches for the cursor since the last AWR snapshot |
|
|
|
Number of executions for the cursor since the last AWR snapshot |
|
|
|
Number of executions performed by parallel execution servers since the last AWR snapshot |
|
|
|
Number of times the cursor was fully executed since the last AWR snapshot |
|
|
|
CPU time (in microseconds) for the cursor since the last AWR snapshot |
|
|
|
Database time (in microseconds) for the cursor since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) in the Application wait class since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) in the Concurrency wait class since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) in the Cluster wait class since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) in the User I/O wait class since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) executing PL/SQL since the last AWR snapshot |
|
|
|
Time spent by the cursor (in microseconds) executing Java since the last AWR snapshot |
|
|
|
Number of sorts for the cursor since the last AWR snapshot |
|
|
|
Number of times the cursor was loaded since the last AWR snapshot |
|
|
|
Number of times the cursor was invalidated since the last AWR snapshot |
|
|
|
Number of physical read I/O requests for the cursor since the last AWR snapshot |
|
|
|
Number of bytes read from disk for the cursor since the last AWR snapshot |
|
|
|
Number of physical write I/O requests for the cursor since the last AWR snapshot |
|
|
|
Number of bytes written to disk for the cursor since the last AWR snapshot |
|
|
|
Number of I/O bytes exchanged between the Oracle database and the storage system for the cursor since the last AWR snapshot |
|
|
|
Number of I/O bytes which can be filtered by the Exadata storage system for the cursor since the last AWR snapshot See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
|
Number of uncompressed bytes that are offloaded to the Exadata cell for the cursor since the last AWR snapshot See Also: Oracle Exadata Storage Server Software documentation for more information |
|
|
|
The ID of the container to which the data pertains. Possible values include:
|
|
|
|
The database ID of the PDB |
|
|
|
Number of times that a parent cursor became obsolete |
|
|
|
Number of executions attempted on this object, but prevented due to the SQL statement being in quarantine |
|
|
|
Number of executions attempted on this object, but prevented due to the SQL statement being in quarantine, since the last AWR snapshot |
Footnote 1 This column is available starting with Oracle Database release 19c, version 19.1.