This section describes managing and monitoring the performance of Autonomous Transaction Processing.
Monitor Database Performance
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.
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.
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
TRUE at the session or system level using
ALTER SESSION or
ALTER SYSTEM. For example, the following command disables hints in your
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE;
You can also disable
PARALLEL hints in your SQL statements by setting
TRUE at the session or system
ALTER SESSION or
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
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.