You can tune for application performance at many levels, including the operating system, the database, and the code within the application itself. A major problem for DBAs who must work with third-party applications connected to Oracle databases is that such applications do not allow you to alter the SQL source code used in the application. Also, the application may generate ad hoc SQL statements, further complicating the tuning picture.

Although the most significant performance gains you can make to an application come from tuning application design and implementation, you can also tune the database to enhance performance. In this article, I provide some DBA tuning options for when application modification is not an option.

DB Block Buffers

Before Oracle9i Database, the DB_BLOCK_BUFFERS parameter value sized the memory for data and index storage. This value, combined with the DB_BLOCK_SIZE value, allowed DBAs to specify how big a chunk of memory to allocate for use with database objects. The SGA_MAX_SIZE and the various configuration parameters for DB_CACHE_SIZE, DB_Xk_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and other memory objects give the DBA better control of System Global Area (SGA) component sizing. In Oracle9i Database, many parameters are dynamic and can be reset with the database in operation.

Also before Oracle9i Database, the database hit ratio was a pivotal piece of information in tuning the values for DB_BLOCK_BUFFERS. But this hit ratio could be misleading, so it is better to look at actual block usage, using the V$BH and X$BH views. In Oracle9i Database, the V$DB_CACHE_ADVICE view and DB_CACHE_ADVICE initialization parameter provide a touch point for deciding how many buffers to add, or, conversely, how many to take away. Listing 1 shows a simple query against the X$BH view. Note that you must run this query as the SYS user.

If, after running at load for several hours, the SELECT statement in Listing 1 shows that fewer than 10 to 20 percent of the buffers in the database-block buffer cache are free, then increase the size of the DB_BLOCK_BUFFERS (for pre-Oracle9i databases) or the DB_CACHE_SIZE (Oracle9i Database) parameter. Do not explicitly specify the DB_BLOCK_BUFFERS parameter in Oracle9i Database, or you will eliminate the ability of the database to dynamically reset buffer sizes.

In Oracle9i Database, the maximum size the SGA can reach is determined by the SGA_MAX_SIZE parameter value. If you allow Oracle9i Database to calculate the SGA_MAX_SIZE parameter value, you can dynamically allocate memory up to your physical limit. If you set the value explicitly, Oracle9i Database uses it as a hard limit.

Shared Pool

The shared pool holds all referenced data dictionary definitions, all executed stored-object code (views, packages, procedures, triggers, functions, etc.), and all SQL code issued. The shared pool includes the library cache, the dictionary cache, latch structures, and message pools.

When it is set too high, the shared-pool value can limit performance.

The shared pool breaks the SQL for stored objects down into small chunks that it can store however it wishes. There is, however, only a fixed number of latches (a maximum of eight) on releases before Oracle9i Database, and if the number of SQL chunks being managed exceeds certain thresholds, performance suffers. Generally speaking, it is a good practice to limit the total number of SQL areas in the V$SQLAREA view to fewer than 5,000. (This maps into more than 130,000 SQL chunks stored in the X$KSMSP internal structure.) To determine the total usage of the shared pool, use a SELECT statement against the V$SGASTAT view, as shown in Listing 2.

Note that if the DBA has specified the size of the shared pool using the K or M shorthand, however, you must create a function that converts the V$PARAMETER value into an actual number, as shown in Listing 3. The translate_param function from Listing 3 is used in the SELECT statement in Listing 2.

You also need to pay attention to how the SQL in the shared pool is being used. A shared pool with 150 megabytes of sharable code is a much different beast than one with 150 megabytes of nonsharable code. Third-party applications are notorious for not using bind variables and thus not producing sharable code.

Look at the executions column of the V$SQLAREA view to see whether code is being reused. I suggest you use the code in Listing 4 as a template to create a view to make finding reused code easier. The code in boldface sets the cardinality of the executions and determines reusability. If your application issues SQL statements that summarize data sets based on discrete time intervals such as monthly, weekly, or quarterly, then you may need to change the cardinality to look for greater than 12, 52, or 4 executions, depending on the intervals.

After creating the view in Listing 4, create a SELECT statement similar to the following example:

SELECT 
	a.users, a.garbage, b.good,(b.good/
   (b.good+a.garbage))*100 good_percent
FROM 
	sql_garbage a, sql_garbage b
WHERE 
	a.users=b.users
AND 
	a.garbage is not null 
AND 
	b.good is not null
/


The SELECT statement produces a report showing code-reuse characteristics of your users. 

Reusable packages, procedures, functions, triggers, cursors, and sequences should all be 'pinned' in the pool. (Pinned code is also called 'kept code' and is not subject to the Least Recently Used (LRU) aging algorithms that mark code for elimination from the pool as space is required for new code.) You should size the shared pool to hold the pinned code, plus a float of 50 to 150 megabytes above the requirements for pinned code. If you still see a gradual performance decrease as the shared pool fills, schedule an automated flushing routine using the Oracle job queues to periodically check the amount of fill in the pool, pin reusable code, and flush. Use the ALTER SYSTEM FLUSH SHARED_POOL command to flush the pool.

Use the DBMS_SHARED_POOL package to pin the appropriate objects in the pool. Use the DBMSPOOL.SQL script (located in ORACLE_HOME/rdbms/admin or its equivalent on your operating system) to create the DBMS_SHARED_POOL package. For releases before Oracle8i Release 3 (8.1.7), you may need to run the prvtpool.plb script to build the DBMS_SHARED_POOL package body.

Large Pool

Some DBAs consider the large pool an optional feature for use only when you run a multithreaded server. But I have seen ORA-04031 and ORA-01037 errors that seemed to indicate insufficient shared-pool size when there were, in fact, megabytes of free pool. The errors went away when the large pool was used.

Turn on the large-pool usage by employing the minimal multithreaded server (MTS) configuration or by setting PARALLEL_AUTOMATIC_TUNING to TRUE. The automatic sizing of the large pool is generally adequate. Note, however, that automatic sizing cannot be monitored through the V$SGASTAT view.

Sorts and Hash Operations

Another problem I see all the time with third-party or off-the-shelf software applications is insufficient sizing of the sort and hash areas. The hash area's size-default value is twice the sort-area size. The SORT_AREA_SIZE parameter sets the sort-area size and, if allowed, determines the hash-area size as well. The following shows the number of sorts measured by a simple SELECT against the V$SYSSTAT view:

SQL> SELECT name,value FROM v$sysstat 

WHERE name LIKE 'sort%';

NAME            VALUE
____            _____

sorts (memory)   8007
sorts (disk)       14
sorts (rows)    68924

It is a good practice to mitigate the occurrence of disk sorts such that the value for sorts (disk) returned by the following query is less than 0.1 percent of total sorts.

In Oracle E-Business Suite 11i, the suggestion for sorts (disk) is even lower: a maximum of 1 in 10,000 sorts should be to disk. Remember that any disk operation is going to be up to 14,000 times slower than a memory operation. This doesn't mean you need to grant a huge sort-area size. If possible, insert an ALTER SESSION SET SORT_AREA SIZE=x; command into any process that needs a large sort area. If you don't have the ability to specify sort size for batch or other large operations, then your only option is to set SORT_AREA_SIZE larger. Generally, I set SORT_AREA_SIZE to 1MB to start with and work up from there. In Oracle8i and Oracle9i Database, you can use the V$SORT_SEGMENT and V$SORT_USAGE views to monitor active sorts.

Remember that sorts occur whenever the following actions take place:

• Index creation
• Group by or order by statements
• Use of the distinct operator
• Join operations
• Use of the union, intersect, and minus set operators

In Oracle8i, you can control sort IO to disk by using SORT_MULTIBLOCK_READ_COUNT. It defaults to 2, and generally I suggest setting it up to, but not greater than, the DB_MULTIBLOCK_READ_COUNT value if you have sorts going to disk. And if you have hash operations going to disk, set HASH_MULTIBLOCK_READ_COUNT to a similar value. A setting of zero for HASH_MULTIBLOCK_READ_COUNT allows the system to calculate it.

Conclusion

Proper tuning of Oracle database resources, including block buffers and pool (shared and large), behind a third-party application can provide dramatic improvements in performance.

If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference".  Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.