The performance of an application can be enhanced by enhancing performance of the database. Database tuning is an effective technique to enhance performance of an application. Mostly third party application does not allow altering SQL source code used in the application. In this article I will discuss some database tuning techniques for Oracle9i Database behind third-party application which does not allow modification.

Database Tuning - Specifying the chunk of memory:

The DB_BLOCK_BUFFERS parameter value combined with the DB_BLOCK_SIZE value allow specification of the chunk of memory to allocate for use with database objects. Many memory objects provide 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.

Database Tuning - Specifying Buffers:

The V$DB_CACHE_ADVICE view and DB_CACHE_ADVICE initialization parameter help in deciding how many buffers to add, or, conversely, how many to take away.

SGA_MAX_SIZE parameter determines the maximum size the SGA can reach. It enables you to dynamically allocate memory up to your physical limit. If you set the value explicitly, Oracle9i Database uses it as a hard limit.

Database Tuning - Parts of Shared Pool:

The shared pool includes

1. All referenced data dictionary definitions
2. All executed stored-object code including views, packages, procedures, triggers, functions, etc.
3. All SQL code issued.
4. The library cache
5. The dictionary cache
6. Latch structures
7. Message pools.

Database Tuning - Storing Stored Objects:

The shared pool stores the SQL for stored objects however it wishes by breaking it into small chunks. The performance suffers if the number of SQL chunks exceeds certain thresholds. It is a good practice to limit the total number of SQL areas in the V$SQLAREA view to fewer than 5,000. You can use SELECT statement against the V$SGASTAT view in order to determine the total usage of the shared pool

Database Tuning - Pinning:

Reusable packages, procedures, functions, triggers, cursors, and sequences should all be 'pinned' in the pool. 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. You can 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.

Database Tuning - DBMS_SHARED_POOL Package:

The DBMS_SHARED_POOL package is used to pin the appropriate objects in the pool.

Database Tuning - Large Pool Usage:

You can turn on the large-pool usage by two methods

1. Employ the minimal multithreaded server (MTS) configuration
2. Set PARALLEL_AUTOMATIC_TUNING to TRUE.

The automatic sizing of the large pool is generally adequate but it cannot be monitored through the V$SGASTAT view.

Database Tuning - SORT_AREA_SIZE:

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.

Database Tuning - Sorts:

Sorts occur when either an index is created, during GROUP BY or ORDER BY statements, using DISTINCT, JOIN, UNION , INTERSECTION and MINUS opertators.

Database Tuning - Controlling Sorts:

In Oracle8i, you can control sort IO to disk by using SORT_MULTIBLOCK_READ_COUNT which has defaults value of 2. The values for SORT_MULTIBLOCK_READ_COUNT can be set high 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:

To conclude I would say that proper tuning of Oracle databases behind a third-party application can provide dramatic improvements in performance.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips to Resolve Tablespace Fragmentation Issues!!
   DBA Tips for Troubleshooting Oracle Orphaned Sessions!!
   Beware of Oracle Outage with database growth!!
   DBA Tips: Handling Recordsets in Oracle Procedures!!