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.
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.
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.
Related Topics
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.
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:
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.
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
Related Topics
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 ...
Related Topics
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
Related Topics
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 |
---|---|
|
Disk controller battery charge. |
|
Temperature of disk controller battery. |
|
The cell CPU utilization which is the instantaneous percentage of time over the previous minute that the system CPUs were not idle (from |
|
The percentage of CPU time used by CELLSRV. |
|
The percentage of CPU time used by MS. |
|
The instantaneous number of working fans on the cell. |
|
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. |
|
The number of I/O requests that did not return data. |
|
The number of I/O requests that did not return data per second. |
|
The percentage of total physical memory used on the cell. |
|
The percentage of physical memory used by CELLSRV. |
|
The percentage of physical memory used by MS. |
|
The instantaneous average number (over the preceding minute) of processes in the Linux run queue marked running or uninterruptible (from |
|
The number of swap pages read in KB per second. |
|
The number of swap pages written in KB per second. |
|
The percentage of swap space used. |
|
The instantaneous temperature (Celsius) of the server, provided by the Baseboard Management Controller (BMC). |
|
The amount of virtual memory used by CELLSRV in megabytes. |
|
Total memory(resident and swap) used by MS in megabytes. |
|
The I/O Resource Management (IORM) objective for the cell. |
|
The number of megabytes received by the InfiniBand interfaces per second. |
|
The number of megabytes transmitted by the InfiniBand interfaces per second. |
|
The number of kilobytes received by the Ethernet interfaces per second. |
|
The number of kilobytes transmitted by the Ethernet interfaces per second. |
|
The rate which is the total number of I/O packets received by interconnections per second. |
|
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
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 |
---|---|
|
The number of megabytes in flash cache that are not synchronized to the cell disk. |
|
The cumulative number of megabytes read in large blocks from a cell disk. |
|
The rate which is the number of megabytes read in large blocks per second from a cell disk. |
|
The number of megabytes read from a cell disk by the scrubbing job. |
|
The number of megabytes read per second from a cell disk by the scrubbing job. |
|
The cumulative number of megabytes read in small blocks from a cell disk. |
|
The rate which is the number of megabytes read in small blocks per second from a cell disk. |
|
The cumulative number of megabytes written in large blocks on a cell disk. |
|
The rate which is the number of megabytes written in large blocks per second on a cell disk. |
|
The cumulative number of megabytes written in small blocks on a cell disk. |
|
The rate which is the number of megabytes written in small blocks per second on a cell disk. |
|
The cumulative number of I/O errors on a cell disk. |
|
The rate of I/O errors on a cell disk per minute. |
|
The number of bad 1 MB blocks detected during a scrubbing job on a cell disk. |
|
The average I/O load from cell disks. I/O load specifies the length of the disk queue. It is similar to iostat's
Because this metric is computed by IORM, this metric is also available per database or PDB. |
|
The cumulative number of requests to read large blocks from a cell disk. |
|
The rate which is the number of requests to read large blocks per second from a cell disk. |
|
The number of requests to read from a cell disk by the scrubbing job. |
|
The number of requests to read per second from a cell disk by the scrubbing job. |
|
The cumulative number of requests to read small blocks from a cell disk. |
|
The rate which is the number of requests to read small blocks per second from a cell disk. |
|
The cumulative number of requests to write large blocks to a cell disk. |
|
The rate which is the number of requests to write large blocks per second to a cell disk. |
|
The cumulative number of requests to write small blocks to a cell disk. |
|
The rate which is the number of requests to write small blocks per second to a cell disk. |
|
Average service time per request for small I/O requests to a cell disk. |
|
The cumulative latency of reading large blocks from a cell disk. Units are microseconds per request. |
|
The rate which is the average latency of reading large blocks per request to a cell disk. Units are microseconds per request. |
|
The cumulative latency of reading small blocks from a cell disk. |
|
The rate which is the average latency of reading small blocks per request from a cell disk. Units are microseconds per request. |
|
The cumulative latency of writing large blocks to a cell disk. Units are microseconds per request. |
|
The rate which is the average latency of writing large blocks per request to a cell disk. Units are microseconds per request. |
|
The cumulative latency of writing small blocks to a cell disk. Units are microseconds per request. |
|
The rate which is the average latency of writing small blocks per request to a cell disk. Units are microseconds per request. |
|
The percentage of device utilization for the cell disk. This metric is similar to iostat's Because this metric is computed by IORM, it is also available per database or PDB. |
|
The percentage of disk resources utilized by large requests for the cell disk. |
|
The percentage of disk resources utilized by small requests for the cell disk. |
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 |
---|---|
|
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. |
|
The number of megabytes allocated for unflushed data in flash cache. |
|
The number of megabytes allocated for OLTP data in flash cache. |
|
The number of megabytes in flash cache that are not synchronized to the grid disks. |
|
The number of megabytes in flash cache which cannot be synchronized because the cached disks are not accessible. |
|
The number of megabytes used in flash cache. Tracks the valid bytes in flash cache. For some workloads, like OLTP, the |
|
The number of megabytes allocated for unflushed keep objects in flash cache. |
|
The number of megabytes for OLTP keep objects in flash cache. |
|
The number of megabytes pushed out of flash cache because of the space limit for |
|
The number of megabytes per second pushed out of flash cache because of space limit for |
|
The number of megabytes used for |
|
The number of megabytes used in columnar flash cache. |
|
The number of megabytes used for keep objects in columnar flash cache. |
|
The number of megabytes that were read from columnar flash cache. |
|
The number of megabytes eligible to read from columnar flash cache. |
|
The number of megabytes per second eligible to read from columnar flash cache. |
|
The number of megabytes per second that were read from columnar flash cache. |
|
The number of megabytes saved by reads from columnar flash cache. |
|
The number of megabytes saved per second by reads from columnar flash cache. |
|
The number of megabytes that are population writes into columnar flash cache due to read misses. |
|
The number of megabytes per second that are population writes into columnar flash cache due to read misses. |
|
The number of megabytes read from columnar flash cache for keep objects. |
|
The number of megabytes read per second from columnar flash cache for keep objects. |
|
The number of requests that were read from columnar flash cache. |
|
The number of read requests eligible for columnar flash cache. |
|
The number of read requests per second eligible for columnar flash cache. |
|
The number of requests per second that were read from columnar flash cache. |
|
The number of requests that are population writers into columnar flash cache due to read misses. |
|
The number of requests per second that are population writes into columnar flash cache due to read misses. |
|
The number of requests read for keep objects from columnar flash cache. |
|
The number of requests read per second for keep objects from columnar flash cache. |
|
The number of megabytes allocated for OLTP data in flash cache. |
|
The number of megabytes written from flash cache to hard disks. |
|
The number of megabytes per second written from flash cache to hard disks. |
|
The number of megabytes read from flash cache. |
|
The number of megabytes for active secondary reads satisfied from flash cache. |
|
The number of megabytes for active secondary reads not satisfied from flash cache. |
|
The number of megabytes per second for active secondary reads not satisfied from flash cache. |
|
The number of megabytes per second for active secondary reads satisfied from flash cache. |
|
The number of megabytes read from flash cache by disk writer. |
|
The number of megabytes per second read from flash cache by disk writer. |
|
The number of megabytes of DW data read from flash cache. |
|
The number of megabytes read from disks because not all requested data was in flash cache. |
|
The number of megabytes of DW data read from disks because not all requested data was in flash cache. |
|
The number of megabytes read from disks per second because not all requested data was in flash cache. |
|
The number of megabytes read per second from flash cache. |
|
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 |
|
The number of megabytes read from disk for I/O requests that bypass flash cache due to heavy load on flash cache. |
|
The number of megabytes read per second from disk for I/O requests that bypass flash cache due to heavy load on flash cache. |
|
The number of megabytes read from disk for I/O requests that bypass flash cache due to the large I/O size. |
|
The number of megabytes read per second from disk for I/O requests that bypass flash cache due to the large I/O size. |
|
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. |
|
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 |
|
The number of megabytes written to flash cache. |
|
The number of megabytes written to hard disks by disk writer. |
|
The number of megabytes per second written to hard disks by disk writer. |
|
The number of megabytes that are first writes into flash cache. |
|
The number of megabytes per second for first writes into flash cache. |
|
The number of megabytes that are flash cache metadata writes |
|
The number of megabytes per second that are flash cache metadata writes |
|
The number of megabytes that are overwrites into flash cache. |
|
The number of megabytes per second that are overwrites into flash cache. |
|
The number of megabytes for population writes into flash cache due to read misses. |
|
The number of megabytes per second that are population writes into flash cache due to read misses into flash cache. |
|
The number of megabytes per second written to flash cache. |
|
The number of megabytes written to disk for I/O requests that bypass flash cache. |
|
The number of megabytes written to disk for I/O requests that bypass flash cache due to heavy load on flash cache. |
|
The number of megabytes written per second to disk for I/O requests that bypass flash cache due to heavy load on flash cache. |
|
The number of megabytes written to disk for I/O requests that bypass flash cache due to the large I/O size. |
|
The number of megabytes written per second to disk for I/O requests that bypass flash cache due to the large I/O size. |
|
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. |
|
The number of megabytes written to disk per second for I/O requests that bypass flash cache. |
|
The number of megabytes read from flash cache for |
|
The number of megabytes read per second from flash cache for |
|
The number of megabytes written to flash cache for |
|
The number of megabytes per second written to flash cache for |
|
The number of I/O errors on flash cache. |
|
The number of requests written from flash cache to hard disks. |
|
The number of requests per second for flash cache writing data to hard disks. |
|
The number of read I/O requests satisfied from flash cache. |
|
The number of requests for active secondary reads satisfied from flash cache. |
|
The number of requests for active secondary reads not satisfied from flash cache. |
|
The number of requests per second for active secondary reads not satisfied from flash cache. |
|
The number of requests per second for active secondary reads satisfied from flash cache. |
|
The number of requests read from flash cache by disk writer. |
|
The number of requests per second read from flash cache by disk writer. |
|
The number of read I/O requests of DW data read from flash cache. |
|
The number of read I/O requests which did not find all data in flash cache. |
|
The number of read I/O requests of DW data read from disks because not all requested data was in flash cache. |
|
The number of read I/O requests per second which did not find all data in flash cache. |
|
The number of read I/O requests satisfied per second from flash cache. |
|
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 |
|
The number of requests read from disk that bypass flash cache due to heavy load on flash cache. |
|
The number of requests per second read from disk that bypassed flash cache due to heavy load on flash cache. |
|
The number of read I/O requests that bypass flash cache due to the large I/O size. |
|
The number of read I/O requests per second that bypass flash cache due to the large I/O size. |
|
The number of requests read from disk that bypass flash cache as the I/O is on non-primary non-active secondary mirror. |
|
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 |
|
The number of requests attempted to find space in the flash cache. |
|
The number of requests that failed to find space in the flash cache. |
|
The number of I/O requests which resulted in flash cache being populated with data. |
|
The number of requests written to hard disks by disk writer. |
|
The number of requests per second written to hard disks by disk writer. |
|
The number of requests that are first writes into flash cache. |
|
The number of requests per second that are first writes into flash cache. |
|
The number of requests that are flash cache metadata writes |
|
The number of requests per second that are flash cache metadata writes |
|
The number of requests that are overwrites into flash cache. |
|
The number of requests per second that are overwrites into flash cache. |
|
The number of requests that are population writes into flash cache due to read misses. |
|
The number of requests per second that are population writes into flash cache due to read misses. |
|
The number of I/O requests per second which resulted in flash cache being populated with data. |
|
The number of write I/O requests that bypass flash cache. |
|
The number of requests written to disk that bypass flash cache due to heavy load on flash cache. |
|
The number of requests written to disk per second that bypass flash cache due to heavy load on flash cache. |
|
The number of requests written to disk that bypass flash cache due to the large I/O size. |
|
The number of requests written to disk per second that bypass flash cache due to the large I/O size. |
|
The number of requests written to disk that bypass flash cache as the I/O is on non-primary, non-active secondary mirror. |
|
The number of write I/O requests per second that bypass flash cache. |
|
The number of read I/O requests for |
|
The number of read I/O requests for |
|
The number of read I/O requests per second for |
|
The number of read I/O requests per second for |
|
The number of read I/O requests for 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 |
|
The rate which is the number of read I/O requests per second for 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 |
|
The number of I/O requests for |
|
The number of I/O requests per second for |
|
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. |
|
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. |
|
The number of prefetch requests in flash cache. |
|
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. |
|
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:
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
—
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.
Related Topics
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 |
---|---|
|
The number of megabytes cached in flash cache that are not synchronized to the grid disk. |
|
The cumulative number of megabytes read in large blocks from a grid disk. |
|
The rate which is the number of megabytes read in large blocks per second from a grid disk. |
|
The number of megabytes read from a grid disk by the scrubbing job. |
|
The number of megabytes read per second from a grid disk by the scrubbing job. |
|
The cumulative number of megabytes read in small blocks from a grid disk. |
|
The rate which is the number of megabytes read in small blocks per second from a grid disk. |
|
The cumulative number of megabytes written in large blocks on a grid disk. |
|
The rate which is the number of megabytes written in large blocks per second on a grid disk. |
|
The cumulative number of megabytes written in small blocks on a grid disk. |
|
The rate which is the number of megabytes written in small blocks per second on a grid disk. |
|
The cumulative number of I/O errors on a grid disk. |
|
The rate of I/O errors on a grid disk per minute. |
|
The number of bad 1 MB blocks detected during a scrubbing job on a grid disk. |
|
The cumulative number of requests to read large blocks from a grid disk. |
|
The rate which is the number of requests to read large blocks per second from a grid disk. |
|
The number of requests to read from a grid disk by the scrubbing job. |
|
The number of requests to read per second from a grid disk by the scrubbing job. |
|
The cumulative number of requests to read small blocks from a grid disk. |
|
The rate which is the number of requests to read small blocks per second from a grid disk. |
|
The cumulative number of requests to write large blocks to a grid disk. |
|
The rate which is the number of requests to write large blocks per second to a grid disk. |
|
The cumulative number of requests to write small blocks to a grid disk. |
|
The rate which is the number of requests to write small blocks per second to a grid disk. |
|
The allocated physical space for grid disk in bytes. |
|
The bytes returned by partial I/Os. |
|
The bytes returned by partial I/Os per second. |
|
The sparse bytes redirected to original data block. |
|
The sparse bytes redirected to original data block per second. |
|
The number of I/O requests that returned partial data. |
|
The number of I/O requests that returned partial data per second. |
|
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. |
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 |
---|---|
|
The cumulative number of megabytes dropped during transmission to a particular host. |
|
The rate which is the number of megabytes dropped per second during transmission to a particular host. |
|
The cumulative number of megabytes dropped during remote direct memory access (RDMA) transmission to a particular host. |
|
The rate which is the number of megabytes dropped per second during RDMA transmission to a particular host. |
|
The cumulative number of megabytes received from a particular host. |
|
The rate which is the number of megabytes received per second from a particular host. |
|
The cumulative number of megabytes retransmitted to a particular host. |
|
The rate which is the number of megabytes retransmitted per second to a particular host. |
|
The cumulative number of megabytes transmitted to a particular host. |
|
The rate which is the number of megabytes transmitted per second to a particular host. |
|
The cumulative latency of the retry action during RDMA transmission to a particular host. |
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 |
---|---|
|
The cumulative number of megabytes eligible for offload by smart I/O. |
|
The number of megabytes per second eligible for offload by smart I/O. |
|
The cumulative number of interconnect megabytes returned by smart I/O. |
|
The number of interconnect megabytes per second returned by smart I/O. |
|
The cumulative number of megabytes of pass-through I/O by smart I/O. |
|
The number of megabytes per second of pass-through I/O by smart I/O. |
|
The cumulative number of megabytes read from flash cache by smart I/O. |
|
The cumulative number of megabytes read from both flash cache and hard disk by smart I/O. |
|
The number of megabytes per second read from both flash cache and hard disk by smart I/O. |
|
The number of megabytes per second read from flash cache by smart I/O. |
|
The cumulative number of megabytes read from hard disk by smart I/O. |
|
The number of megabytes per second read from hard disk by smart I/O. |
|
The cumulative number of read I/O requests from flash cache by smart I/O. |
|
The cumulative number of read I/O requests from both flash cache and hard disk by smart I/O. |
|
The number of read I/O requests per second from both flash cache and hard disk by smart I/O. |
|
The number of read I/O requests per second from flash cache by smart I/O. |
|
The cumulative number of megabytes read I/O requests from hard disk by smart I/O. |
|
The number of read I/O requests per second from hard disk by smart I/O. |
|
The cumulative number of megabytes sent to the database node to balance CPU by smart I/O. |
|
The number of megabytes per second sent to the database node to balance CPU by smart I/O. |
|
The cumulative number of megabytes saved by the storage index. |
|
The number of megabytes per second saved by the storage index. |
|
The cumulative number of megabytes of flash cache population writes by smart I/O. |
|
The number of megabytes per second of flash cache population writes by smart I/O. |
|
The cumulative number of megabytes written to hard disk by smart I/O. |
|
The number of megabytes per second written to hard disk by smart I/O. |
|
The cumulative number of I/O requests for flash cache population writes by smart I/O. |
|
The number of I/O requests per second for flash cache population writes by smart I/O. |
|
The cumulative number of write I/O requests to hard disk by smart I/O. |
|
The number of write I/O requests per second to hard disk by smart I/O. |
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.
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 |
---|---|
|
The number of megabytes of I/O per second for this category to flash cache. |
|
The number of I/O requests issued by an IORM category to flash cache. |
|
The number of large I/O requests issued by an IORM category to flash cache. |
|
The number of large I/O requests issued by an IORM category to flash cache per second. |
|
The number of I/O requests issued by an IORM category to flash cache per second. |
|
The number of small I/O requests issued by an IORM category to flash cache. |
|
The number of small I/O requests issued by an IORM category to flash cache per second. |
|
The number of megabytes of I/O per second for this category to flash disks. |
|
The average I/O load from this category for flash disks. For a description of I/O load, see "CD_IO_LOAD". |
|
The number of large I/O requests issued by an IORM category to flash disks. |
|
The number of large I/O requests issued by an IORM category to flash disks per second. |
|
The number of small I/O requests issued by an IORM category to flash disks. |
|
The number of small I/O requests issued by an IORM category to flash disks per second. |
|
The cumulative latency of reading or writing blocks for this category from flash disks. |
|
The cumulative latency of reading or writing large blocks for this category from flash disks. |
|
The rate which is the average latency of reading or writing large blocks per request for this category from flash disks. |
|
The rate which is the average latency of reading or writing blocks per request for this category from flash disks. |
|
The cumulative latency of reading or writing small blocks for this category from flash disks. |
|
The rate which is the average latency of reading or writing small blocks per request for this category from flash disks. |
|
The percentage of flash resources utilized from this category. |
|
The percentage of flash resources utilized by large requests from this category. |
|
The percentage of flash resources utilized by small requests from this category. |
|
The IORM wait time for large I/O requests issued to flash disks by an IORM category. |
|
The average IORM wait time per request for large I/O requests issued to flash disks by an IORM category. |
|
The IORM wait time for small I/O requests issued to flash disks by an IORM category. |
|
The average IORM wait time per request for small I/O requests issued to flash disks by an IORM category. |
|
The number of megabytes of I/O per second for this category to hard disks. |
|
The average I/O load from this category for hard disks. For a description of I/O load, see "CD_IO_LOAD". |
|
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. |
|
This metric is derived from |
|
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. |
|
This metric is derived from |
|
The cumulative latency of reading or writing large blocks for this category from hard disks. |
|
The rate which is the average latency of reading or writing large blocks per request for this category from hard disks. |
|
The cumulative latency of reading or writing small blocks for this category from hard disks. |
|
The rate which is the average latency of reading or writing small blocks per request for this category from hard disks. |
|
The percentage of disk resources utilized by large requests from this category. |
|
The percentage of disk resources utilized by small requests from this category. |
|
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. |
|
The average IORM wait time per request for large I/O requests issued to hard disks by an IORM category. |
|
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. |
|
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
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 |
---|---|
|
The number of megabytes allocated in flash cache for this pluggable database. |
|
The number of megabytes of I/O per second for the pluggable database to flash cache. |
|
The number of I/O requests issued by this pluggable database to flash cache. |
|
The number of large I/O requests issued by this pluggable database to flash cache. |
|
The number of large I/O requests issued by this pluggable database to flash cache per second. |
|
The number of I/O requests issued by this pluggable database to flash cache per second. |
|
The number of small I/O requests issued by this pluggable database to flash cache. |
|
The number of small IO requests issued by this pluggable database to flash cache per second. |
|
The number of megabytes of I/O per second for the pluggable database to flash disks. |
|
The average I/O load from this pluggable database for flash disks. For a description of I/O load, see "CD_IO_LOAD". |
|
The number of large I/O requests issued by a pluggable database to flash disks. |
|
The number of large I/O requests issued by a pluggable database to flash disks per second. |
|
The number of small I/O requests issued by a pluggable database to flash disks. |
|
The number of small I/O requests issued by a pluggable database to flash disks per second. |
|
The cumulative latency of reading or writing blocks by this pluggable database from flash disks. |
|
The cumulative latency of reading or writing large blocks by this pluggable database from flash disks. |
|
The rate which is the average latency of reading or writing large blocks per request by this pluggable database from flash disks. |
|
The rate which is the average latency of reading or writing blocks per request by this pluggable database from flash disks. |
|
The cumulative latency of reading or writing small blocks by this pluggable database from flash disks. |
|
The rate which is the average latency of reading or writing small blocks per request by this pluggable database from flash disks. |
|
The percentage of flash resources utilized from this pluggable database. |
|
The percentage of flash resources utilized by large requests from this pluggable database. |
|
The percentage of flash resources utilized by small requests from this pluggable database. |
|
The IORM wait time for large I/O requests issued to flash disks by this pluggable database. |
|
The average IORM wait time per request for large I/O requests issued to flash disks by this pluggable database. |
|
The IORM wait time for small I/O requests issued to flash disks by this pluggable database. |
|
The average IORM wait time per request for small I/O requests issued to flash disks by this pluggable database. |
|
The number of megabytes of I/O per second for the pluggable database to hard disks. |
|
The average I/O load from this pluggable database for hard disks. For a description of I/O load, see "CD_IO_LOAD". |
|
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. |
|
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. |
|
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. |
|
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. |
|
The cumulative latency of reading or writing large blocks by this pluggable database from hard disks. |
|
The rate which is the average latency of reading or writing large blocks per request by this pluggable database from hard disks. |
|
The cumulative latency of reading or writing small blocks by this pluggable database from hard disks. |
|
The rate which is the average latency of reading or writing small blocks per request by this pluggable database from hard disks. |
|
The percentage of disk resources utilized by large requests from this pluggable database. |
|
The percentage of disk resources utilized by small requests from this pluggable database. |
|
The IORM wait time for large I/O requests issued to hard disks by this pluggable database. |
|
The average IORM wait time per request for large I/O requests issued to hard disks by this pluggable database. |
|
The IORM wait time for small I/O requests issued to hard disks by this pluggable database. |
|
The average IORM wait time per request for small I/O requests issued to hard disks by this pluggable database. |
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 |
---|---|
|
The number of megabytes allocated in flash cache for this database. |
|
The number of megabytes of I/O per second for this database to flash cache. |
|
The number of I/O requests issued by a database to flash cache. |
|
The number of large I/O requests issued by a database to flash cache. |
|
The number of large I/O requests issued by a database to flash cache per second. |
|
The number of I/O requests issued by a database to flash cache per second. |
|
The number of small I/O requests issued by a database to flash cache. |
|
The number of small I/O requests issued by a database to flash cache per second. |
|
The number of megabytes of I/O per second for this database to flash disks. |
|
The average I/O load from this database for flash disks. For a description of I/O load, see "CD_IO_LOAD". |
|
The number of large I/O requests issued by a database to flash disks. |
|
The number of large I/O requests issued by a database to flash disks per second. |
|
The number of small I/O requests issued by a database to flash disks. |
|
The number of small I/O requests issued by a database to flash disks per second. |
|
The cumulative latency of reading or writing blocks by a database from flash disks. |
|
The cumulative latency of reading or writing large blocks by a database from flash disks. |
|
The rate which is the average latency of reading or writing large blocks per request by a database from flash disks. |
|
The rate which is the average latency of reading or writing blocks per request by a database from flash disks. |
|
The cumulative latency of reading or writing small blocks by a database from flash disks. |
|
The rate which is the average latency of reading or writing small blocks per request by a database from flash disks. |
|
The percentage of flash resources utilized from this database. |
|
The percentage of flash resources utilized by large requests from this database. |
|
The percentage of flash resources utilized by small requests from this database. |
|
The IORM wait time for large I/O requests issued to flash disks by a database. |
|
The average IORM wait time per request for large I/O requests issued to flash disks by a database. |
|
The IORM wait time for small I/O requests issued to flash disks by a database. |
|
The average IORM wait time per request for small I/O requests issued to flash disks by a database. |
|
The number of megabytes written to Oracle Exadata Smart Flash Log. |
|
The number of megabytes written per second to Oracle Exadata Smart Flash Log. |
|
The number of I/O requests issued to Oracle Exadata Smart Flash Log. |
|
The number of I/O requests per second issued to Oracle Exadata Smart Flash Log. |
|
The number of megabytes of I/O per second for this database to hard disks. |
|
The average I/O load from this database for hard disks. For a description of I/O load, see "CD_IO_LOAD". |
|
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. |
|
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. |
|
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. |
|
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. |
|
The cumulative latency of reading or writing large blocks by a database from hard disks. |
|
The rate which is the average latency of reading or writing large blocks per request by a database from hard disks. |
|
The cumulative latency of reading or writing small blocks by a database from hard disks. |
|
The rate which is the average latency of reading or writing small blocks per request by a database from hard disks. |
|
The percentage of hard disk resources utilized by large requests from this database. |
|
The percentage of hard disk resources utilized by small requests from this database. |
|
The IORM wait time for large I/O requests issued to hard disks by a database. |
|
The average IORM wait time per request for large I/O requests issued to hard disks by a database. |
|
The IORM wait time for small I/O requests issued to hard disks by a database. |
|
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_
.
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 |
---|---|
|
The number of megabytes of I/O per second for this consumer group to flash cache. |
|
The number of I/O requests issued by a consumer group to flash cache. |
|
The number of large I/O requests issued by a consumer group to flash cache. |
|
The number of large I/O requests issued by a consumer group to flash cache per second. |
|
The number of I/O requests issued by a consumer group to flash cache per second. |
|
The number of small I/O requests issued by a consumer group to flash cache. |
|
The number of small I/O requests issued by a consumer group to flash cache per second. |
|
The number of megabytes of I/O per second for this consumer group to flash disks. |
|
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". |
|
The number of large I/O requests issued by a consumer group to flash disks. |
|
The number of large I/O requests issued by a consumer group to flash disks per second. |
|
The number of small I/O requests issued by a consumer group to flash disks. |
|
The number of small I/O requests issued by a consumer group to flash disks per second. |
|
The cumulative latency of reading or writing blocks by a consumer group from flash disks. |
|
The cumulative latency of reading or writing large blocks by a consumer group from flash disks. |
|
The rate which is the average latency of reading or writing large blocks per request by a consumer group from flash disks. |
|
The rate which is the average latency of reading or writing blocks per request by a consumer group from flash disks. |
|
The cumulative latency of reading or writing small blocks by a consumer group from flash disks. |
|
The rate which is the average latency of reading or writing small blocks per request by a consumer group from flash disks. |
|
The percentage of flash resources utilized from this consumer group. |
|
The percentage of flash resources utilized by large requests from this consumer group. |
|
The percentage of flash resources utilized by small requests from this consumer group. |
|
The IORM wait time for large I/O requests issued to flash disks by a consumer group. |
|
The average IORM wait time per request for large I/O requests issued to flash disks by a consumer group. |
|
The IORM wait time for small I/O requests issued to flash disks by a consumer group. |
|
The average IORM wait time per request for small I/O requests issued to flash disks by a consumer group. |
|
The number of megabytes of I/O per second for this consumer group to hard disks. |
|
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". |
|
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. |
|
This metric is derived from |
|
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. |
|
This metric is derived from |
|
The cumulative latency of reading or writing large blocks by a consumer group from hard disks. |
|
The rate which is the average latency of reading or writing large blocks per request by a consumer group from hard disks. |
|
The cumulative latency of reading or writing small blocks by a consumer group from hard disks |
|
The rate which is the average latency of reading or writing small blocks per request by a consumer group from hard disks. |
|
The percentage of disk resources utilized by large requests from this consumer group. |
|
The percentage of disk resources utilized by small requests from this consumer group. |
|
The IORM wait time for large I/O requests issued to hard disks by a consumer group. |
|
The average IORM wait time per request for large I/O requests issued to hard disks by a consumer group. |
|
The IORM wait time for small I/O requests issued to hard disks by a consumer group. |
|
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
Related Topics
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.
Related Topics
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.
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:
Related Topics
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
Related Topics
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"
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).
Related Topics
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.
Related Topics
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
Related Topics
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"
Related Topics
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$SEGMENT_STATISTICS 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
The V$CELL
view provides identifying information about cells.
Table 7-11 V$CELL View Columns and Descriptions
Column | Description |
---|---|
|
A numeric hash value for the cell. For example: 138889696 Note: This value is useful because the |
|
A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the |
|
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.
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 |
---|---|
|
Size of the backup data file in blocks. |
|
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 |
|
The number of blocks that were read and filtered at the Oracle Exadata Storage Server to optimize the RMAN incremental backup. |
|
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.
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 |
---|---|
|
The number of read requests that were a cache hit on Exadata flash cache. |
|
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. |
|
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. |
|
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. |
|
The total number of I/O bytes processed with physical disks when processing was offloaded to the cell. |
|
The number of bytes saved by storage index. |
|
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. |
|
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. |
|
The number of I/O bytes sent back to the database server for processing due to CPU usage on Oracle Exadata Storage Server. |
|
The number of I/O bytes exchanged over the interconnection between the database host and cells. |
|
The number of bytes that are returned by the cell for Smart Scan only, and does not include bytes for other database I/O. |
|
Total number of mirror write requests written entirely to Exadata Smart Flash Cache. This statistic is incremented once per mirror write. |
|
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. |
|
Total number of read requests satisfied either by using Exadata Smart Flash Cache or storage index. |
|
The number of physical read I/O requests for which no physical I/O is done on the child file level. |
|
The number of physical I/Os on the base level. |
|
The number of physical I/Os on any snapshot hierarchy. |
|
The number of bytes read from the base. |
|
The number of bytes read from the snapshot. |
|
The total number of new allocations on the snapshot. |
|
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. |
|
Total number of bytes read from Exadata Smart Flash Cache or storage index. |
|
The number of read requests that read one or more database blocks for all instance activity including application, backup, recovery, and other utilities. |
|
Total number of write requests in which all mirrors were written to the Exadata Smart Flash Cache. |
|
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. |
|
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. |
|
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
Related Topics
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 |
---|---|
|
This write event is the total number of write I/O bytes that became eligible for high throughput write 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. |
|
This write event is the total number of logical write requests issued by the process. |
|
This write event is the total number of logical write requests that became eligible for high throughput write offload mode. |
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;
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
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 |
---|---|
|
A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the For example: 172.16.50.28 |
|
The statistics type, such as |
|
Key for a specific statistics type, such as the thread ID if |
|
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
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 |
---|---|
|
A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the For example: 172.16.50.28 |
|
The ID of the snapshot ( |
|
The date and time of the snapshot. |
|
The thread ID ( |
|
The job that the thread was running when the snapshot was taken. |
|
A unique state that identifies the location of the wait, if any exists. |
|
Object being waited on, if any exists. For example, the mutex name. |
|
The identifier of the SQL command that the client was processing for the job that is running. |
|
The ID of the database ( |
|
The ID of the instance ( |
|
The ID of the session ( |
|
The session serial number ( |
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 |
---|---|
|
A character string (maximum 400) that specifies the IP addresses of the cell. These are the IP addresses specified in the For example: 172.16.51.28 |
|
The ID of the snapshot ( |
|
The date and time of the snapshot. |
|
The name of the statistic. |
|
The value of the statistic. |
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. |
|
The number assigned to the disk within this disk group. |
|
The incarnation number for the disk. |
|
The total used physical and materialized capacity on the disk. |
|
The total physical capacity on the disk. |
|
The total number of I/O read requests on non-materialized regions of the disk. |
|
The total number of bytes read from non-materialized regions of the disk. |
|
The time taken by sparse read I/O operations. |
Table 7-19 V$ASM_DISKGROUP_SPARSE Columns and Descriptions
Column | Description |
---|---|
|
The cluster-wide number assigned to the disk group. |
|
The total used physical and materialized capacity of the disk group. |
|
The total physical capacity of the disk group. |
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:
Related Topics
The following table lists the wait events useful for monitoring a cell.
Table 7-20 Wait Events Useful for Cell Monitoring
Wait Event | Description |
---|---|
|
This wait event appears during retransmission for an I/O of a single-block or multiblock read. The cell hash number in the |
|
This wait event is equivalent to database file parallel read for a cell. The |
|
This wait event is equivalent to |
|
This wait event is equivalent to |
|
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 |
|
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 |
|
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 |
|
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 |
|
This wait event appears when the database is waiting for table scans to complete on a cell. The cell hash number in the |
|
This wait event appears when an I/O did not return any data. |
|
This wait event appears when a select is done on the |
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.
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;
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;
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;
You can use various settings to optimize the performance of Oracle Exadata System Software.
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.
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 |
|
Yes |
Yes Notes:
|
OLTP compression |
|
Yes |
Yes |
Warehouse compression |
|
Yes |
Yes Notes:
|
Archive compression |
|
Yes |
Yes Notes:
|
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.
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
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.
Related Topics
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.
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:
ALTER TABLESPACE ... SET DEFAULT COMPRESS
command.SEGMENT_ATTRIBUTES
option of the imported table during import.Related Topics
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:
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.
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.
Related Topics
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;
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;
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.
Related Topics
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.
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.
Related Topics
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.
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
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';
Related Topics
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;
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.
Related Topics
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.
Related Topics
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.
Related Topics
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:
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
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.
Related Topics
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
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';
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.
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.