Database Statistics and Wait Events

6.1.2 Database Statistics and Wait Events

Oracle Database provides performance information in a series of dynamic performance views (also known as V$ views).

The dynamic performance views contain a wealth of database statistics and wait events. By using dynamic performance views, a database administrator can perform detailed and customized monitoring on individual SQL operations and database sessions. The dynamic performance views are also a major information source for Automatic Workload Repository (AWR) and other Oracle Database monitoring tools, such as SQL monitor.

The following sections introduce the main dynamic performance views that include Exadata-specific information.

V$SYSSTAT

V$SYSSTAT contains various system-wide statistics relating to the current database instance, including I/O statistics related to Exadata storage server.

For example, you can use the following query to display I/O statistics related to Exadata storage server:

SQL> SELECT name, value
     FROM v$sysstat
     WHERE name like '%cell%' or name like 'physical%'
     ORDER BY name;

These statistics are also in the Global Activity Statistics or Instance Activity Statistics section of the AWR report.

V$SESSION

V$SESSION contains various statistics and wait events for currently active database sessions.

For example, you can use the following query to display a summary that shows the number of sessions that are currently waiting on cell events:

SQL> SELECT event, count(*)
     FROM v$session
     WHERE event like 'cell%'
       AND wait_time = 0  -- session currently waiting
     GROUP BY event
     ORDER BY count(*) desc;

The following query displays event details for sessions that are currently waiting on cell events:

SQL> SELECT event, p1, p2, p3
     FROM v$session
     WHERE event like 'cell%'
       AND wait_time = 0;

For cell wait events in V$SESSION:

The P1 column identifies the cell hash number. You can use the V$CELL view to identify the cell from the cell hash number.
The P2 column identifies the disk hash number. You can use the V$ASM_DISK view to identify the disk name from the disk hash number.

V$SESSION_EVENT

V$SESSION_EVENT displays information on waits for an event by a session.

For example, you can use the following query to display a summary of the wait events for the current session. By examining the output from this query, you can compare the time spent waiting for Exadata storage server events with the time spent waiting for other events in the database.

SQL> SELECT event, total_waits, time_waited_micro, time_waited_micro/decode(total_waits, 0, null, total_waits) avg_wait_time
     FROM v$session_event
     WHERE wait_class != 'Idle'
       AND sid = userenv('SID')
     ORDER BY time_waited_micro desc;

V$SYSTEM_EVENT

V$SYSTEM_EVENT displays system-wide information on total waits for an event.

You can use the following query to display a summary of the wait events since start-up of the current database instance:

SQL> SELECT event, total_waits, time_waited_micro, time_waited_micro/decode(total_waits, 0, null, total_waits) avg_wait_time
     FROM v$system_event
     WHERE wait_class != 'Idle'
     ORDER by time_waited_micro desc;

Information from V$SYSTEM_EVENT is contained in the Wait Events Statistics section of the AWR report.

V$ACTIVE_SESSION_HISTORY

V$ACTIVE_SESSION_HISTORY keeps a history of the active sessions on the system, where active is defined as using CPU or waiting on a non-idle wait event. For each session, V$ACTIVE_SESSION_HISTORY includes a wealth of information on what the session was doing, such as SQL execution plans and wait event details, including information about Exadata storage interactions.

For example, you can use the following query to display cell block reads over the past 5 minutes with latency in excess of 10ms:


SQL> SELECT sample_time, p1, p2, time_waited
     FROM v$active_session_history
     WHERE event = 'cell single block physical read'
       AND wait_time = 0  -- currently waiting
       AND sample_time > sysdate - 5/1440  -- past 5 minutes
       AND time_waited > 10000
     ORDER by time_waited desc;

The Active Session History (ASH) Report section of the AWR report is based on V$ACTIVE_SESSION_HISTORY. Oracle Database also provides a more detailed Active Session History (ASH) report.

V$SQL

V$SQL, and related views like V$SQLAREA, V$SQLAREA_PLAN_HASH, V$SQLSTATS, and V$SQLSTATS_PLAN_HASH, contain information and statistics for SQL statements processed on the database instance.

V$SQL, and related views, include the following columns that contain information about Exadata storage interactions:

PHYSICAL_READ_BYTES — number of bytes read by the SQL
PHYSICAL_READ_REQUESTS — number of read requests issued by the SQL
PHYSICAL_WRITE_BYTES — number of bytes written by the SQL
PHYSICAL_WRITE_REQUESTS — number of write requests issued by the SQL
IO_CELL_OFFLOAD_ELIGIBLE_BYTES — number of bytes eligible for predicate offload to Exadata storage server
IO_CELL_OFFLOAD_RETURNED_BYTES — number of bytes returned by smart scans
IO_INTERCONNECT_BYTES — number of bytes exchanged between the database and the storage servers
IO_CELL_UNCOMPRESSED_BYTES — number of uncompressed bytes read, where uncompressed bytes is the size after decompression
OPTIMIZED_PHY_READ_REQUESTS — number of read requests satisfied from Exadata Smart Flash Cache, or read requests avoided due to storage index or columnar cache

The following example shows a query that displays Exadata offload processing performance data. The output focuses on a query that scans the SALES table. The output shows that all of the data in the SALES table (approximately 5 GB) was eligible for offload processing. In this case, because of offload processing, even though the query performed 5385.75 MB of I/O, only 417.65 MB of data was delivered over the network to the database host.

SQL> SELECT sql_text,
       io_cell_offload_eligible_bytes/1024/1024 offload_eligible_mb,
       io_cell_uncompressed_bytes/1024/1024 io_uncompressed_mb,
       io_interconnect_bytes/1024/1024 io_interconnect_mb,
       io_cell_offload_returned_bytes/1024/1024 cell_return_bytes_mb,
       (physical_read_bytes + physical_write_bytes)/1024/1024 io_disk_mb 
     FROM v$sql 
     WHERE UPPER(sql_text) LIKE '%FROM SALES%';

SQL_TEXT                      OFFLOAD_ELIGIBLE_MB  IO_UNCOMPRESSED_MB  IO_INTERCONNECT_MB  CELL_RETURN_BYTES_MB  IO_DISK_MB
----------------------------  -------------------  ------------------  ------------------  --------------------  –---------
select count(*) from sales                5283.06             5283.06              520.34                417.65     5385.75

Information from V$SQL is displayed in the SQL Statistics section of the AWR report.

V$SEGMENT_STATISTICS

V$SEGMENT_STATISTICS contains statistics on a per-segment basis. The segment-level statistics can be used to detect specific objects, such as tables or indexes, that are performing optimized reads from Exadata storage.

The optimized physical read segment statistic records the number of read requests for objects that are read from Exadata Smart Flash Cache or reads that are avoided through the use of storage index or columnar cache. The optimized physical writes statistic records the number of write requests for an object that first went to Exadata Smart Flash Cache. Such write requests can be synchronized to the disk later in a lazy manner to free up cache space.

The following example query displays objects that are associated with more than 1000 optimized reads from Exadata storage. A similar query can be used to determine the objects that are associated with very few optimized reads.

SQL> SELECT object_name, value
     FROM v$segment_statistics
     WHERE statistic_name='optimized physical reads' 
       AND value>1000 
     ORDER BY value;

Information from V$SEGMENT_STATISTICS is displayed in the Segment Statistics section of the AWR report.

Parent topic: Introducing Exadata Monitoring Tools and Information Sources