Optimizing Application Design - Oracle Database 11gR2 Performance Tuning - page 36

We will start inspecting the database connection phase, and then move on to general use
of SQL statements in our applications for performance enhancement.

Recipes on useful database objects will follow, and the chapter will close with schema
denormalization and dynamic SQL.

Optimizing connection management

In this recipe, we will see how to manage a database connection in our application, using Java.

Getting ready

To execute the source code we need the java compiler javac and the java runtime
environment installed.

Make sure that jdbc\lib\ojdbc6.jar is in the CLASSPATH environment
variable. The jdbc folder is located under the Oracle home directory.
To set environment variables in Microsoft Windows environments, right-click
on My Computer, select Properties, then navigate to the Advanced button
or link—depending on the OS version—and click on Environment Variables
and find the CLASSPATH environment variable. If you don't find it, click on
the New button and enter the variable name CLASSPATH and variable value
%ORACLE_HOME%\jdbc\lib\ojdbc6.jar . If the variable is already
defined, click on the Edit button and enter the string %ORACLE_HOME%\
jdbc\lib\ojdbc6.jar after the current value.
In Linux environments, export the variable CLASSPATH using the following
command line:
export CLASSPATH=$ORACLE_HOME\jdbc\lib\ojdbc.jar
You can insert this line in your .profile file to avoid manual execution
every time you start up.

We need the example database TESTDB installed, as explained in Chapter 1 in the Exploring
the example database recipe. If we want to use another database, we have to change the
value of the connection string to use the correct value. We will use the HR schema.

How to do it...

The following steps will demonstrate how to optimize connection management:

1. Create a OraclePerformanceTuningCookbook directory and a chapter02

directory inside it.

2. Open your preferred text editor.