Common Complaints

Every application big or small at some point in time will have performance issues in its life spam. Some of the common complaints are

1) Application is very slow
2) Reports taking hours to generate
3) Schedule jobs taking way too long to complete
4) Tired of throwing money @ Oracle consulting.

By taking some pro-active approach and setting the right expectation some of the issues can be handled. There is no single Oracle init parameter which will fix all the problems.

Always design and develop with performance in mind

All too often, SQL tuning is performed on an application designed and developed with virtually no consideration given to performance requirements. SQL tuning is often the best option for tuning such applications, but it is both more efficient and effective to design an application with performance in mind.

Producing good performance by design requires that the following activities occur within the development life-cycle:

1) Identify performance targets
2) Measure performance as early as possible.
3) Focus on key areas of the application
4) Prototype critical portions of the application
5) Consider de-normalization where ever applicable.

Establish a tuning and development environment

1) A significant portion of SQL that performs poorly in production was originally crafted against empty or nearly empty tables.

2) Make sure you establish a reasonable sub-set of production data that is used during development and tuning of SQL

3) Make sure your developers understand EXPLAIN PLAN and tkprof, or equip them with commercial tuning tools.

Keep it Simple.

1) Balance/Reduce the workload (If Possible).

2) Pre – Generate reports overnight when there is less load on the system.

3) Parallelize the queries – Use the full potential of the system.

Execute Smart Queries

1) Index wisely - Use of Indexes

2) Eliminate Full table scans.

3) Use concatenated indexes where appropriate

4) Consider advanced indexing options

a. Hash Clusters

b. Bit mapped indexes

c. Index only tables