Oracle SQL plans have evolved over the past several decades, from
simple stored outlines to complex SQL plan management:
- Optimizer plan
stability (a.k.a. stored outlines) - Stored outlines were
cumbersome to manage, and it was very difficult to "swap"
execution plans with plan stability.
- 10g SQL Profiles -
Starting in Oracle 10g, we see the SQL Profile approach, whereby
a SQL tuning Set (STS) could be tested as a workload, and Oracle
would allow the DBA to implement changes to execution plans.
- 11g SQL Plan management
- Starting in 11g we finally see an easy-to-use approach to
locking-down SQL execution plans.
The Oracle 11g view dba_sql_plan_baselines allows us to
see all of the plans for any given SQL statement:
select
sql_handle,
plan_name,
enabled,
accepted,
fixed
from
dba_sql_plan_baselines
WHERE LOWER(sql_text) LIKE '%hr.employees%';
SQL_HANDLE
PLAN_NAME
ENA ACC FIX
------------------------------
------------------------------ --- --- ---
SYS_SQL_818c1879b000a439
SYS_SQL_PLAN_b000a4397d478871 YES YES NO
SYS_SQL_818c1879b000a439
SYS_SQL_PLAN_b000a439c0e983c6 YES YES YES
SYS_SQL_818c1879b000a439
SYS_SQL_PLAN_b000a439cf314e9e YES NO NO
select
s.sql_text,
b.plan_name,
b.origin,
b.accepted
from
dba_sql_plan_baselines b,
v$sql s
where
s.exact_matching_signature = b.signature
and
s.sql_plan_baseline = b.plan_name;
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|