Before I start the actual post let me clarify something, these post series will be about using practical ways like I did on this post to understand
what is the cause of the post upgrade performance changes. I am trying to be more practical than scientific because of the nature of the production support DBA job.
When you are a production DBA, you deal with many issues/databases at the same time and time spent for a solution is very important for you to be able to move to the second problem.
Normally if you are a scientist, oracle enthusiastic then you get 10053 trace try to understand the data try to find out why optimizer did something on Xth step but in reality when it comes to upgrade issues
this is really time consuming and not very practical. You really need time/experience/knowledge to understand a 10053 trace especially for massive queries which has gazillion joins in it.
You can still understand what is going on from prediction and runtime statistics of a plan but it is not the answer you can give to the developers.
If you go and explain them all the things you spot in execution plan output and 10053 trace All they will say to you is “DB is upgraded and upgrade sucks because previous release was better why are we upgrading”
so you need a bit bigger proof to make them accept the issue and workarounds. I also want to add that, my priority for these series is not focusing on content of sqls
but giving you the practical technique to stabilize their plans during upgrades.
Now back to post, You followed the steps in first post and you found out that your plan is changed, now it is time to find out why it is changed
when the only optimizer version is changed. One of the possible reasons is bugfixes in the optimizer.
a-Brief introduction to bugfixes.
Information below is taken from MOS Note – Init.ora Parameter “_FIX_CONTROL” [Hidden] Reference Note [ID 567171.1];
Support and Historical Notes for "_FIX_CONTROL"
<strong>_FIX_CONTROL</strong> is a special hidden parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes.
This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant
effect on execution plans. Often the default setting (ON or OFF) for a fix is determined by the
value of the OPTIMIZER_FEATURES_ENABLE parameter. A full list of bug numbers which are controlled by fix control can be found in the V$ views:
- GV$SYSTEM_FIX_CONTROL
- GV$SESSION_FIX_CONTROL
The general format for this parameter is the form: _FIX_CONTROL='bug_number:ON|OFF'
eg: alter session set "_fix_control"='4969880:ON';
or in the init.ora
_fix_control='4969880:ON'
Multiple values can be specified:
eg: alter session set "_fix_control"='4969880:ON','5483301:off';
Note that fixes that use _FIX_CONTROL in one release may not use it in other releases so any settings should be reviewed when
upgrading / downgrading or applying a patch set. Also some fix controls have numeric settings rather than ON or OFF.
In summary in every release of Oracle Database (( including patches) ) there are bugfixes and since 10.2.0.2 their availability can be controlled by
this “undocumented” parameter. Some of these bugfixes are on by default some of them are not depending on the optimizer version+oracle binary(check extra test case at the bottom). you are running.
Bugfixes gives us fine grained control. If the optimizer feature you are running causing problems on some queries because of a bugfix then you can still stay on
the version you want but disable the fixes, having said that you still need aproval from ORacle Support if it is safe or not to enable disable bug.
Here are the number of bugfixes by releases taken from 11.2.0.2 base release
SQL> select optimizer_feature_enable,count(*) from v$system_fix_control group by optimizer_feature_enable order by 1 asc; OPTIMIZER_FEATURE_ENABLE COUNT(*) ------------------------- ---------- 10.1.0 3 10.1.0.3 1 10.1.0.5 2 10.2.0.1 7 10.2.0.2 12 10.2.0.3 11 10.2.0.4 73 10.2.0.5 111 11.1.0.6 39 11.1.0.7 19 11.2.0.1 65 11.2.0.2 105 8.0.0 52 8.1.6 1 8.1.7 2 9.2.0 8 9.2.0.8 3 37
And this is from 10.2.0.4 PSU 2
SQL> select optimizer_feature_enable,count(*) from v$system_fix_control group by optimizer_feature_enable order by 1 asc; OPTIMIZER_FEATURE_ENABLE COUNT(*) ------------------------- ---------- 10.1.0 1 10.1.0.5 1 10.2.0.1 4 10.2.0.2 13 10.2.0.3 10 10.2.0.4 68 9.2.0 2 9.2.0.8 7 9
There are couple of things you can spot here. Despite being higher version Oracle sometimes waits to put a bugfix of lower versions.
There are bugfixes from 10.1.0.3 and 8.0.0 and 8.1.6 8.1.7 which are on 11.2.0.2 relase but not on 10.2.0.4 release which is interesting.
In general do not think that bugfixes are added gradually. Oracle can put/make switchable a bugfix from a very old release to a brand new release. There is one more thing to bear in mind not %100 of bugfixes works with value 1 or 0. There are couple of them with values over 1 probably related with thresholds. In this post I will not pay attention to them.
After this brief information now it is time for the actual reason of this post.
As you recall, I shared an optimizer_features_test builder on the first post and now with the same logic I have 2 more builder scripts
to build your bugfix test for versions. After you identify under which OFE your plan behaviour changed, all you need to do is to pass
that version level to the scripts below and let them create test cases by enabling/disabling the bugfixes spesific to that version.
build_optimizer_bugfix_test.sql: This is for runtime bugfix test_case generator. All it does is create optimizer_bugfix_test.sql by using v$system_fix_control_table.
It gets each bugfix from that table for given OFE version and enable/disable them depending on their default value then run the test.sql and disable/enable the bugfix back again.
So we have a controlled environment where we test 1 bugfix at a time. It captures every plan_hash_value into a table and select them once all bugfix numbers tested.
If you are lucky enough or in other words if the problem is caused by a bugfix Oracle will revert the plan back to the original once you are changing that bugfixes value.
There is a tiny little problem with this approach, you cannot run 600 second query for 103 bugfixes. This is mostly for very quick queries.
The code of the sql is like below
undefine optimizer_feature_version
set heading off
set feedback off
define optimizer_feature="&optimizer_feature_version"
set term off
spool optimizer_bugfix_test.sql
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'set term off' from dual;
select 'set echo off' from dual;
select 'spool optimizer_bugfix_test_results.log' from dual;
select 'drop table bugfix_table purge;' from dual;
select 'create table bugfix_gtt (bugfix number,plan_hash_value varchar2(20));' from dual;
select 'alter session set optimizer_features_enable=''&optimizer_feature'';' from dual;
select 'alter session set "_fix_control"='''||bugno||decode(value,1,':OFF''',':ON''')||'; '
||chr(10)||'@test.sql'||chr(10)||
'insert into bugfix_table values ('||bugno||',(select substr(plan_table_output,18,12) from (select * from table(dbms_xplan.display_cursor)) where plan_table_output like ''Plan hash value:%''));'||chr(10)||
'alter session set "_fix_control"='''||bugno||decode(value,1,':ON''',':OFF''')||'; '
from v$system_fix_control
where optimizer_feature_enable='&optimizer_feature' and value in (1,0) order by bugno;
select 'set term on' from dual;
select 'set feedback on' from dual;
select 'set heading on' from dual;
select 'break on plan_hash_value' from dual;
select 'select plan_hash_value,bugfix from bugfix_table order by 1;' from dual;
select 'spool off' from dual;
spool off
set feedback on
set heading on
set term on
If you are sure your plan change behaviour also taking place when you explain the plan, better you run the builder below.
build_optimizer_bugfix_test_e.sql: This is same idea like above only difference is, this time it generates optimizer_features_test_e.sql which explains plans by runnint test_e.sql for every bugfix.
Instead of dbms_xplan.display_cursor I use dbms_xplan.display. Output of the optimizer_features_test_e.sql is same as optimizer_features_test.sql
source code of the builder
undefine optimizer_feature_version
set heading off
set feedback off
define optimizer_feature="&optimizer_feature_version"
set term off
spool optimizer_bugfix_test_e.sql
select 'set feedback off' from dual;
select 'set heading off' from dual;
select 'set term off' from dual;
select 'set echo off' from dual;
select 'spool optimizer_bugfix_test_results_e.log' from dual;
select 'drop table bugfix_table purge;' from dual;
select 'create table bugfix_gtt (bugfix number,plan_hash_value varchar2(20));' from dual;
select 'alter session set optimizer_features_enable=''&optimizer_feature'';' from dual;
select 'alter session set "_fix_control"='''||bugno||decode(value,1,':OFF''',':ON''')||'; '
||chr(10)||'@test_e.sql'||chr(10)||
'insert into bugfix_table values ('||bugno||',(select substr(plan_table_output,18,12) from (select * from table(dbms_xplan.display)) where plan_table_output like ''Plan hash value:%''));'||chr(10)||
'alter session set "_fix_control"='''||bugno||decode(value,1,':ON''',':OFF''')||'; '
from v$system_fix_control
where optimizer_feature_enable='&optimizer_feature' and value in (1,0) order by bugno;
select 'set term on' from dual;
select 'set feedback on' from dual;
select 'set heading on' from dual;
select 'break on plan_hash_value' from dual;
select 'select plan_hash_value,bugfix from bugfix_table order by 1;' from dual;
select 'set feedback on' from dual;
select 'set heading on' from dual;
select 'spool off' from dual;
set feedback on
set heading on
set term on
spool off
Both of the queries drop and create the table that they insert plan and bugfix numbers. You may want to implement it different way.
Let me show you how they work in two different examples for two of the queries.
This example is for a sql which started to run slow after upgrade from 11.2.0.1 to 11.2.0.2.
first check if the plan is changed
SQL> @sqlhist 1m7wy721qytx3
SNAP_ID BEGIN_INTERVAL_TIME SQL_ID CHILD_NO PLAN_HASH_VALUE
---------- ------------------------------ ------------- ---------- ---------------
25077 19-JAN-11 04.00.56.483 1m7wy721qytx3 0 3561051786
25078 19-JAN-11 04.31.00.422 1m7wy721qytx3 0 3561051786
25269 23-JAN-11 04.00.47.349 1m7wy721qytx3 0 2266344041 =>upgrade
25317 24-JAN-11 04.00.53.276 1m7wy721qytx3 0 2266344041
25365 25-JAN-11 04.00.43.525 1m7wy721qytx3 0 2266344041
25366 25-JAN-11 04.30.47.456 1m7wy721qytx3 0 2266344041
25413 26-JAN-11 04.00.22.604 1m7wy721qytx3 0 2266344041
25414 26-JAN-11 04.30.25.833 1m7wy721qytx3 0 2266344041
25461 27-JAN-11 04.00.18.101 1m7wy721qytx3 0 2266344041
25462 27-JAN-11 04.30.24.777 1m7wy721qytx3 0 2266344041
25509 28-JAN-11 04.00.05.631 1m7wy721qytx3 0 2266344041
25510 28-JAN-11 04.30.10.558 1m7wy721qytx3 0 2266344041
25557 29-JAN-11 04.00.41.775 1m7wy721qytx3 0 2266344041
25558 29-JAN-11 04.30.46.453 1m7wy721qytx3 0 2266344041
Plan hash value: 3561051786
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 1082K(100)| | | |
| 1 | MERGE | TABLE_XXXXXXXXXXX | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 286 | 1082K (1)| 01:44:15 | | |
| 4 | VIEW | | 1 | 78 | 1082K (1)| 01:44:15 | | |
| 5 | SORT GROUP BY | | 1 | 75 | 1082K (1)| 01:44:15 | | |
| 6 | FILTER | | | | | | | |
| 7 | NESTED LOOPS | | 237 | 17775 | 1082K (1)| 01:44:15 | | |
| 8 | HASH JOIN | | 237 | 15168 | 1081K (1)| 01:44:12 | | |
| 9 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR| | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
| 11 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
| 12 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TABLE_XXXXXXXXXXX | 1 | 208 | 2 (0)| 00:00:01 | | |
| 14 | INDEX UNIQUE SCAN | PK_TABLE_XXXXXXXXXXX | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2266344041
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 1081K(100)| | | |
| 1 | MERGE | TABLE_XXXXXXXXXXX | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 282 | 1081K (1)| 01:44:11 | | |
| 4 | VIEW | | 1 | 78 | 1081K (1)| 01:44:11 | | |
| 5 | SORT GROUP BY | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 6 | FILTER | | | | | | | |
| 7 | NESTED LOOPS | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 8 | NESTED LOOPS | | 1 | 64 | 1081K (1)| 01:44:11 | | |
| 9 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR| | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
| 11 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
| 12 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TABLE_XXXXXXXXXXX | 1 | 204 | 2 (0)| 00:00:01 | | |
| 14 | INDEX UNIQUE SCAN | PK_TABLE_XXXXXXXXXXX | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
Check the 8th step on the plans which makes the diffrence. Oracle suddenly started to choose nested loop instead of hash and estimated rows also drop down to 1.
Lets build the test case
SQL> @build_bind_vars_h Enter SQL ID ==> 1m7wy721qytx3 Enter Plan Hash Value ==> 2266344041 variable B1 NUMBER begin :B1 := 14544; end; / MERGE INTO MXS.TABLE_XXXXXXXXXXX TP USING ( select /* test 1m7wy721qytx3 */ /*+gather_plan_statistics*/TABLE_1.ID, TABLE_1.SOURCEID, NVL (MAX (CLOSETIMESTAMP), MAX (STARTTIMESTAMP)) LASTTIMESTAMP, MAX (CLOSE) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LAST, MAX (CLOSESPREAD) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTSPREAD, MAX (CLOSEUPFRONT) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTUPFRONT FROM USR1.TABLE_1, USR1.TABLE_2, USR1.TABLE_3 WHERE TABLE_1.ID = TABLE_2.TABLE_2.D AND TABLE_2.MARKETID =TABLE_3.MARKETID AND TABLE_2.MARKETID IN :B1 AND TABLE_1.SOURCEID =TABLE_3.SOURCEID AND DURATION = NUMTODSINTERVAL (1, 'DAY') AND STARTTIMESTAMP < TRUNC (SYSDATE, 'YEAR') - 1 AND STARTTIMESTAMP > TRUNC (SYSDATE, 'YEAR') - 7 GROUP BY TABLE_1.ID, TABLE_1.SOURCEID) TI ON (TP.TABLE_2.D = TI.TABLE_2.D AND TP.SOURCEID = TI.SOURCEID) WHEN MATCHED THEN UPDATE SET TP.LASTYEARCLOSE = TI.LAST, TP.LASTYEARCLOSETIMESTAMP = TI.LASTTIMESTAMP, TP.LASTYEARCLOSESPREAD = TI.LASTSPREAD, TP.LASTYEARCLOSEUPFRONT = TI.LASTUPFRONT WHEN NOT MATCHED THEN INSERT (TP.TABLE_2.D, TP.SOURCEID, TP.LASTYEARCLOSE, TP.LASTYEARCLOSETIMESTAMP, TP.LASTYEARCLOSESPREAD, TP.LASTYEARCLOSEUPFRONT) VALUES (TI.TABLE_2.D, TI.SOURCEID, TI.LAST, TI.LASTTIMESTAMP, TI.LASTSPREAD, TI.LASTUPFRONT);
Since this is a merge statement I needed to remove the merge part.
test.sql is like below
variable B1 NUMBER begin :B1 := 14544; end; / select /* test 1m7wy721qytx3 */ /*+gather_plan_statistics*/TABLE_1.ID, TABLE_1.SOURCEID, NVL (MAX (CLOSETIMESTAMP), MAX (STARTTIMESTAMP)) LASTTIMESTAMP, MAX (CLOSE) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LAST, MAX (CLOSESPREAD) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTSPREAD, MAX (CLOSEUPFRONT) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTUPFRONT FROM USR1.TABLE_1, USR1.TABLE_2, USR1.TABLE_3 WHERE TABLE_1.ID = TABLE_2.TABLE_2.D AND TABLE_2.MARKETID =TABLE_3.MARKETID AND TABLE_2.MARKETID IN :B1 AND TABLE_1.SOURCEID =TABLE_3.SOURCEID AND DURATION = NUMTODSINTERVAL (1, 'DAY') AND STARTTIMESTAMP < TRUNC (SYSDATE, 'YEAR') - 1 AND STARTTIMESTAMP > TRUNC (SYSDATE, 'YEAR') - 7 GROUP BY TABLE_1.ID, TABLE_1.SOURCEID
test_e.sql is like below
explain plan for select /* test 1m7wy721qytx3 */ /*+gather_plan_statistics*/TABLE_1.ID, TABLE_1.SOURCEID, NVL (MAX (CLOSETIMESTAMP), MAX (STARTTIMESTAMP)) LASTTIMESTAMP, MAX (CLOSE) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LAST, MAX (CLOSESPREAD) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTSPREAD, MAX (CLOSEUPFRONT) KEEP (DENSE_RANK LAST ORDER BY STARTTIMESTAMP) AS LASTUPFRONT FROM USR1.TABLE_1, USR1.TABLE_2, USR1.TABLE_3 WHERE TABLE_1.ID = TABLE_2.TABLE_2.D AND TABLE_2.MARKETID =TABLE_3.MARKETID AND TABLE_2.MARKETID IN 14544 AND TABLE_1.SOURCEID =TABLE_3.SOURCEID AND DURATION = NUMTODSINTERVAL (1, 'DAY') AND STARTTIMESTAMP < TRUNC (SYSDATE, 'YEAR') - 1 AND STARTTIMESTAMP > TRUNC (SYSDATE, 'YEAR') - 7 GROUP BY TABLE_1.ID, TABLE_1.SOURCEID;
build the optimizer test to see if its OFE related
SQL> @build_optimizer_features_test Enter value for base_optimizer_version: 11.2.0.1
run the test for different OFE settings
SQL> @optimizer_features_test
SQL> alter session set optimizer_features_enable='11.2.0.1';
Session altered.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c5y47k1zmrb22, child number 0
-------------------------------------
-------------------------------------
Plan hash value: 2561977778
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 320K|00:21:18.17 | 25M| 16M| | | |
| 1 | SORT GROUP BY | | 1 | 1 | 320K|00:21:18.17 | 25M| 16M| 73M| 6303K| 65M (0)|
|* 2 | FILTER | | 1 | | 1579K|01:37:13.89 | 25M| 16M| | | |
| 3 | NESTED LOOPS | | 1 | 1 | 1579K|01:37:11.10 | 25M| 16M| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 5446K|00:15:20.03 | 16M| 16M| | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 1 | 5446K|00:15:10.67 | 16M| 16M| | | |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 1 | 5446K|00:15:01.04 | 16M| 16M| | | |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 5446K| 1 | 1579K|00:05:19.24 | 9518K| 34458 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
5 - access("MARKET"."MARKETID"=:B1)
7 - filter(("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND "STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7
AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND "TABLE_2.NTERVAL"."TABLE_2.D">0 AND "TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID"))
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=:B1)
##########Second Run Cardinality feedback kicked still same plan with different cardinality on the last step
Plan hash value: 2561977778
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 1 | SORT GROUP BY | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | 73M| 6303K| 65M (0)|
|* 2 | FILTER | | 1 | | 1444K|00:22:15.31 | 24M| 15M| | | |
| 3 | NESTED LOOPS | | 1 | 1 | 1444K|00:22:12.87 | 24M| 15M| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 4894K|00:10:27.91 | 15M| 15M| | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 1 | 4894K|00:10:19.78 | 15M| 15M| | | |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 1 | 4894K|00:10:11.63 | 15M| 15M| | | |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 4894K| 1 | 1444K|00:00:53.00 | 8678K| 2859 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
5 - access("MARKET"."MARKETID"=:B1)
7 - filter(("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND "STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7
AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND "TABLE_2.NTERVAL"."TABLE_2.D">0 AND "TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID"))
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=:B1)
Note
-----
- cardinality feedback used for this statement
SQL> alter session set optimizer_features_enable='11.2.0.1';
Session altered.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c5y47k1zmrb22, child number 2
-------------------------------------
Plan hash value: 2179327633
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 320K|00:21:24.30 | 25M| 16M| | | |
| 1 | SORT GROUP BY | | 1 | 1 | 320K|00:21:24.30 | 25M| 16M| 73M| 6303K| 65M (0)|
|* 2 | FILTER | | 1 | | 1579K|00:20:19.75 | 25M| 16M| | | |
| 3 | NESTED LOOPS | | 1 | 234 | 1579K|00:20:17.00 | 25M| 16M| | | |
|* 4 | HASH JOIN | | 1 | 234 | 5446K|00:09:47.69 | 16M| 16M| 1452K| 1452K| 372K (0)|
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 1948 | 20M|00:21:13.17 | 16M| 16M| | | |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 1948 | 20M|00:20:39.18 | 16M| 16M| | | |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 5446K| 1 | 1579K|00:00:51.19 | 9518K| 3216 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
4 - access("TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
5 - access("MARKET"."MARKETID"=:B1)
7 - filter(("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND "STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7
AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND "TABLE_2.NTERVAL"."TABLE_2.D">0))
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=:B1)
##########Second Run Cardinality feedback kicked still same plan with different cardinality on the last step
Plan hash value: 2179327633
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 320K|00:17:51.32 | 25M| 16M| | | |
| 1 | SORT GROUP BY | | 1 | 1 | 320K|00:17:51.32 | 25M| 16M| 73M| 6303K| 65M (0)|
|* 2 | FILTER | | 1 | | 1579K|00:19:42.92 | 25M| 16M| | | |
| 3 | NESTED LOOPS | | 1 | 234 | 1579K|00:19:40.24 | 25M| 16M| | | |
|* 4 | HASH JOIN | | 1 | 234 | 5446K|00:10:22.24 | 16M| 16M| 1452K| 1452K| 419K (0)|
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 1948 | 20M|00:21:33.34 | 16M| 16M| | | |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 1948 | 20M|00:20:59.31 | 16M| 16M| | | |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 5446K| 1 | 1579K|00:00:54.86 | 9518K| 3161 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
4 - access("TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
5 - access("MARKET"."MARKETID"=:B1)
7 - filter(("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND "STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7
AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND "TABLE_2.NTERVAL"."TABLE_2.D">0))
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=:B1)
Note
-----
- cardinality feedback used for this statement
As you see cardinality feedback kicked two times with same plan generated with different cardinalities.
When you check the new 11.2.0.2 plan, when it uses nested loop cardinality suddenly drop to 1 which is a bit suspicios.
Runtime sql statistics
SQL> @sqlid c5y47k1zmrb22 CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH OPT_COST PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS AVG_ELA LIOS PIOS SORTS USERS_EXECUTING ---- ---------------- ---------------- ---------- -------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- 0 000000075E5B37D0 00000009C5E1FEC8 2561977778 1081582 1 1 1 642 320375 633798.646 2034600.72 2034.60072 25789778 16298873 1 0 1 000000075E5B37D0 00000009022773E0 2561977778 1081582 1 1 1 643 320375 607056.710 1424804.27 1424.80427 25136188 16034118 1 0 2 000000075E5B37D0 0000000731957330 2179327633 1082314 1 1 1 642 320375 516728.448 1284256.36 1284.25636 25789785 16266663 1 0 3 000000075E5B37D0 00000008735DB1A8 2179327633 1082314 1 1 1 642 320375 512717.052 1315325.55 1315.32555 25789675 16266877 1 0
Clearly 11.2.0.1 has got better response times. Because the runtime is too long better try our chance if the behaviour is same for explain plan with literals as well
Build the explain plan test case
SQL> @build_optimizer_features_test_e Enter value for base_optimizer_version: 11.2.0.1
Lets run the test
SQL> @optimizer_features_test_e
SQL> alter session set optimizer_features_enable='11.2.0.2';
Session altered.
SQL> set echo off
Explained.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2561977778
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 1 | SORT GROUP BY | | 1 | 75 | 1081K (1)| 01:44:11 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 4 | NESTED LOOPS | | 1 | 64 | 1081K (1)| 01:44:11 | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
5 - access("MARKET"."MARKETID"=14544)
7 - filter("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND
"STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7 AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND
"TABLE_2.NTERVAL"."TABLE_2.D">0 AND "TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=14544)
25 rows selected.
SQL> alter session set optimizer_features_enable='11.2.0.1';
Session altered.
SQL> set echo off
Explained.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2179327633
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 1082K (1)| 01:44:15 | | |
| 1 | SORT GROUP BY | | 1 | 75 | 1082K (1)| 01:44:15 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 234 | 17550 | 1082K (1)| 01:44:15 | | |
|* 4 | HASH JOIN | | 234 | 14976 | 1081K (1)| 01:44:12 | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR| | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
4 - access("TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
5 - access("MARKET"."MARKETID"=14544)
7 - filter("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND
"STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7 AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND
"TABLE_2.NTERVAL"."TABLE_2.D">0)
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=14544)
26 rows selected.
Plans behaviour is same so there is no need for me to run it for every bugfix better I use explain plan testing
Its time to build the bugfix test
SQL> @build_optimizer_bugfix_test_e Enter value for optimizer_feature_version: 11.2.0.2
Time to rock
SQL> @optimizer_bugfix_test_e =>output is truncated and there is break on plan_hash_value
PLAN_HASH_VALUE BUGFIX
-------------------- ----------
2179327633 7641601
2561977778 4926618
5982893
6086930
6236862
6408301
6472966
.......
.......
.......
103 rows selected.
As you see bugfix 7641601 is the guilty guy for us and we found this info under 1 minute. Once we disable it it reverts back to the 11.2.0.1 plan
Lets see what this bug is. Bugfixg is a script where I check bugfix definition from v$system_fix_control
SQL> @bugfixg
Enter value for bugno: 7641601
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ------------------------- ---------------------------------------------------------------- ------------------------- ---------- ----------
7641601 1 QKSFM_CARDINALITY_7641601 treat a single value column as having a frequency histogram 11.2.0.2 0 1
This one looks like a new very interesting bugfix which needs more research from Jonathan Lewis, I don’t feel that much comfortable to understand how it works 🙂
One more thing to prove how enabling disabling the bugfix works
SQL> alter session set "_fix_control"='7641601:OFF';
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2179327633
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 1082K (1)| 01:44:15 | | |
| 1 | SORT GROUP BY | | 1 | 75 | 1082K (1)| 01:44:15 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 234 | 17550 | 1082K (1)| 01:44:15 | | |
|* 4 | HASH JOIN | | 234 | 14976 | 1081K (1)| 01:44:12 | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR| | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1948 | 104K| 1081K (1)| 01:44:12 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
4 - access("TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
5 - access("MARKET"."MARKETID"=14544)
7 - filter("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND
"STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7 AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND
"TABLE_2.NTERVAL"."TABLE_2.D">0)
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=14544)
26 rows selected.
SQL> alter session set "_fix_control"='7641601:ON';
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2561977778
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 1 | SORT GROUP BY | | 1 | 75 | 1081K (1)| 01:44:11 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 75 | 1081K (1)| 01:44:11 | | |
| 4 | NESTED LOOPS | | 1 | 64 | 1081K (1)| 01:44:11 | | |
|* 5 | INDEX RANGE SCAN | INDEX_TABLE_3 | 1 | 9 | 2 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ITERATOR| | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
|* 7 | INDEX FAST FULL SCAN | PK_TABLE_1_XXXXXX | 1 | 55 | 1081K (1)| 01:44:11 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | INDEX_TABLE_2 | 1 | 11 | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmyear')-1>TRUNC(SYSDATE@!,'fmyear')-7)
5 - access("MARKET"."MARKETID"=14544)
7 - filter("DURATION"=INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9) AND
"STARTTIMESTAMP">TRUNC(SYSDATE@!,'fmyear')-7 AND "STARTTIMESTAMP"<TRUNC(SYSDATE@!,'fmyear')-1 AND
"TABLE_2.NTERVAL"."TABLE_2.D">0 AND "TABLE_2.NTERVAL"."SOURCEID"="MARKET"."SOURCEID")
8 - access("TABLE_2.NTERVAL"."TABLE_2.D"="TABLE_2.."TABLE_2.D" AND "TABLE_2.."MARKETID"=14544)
25 rows selected.
I think research for this particular sql is now finished. We found out the problem. We have enough info to stabilize this sql. Rest is checking the effect of statistics with
dynamic sampling and also raising the issue with Oracle which I will also explain later.
I want to share another example with the bugfix I mentioned in this post.
I will skip the initial steps and paste only the output. The query normally runs under 3 seconds in worst condition and has got many binds so I would like to use
build_optimizer_bugfix_test instead of explain plan.
Different optimizer behavior
SQL> @optimizer_features_test
SQL> alter session set optimizer_features_enable='11.2.0.2';
Session altered.
SQL> set echo off
timing for: time_for_ofe_11.2.0.2
Elapsed: 00:00:03.01
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 64xn5ccrd1f9n, child number 0
-------------------------------------
Plan hash value: 2063817604
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.09 | 5308 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE_1_XXXXXXXXXXXX | 1 | 12 | 50 |00:00:00.09 | 5308 |
|* 2 | INDEX SKIP SCAN | IDX_TABLE_1_XXXXXXXXXXXX_05 | 1 | 12 | 50 |00:00:00.45 | 5272 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_1_XXXXXXXXXXXX"."SOURCEID"=:N51)
filter(("TABLE_1_XXXXXXXXXXXX"."SOURCEID"=:N51 AND
INTERNAL_FUNCTION("TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID")))
32 rows selected.
Elapsed: 00:00:00.04
SQL> alter session set optimizer_features_enable='11.2.0.1';
Session altered.
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.01
SQL> set echo off
timing for: time_for_ofe_11.2.0.1
Elapsed: 00:00:00.11
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 64xn5ccrd1f9n, child number 1
-------------------------------------
Plan hash value: 1927123218
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 113 |
| 1 | INLIST ITERATOR | | 1 | | 50 |00:00:00.01 | 113 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE_1_XXXXXXXXXXXX | 50 | 12 | 50 |00:00:00.01 | 113 |
|* 3 | INDEX UNIQUE SCAN | PK_TABLE_1_XXXXXXXXXXXX | 50 | 12 | 50 |00:00:00.01 | 78 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access((("TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N1 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N2 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N3 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N4 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N5 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N6 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N7 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N8 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N9 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N10 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N11 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N12 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N13 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N14 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N15 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N16 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N17 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N18 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N19 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N20 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N21 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N22 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N23 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N24 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N25 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N26 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N27 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N28 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N29 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N30 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N31 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N32 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N33 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N34 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N35 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N36 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N37 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N38 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N39 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N40 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N41 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N42 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N43 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N44 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N45 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N46 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N47 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N48 OR
"TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N49 OR "TABLE_1_XXXXXXXXXXXX"."TABLE_1_XXXXXXXXXXXX_ID"=:N50)) AND
"TABLE_1_XXXXXXXXXXXX"."SOURCEID"=:N51)
SQL> @build_optimizer_bugfix_test
Enter value for optimizer_feature_version: 11.2.0.2
SQL> @optimizer_bugfix_test
PLAN_HASH_VALUE BUGFIX
-------------------- ----------
1927123218 8893626
2063817604 4926618
5982893
6086930
6236862
6408301
6472966
6754080
7215982
7277732
7284269
7641601
.......
.......
.......
.......
103 rows selected.
In under 5 minutes I found out which bugfix is causing the issue
SQL> @bugfixg
Enter value for bugno: 8893626
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ------------------------- ---------------------------------------------------------------- ------------------------- ---------- ----------
8893626 1 QKSFM_ACCESS_PATH_8893626 apply index filter selectivity during skip scan costing 11.2.0.2 0 1
In summary it is not black magic to spot what is causing the plan behaviour change when it comes to bugfixes.
Once you find out you are ready to take control of your plans again. Once you have this valuable information you can get faster responses from Oracle Support on your issue as well.
You will also look cool to your developers when
You can ask, what if my plan is not changing only on 1 bugfix? In that case I would check for the plan I am looking for
and if it exists I still assume thats my bugfix if it does not exist I will follow the upcoming steps.
I will post one more example for the issue I explained above when very old bugfixes suddenly appear on new version.
References Used:
Init.ora Parameter “_FIX_CONTROL” [Hidden] Reference Note [ID 567171.1];
Scripts Used:
All in one zip file
build_optimizer_bugfix_test.sql
build_optimizer_bugfix_test_e.sql
bugfixg.sql
<-Previous Post in Series ——————————- Â Â Next Post in Series–>
Coskan's Approach to Oracle 
[…] <-Previous Post in Series Next Post in Series–> […]
Pingback by Plan stability through Upgrade to 11G- Building a test case « Coskan’s Approach to Oracle — February 14, 2011 @ 12:05 pm
[…] 2-Building the test 3-Why is my plan changed?-bugfixes : how you can find which bug fix may caused your plan change 4-Why is my plan changed?-new optimizer […]
Pingback by Plan stability through Upgrade to 11G-Introduction « Coskan’s Approach to Oracle — February 14, 2011 @ 12:09 pm
Great 🙂 I will do the same tests on my 11.2.0.2. Thank you
Wissem
Comment by orawiss — February 14, 2011 @ 3:08 pm
Hi Coskan,
Excellent post, thank you for sharing. For the past few months I’ve been reading nothing but excellent posts from you.
When PSU05 when out for 11.2, I was playing with fix_control, because I had a severe performance issues and few ORA-600 bugs, but I never got the time to document it 🙂
Regards,
Lazar
Comment by Nakinov — February 14, 2011 @ 8:44 pm
[…] <-Previos Post in Series Next Post in Series–>; […]
Pingback by Plan Stability Through Upgrade-Why is my plan changed?-bugfixes-2 « Coskan’s Approach to Oracle — February 15, 2011 @ 12:03 am
know learn
Plan Stability Through Upgrade-Why is my plan changed?-bugfixes-1 | Coskan’s Approach to Oracle
Trackback by test results — May 17, 2019 @ 7:52 pm