Value: There is no alternative to tuning to ensure successful system usage by the business. If your business users feel that the data your system provides takes a longer time for them to access, you have lost half the battle.

Approach

APPLICATION SQL
Tuning the application gives the biggest increase in performance. It is suggested that long running queries be rewritten to use indexes, appropriate hints etc to help in this scenario.
If the database is running a COST BASED OPTIMIZER (CBO), capture database schema statistics on a regular basis. If running a RULE BASED OPTIMIZER (RBO), create appropriate indexes on “WHERE” conditions in the SQL being issued in the application.
If you know the data access pattern then it sometimes is much efficient to tune the application with “HINTS”. Adding appropriate indexes into the application and adding corresponding INDEX HINTS with give a performance boost.

MEMORY
The next aspect of tuning should focus on database memory allocations. Increasing the SGA appropriately to pin procedures and packages in the library cache together with caching database objects in the db_buffer cache will considerably improve performance by reusing SQL and without spending an extra effort of Parsing and IO.
Also ensure that there is enough free memory for the operating system. As a thumb rule approximately 50% of the available memory should be set aside for the operating system, in case of a shared system. In dedicated systems, this can be considerably lower than 50%.

HARDWARE
Hardware is most expensive piece of database tuning and should be addressed last. Approach hardware tuning in terms of RAID configurations, CPU (number, speed etc), disk space.
OLTP systems should be running a RAID 5 configuration at least with a decent SLA with the hardware vendor. If availability is a critical requirement, consider a FAILOVER strategy. If there is an opportunity to architect the layout of the disks, start with the SAN configuration and layout disks appropriately based on SAN controllers.
Number of CPU’s is dependent on the number of concurrent users of the system and the type of queries. Typically in OLTP systems, queries are smaller but with a large number of users. A decent estimate for the CPU number is 6 CPU’s for about 25 concurrent users.
If disk space is available, consider creating summary tables or materialized views to help query performance.

Get the most out of the investment your company is making with the best available in today’s marketplace – A Performance Tuning engagement from Mercury Software Consulting, Inc.

Click here to be contacted.