Question: I was just advised by
Oracle technical support to fix a SQL bug to adjust my
optimizer_features_enable to a 9i optimizer release. How can I
see the history of SQL optimizer bug fixes in 10g?
Answer: Tracking Oracle features
by release is maddening, and I keep a
list of major
Oracle features by release. As
to optimizer features and bugs, prior to Oracle 11g you had to contact
MOSC, but as of 10gr2 and beyond there are two new views
v$ststem_fix_control and v$session_fix_control for displaying
the specific optimizer features by release of Oracle.
The
v$system_fix_control view can be used to display the SQL features of the
optimizer by release. The relevant columns in v$system_fix_control
are optimizer_feature_enable, bugno, value, sql_feature and
description.
Note the maddening difference between the
v$system_fix_control column of optimizer_feature_enable is the
anchor that lists the specific detauls by release of Oracle, as opposed to
the Oracle parameter optimizer_features_enable, just one letter
different and enough to drive the DBA crazy!
To see all of the new SQL optimizer features in Oracle
10g, try the following command:
select
optimizer_feature_enable,
description
from
v$system_fix_control
where
substr(optimizer_feature_enable,1,2)
= '10'
order by
to_number(optimizer_feature_enable),
description;
For a related view, see my notes on
v$session_fix_control which is used with Oracle technical support to
enable and disable specific optimizer features at the session level
For more detauls, see MOSC note 5483301.8 on using
v$system_fix_control.
 |
If you like Oracle tuning, you may enjoy my 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. |
