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;
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
