This post is part of a series of blog posts on the Oracle Autonomous Database.

Monitoring ADB Performance and Resources

The Overview and Activity tabs in the Service Console provide information about the performance of the service. The Activity tab also shows past and current monitored SQL statements and detailed information about each statement.

• Monitoring Performance from the Service Console – Console Overview
• Storage used: total and used storage capacity of the service. It indicates what percentage of the space is currently in-use.
• CPU utilization (%)
• Running SQL statements: average number of running SQL statements historically. This chart shows hourly data. A data point shows the running SQL statements for that hour
• Number of OCPUs allocated
• SQL statement response time (s)
• Database activity / wait events

The ADB captures performance data for 8 days. This can be modified using: DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

• CPU utilization (%):
  This chart shows the historical CPU utilization of the service:
  
  Auto Scaling Disabled:
  This chart shows hourly data. A data point shows the average CPU utilization for that hour. For example, a data point at 10:00 shows the average CPU utilization for 9:00-10:00. The utilization percentage is reported with respect to the number of CPUs the database is allowed to use which is two times the number of OCPUs. For example, if the database has four (4) OCPUs, the percentage in this graph is based on 8 CPUs.
  
  Auto Scaling Enabled:
  For databases with auto scaling enabled the utilization percentage is reported with respect to the maximum number of CPUs the database is allowed to use, which is six times the number of OCPUs. For example, if the database has four OCPUs with auto scaling enabled the percentage in this graph is based on 24 CPUs.

• Number of OCPUs allocated:
  This chart shows the number of OCPUs allocated:
  
  Auto Scaling Disabled:
  For databases with auto scaling disabled, for each hour the chart shows the number of OCPUs allocated to the database if the database is open for at least some part of the hour.
  
  Auto Scaling Enabled:
  For databases with auto scaling enabled, for each hour the chart shows the average number of OCPUs used during that hour if that value is higher than the number of OCPUs provisioned. If the number of OCPUs used is not higher than the number of OCPUs provisioned, then the chart shows the number of OCPUs allocated for that hour.
  
  Stopped Database:
  If the database was stopped for the full hour the chart shows 0 OCPUs allocated for that hour.

Monitor ADB with Performance Hub

You can view real-time and historical performance data from the Performance Hub. Performance Hub shows Active Session History (ASH) analytics, SQL monitoring and workload information.

The Performance Hub page has the following sections:

• The time selector.
• The Reports drop-down list, containing the option to create and download an AWR (Automatic Workload Repository) report.
• The tabbed data area, with the tabs ASH Analytics, SQL Monitoring and Workload.

Monitor Performance with Autonomous Database Metrics

You can monitor the health, capacity, and performance of your Autonomous Transaction Processing databases with metrics, alarms, and notifications. You can use Oracle Cloud Infrastructure console or Monitoring APIs to view metrics.

To view metrics for an Autonomous Database instance:

On the Details page, under Resources, click Metrics.

There is a chart for each metric. In each chart you can select the Interval and Statistic or use the default values.

To create an alarm on a metric, click Options and select Create an Alarm on this Query.

To view Metrics for Autonomous Databases in a Compartment:

Monitoring > Service Metrics.

On the Service Metrics page, under Compartment select your compartment.
On the Service Metrics page, under Metric Namespace select oci_autonomous_database.
If there are multiple Autonomous Databases in the compartment you can show metrics aggregated across the Autonomous Databases by selecting Aggregate Metric Streams.

Managing Priorities

The priority of user requests in Autonomous Transaction Processing is determined by the database service the user is connected with. For example, a user connecting with the atp1_tp service uses the consumer group TP.

Note: After connecting to the database using one service, do not attempt to manually switch that connection to a different service by simply changing the consumer group of the connection. When you connect using a service, Autonomous Transaction Processing performs more actions to configure the connection than just setting its consumer group.

Autonomous Transaction Processing includes predefined job_class values to use with Oracle Scheduler.

Manage CPU/IO Shares

Autonomous Transaction Processing comes with predefined CPU/IO shares assigned to different consumer groups. You can modify these predefined CPU/IO shares if your workload requires different CPU/IO resource allocations.

By default, the CPU/IO shares assigned to the consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW are 12, 8, 4, 2, and 1, respectively. The shares determine how much CPU/IO resources a consumer group can use with respect to the other consumer groups. With the default settings the consumer group TPURGENT will be able to use 12 times more CPU/IO resources compared to LOW, when needed. The consumer group TP will be able to use 4 times more CPU/IO resources compared to MEDIUM, when needed.

You can set CPU/IO shares from the service console or using the PL/SQL package cs_resource_manager.update_plan_directive.

Follow these steps to set CPU/IO shares from the service console:

From the Autonomous Transaction Processing details page, click Service Console.
On the Service Console click Administration.
Click Set Resource Management Rules.
Select CPU/IO shares to set CPU/IO share values for consumer groups.
Set the CPU/IO share values.
Click Save changes.

BEGIN
cs_resource_manager.update_plan_directive(consumer_group => ‘TPURGENT’, shares => 12);
cs_resource_manager.update_plan_directive(consumer_group => ‘TP’, shares => 4);
cs_resource_manager.update_plan_directive(consumer_group => ‘HIGH’, shares => 2);
cs_resource_manager.update_plan_directive(consumer_group => ‘MEDIUM’, shares => 1);
cs_resource_manager.update_plan_directive(consumer_group => ‘LOW’, shares => 1);
END;
/

Manage Runaway SQL Statements

Specifies how you configure Autonomous Transaction Processing to terminate SQL statements automatically based on their runtime or the amount of IO they are doing.

You can set rules from the service console or using the PL/SQL package cs_resource_manager.

Follow these steps to set rules from the service console:

From the Autonomous Database details page, click Service Console.
On the Service Console click Administration.
Click Set Resource Management Rules.
Select the Run-away criteria tab to set rules for consumer groups.
Select the Consumer group: HIGH, MEDIUM, LOW, TP, or TPURGENT.
Set runaway criteria values:
– Query run time (seconds)
– Amount of IO (MB)
Click Save changes.

When a SQL statement in the specified consumer runs more than the specified runtime limit or does more IO than the specified amount, then the SQL statement will be terminated.

BEGIN
cs_resource_manager.update_plan_directive(consumer_group => ‘HIGH’, io_megabytes_limit => 1000, elapsed_time_limit => 120);
END;
/

To reset the values and lift the limits, you can set the values to null.

Manage Optimizer Statistics

The Autonomous Database gathers optimizer statistics automatically so that you do not need to perform this task manually and this helps to ensure your statistics are current. Automatic statistics gathering is enabled in both Autonomous Transaction Processing and Autonomous Data Warehouse and runs in a standard maintenance window.

Autonomous Transaction Processing honors optimizer hints and PARALLEL hints in SQL statements by default. Autonomous Data Warehouse does not. You can disable optimizer hints by setting the parameter OPTIMIZER_IGNORE_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM.

Manage Automatic Indexing

Automatic indexing automates the index management tasks in Autonomous Database. Auto Indexing is disabled by default in Autonomous Database.

Creating indexes manually requires deep knowledge of the data model, application, and data distribution. In the past, DBAs were responsible for making choices about which indexes to create, and then sometimes the DBAs did not revise their choices or maintain indexes as the conditions changed. As a result, opportunities for improvement were lost, and use of unnecessary indexes could be a performance liability. The automatic indexing feature in Autonomous Database monitors the application workload and creates and maintains indexes automatically.

To enable automatic indexing: EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

This enables automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements.

When automatic indexing is enabled, index compression for auto indexes is enabled by default.

To disable automatic indexing: EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’OFF’); — (existing auto indexes remain enabled)

Rotate Wallets for Autonomous Database

Wallet rotation lets you invalidate existing client certification keys for a database instance or for all Autonomous Database instances in a region.

You may want to rotate wallets for any of the following reasons:

• If your organization’s policies require regular client certification key rotation.
• When a client certification key or a set of keys is suspected to be compromised.

There are two options for client certification key rotation:

• Per-database with Instance Wallet selected:
  –> For the database whose certification key is rotated, any existing database specific instance wallets will be void. After you rotate a wallet you have to download a new wallet to connect to the database.
  
  –> Regional wallets containing all database certification keys continue to work.
  
  –> All user sessions are terminated for the database whose wallet is rotated. User session termination begins after wallet rotation completes, however this process does not happen immediately.

• Regional level with Regional Wallet selected:
  –> For the region whose certification key is rotated, both regional and database specific instance wallets will be void. After you rotate a wallet you have to download new regional or instance wallets to connect to any database in the region.
  
  –> All user sessions are terminated for the databases in the region whose wallet is rotated. User session termination begins after wallet rotation completes, however this process does not happen immediately.

Please note: If you want to terminate all connections immediately after the wallet rotation completes, Oracle recommends that you stop and then start your instance. This provides the highest level of security for your database.

Also, Oracle recommends you provide a database-specific instance wallet, with Wallet Type set to Instance Wallet when you use Download Wallet, to end users and for application use whenever possible. Regional wallets should only be used for administrative purposes that require potential access to all Autonomous Databases within a region.

You can also use the Autonomous Database API to rotate wallets using UpdateAutonomousDatabaseRegionalWallet and UpdateAutonomousDatabaseWallet.