SQL Statistics Daily
The SQL Statistics Daily data object is available for Management Agent Cloud Service-managed, Oracle Database Cloud Service databases, and Oracle Autonomous Databases with Full Feature Set enabled.
Raw data collection
Ops Insights SQL Warehouse has been enhanced to collect rows from the Oracle database internal view V$SQLSTATS periodically. This instance-level view records numerous execution performance instrumentation measures at the individual SQL_ID level.
SQL performance data is collected using the following rough pseudo-logic:
For each RAC instance--
- All SQL active since last collection are ranked
- Top 2000 are selected
- Certain SQL are filtered out
By default, the collection runs every ½ hour, resulting in 48 collections per day.
Delta Computation
SQL statistics collected as above require significant post-processing in order to provide the most value as a source of information about application SQL workloads.
The challenge is that these statistics are generally monotonically increasing values per SQL_ID that have accumulated since the SQL_ID was first executed. In order to find out the progress of these measures over time successive values must be subtracted from each other to produce “deltas” in the measures.
This complexity is compounded by the fact that SQL_ID are not all collected every time so there can be deltas of different sizes and also non-deltas.
The SQL Stats Daily data object handles all this complexity for you.
Join to Entities
SQL Stats Daily also joins to configuration data for the database instance from which it was collected, obtaining a number of important grouping dimensions to use for aggregations.
Daily Rollup
Finally, all deltas (up to 48) for a given SQL_ID on a given day are SUMMED to yield cumulative values for that SQL on that database instance on that day. That is, the data object has one row per SQL_ID per database per day.
Thus is created the SQL Stats Daily data object.
In the table, METRIC in the Column Category is a Measure attribute column.
Table 16-2 SQL Statistics Daily
| Field Name | Name | Column Type | Column Category | Description |
|---|---|---|---|---|
| DAY_OF_WEEK | Collection day of week | VARCHAR2 | DIMENSION | Collection day in weekly range 1-7 (Mon-Sun) |
| DAY_OF_MONTH | Collection day of month | VARCHAR2 | DIMENSION | Collection day in monthly range 1-31 |
| ROLLUP_TIME_UTC | Rollup time (UTC) | TIMESTAMP | TIME_DIMENSION | Beginning timestamp of the rollup period (UTC) |
| INSTANCENAME | Instance name | VARCHAR2 | DIMENSION | Name of the database instance |
| SQL_ID | SQL identifier | VARCHAR2 | DIMENSION | SQL identifier of the parent cursor in the library cache |
| FMS | Force matching signature | VARCHAR2 | DIMENSION | Signature used when the CURSOR_SHARING parameter is set to FORCE |
| EMS | Exact matching signature | VARCHAR2 | DIMENSION |
Signature used when the CURSOR_SHARING parameter is set to EXACT
|
| PARSE_CALLS | Parse calls | NUMBER | METRIC | Number of parse calls for all cursors with this SQL text and plan |
| DISK_READS | Disk reads | NUMBER | METRIC | Number of disk reads for all cursors with this SQL text and plan |
| DIRECT_WRITES | Direct writes | NUMBER | METRIC | Number of direct writes for all cursors with this SQL text and plan |
| DIRECT_READS | Direct reads | NUMBER | METRIC | Number of direct reads for all cursors with this SQL text and plan |
| BUFFER_GETS | Buffer gets | NUMBER | METRIC | Number of buffer gets for all cursors with this SQL text and plan |
| ROWS_PROCESSED | Rows processed | NUMBER | METRIC | Number of rows the parsed SQL statement returns |
| FETCHES | Fetches | NUMBER | METRIC | Number of fetches associated with the SQL statement |
| EXECUTIONS | Executions | NUMBER | METRIC | Number of executions that took place on this object since it was brought into the library cache |
| LOADS | Loads | NUMBER | METRIC | Number of times the object was either loaded or reloaded |
| VERSION_COUNT | Cursors | NUMBER | METRIC | Number of cursors present in the cache with this SQL text and plan |
| INVALIDATIONS | Child cursor invalidations | NUMBER | METRIC | Number of times this child cursor has been invalidated |
| PX_SERVERS_EXECUTIONS | Parallel execution server count | NUMBER | METRIC |
Number of executions performed by parallel execution servers (0 when the statement has never been executed in parallel)
|
| CPU_TIME | CPU time | NUMBER | METRIC | CPU time used by this cursor for parsing, executing, and fetching (micro seconds) |
| ELAPSED_TIME | Elapsed time | NUMBER | METRIC | Elapsed time used by this cursor for parsing, executing, and fetching. If the cursor uses parallel execution, then ELAPSED_TIME is the cumulative time for the query coordinator, plus all parallel query slave processes (micro seconds) |
| AVG_HARD_PARSE_TIME | Average hard parse time | NUMBER | METRIC | Average hard parse time used by this cursor (micro seconds) |
| APPLICATION_WAIT_TIME | Application wait time | NUMBER | METRIC | Application wait time (micro seconds) |
| CONCURRENCY_WAIT_TIME | Concurrency wait Time | NUMBER | METRIC | Concurrency wait Time (micro seconds) |
| CLUSTER_WAIT_TIME | Cluster wait time | NUMBER | METRIC | Accumulated wait time spent waiting for Oracle RAC cluster resources. This value is specific to Oracle RAC (micro seconds) |
| USER_IO_WAIT_TIME | User I/O wait time | NUMBER | METRIC | User I/O wait time (micro seconds) |
| PLSQL_EXEC_TIME | PL/SQL execution time | NUMBER | METRIC | PL/SQL execution time (micro second) |
| JAVA_EXEC_TIME | Java execution time | NUMBER | METRIC | Java execution time (micro second) |
| SORTS | Child cursor sorts | NUMBER | METRIC | Number of sorts that were done for the child cursor |
| IO_CELL_OFFLOAD_ELIGIBLE_BYTES | Cell offload eligible bytes | NUMBER | METRIC | Number of I/O bytes which can be filtered by the Exadata storage system |
| IO_INTERCONNECT_BYTES | I/O interconnect bytes | NUMBER | METRIC | Number of I/O bytes exchanged between Oracle Database and the storage system, typically used for Cache Fusion or parallel queries |
| PHYSICAL_READ_REQUESTS | Read requests | NUMBER | METRIC | Number of physical read I/O requests issued by the monitored SQL |
| PHYSICAL_READ_BYTES | Read bytes | NUMBER | METRIC | Number of bytes read from disks by the monitored SQL |
| PHYSICAL_WRITE_REQUESTS | Write requests | NUMBER | METRIC | Number of physical write I/O requests issued by the monitored SQL |
| PHYSICAL_WRITE_BYTES | Write bytes | NUMBER | METRIC | Number of bytes written to disks by the monitored SQL |
| IO_CELL_UNCOMPRESSED_BYTES | Cell uncompressed bytes | NUMBER | METRIC | Number of uncompressed bytes that are offloaded to the Exadata cells |
| IO_CELL_OFFLOAD_RETURNED_BYTES | Cell offload returned bytes | NUMBER | METRIC | Number of bytes that are returned by Exadata cell through the regular I/O path |
| NAME | Database resource name | VARCHAR2 | DIMENSION | Database resource name |
| VERSION | Database version | VARCHAR2 | DIMENSION | Database version |
| CPUCOUNT | CPU count | VARCHAR2 | DIMENSION | CPU count |
| ENTITY_TYPE | External type | VARCHAR2 | DIMENSION | Resource external type |
| DISPLAY_NAME | Database resource display name | VARCHAR2 | DIMENSION | Database resource display name (database name) |
| ENTITY_SOURCE | Source type | VARCHAR2 | DIMENSION | Source of the database resource, for example: EM_MANAGED_EXTERNAL_DATABASE, MACS_MANAGED_EXTERNAL_DATABASE, PE_COMANAGED_DATABASE |
| OPSI_ENTITY_TYPE | Database type | VARCHAR2 | DIMENSION | Database type, for example: ADB-S, ATP-D, EXTERNAL-HOST, EXTERNAL-PDB, EXTERNAL-NONCDB |
| OPSI_ID | Resource OCID | VARCHAR2 | DIMENSION | OCID of the database insight resource |
| SOURCE_IDENTIFIER | Telemetry source identifier | VARCHAR2 | DIMENSION | Enterprise Manager GUID or Management Agent OCID |
| SOURCE_ENTITY_IDENTIFIER | Telemetry source entity identifier | VARCHAR2 | DIMENSION | Enterprise Manager GUID or Management Agent OCID |
| ASSOCIATED_RESOURCE_ID | Cloud database resource OCID | VARCHAR2 | DIMENSION | Cloud database resource OCID |
| SERVICE | Service | VARCHAR2 | DIMENSION | Last collected service name for the day associated with the SQL |
| MODULE | Module | VARCHAR2 | DIMENSION | Last collected module name for the day that was executing when the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_MODULE |
| ACTION | Action | VARCHAR2 | DIMENSION | Last collected action name for the day that was executing when the SQL statement was first parsed, which is set by calling DBMS_APPLICATION_INFO.SET_ACTION |
| PLAN_HASH_VALUE | Plan hash value | VARCHAR2 | DIMENSION | Last collected plan hash value for the day associated with the SQL |
| PLAN_HASH_VALUE_LISTAGG | Plan list | VARCHAR2 | DIMENSION | List of distinct collected plan hash values for the day associated with the SQL |
| PLAN_HASH_VALUE_COUNT | Plan count | NUMBER | METRIC | Count of all distinct collected plan hash values for the day associated with the SQL |
| OPTIMIZER_COST | Optimizer cost | NUMBER | METRIC | Maximum cost of the query execution plan |
| COMMAND_TYPE | SQL command number | NUMBER | DIMENSION | SQL command number |
| COMMAND_NAME | Command name | VARCHAR2 | DIMENSION | SQL command name, for example: SELECT, UPDATE, CREATE |
| SQL_TEXT | SQL text | VARCHAR2 | DIMENSION | First 1000 characters of the SQL text |