author:skate
time:2010-03-23
OPTIMIZER_FEATURES_ENABLE
Property | Description |
---|---|
Parameter type | String |
Syntax | OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 } |
Default value | 10.0.0 |
Modifiable | ALTER SESSION , ALTER SYSTEM |
OPTIMIZER_FEATURES_ENABLE
acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 8.0.6 to release 9.2, but you want to keep the release 8.0.6 optimizer behavior, you can do so by setting this parameter to 8.0.6
. At a later time, you can try the enhancements introduced in releases up to and including release 9.2 by setting the parameter to 9.2.0
.
Table 1-2 describes some of the optimizer features that are enabled when you set the OPTIMIZER_FEATURES_ENABLE
parameter to an 8.0 release or an 8.1 release. Table 1-3 describes some of the optimizer features that are enabled when you set the OPTIMIZER_FEATURES_ENABLE
parameter to a 9.0 release or a 9.2 release.
See Also: Oracle Database Performance Tuning Guide for more information about the optimizer and for information about the features listed in the following tables |
Table 1-2 Release 8.0 and Release 8.1 Optimizer Features
Features | Release | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | |
Index fast full scan | X | X | X | X | X | X | X | X | X | X | ||
Consideration of bitmap access paths for tables with only B-tree indexes | X | X | X | X | ||||||||
Complex view merging | X | X | X | X | ||||||||
Push-join predicate | X | X | X | X | ||||||||
Ordered nested loop costing | X | X | X | X | ||||||||
Improved outer join cardinality calculation | X | X | X | X | X | X | X | X | ||||
Improved verification of NULLs inclusion in B-tree indexes | X | X | X | |||||||||
Random distribution method for left of nested loops | X | X | ||||||||||
Type-dependent selectivity estimates | X | X | ||||||||||
Setting of optimizer mode for user recursive SQL | X | X | ||||||||||
Improved average row length calculation | X | X | ||||||||||
Partition pruning based on subquery predicates | X | X | ||||||||||
Common subexpression elimination | X | |||||||||||
Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity | X | |||||||||||
Improved partition statistics aggregation | X |
Table 1-3 Release 9.0 and Release 9.2 Optimizer Features
Features | Release | ||
---|---|---|---|
9.0.0 | 9.0.1 | 9.2.0 | |
Index fast full scan | X | X | X |
Consideration of bitmap access paths for tables with only B-tree indexes | X | X | X |
Complex view merging | X | X | X |
Push-join predicate | X | X | X |
Ordered nested loop costing | X | X | X |
Improved outer join cardinality calculation | X | X | X |
Improved verification of NULLs inclusion in B-tree indexes | X | X | X |
Random distribution method for left of nested loops | X | X | X |
Type-dependent selectivity estimates | X | X | X |
Setting of optimizer mode for user recursive SQL | X | X | X |
Improved average row length calculation | X | X | X |
Partition pruning based on subquery predicates | X | X | X |
Common subexpression elimination | X | X | X |
Use statistics of a column imbedded in some selected functions such as TO_CHAR to compute selectivity | X | X | X |
Improved partition statistics aggregation | X | X | X |
Peeking at user-defined bind variables | X | X | X |
Index joins | X | X | X |
Subquery unnesting | X | X | X |
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
SQL>
----------------------------------------------------------
Plan hash value: 1189072556
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 123 | 33 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 123 | | |
|* 2 | HASH JOIN | | 2433 | 292K| 33 (4)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 385 | 46585 | 13 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 346 | 38752 | 13 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 346 | 35638 | 13 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 346 | 33216 | 13 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 462 | 36498 | 13 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_DEVICETYPE | 1 | 9 | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | DEVICE | 462 | 32340 | 13 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_DEVICEMODEL | 1 | 17 | 0 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_DEVICEPROP | 1 | 7 | 0 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_RESCOLTIMEPLAN | 1 | 9 | 0 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_RESCOLITEMPROFILE | 1 | 9 | 0 (0)| 00:00:01 |
| 14 | COLLECTION ITERATOR PICKLER FETCH| USERRESOURCEAUTH | | | | |
------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 1816705116
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 109 | 113 (4)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 109 | | |
|* 2 | HASH JOIN | | 795 | 86655 | 113 (4)| 00:00:02 |
| 3 | NESTED LOOPS OUTER | | 656 | 70192 | 88 (4)| 00:00:02 |
| 4 | NESTED LOOPS OUTER | | 656 | 64944 | 88 (4)| 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 656 | 61008 | 88 (4)| 00:00:02 |
| 6 | NESTED LOOPS | | 656 | 55760 | 88 (4)| 00:00:02 |
| 7 | NESTED LOOPS | | 674 | 46506 | 87 (3)| 00:00:02 |
|* 8 | INDEX UNIQUE SCAN | PK_DEVICETYPE | 1 | 9 | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | DEVICE | 674 | 40440 | 87 (3)| 00:00:02 |
|* 10 | INDEX UNIQUE SCAN | PK_DEVICEMODEL | 1 | 16 | 0 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_RESCOLITEMPROFILE | 1 | 8 | 0 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_DEVICEPROP | 1 | 6 | 0 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_RESCOLTIMEPLAN | 1 | 8 | 0 (0)| 00:00:01 |
| 14 | COLLECTION ITERATOR PICKLER FETCH| USERRESOURCEAUTH | | | | |
------------------------------------------------------------------------------------------------------------
from slview.Device a,
slview.Node b,
slview.DeviceType c,
slview.DeviceModel e,
slview.DeviceProp f,
slview.ResColItemProfile Profile,
slview.ResColTimePlan ColPlan,
table(cast(userresourceauth('admin', 'DEV', 'NOD999', 'CFG') as
resauthresult)) z
where a.NodeCode = b.NodeCode
and a.DeviceTypeCode = c.DeviceTypeCode
and a.DeviceModelCode = e.DeviceModelCode
and a.ChangeType = '0'
and a.DevicePropCode = f.DevicePropCode(+)
and a.ColItemProfile = Profile.ColItemProfile(+)
and a.ColTimePlanId = ColPlan.ColTimePlanId(+)
and a.deviceid = z.resid
AND a.DEVICETYPECODE = 'DEV_IP_R'
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.08 0 114 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.61 0.59 0 1230 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.71 0.68 0 1344 0 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1230 pr=0 pw=0 time=599268 us)
461 HASH JOIN (cr=1230 pr=0 pw=0 time=600548 us)
461 NESTED LOOPS OUTER (cr=77 pr=0 pw=0 time=29314 us)
461 NESTED LOOPS OUTER (cr=75 pr=0 pw=0 time=23774 us)
461 NESTED LOOPS OUTER (cr=73 pr=0 pw=0 time=17774 us)
461 NESTED LOOPS (cr=71 pr=0 pw=0 time=11310 us)
461 NESTED LOOPS (cr=69 pr=0 pw=0 time=4384 us)
1 INDEX UNIQUE SCAN PK_DEVICETYPE (cr=1 pr=0 pw=0 time=42 us)(object id 52114)
461 TABLE ACCESS FULL DEVICE (cr=68 pr=0 pw=0 time=3878 us)
461 INDEX UNIQUE SCAN PK_DEVICEMODEL (cr=2 pr=0 pw=0 time=4169 us)(object id 52110)
461 INDEX UNIQUE SCAN PK_DEVICEPROP (cr=2 pr=0 pw=0 time=3197 us)(object id 52084)
461 INDEX UNIQUE SCAN PK_RESCOLTIMEPLAN (cr=2 pr=0 pw=0 time=3016 us)(object id 52243)
461 INDEX UNIQUE SCAN PK_RESCOLITEMPROFILE (cr=2 pr=0 pw=0 time=3134 us)(object id 52239)
1233 COLLECTION ITERATOR PICKLER FETCH USERRESOURCEAUTH (cr=1153 pr=0 pw=0 time=561120 us)
from Device a,
Node b,
DeviceType c,
DeviceModel e,
DeviceProp f,
ResColItemProfile Profile,
ResColTimePlan ColPlan,
table(cast(userresourceauth('admin', 'DEV', 'NOD999', 'CFG') as
resauthresult)) z
where a.NodeCode = b.NodeCode
and a.DeviceTypeCode = c.DeviceTypeCode
and a.DeviceModelCode = e.DeviceModelCode
and a.ChangeType = '0'
and a.DevicePropCode = f.DevicePropCode(+)
and a.ColItemProfile = Profile.ColItemProfile(+)
and a.ColTimePlanId = ColPlan.ColTimePlanId(+)
and a.deviceid = z.resid
AND a.DEVICETYPECODE = 'DEV_IP_R'
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.22 0.20 0 127 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 175.75 171.77 0 192609 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 175.97 171.98 0 192736 0 1
Optimizer mode: CHOOSE
Parsing user id: 60
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=192609 pr=0 pw=0 time=171775026 us)
235 HASH JOIN (cr=192609 pr=0 pw=0 time=171763231 us)
235 NESTED LOOPS (cr=314 pr=0 pw=0 time=22534 us)
235 NESTED LOOPS (cr=312 pr=0 pw=0 time=18535 us)
236 NESTED LOOPS OUTER (cr=310 pr=0 pw=0 time=15305 us)
236 NESTED LOOPS OUTER (cr=308 pr=0 pw=0 time=11522 us)
236 NESTED LOOPS OUTER (cr=306 pr=0 pw=0 time=7743 us)
236 NESTED LOOPS (cr=304 pr=0 pw=0 time=2537 us)
1 INDEX UNIQUE SCAN PK_DEVICETYPE (cr=1 pr=0 pw=0 time=44 us)(object id 66966)
236 TABLE ACCESS FULL DEVICE (cr=303 pr=0 pw=0 time=2020 us)
236 INDEX UNIQUE SCAN PK_RESCOLTIMEPLAN (cr=2 pr=0 pw=0 time=2073 us)(object id 68692)
187 INDEX UNIQUE SCAN PK_RESCOLITEMPROFILE (cr=2 pr=0 pw=0 time=1500 us)(object id 68688)
234 INDEX UNIQUE SCAN PK_DEVICEPROP (cr=2 pr=0 pw=0 time=1607 us)(object id 66964)
235 INDEX UNIQUE SCAN PK_DEVICEMODEL (cr=2 pr=0 pw=0 time=1720 us)(object id 66958)
235 INDEX UNIQUE SCAN PK_NODE (cr=2 pr=0 pw=0 time=1788 us)(object id 68056)
2253 COLLECTION ITERATOR PICKLER FETCH USERRESOURCEAUTH (cr=192295 pr=0 pw=0 time=171735676 us)