The Barriers to
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
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
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
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
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
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
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.