1059-1061 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 1059

Table 45.6. User -exit return values.

Return Type	Description
Success	No errors were encountered . SQL*Forms can continue to the next step.
Failure	The user exit detected an error. A message is displayed.
Fatal error	The user exit detected an error that will not let the process continue.

To incorporate a user exit into a form, follow these steps:

Write the user exit in a supported host language.
Precompile the source code.
Compile the modified source code.
Use the GENXTB utility to add an entry to the IAP program table IAPXTB in the module IAPXIT. (IAP is the component of SQL*Forms that runs a form.)
Create a new IAP by linking the standard IAP modules, the modified IAPXIT module, and the new user-exit module.
In the form, define a trigger step to call the user exit.
Instruct operators to use the new IAP when running the form.

Utilizing dynamic SQL or user exits can create problems with performance, and if your program is in a high-demand system, this might be undesirable. The next section of this chapter discusses how to tune your SQL to improve performance.

Performance Tuning

When you develop applications with embedded SQL, performance can be a major issue, depending on the type of platform you use. This section provides easy-to-apply methods for improving the performance of your applications. It looks at what causes poor performance and how performance can be improved.

NOTE

Refer to Chapter 23, "Performance Tuning and Optimization," for more information about performance tuning.

Poor Performance

One of the first causes of poor performance is high Oracle communication overhead. Oracle processes each SQL statement one at a time, which results in numerous calls to Oracle. If you

Page 1060

are operating in a network environment, each call creates additional traffic on the network. The more traffic you have, the slower the performance will become.

The second cause of poor performance is inefficient SQL statements. Just because SQL statements can be written in several different ways and still get the same results, this does not mean that every statement runs efficiently . In some cases, full table scans occur (which is time- consuming if the table is large); in other cases, using indexes greatly speeds up the search.

The third cause of poor performance is managing cursors inefficiently. The result of not managing cursors correctly is additional parsing and binding, which adds noticeable processing overhead for Oracle.

You can address these problems by reducing Oracle communication overhead or reducing processing overhead. The next section provides methods that will help you reduce overhead and improve performance.

Improving Performance

Improving performance can make a dramatic difference in the way your application functions under normal or high usage. You should always consider two areas when writing a SQL statement: Oracle communications and processing overhead.

Reducing Oracle Communication Overhead

You can use two methods to reduce Oracle communication overhead: host arrays and PL/SQL blocks.

Using host arrays can dramatically boost your application's performance. You can issue one SQL statement to manipulate numerous rows, instead of issuing a SQL statement for each row. For example, if you want to update 1,200 student grades, you can issue one SQL statement with a host array instead of 1,200 statements with a host variable. For more information, see the section "Host Arrays," earlier in this chapter.

The second way to reduce Oracle communication overhead is to use embedded PL/SQL. If your application is database intensive , you can use PL/SQL blocks to group SQL statements together and then send the block to Oracle for processing.

After reducing the Oracle communication overhead, your next step is to reduce processing overhead.

Reducing Processing Overhead

To reduce processing overhead, you should analyze your SQL statement to ensure it uses the appropriate indexes and row-locking properly, and it manages cursors effectively. To ensure that indexes are used properly, Oracle provides tools that will help you identify problem areas.

Page 1061

The trace facility in conjunction with the EXPLAIN PLAN statement generates statistics that enable you to identify which SQL statements take a lot of time to execute. This explain plan describes what database operations Oracle executes to complete processing the SQL statement that you write. One of the most common problems with SQL statements is that full table scans are done instead of using indexes. The explain plan indicates whether full table scans are done; from this, you can alter the SQL statements to use indexes.

Another area where you can improve performance is how the database locks data. To increase performance, you want to lock only at the row level. This lets many users (instead of just one) access the table. Applications that perform online transactions can drastically benefit from row-locking versus table-locking. The default value is different depending on what version of Oracle you use. In Oracle version 6, row-locking is the default.

Managing cursors can create an enormous amount of processing overhead. The easiest way to manage cursors is to declare them explicitly. This gives you the flexibility to control them as you need resources. Remember that you need to PREPARE, DECLARE, OPEN, and CLOSE explicit cursors in dynamic SQL ” especially with methods three and four. After a cursor is PREPAREd (which does the parsing), it can be used multiple times until it is CLOSEd. This can drastically reduce the parsing and binding that is done with each cursor.

Now that you have stepped through each part of creating an embedded SQL host program, you should stay current on what new features the precompilers offer. Oracle makes an effort to improve its tools with each step; as a programmer, you should capitalize on these features.

New Features in Version 1.8

The new features in version 1.8 precompilers are as follows :

UNSAFE_NULL option: With precompiler releases 1.5, 1.6, and 1.7 indicator variables were required. Otherwise, an ORA-1405 error was returned when a field had a NULL value. Using version 1.8 with options MODE=ORACLE and DBMS=V7, you can disable the ORA-1405 error by specifying UNSAFE_NULL=YES.
Using DBMS=V6: Applications precompiled with DBMS=V6 are backward compatible and unaffected by the ORA-1405 messages.

Oracle Call Interface (OCI)

Oracle Call Interface allows you to control all phases of SQL execution in native 3GL procedure or function calls. It is for developers who prefer a low-level interface with absolute control over SQL and PL/SQL statement processing. It does not require precompiling and the code is not produced by the Oracle precompilers; it is a separate library of linkable objects.

Previous Table of Contents Next