 |
|
Preparing Oracle for
Stored Outlines
Oracle Tips by
Burleson
|
Several steps need to be completed before
Oracle will use stored outlines. These include:
-
Setting the use_stored_outlines command
-
Verifying important initialization parameters
-
Creating the outline package
-
Defining the tablespace for the stored outlines
Setting the use_stored_outlines Command
Oracle provides a parameter called
use_stored_outlines to enable optimizer plan stability. The
use_stored_outlines parameter is not an initialization
parameter, although it probably will become an initialization
parameter in Oracle. To enable the use of stored outlines for
the whole database, you can issue the following command:
alter system
set use_stored_outlines=true;
However, a new parameter called
USE_PRIVATE_OUTLINES enables the use of a category of outlines
strictly for the current session while other sessions may use a
different set. In addition 9i OEM has a outline editor to facilitate
changing the outlines.
Of course, this command must be reissued each
time the database is restarted. Most Oracle DBAs add this command to
the post-startup procedure that executes dbms_shared_pool.keep
to pin Oracle packages into the shared pool.
You can also enable optimizer plan stability at
the session level by issuing the alter session set
use_stored_outlines=true command, but it is much more effective to
enable stored outlines at the database level, provided that your
system does not generate SQL with embedded literal values.
WARNING: The outln users default
system tablespace can become exhausted if the you alter system set
create_stored_outlines=true and your application issues lots
of nonreusable SQL with literal values. If this happens, set
cursor_sharing=force in your initialization file and use the
drop_unused function of the outline package to remove the unused
outlines.
Verifying Important Initialization Parameters
Oracle recommends several initialization
parameters that must be set in order to use stored outlines. These
parameters include:
-
cursor_sharing=forceSet this parameter
if your system uses SQL with embedded literal values. With
cursor_sharing set, the SQL will be rewritten to replace the
literals with host variables, making the stored outlines reusable.
-
query_rewrite_enabled=true This
enables materialized views.
-
star_transformation_enabled=true This
enables the star join.
-
optimizer_features_enable=true This
will enable the initialization parameters b_tree_bitmap_plans,
complex_view_merging, fast_full_scan_enabled, and
push_join_predicate.
Create the Outline Package
You will need to install the outln_pkg
package to get the procedures available to maintain your stored
outlines. Upon installation of the Oracle8i software, a user
named outln is created with a password of outln. The
script to create the outn_pkg packages is called dbmsol.sql
in the $ORACLE_HOME/rdbms/admin directory. Here is a listing from the
creation:
SQL> connect
/ as sysdba;
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmsol
Also, note that the catproc.sql script builds
this using the following calls:
@@catol.sql
@@dbmsol.sql
@@prvtol.plb
rem - If catol.sql and prvtol.sql aren’t run you won’t get the
catalog views or
he package body.
Note that this script creates the following synonyms and
grants after installing the sys.outln_pkg package:
CREATE PUBLIC
SYNONYM outln_pkg FOR sys.outln_pkg;
GRANT EXECUTE ON outln_pkg TO dba;
GRANT EXECUTE ON outln_pkg TO outln;
CREATE PUBLIC SYNONYM outline FOR sys.outln_pkg;
GRANT EXECUTE ON outline TO dba;
GRANT EXECUTE ON outline TO outln;
As we can see, you can reference the outline
package by the names outln_pkg or outline. Now that the
outline package is installed, you can now describe the
outline procedure and see all of the available functions:
SQL> desc
outline;
PROCEDURE DROP_BY_CAT
Argument Name
Type
In/Out Default?
------------------------------ ------------------ ------ --------
CAT
VARCHAR2
IN
PROCEDURE DROP_COLLISION
FUNCTION DROP_COLLISION_EXPACT RETURNS VARCHAR2
PROCEDURE DROP_EXTRAS
FUNCTION DROP_EXTRAS_EXPACT RETURNS VARCHAR2
PROCEDURE DROP_UNREFD_HINTS
FUNCTION DROP_UNREFD_HINTS_EXPACT RETURNS VARCHAR2
PROCEDURE DROP_UNUSED
PROCEDURE UPDATE_BY_CAT
Argument Name
Type
In/Out Default?
------------------------------ ------------------ ------ --------
OLDCAT
VARCHAR2
IN DEFAULT
NEWCAT
VARCHAR2
IN DEFAULT
Next let’s look at how to create a tablespace
for the outln user to hold the stored outlines.
Create the Stored Outline Tablespace
The outln user requires a separate
tablespace because your database may store thousands of stored
outlines and it is a good idea to segregate the outlines into a
separate tablespace for improved manageability. Here is the syntax to
create the tablespace:
create
tablespace
outline_ts
datafile
‘/u01/oradata/PROD/outline.dbf’
size
10M
default storage
(initial 10K next 20K minextents 1 maxextents unlimited)
online;
Tablespace created.
Next, we alter the outln user to use this
tablespace by default.
alter user
outln
default
tablespace
outline_ts;
User altered.
We are now ready to start capturing stored
outlines. Let’s take a look at how it works.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
