As you might remember from the previous post this Saturday we upgraded one of our databases from 11.2.0.1 to 11.2.0.2. The performance of this DB was not always stable but it was running fine before the upgrade.
As usual we came on Monday and start post upgrade firefighting. server seems to use more CPU then it was using. When I checked the queries which were on CPU, they were nearly all changed their plans (I have awr option to check the plan changes) and interestingly Oracle suddenly decided not to use unique primary keys but other indexes with skip scan option.
What could be done to understand the problem ?
1- build a test case
2- run the test case with 11.2.0.1 and 11.2.0.2 optimizer_features_enable
3- If the behaviour is different then find the bottom of the problem by digging which optimizer_fix could have caused the problem ?
Building a test case is very easy thanks to Kerry Osborne and his amazingly useful (I have no idea how many times it made my day) build_bind_vars script. Normally building a test case for a sql which has 100 bind variables is a nightmare but with that script its just a second.
Once I build the test case I run the sql with different optimizer_features_enable settings and found out that sql goes back to its previous good plan when we run it with 11.2.0.1 optimizer settings
Since we found out what caused the problem in general, we can either move forward to 3th step to find bottom of the problem or change optimizer_features_enable to 11.2.0.1 system wide. We chose the first option to see if it is our main problem and it gave us stability across the system.
Was this acceptable workaround ? Not for me or for business. We upgraded the database and now why not using the 11.2.0.2 features. We needed to find out what fix actually caused.
To find out the actual problem we have two brilliant view called v$system_fix_control and v$session_fix_control.
Looks like we have 105 options to check
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
Lets see what they are
SQL> select * from v$system_fix_control where optimizer_feature_enable='11.2.0.2';
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE
---------- ---------- ---------------------------------- ---------------------------------------------------------------- -----------------
8602840 1 QKSFM_CBO_8602840 kkeRangeJoinSel should avoid using collapsed min/max column stat 11.2.0.2
8725296 1 QKSFM_OR_EXPAND_8725296 try non-driver predicate only if advanced check requested 11.2.0.2
8628970 1 QKSFM_CBO_8628970 group predicates like col op const of 1 NDV col 11.2.0.2
6754080 1 QKSFM_ACCESS_PATH_6754080 enable expression replacement thru views 11.2.0.2
8767442 1 QKSFM_DYNAMIC_SAMPLING_8767442 compute NDV for all columns in a multi-column join key using DS 11.2.0.2
8760135 1 QKSFM_CBO_8760135 allow compile-time peeking of CURRENT_DATE / CURRENT_TIMESTAMP 11.2.0.2
8644935 1 QKSFM_PQ_8644935 new parallelization of concat 11.2.0.2
8352378 1 QKSFM_CBO_8352378 Allow virtual columns in OR expansion index access 11.2.0.2
8685327 1 QKSFM_ACCESS_PATH_8685327 Heuristic to enable full index scan used if table & index analyz 11.2.0.2
8763472 1 QKSFM_PARTITION_8763472 enable parallel nested loop PWJ for ref-part tables 11.2.0.2
8773324 1 QKSFM_CBO_8773324 better recognition of index only plans with virtual columns 11.2.0.2
8813674 1 QKSFM_TRANSFORMATION_8813674 relax restriction for non-nullness check 11.2.0.2
8629716 1 QKSFM_CBO_8629716 enable early replacement for virtual columns 11.2.0.2
7277732 1 QKSFM_CBO_7277732 allow skip scan costing for NL with non-join predicate 11.2.0.2
8692170 1 QKSFM_TRANSFORMATION_8692170 do not apply JF if it prevents JPPD 11.2.0.2
8900973 1 QKSFM_TRANSFORMATION_8900973 relax restriction on inequality join for group-by placement (GBP 11.2.0.2
8919133 1 QKSFM_CBO_8919133 correct nested OR costing bug 11.2.0.2
8551880 1 QKSFM_CBO_8551880 Rewrite decode predicate to join 11.2.0.2
8901237 1 QKSFM_TRANSFORMATION_8901237 correct search of state-space for group-by placement (GBP) 11.2.0.2
6236862 1 QKSFM_FIRST_ROWS_6236862 account for partition-extended names in first rows optimization 11.2.0.2
8528517 1 QKSFM_TRANSFORMATION_8528517 early query edit checks use weak typecheck expression comparison 11.2.0.2
7215982 1 QKSFM_UNNEST_7215982 unnest subquery embedded inside an expression 11.2.0.2
8214022 1 QKSFM_UNNEST_8214022 perform additional CBQT phase for subquery unnesting 11.2.0.2
8595392 1 QKSFM_CBO_8595392 reject join predicate pushdown if parallel access path selected 11.2.0.2
8890233 1 QKSFM_PQ_8890233 enable pushing bloom filter through NLJ 11.2.0.2
8999317 1 QKSFM_UNNEST_8999317 correct cost comparison for the additional phase for JPPD 11.2.0.2
8986163 1 QKSFM_PQ_8986163 align partition-wise gby DOP requirements with partition-wise jo 11.2.0.2
8855396 1 QKSFM_ACCESS_PATH_8855396 sanity check for skip scan costing 11.2.0.2
8800514 20 QKSFM_JPPD_8800514 threshold of table count to disable extended JPPD 11.2.0.2
9007859 1 QKSFM_CBO_9007859 Context index to be picked on XMLType Columns 11.2.0.2
9053879 1 QKSFM_CBO_9053879 search all query blocks for replacement candidates 11.2.0.2
6086930 1 QKSFM_ACCESS_PATH_6086930 correct skip scan selectivity evaluation for BETWEEN predicate 11.2.0.2
7641601 1 QKSFM_CARDINALITY_7641601 treat a single value column as having a frequency histogram 11.2.0.2
9052506 1 QKSFM_UNNEST_9052506 Allow NAAJ for UPDATE and DELETE 11.2.0.2
9047975 1 QKSFM_CBO_9047975 improve selectivity for truncated character strings 11.2.0.2
8893626 0 QKSFM_ACCESS_PATH_8893626 apply index filter selectivity during skip scan costing 11.2.0.2
9111170 1 QKSFM_TRANSFORMATION_9111170 allow subquery to appear on left side when generating inline vie 11.2.0.2
8971829 1 QKSFM_ACCESS_PATH_8971829 set KKEGIXSL for unpartitioned global index 11.2.0.2
9125151 1 QKSFM_STATS_9125151 produce more accurate stats for olap when stats has not been run 11.2.0.2
9106224 1 QKSFM_PQ_9106224 allow range distribution in create of global partitioned index 11.2.0.2
9185228 1 QKSFM_UNNEST_9185228 move correlated filters from subquery to outer query 11.2.0.2
9206747 1 QKSFM_CBO_9206747 share metadata for virtual columns when making copy 11.2.0.2
9088510 1 QKSFM_TRANSFORMATION_9088510 compress predicate tree before cost-based query transformation 11.2.0.2
9143856 1 QKSFM_TRANSFORMATION_9143856 uncorrelated OR-ed unary predicates are OK for unnesting 11.2.0.2
8949971 1 QKSFM_STATS_8949971 make stats for leaf level data appear very large in COTs 11.2.0.2
9148171 1 QKSFM_TRANSFORMATION_9148171 allow distinct elim & distinct aggr transform for non-select stm 11.2.0.2
8706652 1 QKSFM_CBO_8706652 fix for 7449971 not complete 11.2.0.2
9245114 1 QKSFM_TABLE_ELIM_9245114 eliminate redundant join predicates in join elimination 11.2.0.2
9011016 1 QKSFM_ACCESS_PATH_9011016 use 1/NDV+1/NROWS for col1 LIKE col2 selectivities (index driver 11.2.0.2
9265681 1 QKSFM_CARDINALITY_9265681 sanity check for derived ndv/cdn for inner join on range 11.2.0.2
7284269 1 QKSFM_CURSOR_SHARING_7284269 extended cursor sharing for like predicates 11.2.0.2
9272549 1 QKSFM_DYNAMIC_SAMPLING_9272549 do not sample columns which have statistics 11.2.0.2
8531463 1 QKSFM_ACCESS_PATH_8531463 cost cutoff for bitmap OR chains based on best table access cost 11.2.0.2
9263333 1 QKSFM_CBO_9263333 generate transitive predicates for virtual column expressions 11.2.0.2
8896955 1 QKSFM_TRANSFORMATION_8896955 interleave TE with ST and JF 11.2.0.2
9041934 1 QKSFM_EXECUTION_9041934 use selected measures for AW LOOP OPTIMIZED looping strategy 11.2.0.2
9344709 1 QKSFM_PQ_9344709 disable parallel execution for a qb with a constant false pred 11.2.0.2
9024933 1 QKSFM_JPPD_9024933 Do not allow Old JPPD for OJ view with window function 11.2.0.2
9033718 1 QKSFM_TABLE_EXPANSION_9033718 remove restriction on bind variables for table expansion 11.2.0.2
9081848 1 QKSFM_CBO_9081848 don't use fake index stats as extended stats 11.2.0.2
5982893 1 QKSFM_SQL_CODE_GENERATOR_5982893 compact row vector of colocated join 11.2.0.2
9287401 1 QKSFM_TRANSFORMATION_9287401 full outer join to outer join conversion 11.2.0.2
8590021 1 QKSFM_CBO_8590021 using col stats for pred NVL() const selectivity estimation 11.2.0.2
9340120 1 QKSFM_CBO_9340120 derive stats for sys generated UA view selectivity estimation 11.2.0.2
9355794 1 QKSFM_CBO_9355794 clear sort merge joins paths noted in apafjo 11.2.0.2
9385634 1 QKSFM_EXECUTION_9385634 always return error for null end point expression 11.2.0.2
9069046 1 QKSFM_CBO_9069046 amend histogram column tracking for multicolumn stats 11.2.0.2
9239337 1 QKSFM_CBO_9239337 eliminate unreferenced subqueries after view merging 11.2.0.2
9298010 1 QKSFM_PARTITION_9298010 enable pruning for partitioned IOT rowid predicates 11.2.0.2
8836806 1 QKSFM_CBO_8836806 push rownum predicate into sortable domain index 11.2.0.2
9344055 1 QKSFM_CBO_9344055 Control the memory used during query optimization 11.2.0.2
9203723 1 QKSFM_SQL_CODE_GENERATOR_9203723 allow bloom pruning and bloom filtering on the same join 11.2.0.2
9443476 1 QKSFM_CBO_9443476 set OPNF2NOSEL bit for auto generated virtual column predicates 11.2.0.2
9195582 1 QKSFM_ACCESS_PATH_9195582 leaf blocks as upper limit for skip scan blocks 11.2.0.2
9433490 1 QKSFM_CBO_9433490 enable VC replacement for nest operator 11.2.0.2
9303766 1 QKSFM_ACCESS_PATH_9303766 use 1/NDV+1/NROWS for col1 LIKE col2 selectivities (table access 11.2.0.2
9437283 1 QKSFM_CBO_9437283 Do not consider NLJ from JPPD when checking for FCP 11.2.0.2
9116214 1 QKSFM_ACCESS_PATH_9116214 index filter ordering 11.2.0.2
9456688 1 QKSFM_ACCESS_PATH_9456688 account for to_number/to_char cost after temp conversion 11.2.0.2
9342979 1 QKSFM_CURSOR_SHARING_9342979 disable cardinality feedback for old JPPD 11.2.0.2
9465425 1 QKSFM_CURSOR_SHARING_9465425 correct hint for index join from cardinality feedback 11.2.0.2
9092442 1 QKSFM_DML_9092442 equi-partition load 11.2.0.2
4926618 1 QKSFM_EXECUTION_4926618 do not use hash unique for subqueries in update-set expression 11.2.0.2
8792846 1 QKSFM_STAR_TRANS_8792846 use _optimizer_star_trans_min_ratio even if ST not hinted 11.2.0.2
9474259 1 QKSFM_CBO_9474259 use cdn sanity check when unique colgroup on both sides of join 11.2.0.2
6472966 1 QKSFM_PARTITION_6472966 load and use statistics for hash sub-partitions 11.2.0.2
6408301 1 QKSFM_DYNAMIC_SAMPLING_6408301 use recursive idx selectivity for partitioned table as well 11.2.0.2
8500130 1 QKSFM_EXECUTION_8500130 enable rownum optimization without partition pushup 11.2.0.2
9584723 1 QKSFM_INDEX_JOIN_9584723 enable functional indexes for index join 11.2.0.2
9593680 1 QKSFM_CBO_9593680 fix typo in cost computation of subquery filters 11.2.0.2
9309281 1 QKSFM_JPPD_9309281 outer join JPPD allowed for function-based index access path 11.2.0.2
8693158 1 QKSFM_JPPD_8693158 consider pushing if only shared predicates to push 11.2.0.2
9381638 1 QKSFM_PQ_9381638 Treat CP differently from NLJ for parallel optimizations 11.2.0.2
9383967 1 QKSFM_COMPILATION_9383967 Allow unique (ie. select distinct) pushdown 11.2.0.2
7711900 1 QKSFM_CBQT_7711900 copy query block text position fields in copy service 11.2.0.2
9218587 1 QKSFM_CARDINALITY_9218587 Don't use column density for selectivity with 1-bucket histogram 11.2.0.2
9728438 1 QKSFM_CBO_9728438 don't go parallel if no operations are expensive enough 11.2.0.2
9577300 1 QKSFM_CBO_9577300 Improve range join selectivity for predicates with round 11.2.0.2
9171113 1 QKSFM_CBO_9171113 consider parallel cost for partition bitmap table access by rowi 11.2.0.2
8973745 1 QKSFM_SQL_PLAN_MANAGEMENT_8973745 auto-capture only if literal replaced SQL parses recursively 11.2.0.2
9102474 1 QKSFM_CBO_9102474 use IO calibrate statistics to estimate time from cost 11.2.0.2
9243499 1 QKSFM_CBO_9243499 relax conditions for logical antijoin 11.2.0.2
9912503 1 QKSFM_TRANSFORMATION_9912503 Remove having clause subquery at all levels 11.2.0.2
9153459 1 QKSFM_TABLE_ELIM_9153459 allow loading of rely constraints for all statement types 11.2.0.2
9762592 3 QKSFM_PQ_9762592 fold bloom filter when offload to storage 1
Lets shoot in the dark
SQL> select * from v$system_fix_control where optimizer_feature_enable='11.2.0.2' and description like '%skip%';
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------
7277732 1 QKSFM_CBO_7277732 allow skip scan costing for NL with non-join predicate 11.2.0.2
8855396 1 QKSFM_ACCESS_PATH_8855396 sanity check for skip scan costing 11.2.0.2
6086930 1 QKSFM_ACCESS_PATH_6086930 correct skip scan selectivity evaluation for BETWEEN predicate 11.2.0.2
8893626 1 QKSFM_ACCESS_PATH_8893626 apply index filter selectivity during skip scan costing 11.2.0.2
9195582 1 QKSFM_ACCESS_PATH_9195582 leaf blocks as upper limit for skip scan blocks 11.2.0.2
Lets focus on those fixes
In the stack below test4.sql has got the sql causing problem and what I do is altering the session to disable the fix in question and keep it disabled while my current session is under 11.2.0.2 . (For data security I could not add the sql and table names. )
SQL> alter session set optimizer_features_enable='11.2.0.2'; Session altered. SQL> alter session set "_fix_control"='8855396:OFF'; Session altered. SQL> @test4.sql Plan hash value: 2840796242 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:01.09 | 5404 | | 1 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:01.09 | 5404 | | 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:01.09 | 5404 | |* 3 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXXXXXXXXXXXX | 1 | 1 | 0 |00:00:01.09 | 5404 | |* 4 | INDEX SKIP SCAN | IDX_TABLE_XXXXXXXXXXXXXX_02 | 1 | 1 | 50 |00:00:00.07 | 5317 | | 5 | TABLE ACCESS BY INDEX ROWID| TABLE_YYYYYYYYYYYYYYYYYY | 0 | 1 | 0 |00:00:00.01 | 0 | |* 6 | INDEX UNIQUE SCAN | PK_ZZZZZZZZZZZZZZZZZZZ | 0 | 1 | 0 |00:00:00.01 | 0 | | 7 | TABLE ACCESS BY INDEX ROWID | TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | |* 8 | INDEX UNIQUE SCAN | PK_TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------- 48 rows selected. SQL> SQL> alter session set "_fix_control"='6086930:OFF'; Session altered. SQL> @test4.sql PL/SQL procedure successfully completed. Plan hash value: 2840796242 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:01.11 | 5404 | | 1 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:01.11 | 5404 | | 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:01.11 | 5404 | |* 3 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXXXXXXXXXXXX | 1 | 1 | 0 |00:00:01.11 | 5404 | |* 4 | INDEX SKIP SCAN | IDX_TABLE_XXXXXXXXXXXXXX_02 | 1 | 1 | 50 |00:00:00.07 | 5317 | | 5 | TABLE ACCESS BY INDEX ROWID| TABLE_YYYYYYYYYYYYYYYYYY | 0 | 1 | 0 |00:00:00.01 | 0 | |* 6 | INDEX UNIQUE SCAN | PK_ZZZZZZZZZZZZZZZZZZZ | 0 | 1 | 0 |00:00:00.01 | 0 | | 7 | TABLE ACCESS BY INDEX ROWID | TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | |* 8 | INDEX UNIQUE SCAN | PK_TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------- SQL> SQL> alter session set "_fix_control"='8893626:OFF'; Session altered. SQL> @test4.sql PL/SQL procedure successfully completed. Plan hash value: 3290908244 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.08 | 178 | 15 | | 1 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.08 | 178 | 15 | | 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.08 | 178 | 15 | | 3 | INLIST ITERATOR | | 1 | | 0 |00:00:00.08 | 178 | 15 | |* 4 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXXXXXXXXXXXX | 50 | 1 | 0 |00:00:00.08 | 178 | 15 | |* 5 | INDEX UNIQUE SCAN | PK_TABLE_XXXXXXXXXXXXXX | 50 | 50 | 50 |00:00:00.08 | 91 | 15 | | 6 | TABLE ACCESS BY INDEX ROWID | TABLE_YYYYYYYYYYYYYYYYYY | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |* 7 | INDEX UNIQUE SCAN | PK_ZZZZZZZZZZZZZZZZZZZ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | 8 | TABLE ACCESS BY INDEX ROWID | TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |* 9 | INDEX UNIQUE SCAN | PK_TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | ------------------------------------------------------------------------------------------------------------------------------ SQL> SQL> alter session set "_fix_control"='9195582:OFF'; Session altered. SQL> @test4.sql --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 178 | | 1 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 178 | | 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 178 | | 3 | INLIST ITERATOR | | 1 | | 0 |00:00:00.01 | 178 | |* 4 | TABLE ACCESS BY INDEX ROWID| TABLE_XXXXXXXXXXXXXX | 50 | 1 | 0 |00:00:00.01 | 178 | |* 5 | INDEX UNIQUE SCAN | PK_TABLE_XXXXXXXXXXXXXX | 50 | 50 | 50 |00:00:00.01 | 91 | | 6 | TABLE ACCESS BY INDEX ROWID | TABLE_YYYYYYYYYYYYYYYYYY | 0 | 1 | 0 |00:00:00.01 | 0 | |* 7 | INDEX UNIQUE SCAN | PK_ZZZZZZZZZZZZZZZZZZZ | 0 | 1 | 0 |00:00:00.01 | 0 | | 8 | TABLE ACCESS BY INDEX ROWID | TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | |* 9 | INDEX UNIQUE SCAN | PK_TABLE_TTTTTTTTTTTT | 0 | 1 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------
As you can see we hit the jackpot when we disabled the fix 8893626. Our Table called TABLE_XXXXXXXXXXXXXX started to use PK_TABLE_XXXXXXXXXXXXXX instead of IDX_TABLE_XXXXXXXXXXXXXX_02 and ran faster.
this was a quick teaser post I will talk about this technique (you may think dodgy) further in the future
I may also add test case if I have time.
FootNote: This fix was for immediate action. Gathering stats is not helping us so we needed to apply it. I am not the big fun of these changes and as you see they are undocumented please do it with asking Oracle as we are doing at the moment.
For more about the bug
Bug 8893626 – Index Skip Scan with selective index filter predicate has high cost [ID 8893626.8]
For more about the optimizer_fix_control
Init.ora Parameter “_FIX_CONTROL” [Hidden] Reference Note [ID 567171.1]
http://el-caro.blogspot.com/2007/06/fix-control.html
http://jonathanlewis.wordpress.com/2009/12/22/optimizer-features/
Update – 24/01/11 – We now have an official bug for our issue
Bug 11672675: INDEX SKIP SCAN IS USED INAPPROPR. AFTER UPGR. FROM 11.2.0.1 -> 11.2.0.2
Coskan's Approach to Oracle 
Hi Coskan,
thanks for sharing this information which is useful for our future upgrades.
BTW, the property fix_control can be used not only with alter session but also in hints:
/*+ OPT_PARAM(‘_fix_control’ ‘9195582:0’) */
Regards,
Martin Decker – OCM11g
ora-solutions.net
Comment by Martin Decker — January 17, 2011 @ 1:38 pm
Thank you for your comments Martin. I know that we can use it as hint but the problem for us was that we could not touch the code. One thing we could try is tweak it with a baseline but then it becomes managing many queries with baselines nightmare. There isn’t just a simple solution 😦
Comment by coskan — January 17, 2011 @ 1:43 pm
Nice post Coskan.
Comment by Dom Brooks — January 17, 2011 @ 3:04 pm
Coskan
Thanks for a straightforward and informative blog post. I hadn’t picked up on this technique – even though its quite old now – a much finer grained control than optimizer features enable.
Comment by Niall Litchfield — January 17, 2011 @ 4:33 pm
“I have awr option to check the plan changes”
Could you please let me know how you did this?
Comment by Yasser — January 17, 2011 @ 5:13 pm
I use this h_sql.sql
but you can also use Kerry Osbornes script
http://kerryosborne.oracle-guy.com/scripts/awr_plan_change.sql
Comment by coskan — January 17, 2011 @ 5:32 pm
Good stuff!
Comment by Tanel Poder — January 17, 2011 @ 6:14 pm
Nice Post !
Comment by Jagjeet Singh — January 17, 2011 @ 7:25 pm
[…] a very useful posting from Coskan Gundogar about tracking down a problem to do with an 11g […]
Pingback by Fix Control « Oracle Scratchpad — January 28, 2011 @ 3:35 pm
Great post, Coskan. Thank you for this valuable information.
Comment by Arup Nanda — January 28, 2011 @ 3:42 pm
Another great post from Coskan.
Comment by Evren — January 28, 2011 @ 4:47 pm
[…] Nice blog post about a dbas approach to solving query problems (plan changes) after upgrade from 11.2.0.1 to 11.2.0.2. Â Find it here. […]
Pingback by 11.2.0.1 to 11.2.0.2 upgrade performance issues « Oracle Notes — January 28, 2011 @ 5:28 pm
Great Post Coskan.Thank you for sharing
Comment by ghassem — January 28, 2011 @ 8:15 pm
This is a very helpful article for everyone who wants to troubleshoot performance issues.
I have a question:
You mentioned that you used a script to find out changes in execution plan by using Kerry’s script. I downloaded the script and executed.
With that script, we know when the PLAN_HASH_VALUE is changed for a given SQL_ID. But how can we know the execution plan for these two different plan hash values?
Thank You
Giridhar
Comment by Giridhar — January 28, 2011 @ 9:27 pm
Hi there,
glad that you liked the post because more are on their way.
For your question for spesific plan_hash_value I use below. you can chance ADVANCED to BASIC
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR((‘&sql_id’),&plan_hash,null,’ADVANCED’));
for seeing all historic plans at one time I use this one which has truncated info for execution plan
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR((‘&sql_id’),null,null,’TYPICAL PROJECTION’));
Basically your package is DBMS_XPLAN.DISPLAY_AWR
Comment by Coskan Gundogar — January 28, 2011 @ 9:35 pm
Coskan,
Perfect timing. This is valuable information as we plan to upgrade to 11.2.0.2 next month. It’s good to know that there is a patch available already.
Ittichai
Comment by ittichai chammavanijakul — January 28, 2011 @ 10:31 pm
[…] I want to share another example with the bugfix I mentioned in this post. […]
Pingback by Plan Stability Through Upgrade-Why is my plan changed?-bugfixes-1 « Coskan’s Approach to Oracle — February 14, 2011 @ 12:00 pm
Hello Coskan,
Thanks for this post, it turns out to be a useful one since we are planning to upgrade to 11.2.0.2 version sometime next month.
We are using Oracle 10.2.0.4 at the moment and work on Oracle APEX applications. Does this in any way have an impact on this kind of a setup i.e. ORACLE APEX + ORACLE DB (Please note that we wont be upgrading Oracle APEX, it will be version 3.2 only)
And the above bug, is it generic? Has Oracle identified this and patched it up ?
Comment by Raja Shahnawaz Soni — June 1, 2011 @ 6:53 am
Thank you sir, you just saved me a large headache.
Comment by fred — July 29, 2011 @ 6:15 pm
Brilliant Post. I was looking for something like this.
Kind Regards,
Harman
Comment by Harmandeep Singh — November 21, 2011 @ 6:33 pm
Really its a great article… Thaxs Sir for this information
Comment by Amit — July 23, 2012 @ 6:33 am
[…] https://coskan.wordpress.com/2011/01/17/11-2-0-2-performance-stories/ […]
Pingback by Sudden Performance Change after Upgrade | oraclebits — December 10, 2013 @ 1:41 pm
Thanks for the post. I started using SQLT Xplore method which is a brute force method of checking for CBO parameter and fix control bugs. Takes long time to run the test for one SQL.
So I used system fix control view to narrow down bugs to 11.2.0.4 … I had to hack the XPLORE method code though.
Anyways, thanks for the idea of using fix control.
Comment by Uday — February 7, 2014 @ 3:58 pm
It’s genuinely very difficult in this busy life to listen news on TV,
therefore I only use internet for that reason, and obtain the most recent news.
Comment by Charlie C. Urtiaga — September 26, 2014 @ 4:59 pm
Woah! I’m really enjoying the template/theme of this website.
It’s simple, yet effective. A lot of times it’s tough
to get that “perfect balance” between superb usability and appearance.
I must say that you’ve done a fantastic job with this. Additionally, the blog loads very fast for me on Chrome.
Outstanding Blog!
Comment by Keena Q. Fiesel — October 11, 2014 @ 7:32 am
[…] https://coskan.wordpress.com/2011/01/17/11-2-0-2-performance-stories/ […]
Pingback by Sudden Performance Change after database upgrade – oraclebits — October 18, 2016 @ 7:36 am
[…] years ago Coskan Gundogar wrote a note about checking v$system_fix_control to see if it gave any clues about unexpected […]
Pingback by 19c Trivia | Oracle Scratchpad — January 12, 2022 @ 11:18 am