O'Reilly logo

Oracle SQL: the Essential Reference by David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.4. Improving Query Performance

If your query performance is unsatisfactory, remember that methods are available to help you improve it. You may want to:

  • Modify the database structure

  • Gather up-to-date statistics

  • Rewrite the query

  • Use optimizer hints to control the execution plan

8.4.1. Modifying Database Structure

Database structure modifications usually involve the creation of indexes. It's important to have the proper indexes to support your queries, but indexes aren't always the answer to poor performance. Other structural changes include separating table data and index data onto separate disks, spreading data over multiple disks, partitioning the data, and clustering data. However, these changes aren't likely to improve performance dramatically if you have a poorly written query to begin with.

8.4.2. Gathering Statistics

If the cost-based optimizer is in use, then table and index statistics play a large role in determining the execution plan that Oracle will use for any given statement. It's important that there be a set of statistics that results in the generation of good execution plans. In theory, up-to-date statistics that reflect the data accurately would always result in the best execution plans. In practice, occasional performance drops have been experienced after analyzing tables. Oracle8i actually includes a feature that allows the import of an arbitrary set of statistics to serve as the basis for optimizer-generated execution plans.

8.4.3. Rewriting Queries ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required