1. Using Oracle Autonomous JSON Database
  2. Code for High Performance

9 Code for High Performance

Oracle Autonomous JSON Database includes several features that automatically monitor, analyze and optimize the performance of your JSON database.

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 JSON 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 most applications that use JSON documents you use the typical connection service for transaction processing, tp.) For a list of the database services and their characteristics, see Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database in Using Oracle Autonomous Database Serverless.

  • 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

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.

  • Shared Server Configuration is another feature supported by Oracle Autonomous JSON Database for maintaining legacy applications designed without connection pooling. The shared server architecture enables the database server to allow many client processes to share very few server processes. This increases the number of users that can be supported by the application. Using the shared server architecture for such legacy applications enables them to scale up without making any changes to the application itself.

    By default, the shared server mode is disabled for Autonomous JSON Database. To enable it, submit an SR in My Oracle Support requesting support operations to assist you with shared server configuration for the required Exadata Infrastructure OCIDs.

    See also Oracle Database Net Services Administrator's Guide for more detailed information about shared server, including features such as session multiplexing.

    The client that wants to use the shared server configuration must configure (SERVER=shared) in the CONNECT_DATA section of the connect descriptor. For example:
    sales=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(SERVER=shared))

    Tip:

    You can disable Shared Server for a specific Autonomous JSON Database created under a Shared Server enabled Autonomous Container Database by setting its SHARED_SERVERS value to 0. To re-enable Shared Servers for that Autonomous JSON Database, run the ALTER SYSTEM RESET SHARED_SERVERS command.

Parent topic: Code for High Performance

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 Database Actions (which is built into your JSON 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.

Parent topic: Code for High 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 feature-rich tool is the Performance Hub, which is available in each of the following:

You can monitor the performance of SQL statements by choosing SQL Monitoring from the Performance Hub. See Monitor SQL Statements in Using Oracle Autonomous Database Serverless.

To turn monitoring on or off for a given SQL statement add the hint MONITOR or NO MONITOR, respectively, to the statement. See MONITOR and NO_MONITOR Hints in Oracle Database SQL Tuning Guide.

With SODA for Java you can use the same hints to monitor the SQL statements that underlie SODA operations. See the following topics in Oracle Database SODA for Java Developer's Guide:

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 Get Started with Performance Tuning.

For a complete list 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.

Parent topic: Code for High Performance