The Barriers to SQL Tuning Oracle Tips by Burleson Consulting

The tuning of Oracle SQL is one of the most time-consuming, frustrating, and annoying areas of Oracle tuning. There are several factors that make SQL tuning a maddening undertaking.

• Locating the offensive SQL statement Later in this book we will explore technique for fishing SQL out of the Oracle library cache and tuning the statement. However, if you have an early release of Oracle8 that does not support optimizer plan stability, you must find the location of the SQL source code in order to make your tuning changes permanent. As we know, SQL source code can exist in a variety of locations, including PL/SQL, C programs, and client-side Visual Basic code.

• Resistance from management SQL tuning is a time-consuming and expensive process, and it is not uncommon for managers to be reluctant to invest in the time required to tune the SQL. In many cases, the Remote DBA must prepare a cost-benefit analysis showing that the saving in hardware resources justifies the costs of tuning all of the SQL in a database.

• Tuning with ad hoc SQL generators Products such as the SAP application dynamically create the Oracle SQL inside the SAP ABAP programs, and it is often impossible to modify the SQL source code.

• Resistance from SQL programmers Many programmers are reluctant to admit that they have created a suboptimal SQL statement. This can make it quite difficult when the Oracle Remote DBA determines that the statement must be rewritten to improve performance.

• Tuning nonreusable SQL statements Many third-party applications generate SQL statements with embedded literal values (e.g., select * from customer where name = ‘JONES’;). In these cases, the library is flooded with tens of thousands of nonreusable SQL statements, with many identical SQL statement that cannot be reused because they have no host variables (e.g., select * from customer where name = :var1;). In these cases, cursor_sharing must be implemented, and sometimes the library cache must be downsized or flushed with the alter system flush shared pool command.

• Diminishing marginal returns As the Oracle Remote DBA identifies and tunes high-use SQL statements, it becomes harder to locate SQL statements for tuning. For example, there may be many dozens of infrequently executed SQL statements that would greatly benefit from tuning, but their sporadic appearance in the library cache makes them difficult to locate. Hence, we see a point where the marginal benefit from tuning will become less than the Oracle Remote DBA effort in locating the statements, as Figure 1-8 illustrates.

Figure 8: The marginal benefits of SQL tuning

Despite these difficulties and challenges, the tuning of Oracle SQL can make the Oracle Remote DBA a hero, both to management and to the programming staff. Enlightened companies always make the programmers submit SQL statements with their current execution plans to the Remote DBA for a review, prior to placing the SQL into their production environment.

Next, let’s take a high-level tour of the steps involved in SQL tuning.

The Process of SQL Tuning

Once all of the prerequisite tuning has been done to the server, network, disk, instance, and objects, the process of SQL tuning can begin. Although we will be covering this subject in greater detail later, let’s take a high-level tour of the steps of SQL tuning.

It is critical to remember that SQL tuning is an iterative activity. The Oracle Remote DBA is challenged to perform the following activities:

• Locate high-use SQL statements The first step in SQL tuning is locating the  frequently executed SQL. This involves using STATSPACK or fishing through the library cache.

• Tune the SQL statement The tuning of a SQL statement involves generating execution plan and evaluating alternative execution plans by:

• Adding indexes You can add indexes (especially bitmapped and function-based indexes) to remove unwarranted full-table scans.

  • Changing the optimizer mode You can try changing the optimizer mode to rule, all_rows, or first_rows.

  • Adding hints You can add hints to force a change to the execution plan.

• Make the tuning permanent Once you have tuned the SQL statement, you must make the change permanent by locating and changing the SQL source code or by using optimizer plan stability.

Let’s take a close look at these steps.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.