Oracle8i SQLJ Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83723-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

Performance Enhancement Features

Oracle SQLJ includes features to enhance your performance by making database access more efficient. These include the following:

For information about underlying Oracle JDBC support for these features, see the Oracle8i JDBC Developer's Guide and Reference.


Note:

Neither Oracle SQLJ nor Oracle JDBC supports batch fetches, which is the fetching of sets of rows into arrays of values.  


In addition to the preceding Oracle SQLJ (and JDBC) performance enhancements, you can employ optimizer hints in the SQL operations within a SQLJ program, as you can in any Oracle SQL operations.

Oracle SQL allows you to tune your SQL statements by using /*+ or --+ comment notation to pass hints to the Oracle SQL optimizer. The SQLJ translator recognizes and supports these optimizer hints, passing them at runtime to the database, as part of your SQL statement.

You can also define cost and selectivity information for a SQLJ stored function, as for any other stored function, using the extensibility features for SQL optimization in Oracle8i. During SQL execution, the optimizer invokes the cost and selectivity methods for the stored function, evaluates alternate strategies for execution, and chooses an efficient execution plan.

For information about the Oracle optimizer, see the Oracle8i SQL Reference.

Note that using Oracle performance extensions in your code requires the following:

(The Oracle SQLJ runtime and an Oracle JDBC driver are required by your application whenever you customize profiles with the Oracle customizer, even if you do not actually use Oracle extensions in your code.)

Row Prefetching

Standard JDBC receives the results of a query one row at a time, with each row requiring a separate round trip to the database. Row prefetching allows you to receive the results more efficiently, in groups of multiple rows each.

To specify the number of rows to prefetch for queries that use a given connection context instance, use the underlying JDBC Connection instance cast to an OracleConnection. Following is an example that sets the prefetch value to 20 for your default connection:

((OracleConnection)DefaultContext.getDefaultContext().getConnection()).setDefaultRowPrefetch(20);
    

Each additional connection context instance you use must be set separately, as desired. For example, if ctx is an instance of a declared connection context class, set its prefetch value as follows:

((OracleConnection)ctx.getConnection()).setDefaultRowPrefetch(20);

There is no maximum row-prefetch value. The default is 10 in JDBC, and this is inherited by SQLJ. This value is effective in typical circumstances, although you might want to increase it if you receive a large number of rows.

See "Prefetch Demo--PrefetchDemo.sqlj" for a sample application showing row prefetching through SQLJ, and update batching through JDBC.

Statement Caching

SQLJ offers a statement caching feature that improves performance by saving executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. When a statement is cached before it is re-executed, the code does not have to be reparsed, the statement object does not have to be recreated, and the parameter size definitions do not have to be recalculated.

Oracle JDBC Support for Statement Caching

Statement caching is a standard SQLJ feature that does not require any particular JDBC driver; however, using a driver that implements the interface sqlj.runtime.profile.ref.ClientDataSupport allows more robust caching. Beginning with release 8.1.6, the Oracle JDBC drivers implement this interface, providing the following features:

When a single cache is used, as is the case with a generic JDBC driver that does not implement ClientDataSupport, a statement executed in one connection can cause a cached statement from another connection to be flushed (if the statement cache size--the maximum number of statements that can be cached--is exceeded).

Oracle Customizer Option for Statement Cache Size

By default, statement caching is enabled in your application when you use the Oracle customizer, which is executed by default when you use the Oracle SQLJ translator.

The default statement cache size is 5, meaning a maximum of five statements is cached per connection. You can alter the statement cache size or disable statement caching (by setting the cache size to 0) through the Oracle customizer stmtcache option. For information, see "Oracle Customizer Statement Cache Size Option (stmtcache)".

If you use multiple connection context classes and, therefore, have multiple profiles, you can set their statement cache sizes individually by running SQLJ (actually, the customizer) separately for each profile.

At runtime, the appropriate SQLJ profile determines the statement cache size for a connection. This would be the profile that corresponds to the first connection context class instantiated for this connection. Its statement cache size setting, if any, is determined according to how you set the Oracle customizer stmtcache option when you customized the profile. The runtime statement cache size for a connection is set when the first statement on that connection is executed.

Statement Caching Limitations

Using a statement cache, even of size 1, will improve the performance of almost any SQLJ application. Be aware of the following, however:

Be careful in choosing an appropriate statement cache size. If it is too small, then the cache might fill up, resulting in statements being flushed before they are re-executed. If it is too large, then database resources or program resources may be exhausted.


Note:

For Oracle applications, the statement cache size plus the maximum number of open JDBC statements in your application (both directly and through SQLJ) should total less than the maximum number of cursors available for a session, because the maximum number of cursors defines the maximum number of statements that can be open simultaneously.  


Using a statement cache generally does not change the execution semantics of an operation itself, although there are some scenarios where it does. For example, if you have a statement that throws an exception when its resources are released, then using a cache would mean that the exception would not be thrown until the connection is closed or the statement is flushed from the cache (which happens when the cache size is exceeded).

Update Batching

Update batching (referred to as batch updates in the Sun Microsystems JDBC 2.0 specification) allows UPDATE, DELETE, and INSERT statements that are batchable and compatible (as discussed below) to be collected into a batch and sent to the database for execution at once, saving round trips to the database. In prior releases, this feature was supported as an Oracle JDBC extension, but not by Oracle SQLJ. As of release 8.1.6, however, this feature is included in the JDBC 2.0 and SQLJ specifications, and is therefore supported by both Oracle JDBC and Oracle SQLJ. Update batching is typically used for an operation that is executed repeatedly within a loop.

In SQLJ, update batching is tied to execution context usage. This feature is enabled or disabled in each execution context, independently of any other execution context, and each execution context instance maintains its own batch.


Note:

To use update batching, you must customize your application with the Oracle customizer.  


Batchable and Compatible Statements

Two criteria determine whether a statement can be added to an existing batch of statements:

Batchable

For Oracle SQLJ as of release 8.1.6, the following kinds of statements are batchable:

Note the following restriction, however:

In future releases or other implementations of SQLJ, additional kinds of statements might be batchable (such as stored procedure calls or DDL statements).

Compatible

In Oracle SQLJ release 8.1.6, only multiple instances of the same statement are compatible. This can occur in one of two circumstances:

In future releases or other implementations of SQLJ, additional kinds of statements might be compatible (such as instances of different statements that have no host expressions).

Enabling and Disabling Update Batching

SQLJ performs update batching separately for each execution context instance. Each one can have update batching enabled independently of your other execution context instances, and each maintains its own batch.

To enable or disable update batching for a particular execution context instance, use the setBatching() method of that execution context instance. This method takes a boolean input, as follows:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...

or:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(false);
...

Update batching is disabled by default.


Note:

The setBatching() method does not affect an existing statement batch. Neither enabling nor disabling update batching causes an existing batch to be executed or canceled.  


Use the isBatching() method of an execution context instance to determine if update batching is enabled for that execution context:

ExecutionContext ec = new ExecutionContext();
...
boolean batchingOn = ec.isBatching();

This does not, however, indicate whether a batch is currently pending.

Explicit and Implicit Batch Execution

You can explicitly execute a pending update batch as desired, but it might also be implicitly executed under certain circumstances.


Note:

It is important to be aware of what happens when an exception occurs in the middle of a batch execution. See "Error Conditions During Batch Execution".  


Explicit

Use the executeBatch() method of the execution context instance to explicitly execute an update batch. This method returns an int array of update counts with meanings as described in "Execution Context Update Counts".

Following is an example of explicitly executing a batch:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...;
for (int i = 0; i < empnos.length; i++)
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
int[] updateCounts = ec.executeBatch();
...


Notes:

If you invoke executeBatch() when the execution context instance has no pending batch, then the method returns null.  


Implicit

When a pending update batch exists, it is implicitly executed in the following circumstances:

Following is an example. First one batch is created and executed implicitly when an unbatchable statement is encountered, then a new batch is created and executed implicitly when a batchable, but incompatible, statement is encountered:

ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
/* Statements in the following loop will be placed in a batch */
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}

/* a SELECT is unbatchable so causes the batch to be executed */
double avg;
#sql [ec] { SELECT avg(sal) INTO :avg FROM emp };

/* Statements in the following loop will be placed in a new batch */
double[] comms = ...;
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp SET comm = :(comms[i]) WHERE empno = :(empnos[i]) };
}

/* the following update is incompatible with the second batch, so causes it to 
be executed */
int smithdeptno = ...;
#sql [ec] { UPDATE emp SET deptno = :smithdeptno WHERE ename = 'Smith' };

To obtain the update count array for a batch executed implicitly, invoke the getBatchUpdateCounts() method of the execution context instance. This returns the update counts for the last batch to be executed successfully in this execution context instance. The following code statement could be inserted after the SELECT and after the last UPDATE:

int[] updateCounts = ec.getBatchUpdateCounts();

The meanings of these update counts are described "Execution Context Update Counts".


Note:

If no update batch has been executed successfully for the execution context instance, then getBatchUpdateCounts() returns null.  


Canceling a Batch

To cancel the batch that is pending in an execution context, use the cancel() method of the execution context instance. You can, for example, cancel a batch that has been executed, but not yet committed, in the event that an exception occurred during batch execution. Following is an example:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...;
for (int i = 0; i < empnos.length; i++)
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}

try 
{ 
   int[] updateCounts = ec.executeBatch();
} catch ( SQLException exception) { ec.cancel(); }
...

When you cancel a batch, the next batchable statement will start a new batch.


Notes:

  • Calling cancel() will also cancel any statement currently executing.

  • Canceling a batch does not disable update batching.

 

Execution Context Update Counts

As of release 8.1.6, the array of update counts returned by the executeBatch() method or the getBatchUpdateCounts() method of an execution context instance does not contain counts of the number of rows updated by the batched statements, but simply values indicating whether each statement was successful. So its functionality differs from that of the single update count returned by the getUpdateCount() method of the execution context instance when batching is not enabled, which is described in "Status Methods".

As statements are batched, and after batch execution, the single update count returned by getUpdateCount() is also affected.

Value Available from getUpdateCount()

In a batch-enabled environment, the value available from the getUpdateCount() method of the execution context instance is modified after each statement is encountered. It will be updated with one of several ExecutionContext class static constant values, as follows:

If you refer to these constants, use the qualified names:

ExecutionContext.NEW_BATCH_COUNT
ExecutionContext.ADD_BATCH_COUNT
ExecutionContext.EXEC_BATCH_COUNT

Values Available from executeBatch() or getBatchUpdateCounts()

After a batch has been executed, either explicitly or implicitly, the array of values returned by executeBatch() or getBatchUpdateCounts() indicates only whether the statements executed successfully. There is an array element for each batched statement. As per the JDBC 2.0 specification, a value of -2 for an array element indicates that the corresponding statement completed successfully, but that the number of rows it affected is unknown.

Checking all the array values after execution of a batch would not be meaningful. As currently implemented, the only useful test of this array would be to verify the number of statements that were in the batch prior to execution, by checking the number of elements in the array after a successful execution (essentially, after a batch execution that does not produce an exception).

Note that the update counts array is not modified as statements are batched, only as the batch is executed.

Setting a Batch Limit

You can specify that each update batch be executed after a predefined number of statements have been batched, before the next statement would be added. Use the setBatchLimit() method of the execution context instance, inputting a positive, non-zero integer as follows:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
ec.setBatchLimit(10);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < 20; i++) 
{
   #sql [ec] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}

This loop is executed 20 times, with the statements being batched and the batch being executed during the 11th time through the loop (before an 11th statement would be added to the batch). Note that the batch would not be executed a second time in the loop, however. When your application exits the loop, the last ten statements would still be in the batch and would not be executed until another statement is encountered or you execute the batch explicitly.

You can use two special static constants of the ExecutionContext class as input to the setBatchLimit() method:

For example:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
ec.setBatchLimit(ExecutionContext.AUTO_BATCH);
...

or:

ec.setBatchLimit(ExecutionContext.UNLIMITED_BATCH);
...

To check the current batch limit, use the getBatchLimit() method of the execution context instance.

Batching Incompatible Statements

If you want to batch a statement that is incompatible with statements in an existing batch without implicitly executing the existing batch, then you will have to use a separate execution context instance. Following is an example:

...
ExecutionContext ec1 = new ExecutionContext();
ec1.setBatching(true);
ExecutionContext ec2 = new ExecutionContext();
ec2.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec1] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
   #sql [ec2] { UPDATE emp2 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
int[] updateCounts1 = ec1.executeBatch();
int[] updateCounts2 = ec2.executeBatch();
...


Note:

This example assumes that the two UPDATE statements are completely independent of each other. Do not batch interdependent statements in different execution contexts because you cannot completely assure the order in which they will be executed.  


An alternative is to use a single execution context and separate loops so that all the EMP1 updates are batched and executed prior to the EMP2 updates:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp2 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
ec.executeBatch();
...

(This example executes the first batch implicitly and the second batch explicitly.)

Using Implicit Execution Contexts for Update Batching

All the update batching examples so far have created and specified explicit execution context instances. This is not necessary, however, given that every connection context instance has an implicit execution context instance. For example, you can access the implicit execution context instance of the default connection as follows:

DefaultContext.getDefaultContext().getExecutionContext().setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
// implicitly execute the batch and commit
#sql { COMMIT };

Or, alternatively, you could execute the batch explicitly:

DefaultContext.getDefaultContext().getExecutionContext().executeBatch();

General Cautions Regarding Update Batching

If you use update batching, especially if you mix statements using an unbatched execution context instance with statements using a batched execution context instance, remember the following points:

Error Conditions During Batch Execution

In the event that a statement causes an exception in the middle of a batch execution, be aware of the following:

When an exception occurs during batch execution under JDBC 2.0, it is typically an instance of the standard java.sql.BatchUpdateException class, a subclass of the java.sql.SQLException class (Under JDK 1.1.x, the Oracle JDBC drivers support update-batching exceptions with the oracle.jdbc2.BatchUpdateException class.)

The BatchUpdateException class has a getUpdateCounts() method that, for batched statements successfully executed before the exception occurred, returns an array of update counts equivalent to what would be returned by the ExecutionContext class executeBatch() or getBatchUpdateCounts() method.

Recursive Call-ins and Update Batching

As discussed in "Recursive SQLJ Calls in the Server", execution of SQLJ stored procedures where one calls the other can result in situations where the two procedures are simultaneously using the same execution context instance. The update-batching flag (set using the setBatching() method of the execution context instance) would behave in the same way as other execution context attributes--regardless of which stored procedure sets it, it would affect the next executable statement in either stored procedure.

For this reason, update batching is automatically disabled in the server whenever a recursive call-in occurs. The pending batch is executed, and no batching occurs in the recursively invoked procedure.

To avoid this behavior, use explicit execution context instances in batch-enabled stored procedures.

Column Definitions

Oracle SQLJ reflects Oracle JDBC support for column type and size definitions. Depending on the driver implementation, which differs somewhat among the different Oracle JDBC drivers, registering column types and sizes can save a trip to the database for each query. In particular, this is true for the Oracle JDBC Thin driver.

Oracle SQLJ Implementation of Column Definitions

If you enable column definitions, Oracle SQLJ takes the following steps to automatically register column types and sizes:

Customizer Options for Column Definitions

To enable column definitions, set customizer options as follows:

For information about these customizer options, see the optcols section under "Overview of Customizer-Specific Options" and the user, password, url, and driver sections under "Overview of Customizer Harness Options".


Note:

Be aware that user, password, URL, and driver settings for customization are not the same as the settings for semantics-checking during translation. They are unrelated.  


Parameter Size Definitions

Oracle JDBC and Oracle SQLJ allow you to optimize JDBC resource allocation by defining parameter sizes--sizes of Java host variables used as any of the following:

Oracle SQLJ Implementation of Parameter Size Definitions

SQLJ implements parameter size definitions through customizer option settings in combination with "hints" embedded in source code comments. You can use these options and hints as follows:

For any given host variable, when parameter size definitions are enabled, resources are allocated according to the source code hint if there is one. If there is no source code hint, then the default size for the corresponding datatype is used if one was specified. If there is no source code hint or appropriate default size, then maximum resources are allocated according to the JDBC implementation.

When your application executes, the SQLJ runtime registers parameter sizes by calling the defineParameterType() and registerOutParameter() methods available in the Oracle JDBC statement classes.


Note:

If you do not enable the parameter definition flag, then parameter size defaults and source code hints will be ignored, and maximum or default resources will be allocated according to the JDBC implementation.  


Customizer Options for Parameter Size Definitions

Use the following customizer options for parameter size definitions:

For information about these options, see the sections under "Overview of Customizer-Specific Options".

Source Code Hints for Parameter Size Definitions

Embed source code hints for parameter size definitions within your SQLJ statements in the following format (you can add white space within the comment, as desired):

/*(size)*/

The size is in bytes. Hints are ignored if the Oracle customizer optparams flag is disabled.

You can override the default parameter size, without specifying a new size (leaving size allocation to the JDBC implementation), as follows:

/*()*/

Here is an example:

byte[] hash;
String name=Tyrone;
String street=2020 Meryl Street;
String city=Wichita;
String state=Kansas;
String zipcode=77777;
#sql hash = { /* (5) */ VALUES (ADDR_HASH(:name /* (20) */, :street /* () */, 
                               :city, :state, :INOUT zipcode /* (10) */ )) };

A hint for a result expression, such as the result expression hash in the example, must be the first item appearing inside the brackets of the SQLJ statement, as shown. Hints for input and output host variables must immediately follow the variables, as shown.

The example sets parameter sizes as follows:



Go to previous page
Go to beginning of chapter
Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index