'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 19 - Improving Throughput with SQL, PL/SQL, and Precompilers

Using Oracle8

Chapter 19

Improving Throughput with SQL, PL/SQL, and Precompilers

Understanding SQL Tuning

Because SQL is the most widely used utility for database queries, having a good handle on how to write efficient queries can give your system a great performance boost. You need to determine which database access method will yield the fastest system performance and use the least amount of system resources.

The true power of SQL
How SQL queries are written have the most impact on the performance of the database. Many times, a small change to a SQL query can make it execute many times more quickly-or slowly.

Just because a SQL script accomplishes the task it was assigned to do doesn't in any way mean that it's a good script. A good SQL script is one that uses all the database features available to accomplish the task as fast as possible with as little system resource use as possible. That's where SQL tuning comes into play-how you can increase system throughput by reducing resource usage and contention.

Defining System Performance

Before discussing how to maximize system performance through tuning, you need to answer the question of what exactly good system performance is. The easiest answer is to get the most amount of processing done in the least amount of elapsed time, but that's not always true.

Good system performance is a balance between effective, efficient application code versus available system resources. By using threads and multiple CPUs, a single database application can use all the resources of an entire system. This results in the application executing very quickly, but other users of the system will most probably experience slow or sluggish response time. You create good system performance by writing efficient code and also by considering how system resources are affected.

When thinking about system performance as it relates to software development with Oracle, what you need to keep in mind is how you structure your queries. It's true that database design and maintenance pretty much determines how a database will perform, but you can use a few tricks as a database administrator that will help your scripts and application programs run faster.

Deciding Which SQL Statements to Tune

Before you can begin tuning SQL statements, you need to decide which statements to tune. Your best bet is to start by using the utilities that come with the Oracle Server: TKPROF, Oracle Trace, and the EXPLAIN PLAN utility. You should also query the V$SORT_USAGE and V$SQLAREA views for more information on which SQL statements are using the most system resources.

As a general rule, you should tune statements that do one of two things: take a very long time to execute or execute fairly quickly but are repeated many times (for example, in a loop). Tuning statements such as these will reap the most benefit in terms of system resources because they're using the most resources in your application. When tuning these statements, you want to reduce the resources the statements require, and, if possible, also reduce the number of times the statement is executed.

SQL Statement Tuning

This section focuses on quick suggestions on how to write the most effective SQL code and to perform the same task your code was doing before more quickly and efficiently.

Use joins effectively
How you order your joins in a SQL statement can affect the performance of the statement. When your SQL statement joins two or more tables, you'll want to list the tables that return the most rows first and have the smaller row selections later. You'll also want to use an index if possible to avoid full table scans.

Take a Different Approach

As stated earlier, SQL is a powerful language, and queries that produce the same result can be written in different ways. You can rewrite a query and then run the EXPLAIN PLAN command against the old and the rewritten query to determine the cost of each approach. In a nutshell, the utility analyzes a SQL statement and places the results in a table, which you query. The default name for this table is PLAN_TABLE, and its fields are as follows:

SQL> describe plan_table
 Name                            Null?    Type
 ------------------------------- -------- ----
 STATEMENT_ID                             VARCHAR2(30)
 TIMESTAMP                                DATE
 REMARKS                                  VARCHAR2(80)
 OPERATION                                VARCHAR2(30)
 OPTIONS                                 VARCHAR2(30)
 OBJECT_NODE                             VARCHAR2(128)
 OBJECT_OWNER                            VARCHAR2(30)
 OBJECT_NAME                             VARCHAR2(30)
 OBJECT_INSTANCE                         NUMBER(38)
 OBJECT_TYPE                             VARCHAR2(30)
 OPTIMIZER                               VARCHAR2(255)
 SEARCH_COLUMNS                          NUMBER
 ID                                      NUMBER(38)
 PARENT_ID                               NUMBER(38)
 POSITION                                NUMBER(38)
 COST                                    NUMBER(38)
 CARDINALITY                             NUMBER(38)
 BYTES                                   NUMBER(38)
 OTHER_TAG                               VARCHAR2(255)
 PARTITION_START                         VARCHAR2(255)
 PARTITION_STOP                          VARCHAR2(255)
 PARTITION_ID                            NUMBER(38)
 OTHER                                   LONG

How to invoke the EXPLAIN PLAN utility,

Avoid Transformation Where Possible

If you can, avoid using the SQL functions that cast column information to a different data type-for example, numerical text information to a NUMBER. Also, avoid character information where you pull out a specific range of characters. When using the WHERE clause, make sure that the comparison is done on two variables of the same data type.

A good example of what to avoid is the case where you're comparing an integer value with a character numerical value. By default, the optimizer will convert the character data to an integer before comparing, which will cause the SQL statement to fail if a row of character data doesn't translate to an integer. To avoid this, you should cast the integer value as a character value by using the to_char function, as in the following WHERE clause:


Declared as a VARCHAR2
Declared as a NUMBER

Keep SQL Statements Single Purpose

A long time ago there was a book out that contained a collection of programs written in a single line of the BASIC programming language. Because BASIC can do multiple things within a single line, it was possible to encapsulate an entire program in the context of one programming line.

Although these programs worked, they were probably some of the worst examples of software design that have ever existed. Remember that when writing your SQL queries.

Although it's possible to have a single SQL statement perform multiple operations, it's not always the best thing when you're working on improving system performance. Keep your queries to the point and single-tracked; watch out for SQL statements that do different things based on the data being selected. These types of operations bypass the optimizer and in many cases don't run as efficiently as they should.

Make Use of Optimizer Hints

By using optimizer hints (such as FULL, ROWID, and HASH), you can steer the optimizer down a particular data access path. Because you know the data and application better than the optimizer does, sometimes you'll override what the optimizer wants to do. By using hints, you can change the way the optimizer accesses data and therefore possibly speed up a query. (You can also slow down a query if it's not done right, so be careful!)

Create Extra Tables for Reference When Necessary

Sometimes you need to use the IN clause to select a group of rows. Suppose you're working at the Internal Revenue Service, and you want to list all the athletes you have in your database. Your query would look something like the following:

Select name, gross, occupation from taxpayer
      occupation in ('BOWLER', 'GOLFER', 'BASEBALL PLAYER',
                     'FOOTBALL PLAYER');

Do you see the problem? You would have a very long list of sports-related occupations to add, and the query would be very long and inefficient. Although there's really no way to tune the query, what you could do is create another table in the database called OCCUPATION_TYPE that would list each occupation name and the type of job that it is. The WHERE clause in your query now becomes

    Occupation_type = 'ATHLETE';
Be careful when using SELECT with IN
Another possible performance killer is the use of a SELECT statement within an IN clause. You want to avoid the situation where SELECT could return hundreds or even thousands of rows, each one of which is processed with the IN clause.

Not only will this run faster, but you won't have to worry about changing your query when a new type of sport gets added.

Combine INSERT and UPDATE operations

This suggestion is very simple to implement and has a good performance gain. When selecting data and updating it, you can combine those operations into a single SQL statement, so that the data being updated is read from the database only once. This reduces the number of calls to the database server, thereby increasing the performance of the query.

Index Tuning

To make the most out of database indexes, you might be adding more indexes or even dropping some. The common thought among software developers is that things will always run faster when using an index. But that's true only most of the time. If there are many indexes on a single set of Oracle data, there comes a point of diminishing returns, and the indexes could actually cause a performance bottleneck. So keep in mind that indexes aren't an answer to poorly written queries-they should be used with efficient code to get the most out of the Oracle Server.

Consider hash clusters when indexing
Hash clusters group data in the tables by applying a hash algorithm to the cluster key value of each table row. Hash clusters are very useful in the situation where tables are accessed with a WHERE clause. Performance can be better than just using an index.

Data Tuning

Believe it or not, how you store the raw data within the database can affect performance. This is particularly true when you're dealing with distributed data and are performing joins over the network. If that's the case, you can minimize processing time by keeping the data local as well by using replication. Also, partitioning the data helps as well by distributing the data across multiple disk drives that can be read in parallel.

Shared SQL

Sometimes you have different users executing the same or similar queries against the database. Each statement is parsed out and uses its own space in the shared pool. If two users submit the exact same query, however, Oracle stores only one copy of the parsed query in the SGA, and that one copy is shared among the processes making that query. This is a performance gain because of the space that this frees up in the SGA-space that can be used for other processing.

Put common SQL into the shared pool
Take your most common SQL and create stored procedures, which are automatically shared when multiple users execute them. Stored procedures also are parsed only once and stored in memory as parsed. Performance is improved through the elimination of reparsing.

Look at the steps that Oracle takes to determine if a query already has an identical copy resident in the shared area:

SQL> select player, position from team;
SQL> Select player, position from team;
Keep SQL text consistent
When Oracle compares the SQL text, to find a match with an existing statement it must match exactly. The case of each character must be the same, and spaces must match exactly as well. If not, Oracle treats the text as two distinct SQL statements.


The benefit of programming standards
If you get your developers together and decide on some common approaches to writing programs that access the Oracle Server, you can take advantage of being able to share SQL code within the SGA. Although it might be beneficial to change applications so that they use the same queries, you could define a few standards the developers would use and if they're submitting the same query. For example, you can define whether to use upper-case or lowercase and how they're spaced. Oracle will detect the similarity and share them because they will match all the criteria for sharing SQL.

The benefit of shared SQL isn't really that two statements are sharing space in the shared pool; the real benefit comes when you have many application users running the same queries. That's why it's important to keep in mind shared SQL when writing your applications. When you get into the hundreds of users and the same SQL statements aren't shared among the users, you'll end up increasing the size of your shared pool unnecessarily.

Sizing the shared pool
Having a large shared pool takes away some of the issues regarding fitting objects into the shared pool. The INIT.ORA parameter SHARED_POOL_SIZE sets the size of the pool. You should make it as large as is reasonable, without making it so big that you waste system memory.

Keeping Shared SQL in the Shared Pool

Now that you've created identical SQL statements and they're being shared, you'll want to take steps to help ensure that they stay in the shared pool. You want to make sure that they're not knocked out of the shared pool because of the LRU (least recently used) algorithm used on the shared pool to keep the pool current. You also want to make sure that a large PL/SQL package being read into the shared pool doesn't overwrite your shared SQL statements. Take a quick look at how to prevent these two things from happening.

How to Handle Large Allocations of Shared Pool

The problem here is when a large PL/SQL script is being loaded for the first time, or that same script was loaded, was knocked out of shared pool because of non-use, and is being reloaded. This has the tendency to cause some of the smaller SQL statements in the shared pool to be knocked out of the shared pool because of the LRU algorithm.

What you can do to prevent this is to use the INIT.ORA parameters to reserve space for large allocations. If this is done, the large allocations will be done in a reserved section of the shared pool and won't overwrite the other statements in the pool. This is done with two initialization parameters: SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_SIZE_MIN_ALLOC

First, set SHARED_POOL_RESERVED_SIZE to reserve a chunk of shared pool memory for large allocations. Next, set SHARED_POOL_ RESERVED_SIZE_MIN_ALLOC to the smallest value you want allocated in the reserved space. This means that any request for shared pool larger than the SHARED_POOL_ RESERVED_SIZE_MIN_ALLOC parameter will be allocated in the reserved memory, thus protecting the smaller statements from being pushed out.

Keeping Objects in the Shared Pool

Even if you use the parameters listed in the previous section, this won't prevent commonly used SQL statements from being aged out of the shared pool. There's something you can do about it, however.

You'll want to check out the package DBMS_SHARED_POOL package. You create this package and package body by executing the DBMSPOOL.SQL and PRVTPOOL.PLB scripts, located in the /rdbms/admin directory under ORACLE_HOME. When you use this package, you can load objects into memory early, before memory fragmentation begins, and they can stay there for the duration.

It's the DBMS_SHARED_POOL package that you use to pin a SQL or PL/SQL area. By pinning (locking in memory) large objects, you increase system performance in two ways:

To pin objects in the shared pool, decide what you want to pin, start up the database, and then run DBMS_SHARED_POOL.KEEP.

Three procedures come with the DBMS_SHARED_POOL package:

Speeding Up Access to the Shared SQL Area

Now that you know how to get your SQL into the shared pool and keep it in memory, look at how you can increase the speed of the shared SQL area of the SGA. An INIT.ORA parameter called CURSOR_SPACE_FOR_TIME can speed up execution calls on systems that have a large shared pool and plenty of free memory. It's a parameter set to TRUE or FALSE, and should be used only if your library cache misses on execution calls whose count is 0.

If you have no library cache misses and you set the CURSOR_SPACE_FOR_TIME parameter to TRUE, you'll see a slight performance improvement on execution calls. The way it works is as follows: When the parameter is set to TRUE, a shared SQL area won't be deallocated until all cursors associated with the shared SQL statement have been closed. This saves some time because Oracle doesn't have to check to see if the statement is still in the shared pool when performing an execution call.

Keep an eye on library cache misses
You don't want to set CURSOR_SPACE_FOR_TIME to TRUE if you're seeing library cache misses. Performance will get worse if you do, and statements may begin failing with out-of-shared-pool errors.

SQL and PL/SQL Parsing Reduction

Another tool you can use to increase throughput of your SQL and PL/SQL objects is to tune the private SQL and PL/SQL areas. You can do this by reducing the number of parse calls made to the database. Again, this tuning technique is for systems with available free memory, because reducing parsing by the means suggested in the following sections will require more memory and a larger shared pool.

How to Identify Unnecessary Parse Calls

You need to find out the number of SQL execution calls being parsed compared with the number of statements being executed. Two Oracle tables can help you in this task: V$SQLAREA and V$SESSTAT.


The V$SQLAREA view contains information on the SQL statements in the shared SQL area. One row in this view constitutes one SQL statement. It provides statistics on SQL statements already in memory, parsed and ready for execution. The V$SQLAREA view contains the following field names:

SQL> describe V$SQLAREA;
 Name                            Null?    Type
 ------------------------------- -------- ----
 SQL_TEXT                                 VARCHAR2(1000)
 SHARABLE_MEM                             NUMBER
 PERSISTENT_MEM                           NUMBER
 RUNTIME_MEM                              NUMBER
 SORTS                                    NUMBER
 VERSION_COUNT                            NUMBER
 LOADED_VERSIONS                          NUMBER
 OPEN_VERSIONS                            NUMBER
 USERS_OPENING                            NUMBER
 EXECUTIONS                               NUMBER
 USERS_EXECUTING                          NUMBER
 LOADS                                    NUMBER
 FIRST_LOAD_TIME                          VARCHAR2(19)
 INVALIDATIONS                            NUMBER
 PARSE_CALLS                              NUMBER
 DISK_READS                               NUMBER
 BUFFER_GETS                              NUMBER
 ROWS_PROCESSED                           NUMBER
 COMMAND_TYPE                             NUMBER
 OPTIMIZER_MODE                           VARCHAR2(25)
 PARSING_USER_ID                          NUMBER
 PARSING_SCHEMA_ID                       NUMBER
 KEPT_VERSIONS                           NUMBER
 ADDRESS                                 RAW(4)
 HASH_VALUE                              NUMBER
 MODULE                                  VARCHAR2(64)
 MODULE_HASH                             NUMBER
 ACTION                                  VARCHAR2(64)
 ACTION_HASH                             NUMBER

For your purposes, in this section you'll be concerned only with the SQL_TEXT, PARSE_CALLS, and EXECUTIONS fields. The SQL_TEXT field contains the actual text of the SQL statement being executed. The PARSE_CALLS field is the number of times the statement has been parsed, and the EXECUTIONS field is the number of times the statement was executed.

Try the following SQL_TEXT query on one of your databases to get the parsing information:

Interpreting parses versus executions
Look carefully at the ratio of parses to executions of the individual SQL statement. If the number or parses for any statement is close to the number of executions, that means you're continually reparsing that statement.


The V$SESSTAT view stores statistics on the individual user sessions. The individual statistics are stored by number, so you need to query the V$STATNAME view with the statistic name to get the corresponding number. In Oracle8, this table contains more than 200 statistic names. For now, however, you'll be concerned only with the PARSE_COUNT and EXECUTE_COUNT statistics.

First, get the statistic numbers for parse and execute counts. Start by executing the following two queries:

     WHERE NAME IN ('parse count (hard)','execute count');

You'll see output such as this:

  2> WHERE NAME IN ('parse count (hard)','execute count');

---------- ------------------------------------------------
       153 parse count (hard)
       154 execute count

From this output you can tell that the parse count statistic is number 153 and the execute count statistic is number 154.

Now look at the V$SESSTAT view, which has only three fields:

SQL> describe v$sesstat;
 Name                            Null?    Type
 ------------------------------- -------- ----
 SID                                      NUMBER
 STATISTIC#                               NUMBER
 VALUE                                    NUMBER

Select the parse and execution statistics for all connected Oracle sessions with the following query:

SQL> select * from v$sesstat
  2  where statistic# in (153,154)
  3  order by sid, statistic#;

This query lists out (by session) the parse count and execution count for each session. You're looking for sessions where the two statistical values are closer rather than further apart. The closer in value the two statistics are, the more potential there is to reduce unnecessary parsing. The output of the query is as follows:

---------- ---------- ----------
         1        153          0
         1        154          0
         2        153          0
         2        154          0
         3        153          0
         3        154          0
         4        153          0
         4        154          0
         5        153          0
         5        154          0
         6        153          5

---------- ---------- ----------
         6        154        46
         7        153         1
         7        154        14
         8        153        52
         8        154       395

16 rows selected.

In the output, look at session ID 8; it had 395 execution calls but only 52 parses. You'll want to find sessions where the parse count is much higher to reduce unnecessary parsing. (This query was run against the test database, which unfortunately doesn't have much of a user load, but this is the query you use to get this information.)

Oracle Performance Pack
A great tool for interpreting data-base statistics is Oracle's Performance Pack, which provides a graphical interface to database statistics such as cache rates, disk I/O, and SQL performance.

Reducing Unnecessary Parsing

The key to eliminating unnecessary parsing is how you handle the cursors. This is especially true when you're using explicit cursors.

When using explicit cursors, be sure to avoid the situation where you open and close cursors in a loop. Moving the open and close statements outside the loop will keep the statement from being reparsed. Your program also should DECLARE the cursor, reOPEN it each time the value of a host variable changes, and CLOSE the cursor only after the SQL statement is no longer required.

Remember also that when you're using MODE=ANSI with the precompilers, a COMMIT command will close a cursor.

The cursor-management functions HOLD_CURSOR, RELEASE CURSOR, and MAXOPENCURSORS give you additional power in how to control the parsing and reparsing of SQL statements. In a nutshell, these commands help you control how Oracle handles the creation and uses cursors. You use these commands to keep cursors to prevent reparsing and to remove cursors to save memory.

Using Array Processing

Using host arrays in a program is useful when you're updating multiple rows of a table. You get a real performance gain if the rows are contiguous.

You define the arrays the same way you would any other programming array. For example, look at an array of size 100 for the three fields of the table TEAM:

int     uniform_no[100];
char    player_name[100][25];
int     position[100];

Oracle8 supports only single-dimensional arrays. The player_name array is single dimensional; the 25 is simply the maximum size of each string. The benefit here comes in how you can update the table after the arrays are populated.

Rather than use a FOR loop to update the table in the following example:

FOR (counter = 0; counter <=100; counter++)

you could pass just the array name to the EXEC statement. Oracle not only updates the table with all the elements in the arrays, but also does it as a single operation.

Handling NULL values in a program
You also can use indicator arrays to assign NULLs to variables in input host arrays, and to detect NULL or truncated values in output host arrays. Check the Oracle documentation for more information on indicator arrays.

In the preceding example, 100 insert statements are executed. Look at the new code:

EXEC SQL INSERT INTO TEAM (uniform_no,player_name,position)
    VALUES(:uniform_no, :player_name, :position);

The loop is no longer necessary, and you have a much nicer looking piece of code.

© Copyright, Macmillan Computer Publishing. All rights reserved.