7 Connect and Code for High Performance

Oracle Autonomous Database includes several features that automatically monitor, analyze and optimize the performance of your dedicated Autonomous Transaction Processing database. However, 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.

To ensure optimal performance of your application's use of the database, you need to make sure it:

Oracle provides several tools to help you monitor performance, diagnose performance problems, and tune the performance of your SQL code and the database. See Tools for Monitoring and Tuning Performance.

Connect for High Performance

When making connections to your dedicated Autonomous Transaction Processing database, two factors have great impact on the performance of your application's interaction with the database:

  • Which database service you connect to: connect to the database service that best matches the database operations you are performing. For a list of the database services and their characteristics, see Database Services for Dedicated Autonomous Transaction Processing Databases.

  • Whether you use connection pools: use connection pools to reduce the performance overhead of repeatedly creating and destroying individual connections. For more information, see Use Connection Pools.

Use Connection Pools

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.
  • Controls the amount of resources spent on maintaining connections.
  • Reduces the amount of coding effort required to manually manage connection objects.

Special-Purpose Connection Features

Oracle Net Services (previous called SQL*Net) provides a variety of connection features that improve performance in specific connection scenarios. These features are described in Oracle Database Net Services Administrator's Guide.

Colocation tagging is one such feature that is useful in certain transaction processing applications. If your application repeatedly makes connections to the same database service, colocation tagging permits all such connections to be directed to the same database instance, bypassing the load-balancing processing normally done on the database side of connections. For more information, see COLOCATION_TAG of Client Connections.

Code for High Performance

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 new in Release 19c; for example:

  • Automatic Indexing
  • Automatic resolution of SQL plan regressions
  • Automatic quarantine of runaway SQL statements
  • SQL Plan comparison function

Others such features have been available in Oracle Database and used by developers for years; for example:

  • SQL Plan Management
  • SQL Tuning sets
  • SQL Tuning Advisor
  • SQL Access Advisor

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 Worksheet tool provided by both Oracle SQL Developer Web (which is built into your dedicated Autonomous Transaction Processing database) and Oracle SQL Developer (a free application you install on your development system). For more information about these tools, see Tools for Monitoring and Tuning Performance.

Tools for Monitoring and Tuning Performance

Several situations can give rise to application performance issues: changing workloads, resource limitations on application and database servers, or simply network bottlenecks.

Oracle provides a wide range of tools to help you monitor performance, diagnose performance issues, and tune your application or the database to resolve the issue.

A readily available a feature-rich tool is the Performance Hub, which is available in Oracle SQL Developer Web, Oracle Enterprise Manager Cloud Control and Oracle Management Cloud. For information on using these products with your dedicated database (and so gain access to the Performance Hub), see Connect Oracle Database Tools to the Database.

Two other commonly used tools are the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM). AWR stores performance related statics for an Oracle database, and ADDM is a diagnostic tool that analyzes the AWR data on a regular basis, locates root causes of any performance problems, provides recommendations for correcting the problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem. For instructions on using these tools, as well as detailed information about database performance monitoring and tuning, see Oracle Database Performance Tuning Guide. For a quick introduction to database performance monitoring and tuning, see Oracle Database 2 Day + Performance Tuning Guide.

For a complete listing of the SQL tuning and performance management features of Oracle Autonomous Database, and instructions on how to use them, see Oracle Database SQL Tuning Guide.