' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //-->

Using Oracle8

Chapter 19

Improving Throughput with SQL, PL/SQL, and Precompilers

• Understanding SQL Tuning
  • Defining System Performance
  • Deciding Which SQL Statements to Tune
  • SQL Statement Tuning
  • Index Tuning
  • Data Tuning
• Shared SQL
  • Keeping Shared SQL in the Shared Pool
  • Speeding Up Access to the Shared SQL Area
• SQL and PL/SQL Parsing Reduction
  • How to Identify Unnecessary Parse Calls
  • Reducing Unnecessary Parsing
  • Using Array Processing

• Maintain a balance between hardware and software to keep your system fast
• Learn how to write queries so that multiple users share the same space in the SGA
• Remove unnecessary SQL parsing to speed up your system

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.

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.

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

SEE ALSO
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:

WHERE

	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
    where
      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

WHERE
    Occupation_type = 'ATHLETE';

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.

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.

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

• First, Oracle takes the query and compares its hash value to that of all the other statements in the shared pool. If it finds a match, Oracle then compares the text of the query to the text of the other queries in the shared pool. The following group of SQL statements all return the same data, but are handled as completely different by Oracle and don't share space in the SGA:

SQL> SELECT PLAYER, POSITION FROM TEAM;
SQL> SELECT PLAYER, POSITION  FROM TEAM;
SQL> select player, position from team;
SQL> Select player, position from team;

• Oracle then compares the objects referenced in the statement to make sure that they're referencing the same physical objects within the database. Suppose that two users have loaded the demo tables into their personal schemas and are performing the same query on the EMP table. Even though the text is exactly the same, the query is referencing two distinct tables and is treated as two separate queries by the database.
• Lastly, the bind variable names in each statement-if they're used-must match exactly. The following shows two SQL statements that do the same thing but aren't shared in the pool:

SQL> SELECT * FROM TEAM WHERE NUMBER = :PLAYERNUM;
SQL> SELECT * FROM TEAM WHERE NUMBER = :PLAYERNO;

Although the bind variable in the example references the NUMBER field of the TEAM table, it won't be shared in the shared pool because the two queries aren't syntactically the same. Develop a common naming convention for bind variables.

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.

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:

• Response time doesn't slow as the larger objects are read into the shared pool.
• It's less likely that the smaller SQL areas will be aged out to make room for a much larger one.

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:

• DBMS_SHARED_POOL.SIZES displays the objects in the shared pool that are larger than the size passed in as a parameter.
• DBMS_SHARED_POOL.KEEP pins a SQL or PL/SQL area in memory.
• DBMS_SHARED_POOL.UNKEEP marks a pinned object as available to be aged out. It's the opposite of the KEEP function, but doesn't actually remove the pinned object from the shared pool.

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.

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.

V$SQLAREA

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
 SERIALIZABLE_ABORTS                     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:

SQL> SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
  2> FROM V$SQLTEXT;

V$SESSTAT

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:

SQL> SELECT STATISTIC#, NAME FROM V$STATNAME
     WHERE NAME IN ('parse count (hard)','execute count');

You'll see output such as this:

SQL> SELECT STATISTIC#, NAME FROM V$STATNAME
  2> WHERE NAME IN ('parse count (hard)','execute count');

STATISTIC# NAME
---------- ------------------------------------------------
       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:

       SID STATISTIC#      VALUE
---------- ---------- ----------
         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

       SID STATISTIC#      VALUE
---------- ---------- ----------
         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.)

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++)
    EXEC SQL INSERT INTO TEAM
               (uniform_no,player_name,position)
               VALUES(:uniform_no[counter],
               :player_name[counter],
               :position[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.

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.