Oracle SQL and index internals: And if you can't change the code...

The following is the tenth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss. Check back to the main series page for upcoming installments.

And if you can't change the code...

While system-wide tuning is not the best solution to SQL tuning, it can be extremely helpful in tuning systems with regular patterns of SQL processing, when you can't change the SQL (vendor delivered packages). It is not uncommon to see the Oracle DBA change the optimizer parameters depending on the time-of-day and day-of-week to accommodate changes in the type of SQL in the library cache.

Some of the more common changes:

Some of the most important Oracle Optimizer parameters for tuning include: We need to start by defining the "best" execution plan. At any given time, all SQL statements in the library cache need to have the "best" execution plan. (Of course, this may change frequently because at any given time the processing demands may change). Is the best execution plan the one that begins to return row the fastest, or is the best execution plan the one that executes with the smallest amount of computing resources? Obviously, the answer depends on the processing for your database, and Oracle offers two optimizer modes that allow you to choose your definition of the "best" execution pl

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Chapter Downloads Writing single-row and multiple-row subqueries List the types of SQL subqueries Using subqueries in SQL Define SQL subqueries Oracle 11g: PL/SQL Basics Oracle 11g: Backup and recovery concepts Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL Oracle Database 11g SQL Tuning Upgrading to Oracle Database 11g Tuning the Oracle database with initialization parameters

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

an for you: While the optimizer_mode parameter controls the overall behavior of the CBO, there are other Oracle parameters that have a great affect on CBO behavior. Oracle provides several important parameters to control the choices made by the CBO: This is no longer true, as of the version 9.2. System statistics, when computed, contains the value MBRC ("Multi Block Read Count"), which determines the price of the full table scan. What is more, Oracle 10g puts in some "platform defaults", which are rather bad. For the 9.2 version, please see Note: 149560.1 on Metalink.

Go to the main series page.

About the author

Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.