Oracle SQL tuning with parameters

Donald K. Burleson's picture

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.

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

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.


Hello Mr.Burleson,

These articles are of great help.
Thank You Sir


Hello Mr. Burleson,

These articles are very usefull and guideline.

Thanks a lot for you Blog and information.


Vivekanand Pandey