Oracle Code Tuning Tips

Performance tuning of code has always been the most difficult area of Oracle development. In fact, some developers say that tuning an unstructured Oracle code is like trying to work on a car while it's flying down the highway at 60 mph!.

Most Oracle experts generally use a top-down approach for tuning. This means that you start at a very high level, taking a look at the overall design of the code, and then successively drill down into more detail as you begin to tune the individual components within the code.

The goal of this article is to give you the general tools required to ensure that your Oracle code is performing at an optimal level. Although the article won't make you an expert, they'll give you a solid understanding of all the important Oracle tuning issues.

First of all, you have to know why it is slow. What is the real cause of your problem. If the reason why is not known, suggesting to rewrite the query, or hinting the query, suggesting parallellization is not very productive. Once in a while you may get lucky. But even then you have to realize that if your problem seems "solved", but you don't know why, nothing guarantees you that the problem won't come back tomorrow. So the first step should always be to investigate the root cause.

Some of the tools at your disposal are:
- dbms_profiler
- explain plan
- SQL*Trace / tkprof

dbms_profiler
Use dbms_profiler if you want to know where time is being spent in PL/SQL code. Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.

Explain Plan
This is one of the best tip. When we find that there is a performance issue with the query we need to break the query into smaller chunks. This approach is best called as 'Divide and Rule'. To implement this we need to type the following in SQL*Plus:

explain plan for
select * from table(dbms_xplan.display);
When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.

SQL*Trace/tkprof
For this you have to type in SQL*Plus:
- alter session set sql_trace true;
-
- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)
- identify your trace file in the server directory as specified in the parameter user_dump_dest
- on your operating system: tkprof a.txt sys=no

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

A note about database design and performance
The initial design of the Oracle tables and indexes is the single most critical factor in overall performance—and unfortunately, the design can rarely be changed once the system is placed into production use. So while the tuning techniques we'll be discussing can help you maximize the efficiency of your database engine, bear in mind that the initial design is the most important performance factor.

When a database is initially analyzed, the designer will often apply the normalization rules developed by E. F. Codd and C. J. Date. Their normalization study resulted in a set of table definitions that made it easier to design tables with controlled redundancy.

In the 1970s, database redundancy was difficult and expensive. As a result, database designers were taught to create databases in Third Normal Form (3NF), which prevented data duplication in multiple tables. But although a 3NF database was totally free of redundancy, the database queries could run very slowly because of the extra navigation required to access information. Over the 1980s and 1990s, database designers became more liberal with the introduction of redundant data to speed database queries.

The overall tuning approach
While there is no silver bullet for tuning Oracle unstructured code, a comprehensive approach can help ensure that all of the bases are covered and that no important tuning facts have been overlooked. When tuning the code, we have to start by taking a broad look at the overall client requirements and successively drilling down for more details.

PS: I am fully aware that this text is only a tiny fraction of what can be done, and that other people may choose different tools and actions, but the above gives you a very reasonable start at solving your performance problem.

To learn in more detail about Query optimization in Oracle please click here