 |
|
Reusable SQL Inside the Library Cache
Oracle Tips by Burleson Consulting |
One of the biggest problems with Oracle SQL
prior to Oracle8i was that execution plans for SQL could not
be stored. In Oracle8i, we have the ability to use the
optimizer plan stability feature to create stored outlines
to store the execution plan for a SQL statement, and I will discuss
this new feature in Chapter 13. When Oracle recognizes an incoming
SQL statement, it hashes the SQL syntax to the Remote DBA_hints
view. If a stored outline exists, the execution plan is extracted
from the ol$hints table, thereby bypassing the overhead of
reparsing the SQL. However, Oracle still has problems recognizing
“similar” SQL statements. For example, Oracle library cache will
examine the following SQL statements and conclude that they are not
identical:
SELECT *
FROM customer;
Select * From Customer;
While capitalizing a single letter, adding an
extra space between verbs, or using a different variable name might
seem trivial, the Oracle software is not sufficiently intelligent to
recognize that the statements are identical. Consequently, Oracle
will reparse and execute the second SQL statement, even though it is
functionally identical to the first SQL statement.
The best way to prevent SQL reloads is to
encapsulate all SQL into stored procedures, and place these stored
procedures into packages. This use of stored procedures removes all
SQL from application programs and moves the SQL into Oracle’s data
dictionary. This method also has the nice side effect of making all
calls to the Oracle database look like a logical function. For
example, instead of having a complex SQL statement inside a program,
you would have a single call to a stored procedure.
There are other ways to make storage reusable
within the library cache. The cursor_space_for_time init.ora
parameter can be used to speed executions within the library cache.
Setting cursor_space_for_time to FALSE tells Oracle that a
shared SQL area can be deallocated from the library cache to make
room for a new SQL statement. Setting cursor_space_for_time
to TRUE means that all shared SQL areas are pinned in the cache
until all application cursors are closed. When this parameter is set
to TRUE, Oracle will not bother to check the library cache on
subsequent execution calls, because it has already pinned the SQL in
the cache. This technique can improve the performance for some
queries, but cursor_space_for_time should not be set to TRUE
if there are cache misses on execution calls. Cache misses indicate
that the shared_pool_size is already too small, and forcing
the pinning of shared SQL areas will only aggravate the problem.
Another way to improve performance on the
library cache is to use the init.ora
session_cached_cursors parameter. As you probably know, Oracle
checks the library cache for parsed SQL statements, but
session_cached_cursors can be used to cache the cursors for a
query. This is especially useful for tasks that repeatedly issue
parse calls for the same SQL statement—for instance, where a SQL
statement is repeatedly executed with a different variable value. An
example would be the following SQL request that performs the same
query 50 times, once for each state:
select
sum(dollars_sold)
from
sales_table
where
region = :var1;
In Oracle8i, we can also reduce
excessive SQL reparsing by setting cursor_sharing=force. This
initialization parameter will dynamically rewrite all SQL that
contains literal values and replace the literals with host
variables. For applications that dynamically generate SQL with
embedded literals, cursor_sharing is a godsend that can
dramatically reduce library cache overhead and improve the
performance of SQL.
Now that we have reviewed techniques for
efficiently using library cache storage, let’s look at a STATSPACK
report that will show us what is happening inside Oracle. There are
several metrics that address the inner workings of the library
cache.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
