 |
|
Making Permanent
Changes to Tuned SQL
Oracle Tips by Burleson
|
Tuning the SQL statement is only half of the
battle. Once tuned, the DBA must be able to make the tuning change
permanent. For some changes, such as adding a new index, the change to
the optimizer behavior is automatic. Other changes such as adding
hints to SQL require more work to become permanent. There are two
approaches to doing this:
If we have a database where all of the SQL is
encapsulated into packages, it is very easy for the DBA to extract the
package and change the SQL. If the SQL arrives into Oracle from an
external location such as a Pro*C program or a client/server call, we
have the time-consuming task of locating the program that sent the SQL
statement.
Using Stored Outlines for SQL
If we are using Oracle8i, we have another great
option. Oracle8i provides a new package called outline that
allows the DBA to store a ready-to-run execution plan for any SQL
statement. This utility is called optimizer plan stability and
has several features:
-
Parsing and execution time is reduced because
Oracle will quickly grab and execute the stored outline for the SQL.
-
Tuning of SQL statements can easily be made
permanent without locating the source code.
-
SQL from third-party products (e.g., SAP,
PeopleSoft) can be tuned without touching the SQL source code.
Plan stability allows you to maintain the same
execution plans for the same SQL statements, regardless of changes to
the database such as reanalyzing tables, adding or deleting data,
modifying a table's columns, constraints, or indexes, changing the
system configuration, or even upgrading to a new version of the
optimizer.
To use optimizer plan stability you must run
the dbmsol.sql script from $ORACLE_HOME/rdbms/admin. When
executed, a new Oracle user called OUTLN is created (with DBA
privileges) and a package called outln_pkg is installed to provide
procedures used for managing stored outlines.
Oracle provides the create outline
statement to create a stored outline. The stored outline contains a
set of attributes that the optimizer uses to create an execution plan.
Stored outlines can also be created automatically by setting the
init.ora parameter create_stored_outlines=TRUE.
For more details on using stored outlines, see
your Oracle8i documentation.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
