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.
- 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.
- 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.
- 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.
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.