Oracle Tuning with Parameters

Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats.  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

10gr2 Note:  Starting in Oracle 10g release 2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.  For more details, see my notes on 10gR2 automatically tuned multi-block reads.

While all SQL tuning professionals advocate tuning each individual SQL statement to reduce logical I/O, there are many cases where you do not have the luxury of tuning each-and-every SQL statement in an application. In these cases, the best you can hope to do is adjust the global optimizer parameters to optimizer as many SQL statements as possible.

Cases where you may not be able to tune individual SQL statements include:

• Vendor applications - If you do not have access to the source code, if can be very challenging too change the SQL execution plans.
   
• Ad-Hoc query tools - Tools that generate dynamic SQL are notoriously difficult to tune because there is no SQL source code.
   
• Economic constraints - If IT management will not pay to tune 10,000 SQL statements then you are forced to adjust system-wide Oracle parameters.

While system-wide tuning is not the best solution to SQL tuning it can be extremely helpful in tuning system with regular patterns of SQL processing. 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. Approaches to system-wide SQL tuning include these steps:

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

Tuning the SQL workload by adjusting the tuning parameters is a part of Oracle 11g Real Application Testing (RAT) and the SQL Performance Analyzer (SPA), a codification of the top-down approach to Oracle tuning:

Troubleshooting tip!  For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an "alter session" command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Let's start by looking at the important Oracle optimizer parameters.

The 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 what the "best" execution plan is. At any given time, all SQL statement 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? Of course, 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

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query:

While the optimizer_mode parameter controls the overall behavior of the CBO, there are other Oracle parameters that have a great effect on CBO behavior. Oracle provides several important parameters to control the choices made by the CBO:

• optimizer_index_cost_adj - This is an important CBO parameter because it adjusts the propensity of the CBO to favor index access over full-table scan access. The smaller the value, the more like that the CBO will use an available index. 
   
• optimizer_index_caching - This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching effects 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.

In later installments we will examine these parameters more closely and see how Oracle can optimize whole batches of SQL queries with a broad-brush approach.

The popular Ion tool is the easiest way to analyze Oracle table behavior (average CPU cost per table access shown above)  and Ion allows you to spot hidden table-related performance trends.  Ion is our favorite Oracle tuning tool, and the only 3rd party tool that we use.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

If you like Oracle tuning, you might enjoy my latest book "Oracle Tuning: The Definitive Reference" by Rampant TechPress. It's only $41.95(I don't think it is right to charge a fortune for books!) and you can buy it right now at this link:

www.rampant-books.com/book_2003_1_rac.htm