7 Exadata Warehouse

Starting with Enterprise Manager 13.5 Release Update 15, you can set up Exadata Warehouse through Enterprise Manager Cloud Control such that it serves as a repository for fine-grained performance metric data collected from Oracle Exadata Database Service on Cloud@Customer (ExaCC).

It provides a platform for long-term retention of historical and forecast data for performance and capacity analysis and planning.

The following image illustrates the data flow into the Exadata Warehouse after the configuration is complete:

The fine-grained data from Exadata components like storage servers and compute servers are uploaded to an Autonomous Database. The Exadata Warehouse generates periodic forecasts based on historical data, which are available for queries and export. Older forecast data points are retained for easy comparison.

Since the Exadata Warehouse resides in an Autonomous Database that you specify, the data uploaded to the Exadata Warehouse can be accessed even after data uploads for the Exadata systems have been paused or terminated.

Topics:

• Setting up Exadata Warehouse and Analyzing Performance Data

• Simplifying Capacity Planning and Forecasting

• Exadata Warehouse Sample Queries

• Configuring Purge Settings for Old Metric Data

Setting up Exadata Warehouse and Analyzing Performance Data

For steps to set up Exadata Warehouse through Enterprise Manager Cloud Control, see Exporting and Analyzing Oracle Enterprise Manager Engineered Systems Performance Data Using Exadata Warehouse (Tutorial).

Simplifying Capacity Planning and Forecasting

Oracle Cloud Infrastructure (OCI) Operations Insights supports Exadata Warehouse for monitoring Oracle engineered systems like Exadata Database Machine and Zero Data Loss Recovery Appliance (ZDLRA).

Capabilities of Operations Insights’ Exadata Warehouse:

• Analyze resource usage for databases running on on-premises Exadata platforms across the enterprise

• Forecast future demand for resources based on historical trends

With the introduction of Exadata Warehouse, resource utilization of the Exadata systems can be analyzed to identify spare capacity for new workloads. This feature is available for Exadata systems that are managed by Enterprise Manager Cloud Control (EM) 13c Release 5 Update 7 (13.5.0.7) and above. The use of Exadata Warehouse features requires OCI Operations Insights service license subscription.

For more information, see Operations Insights: Analyze Exadata Resources.

Key functionality of fine-grained metric ingestion and forecasting:

• Ingest metrics to Autonomous Databases in customer's tenancy.

• Store fine-grained metric data with per-minute granularity. Offers upto 1,440 data points per metric, if available.

• Back-fill metric data to accommodate periods where monitoring of Exadata through Enterprise Manager is interrupted.

• Automated forecasts and roll-ups, updated periodically

• Forecast data using machine learning with seasonality models.

• Forecasts can be generated for up to the next 3 months by analyzing 9 months of historical data, subject to the availability of data.

Capacity Planning:

Exadata Warehouse collects metrics from ASM Cluster, Exadata Storage Server and Exadata VM Guests and provides coverage for the areas below:

• CPU/vCPU and Memory consumption

• Storage Utilization

• IO Capacity, utilization and performance

• Network performance

Default metrics collected by Exadata warehouse:

ASM Cluster	Exadata Storage Server	Exadata VM Guests	Zero Data Loss Recovery Appliance
Disk Group Physical Free (MB) Disk Group Free (MB) Disk Group Used % Used % of Safely Usable Physical Size (MB) Size (MB) Disk Group Usable Free (MB) Disk Group Usable (MB)	Large Read/Write Throughput Small Read/Write Throughput Large Write Throughput Small Write Throughput I/O Load Large Read Requests Small Read Requests Large Write Requests Small Write Requests Average Large Read Response Time Average Small Read Response Time Average Large Write Response Time Average Small Write Response Time I/O Utilization Flash Cache Size Flash Cache Used Read Throughput for Scan Read Throughput Redirected to Disk for Scan Read Throughput Redirected to Disk Read IOPS for Random I/O	CPU Utilization Memory Utilization Network Read Rate Network Write Rate	Storage Locations Unused Space (GB) Storage Locations Size (GB) Storage Locations Recovery Window Space (GB) Number of Protected Databases

Applications of Exadata Warehouse:

• Metric data from the Exadata Warehouse can be leveraged to build reports through Oracle Analytics Cloud to visualize historical and forecasted capacity utilization and resource usage.

Exadata Warehouse Sample Queries

Topics:

• Query 1: Top 10 Exadata Targets Based on Average CPU Usage Over a Chosen Time Period

• Query 2: Exadata Targets with Under-utilized IOPS

Query 1: Top 10 Exadata Targets Based on Average CPU Usage Over a Chosen Time Period

Use this query to get a list of the top 10 Oracle Exadata Database Machines, based on Average CPU Usage. The query takes the EM_ID and historic period in days as parameters and returns a list of up to 10 Oracle Exadata Database Machines sorted in descending order on the basis of CPU utilization over the selected historical period.

Input Parameters:

• EM_ID
• Historical time period in days, for example, 180 for 6 months

WITH cpu_usage_historical AS (
  SELECT
    em_id,
    target_name,
    target_type,
    mean_value v
  FROM
    XAWH_DAILY_METRIC_DATA
  WHERE
      em_id = :1
    AND target_type = 'host'
    AND metric_column = 'DS_CPUT'
    AND metric_name = 'xa_analytics'
    AND metric_time > SYSDATE - :2
    AND metric_time < SYSDATE
), targets_with_exadata_name AS (
  SELECT DISTINCT
    em_id,
    target_name,
    target_type,
    CONNECT_BY_ROOT target_name AS exadata_name
  FROM
    XAWH_TARGETS
  WHERE
    target_type = 'host'
  CONNECT BY PRIOR target_guid = parent_guid
             AND em_id = :1 START WITH ( parent_guid IS NULL )
)
SELECT
  t.exadata_name "Target",
  TRUNC(AVG(cpu_usage_historical.v),2) "Average CPU Usage"
FROM
  cpu_usage_historical,
  targets_with_exadata_name t
WHERE
    cpu_usage_historical.em_id = t.em_id
  AND cpu_usage_historical.target_name = t.target_name
  AND cpu_usage_historical.target_type = t.target_type
GROUP BY
  t.exadata_name
ORDER BY
  AVG(cpu_usage_historical.v) DESC
FETCH FIRST 10 ROWS ONLY;

Sample Output:

Input parameters provided:

• EM_ID: 19102682514EE3FAAE6DC8AFB460EAC8
• Historical period in days: 365

Output:

Target	Average CPU Usage
DB Machine sample1.example.com	29.08
DB Machine sample2.example.com	24.48
DB Machine sample3.example.com	14.52
DB Machine sample4.example.com	7.57
DB Machine sample5.example.com	3.68

Query 2: Exadata Targets with Under-utilized IOPS

Use this query to get a list of the top 10 under-utilized Oracle Exadata Database Machines, based on IOPS utilization computed for the following metrics:

1. Number of requests to read small blocks per second from a cell disk (CD_IO_RQ_R_SM_SEC)
2. Number of requests to read large blocks per second from a cell disk (CD_IO_RQ_R_LG_SEC)
3. Number of requests to write small blocks per second to a cell disk (CD_IO_RQ_W_SM_SEC)
4. Number of requests to write large blocks per second to a cell disk (CD_IO_RQ_W_LG_SEC)

The query takes the EM_ID and historic period in days as parameters and returns a list of up to 10 Oracle Exadata Database Machines sorted in ascending order on the basis of least IOPS usage over the selected historical period.

Input Parameters:

• EM_ID
• Historical time period in days, for example, 365 for 1 year

WITH iops_usage_historical AS (
  SELECT
    em_id,
    target_name,
    target_type,
    mean_value v,
    CASE
      WHEN KEY_PART_1 LIKE 'CD%' THEN
        'CD'
      ELSE
        'FD'
    END        disk_type
  FROM
    XAWH_DAILY_METRIC_DATA
  WHERE
      em_id = :1
    AND target_type = 'oracle_exadata'
    AND metric_column IN ( 'CD_IO_RQ_R_SM_SEC', 'CD_IO_RQ_R_LG_SEC', 'CD_IO_RQ_W_SM_SEC', 'CD_IO_RQ_W_LG_SEC' )
    AND metric_name = 'xa_analytics'
    AND metric_time > SYSDATE - :2
    AND metric_time < SYSDATE
), targets_with_exadata_name AS (
  SELECT DISTINCT
    em_id,
    target_name,
    target_type,
    CONNECT_BY_ROOT target_name AS exadata_name
  FROM
    XAWH_TARGETS
  WHERE
    target_type = 'oracle_exadata'
  CONNECT BY PRIOR target_guid = parent_guid
             AND em_id = :1 START WITH ( parent_guid IS NULL )
)
SELECT
  t.exadata_name "Target",
  t.target_name "Oracle Storage Server",
  disk_type "Disk Type",
  SUM(v) "Total IOPS"
FROM
  iops_usage_historical,
  targets_with_exadata_name t
WHERE
    iops_usage_historical.em_id = t.em_id
  AND iops_usage_historical.target_name = t.target_name
  AND iops_usage_historical.target_type = t.target_type
GROUP BY
  t.exadata_name,
  t.target_name,
  disk_type
ORDER BY
  AVG(iops_usage_historical.v) ASC
FETCH FIRST 10 ROWS ONLY;

Sample Output:

Input parameters provided:

• EM_ID: 19102682514EE3FAAE6DC8AFB460EAC8
• Historical period in days: 365

Output:

Target	Oracle Storage Server	Disk Type	Total IOPS
DB Machine sample4.example.com	sample4adm09.example.com	FD	14368.85
DB Machine sample4.example.com	sample4adm11.example.com	FD	14605.37
DB Machine sample4.example.com	sample4adm10.example.com	FD	15722.89
DB Machine sample3.example.com	sample3adm02.example.com	CD	8364.61
DB Machine sample3.example.com	sample3adm01.example.com	CD	9614.26
DB Machine sample1.example.com	sample1adm03.example.com	CD	26910.9
DB Machine sample3.example.com	sample3adm02.example.com	FD	27305.82
DB Machine sample2.example.com	sample2adm06.example.com	FD	222255.41
DB Machine sample2.example.com	sample2adm04.example.com	FD	222294.83
DB Machine sample2.example.com	sample2adm05.example.com	FD	222339.38

Configuring Purge Settings for Old Metric Data

The volume of fine-grained data and rolled-up statistics stored in the Exadata Warehouse grows over time. You can configure settings to periodically delete old data from the Exadata Warehouse.

Data retention settings:

Data Type	Default Value (days)	Minimum Value (days)
Fine-grained metric data	395 (13 months)	30 (1 month)
Hourly roll-up metric data	1460 (4 years)	395 (13 months)
Daily roll-up metric data	2555 (7 years)	395 (13 months)
Diagnostic data	395 (13 months)	60 (2 months)

Customize Data Retention Settings for Exadata Warehouse

To override the default retention periods and to view the latest retention settings for the Exadata Warehouse tables, use the EM Job type Exadata Warehouse Auto Purge Settings. In the job, you can specify the value for the retention period for fine-grained data, hourly roll-up data, daily roll-up data, and diagnostic data. To create a new Job of the type:

1. In Enterprise Manager Cloud Control, click Enterprise, then Job, then click Activity.

2. On the Job Activity page, select the job type Exadata Warehouse Auto Purge Settings from the Create Job menu and click Go.

3. In the General tab, provide a name for the job and add the individual targets or one composite target such as a Group.

4. In the Parameters tab, provide value for the retention period for fine-grained data, hourly roll-up data, daily roll-up data, and diagnostic data, all specified in days.

5. In the Credentials tab, select an appropriate option for credentials.

6. In the Schedule tab, schedule the job.

7. In the Access tab, define or modify the access you want other users to have to this job.

8. Click Submit.

Auto-Purge Job Details

After the auto-purge job is created to override the default purge settings, you can view the job or modify the auto-purge settings:

If the job is run without any change to the parameters, the current retention settings are displayed in the job output.