SQL Statistics Daily

SQL Statistics Daily is a data object exposed through SQL Explorer and is a daily aggregate of per-SQL performance statistics that are collected continuously on Ops Insights-enabled databases and Enterprise Manager managed database targets.
Note

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.

Note

In the table, METRIC in the Column Category is a Measure attribute column.

Table 16-4 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