Problem solve Get help with specific problems with your technologies, process and projects.

Tuning SQL and PL/SQL

How do I tune SQL queries and PL/SQL? Any rules of thumb? Please also tell me any tools available for tuning and best tools among them.
Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. The "Oracle9i Database concepts" manual provides an overview in "Part V data access: SQL, PL/SQL and Java." You should also review the "Oracle9i Database performance tuning guide and reference."

Most of the decisions about SQL processing can and should be left to Oracle's Cost-Based Optimizer. Make sure that you have accurate statistics on your tables and indexes so that the optimizer has the information it needs to do its job.

When you are ready to tackle the tuning of individual SQL statements, you will want to use the EXPLAIN PLAN command to see the optimizer plan for a given query, the DBMS_SUPPORT package to trace Oracle sessions, and the TKPROF utility to format trace information. The Oracle documentation contains information on all of these.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.