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

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.

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:

  • Changes to Oracle parameters -- Changes to optimizer_mode, optimizer_index_cost_adj and optimizer_index_caching can make a huge difference in the execution plans of SQL.

  • Changes to statistics -- Using the dbms_stats package to import specialized statistics (geared to the current processing mode) can make a huge difference in SQL execution speed.

  • Using automatic query re-write -- Using Oracle Materialized Views you can pre-aggregate and pre-summarize data to reduce the amount of run-time table joins. For low-update databases, you can also pre-join tables together to improve processing speed.

Some of the most important Oracle Optimizer parameters for tuning include:

  • optimizer_mode -- In Oracle9i there are many optimizer modes, all determined by the value of the optimizer_mode parameter. The values are rule, choose, all_rows, first_rows, first_rows_1, first_rows_10 and first_rows_100.
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 plan for you:
  • optimizer_mode=first_rows -- This optimizer_mode favors index access over full table scan access and is used when you want a query to start returning rows quickly, even if the overall amount of logical I/O is higher than a full-table scan. The first_rows optimizer_mode is generally used in online system where the end-user wants to see the first page of query results as quickly as possible.

  • optimizer_mode=all_rows -- This optimizer mode favors full-table scans (especially parallel full-table-scans) in cases where the server resources will be minimized. The all_rows mode is generally used during batch-oriented processing and for data warehouses where the goal is to minimize server resource consumption.

  • optimizer_mode=first_rows_n -- Starting with Oracle9i, we also have a new optimizer_mode to optimizer a query for a smaller result set. The values are first_rows_1, first_rows_10 and first_rows_100, and you can use this parameter to ensure that Oracle optimizes the SQL.
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:
  • optimizer_index_cost_adj -- Adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more likely that the CBO will use an available index.

  • optimizer_index_caching -- Tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBOs decision to use an index for a table join (nested loops), or to favor a full-table scan.

  • db_file_multiblock_read_count -- When set to a high value (with larger servers), the CBO recognizes that scattered (multi-block) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
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.
  • parallel_automatic_tuning -- When set "on", full-table scans are parallelized on Oracle servers with many CPUs. Because parallelized full-table scans can be very fast, the CBO will give a higher cost to index access, and be friendlier to full-table scans.

  • hash_area_size (if not using pga_aggregate_target) -- The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins.

  • sort_area_size (only if you are not using the pga_aggregate_target) -- The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM (thousands of time faster than the TEMP tablespace), and the more likely that the CBO will favor a sort over pre-sorted index retrieval.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.