7 Monitoring and Tuning Oracle Exadata System Software

This chapter discusses monitoring and tuning Oracle Exadata System Software. Oracle provides utilities for most monitoring tasks in the Oracle Oracle Exadata Storage Server environment.

• Understanding Metrics and Alerts

• Monitoring Oracle Exadata Storage Server with Metrics

• Monitoring IORM with Metrics

• Monitoring Requests and Alerts for Oracle Exadata Storage Server

• Monitoring Oracle Exadata Storage Server using Views

• Understanding Oracle Exadata System Software Wait Events

• Optimizing Performance

• Using the SQL EXPLAIN PLAN Command with Oracle Exadata System Software

• Enabling or Disabling In-Memory Columnar Caching on Storage Servers

7.1 Understanding Metrics and Alerts

Metrics and alerts help you monitor Oracle Exadata System Software.

Metrics are associated with objects such as cells and cell disks, and can be cumulative, rate, or instantaneous. By reviewing metrics and setting thresholds for alerts, you can tune and optimize resource management.

7.1.1 About Metrics

Metrics are recorded observations of important run-time properties or internal instrumentation values of the storage cell and its components, such as cell disks or grid disks. Metrics are a series of measurements that are computed and retained in memory for an interval of time, and stored on a disk for a more permanent history.

Metric values typically record either the absolute value of a cumulative counter or a rate of change in a cumulative counter over an observed time period. Some metrics are used to record the time of state transitions as well.

Metric values can cause alerts to signal by comparing those values against threshold boundaries. Metrics can be associated with warning and critical thresholds when extreme values in the metric might indicate a problem or other event of interest to an administrator.

7.1.2 About Alerts

Alerts represent events of importance occurring within the storage cell, typically indicating that storage cell functionality is either compromised or in danger of failure.

An administrator should investigate these alerts, because they might require corrective or preventive action. There are three types of alerts, informational, warning or critical. Alerts are typically propagated to a monitoring infrastructure, such as Oracle Enterprise Manager Cloud Control, for notification to storage administrators. Examples of possible alerts that can be used are physical disk failure, disk read/write errors, cell temperature exceeding recommended value, Oracle Exadata System Software failure, and excessive I/O latency. Metrics can be used to signal alerts using warning or critical threshold values. When the metric value exceeds the threshold value, an alert can be signaled.

Alerts are either stateful or stateless. Stateful alerts represent observable cell states that can be subsequently retested to detect whether the state has changed, so that a previously observed alert condition is no longer a problem. Stateless alerts represent point-in-time events that do not represent a persistent condition; they simply show that something has occurred.

Note:

There are built-in thresholds for ambient temperature. If the temperature is too low or too high, an alert is automatically sent.

7.2 Monitoring Oracle Exadata Storage Server with Metrics

You can monitor a cell by viewing the Oracle Exadata Storage Server metrics. Metrics are of the following type:

• Cumulative: Statistics since the metric was created.

• Instantaneous: Values at the time the metric is collected.

• Rate: Computed value for metric by averaging statistics over a period of time.

In the table descriptions, small means less than or equal to 128 KB, and large means more than 128 KB.

This section contains the following topics:

• Displaying Metrics

• Monitoring Cell Metrics

• Monitoring Cell Disk Metrics

• Monitoring Flash Cache Metrics

• Monitoring Exadata Smart Flash Log with Metrics

• Monitoring Grid Disk Metrics

• Monitoring Host Interconnection Metrics

• Monitoring Smart I/O Metrics

7.2.1 Displaying Metrics

You can use the CellCLI LIST command to display and monitor metrics for cell objects. You can display metric definitions, current metrics, and metric history using the LIST command. The following sections describe how to use the LIST command with metrics.

7.2.1.1 Displaying Metric Definitions

Use the LIST METRICDEFINITION command to display the metric definitions for the cell. A metric definition listing shows the configuration of a metric.

Example 7-1 Displaying Metric Definitions

This example shows how to display attributes for the METRICDEFINITION object.

CellCLI> LIST METRICDEFINITION CL_CPUT DETAIL

CellCLI> LIST METRICDEFINITION WHERE objectType = 'GRIDDISK'

CellCLI> LIST METRICDEFINITION WHERE name LIKE 'CD_IO_RQ.*' -
         ATTRIBUTES name, metricType, description

7.2.1.2 Displaying Current Metrics

Use the LIST METRICCURRENT command to display the current metric values for the cell. A current metric listing shows a set of observations on the current value of an individual metric.

Example 7-2 Displaying Current Metric Values

This example shows how to display attributes for the METRICCURRENT object.

CellCLI> LIST METRICCURRENT CL_TEMP DETAIL

         name:                   CL_TEMP
         alertState:             normal
         collectionTime:         2009-12-17T15:32:25-08:00
         metricObjectName:       abcd2x3
         metricType:             Instantaneous
         metricValue:            48.0 C
         objectType:             CELL

CellCLI> LIST METRICCURRENT WHERE objectType = 'CELLDISK' AND                 - 
         metricValue != 0 ATTRIBUTES name, metricObjectName,                  -
         metricValue, collectionTime

         CD_IO_BY_R_LG   CD_00_abcd2x3    1.9 MB  2009-12-17T15:46:52-08:00
         CD_IO_BY_R_LG   CD_01_abcd2x3    1.0 MB  2009-12-17T15:46:52-08:00
         CD_IO_BY_R_LG   CD_02_abcd2x3    4.1 MB  2009-12-17T15:46:52-08:00
         CD_IO_BY_R_LG   CD_03_abcd2x3    9.5 MB  2009-12-17T15:46:52-08:00
         CD_IO_BY_R_LG   CD_04_abcd2x3    0.1 MB  2009-12-17T15:46:52-08:00
         CD_IO_BY_R_LG   CD_05_abcd2x3    0.4 MB  2009-12-17T15:46:52-08:00
         ...

7.2.1.3 Displaying Metric History

Use the LIST METRICHISTORY command to display the metric history for the cell. A metric history listing shows a collection of past individual observations of all metric values.

The retention period for metric history files is specified by the metricHistoryDays cell attribute. You can modify this setting with the CellCLI ALTER CELL command.

Example 7-3 Displaying Metric History Values

This example shows how to display attributes for the METRICHISTORY object.

CellCLI> LIST METRICHISTORY CD_IO_RQ_R_LG WHERE alertState='critical' DETAIL

CellCLI> LIST METRICHISTORY WHERE objectType = 'CELLDISK' AND metricValue != 0   -
         AND collectionTime > '2009-08-12T09:10:51-07:00' -ATTRIBUTES name,      -
         metricObjectName, metricValue, collectionTime

7.2.2 Monitoring Cell Metrics

Cell metrics provide information about the cell, such as CPU utilization, memory or network interconnections. To display cell metrics, use an object type attribute equal to CELL with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects. Example 7-4 shows how to display cell metrics.

The following table lists the metric names and descriptions for cell metrics. The cell for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects.

Note:

Oracle Exadata System Software may utilize most of the memory available on a cell. Oracle Exadata System Software has internal controls to avoid over-utilization of the memory. In the event of low swap space, Restart Server (RS) generates an alert, and restarts the Oracle Exadata Storage Server CELLSRV process. These alerts should be reported to Oracle Support Services. Oracle does not recommend the configuration of other alerts for memory utilization monitoring for Oracle Exadata Storage Servers by Oracle Enterprise Manager.

Table 7-1 Cell Metrics and Descriptions

Metric	Description
CL_BBU_CHARGE	Disk controller battery charge.
CL_BBU_TEMP	Temperature of disk controller battery.
CL_CPUT	The cell CPU utilization which is the instantaneous percentage of time over the previous minute that the system CPUs were not idle (from /proc/stat).
CL_CPUT_CS	The percentage of CPU time used by CELLSRV.
CL_CPUT_MS	The percentage of CPU time used by MS.
CL_FANS	The instantaneous number of working fans on the cell.
CL_FSUT	The percentage of total space utilized on the file system that is currently in use. This metric shows the space utilization in the various files systems on the cell.
CL_IO_RQ_NODATA	The number of I/O requests that did not return data.
CL_IO_RQ_NODATA_SEC	The number of I/O requests that did not return data per second.
CL_MEMUT	The percentage of total physical memory used on the cell.
CL_MEMUT_CS	The percentage of physical memory used by CELLSRV.
CL_MEMUT_MS	The percentage of physical memory used by MS.
CL_RUNQ	The instantaneous average number (over the preceding minute) of processes in the Linux run queue marked running or uninterruptible (from /proc/loadavg).
CL_SWAP_IN_BY_SEC	The number of swap pages read in KB per second.
CL_SWAP_OUT_BY_SEC	The number of swap pages written in KB per second.
CL_SWAP_USAGE	The percentage of swap space used.
CL_TEMP	The instantaneous temperature (Celsius) of the server, provided by the Baseboard Management Controller (BMC).
CL_VIRTMEM_CS	The amount of virtual memory used by CELLSRV in megabytes.
CL_VIRTMEM_MS	Total memory(resident and swap) used by MS in megabytes.
IORM_MODE	The I/O Resource Management (IORM) objective for the cell.
N_HCA_MB_RCV_SEC	The number of megabytes received by the InfiniBand interfaces per second.
N_HCA_MB_TRANS_SEC	The number of megabytes transmitted by the InfiniBand interfaces per second.
N_NIC_KB_RCV_SEC	The number of kilobytes received by the Ethernet interfaces per second.
N_NIC_KB_TRANS_SEC	The number of kilobytes transmitted by the Ethernet interfaces per second.
N_NIC_RCV_SEC	The rate which is the total number of I/O packets received by interconnections per second.
N_NIC_TRANS_SEC	The rate which is the total number of I/O packets transmitted by interconnections per second.

Example 7-4 Displaying Cell Metrics

CellCLI> LIST METRICCURRENT CL_CPUT DETAIL

         name:                   CL_CPUT
         alertState:             normal
         collectionTime:         2009-12-17T15:54:25-08:00
         metricObjectName:       abcd2x3
         metricType:             Instantaneous
         metricValue:            4.3 %
         objectType:             CELL

7.2.3 Monitoring Cell Disk Metrics

Cell disk metrics provide information about the I/O load for cell disks, such as the number of large blocks read from a cell disk. To display cell disk metrics, use an object type attribute equal to CELLDISK with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following table lists the metric names and descriptions for cell disk metrics. The cell disk for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute.

Table 7-2 Cell Disk Metrics and Descriptions

Metric	Description
CD_BY_FC_DIRTY	The number of megabytes in flash cache that are not synchronized to the cell disk.
CD_IO_BY_R_LG	The cumulative number of megabytes read in large blocks from a cell disk.
CD_IO_BY_R_LG_SEC	The rate which is the number of megabytes read in large blocks per second from a cell disk.
CD_IO_BY_R_SCRUB	The number of megabytes read from a cell disk by the scrubbing job.
CD_IO_BY_R_SCRUB_SEC	The number of megabytes read per second from a cell disk by the scrubbing job.
CD_IO_BY_R_SM	The cumulative number of megabytes read in small blocks from a cell disk.
CD_IO_BY_R_SM_SEC	The rate which is the number of megabytes read in small blocks per second from a cell disk.
CD_IO_BY_W_LG	The cumulative number of megabytes written in large blocks on a cell disk.
CD_IO_BY_W_LG_SEC	The rate which is the number of megabytes written in large blocks per second on a cell disk.
CD_IO_BY_W_SM	The cumulative number of megabytes written in small blocks on a cell disk.
CD_IO_BY_W_SM_SEC	The rate which is the number of megabytes written in small blocks per second on a cell disk.
CD_IO_ERRS	The cumulative number of I/O errors on a cell disk.
CD_IO_ERRS_MIN	The rate of I/O errors on a cell disk per minute.
CD_IO_ERRS_SCRUB	The number of bad 1 MB blocks detected during a scrubbing job on a cell disk.
CD_IO_LOAD	The average I/O load from cell disks. I/O load specifies the length of the disk queue. It is similar to iostat's avgqu-sz, but I/O load is a weighted value depending on the type of disk: For hard disks, a large I/O has three times the weight of a small I/O. For flash disks, large and small I/Os have the same weight. Because this metric is computed by IORM, this metric is also available per database or PDB.
CD_IO_RQ_R_LG	The cumulative number of requests to read large blocks from a cell disk.
CD_IO_RQ_R_LG_SEC	The rate which is the number of requests to read large blocks per second from a cell disk.
CD_IO_RQ_R_SCRUB	The number of requests to read from a cell disk by the scrubbing job.
CD_IO_RQ_R_SCRUB_SEC	The number of requests to read per second from a cell disk by the scrubbing job.
CD_IO_RQ_R_SM	The cumulative number of requests to read small blocks from a cell disk.
CD_IO_RQ_R_SM_SEC	The rate which is the number of requests to read small blocks per second from a cell disk.
CD_IO_RQ_W_LG	The cumulative number of requests to write large blocks to a cell disk.
CD_IO_RQ_W_LG_SEC	The rate which is the number of requests to write large blocks per second to a cell disk.
CD_IO_RQ_W_SM	The cumulative number of requests to write small blocks to a cell disk.
CD_IO_RQ_W_SM_SEC	The rate which is the number of requests to write small blocks per second to a cell disk.
CD_IO_ST_RQ	Average service time per request for small I/O requests to a cell disk.
CD_IO_TM_R_LG	The cumulative latency of reading large blocks from a cell disk. Units are microseconds per request.
CD_IO_TM_R_LG_RQ	The rate which is the average latency of reading large blocks per request to a cell disk. Units are microseconds per request.
CD_IO_TM_R_SM	The cumulative latency of reading small blocks from a cell disk.
CD_IO_TM_R_SM_RQ	The rate which is the average latency of reading small blocks per request from a cell disk. Units are microseconds per request.
CD_IO_TM_W_LG	The cumulative latency of writing large blocks to a cell disk. Units are microseconds per request.
CD_IO_TM_W_LG_RQ	The rate which is the average latency of writing large blocks per request to a cell disk. Units are microseconds per request.
CD_IO_TM_W_SM	The cumulative latency of writing small blocks to a cell disk. Units are microseconds per request.
CD_IO_TM_W_SM_RQ	The rate which is the average latency of writing small blocks per request to a cell disk. Units are microseconds per request.
CD_IO_UTIL	The percentage of device utilization for the cell disk. This metric is similar to iostat's %util. Because this metric is computed by IORM, it is also available per database or PDB.
CD_IO_UTIL_LG	The percentage of disk resources utilized by large requests for the cell disk.
CD_IO_UTIL_SM	The percentage of disk resources utilized by small requests for the cell disk.

7.2.4 Monitoring Flash Cache Metrics

Flash cache metrics provide information about the utilization of flash cache, such as the number of megabytes read per second from flash cache.

To display flash cache metrics, use an object type attribute equal to FLASHCACHE with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following table lists the metric names and descriptions for flash cache metrics. The flash cache for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects.

Table 7-3 Flash Cache Metrics and Descriptions

Metric	Description
FC_BY_ALLOCATED	The number of megabytes allocated in flash cache. Tracks how many cachelines are used in the flash cache. If the value is close to the flash cache size, then the flash cache is fully populated.
FC_BY_ALLOCATED_DIRTY	The number of megabytes allocated for unflushed data in flash cache.
FC_BY_ALLOCATED_OLTP	The number of megabytes allocated for OLTP data in flash cache.
FC_BY_DIRTY	The number of megabytes in flash cache that are not synchronized to the grid disks.
FC_BY_STALE_DIRTY	The number of megabytes in flash cache which cannot be synchronized because the cached disks are not accessible.
FC_BY_USED	The number of megabytes used in flash cache. Tracks the valid bytes in flash cache. For some workloads, like OLTP, the FC_BY_USED can be much less than the value of FC_BY_ALLOCATED because an OLTP write might only use 8K out of a 64K cacheline.
FC_BYKEEP_DIRTY	The number of megabytes allocated for unflushed keep objects in flash cache.
FC_BYKEEP_OLTP	The number of megabytes for OLTP keep objects in flash cache.
FC_BYKEEP_OVERWR	The number of megabytes pushed out of flash cache because of the space limit for keep objects.
FC_BYKEEP_OVERWR_SEC	The number of megabytes per second pushed out of flash cache because of space limit for keep objects.
FC_BYKEEP_USED	The number of megabytes used for keep objects on flash cache.
FC_COL_BY_USED	The number of megabytes used in columnar flash cache.
FC_COL_BYKEEP_USED	The number of megabytes used for keep objects in columnar flash cache.
FC_COL_IO_BY_R	The number of megabytes that were read from columnar flash cache.
FC_COL_IO_BY_R_ELIGIBLE	The number of megabytes eligible to read from columnar flash cache.
FC_COL_IO_BY_R_ELIGIBLE_SEC	The number of megabytes per second eligible to read from columnar flash cache.
FC_COL_IO_BY_R_SEC	The number of megabytes per second that were read from columnar flash cache.
FC_COL_IO_BY_SAVED	The number of megabytes saved by reads from columnar flash cache.
FC_COL_IO_BY_SAVED_SEC	The number of megabytes saved per second by reads from columnar flash cache.
FC_COL_IO_BY_W_POPULATE	The number of megabytes that are population writes into columnar flash cache due to read misses.
FC_COL_IO_BY_W_POPULATE_SEC	The number of megabytes per second that are population writes into columnar flash cache due to read misses.
FC_COL_IO_BYKEEP_R	The number of megabytes read from columnar flash cache for keep objects.
FC_COL_IO_BYKEEP_R_SEC	The number of megabytes read per second from columnar flash cache for keep objects.
FC_COL_IO_RQ_R	The number of requests that were read from columnar flash cache.
FC_COL_IO_RQ_R_ELIGIBLE	The number of read requests eligible for columnar flash cache.
FC_COL_IO_RQ_R_ELIGIBLE_SEC	The number of read requests per second eligible for columnar flash cache.
FC_COL_IO_RQ_R_SEC	The number of requests per second that were read from columnar flash cache.
FC_COL_IO_RQ_W_POPULATE	The number of requests that are population writers into columnar flash cache due to read misses.
FC_COL_IO_RQ_W_POPULATE_SEC	The number of requests per second that are population writes into columnar flash cache due to read misses.
FC_COL_IO_RQKEEP_R	The number of requests read for keep objects from columnar flash cache.
FC_COL_IO_RQKEEP_R_SEC	The number of requests read per second for keep objects from columnar flash cache.
FC_IO_BY_ALLOCATED_OLTP	The number of megabytes allocated for OLTP data in flash cache.
FC_IO_BY_DISK_WRITE	The number of megabytes written from flash cache to hard disks.
FC_IO_BY_DISK_WRITE_SEC	The number of megabytes per second written from flash cache to hard disks.
FC_IO_BY_R	The number of megabytes read from flash cache.
FC_IO_BY_R_ACTIVE_SECONDARY	The number of megabytes for active secondary reads satisfied from flash cache.
FC_IO_BY_R_ACTIVE_SECONDARY_MISS	The number of megabytes for active secondary reads not satisfied from flash cache.
FC_IO_BY_R_ACTIVE_SECONDARY_MISS_SEC	The number of megabytes per second for active secondary reads not satisfied from flash cache.
FC_IO_BY_R_ACTIVE_SECONDARY_SEC	The number of megabytes per second for active secondary reads satisfied from flash cache.
FC_IO_BY_R_DISK_WRITER	The number of megabytes read from flash cache by disk writer.
FC_IO_BY_R_DISK_WRITER_SEC	The number of megabytes per second read from flash cache by disk writer.
FC_IO_BY_R_DW	The number of megabytes of DW data read from flash cache.
FC_IO_BY_R_MISS	The number of megabytes read from disks because not all requested data was in flash cache.
FC_IO_BY_R_MISS_DW	The number of megabytes of DW data read from disks because not all requested data was in flash cache.
FC_IO_BY_R_MISS_SEC	The number of megabytes read from disks per second because not all requested data was in flash cache.
FC_IO_BY_R_SEC	The number of megabytes read per second from flash cache.
FC_IO_BY_R_SKIP	The number of megabytes read from disks for I/O requests that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_BY_R_SKIP_FC_THROTTLE	The number of megabytes read from disk for I/O requests that bypass flash cache due to heavy load on flash cache.
FC_IO_BY_R_SKIP_FC_THROTTLE_SEC	The number of megabytes read per second from disk for I/O requests that bypass flash cache due to heavy load on flash cache.
FC_IO_BY_R_SKIP_LG	The number of megabytes read from disk for I/O requests that bypass flash cache due to the large I/O size.
FC_IO_BY_R_SKIP_LG_SEC	The number of megabytes read per second from disk for I/O requests that bypass flash cache due to the large I/O size.
FC_IO_BY_R_SKIP_NCMIRROR	The number of megabytes read from disk for I/O requests that bypass flash cache as the I/O is on non-primary, non-active secondary mirror.
FC_IO_BY_R_SKIP_SEC	The number of megabytes read from disks per second for I/O requests that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_BY_W	The number of megabytes written to flash cache.
FC_IO_BY_W_DISK_WRITER	The number of megabytes written to hard disks by disk writer.
FC_IO_BY_W_DISK_WRITER_SEC	The number of megabytes per second written to hard disks by disk writer.
FC_IO_BY_W_FIRST	The number of megabytes that are first writes into flash cache.
FC_IO_BY_W_FIRST_SEC	The number of megabytes per second for first writes into flash cache.
FC_IO_BY_W_METADATA	The number of megabytes that are flash cache metadata writes
FC_IO_BY_W_METADATA_SEC	The number of megabytes per second that are flash cache metadata writes
FC_IO_BY_W_OVERWRITE	The number of megabytes that are overwrites into flash cache.
FC_IO_BY_W_OVERWRITE_SEC	The number of megabytes per second that are overwrites into flash cache.
FC_IO_BY_W_POPULATE	The number of megabytes for population writes into flash cache due to read misses.
FC_IO_BY_W_POPULATE_SEC	The number of megabytes per second that are population writes into flash cache due to read misses into flash cache.
FC_IO_BY_W_SEC	The number of megabytes per second written to flash cache.
FC_IO_BY_W_SKIP	The number of megabytes written to disk for I/O requests that bypass flash cache.
FC_IO_BY_W_SKIP_FC_THROTTLE	The number of megabytes written to disk for I/O requests that bypass flash cache due to heavy load on flash cache.
FC_IO_BY_W_SKIP_FC_THROTTLE_SEC	The number of megabytes written per second to disk for I/O requests that bypass flash cache due to heavy load on flash cache.
FC_IO_BY_W_SKIP_LG	The number of megabytes written to disk for I/O requests that bypass flash cache due to the large I/O size.
FC_IO_BY_W_SKIP_LG_SEC	The number of megabytes written per second to disk for I/O requests that bypass flash cache due to the large I/O size.
FC_IO_BY_W_SKIP_NCMIRROR	The number of megabytes written to disk for I/O requests that bypass flash cache as the I/O is on non-primary, non-active secondary mirror.
FC_IO_BY_W_SKIP_SEC	The number of megabytes written to disk per second for I/O requests that bypass flash cache.
FC_IO_BYKEEP_R	The number of megabytes read from flash cache for keep objects.
FC_IO_BYKEEP_R_SEC	The number of megabytes read per second from flash cache for keep objects
FC_IO_BYKEEP_W	The number of megabytes written to flash cache for keep objects.
FC_IO_BYKEEP_W_SEC	The number of megabytes per second written to flash cache for keep objects.
FC_IO_ERRS	The number of I/O errors on flash cache.
FC_IO_RQ_DISK_WRITE	The number of requests written from flash cache to hard disks.
FC_IO_RQ_DISK_WRITE_SEC	The number of requests per second for flash cache writing data to hard disks.
FC_IO_RQ_R	The number of read I/O requests satisfied from flash cache.
FC_IO_RQ_R_ACTIVE_SECONDARY	The number of requests for active secondary reads satisfied from flash cache.
FC_IO_RQ_R_ACTIVE_SECONDARY_MISS	The number of requests for active secondary reads not satisfied from flash cache.
FC_IO_RQ_R_ACTIVE_SECONDARY_MISS_SEC	The number of requests per second for active secondary reads not satisfied from flash cache.
FC_IO_RQ_R_ACTIVE_SECONDARY_SEC	The number of requests per second for active secondary reads satisfied from flash cache.
FC_IO_RQ_R_DISK_WRITER	The number of requests read from flash cache by disk writer.
FC_IO_RQ_R_DISK_WRITER_SEC	The number of requests per second read from flash cache by disk writer.
FC_IO_RQ_R_DW	The number of read I/O requests of DW data read from flash cache.
FC_IO_RQ_R_MISS	The number of read I/O requests which did not find all data in flash cache.
FC_IO_RQ_R_MISS_DW	The number of read I/O requests of DW data read from disks because not all requested data was in flash cache.
FC_IO_RQ_R_MISS_SEC	The number of read I/O requests per second which did not find all data in flash cache.
FC_IO_RQ_R_SEC	The number of read I/O requests satisfied per second from flash cache.
FC_IO_RQ_R_SKIP	The number of read I/O requests that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_RQ_R_SKIP_FC_THROTTLE	The number of requests read from disk that bypass flash cache due to heavy load on flash cache.
FC_IO_RQ_R_SKIP_FC_THROTTLE_SEC	The number of requests per second read from disk that bypassed flash cache due to heavy load on flash cache.
FC_IO_RQ_R_SKIP_LG	The number of read I/O requests that bypass flash cache due to the large I/O size.
FC_IO_RQ_R_SKIP_LG_SEC	The number of read I/O requests per second that bypass flash cache due to the large I/O size.
FC_IO_RQ_R_SKIP_NCMIRROR	The number of requests read from disk that bypass flash cache as the I/O is on non-primary non-active secondary mirror.
FC_IO_RQ_R_SKIP_SEC	The number of read I/O requests per second that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_RQ_REPLACEMENT_ATTEMPTED	The number of requests attempted to find space in the flash cache.
FC_IO_RQ_REPLACEMENT_FAILED	The number of requests that failed to find space in the flash cache.
FC_IO_RQ_W	The number of I/O requests which resulted in flash cache being populated with data.
FC_IO_RQ_W_DISK_WRITER	The number of requests written to hard disks by disk writer.
FC_IO_RQ_W_DISK_WRITER_SEC	The number of requests per second written to hard disks by disk writer.
FC_IO_RQ_W_FIRST	The number of requests that are first writes into flash cache.
FC_IO_RQ_W_FIRST_SEC	The number of requests per second that are first writes into flash cache.
FC_IO_RQ_W_METADATA	The number of requests that are flash cache metadata writes
FC_IO_RQ_W_METADATA_SEC	The number of requests per second that are flash cache metadata writes
FC_IO_RQ_W_OVERWRITE	The number of requests that are overwrites into flash cache.
FC_IO_RQ_W_OVERWRITE_SEC	The number of requests per second that are overwrites into flash cache.
FC_IO_RQ_W_POPULATE	The number of requests that are population writes into flash cache due to read misses.
FC_IO_RQ_W_POPULATE_SEC	The number of requests per second that are population writes into flash cache due to read misses.
FC_IO_RQ_W_SEC	The number of I/O requests per second which resulted in flash cache being populated with data.
FC_IO_RQ_W_SKIP	The number of write I/O requests that bypass flash cache.
FC_IO_RQ_W_SKIP_FC_THROTTLE	The number of requests written to disk that bypass flash cache due to heavy load on flash cache.
FC_IO_RQ_W_SKIP_FC_THROTTLE_SEC	The number of requests written to disk per second that bypass flash cache due to heavy load on flash cache.
FC_IO_RQ_W_SKIP_LG	The number of requests written to disk that bypass flash cache due to the large I/O size.
FC_IO_RQ_W_SKIP_LG_SEC	The number of requests written to disk per second that bypass flash cache due to the large I/O size.
FC_IO_RQ_W_SKIP_NCMIRROR	The number of requests written to disk that bypass flash cache as the I/O is on non-primary, non-active secondary mirror.
FC_IO_RQ_W_SKIP_SEC	The number of write I/O requests per second that bypass flash cache.
FC_IO_RQKEEP_R	The number of read I/O requests for keep objects from flash cache.
FC_IO_RQKEEP_R_MISS	The number of read I/O requests for keep objects which did not find all data in flash cache.
FC_IO_RQKEEP_R_MISS_SEC	The number of read I/O requests per second for keep objects which did not find all data in flash cache.
FC_IO_RQKEEP_R_SEC	The number of read I/O requests per second for keep objects satisfied from flash cache.
FC_IO_RQKEEP_R_SKIP	The number of read I/O requests for keep objects that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_RQKEEP_R_SKIP_SEC	The rate which is the number of read I/O requests per second for keep objects that bypass flash cache. Read I/O requests that bypass flash cache go directly to disks. These requests do not populate flash cache after reading the requested data. For example: reads for database objects with CELL_FLASH_CACHE=NONE, or Smart Scan I/Os for database objects with CELL_FLASH_CACHE=DEFAULT.
FC_IO_RQKEEP_W	The number of I/O requests for keep objects which resulted in flash cache being populated with data.
FC_IO_RQKEEP_W_SEC	The number of I/O requests per second for keep objects which resulted in flash cache being populated with data.
FC_PREFETCH_HIT	The number of prefetch hits in flash cache. A hit means a page is prefetched and later used by a client for read or write. Once a prefetched page is hit, it is no longer marked as prefetched.
FC_PREFETCH_MISS	The number of prefetch misses in flash cache. A miss means a page is prefetched but never used by a client before the page is freed or reused.
FC_PREFETCH_RQ	The number of prefetch requests in flash cache.
FC_PREFETCH_SKIP	The number of prefetch requests skipped in flash cache. A prefetch request can be skipped if a prefetch disk IO is not issued due to error, throttling, or no buffer.
FC_PREFETCH_USED	The number of megabytes in flash cache holding prefetched pages that have not been used by client yet.

The following tables and views also have metrics for flash cache:

• Table 7-2

• Table 7-4

• Table 7-7

• Table 7-9

• Table 7-13

• V$SEGMENT_STATISTICS

• V$SQL

7.2.5 Monitoring Exadata Smart Flash Log with Metrics

Exadata Smart Flash Log metrics provide information about flash log utilization, such as the number of megabytes written per second. To display Exadata Smart Flash Log metrics, use an object type attribute equal to FLASHLOG with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following table lists the metric names and descriptions for Exadata Smart Flash Log metrics. The flash log for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects.

The size of Exadata Smart Flash Log can be set. When the size is too small, then the FL_IO_W_SKIP_LARGE and FL_IO_W_SKIP_BUSY_MIN metrics can have high values.

Exadata Smart Flash Log Metrics and Descriptions

• FL_ACTUAL_OUTLIERS — The number of redo writes written to flash and disk that exceeded the outlier threshold

• FL_BY_KEEP — The number of redo data bytes saved on flash due to disk I/O errors

• FL_DISK_FIRST — The number of redo writes first written to disk

• FL_DISK_IO_ERRS — The number of disk I/O errors encountered by Exadata Smart Flash Log

• FL_EFFICIENCY_PERCENTAGE — The efficiency of Exadata Smart Flash Log expressed as a percentage

• FL_EFFICIENCY_PERCENTAGE_HOUR — The efficiency of Exadata Smart Flash Log over the past hour expressed as a percentage

• FL_FLASH_FIRST — The number of redo writes first written to flash

• FL_FLASH_IO_ERRS — The number of flash I/O errors encountered by Exadata Smart Flash Log

• FL_FLASH_ONLY_OUTLIERS — The number of redo writes written to flash that exceeded the outlier threshold.

• FL_IO_DB_BY_W — The number of megabytes written to hard disk by Exadata Smart Flash Log

• FL_IO_DB_BY_W_SEC — The number of megabytes written per second were written to hard disk by Exadata Smart Flash Log

• FL_IO_FL_BY_W — The number of megabytes written to flash by Exadata Smart Flash Log

• FL_IO_FL_BY_W_SEC — The number of megabytes written per second were written to flash by Exadata Smart Flash Log

• FL_IO_TM_W —

  Cumulative redo log write latency. It includes latency for requests not handled by Exadata Smart Flash Log.

• FL_IO_TM_W_RQ — Average redo log write latency. It includes write I/O latency only

• FL_IO_W — The number of writes serviced by Exadata Smart Flash Log

• FL_IO_W_SKIP_BUSY — The number of redo writes that bypassed Exadata Smart Flash Log because the flash log disks contained too much active data, which is data that has not yet been written to disk, or has been checkpointed by flash log.

  There are several factors that can cause redo writes to bypass flash log:

  • Flash disks are not performing as fast as they should, that is, write latencies are high. You can verify this by checking relevant flash disk performance metrics and also by checking FL_FLASH_ONLY_OUTLIERS. If this metric value is high, then it would indicate a flash disk performance issue.

  • Hard disks containing log file data are not performing as fast as they should. You can verify this by checking relevant hard disk performance metrics and also by checking FL_PREVENTED_OUTLIERS. If this metric value is high, then it would indicate a hard disk performance issue.

  • Redo log data is being generated at a high volume and rate, and Exadata Smart Flash Logging cannot keep up. You can verify this by checking FL_IO_DB_BY_W_SEC. If this metric value is several megabytes per second, then the redo log generation rate may be higher than what flash log can handle. To address this issue, you can make the flash log size larger, but this would require re-configuring the size of the Exadata Smart Flash Cache to make it smaller, so that the corresponding space can be allocated to the flash log.

    Generally, FL_IO_W_SKIP_BUSY is not as important as other factors, such as "log file parallel write" wait times, as indicated in Automatic Workload Repository (AWR), and FL_ACTUAL_OUTLIERS.

    If log file parallel write wait times are high (more than 0.5 seconds) or FL_ACTUAL_OUTLIERS is substantial, then this means that the flash log feature is not performing efficiently.

• FL_IO_W_SKIP_BUSY_MIN — The number of redo writes during the last minute that could not be serviced by Exadata Smart Flash Log because too much data had not yet been written to disk.

• FL_IO_W_SKIP_DISABLED_GD — The number of times Exadata Smart Flash Log was disabled for the underlying grid disk, usually caused by recent write errors on that disk

• FL_IO_W_SKIP_IORM_LIMIT — The number of times IORM limit was reached for the disk containing the redo log

• FL_IO_W_SKIP_IORM_PLAN — The number of times the flash log was disabled in the IORM plan

• FL_IO_W_SKIP_LARGE — The number of large redo writes that could not be serviced by Exadata Smart Flash Log because the size of the data was larger than the amount of available space on any flash disk.

• FL_IO_W_SKIP_LOG_ON_FLASH — The number of times the redo log resided on flash (for systems that are not Extreme Flash systems)

• FL_IO_W_SKIP_NO_BUFFER — The metric shows the number of redo writes that could not be serviced by Exadata Smart Flash Log due to a lack of available buffers.

• FL_IO_W_SKIP_NO_FL_DISKS — The number of times there were no active flash log disks because the disks were inactive or unhealthy

• FL_PREVENTED_OUTLIERS — The number of redo writes written to disk that exceeded the outlier threshold. These writes would have been outliers if not for Exadata Smart Flash Log.

• FL_RQ_TM_W — Cumulative redo log write request latency. It includes networking and other overhead.

  To get the latency overhead due to factors such as network and processing, you can use (FL_RQ_TM_W - FL_IO_TM_W).

• FL_RQ_TM_W_RQ — Average redo log write request latency.

• FL_RQ_W — Total number of redo log write requests. It includes requests not handled by Exadata Smart Flash Log.

  To get the number of redo log write requests not handled by Exadata Smart Flash Log, you can use (FL_RQ_W - FL_IO_W).

• FL_SKIP_OUTLIERS — The number of outliers when redo log write skips use of the flash log. The use of the flash log can be skipped because of FL_IO_W_SKIP_DISABLED_GD, FL_IO_W_SKIP_IORM_LIMIT, FL_IO_W_SKIP_IORM_PLAN, FL_IO_W_SKIP_LOG_ON_FLASH, or FL_IO_W_SKIP_NO_FL_DISKS.

Note:

• An outlier is a redo log write that exceeds 0.5 seconds.

• Often, there are more redo log write operations that complete to disk first before flash. The value of FL_DISK_FIRST is greater than the value of FL_FLASH_FIRST. This does not mean that the Exadata Smart Flash Logging feature is ineffective or unnecessary.

7.2.6 Monitoring Grid Disk Metrics

Grid disk metrics provide information about the I/O load for grid disks, such as the number of large blocks read from a grid disk. To display grid disk metrics, use an object type attribute equal to GRIDDISK with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following tale lists the metric names and descriptions for grid disk metrics. The grid disk for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute.

Table 7-4 Grid Disk Metrics and Descriptions

Metric	Description
GD_BY_FC_DIRTY	The number of megabytes cached in flash cache that are not synchronized to the grid disk.
GD_IO_BY_R_LG	The cumulative number of megabytes read in large blocks from a grid disk.
GD_IO_BY_R_LG_SEC	The rate which is the number of megabytes read in large blocks per second from a grid disk.
GD_IO_BY_R_SCRUB	The number of megabytes read from a grid disk by the scrubbing job.
GD_IO_BY_R_SCRUB_SEC	The number of megabytes read per second from a grid disk by the scrubbing job.
GD_IO_BY_R_SM	The cumulative number of megabytes read in small blocks from a grid disk.
GD_IO_BY_R_SM_SEC	The rate which is the number of megabytes read in small blocks per second from a grid disk.
GD_IO_BY_W_LG	The cumulative number of megabytes written in large blocks on a grid disk.
GD_IO_BY_W_LG_SEC	The rate which is the number of megabytes written in large blocks per second on a grid disk.
GD_IO_BY_W_SM	The cumulative number of megabytes written in small blocks on a grid disk.
GD_IO_BY_W_SM_SEC	The rate which is the number of megabytes written in small blocks per second on a grid disk.
GD_IO_ERRS	The cumulative number of I/O errors on a grid disk.
GD_IO_ERRS_MIN	The rate of I/O errors on a grid disk per minute.
GD_IO_ERRS_SCRUB	The number of bad 1 MB blocks detected during a scrubbing job on a grid disk.
GD_IO_RQ_R_LG	The cumulative number of requests to read large blocks from a grid disk.
GD_IO_RQ_R_LG_SEC	The rate which is the number of requests to read large blocks per second from a grid disk.
GD_IO_RQ_R_SCRUB	The number of requests to read from a grid disk by the scrubbing job.
GD_IO_RQ_R_SCRUB_SEC	The number of requests to read per second from a grid disk by the scrubbing job.
GD_IO_RQ_R_SM	The cumulative number of requests to read small blocks from a grid disk.
GD_IO_RQ_R_SM_SEC	The rate which is the number of requests to read small blocks per second from a grid disk.
GD_IO_RQ_W_LG	The cumulative number of requests to write large blocks to a grid disk.
GD_IO_RQ_W_LG_SEC	The rate which is the number of requests to write large blocks per second to a grid disk.
GD_IO_RQ_W_SM	The cumulative number of requests to write small blocks to a grid disk.
GD_IO_RQ_W_SM_SEC	The rate which is the number of requests to write small blocks per second to a grid disk.
GD_SP_BY_ALLOCATED	The allocated physical space for grid disk in bytes.
GD_SP_IO_BY_PARTIAL	The bytes returned by partial I/Os.
GD_SP_IO_BY_PARTIAL_SEC	The bytes returned by partial I/Os per second.
GD_SP_IO_BY_REDIRECTED	The sparse bytes redirected to original data block.
GD_SP_IO_BY_REDIRECTED_SEC	The sparse bytes redirected to original data block per second.
GD_SP_IO_RQ_PARTIAL	The number of I/O requests that returned partial data.
GD_SP_IO_RQ_PARTIAL_SEC	The number of I/O requests that returned partial data per second.
GD_SP_PRCT_ALLOCATED	The allocated physical space for grid disk by percentage. A warning alert is generated when space usage is more than 95%. A critical alert is generated when space usage reaches 99%. If an alert occurs, then either increase the size of the sparse grid disk, or drop some snapshot databases to free space.

7.2.7 Monitoring Host Interconnection Metrics

Host interconnection metrics provide information about the I/O transmission for hosts that access cell storage. To display host interconnection metrics, use an object type attribute equal to HOST_INTERCONNECT with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following table lists the metric names and descriptions for host interconnection metrics. The host interconnection for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute.

Table 7-5 Host Interconnection Metrics and Descriptions

Metric	Description
N_MB_DROP	The cumulative number of megabytes dropped during transmission to a particular host.
N_MB_DROP_SEC	The rate which is the number of megabytes dropped per second during transmission to a particular host.
N_MB_RDMA_DROP	The cumulative number of megabytes dropped during remote direct memory access (RDMA) transmission to a particular host.
N_MB_RDMA_DROP_SEC	The rate which is the number of megabytes dropped per second during RDMA transmission to a particular host.
N_MB_RECEIVED	The cumulative number of megabytes received from a particular host.
N_MB_RECEIVED_SEC	The rate which is the number of megabytes received per second from a particular host.
N_MB_RESENT	The cumulative number of megabytes retransmitted to a particular host.
N_MB_RESENT_SEC	The rate which is the number of megabytes retransmitted per second to a particular host.
N_MB_SENT	The cumulative number of megabytes transmitted to a particular host.
N_MB_SENT_SEC	The rate which is the number of megabytes transmitted per second to a particular host.
N_RDMA_RETRY_TM	The cumulative latency of the retry action during RDMA transmission to a particular host.

7.2.8 Monitoring Smart I/O Metrics

Smart I/O metrics provide information about smart I/O performance. Smart I/O is the I/O processing, such as smart scan, and smart incremental backup, performed by the Oracle Exadata Storage Server offload functionality. To display smart I/O metrics, use the object type attribute equal to SMARTIO with the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects.

The following table lists the metric names and descriptions for the smart I/O metrics. The smart I/O for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods.

Table 7-6 Smart I/O Metrics and Descriptions

Metric	Description
SIO_IO_EL_OF	The cumulative number of megabytes eligible for offload by smart I/O.
SIO_IO_EL_OF_SEC	The number of megabytes per second eligible for offload by smart I/O.
SIO_IO_OF_RE	The cumulative number of interconnect megabytes returned by smart I/O.
SIO_IO_OF_RE_SEC	The number of interconnect megabytes per second returned by smart I/O.
SIO_IO_PA_TH	The cumulative number of megabytes of pass-through I/O by smart I/O.
SIO_IO_PA_TH_SEC	The number of megabytes per second of pass-through I/O by smart I/O.
SIO_IO_RD_FC	The cumulative number of megabytes read from flash cache by smart I/O.
SIO_IO_RD_FC_HD	The cumulative number of megabytes read from both flash cache and hard disk by smart I/O.
SIO_IO_RD_FC_HD_SEC	The number of megabytes per second read from both flash cache and hard disk by smart I/O.
SIO_IO_RD_FC_SEC	The number of megabytes per second read from flash cache by smart I/O.
SIO_IO_RD_HD	The cumulative number of megabytes read from hard disk by smart I/O.
SIO_IO_RD_HD_SEC	The number of megabytes per second read from hard disk by smart I/O.
SIO_IO_RD_RQ_FC	The cumulative number of read I/O requests from flash cache by smart I/O.
SIO_IO_RD_RQ_FC_HD	The cumulative number of read I/O requests from both flash cache and hard disk by smart I/O.
SIO_IO_RD_RQ_FC_HD_SEC	The number of read I/O requests per second from both flash cache and hard disk by smart I/O.
SIO_IO_RD_RQ_FC_SEC	The number of read I/O requests per second from flash cache by smart I/O.
SIO_IO_RD_RQ_HD	The cumulative number of megabytes read I/O requests from hard disk by smart I/O.
SIO_IO_RD_RQ_HD_SEC	The number of read I/O requests per second from hard disk by smart I/O.
SIO_IO_RV_OF	The cumulative number of megabytes sent to the database node to balance CPU by smart I/O.
SIO_IO_RV_OF_SEC	The number of megabytes per second sent to the database node to balance CPU by smart I/O.
SIO_IO_SI_SV	The cumulative number of megabytes saved by the storage index.
SIO_IO_SI_SV_SEC	The number of megabytes per second saved by the storage index.
SIO_IO_WR_FC	The cumulative number of megabytes of flash cache population writes by smart I/O.
SIO_IO_WR_FC_SEC	The number of megabytes per second of flash cache population writes by smart I/O.
SIO_IO_WR_HD	The cumulative number of megabytes written to hard disk by smart I/O.
SIO_IO_WR_HD_SEC	The number of megabytes per second written to hard disk by smart I/O.
SIO_IO_WR_RQ_FC	The cumulative number of I/O requests for flash cache population writes by smart I/O.
SIO_IO_WR_RQ_FC_SEC	The number of I/O requests per second for flash cache population writes by smart I/O.
SIO_IO_WR_RQ_HD	The cumulative number of write I/O requests to hard disk by smart I/O.
SIO_IO_WR_RQ_HD_SEC	The number of write I/O requests per second to hard disk by smart I/O.

7.3 Monitoring IORM with Metrics

I/O Resource Management (IORM) can be monitored using Oracle Exadata System Software metrics.

IORM uses the database name, not the database identifier, to collect statistics and display output.

• Monitoring IORM with Category Metrics

• Monitoring IORM with Pluggable Database Metrics

• Monitoring IORM with Database Metrics

• Monitoring IORM with Consumer Group Metrics

• Monitoring IORM Utilization

• Tuning Interdatabase Plans with Metrics

7.3.1 Monitoring IORM with Category Metrics

Category metrics provide information about the size of the I/O load from each category specified in the current IORM category plan. The objectType attribute of the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects is equal to IORM_CATEGORY for category metrics.

The following table lists the metric name and description for category metrics. The category for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute. In the table descriptions, small means less than or equal to 128 KB, and large means more than 128 KB.

Table 7-7 Category Metrics and Descriptions

Metric	Description
CT_FC_IO_BY_SEC	The number of megabytes of I/O per second for this category to flash cache.
CT_FC_IO_RQ	The number of I/O requests issued by an IORM category to flash cache.
CT_FC_IO_RQ_LG	The number of large I/O requests issued by an IORM category to flash cache.
CT_FC_IO_RQ_LG_SEC	The number of large I/O requests issued by an IORM category to flash cache per second.
CT_FC_IO_RQ_SEC	The number of I/O requests issued by an IORM category to flash cache per second.
CT_FC_IO_RQ_SM	The number of small I/O requests issued by an IORM category to flash cache.
CT_FC_IO_RQ_SM_SEC	The number of small I/O requests issued by an IORM category to flash cache per second.
CT_FD_IO_BY_SEC	The number of megabytes of I/O per second for this category to flash disks.
CT_FD_IO_LOAD	The average I/O load from this category for flash disks. For a description of I/O load, see "CD_IO_LOAD".
CT_FD_IO_RQ_LG	The number of large I/O requests issued by an IORM category to flash disks.
CT_FD_IO_RQ_LG_SEC	The number of large I/O requests issued by an IORM category to flash disks per second.
CT_FD_IO_RQ_SM	The number of small I/O requests issued by an IORM category to flash disks.
CT_FD_IO_RQ_SM_SEC	The number of small I/O requests issued by an IORM category to flash disks per second.
CT_FD_IO_TM	The cumulative latency of reading or writing blocks for this category from flash disks.
CT_FD_IO_TM_LG	The cumulative latency of reading or writing large blocks for this category from flash disks.
CT_FD_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request for this category from flash disks.
CT_FD_IO_TM_RQ	The rate which is the average latency of reading or writing blocks per request for this category from flash disks.
CT_FD_IO_TM_SM	The cumulative latency of reading or writing small blocks for this category from flash disks.
CT_FD_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request for this category from flash disks.
CT_FD_IO_UTIL	The percentage of flash resources utilized from this category.
CT_FD_IO_UTIL_LG	The percentage of flash resources utilized by large requests from this category.
CT_FD_IO_UTIL_SM	The percentage of flash resources utilized by small requests from this category.
CT_FD_IO_WT_LG	The IORM wait time for large I/O requests issued to flash disks by an IORM category.
CT_FD_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to flash disks by an IORM category.
CT_FD_IO_WT_SM	The IORM wait time for small I/O requests issued to flash disks by an IORM category.
CT_FD_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to flash disks by an IORM category.
CT_IO_BY_SEC	The number of megabytes of I/O per second for this category to hard disks.
CT_IO_LOAD	The average I/O load from this category for hard disks. For a description of I/O load, see "CD_IO_LOAD".
CT_IO_RQ_LG	The cumulative number of large I/O requests issued by the category for hard disks. A large value indicates a heavy I/O workload from this category.
CT_IO_RQ_LG_SEC	This metric is derived from CT_IO_RQ_LG. It specifies the rate of large I/O requests issued by the category for hard disks. Its units are number of I/O requests per second. A large value indicates a heavy I/O workload from this category in the past minute.
CT_IO_RQ_SM	The cumulative number of small I/O requests issued by the category for hard disks. A large value indicates a heavy I/O workload from this category.
CT_IO_RQ_SM_SEC	This metric is derived from CT_IO_RQ_SM. It specifies the rate of small I/O requests issued by the category for hard disks. Its units are number of I/O requests per second. A large value indicates a heavy I/O workload from this category in the past minute.
CT_IO_TM_LG	The cumulative latency of reading or writing large blocks for this category from hard disks.
CT_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request for this category from hard disks.
CT_IO_TM_SM	The cumulative latency of reading or writing small blocks for this category from hard disks.
CT_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request for this category from hard disks.
CT_IO_UTIL_LG	The percentage of disk resources utilized by large requests from this category.
CT_IO_UTIL_SM	The percentage of disk resources utilized by small requests from this category.
CT_IO_WT_LG	The cumulative number of milliseconds that large I/O requests issued to hard disks by the category have waited to be scheduled by IORM. A large value indicates that the I/O workload from this category is exceeding the allocation specified for it in the category plan.
CT_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to hard disks by an IORM category.
CT_IO_WT_SM	The cumulative number of milliseconds that small I/O requests issued to hard disks by the category have waited to be scheduled by IORM. A large value indicates that the I/O workload from this category is exceeding the allocation specified for it in the category plan.
CT_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to hard disks by an IORM category.

All category cumulative metrics are reset to zero whenever a category, IORM, or any database resource plan is modified. To list the category metric history for an interdatabase plan, use the following CellCLI command:

CellCLI> LIST METRICHISTORY WHERE objectType = 'IORM_CATEGORY' AND            -
         metricValue != 0 ATTRIBUTES name, metricObjectName, metricValue,     -
         collectionTime

In addition, category metrics are provided for the following internally-generated and automatically-managed categories:

• _ASM_: Oracle ASM-related I/Os

• _ORACLE_BG_CATEGORY_: High-priority I/Os issued by Oracle Database background processes

• _ORACLE_MEDPRIBG_CATEGORY_: Medium-priority I/Os issued by Oracle Database background processes

• _ORACLE_LOWPRIBG_CATEGORY_: Low-priority I/Os issued by Oracle Database background processes

7.3.2 Monitoring IORM with Pluggable Database Metrics

Pluggable database (PDB) metrics provide information about the size of the I/O load from each PDB specified in the container database (CDB) plan. The objectType attribute of the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects is equal to IORM_PLUGGABLE_DATABASE for database metrics.

The following table lists the metric name and description for database metrics. The PDB for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. The PDB name is a concatenation of the CDB name with the PDB name. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute. In the table descriptions, small means less than or equal to 128 KB, and large means more than 128 KB.

Table 7-8 Pluggable Database Metrics and Descriptions

Metric	Description
PDB_FC_BY_ALLOCATED	The number of megabytes allocated in flash cache for this pluggable database.
PDB_FC_IO_BY_SEC	The number of megabytes of I/O per second for the pluggable database to flash cache.
PDB_FC_IO_RQ	The number of I/O requests issued by this pluggable database to flash cache.
PDB_FC_IO_RQ_LG	The number of large I/O requests issued by this pluggable database to flash cache.
PDB_FC_IO_RQ_LG_SEC	The number of large I/O requests issued by this pluggable database to flash cache per second.
PDB_FC_IO_RQ_SEC	The number of I/O requests issued by this pluggable database to flash cache per second.
PDB_FC_IO_RQ_SM	The number of small I/O requests issued by this pluggable database to flash cache.
PDB_FC_IO_RQ_SM_SEC	The number of small IO requests issued by this pluggable database to flash cache per second.
PDB_FD_IO_BY_SEC	The number of megabytes of I/O per second for the pluggable database to flash disks.
PDB_FD_IO_LOAD	The average I/O load from this pluggable database for flash disks. For a description of I/O load, see "CD_IO_LOAD".
PDB_FD_IO_RQ_LG	The number of large I/O requests issued by a pluggable database to flash disks.
PDB_FD_IO_RQ_LG_SEC	The number of large I/O requests issued by a pluggable database to flash disks per second.
PDB_FD_IO_RQ_SM	The number of small I/O requests issued by a pluggable database to flash disks.
PDB_FD_IO_RQ_SM_SEC	The number of small I/O requests issued by a pluggable database to flash disks per second.
PDB_FD_IO_TM	The cumulative latency of reading or writing blocks by this pluggable database from flash disks.
PDB_FD_IO_TM_LG	The cumulative latency of reading or writing large blocks by this pluggable database from flash disks.
PDB_FD_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by this pluggable database from flash disks.
PDB_FD_IO_TM_RQ	The rate which is the average latency of reading or writing blocks per request by this pluggable database from flash disks.
PDB_FD_IO_TM_SM	The cumulative latency of reading or writing small blocks by this pluggable database from flash disks.
PDB_FD_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by this pluggable database from flash disks.
PDB_FD_IO_UTIL	The percentage of flash resources utilized from this pluggable database.
PDB_FD_IO_UTIL_LG	The percentage of flash resources utilized by large requests from this pluggable database.
PDB_FD_IO_UTIL_SM	The percentage of flash resources utilized by small requests from this pluggable database.
PDB_FD_IO_WT_LG	The IORM wait time for large I/O requests issued to flash disks by this pluggable database.
PDB_FD_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to flash disks by this pluggable database.
PDB_FD_IO_WT_SM	The IORM wait time for small I/O requests issued to flash disks by this pluggable database.
PDB_FD_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to flash disks by this pluggable database.
PDB_IO_BY_SEC	The number of megabytes of I/O per second for the pluggable database to hard disks.
PDB_IO_LOAD	The average I/O load from this pluggable database for hard disks. For a description of I/O load, see "CD_IO_LOAD".
PDB_IO_RQ_LG	The cumulative number of large I/O requests issued by the pluggable database to hard disks. A large value indicates a heavy large I/O workload from this pluggable database.
PDB_IO_RQ_LG_SEC	The rate of large I/O requests issued by a pluggable database per second over the past minute to hard disks. A large value indicates a heavy, large I/O workload from this pluggable database in the past minute.
PDB_IO_RQ_SM	The cumulative number of small I/O requests issued by the pluggable database to hard disks. A large value indicates a heavy small I/O workload from this pluggable database.
PDB_IO_RQ_SM_SEC	The rate of small I/O requests issued by a pluggable database per second over the past minute to hard disks. A large value indicates a heavy, small I/O workload issued by this pluggable database in the past minute.
PDB_IO_TM_LG	The cumulative latency of reading or writing large blocks by this pluggable database from hard disks.
PDB_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by this pluggable database from hard disks.
PDB_IO_TM_SM	The cumulative latency of reading or writing small blocks by this pluggable database from hard disks.
PDB_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by this pluggable database from hard disks.
PDB_IO_UTIL_LG	The percentage of disk resources utilized by large requests from this pluggable database.
PDB_IO_UTIL_SM	The percentage of disk resources utilized by small requests from this pluggable database.
PDB_IO_WT_LG	The IORM wait time for large I/O requests issued to hard disks by this pluggable database.
PDB_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to hard disks by this pluggable database.
PDB_IO_WT_SM	The IORM wait time for small I/O requests issued to hard disks by this pluggable database.
PDB_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to hard disks by this pluggable database.

7.3.3 Monitoring IORM with Database Metrics

Database metrics provide information about the size of the I/O load from each database specified in the interdatabase plan. The objectType attribute of the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects is equal to IORM_DATABASE for database metrics.

The following table lists the metric name and description for database metrics. The database for the metric is specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute. In the table descriptions, small means less than or equal to 128 KB, and large means more than 128 KB.

Table 7-9 Database Metrics and Descriptions

Metric	Description
DB_FC_BY_ALLOCATED	The number of megabytes allocated in flash cache for this database.
DB_FC_IO_BY_SEC	The number of megabytes of I/O per second for this database to flash cache.
DB_FC_IO_RQ	The number of I/O requests issued by a database to flash cache.
DB_FC_IO_RQ_LG	The number of large I/O requests issued by a database to flash cache.
DB_FC_IO_RQ_LG_SEC	The number of large I/O requests issued by a database to flash cache per second.
DB_FC_IO_RQ_SEC	The number of I/O requests issued by a database to flash cache per second.
DB_FC_IO_RQ_SM	The number of small I/O requests issued by a database to flash cache.
DB_FC_IO_RQ_SM_SEC	The number of small I/O requests issued by a database to flash cache per second.
DB_FD_IO_BY_SEC	The number of megabytes of I/O per second for this database to flash disks.
DB_FD_IO_LOAD	The average I/O load from this database for flash disks. For a description of I/O load, see "CD_IO_LOAD".
DB_FD_IO_RQ_LG	The number of large I/O requests issued by a database to flash disks.
DB_FD_IO_RQ_LG_SEC	The number of large I/O requests issued by a database to flash disks per second.
DB_FD_IO_RQ_SM	The number of small I/O requests issued by a database to flash disks.
DB_FD_IO_RQ_SM_SEC	The number of small I/O requests issued by a database to flash disks per second.
DB_FD_IO_TM	The cumulative latency of reading or writing blocks by a database from flash disks.
DB_FD_IO_TM_LG	The cumulative latency of reading or writing large blocks by a database from flash disks.
DB_FD_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by a database from flash disks.
DB_FD_IO_TM_RQ	The rate which is the average latency of reading or writing blocks per request by a database from flash disks.
DB_FD_IO_TM_SM	The cumulative latency of reading or writing small blocks by a database from flash disks.
DB_FD_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by a database from flash disks.
DB_FD_IO_UTIL	The percentage of flash resources utilized from this database.
DB_FD_IO_UTIL_LG	The percentage of flash resources utilized by large requests from this database.
DB_FD_IO_UTIL_SM	The percentage of flash resources utilized by small requests from this database.
DB_FD_IO_WT_LG	The IORM wait time for large I/O requests issued to flash disks by a database.
DB_FD_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to flash disks by a database.
DB_FD_IO_WT_SM	The IORM wait time for small I/O requests issued to flash disks by a database.
DB_FD_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to flash disks by a database.
DB_FL_IO_BY	The number of megabytes written to Oracle Exadata Smart Flash Log.
DB_FL_IO_BY_SEC	The number of megabytes written per second to Oracle Exadata Smart Flash Log.
DB_FL_IO_RQ	The number of I/O requests issued to Oracle Exadata Smart Flash Log.
DB_FL_IO_RQ_SEC	The number of I/O requests per second issued to Oracle Exadata Smart Flash Log.
DB_IO_BY_SEC	The number of megabytes of I/O per second for this database to hard disks.
DB_IO_LOAD	The average I/O load from this database for hard disks. For a description of I/O load, see "CD_IO_LOAD".
DB_IO_RQ_LG	The cumulative number of large I/O requests issued to hard disks by the database. A large value indicates a heavy large I/O workload from this database.
DB_IO_RQ_LG_SEC	The rate of large I/O requests issued to hard disks by a database per second over the past minute. A large value indicates a heavy large I/O workload from this database in the past minute.
DB_IO_RQ_SM	The cumulative number of small I/O requests issued to hard disks by the database. A large value indicates a heavy small I/O workload from this database.
DB_IO_RQ_SM_SEC	The rate of small I/O requests issued to hard disks by a database per second over the past minute. A large value indicates a heavy small I/O workload issued by this database in the past minute.
DB_IO_TM_LG	The cumulative latency of reading or writing large blocks by a database from hard disks.
DB_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by a database from hard disks.
DB_IO_TM_SM	The cumulative latency of reading or writing small blocks by a database from hard disks.
DB_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by a database from hard disks.
DB_IO_UTIL_LG	The percentage of hard disk resources utilized by large requests from this database.
DB_IO_UTIL_SM	The percentage of hard disk resources utilized by small requests from this database.
DB_IO_WT_LG	The IORM wait time for large I/O requests issued to hard disks by a database.
DB_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to hard disks by a database.
DB_IO_WT_SM	The IORM wait time for small I/O requests issued to hard disks by a database.
DB_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to hard disks by a database.

All database cumulative metrics are reset to zero whenever a category, IORM, or any database resource plan is modified.

To list the database metric history for an interdatabase plan, use the following CellCLI command:

CellCLI> LIST METRICHISTORY WHERE objectType = 'IORM_DATABASE' AND - 
metricValue != 0 ATTRIBUTES name, metricObjectName, metricValue, collectionTime

In addition, database metrics are provided for Oracle ASM and all other databases in a metric named _OTHER_DATABASE_.

7.3.4 Monitoring IORM with Consumer Group Metrics

Consumer group metrics provide information about the size of the I/O load from each consumer group specified in a database resource plan. Each database in the interdatabase plan has metrics for each of its consumer groups. The objectType attribute of the METRICCURRENT, METRICDEFINITION, and METRICHISTORY objects is equal to IORM_CONSUMER_GROUP for consumer group metrics.

The following table lists the metric name and description for consumer group metrics. The consumer group and database for the metric are specified by the metricObjectName attribute of the METRICCURRENT and METRICHISTORY objects. The name is formed by the database name followed by a period (.) and the consumer group name. For example, for a database named PRODUCTIONDB and a consumer group named OLTP, the metricObjectName would be PRODUCTIONDB.OLTP.

For cumulative metrics, the metric value for a specific time period can be determined by subtracting values from different collectionTime periods. For rate metrics, the time period for the metric value is over the previous minute. In the table descriptions, small means less than or equal to 128 KB, and large means more than 128 KB.

Table 7-10 Consumer Group Metrics and Descriptions

Metric	Description
CG_FC_IO_BY_SEC	The number of megabytes of I/O per second for this consumer group to flash cache.
CG_FC_IO_RQ	The number of I/O requests issued by a consumer group to flash cache.
CG_FC_IO_RQ_LG	The number of large I/O requests issued by a consumer group to flash cache.
CG_FC_IO_RQ_LG_SEC	The number of large I/O requests issued by a consumer group to flash cache per second.
CG_FC_IO_RQ_SEC	The number of I/O requests issued by a consumer group to flash cache per second.
CG_FC_IO_RQ_SM	The number of small I/O requests issued by a consumer group to flash cache.
CG_FC_IO_RQ_SM_SEC	The number of small I/O requests issued by a consumer group to flash cache per second.
CG_FD_IO_BY_SEC	The number of megabytes of I/O per second for this consumer group to flash disks.
CG_FD_IO_LOAD	The metric shows the average I/O load from this consumer group for flash disks. For a description of I/O load, see "CD_IO_LOAD".
CG_FD_IO_RQ_LG	The number of large I/O requests issued by a consumer group to flash disks.
CG_FD_IO_RQ_LG_SEC	The number of large I/O requests issued by a consumer group to flash disks per second.
CG_FD_IO_RQ_SM	The number of small I/O requests issued by a consumer group to flash disks.
CG_FD_IO_RQ_SM_SEC	The number of small I/O requests issued by a consumer group to flash disks per second.
CG_FD_IO_TM	The cumulative latency of reading or writing blocks by a consumer group from flash disks.
CG_FD_IO_TM_LG	The cumulative latency of reading or writing large blocks by a consumer group from flash disks.
CG_FD_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by a consumer group from flash disks.
CG_FD_IO_TM_RQ	The rate which is the average latency of reading or writing blocks per request by a consumer group from flash disks.
CG_FD_IO_TM_SM	The cumulative latency of reading or writing small blocks by a consumer group from flash disks.
CG_FD_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by a consumer group from flash disks.
CG_FD_IO_UTIL	The percentage of flash resources utilized from this consumer group.
CG_FD_IO_UTIL_LG	The percentage of flash resources utilized by large requests from this consumer group.
CG_FD_IO_UTIL_SM	The percentage of flash resources utilized by small requests from this consumer group.
CG_FD_IO_WT_LG	The IORM wait time for large I/O requests issued to flash disks by a consumer group.
CG_FD_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to flash disks by a consumer group.
CG_FD_IO_WT_SM	The IORM wait time for small I/O requests issued to flash disks by a consumer group.
CG_FD_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to flash disks by a consumer group.
CG_IO_BY_SEC	The number of megabytes of I/O per second for this consumer group to hard disks.
CG_IO_LOAD	The metric shows the average I/O load from this consumer group for hard disks. For a description of I/O load, see "CD_IO_LOAD".
CG_IO_RQ_LG	The cumulative number of large I/O requests issued by the consumer group. A large value indicates a heavy I/O workload from this consumer group.
CG_IO_RQ_LG_SEC	This metric is derived from CG_IO_RQ_LG. It specifies the rate of large I/O requests issued by a consumer group per second over the past minute. A large value indicates a heavy I/O workload from this consumer group in the past minute.
CG_IO_RQ_SM	The cumulative number of small I/O requests issued by the consumer group. A large value indicates a heavy I/O workload from this consumer group.
CG_IO_RQ_SM_SEC	This metric is derived from CG_IO_RQ_SM. It specifies the rate of small I/O requests issued by a consumer group per second over the past minute. A large value indicates a heavy I/O workload from this consumer group in the past minute.
CG_IO_TM_LG	The cumulative latency of reading or writing large blocks by a consumer group from hard disks.
CG_IO_TM_LG_RQ	The rate which is the average latency of reading or writing large blocks per request by a consumer group from hard disks.
CG_IO_TM_SM	The cumulative latency of reading or writing small blocks by a consumer group from hard disks
CG_IO_TM_SM_RQ	The rate which is the average latency of reading or writing small blocks per request by a consumer group from hard disks.
CG_IO_UTIL_LG	The percentage of disk resources utilized by large requests from this consumer group.
CG_IO_UTIL_SM	The percentage of disk resources utilized by small requests from this consumer group.
CG_IO_WT_LG	The IORM wait time for large I/O requests issued to hard disks by a consumer group.
CG_IO_WT_LG_RQ	The average IORM wait time per request for large I/O requests issued to hard disks by a consumer group.
CG_IO_WT_SM	The IORM wait time for small I/O requests issued to hard disks by a consumer group.
CG_IO_WT_SM_RQ	The average IORM wait time per request for small I/O requests issued to hard disks by a consumer group.

All consumer group cumulative metrics are reset to zero whenever a category, IORM, or any database resource plan is modified.

To list the current metrics for consumer groups, use the following CellCLI command:

CellCLI> LIST METRICCURRENT WHERE objectType = 'IORM_CONSUMER_GROUP' AND        -
         metricValue != 0 ATTRIBUTES name, metricObjectName, metricValue,       -
         collectionTime

For Oracle ASM and all other databases, metrics are only provided for the BACKGROUND and OTHER consumer groups. The BACKGROUND consumer groups are:

• _ORACLE_BACKGROUND_GROUP_: High-priority I/O requests from Oracle Database background processes

• _ORACLE_MEDPRIBG_GROUP_: Medium-priority I/O requests from Oracle Database background processes

• _ORACLE_LOWPRIBG_GROUP_: Low-priority I/O requests from Oracle Database background processes

7.3.5 Monitoring IORM Utilization

You use metrics to monitor IORM utilization.

When OLTP and DSS workloads share Oracle Exadata Storage Servers, IORM determines whether to optimize for low latency or high throughput. To optimize for low latency, large I/O requests should be distributed so the disk is not fully utilized. To optimize for high throughput, each Oracle Exadata Storage Server must handle many concurrent large I/O requests, allowing the cell to be fully utilized while applying optimization algorithms. However, when a cell has many concurrent large I/O requests, I/O latency is high because each I/O is queued behind many other I/Os.

The utilitization metrics for I/O requests from database, PDB, and consumer groups correspond to the amount of time a database, PDB, or consumer group utilized a cell. Large I/O requests utilize more of a cell than small I/O requests. The following are the utilization metrics for determining IORM optimization:

• CG_IO_UTIL_LG

• CG_IO_UTIL_SM

• PDB_IO_UTIL_LG

• PDB_IO_UTIL_SM

• CT_IO_UTIL_LG

• CT_IO_UTIL_SM

• DB_IO_UTIL_LG

• DB_IO_UTIL_SM

By comparing the amount of I/O resources consumed with the I/O resource allocations, the database administrator can determine if IORM should be tuned for latency or throughput. The IORM metric, IORM_MODE, shows the mode for IORM. The metric value ranges between 1 and 3. The following are the definitions for the values:

• 1 means the cell IORM objective was set to low_latency.

• 2 means the cell IORM objective was set to balanced.

• 3 means the cell IORM objective was set to high_throughput.

A value in between 1-2 or 2-3 indicates the IORM objective was not the same throughout the metric period, and the value indicates proximity to a given objective. It is also indicative of a constantly-changing mix of workloads.

7.3.6 Tuning Interdatabase Plans with Metrics

The Oracle Exadata System Software metrics relevant to I/O Resource Management (IORM) can be used in multiple ways.

Metrics can be used to understand the size of the I/O workload as a whole and which portion of it corresponds to each category, database, or consumer group. For example, the metrics might show that a particular database is issuing a higher I/O workload than expected.

In addition, metrics can be used to understand which category, database, pluggable database (PDB), or consumer group is not using its resource allocation and which one is exceeding its resource allocation. For example:

• If the wait times (DB_IO_WT_SM_RQ, DB_IO_WT_LG_RQ, PDB_IO_WT_SM_RQ, PDB_IO_WT_LG_RQ and CG_IO_WT_SM_RQ) are typically small or zero, then the plan allocation is probably sufficient.

• If the wait times are large, then the plan allocation is insufficient.

• If the wait times cause an unacceptable drop in performance, then the plans should be adjusted to give a larger allocation, or more cells or disks should be added to increase the total I/O resources.

7.4 Monitoring Requests and Alerts for Oracle Exadata Storage Server

An active request provides a client-centric or application-centric view of client I/O requests that are currently being processed by a cell. Alerts represent events of importance occurring within the cell. Typically, alerts indicate that Oracle Exadata Storage Server functionality is compromised or in danger of failure. You can monitor the active requests and alerts for a cell. In addition, you can receive e-mail notifications for alerts. To receive notifications, use the ALTER CELL command.

This section contains the following topics:

• Displaying Active Requests

• Displaying Alert Definitions

• Receiving Alert Notifications

• Monitoring syslog Messages Remotely

• Displaying Alert History

• Modifying Alert History

7.4.1 Displaying Active Requests

Use the LIST ACTIVEREQUEST command to display the active requests for the cell.

To view the ACTIVEREQUEST attributes, use the DESCRIBE ACTIVEREQUEST command.

Example 7-5 Listing Active Request Attributes

This example shows how to display a detailed list of attributes for a specified request I/O type.

CellCLI> LIST ACTIVEREQUEST WHERE IoType = 'predicate pushing' DETAIL

7.4.2 Displaying Alert Definitions

Use the LIST ALERTDEFINITION command to display the alert definitions for the cell. An alert definition provides a definition for every alert that can be produced on a cell.

Example 7-6 Listing Alert Definition Attributes

This example shows how to display a detailed list of attributes for an alert definition.

CellCLI> LIST ALERTDEFINITION ADRAlert DETAIL
         name:                   ADRAlert
         alertShortName:         ADR
         alertSource:            "Automatic Diagnostic Repository"
         alertType:              Stateless
         description:            "CELLSRV Incident Error"
         metricName:

Example 7-7 Listing Alert Definition Name and Description Attributes

You can display a list of specified attributes for an alert definition. This example shows how to display the alert name, metric name, and description. The metric name identifies the metric on which the alert is based. ADRAlert, HardwareAlert, Stateful_HardwareAlert, and Stateful_SoftwareAlert are not based on a metric, and therefore do not have metric names.

CellCLI> LIST ALERTDEFINITION ATTRIBUTES name, metricName, description 
    ADRAlert                                        "Incident Alert"
    HardwareAlert                                   "Hardware Alert"
    StatefulAlert_CD_IO_ERRS_MIN    CD_IO_ERRS_MIN  "Threshold Alert"
    StatefulAlert_CG_IO_RQ_LG       CG_IO_RQ_LG     "Threshold Alert"
    StatefulAlert_CG_IO_RQ_LG_SEC   CG_IO_RQ_LG_SEC "Threshold Alert"
    StatefulAlert_CG_IO_RQ_SM       CG_IO_RQ_SM     "Threshold Alert"
    StatefulAlert_CG_IO_RQ_SM_SEC   CG_IO_RQ_SM_SEC "Threshold Alert"
    StatefulAlert_CG_IO_WT_LG       CG_IO_WT_LG     "Threshold Alert"
    StatefulAlert_CG_IO_WT_LG_RQ    CG_IO_WT_LG_RQ  "Threshold Alert"
    StatefulAlert_CG_IO_WT_SM       CG_IO_WT_SM     "Threshold Alert"
    StatefulAlert_CG_IO_WT_SM_RQ    CG_IO_WT_SM_RQ  "Threshold Alert"
    StatefulAlert_CL_FSUT           CL_FSUT         "Threshold Alert"
    StatefulAlert_CL_MEMUT          CL_MEMUT        "Threshold Alert"
    StatefulAlert_CT_IO_RQ_LG       CT_IO_RQ_LG     "Threshold Alert"
    StatefulAlert_CT_IO_RQ_LG_SEC   CT_IO_RQ_LG_SEC "Threshold Alert"
    StatefulAlert_CT_IO_RQ_SM       CT_IO_RQ_SM     "Threshold Alert"
    StatefulAlert_CT_IO_RQ_SM_SEC   CT_IO_RQ_SM_SEC "Threshold Alert"
    StatefulAlert_CT_IO_WT_LG       CT_IO_WT_LG     "Threshold Alert"
    StatefulAlert_CT_IO_WT_LG_RQ    CT_IO_WT_LG_RQ  "Threshold Alert"
    StatefulAlert_CT_IO_WT_SM       CT_IO_WT_SM     "Threshold Alert"
    StatefulAlert_CT_IO_WT_SM_RQ    CT_IO_WT_SM_RQ  "Threshold Alert"
    StatefulAlert_DB_IO_RQ_LG       DB_IO_RQ_LG     "Threshold Alert"
    StatefulAlert_DB_IO_RQ_LG_SEC   DB_IO_RQ_LG_SEC "Threshold Alert"
    StatefulAlert_DB_IO_RQ_SM       DB_IO_RQ_SM     "Threshold Alert"
    StatefulAlert_DB_IO_RQ_SM_SEC   DB_IO_RQ_SM_SEC "Threshold Alert"
    StatefulAlert_DB_IO_WT_LG       DB_IO_WT_LG     "Threshold Alert"
    StatefulAlert_DB_IO_WT_LG_RQ    DB_IO_WT_LG_RQ  "Threshold Alert"
    StatefulAlert_DB_IO_WT_SM       DB_IO_WT_SM     "Threshold Alert"
    StatefulAlert_DB_IO_WT_SM_RQ    DB_IO_WT_SM_RQ  "Threshold Alert"
    StatefulAlert_GD_IO_ERRS_MIN    GD_IO_ERRS_MIN  "Threshold Alert"
    Stateful_HardwareAlert                          "Hardware Stateful Alert"
    Stateful_SoftwareAlert                          "Software Stateful Alert"

7.4.3 Receiving Alert Notifications

Administrators for Oracle Exadata System Software can receive alert notifications by e-mail or by Simple Network Management Protocol (SNMP) trap alerts.

Use of SNMP alerts allows Oracle Exadata Storage Servers to be monitored by a management application, such as Oracle Enterprise Manager Cloud Control. To configure Oracle Exadata Storage Server to send e-mail messages or SNMP trap alerts, use the ALTER CELL command.

Note:

The SNMP alerts conform to a MIB (management information base) which is included with each installation of Oracle Exadata System Software. The MIB file on Oracle Exadata Storage Server is available at /opt/oracle/cell/cellsrv/deploy/config/cell_alert.mib. The SNMP alerts and MIB conform to SNMP version 1 (SNMPv1).

7.4.4 Monitoring syslog Messages Remotely

By default, storage server syslog messages are written to local log files. A separate management server, known as a loghost server, can receive syslog messages from Oracle Exadata Storage Servers. To monitor the syslog messages remotely, configure the syslog service on the loghost server to listen for incoming syslog messages by setting SYSLOGD_OPTIONS -r in the loghost server /etc/sysconfig/syslog file. Configure each Oracle Exadata Storage Server to forward specified syslog messages to the loghost server using the ALTER CELL command. The configuration on Oracle Exadata Storage Servers is maintained across restarts and updates. The ALTER CELL VALIDATE SYSLOGCONF command can be used to test that messages are sent by the cells and received by the loghost server.

7.4.5 Displaying Alert History

Use the LIST ALERTHISTORY command to display the alert history that has occurred on a cell.

Alert history entries are retained for a maximum of 100 days. If the number of alert history entries exceeds 500, then the alert history entries are only retained for 7 days. When stateful alerts are cleared, meaning that the underlying metric, hardware or software condition has returned to normal, then the stateful alert is retained either 100 or 7 days, depending on the number of alert history entries. Stateful alerts that are not cleared are retained, regardless of their age.

Example 7-8 Listing Alert History Attributes

This example shows how to display a detailed list of attributes for alert history entries where the severity attribute is set to critical and the examinedBy attribute has not been set.

CellCLI> LIST ALERTHISTORY WHERE severity = 'critical' AND examinedBy = '' DETAIL

7.4.6 Modifying Alert History

Use the ALTER ALERTHISTORY command to update the alert history for the cell.

Example 7-9 Altering Alert History Attributes

This example shows how to set the examinedBy attribute to the user ID of the administrator that examined the alert. The examinedBy attribute is the only ALERTHISTORY attribute that can be modified.

CellCLI> ALTER ALERTHISTORY 1671443714 examinedBy="jdoe"

7.5 Monitoring Oracle Exadata Storage Server using Views

Several dynamic V$ views can help monitor the Oracle Exadata Storage Server environment. This section contains the following topics:

• Using the V$CELL and GV$CELL Views to Display Oracle Exadata Storage Server Identification

• Using V$BACKUP_DATAFILE with Oracle Exadata Storage Server

• Using V$SYSSTAT with Oracle Exadata System Software

• Using V$SEGMENT_STATISTICS with Oracle Exadata System Software

• Using V$SQL with Oracle Exadata System Software

• Using V$CELL_STATE to Display Oracle Exadata System Software Performance Statistics

• Using V$CELL_THREAD_HISTORY to Display Oracle Exadata System Software Threads

• Using V$CELL_REQUEST_TOTALS with Oracle Exadata System Software

• Using V$ASM_DISK_SPARSE and V$ASM_DISKGROUP_SPARSE to Monitor Sparse Disks

7.5.1 Using the V$CELL and GV$CELL Views to Display Oracle Exadata Storage Server Identification

The V$CELL view provides identifying information about cells.

Table 7-11 V$CELL View Columns and Descriptions

Column	Description
CELL_HASHVAL	A numeric hash value for the cell. For example: 138889696 Note: This value is useful because the P1 argument is often the cell hash name for cell-related wait events in the V$SESSION_WAIT and V$ACTIVE_SESSION_HISTORY views. Using this value, you can use a join with the V$CELL view on cell_hash to determine the corresponding cell name.
CELL_PATH	A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the cellip.ora file.
CELL_TYPE	The type of storage cell.

The GV$CELL view contains the same columns as the V$CELL view, and includes the INST_ID column. The INST_ID column displays the instance number from which the associated V$ view information was obtained. Querying a GV$ view retrieves the V$ view information from all qualified instances.

7.5.2 Using V$BACKUP_DATAFILE with Oracle Exadata Storage Server

The V$BACKUP_DATAFILE view contains columns relevant to Oracle Exadata Storage Server during Oracle Recovery Manager (RMAN) incremental backups.

Table 7-12 V$BACKUP_DATAFILE Columns and Descriptions

Column	Description
BLOCKS	Size of the backup data file in blocks.
BLOCKS_READ	The number of blocks that were scanned while taking this backup. If this is an incremental backup, and block change tracking was used to optimize the backup, then the value of this column is smaller than DATAFILE_BLOCKS. Otherwise, the value of this column is the same as DATAFILE_BLOCKS.
BLOCKS_SKIPPED_IN_CELL	The number of blocks that were read and filtered at the Oracle Exadata Storage Server to optimize the RMAN incremental backup.
DATAFILE_BLOCKS	Size of the data file in blocks at backup time. This value is also the number of blocks taken by the data file restarted from this backup.

The percentage of blocks skipped by Oracle Exadata System Software is calculated as follows:

(BLOCKS_SKIPPED_IN_CELL / BLOCKS_READ) * 100

This number changes significantly based on block change tracking.

If block change tracking for fast incremental backups is used, then most of the filtering is done at the database using the change tracking file, and the blocks are skipped before making an I/O request to the cell. If block change tracking is not used, then all of the blocks are filtered at the cell.

7.5.3 Using V$SYSSTAT with Oracle Exadata System Software

You can query the V$SYSSTAT view for statistics that can be used to compute Oracle Exadata System Software effectiveness.

Table 7-13 V$SYSSTAT View Key Statistics

Statistic	Description
cell flash cache read hits	The number of read requests that were a cache hit on Exadata flash cache.
cell IO uncompressed bytes	The total size of uncompressed data that is processed on the cell. For scan on Exadata Hybrid Columnar Compression-compressed tables, this statistic is the size of data after decompression.
cell overwrites in flash	Total number of mirror write requests that overwrote an existing mirror in Exadata Smart Flash Cache that had not been written to disk. This statistic is incremented once per mirror write.
cell partial writes in flash	Total number of mirror write requests written to both Exadata Smart Flash Cache and disk. Part of the data was written to flash, and the rest was written to disk. This statistic is incremented once per mirror write.
cell physical IO bytes eligible for predicate offload	The total number of I/O bytes processed with physical disks when processing was offloaded to the cell.
cell physical IO bytes saved by storage index	The number of bytes saved by storage index.
cell physical IO bytes saved during optimized file creation	The number of I/O bytes saved by the database host by offloading the file creation operation to cells. This statistic shows the Oracle Exadata System Software benefit due to optimized file creation operations.
cell physical IO bytes saved during optimized RMAN file restore	The number of I/O bytes saved by the database host by offloading the RMAN file restore operation to cells. This statistic shows the Oracle Exadata System Software benefit due to optimized RMAN file restore operations.
cell physical IO bytes sent directly to DB node to balance CPU usage	The number of I/O bytes sent back to the database server for processing due to CPU usage on Oracle Exadata Storage Server.
cell physical IO interconnect bytes	The number of I/O bytes exchanged over the interconnection between the database host and cells.
cell physical IO interconnect bytes returned by smart scan	The number of bytes that are returned by the cell for Smart Scan only, and does not include bytes for other database I/O.
cell writes to flash	Total number of mirror write requests written entirely to Exadata Smart Flash Cache. This statistic is incremented once per mirror write.
physical read partial requests	Total number of read requests satisfied from both Exadata Smart Flash Cache and hard disks. Part of the data was dirty on flash and rest was retrieved from disk.
physical read requests optimized	Total number of read requests satisfied either by using Exadata Smart Flash Cache or storage index.
physical read snap IO requests no data	The number of physical read I/O requests for which no physical I/O is done on the child file level.
physical read snap IO requests base	The number of physical I/Os on the base level.
physical read snap IO requests copy	The number of physical I/Os on any snapshot hierarchy.
physical read snap bytes base	The number of bytes read from the base.
physical read snap bytes copy	The number of bytes read from the snapshot.
physical write snap IO requests new allocation	The total number of new allocations on the snapshot.
physical read total bytes	Total amount of I/O bytes for reads processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.
physical read total bytes optimized	Total number of bytes read from Exadata Smart Flash Cache or storage index.
physical read total IO requests	The number of read requests that read one or more database blocks for all instance activity including application, backup, recovery, and other utilities.
physical write requests optimized	Total number of write requests in which all mirrors were written to the Exadata Smart Flash Cache.
physical write total bytes	Total amount of I/O bytes for writes processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.
physical write total bytes optimized	Total number of bytes written to Exadata Smart Flash Cache first. These bytes are synchronized to the disk in a lazy manner to create free space in flash cache.
physical write total IO requests	The number of write requests written to one or more database blocks from all instance activity including application activity, backup, recovery, and other utilities.

Example 7-10 Querying Cell Statistics in the V$SYSSTAT View

This example shows the V$SYSSTAT statistics.

SQL> SELECT name, value/1024/1024 MB FROM v$sysstat a WHERE
        a.name = 'physical read total bytes' OR
        a.name = 'physical write total bytes' OR
        a.name = 'cell physical IO interconnect bytes' OR
        a.name = 'cell physical IO bytes eligible for predicate offload' OR
        a.name = 'cell physical IO bytes saved during optimized file creation' OR
        a.name = 'cell physical IO bytes saved during optimized RMAN file restore'
        OR
        a.name = 'cell IO uncompressed bytes' OR
        a.name = 'cell physical IO interconnect bytes returned by smart scan' OR
        a.name = 'cell physical IO bytes saved by storage index';
 
 
NAME                                                                          MB
----------------------------------------------------------------------------------
physical read total bytes                                                  5425.11
physical write total bytes                                                   26.24
cell physical IO interconnect bytes                                         550.94
cell physical IO bytes saved during optimized file creation                      0
cell physical IO bytes saved during optimized RMAN file restore                  0
cell physical IO bytes eligible for predicate offload                      5372.53
cell physical IO bytes saved by storage index                                    0
cell physical IO interconnect bytes returned by smart scan                  472.12
cell IO uncompressed bytes                                                 5372.53
 
9 rows selected.

You can determine the amount of disk I/O performed by calculating the following:

physical read total bytes  + physical write total bytes - cell physical IO bytes
saved by storage index

7.5.3.1 Monitoring Write Statistics

Exafusion direct-to-wire protocol allows processes that read and serve cache blocks across an Oracle Real Applications Cluster (Oracle RAC) interconnect to directly send and receive messages over the InfiniBand network. The write statistics can be monitored using the V$SYSSTAT view. The following table lists the write statistics for the cell:

Table 7-14 Write Statistics for Cell Monitoring

Statistic	Description
cell physical write IO bytes eligible for offload	This write event is the total number of write I/O bytes that became eligible for high throughput write offload.
cell physical write IO host network bytes written during offload	This write event is the total number of write I/O bytes that became eligible for high throughput write offload, and were written directly from the database server process which created the logical write.
cell logical write IO requests	This write event is the total number of logical write requests issued by the process.
cell logical write IO requests eligible for offload	This write event is the total number of logical write requests that became eligible for high throughput write offload mode.

7.5.4 Using V$SEGMENT_STATISTICS with Oracle Exadata System Software

The V$SEGMENT_STATISTICS view lists 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 the cell. The optimized physical read segment statistic provides the number of read requests for an objects that were read from Exadata Smart Flash Cache or from the storage index. The optimized physical writes statistic provides the number of write requests for an object which went to Exadata Smart Flash Cache first. These write requests can later be synchronized to the disk in a lazy manner to create free space on the flash.

Example 7-11 Using the V$SEGMENT_STATISTICS View

This example shows a query that returns all objects that have performed more than 1000 optimized reads from a cell. A similar query can be used to determine which objects have performed very few optimized reads.

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

7.5.5 Using V$SQL with Oracle Exadata System Software

The V$SQL view lists statistics on shared SQL areas and contains one row for each child of the original SQL text entered.

You can query the following columns in the V$SQL view for physical I/O read disk bytes, physical I/O write disk bytes, cell physical I/O interconnect bytes, cell physical I/O bytes eligible for predicate offload, cell uncompressed bytes, cell interconnect bytes returned for Smart Scan, and the number of physical read requests returned by Exadata Smart Flash Cache or storage index:

• PHYSICAL_READ_BYTES

• PHYSICAL_WRITE_BYTES

• IO_INTERCONNECT_BYTES

• IO_CELL_OFFLOAD_ELIGIBLE_BYTES

• IO_CELL_UNCOMPRESSED_BYTES

• IO_CELL_OFFLOAD_RETURNED_BYTES

• OPTIMIZED_PHY_READ_REQUESTS

The values returned in the view are the number of bytes read by the parallel queries for the particular instance.

Note:

The preceding columns are also available in the following views:

• V$SQLAREA

• V$SQLAREA_PLAN_HASH

• V$SQLSTATS

• V$SQLSTATS_PLAN_HASH

Example 7-12 Querying the V$SQL View

This example shows a query returning Oracle Exadata System Software offload processing performance data. A WHERE predicate was used to select only the V$SQL command of interest, which was the query that scanned the SALES table. The performance data stored in V$SQL for the query shows that of the approximately 5 GB of data in the SALES table, all of it was eligible for offload processing. Because of the effectiveness of Oracle Exadata System Software offload processing, only 417 MB of data was delivered over the network to the database host.

SELECT sql_text,
       io_cell_offload_eligible_bytes/1024/1024 cell_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
       sql_text LIKE '%from sales%';

SQL_TEXT CELL_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

7.5.6 Using V$CELL_STATE to Display Oracle Exadata System Software Performance Statistics

The V$CELL_STATE view describes the state of all the cells accessible from the database client.

The state includes key performance statistics, such as Cell Server (CELLSRV) network layer information, scheduling layer information, and Cell Server configuration information. This view is used by Oracle Support Services.

Table 7-15 V$CELL_STATE Columns and Descriptions

Column	Description
CELL_NAME	A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the cellip.ora file. For example: 172.16.50.28
STATISTICS_TYPE	The statistics type, such as thread_stats.
OBJECT_NAME	Key for a specific statistics type, such as the thread ID if STATISTICS_TYPE is thread_stats.
STATISTICS_VALUE	The actual statistic values in an XML document with attribute and value pairs.

Example 7-13 Displaying Statistics from the V$CELL_STATE View

This example shows how to retrieve the information in the STATISTICS_VALUE column in a readable format.

SQL> SPOOL /tmp/cell_state_080619.log
SQL> SET PAGESIZE 10000
SQL> SET LONG 500000
SQL> SELECT  statistics_type, XMLTYPE(statistics_value) FROM v$cell_state;
SQL> SPOOL OFF

7.5.7 Using V$CELL_THREAD_HISTORY to Display Oracle Exadata System Software Threads

The V$CELL_THREAD_HISTORY view contains the samples that Cell Server (CELLSRV) takes of the threads in the cells visible to the database client.

This view is used by Oracle Support Services.

Table 7-16 V$CELL_THREAD_HISTORY Columns and Descriptions

Column	Description
CELL_NAME	A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the cellip.ora file. For example: 172.16.50.28
SNAPSHOT_ID	The ID of the snapshot (NUMBER).
SNAPSHOT_TIME	The date and time of the snapshot.
THREAD_ID	The thread ID (NUMBER).
JOB_TYPE	The job that the thread was running when the snapshot was taken.
WAIT_STATE	A unique state that identifies the location of the wait, if any exists.
WAIT_OBJECT_NAME	Object being waited on, if any exists. For example, the mutex name.
SQL_ID	The identifier of the SQL command that the client was processing for the job that is running.
DATABASE_ID	The ID of the database (NUMBER).
INSTANCE_ID	The ID of the instance (NUMBER).
SESSION_ID	The ID of the session (NUMBER).
SESSION_SERIAL_NUM	The session serial number (NUMBER).

7.5.8 Using V$CELL_REQUEST_TOTALS with Oracle Exadata System Software

The V$CELL_REQUEST_TOTALS view contains a historic view of the types and frequencies of the requests being run by a cell.

CELLSRV samples these requests periodically and stores them for use in this view. This view is used by Oracle Support Services.

Table 7-17 V$CELL_REQUEST_TOTALS Columns and Descriptions

Column	Description
CELL_NAME	A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the cellip.ora file. For example: 172.16.51.28
SNAPSHOT_ID	The ID of the snapshot (NUMBER).
SNAPSHOT_TIME	The date and time of the snapshot.
STATISTICS_NAME	The name of the statistic.
STATISTICS_VALUE	The value of the statistic.

7.5.9 Using V$ASM_DISK_SPARSE and V$ASM_DISKGROUP_SPARSE to Monitor Sparse Disks

The V$ASM_DISK_SPARSE and V$ASM_DISKGROUP_SPARSE views contain information about sparse disks.

Table 7-18 V$ASM_DISK_SPARSE Columns and Descriptions

Column	Description
GROUP_NUMBER	The number of the disk group containing the disk.
DISK_NUMBER	The number assigned to the disk within this disk group.
INCARNATION	The incarnation number for the disk.
ALLOCATED_MAT_MB	The total used physical and materialized capacity on the disk.
TOTAL_MAT_MB	The total physical capacity on the disk.
SPARSE_READS	The total number of I/O read requests on non-materialized regions of the disk.
SPARSE_BYTES_READ	The total number of bytes read from non-materialized regions of the disk.
SPARSE_READ_TIME	The time taken by sparse read I/O operations.

Table 7-19 V$ASM_DISKGROUP_SPARSE Columns and Descriptions

Column	Description
GROUP_NUMBER	The cluster-wide number assigned to the disk group.
ALLOCATED_MAT_MB	The total used physical and materialized capacity of the disk group.
TOTAL_MAT_MB	The total physical capacity of the disk group.

7.6 Understanding Oracle Exadata System Software Wait Events

Oracle uses a different set of wait events for disk I/O to Oracle Exadata Storage Server than disk I/O to conventional storage because the wait events that are designed for Oracle Exadata Storage Server directly show the cell and grid disk being accessed. This information is more useful for performance and diagnostics purposes than the database file# and block# information provided by wait events for conventional storage. Information about wait events is displayed in V$ dynamic performance views.

Note:

The V$SESSION_ACTIVE_HISTORY view can be used to analyze wait events. This view shows what has happened, when a query was run, and how it ran. It also shows what events the query had to wait on.

This section contains these topics:

• Monitoring Wait Events for Oracle Exadata System Software

• Using V$SESSION_WAIT to Monitor Sessions

• Using V$SYSTEM_EVENT to Monitor Wait Events

• Using V$SESSION_EVENT to Monitor Events by Sessions

7.6.1 Monitoring Wait Events for Oracle Exadata System Software

The following table lists the wait events useful for monitoring a cell.

Table 7-20 Wait Events Useful for Cell Monitoring

Wait Event	Description
cell interconnect retransmit during physical read	This wait event appears during retransmission for an I/O of a single-block or multiblock read. The cell hash number in the P1 column in the V$SESSION_WAIT view is the same cell identified for cell single block physical read and cell multiblock physical read. The P2 column contains the subnet number to the cell, and the P3 column contains the number of bytes processed during the I/O read operation.
cell list of blocks physical read	This wait event is equivalent to database file parallel read for a cell. The P1, P2, and P3 columns in V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the number of blocks processed during the I/O read operation.
cell multiblock physical read	This wait event is equivalent to db file scattered read for a cell. The P1, P2, and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the total number of bytes processed during the I/O read operation.
cell single block physical read	This wait event is equivalent to db file sequential read for a cell. The P1, P2, and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the number of bytes processed during the I/O read operation.
cell smart file creation	This wait event appears when the database is waiting for the completion of a file creation on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell compared to the rest of the cells.
cell smart incremental backup	This wait event appears when the database is waiting for the completion of an incremental backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
cell smart index scan	This wait event appears when the database is waiting for index or index-organized table (IOT) fast full scans. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
cell smart restore from backup	This wait event appears when the database is waiting for the completion of a file initialization for restore from backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
cell smart table scan	This wait event appears when the database is waiting for table scans to complete on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
cell sparse block physical read	This wait event appears when an I/O did not return any data.
cell statistics gather	This wait event appears when a select is done on the V$CELL_STATE, V$CELL_THREAD_HISTORY, or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.

If a cell hash number or disk hash number is associated with these wait events, then the value can be joined with the CELL_HASHVAL column of V$CELL and the HASH_VALUE column of V$ASM_DISK to help identify slow cells or disks.

7.6.2 Using V$SESSION_WAIT to Monitor Sessions

The V$SESSION_WAIT view displays the current or last wait for each session.

Example 7-14 Using the V$SESSION_WAIT View

This example shows how to query the V$SESSION_WAIT view. The second SELECT query displays the cell path and disk name.

SELECT w.event, w.p1, w.p2, w.p3 FROM V$SESSION_WAIT w, V$EVENT_NAME e 
       WHERE e.name LIKE 'cell%' AND e.wait_class_id = w.wait_class_id;

SELECT w.event, c.cell_path, d.name, w.p3 FROM V$SESSION_WAIT w,
       V$EVENT_NAME e, V$ASM_DISK d, V$CELL c
       WHERE e.name LIKE 'cell%' AND e.wait_class_id = w.wait_class_id
       AND w.p1 = c.cell_hashval AND w.p2 = d.hash_value;

7.6.3 Using V$SYSTEM_EVENT to Monitor Wait Events

The V$SYSTEM_EVENT view displays information about the number of total waits for an event.

Example 7-15 Using the V$SYSTEM_EVENT View

This example shows how to query the V$SYSTEM_EVENT view.

SELECT s.event FROM V$SYSTEM_EVENT s, V$EVENT_NAME e WHERE e.name LIKE 'cell%'
       AND e.event_id = s.event_id;

7.6.4 Using V$SESSION_EVENT to Monitor Events by Sessions

The V$SESSION_EVENT view displays information about waits for an event by a session.

Example 7-16 Using the V$SESSION_EVENT View

This example shows how to query the V$SESSION_EVENT view.

SELECT s.event FROM V$SESSION_EVENT s, V$EVENT_NAME e WHERE e.name LIKE 'cell%'
       AND e.event_id = s.event_id;

7.7 Optimizing Performance

You can use various settings to optimize the performance of Oracle Exadata System Software.

• About Exadata Hybrid Columnar Compression

• About SQL Processing Offload

• About SQL Tuning Advisor

• About SQL Monitor

• About Quality of Service Management

• About Fault Isolation

• About Fast Disk Scan Rates

• About Indexes and Scan Rates

• About Automatic Extent Management

• About Minimum Extent Size

• About SQL*Loader in Parallel Mode

• About Fragmentation

• About Disk Group Balance

• About Oracle ASM Disk Repair Timer

7.7.1 About Exadata Hybrid Columnar Compression

With Exadata Hybrid Columnar Compression, the database stores the same column for a group of rows together. Storing the database column values together improves the effectiveness of compression algorithms. The data block does not store the rows in the row-major format. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads.

Exadata Hybrid Columnar Compression provides a very high degree of size reduction. Database operations work transparently against compressed objects, so no application changes are required.

You can specify the following types of Exadata Hybrid Columnar Compression, depending on your requirements:

• Warehouse compression: This type of compression is optimized for query performance, and is intended for data warehouse applications.

• Archive compression: This type of compression is optimized for maximum compression levels, and is intended for historic data and data that does not change.

Assume that you apply Exadata Hybrid Columnar Compression to a daily_sales table. At the end of every day, the table is populated with items and the number sold, with the item ID and date forming a composite primary key. A row subset is shown in Table 7-21.

Table 7-21 Sample Table daily_sales

Item_ID	Date	Num_Sold	Shipped_From	Restock
1000	01-JUN-07	2	WAREHOUSE1	Y
1001	01-JUN-07	0	WAREHOUSE3	N
1002	01-JUN-07	1	WAREHOUSE3	N
1003	01-JUN-07	0	WAREHOUSE2	N
1004	01-JUN-07	2	WAREHOUSE1	N
1005	01-JUN-07	1	WAREHOUSE2	N

The database stores a set of rows in an internal structure called a compression unit. For example, assume that the rows in Table 7-21 are stored in one unit. Exadata Hybrid Columnar Compression stores each unique value from column 4 with metadata that maps the values to the rows. Conceptually, the compressed value can be represented as follows:

WAREHOUSE1WAREHOUSE3WAREHOUSE2

The database then compresses the repeated word WAREHOUSE in this value by storing it once and replacing each occurrence with a reference. If the reference is smaller than the original word, then the database achieves compression. The compression benefit is particularly evident for the Date column, which contains only one unique value.

As shown in Figure 7-1, each compression unit can span multiple data blocks. The values for a particular column may or may not span multiple blocks.

Figure 7-1 Compression Unit

Description of "Figure 7-1 Compression Unit"

Exadata Hybrid Columnar Compression has implications for row locking. When an update occurs for a row in an uncompressed data block, only the updated row is locked. In contrast, the database must lock all rows in the compression unit if an update is made to any row in the unit. Updates to rows using Exadata Hybrid Columnar Compression cause rowids to change.

Note:

When tables use Exadata Hybrid Columnar Compression, Oracle DML locks larger blocks of data (compression units) which may reduce concurrency.

Oracle Database supports four methods of table compression. The methods are summarized in Table 7-22.

Table 7-22 Table Compression Methods

Table Compression Method	Compression Level	CPU Overhead	Applications
Basic compression	High	Minimal	DSS
OLTP compression	High	Minimal	OLTP, DSS
Warehouse compression	Higher (compression level depends on compression level specified (LOW or HIGH))	Higher (CPU overhead depends on compression level specified (LOW or HIGH))	DSS
Archive compression	Highest (compression level depends on compression level specified (LOW or HIGH))	Highest (CPU overhead depends on compression level specified (LOW or HIGH))	Archiving

Warehouse compression and archive compression achieve the highest compression levels because they use Exadata Hybrid Columnar Compression technology. Exadata Hybrid Columnar Compression technology uses a modified form of columnar storage instead of row-major storage. This enables the database to store similar data together, which improves the effectiveness of compression algorithms. Because Exadata Hybrid Columnar Compression requires high CPU overhead for DML, use it only for data that is updated infrequently.

The higher compression levels of Exadata Hybrid Columnar Compression are achieved only with data that is direct-path inserted. Conventional inserts and updates are supported, but result in a less compressed format, and reduced compression level.

The following table lists characteristics of each table compression method.

Table 7-23 Table Compression Characteristics

Table Compression Method	CREATE/ALTER TABLE Syntax	Direct-Path Insert	DML
Basic compression	COMPRESS [BASIC] COMPRESS and COMPRESS BASIC are equivalent	Yes	Yes Notes: Inserted and updated rows are uncompressed.
OLTP compression	COMPRESS FOR OLTP	Yes	Yes
Warehouse compression	COMPRESS FOR QUERY [LOW|HIGH]	Yes	Yes Notes: High CPU overhead. Inserted and updated rows go to a block with a less compressed format and have lower compression level.
Archive compression	COMPRESS FOR ARCHIVE [LOW|HIGH]	Yes	Yes Notes: Inserted and updated rows are uncompressed. Inserted and updated rows go to a block with a less compressed format and have lower compression level.

The COMPRESS FOR QUERY HIGH option is the default data warehouse compression mode. It provides good compression and performance. The COMPRESS FOR QUERY LOW option should be used in environments where load performance is critical. It loads faster than data compressed with the COMPRESS FOR QUERY HIGH option.

The COMPRESS FOR ARCHIVE LOW option is the default archive compression mode. It provides a high compression level and good query performance. It is ideal for infrequently-accessed data. The COMPRESS FOR ARCHIVE HIGH option should be used for data that is rarely accessed.

A compression advisor, provided by the DBMS_COMPRESSION package, helps you determine the expected compression level for a particular table with a particular compression method.

You specify table compression with the COMPRESS clause of the CREATE TABLE command. You can enable compression for an existing table by using these clauses in an ALTER TABLE statement. In this case, only data that is inserted or updated is compressed after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS command. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.

7.7.1.1 Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.

SQL> SELECT table_name, compression, compress_for FROM user_tables;
 
TABLE_NAME       COMPRESSION   COMPRESS_FOR
---------------- ------------  -----------------
T1               DISABLED
T2               ENABLED       BASIC
T3               ENABLED       OLTP
T4               ENABLED       QUERY HIGH
T5               ENABLED       ARCHIVE LOW

SQL> SELECT table_name, partition_name, compression, compress_for
  FROM user_tab_partitions;

TABLE_NAME  PARTITION_NAME   COMPRESSION   COMPRESS_FOR
----------- ---------------- -----------   ------------------------------
SALES       Q4_2004          ENABLED       ARCHIVE HIGH
  ...
SALES       Q3_2008          ENABLED       QUERY HIGH
SALES       Q4_2008          ENABLED       QUERY HIGH
SALES       Q1_2009          ENABLED       OLTP
SALES       Q2_2009          ENABLED       OLTP

7.7.1.2 Determining Which Rows are Compressed

When Exadata Hybrid Columnar Compression tables are updated, the rows change to a lower level of compression, such as from COMP_FOR_QUERY_HIGH to COMP_FOR_OLTP or COMP_NOCOMPRESS. To determine the compression level of a row, use the following query:

DBMS_COMPRESSION.GET_COMPRESSION_TYPE (
   ownname    IN    VARCHAR2, 
   tabname    IN    VARCHAR2, 
   row_id     IN    ROWID)
  RETURN NUMBER;

By sampling the table rows, you can determine the percentage of rows that are no longer at the higher compression level. You can use ALTER TABLE or MOVE PARTITION to set the rows to a higher compression level. For example, if 10 percent of the rows are no longer at the highest compression level, then you might alter or move the rows to a higher compression level.

7.7.1.3 Changing Compression Level

The compression level can be changed for a partition, table, or tablespace.

An example of when you might want to change the compression level is: A company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free up disk space.

• To change the compression level of a partitioned table use the DBMS_REDEFINITION package.

  This package performs online redefinition of a table by creating a temporary copy of the table which holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level for the existing table, and the new table. Ensure you have enough hard disk space on your system before using the DBMS_REDEFINITION package.

• To change the compression level for a single partition of a partitioned table, use the ALTER TABLE...MODIFY PARTITION command.
• To change the compression level of a non-partitioned table use the ALTER TABLE...MOVE...COMPRESS FOR... command.

  The ALTER TABLE...MOVE command does not permit DML statements against the table while the command is running.

• To change the compression level for a tablespace, use the ALTER TABLESPACE command.

7.7.1.4 Importing and Exporting Exadata Hybrid Columnar Compression Tables

You can use the impdp and expdp commands to import and export Exadata Hybrid Columnar Compression tables.

Exadata Hybrid Columnar Compression tables can be imported using the impdp command of the Data Pump Import utility. By default, the impdp command preserves the table properties and the imported table is Exadata Hybrid Columnar Compression table. The tables can also be exported using the expdp command.

On tablespaces not supporting Exadata Hybrid Columnar Compression, the impdp command fails with the following error:

ORA-6430: hybrid columnar compression is only supported in tablespaces residing on
Exadata storage

You can import the Exadata Hybrid Columnar Compression table as an uncompressed table using the TRANSFORM:SEGMENT_ATTRIBUTES=n option clause of the impdp command.

An uncompressed or OLTP-compressed table can be converted to Exadata Hybrid Columnar Compression format during import. To convert a non-Exadata Hybrid Columnar Compression table to an Exadata Hybrid Columnar Compression table, do the following:

1. Specify default compression for the tablespace using the ALTER TABLESPACE ... SET DEFAULT COMPRESS command.
2. Override the SEGMENT_ATTRIBUTES option of the imported table during import.

7.7.1.5 Restoring an Exadata Hybrid Columnar Compression Table

There may be times when an Exadata Hybrid Columnar Compression table needs to be restored from a backup. The table can be restored to a system that supports Exadata Hybrid Columnar Compression, or to a system that does not support Exadata Hybrid Columnar Compression. When restoring a table with Exadata Hybrid Columnar Compression to a system that supports Exadata Hybrid Columnar Compression, restore the file using Oracle Recovery Manager (RMAN) as usual.

When an Exadata Hybrid Columnar Compression table is restored to a system that does not support Exadata Hybrid Columnar Compression, it is necessary to convert the table from Exadata Hybrid Columnar Compression to OLTP compression or a uncompressed format. To restore the table, do the following:

1. Ensure there is sufficient storage in the non-Oracle Exadata Storage Server environment to hold the data in uncompressed or OLTP compression format.
2. Use RMAN to restore the Exadata Hybrid Columnar Compression tablespace.
3. Use the following command to change the data compression from Exadata Hybrid Columnar Compression to NOCOMPRESS:

   ALTER TABLE table_name MOVE NOCOMPRESS
   

4. Use the following command to change each partition:

   ALTER TABLE table_name MOVE PARTITION partition_name NOCOMPRESS
   

   Change each partition separately.

5. Use the following command to move the data in parallel:

   ALTER TABLE table_name MOVE NOCOMPRESS PARALLEL
   

6. Use the following command to change the data compression from Exadata Hybrid Columnar Compression to OLTP COMPRESS:

   ALTER TABLE table_name MOVE COMPRESS for OLTP
   

7.7.2 About SQL Processing Offload

To optimize the performance of queries that do table and index scans, the database can offload data search and retrieval processing to the storage cell. This feature is managed by the CELL_OFFLOAD_PROCESSING and CELL_OFFLOAD_PLAN_DISPLAY initialization parameters.

• CELL_OFFLOAD_PROCESSING

• CELL_OFFLOAD_PLAN_DISPLAY

7.7.2.1 CELL_OFFLOAD_PROCESSING

The CELL_OFFLOAD_PROCESSING initialization parameter enables SQL processing offload to Oracle Exadata Storage Server.

When the value of the parameter is set to TRUE, predicate evaluation can be offloaded to cells. The default value of the parameter is TRUE. If the parameter is set to FALSE at the session or system level, then the database performs all the predicate evaluation with cells serving blocks. You can set CELL_OFFLOAD_PROCESSING dynamically with the SQL ALTER SYSTEM or ALTER SESSION commands, for example:

SQL> ALTER SESSION SET CELL_OFFLOAD_PROCESSING = TRUE;

The CELL_OFFLOAD_PROCESSING parameter can also be set with the OPT_PARAM optimizer hint to enable or disable predicate filtering for a specific SQL command.

• To disable CELL_OFFLOAD_PROCESSING for a SQL command:

  SELECT /*+ OPT_PARAM('cell_offload_processing' 'false') */ COUNT(*) FROM EMPLOYEES;
  

• To enable CELL_OFFLOAD_PROCESSING for a SQL command:

  SELECT /*+ OPT_PARAM('cell_offload_processing' 'true') */ COUNT(*) FROM EMPLOYEES;
  

Note:

The CELL_OFFLOAD_PROCESSING initialization parameter cannot be used to compare the performance of Oracle Exadata Storage Server with conventional storage. Even when CELL_OFFLOAD_PROCESSING is set to FALSE, Oracle Exadata Storage Server has many advantages over conventional storage. Oracle Exadata Storage Server is highly optimized for fast processing of large queries. It has no bottlenecks at the controller or other levels inside the cell. Oracle Exadata System Software uses a modern scale-out architecture and a state-of-the-art InfiniBand network that has much higher throughput than conventional storage networks. Oracle Exadata System Software is tightly integrated with the Oracle Database, and has unique capabilities for setup, execution, monitoring, diagnostics, resource management, and corruption prevention.

7.7.2.2 CELL_OFFLOAD_PLAN_DISPLAY

The database parameter CELL_OFFLOAD_PLAN_DISPLAY determines whether the SQL EXPLAIN PLAN command displays the predicates that can be evaluated by Oracle Exadata System Software as STORAGE predicates for a given SQL command.

The values for the CELL_OFFLOAD_PLAN_DISPLAY parameter are AUTO, ALWAYS, or NEVER. The default value is AUTO.

• AUTO instructs the SQL EXPLAIN PLAN command to display the predicates that can be evaluated as STORAGE only if a cell is present and if a table is on the cell.

• ALWAYS produces changes to the SQL EXPLAIN PLAN command based on Oracle Exadata System Software, whether or not Oracle Exadata System Software is present or the table is on the cell. You can use this setting to see what can be offloaded to Oracle Exadata Storage Server before migrating to Oracle Exadata Storage Server.

• NEVER produces no changes to the SQL EXPLAIN PLAN command for Oracle Exadata System Software.

You can set the CELL_OFFLOAD_PLAN_DISPLAY parameter dynamically with the SQL ALTER SYSTEM or ALTER SESSION commands. For example:

SQL> ALTER SESSION SET cell_offload_plan_display = ALWAYS;

7.7.2.3 CELL_OFFLOAD_DECRYPTION

The CELL_OFFLOAD_DECRYPTION initialization parameter enables decryption offload to Oracle Exadata Storage Servers. This decryption applies to both encrypted tablespaces and encrypted columns. When the value of the parameter is set to TRUE, decryption can be offloaded to cells. The default value of the parameter is TRUE. If the parameter is set to FALSE at the system level, then the database performs all decryption with cells serving blocks. You can set CELL_OFFLOAD_DECRYPTION dynamically with the SQL ALTER SYSTEM command. For example:

SQL> ALTER SYSTEM SET CELL_OFFLOAD_DECRYPTION = FALSE;

7.7.3 About SQL Tuning Advisor

SQL Tuning Advisor takes one or more SQL statements as input and uses the Automatic Tuning Optimizer to perform SQL tuning on the statements.

The output of SQL Tuning Advisor is in the form of advice or recommendations, along with a rationale for each recommendation and its expected benefit. SQL Tuning Advisor provides information about the following:

• Missing and stale statistics

• Better execution plans

• Better access paths and objects

• Better SQL statements

System statistics measure the performance of CPU and storage so that the optimizer can use these inputs when evaluating plans. The database automatically gathers default system statistics, called noworkload statistics, at the first instance startup. System statistics ensure the optimizer is aware of Oracle Exadata Database Machine performance.

Use the following SQL command to see if Oracle Exadata-specific statistics have been gathered.

SELECT pname, PVAL1  FROM aux_stats$ WHERE pname='MBRC';

If the PVAL1 is null or not set, then the system statistics have not been gathered manually and the default system statistics are being used.

It is not recommended to gather system statistics in Exadata mode for all Exadata environments. Oracle recommends using the defaults for system statistics in most cases. If you have acceptable performance with your current execution plans, then do not gather system statistics in Exadata mode. If the cardinality estimates in your execution plans are accurate, but suffer from the optimizer over-estimating the cost of a full table scan where the full scan performs better, then you should gather system statistics in Exadata mode. For new applications or deployments where the impact can be assessed from the beginning, and dealt with easily if there is a problem, you can gather system statistics in Exadata mode.

The following SQL command gathers system statistics in Exadata mode:

exec dbms_stats.gather_system_stats('EXADATA');

If your new application or new deployment testing reveals that the default system statistics performed as well or better than using manually collected system statistics, you can reset system statistics to their default values using the DBMS_STATS.DELETE_SYSTEM_STATS procedure. After executing this procedure, shut down and then reopen the database.

Note:

Oracle Exadata System Software must be using the latest patch bundle.

7.7.4 About SQL Monitor

Oracle Database has SQL monitor which allows you to monitor SQL statements as they are run.

The SQL monitor provides extensive statistics for queries, and works with serial and parallel queries. By default, SQL monitoring is automatically started when a SQL statement runs parallel queries, or when the statement has consumed at least 5 seconds of CPU or I/O time in a single execution. To monitor shorter queries, use the MONITOR hint.

The REPORT_SQL_MONITOR function builds a report in text, HTML or XML with the monitoring information collected about the execution of the SQL statement.

7.7.5 About Quality of Service Management

Oracle Exadata Quality of Service Management (QoS Management) allows system administrators to manage application service levels hosted on Oracle Exadata Database Machines. Using a policy-based architecture, QoS Management correlates accurate run-time performance and resource metrics, analyzes the data with its expert system to identify bottlenecks, and produces recommended resource adjustments to meet and maintain performance objectives under dynamic load conditions. When sufficient resources are not available, QoS Management preserves the most business critical objectives at the expense of the less critical ones.

In conjunction with Oracle Clusterware Cluster Health Monitor, QoS Management Memory Guard detects servers that are at risk of failure due to memory over-commitment. It responds to such conditions by automatically preventing new connections, and preserving existing workloads. QoS Management restores connectivity when sufficient memory is available.

7.7.6 About Fault Isolation

Oracle Exadata System Software has the ability to learn from the past events to avoid a potential fatal error.

When a faulty SQL statement caused a crash of the server in the past, Oracle Exadata System Software quarantines the SQL statement so that when the faulty SQL statement occurs again, Oracle Exadata System Software does not allow the SQL statement to perform Smart Scan. This reduces the chance of server software crashes, and improves storage availability. The following types of quarantine are available:

• SQL Plan: Created when Oracle Exadata System Software crashes while performing Smart Scan for a SQL statement. As a result, the SQL Plan for the SQL statement is quarantined, and Smart Scan is disabled for the SQL plan.

• Disk Region: Created when Oracle Exadata System Software crashes while performing Smart Scan of a disk region. As a result, the 1 MB disk region being scanned is quarantined and Smart Scan is disabled for the disk region.

• Database: Created when Oracle Exadata System Software detects that a particular database causes instability to a cell. Instability detection is based on the number of SQL Plan Quarantines for a database. Smart Scan is disabled for the database.

• Cell Offload: Created when Oracle Exadata System Software detects some offload feature has caused instability to a cell. Instability detection is based on the number of Database Quarantines for a cell. Smart Scan is disabled for all databases.

• Cell-to-Cell Offload: See "Quarantine Manager Support for Cell-to-Cell Offload Operations".

When a quarantine is created, alerts notify administrators of what was quarantined, why the quarantine was created, when and how the quarantine can be dropped manually, and when the quarantine is dropped automatically. All quarantines are automatically removed when a cell is patched or upgraded.

CellCLI commands are used to manually manipulate quarantines. For instance, the administrator can manually create a quarantine, drop a quarantine, change attributes of a quarantine, and list quarantines.

7.7.6.1 Quarantine Manager Support for Cell-to-Cell Offload Operations

Minimum Exadata software required: 12.2.1.1.0

Quarantine manager support is enabled for rebalance and high throughput writes in cell-to-cell offload operations. If Exadata detects a crash during these operations, the offending operation will be quarantined, and Exadata will fall back to using non-offloaded operations.

These types of quarantines are most likely caused by incompatible versions of CELLSRV. If such quarantines occur on your system, contact Oracle Support Services.

For rebalance operations, the quarantine is based on the ASM cluster ID. Rebalance will continue using the fallback path, which is slower.

For high throughput writes that originated from a database, the quarantine is based on a combination of ASM cluster ID and database ID.

For high throughput writes that originated from a CDB or PDB, the quarantine is based on a combination of ASM cluster ID and container database ID.

To identify these types of quarantine, run the LIST QUARANTINE DETAIL command and check the value of the quarantineType attribute. Values for this attribute for these quarantines are ASM_OFFLOAD_REBALANCE and HIGH_THROUGHPUT_WRITE. For the HIGH_THROUGHPUT_WRITE type there is a database case and a CDB case.

The LIST QUARANTINE statement produces output that looks like the following:

For rebalance:

CellCLI> list quarantine detail
 name:                   2
 asmClusterId:           b6063030c0ffef8dffcc99bd18b91a62
 cellsrvChecksum:        9f98483ef351a1352d567ebb1ca8aeab
 clientPID:              10308
 comment:                None
 crashReason:            ORA-600[CacheGet::process:C2C_OFFLOAD_CACHEGET_CRASH]
 creationTime:           2016-06-23T22:33:30-07:00
 dbUniqueID:             0
 dbUniqueName:           UnknownDBName
 incidentID:             1
 quarantineMode:         "FULL Quarantine"
 quarantinePlan:         SYSTEM
 quarantineReason:       Crash
 quarantineType:         ASM_OFFLOAD_REBALANCE
 remoteHostName:         slc10vwt
 rpmVersion:             OSS_MAIN_LINUX.X64_160623

For high throughput writes that originated from database:

CellCLI> list quarantine detail
 name:                   10
 asmClusterId:           b6063030c0ffef8dffcc99bd18b91a62
 cellsrvChecksum:        9f98483ef351a1352d567ebb1ca8aeab
 clientPID:              8377
 comment:                None
 crashReason:            ORA-600[CacheGet::process:C2C_OFFLOAD_CACHEGET_CRASH]
 creationTime:           2016-06-23T23:47:01-07:00
 conDbUniqueID:          0
 conDbUniqueName:        UnknownDBName
 dbUniqueID:             4263312973
 dbUniqueName:           WRITES
 incidentID:             25
 quarantineMode:         "FULL Quarantine"
 quarantinePlan:         SYSTEM
 quarantineReason:       Crash
 quarantineType:         HIGH_THROUGHPUT_WRITE
 remoteHostName:         slc10vwt
 rpmVersion:             OSS_MAIN_LINUX.X64_160623

For high throughput writes that originated from the CDB (differences noted in bold):

CellCLI> list quarantine detail
 name:                   10
 asmClusterId:           eff096e82317ff87bfb2ee163731f7f7
 cellsrvChecksum:        9f98483ef351a1352d567ebb1ca8aeab
 clientPID:              17206
 comment:                None
 crashReason:            ORA-600[CacheGet::process:C2C_OFFLOAD_CACHEGET_CRASH]
 creationTime:           2016-06-24T12:59:06-07:00
 conDbUniqueID:          4263312973 
 conDbUniqueName:        WRITES 
 dbUniqueID:             0 
 dbUniqueName:           UnknownDBName 
 incidentID:             25
 quarantineMode:         "FULL Quarantine"
 quarantinePlan:         SYSTEM
 quarantineReason:       Crash
 quarantineType:         HIGH_THROUGHPUT_WRITE
 remoteHostName:         slc10vwt
 rpmVersion:             OSS_MAIN_LINUX.X64_160623

7.7.7 About Fast Disk Scan Rates

To achieve fast disk scan rates, it is important to lay out segments with at least 4 MB of contiguous space. This allows disk scans to read 4 MB of data before performing another seek at a different location on disk. To ensure segments are laid out with 4 MB of contiguous space, set the Oracle ASM allocation unit size to 4 MB, and ensure data file extents are also at least 4 MB. The allocation unit can be set with the disk group attribute AU_SIZE when creating the disk group.

The following SQL command creates a disk group with the allocation unit set to 4 MB. The compatible.rdbms attribute is set to 11.2.0.2 in order to support both release 11.2.0.2 and release 11.2.0.3 databases in a consolidated environment.

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY 
     DISK 'o/*/data_CD*'
     ATTRIBUTE 'compatible.rdbms' = '11.2.0.2', 
               'compatible.asm' = '11.2.0.3',
               'content.type' = 'data',
               'cell.smart_scan_capable' = 'TRUE',
               'au_size' = '4M';

7.7.8 About Indexes and Scan Rates

In the past, storage required indexes for good performance. Oracle Exadata System Software can have superior scan rates without using indexes.

Review the application execution plans that use indexes to determine if they would run faster with Oracle Exadata System Software scans. To determine if a scan would be faster when there is no index, make the index invisible to the optimizer. An invisible index is maintained by DML operations, but it is not used by the optimizer.

To make the index invisible, use the following command, where index_name is the name of the index:

ALTER INDEX index_name INVISIBLE;

7.7.9 About Automatic Extent Management

Table extent size is managed automatically in locally-managed tablespaces using the AUTOALLOCATE option. This option automatically increases the size of the extent depending on segment size, available free space in the tablespace, and other factors. The extent size starts at 64 KB and increases to 1 MB for the segment. The extent can increase to 8 MB if the segment size exceeds 64 MB. For a large table, the extent size automatically increases to accommodate the table.

7.7.10 About Minimum Extent Size

Extent size is based on the size of the segment, not the table. A large table that has many partitions can have many small segments. For example, a 100 GB table that is partitioned into 1000 partitions has an average segment size of 100 MB. Therefore, it is good practice to ensure that large tables with many partitions use large extents. To do this, you can provide a hint to the database when the table is created or use an instance level initialization parameter. The following option can be used to hint to the database to allocate large extents for a table:

• The INITIAL storage parameter sets the starting extent size for tables in locally-managed tablespace. The database makes best effort to size every extent in the segment to be at least the extent size defined by the INITIAL storage parameter. For tables that are large, set INITIAL to 8 MB. This storage parameter affects newly-created segments. It is not possible to alter the INITIAL parameter for objects that have already been created.

7.7.11 About SQL*Loader in Parallel Mode

When SQL*Loader is used to load data in parallel, the server uses temporary segments to load data, and then merge the segments into the base segment during transaction commit. For heavily partitioned tables, do not use SQL*Loader to load data. Instead, put the data in a flat file, and use parallel INSERT direct load to load from the flat file using the external tables feature of the database.

7.7.12 About Fragmentation

A table is considered to be fragmented when the number of small extents in the table is high relative to the size of the table. When the number of small extents is high, then the metadata used to map the segment space is bloated, and the user data is not optimally laid out on disk. This impacts the performance of most of the operations including subsequent load, scan, DDL and DML operations. If the extent size are significantly less than 8 MB, then the segment must be reorganized. Large objects must have at least 8 MB extent sizes. There are several methods to reorganize objects. During reorganization, the storage parameter INITIAL may be set to at least 8 MB for the target object. If the table is not partitioned and very large, then consider partitioning it.

See Also:

Oracle Database Administrator’s Guide for additional information about the following:

• Reclaiming space

• Redefinition of tablespaces

7.7.13 About Disk Group Balance

Files should be equally balanced across all disks. The following queries and script can be used to check disk group balance:

• To check I/O balance, query the V$ASM_DISK_IOSTAT view before and after running a large SQL statement. For example, if a large query has a lot of reads, then the read column and the read_bytes column should be approximately the same for all disks in the disk group.

• To check all mounted disk groups, run the script available in My Oracle Support Note 367445.1. My Oracle Support is available at

  http://support.oracle.com

7.7.14 About Oracle ASM Disk Repair Timer

The Oracle ASM disk repair timer represents the amount of time a disk can remain offline before it is dropped by Oracle ASM. While the disk is offline, Oracle ASM tracks the changed extents so the disk can be resynchronized when it comes back online. The default disk repair time is 3.6 hours. If the default is inadequate, then the attribute value can be changed to the maximum amount of time it might take to detect and repair a temporary disk failure. The following command is an example of changing the disk repair timer value to 8.5 hours for the DATA disk group:

ALTER DISKGROUP data SET ATTRIBUTE 'disk_repair_time' = '8.5h'

The disk_repair_time attribute does not change the repair timer for disks currently offline. The repair timer for those offline disks is either the default repair timer or the repair timer specified on the command line when the disks were manually set to offline. To change the repair timer for currently offline disks, use the OFFLINE command and specify a repair timer value. The following command is an example of changing the disk repair timer value for disks that are offline:

ALTER DISKGROUP data OFFLINE DISK data_CD_06_cell11 DROP AFTER 20h;

Note:

When the disk repair time value is increased, the vulnerability of a double failure is increased.

7.8 Using the SQL EXPLAIN PLAN Command with Oracle Exadata System Software

The SQL EXPLAIN PLAN command was enhanced to display the predicates that are evaluated on Oracle Exadata Storage Server.

The database parameter CELL_OFFLOAD_PLAN_DISPLAY must be set to AUTO or ALWAYS for EXPLAIN PLAN to display predicates pushed to Oracle Exadata Storage Server.

Predicate evaluation is not offloaded to Oracle Exadata Storage Server in the following cases:

• The CELL_OFFLOAD_PROCESSING parameter is set to FALSE.

• The table or partition being scanned is small.

• The optimizer does not use direct path read.

• A scan is performed on a clustered table.

• A scan is performed on an index-organized table.

• A fast full scan is performed on compressed indexes.

• A fast full scan is performed on reverse key indexes.

• The table has row dependencies enabled or the rowscn is being fetched.

• The optimizer wants the scan to return rows in ROWID order.

• The optimizer does not use direct path read.

• The command is CREATE INDEX using nosort.

• A LOB or LONG column is being selected or queried.

• A SELECT ... VERSIONS query is done on a table.

• A query that has more than 255 columns referenced and heap table is uncompressed, or Basic or OLTP compressed. However such queries on Exadata Hybrid Columnar Compression-compressed tables are offloaded.

• The tablespace is encrypted, and the CELL_OFFLOAD_DECRYPTION parameter is set to FALSE. In order for Oracle Exadata System Software to perform decryption, Oracle Database needs to send the decryption key to Oracle Exadata Storage Server. If there are security concerns about keys being shipped across the network to Oracle Exadata Storage Server, then disable the decryption feature.

• The tablespace is not completely stored on Oracle Exadata Storage Server.

• The predicate evaluation is on a virtual column.

Examples

In Example 7-17, the storage predicate in the plan output indicates the predicates evaluated by Oracle Exadata System Software. The storage predicate is shown in bold.

Example 7-18 shows the use of UTLXPLP to display the EXPLAIN PLAN command output when CELL_OFFLOAD_PLAN_DISPLAY is enabled.

Example 7-17 Using the EXPLAIN PLAN Command

SQL> EXPLAIN PLAN FOR
  SELECT t.prod_id, v.exp1, t2_prod_id, t2_amount_sold
  FROM   sales t, v1 v
  WHERE  t.prod_id = v.prod_id AND t.cust_id = v.cust_id
    AND  t.prod_id != 45
    AND  v.amount_sold * v.quantity_sold > 10000;

Explained.

SQL>
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2267424675

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|*  1 |  HASH JOIN                  |            |
|*  2 |   HASH JOIN                 |            |
|*  3 |    TABLE ACCESS STORAGE FULL| SALES      |
|*  4 |    TABLE ACCESS STORAGE FULL| SALES      |
|*  5 |   TABLE ACCESS STORAGE FULL | SALES      |
--------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."CUST_ID"="T2"."CUST_ID" AND
              "T1"."PROD_ID"="T2"."PROD_ID" AND "T1"."CUST_ID"="T2"."CUST_ID")
   2 - access("T"."PROD_ID"="T1"."PROD_ID")
   3 - storage("T1"."PROD_ID"<200 AND
              "T1"."AMOUNT_SOLD"*"T1"."QUANTITY_SOLD">10000 AND "T1"."PROD_ID"<>45)
       filter("T1"."PROD_ID"<200 AND
              "T1"."AMOUNT_SOLD"*"T1"."QUANTITY_SOLD">10000 AND "T1"."PROD_ID"<>45)
   4 - storage("T"."PROD_ID"<200 AND "T"."PROD_ID"<>45)
       filter("T"."PROD_ID"<200 AND "T"."PROD_ID"<>45)
   5 - storage("T2"."PROD_ID"<200 AND "T2"."PROD_ID"<>45)
       filter("T2"."PROD_ID"<200 AND "T2"."PROD_ID"<>45)

Example 7-18 Using UTLXPLP to Display the EXPLAIN PLAN Command

SQL> ALTER SESSION SET CELL_OFFLOAD_PLAN_DISPLAY = ALWAYS;
Session altered.

SQL> EXPLAIN PLAN FOR SELECT COUNT(*),SUM(creditlim) FROM s_customer1, s_region1
     WHERE s_customer1.zip = s_region1.zip AND region_code = 'NE' ;
Explained.

SQL> @?/rdbms/admin/utlxplp

... 
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   5 - access("S_CUSTOMER1"."ZIP"="S_REGION1"."ZIP")
  10 - storage("REGION_CODE"='NE')
       filter("REGION_CODE"='NE')
  15 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"S_CUSTOMER1"."ZIP"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"S_CUSTOMER1"."ZIP"))

See Also:

• Oracle Database SQL Tuning Guide for additional information about using and interpreting EXPLAIN PLAN commands

• Oracle Database SQL Language Reference for additional information about the SQL EXPLAIN PLAN command

• Oracle Database Reference and Oracle Database Performance Tuning Guide for additional information about OPTIMZER_MODE

7.8.1 SQL Storage Clause CELL_FLASH_CACHE for Exadata Smart Flash Cache

The CELL_FLASH_CACHE storage clause attribute allows the override of the automatic caching policy when the COMPATIBLE parameter is greater than or equal to 11.2.0.2. In addition, this storage clause attribute can be used to specify how specific database objects are cached in Exadata Smart Flash Cache. The CELL_FLASH_CACHE storage_clause option supports the following values:

• NONE: The value ensures that the database object buffers are never cached in Exadata Smart Flash Cache. This allows the user to reserve the flash cache space for useful and frequently-accessed objects.

• DEFAULT: This value specifies that database objects are cached with the default LRU (least recently used) of Exadata Smart Flash Cache. This is the default value for CELL_FLASH_CACHE when the storage clause is omitted from the command.

• KEEP: This value specifies the database object buffers remain cached in the flash cache as long as the flash cache is large enough.

The storage clause can be provided during the CREATE and ALTER commands for a table or other objects. Example 7-19 shows the CREATE TABLE command for the CELL_FLASH_CACHE use with the storage clause.

For objects where altering the storage clause is allowed, the ALTER command can be used with CELL_FLASH_CACHE, as shown in Example 7-20:

This CELL_FLASH_CACHE storage clause attribute can be queried using database views based on the object involved, as shown in Example 7-21.

See Also:

Oracle Database SQL Language Reference for additional information about storage_clause.

Example 7-19 Using CREATE TABLE with CELL_FLASH_CACHE

CREATE TABLE ptable (c1 number, c2 clob) TABLESPACE TBS_1
          PARTITION BY RANGE(c1) ( PARTITION p1 VALUES LESS THAN (100)
             TABLESPACE TBS_2 STORAGE (CELL_FLASH_CACHE DEFAULT),
          PARTITION p2 VALUES LESS THAN (200) TABLESPACE TBS_3
             STORAGE (CELL_FLASH_CACHE KEEP));

CREATE TABLE tkbcsrbc (c1 number, l1 clob)
           lob (l1) STORE AS securefile 
              (cache nologging STORAGE (CELL_FLASH_CACHE NONE))
           PCTFREE 0 TABLESPACE tbs_93 STORAGE 
              (initial 128K next 128K pctincrease 0);

Example 7-20 Using ALTER TABLE with CELL_FLASH_CACHE

ALTER TABLE tkbcsrbc STORAGE( CELL_FLASH_CACHE DEFAULT);

ALTER TABLE tkbcsrbc MODIFY LOB (l1) (STORAGE (CELL_FLASH_CACHE KEEP));

Example 7-21 Using Views to Query CELL_FLASH_CACHE Storage Clause

SELECT TABLESPACE_NAME, TABLE_NAME, CELL_FLASH_CACHE FROM user_tables WHERE table_name='TKBCSRBC';

SELECT CELL_FLASH_CACHE FROM ALL_INDEXES WHERE index_name='TKBCIDX';

7.8.2 Determining which SQL Operators and Functions are Supported by Oracle Exadata System Software

One of the primary factors in determining whether Oracle Exadata System Software performs predicate evaluation is the SQL operator or function referenced in the predicate.

Oracle Exadata System Software supports many SQL operators and functions. However, there are also some operators which are not supported. The dynamic performance view V$SQLFN_METADATA has been enhanced to indicate whether a given SQL operator or function is supported by Oracle Exadata System Software. The OFFLOADABLE column in the view has been added to provide this information. A YES value indicates that the operator is supported, while a NO value indicates the operator is not supported.

7.9 Enabling or Disabling In-Memory Columnar Caching on Storage Servers

In-Memory Columnar Caching on cells is enabled by default when the INMEMORY_SIZE is configured. You do not need to do anything to get this enhancement.

The Database In-Memory format cache offers a significant boost to the amount of data held in Database In-Memory format formats and to Smart Scan performance over and above that offered by the pure columnar Hybrid Columnar Compression (HCC) format.

This feature is available if you have licensed the Oracle Database In-Memory option.

You do not need to use any DDL to enable this behavior. However, if you need to override the default behavior, you can use a new DDL keyword CELLMEMORY with the ALTER TABLE command.

ALTER TABLE table_name  [ [ NO ] CELLMEMORY [ MEMCOMPRESS FOR [ QUERY | CAPACITY ] [ LOW | HIGH ] ]

Options and Clauses	Usage Description
NO CELLMEMORY	Indicates a table is ineligible for the rewrite from 12.1.0.2 columnar flash cache format into the 12.2 Database In-Memory format.
CELLMEMORY and CELLMEMORY MEMCOMPRESS FOR CAPACITY	Allows a table to be cached in the default Oracle Database 12.2 In-Memory format. You only need to use this clause to undo a previously specified NO CELLMEMORY statement or to change the specified compression level.
CELLMEMORY MEMCOMPRESS FOR QUERY	This option indicates that In-Memory column store data should be compressed less than if MEMCOMPRESS FOR CAPACITY was specified. This option can provide an increase in query performance but requires almost twice as much flash space.
LOW and HIGH	Not implemented at this time.

Example 7-22 Using CELLMEMORY and INMEMORY Options on the Same Table

You can use both INMEMORY and CELLMEMORY on the same table. For example:

CREATE TABLE t (c1 NUMBER) INMEMORY CELLMEMORY MEMCOMPRESS FOR QUERY;

Specifying both options is useful for when you have a low priority table that is unlikely to get loaded in memory. By also specifying CELLMEMORY you still get columnar performance.