6 Managing and Monitoring Performance

This section describes managing and monitoring the performance of Autonomous Transaction Processing.

Use SQL Developer Web to Manage and Monitor Databases

Oracle SQL Developer Web, a browser-based application that provides many database development, management and monitoring features, is built into dedicated Autonomous Transaction Processing databases. Therefore, you can use it without downloading or installing additional software on your system.

Use SQL Developer Web monitoring features, like Performance Hub, to view and analyze performance of your database. To connect to SQL Developer Web, see Connect to Oracle SQL Developer Web in the Database. For information about the wide-ranging features of SQL Developer Web, see Using Oracle SQL Developer Web.

Use Enterprise Manager to Manage and Monitor Databases

You can use Oracle Enterprise Manager to manage and monitor dedicated Autonomous Transaction Processing databases.

Note:

You must be using, at a minimum, Enterprise Manager version 13.3 with EM DB Plugin Bundle Patch 13.3.2.0.190731 to manage and monitor dedicated databases.

Enterprise Manager is Oracle’s management platform, providing a single pane of glass for managing all of your Oracle deployments, whether in your data centers or in Oracle Cloud. Through deep integration with Oracle’s product stack, Enterprise Manager provides market-leading management and automation support for Oracle applications, databases, middleware, hardware and engineered systems.

To use Enterprise Manager to manage and monitor your dedicated database, you perform these high-level tasks:

  1. Configure connectivity between your Enterprise Manager deployment (whether on-premises or on Oracle Cloud Infrastructure Marketplace) and your dedicated Autonomous Transaction Processing database.

  2. Use the Enterprise Manager console, CLI or REST API to discover the dedicated database and add it as a target.

For detailed steps to perform these tasks, see Discover Autonomous Databases in Enterprise Manager Cloud Control Administrator's Guide for Oracle Autonomous Databases.

After performing the discovery tasks, you can use Enterprise Manager to:

  • Monitor the health and performance of your database and perform deep diagnostics on the Performance Hub.
  • Perform database administration tasks such as storage management, and schema management tasks such as creating database objects.

For details about how to use these features, see Monitoring and Administration Tasks in Enterprise Manager Cloud Control Administrator's Guide for Oracle Autonomous Databases.

Manage Database Service Consumer Groups

Oracle Autonomous Transaction Processing uses Oracle Database Resource Manager consumer groups to define different workload characteristics for each of the five pairs of database services described in Predefined Database Service Names for Autonomous Transaction Processing. If the default values used in these consumer groups do not meet a given database's usage model, you can change the values.

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 using the PL/SQL package cs_resource_manager.update_plan_directive. For example, running the following script with the ADMIN user sets CPU/IO shares to 12, 4, 2, 1, and 1 for consumer groups TPURGENT, TP, HIGH, MEDIUM, and LOW respectively. This will allow the consumer group TPURGENT to use 3 times more CPU/IO resources compared to the consumer group TP and 12 times CPU/IO resources compared to the consumer group MEDIUM:

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

Use the PL/SQL procedure cs_resource_manager.update_plan_directive to change the rules Autonomous Transaction Processing uses to terminate SQL statements automatically for a given consumer group.

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

For example, to set a runtime limit of 120 seconds and an IO limit of 1000MB for the HIGH consumer group run the following command when connected to the database as the ADMIN user:

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:

BEGIN
     cs_resource_manager.update_plan_directive(consumer_group => 'HIGH', io_megabytes_limit => null, elapsed_time_limit => null);
END;
/	

Manage Optimizer Statistics

Describes Autonomous Transaction Processing commands to run when you need to gather optimizer statistics or enable optimizer hints.

Managing Optimizer Statistics

Autonomous Transaction Processing 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 Autonomous Transaction Processing and runs in a standard maintenance window.

Note:

For more information on maintenance window times and automatic optimizer statistics collection, see Oracle Database Administrator’s Guide.

For more information on optimizer statistics see Oracle Database SQL Tuning Guide.

Managing Optimizer Hints

Autonomous Transaction Processing honors optimizer hints and PARALLEL hints in SQL statements by default. 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. For example, the following command disables hints in your session:

ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE;

You can also disable PARALLEL hints in your SQL statements by setting OPTIMIZER_IGNORE_PARALLEL_HINTS to TRUE at the session or system level using ALTER SESSION or ALTER SYSTEM.

ALTER SESSION SET OPTIMIZER_IGNORE_PARALLEL_HINTS=TRUE;

Manage Automatic Indexing

The automatic indexing feature in Oracle Database is enabled in Autonomous Transaction Processing.

Creating indexes manually requires deep knowledge of the data model, application, and data distribution. Often DBAs make choices about which indexes to create, and then never revise their choices. As a result, opportunities for improvement are lost, and unnecessary indexes can become a performance liability.

With automatic indexing the database monitors the application workload, creating and maintaining indexes automatically. The indexing feature is implemented as an automatic task that runs at a fixed interval. You can control behavior at the statement level by using the USE_AUTO_INDEXES or NO_USE_AUTO_INDEXES hints. Automatic indexing hints enable the optimizer to use automatic indexes for SQL statements. You can use the DBMS_AUTO_INDEX package to report on the automatic task and to set your preferences.

For more information see Managing Auto Indexes in Oracle Database Administrator’s Guide.