Customize Exadata Warehouse Settings

Define Data Extraction Location

You can specify a custom staging directory for fine-grained data collected by Enterprise Manager agents for Exadata Warehouse.

By default, Enterprise Manager agents temporarily store fine-grained data collected from Exadata compute nodes and storage servers in the agent stage directory. For example, if the agent stage directory is $agentHome/agent_inst, the files collected for Exadata Warehouse are temporarily stored in the designated folder under $agentHome/agent_inst and later transferred to Exadata Warehouse.

To specify a different data extraction location:

Log in to the Enterprise Manager repository:
```
sqlplus sysman/password@repos
```
Specify the staging directory using one of the following options. A staging directory specified for a specific target takes precedence over the general staging directory setting for which no target name is specified.
- To specify a nondefault staging directory for all Exadata Database Machine agents, run the following commands:
```
insert into xawh_config_props (property_name, property_value)
values ('staging_dir', '/user/my_custom_dir');
commit;
```
- To specify a nondefault staging directory for a specific target agent, run the following commands:
```
insert into xawh_config_props
  (property_name, property_value, target_name, target_type)
values
  ('staging_dir', '/user/my_custom_dir', 'agent_host_name_1', 'host');
commit;
```

Define Maximum Space for Data Extraction

You can specify the maximum percentage of staging directory space that Enterprise Manager agents can use for Exadata Warehouse data extraction.

By default, the space limit is set to 80% of the staging directory. You can change the percentage of available staging directory space that can be used for data extraction.

Log in to the Enterprise Manager repository:
```
sqlplus sysman/password@repos
```
Update the staging directory space limit percentage by setting the staging_dir_limit_pct property. For example, to set the limit to 70.5%, run the following commands:
```
update xawh_config_props
set property_value='70.5'
where property_name='staging_dir_limit_pct';
commit;
```

Customize Forecast Settings

You can customize the data granularity, historical data set size, and forecast period used by Exadata Warehouse to generate forecasts.

By default, Exadata Warehouse generates forecasts for the next 90 days using 270 days of rolled-up daily metric data. The default forecast settings are:

Data type (forecast granularity): Daily
Historical data set size: 270 days
Forecast period: 90 days

If sufficient historical data is not available, Exadata Warehouse adjusts the forecast period based on the available data. A minimum of 30 days of historical data is required to generate a forecast.

You can override the default settings to change the data granularity, forecast period, or historical data set size.

Log in to the Enterprise Manager repository:
```
sqlplus sysman/password@repos
```

Update the Exadata Warehouse forecast settings by running the following script:

SET SERVEROUTPUT ON
DECLARE
  l_sql VARCHAR2(1000);

  PROCEDURE INSERT_OR_UPDATE_PROP(l_prop VARCHAR2, l_value VARCHAR2) IS
    l_insert_sql VARCHAR2(1000) :=
      ' MERGE INTO XAWH_CONFIG_PROPS_E USING (SELECT 1 FROM DUAL) ' ||
      ' on ( PROPERTY_NAME = ''' || l_prop || ''')' ||
      ' WHEN NOT MATCHED THEN INSERT (PROPERTY_NAME, PROPERTY_VALUE) VALUES (''' ||
      l_prop || ''',''' || l_value || ''' )' ||
      ' WHEN MATCHED THEN UPDATE SET PROPERTY_VALUE= ''' || l_value || '''';
  BEGIN
    dbms_output.put_line('sql ' || l_insert_sql);
    EXECUTE IMMEDIATE l_insert_sql;
  EXCEPTION
    WHEN others THEN
      dbms_output.put_line(
        'Error encountered when inserting property ' || l_prop ||
        '. ErrMsg: ' || substr(SQLERRM,1,256));
  END;

BEGIN
  INSERT_OR_UPDATE_PROP('forecast_steps', '168');
  INSERT_OR_UPDATE_PROP('forecast_model_history', '840');
  INSERT_OR_UPDATE_PROP('forecast_model_minimum_history', '168');
  INSERT_OR_UPDATE_PROP('forecast_granularity', 'HOURLY');
  COMMIT;
END;
/
SHOW ERRORS;
disconnect;
exit;

Collect Additional Metrics

You can configure Exadata Warehouse to collect additional metrics from Exadata Database Machine targets.

By default, Exadata Warehouse collects a curated set of metrics. To collect additional metrics, identify the internal metric names and add them to the XAWH_METRIC_LIST table in the Enterprise Manager repository.

Log in to the Enterprise Manager repository:
```
sqlplus sysman/password@repos
```
Identify the internal names of the additional metrics that you want to collect.
For example, to collect additional swap usage metrics from Exadata compute nodes, identify the internal metric names, such as DS_SWAP_USAGE, DS_SWAP_OUT_BY_SEC, and DS_SWAP_IN_BY_SEC. For information on the available database server metrics and their internal names, see Database Server Metrics in the Oracle Exadata Database Machine Maintenance Guide.
Add the metrics to the XAWH_METRIC_LIST table. Specify multiple metric names by separating them with a colon (:). Continuing with the previous example, run the following command to add the swap usage metrics to the XAWH_METRIC_LIST table for all Exadata compute node targets:
```
INSERT INTO XAWH_METRIC_LIST
  (TARGET_NAME, TARGET_TYPE, METRIC_NAME, METRIC_COLUMNS)
VALUES
  ('all', 'host', 'fine_grained_metrics',
   'DS_SWAP_USAGE:DS_SWAP_OUT_BY_SEC:DS_SWAP_IN_BY_SEC');
```
For TARGET_NAME, specify one of the following values:
- all - Applies to all targets of the specified target type.
- A specific target name - Applies only to the specified target.
To collect additional metrics from another target type, identify the required metric names and add them to XAWH_METRIC_LIST.
For example, to collect Cell Disk I/O metrics from Exadata Storage Servers, run the following command to add the CD_IO_BY_R_LG and CD_IO_BY_R_SM metrics to the XAWH_METRIC_LIST table for all Exadata Storage Server targets:
```
INSERT INTO XAWH_METRIC_LIST
  (TARGET_NAME, TARGET_TYPE, METRIC_NAME, METRIC_COLUMNS)
VALUES
  ('all', 'oracle_exadata', 'fine_grained_metrics',
   'CD_IO_BY_R_LG:CD_IO_BY_R_SM');
```
For information on available Cell Disk I/O metrics and their internal names, see Monitoring Cell Disk I/O Using Exadata Metrics in the Oracle Exadata Database Machine System Software User's Guide.

Configure Purge Settings for Old Metric Data

You can configure data retention periods to periodically delete old data from Exadata Warehouse.

The volume of fine-grained data and rolled-up statistics stored in Exadata Warehouse grows over time. You can override the default retention periods by using the Exadata Warehouse Auto Purge Settings job.

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)

To configure data retention and purge settings for Exadata Warehouse:

In Enterprise Manager, click Enterprise, then Job, and then Activity.
On the Job Activity page, select Exadata Warehouse Auto Purge Settings from the Create Job menu, and then click Go.
Specify the job settings:
- On the General tab, provide a name for the job and add the individual targets or a composite target such as a group.
- On the Parameters tab, specify the retention period, in days, for fine-grained data, hourly roll-up data, daily roll-up data, and diagnostic data.
- On the Credentials tab, select an appropriate credential option.
- On the Schedule tab, schedule the job.
- On the Access tab, define or modify the access that other users have to the job.
Click Submit to create the job.

After the auto-purge job is created, you can view the job or modify the purge settings.