| where inner.dept_no = outer.dept_no |
| -- reference script. with_restriction.sql |
Here are the three plans – from 10.2.0.5, 11.1.0.7, and 11.2.0.3 respectively:
| PLAN_TABLE_OUTPUT 10.2.0.5 |
| ------------------------------------------------------------------------------------------------------------------------ |
| Plan hash value: 834666169 |
| ---------------------------------------------------------------- |
| | Id | Operation | Name | |
| ---------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | |
| | 1 | TEMP TABLE TRANSFORMATION | | |
| |* 4 | TABLE ACCESS FULL | EMP | |
| | 7 | TABLE ACCESS FULL | EMP | |
| | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_AAB1DB | |
| ---------------------------------------------------------------- |
| PLAN_TABLE_OUTPUT 11.1.0.7 |
| ------------------------------------------------------------------------------------------------------------------------ |
| Plan hash value: 706934928 |
| ----------------------------------------------------------------- |
| | Id | Operation | Name | |
| ----------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | |
| | 1 | TEMP TABLE TRANSFORMATION | | |
| |* 4 | TABLE ACCESS FULL | EMP | |
| | 7 | TABLE ACCESS FULL | EMP | |
| | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_50EDB63 | |
| ----------------------------------------------------------------- |
| PLAN_TABLE_OUTPUT (11.2.0.3) |
| ------------------------------------------------------------------------------------------------------------------------ |
| Plan hash value: 2718753531 |
| ------------------------------------------------------------------ |
| | Id | Operation | Name | |
| ------------------------------------------------------------------ |
| | 0 | SELECT STATEMENT | | |
| | 1 | TEMP TABLE TRANSFORMATION | | |
| | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D662A_40002053 | |
| |* 4 | TABLE ACCESS FULL | EMP | |
| | 7 | TABLE ACCESS FULL | EMP | |
| | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662A_40002053 | |
| ------------------------------------------------------------------ |
Can you spot the problem ?
Apart from anything else, the 11.2.0.3 plan has two references to the “temporary table” while the other two plans only have one reference – so the plan_hash_value for 11.2.0.3 is going to be different from the plan_hash_value for the other two (the plan hash value is derived from the line numbers, operations, and object names).
But then again, 10.2.0.5 and 11.1.0.7 have exactly the same plan but different plan_hash_values
– and that’s because you can see (when you look closely) that they have
different name for the temporary table. In fact, every time the
statement is optimised you’ll see a new sequential value
for (the middle section of) the temporary table name. At least, that’s
what happens until 11.2.0.3 where bug fix 10162430 comes into play and
any object names starting with SYS_TEMP are ignored. (Thanks to Timur Ahkmadeev for pointing that out).
Why does this matter? Because when Oracle tries to use an SQL
Baseline, it optimizes the query with the baseline in place and checks
to see if the resulting plan has the same plan_hash_value
as the one stored with the baseline. If the values don’t match, the
baseline isn’t used. So if, just before upgrading, you were planning
to create some baselines for queries that have materialized factored
subqueries (or any other constructs, such as star transformations and
grouping set queries, that create temporary tables) then any baselines
you do generate won’t work after the upgrade, unless you’re upgrading
FROM 11.2.0.3.
One option, perhaps, is to fake in a new baseline after the upgrade
to 11.2.0.3, and this might be relatively easy if you first try to run
the system with parameter optimizer_features_enable
set to your earlier release but switch on the fix for bug 10162430 and
capture baselines with that configuration in place. This will give you a
fixed plan_hash_value for the plan and if most cases will probably give you the plan
you had seen in the previous release; the only little oddity will be
that the outline will include the entries: OPT_PARAM(‘_fix_control’
’10162430:1′) and OPTIMIZER_FEATURES_ENABLE(‘{your chosen version}’).
Footnote:
Bug 10162430 was reported against 10.2.0.4, and is reported as fixed in
12.1. To revert to older behaviour in 11.2.0.3 you can use the
fix_control mechanism:
| alter session set "_fix_control"='10162430:OFF'; |
To revert to an older set of optimizer features but enable this bug fix you would do something like:
| alter session set optimizer_features_enable='10.2.0.5'; |
alter session set "_fix_control"='10162430:ON';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24383181/viewspace-745136/
，如需转载，请注明出处，否则将追究法律责任。
