How your application connects to your database and how you code SQL calls to the database determine the overall performance of your application's transaction processing and reporting operations.

When making connections to your Autonomous Database, you can use connection pools to reduce the performance overhead of repeatedly creating and destroying individual connections. This is another factor that has great impact on the performance of your application's interaction with the database.

Quite often, the use of connection pools is considered only when designing or enhancing an application to provide continuous availability. However, the use of connection pools instead of individual connections can benefit almost every transaction processing application. A connection pool provides the following benefits:

• Reduces the number of times new connection objects are created.
• Promotes connection object reuse.
• Quickens the process of getting a connection.
• Controls the amount of resources spent on maintaining connections.
• Reduces the amount of coding effort required to manually manage connection objects.

Great applications begin with well written SQL. Oracle Autonomous Database provides numerous features that enable you to build high performance applications and validate your SQL and PL/SQL code. Some of these features are listed below:

• Automatic Indexing
• Optimizer Statistics and Hints
• Automatic resolution of SQL plan regressions
• Automatic quarantine of runaway SQL statements
• SQL Plan Management
• SQL Tuning sets
• SQL Trace

As you develop your application, you can quickly learn how these features are affecting the SQL code you write and so improve your code by using the SQL Worksheet provided by both Oracle Database Actions (which is built into your Autonomous Database) and Oracle SQL Developer (a free application you install on your development system).

When an application operation takes longer than expected, getting a trace of all the SQL statements executed as part of this operation with details such as time spent by that SQL statement in the parse, execution, and fetch phases will help you identify and resolve the cause of the performance issue. You can use SQL tracing on an Autonomous Database to achieve this.

SQL tracing is disabled by default in Autonomous Database. You must enable it to start collecting the SQL tracing data. Refer to Use SQL Tracing on Autonomous Database for detailed instructions to enable and use SQL Tracing.

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

Optimizer hints are special comments in a SQL statement that pass instructions to the optimizer. The optimizer uses hints to choose an execution plan for the statement unless prevented by some condition.

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

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Fast ingest optimizes the processing of high-frequency, single-row data inserts into a database. Fast ingest uses the large pool for buffering the inserts before writing them to disk, so as to improve data insert performance.

Autonomous Database includes predefined job_class values to use with Oracle Scheduler. These job classes let you group jobs that share common characteristics and behavior into larger entities so that you can prioritize among these classes by controlling the resources allocated to each class.

With predefined job classes, you can ensure that your critical jobs have priority and enough resources to complete. For example, for a critical project to load a data warehouse, you can combine all the data warehousing jobs into one class and prioritize it over other jobs by allocating a high percentage of the available resources. You can also assign relative priorities to the jobs within a job class.

The predefined job_class values, TPURGENT, TP, HIGH, MEDIUM and LOW map to the corresponding consumer groups. These job classes allow you to specify the consumer group a job runs in with DBMS_SCHEDULER.CREATE_JOB.

The DBMS_SCHEDULER.CREATE_JOB procedure supports PLSQL_BLOCK and STORED_PROCEDURE job types for the job_type parameter in Autonomous Database.

For example: use the following to create a single regular job to run in HIGH consumer group:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
     job_name => 'update_sales',
     job_type => 'STORED_PROCEDURE',
     job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
     start_date => '28-APR-19 07.00.00 PM Australia/Sydney',
     repeat_interval => 'FREQ=DAILY;INTERVAL=2',
     end_date => '20-NOV-19 07.00.00 PM Australia/Sydney',
     auto_drop => FALSE,
     job_class => 'HIGH',
     comments => 'My new job');
END;
/

Notes for Oracle Scheduler:

• To use DBMS_SCHEDULER.CREATE_JOB additional grants for specific roles or privileges might be required. The ADMIN user and users with DWROLE have the required CREATE SESSION and CREATE JOB privileges. If a user does not have DWROLE then grants are required for CREATE SESSION and CREATE JOB privileges.

• The instance_id job attribute is ignored for Oracle Scheduler jobs running on Autonomous Database.

See Scheduling Jobs with Oracle Scheduler for more information on Oracle Scheduler and DBMS_SCHEDULER.CREATE_JOB.

See SET_ATTRIBUTE Procedure for information on job attributes.