Every Oracle version, developers at Oracle add or change some of the undocumented parameters to do better optimization. Most of the time these new optimizations
works fine but from time to time they have a negative effect for the generated plan which causes post upgrade slowness. When you change OFE (optimizer_features_enable)
from 10.2.0.4 to 11.2.0.1 Oracle changes value of 33 parameters !!! Because they are undocumented, unless an Oracle Scientist reveals what they are actually doing
or note on MOS explaining the parameter, their effects to the plans are not that clear.
To overcome this problem I again wrote couple of simple scripts to automate the process of what is actually changing our plan. They really helped me during the upgrades
and understanding of plans. Again purpose of the post is not about telling deeply how these parameters are effecting plans but how we can find what parameter is effecting.
First script is
build_opt_param_table: It needs to be run as root or a user which has read access to x$ksppi and x$ksppcv tables.
What this script does is, it accepts a base OFE version then creates a table (optimizer_parameters) for all the parameters for given OFE version
and OFE versions above. This script is the prerequsite for other scripts in this post because they will all use optimizer_parameters table.
!!! In the script it creates the public synonym and grants select to dba.
First lets build the optimizer_parameters table
SQL> @build_opt_param_table Enter value for optimizer_feature_version: 10.2.0.4 SQL> grant select on optimizer_parameters to DBA; Grant succeeded. SQL> create public synonym optimize_parameters for optimizer_parameters; Synonym created. SQL> select ofe,count(*) from optimizer_parameters group by OFE; OFE COUNT(*) ----------------- ---------- 10.2.0.4 2399 10.2.0.5 2399 11.1.0.6 2399 11.1.0.7 2399 11.2.0.1 2399
Once our table is generated we can see the differences between OFE versions
before_after_opt_param: This script is just checking which values are different for given to OFE versions
Changes between 10.2.0.4 to 10.2.0.5
SQL> @before_after_opt_param Enter value for before_ofe: 10.2.0.4 Enter value for after_ofe: 10.2.0.5 PD_NAME BEFORE_OFE_VALUE AFTER_OFE_VALUE DESCRIPTION -------------------------------------------------- -------------------- -------------------- ---------------------------------------------------- optimizer_features_enable 10.2.0.4 10.2.0.5 optimizer plan compatibility parameter _optimizer_undo_cost_change 10.2.0.4 10.2.0.5 optimizer undo cost change
Changes between 10.2.0.4 to 11.1.0.6
SQL> @before_after_opt_param Enter value for before_ofe: 10.2.0.4 Enter value for after_ofe: 11.1.0.6 PD_NAME BEFORE_OFE_VALUE AFTER_OFE_VALUE DESCRIPTION -------------------------------------------------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------- optimizer_features_enable 10.2.0.4 11.1.0.6 optimizer plan compatibility parameter _replace_virtual_columns FALSE TRUE replace expressions with virtual columns _optimizer_undo_cost_change 10.2.0.4 11.1.0.6 optimizer undo cost change _optimizer_null_aware_antijoin FALSE TRUE null-aware antijoin parameter _optimizer_native_full_outer_join OFF FORCE execute full outer join using native implementaion _optimizer_multi_level_push_pred FALSE TRUE consider join-predicate pushdown that requires multi-level pushdown to base table _optimizer_improve_selectivity FALSE TRUE improve table and partial overlap join selectivity computation _optimizer_group_by_placement FALSE TRUE consider group-by placement optimization _optimizer_extended_stats_usage_control 255 240 controls the optimizer usage of extended stats _optimizer_extended_cursor_sharing_rel NONE SIMPLE optimizer extended cursor sharing for relational operators _optimizer_extend_jppd_view_types FALSE TRUE join pred pushdown on group-by, distinct, semi-/anti-joined view _optimizer_enable_extended_stats FALSE TRUE use extended statistics for selectivity estimation _optimizer_enable_density_improvements FALSE TRUE use improved density computation for selectivity estimation _optimizer_adaptive_cursor_sharing FALSE TRUE optimizer adaptive cursor sharing _nlj_batching_enabled 0 1 enable batching of the RHS IO in NLJ _first_k_rows_dynamic_proration FALSE TRUE enable the use of dynamic proration of join cardinalities _bloom_pruning_enabled FALSE TRUE Enable partition pruning using bloom filtering 17 rows selected.
Changes between 11.1.0.6 to 11.1.0.7
SQL> @before_after_opt_param Enter value for before_ofe: 11.1.0.6 Enter value for after_ofe: 11.1.0.7 PD_NAME BEFORE_OFE_VALUE AFTER_OFE_VALUE DESCRIPTION -------------------------------------------------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------- optimizer_features_enable 11.1.0.6 11.1.0.7 optimizer plan compatibility parameter _optimizer_undo_cost_change 11.1.0.6 11.1.0.7 optimizer undo cost change _optimizer_extended_stats_usage_control 240 224 controls the optimizer usage of extended stats _bloom_folding_enabled FALSE TRUE Enable folding of bloom filter
Changes between 11.1.0.7 to 11.2.0.1
SQL> @before_after_opt_param Enter value for before_ofe: 11.1.0.7 Enter value for after_ofe: 11.2.0.1 PD_NAME BEFORE_OFE_VALUE AFTER_OFE_VALUE DESCRIPTION -------------------------------------------------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------- optimizer_features_enable 11.1.0.7 11.2.0.1 optimizer plan compatibility parameter _optimizer_use_feedback FALSE TRUE optimizer use feedback _optimizer_use_cbqt_star_transformation FALSE TRUE use rewritten star transformation using cbqt framework _optimizer_unnest_disjunctive_subq FALSE TRUE Unnesting of disjunctive subqueries (TRUE/FALSE) _optimizer_unnest_corr_set_subq FALSE TRUE Unnesting of correlated set subqueries (TRUE/FALSE) _optimizer_undo_cost_change 11.1.0.7 11.2.0.1 optimizer undo cost change _optimizer_try_st_before_jppd FALSE TRUE try Star Transformation before Join Predicate Push Down _optimizer_table_expansion FALSE TRUE consider table expansion transformation _optimizer_join_factorization FALSE TRUE use join factorization transformation _optimizer_fast_pred_transitivity FALSE TRUE use fast algorithm to generate transitive predicates _optimizer_fast_access_pred_analysis FALSE TRUE use fast algorithm to traverse predicates for physical optimizer _optimizer_eliminate_filtering_join FALSE TRUE optimizer filtering join elimination enabled _optimizer_distinct_placement FALSE TRUE consider distinct placement optimization _optimizer_distinct_agg_transform FALSE TRUE Transforms Distinct Aggregates to non-distinct aggregates _optimizer_connect_by_elim_dups FALSE TRUE allow connect by to eliminate duplicates from input _optimizer_coalesce_subqueries FALSE TRUE consider coalescing of subqueries optimization _connect_by_use_union_all OLD_PLAN_MODE TRUE use union all for connect by _and_pruning_enabled FALSE TRUE allow partition pruning based on multiple mechanisms _aggregation_optimization_settings 32 0 settings for aggregation optimizations 19 rows selected.
As you might expect changes between minor releases are very small numbers but major release changes really effects the way CBO works.
Next script is for using the information above as a test case. They have the similar mind as bugfix test builders.
build_optimizer_param_test: accepts before and after OFE. you can make the test by changing parameters one by one do the test and revert back to the original.
Even it accepts the OFE as before and after you can reverse the order like after can be 10.2.0.4 and before can be 11.2.0.1. What the script will do is
set OFE to 11.2.0.1 and try each parameter by reverting them back to 10.2.0.4 values. Generated scripts is optimizer_parameter_test.sql and test.sql is the
script it calls for tests.
build_optimizer_param_test_e: Same as above but this time it is for explain plan only and generates optimizer_parameter_test_e.sql and uses test_e.sql
Now its time to play with the scripts
After the upgrade one of the massive queries again start to go crazy. This time I have the chance to build a test case.
We have two tables monthly partitioned on ASOF date. Once we use ansi “full outer join” syntax Oracle was not doing partition pruning
after upgrade from 10.2.0.4 to 11.2.0.1.
The full test case can be seen here full_outer_join_test_case.sql.
The test_e.sql is like below
EXPLAIN PLAN
FOR
SELECT T1.asof asof,
T1.t1_id,
t1.t1_data,
T2.t2_data
FROM table_1 t1
FULL OUTER JOIN
(SELECT *
FROM table_2
WHERE table_2.asof = TO_DATE ('15Sep10', 'DDMONYY')) t2
ON t1.asof = t2.asof
WHERE t1.asof = TO_DATE ('15Sep10', 'DDMONYY');
Lets see how our test case behaves with different OFE settings
HR@ORACOS> @test_e =>default OFE is 11.2.0.1
HR@ORACOS> @e
Plan hash value: 1183327479
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1991K| 7644M| 16343 (1)| 00:01:22 | | |
|* 1 | VIEW | VW_FOJ_0 | 1991K| 7644M| 16343 (1)| 00:01:22 | | |
|* 2 | HASH JOIN FULL OUTER | | 1991K| 4348M| 16343 (1)| 00:01:22 | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
| 4 | VIEW | | 1 | 2011 | 1 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T2 | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | TEST2_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | PARTITION RANGE ALL | | 1991K| 529M| 16329 (1)| 00:01:22 | 1 | 28 |
| 8 | TABLE ACCESS FULL | T1 | 1991K| 529M| 16329 (1)| 00:01:22 | 1 | 28 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
2 - access("T1"."ASOF"="T2"."ASOF")
6 - access("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
HR@ORACOS> alter session set optimizer_features_enable='10.2.0.4';
Session altered.
HR@ORACOS> @test_e
Explained.
HR@ORACOS> @e
Plan hash value: 2581814110
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8052 | 1 (100)| 00:00:01 | | |
| 1 | VIEW | | 2 | 8052 | 1 (100)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | HASH JOIN OUTER | | 1 | 4035 | 1 (100)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 1 | 2024 | 0 (0)| 00:00:01 | KEY | KEY |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID | T1 | 1 | 2024 | 0 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | TEST1_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
| 7 | PARTITION RANGE SINGLE | | 1 | 2011 | 0 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | T2 | 1 | 2011 | 0 (0)| 00:00:01 | KEY | KEY |
|* 9 | INDEX SKIP SCAN | TEST2_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
|* 10 | FILTER | | | | | | | |
| 11 | NESTED LOOPS ANTI | | 1 | 2020 | 0 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE SINGLE | | 1 | 2011 | 0 (0)| 00:00:01 | KEY | KEY |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID| T2 | 1 | 2011 | 0 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX SKIP SCAN | TEST2_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY |
| 15 | PARTITION RANGE SINGLE | | 1 | 9 | 0 (0)| 00:00:01 | KEY | KEY |
|* 16 | INDEX FULL SCAN | TEST1_PK | 1 | 9 | 0 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ASOF"="T2"."ASOF"(+))
6 - access("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
9 - access("T2"."ASOF"(+)=TO_DATE('15Sep10','DDMONYY'))
filter("T2"."ASOF"(+)=TO_DATE('15Sep10','DDMONYY'))
10 - filter(CAST(NULL AS DATE)=TO_DATE('15Sep10','DDMONYY'))
14 - access("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
16 - access("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
As you can clearly see, partition pruning is not happening on 11.2.0.1 and query suddenly becomes killer for the system and slower for end user.
Lets see what is changed in terms of parameters between the releases?
HR@ORACOS> @before_after_opt_param Enter value for before_ofe: 10.2.0.4 Enter value for after_ofe: 11.2.0.1 PD_NAME BEFORE_OFE_VALUE AFTER_OFE_VALUE DESCRIPTION -------------------------------------------------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------- optimizer_features_enable 10.2.0.4 11.2.0.1 optimizer plan compatibility parameter _replace_virtual_columns FALSE TRUE replace expressions with virtual columns _optimizer_use_feedback FALSE TRUE optimizer use feedback _optimizer_use_cbqt_star_transformation FALSE TRUE use rewritten star transformation using cbqt framework _optimizer_unnest_disjunctive_subq FALSE TRUE Unnesting of disjunctive subqueries (TRUE/FALSE) _optimizer_unnest_corr_set_subq FALSE TRUE Unnesting of correlated set subqueries (TRUE/FALSE) _optimizer_undo_cost_change 10.2.0.4 11.2.0.1 optimizer undo cost change _optimizer_try_st_before_jppd FALSE TRUE try Star Transformation before Join Predicate Push Down _optimizer_table_expansion FALSE TRUE consider table expansion transformation _optimizer_null_aware_antijoin FALSE TRUE null-aware antijoin parameter _optimizer_native_full_outer_join OFF FORCE execute full outer join using native implementaion _optimizer_multi_level_push_pred FALSE TRUE consider join-predicate pushdown that requires multi-level pushdown to base table _optimizer_join_factorization FALSE TRUE use join factorization transformation _optimizer_improve_selectivity FALSE TRUE improve table and partial overlap join selectivity computation _optimizer_group_by_placement FALSE TRUE consider group-by placement optimization _optimizer_fast_pred_transitivity FALSE TRUE use fast algorithm to generate transitive predicates _optimizer_fast_access_pred_analysis FALSE TRUE use fast algorithm to traverse predicates for physical optimizer _optimizer_extended_stats_usage_control 255 224 controls the optimizer usage of extended stats _optimizer_extended_cursor_sharing_rel NONE SIMPLE optimizer extended cursor sharing for relational operators _optimizer_extend_jppd_view_types FALSE TRUE join pred pushdown on group-by, distinct, semi-/anti-joined view _optimizer_enable_extended_stats FALSE TRUE use extended statistics for selectivity estimation _optimizer_enable_density_improvements FALSE TRUE use improved density computation for selectivity estimation _optimizer_eliminate_filtering_join FALSE TRUE optimizer filtering join elimination enabled _optimizer_distinct_placement FALSE TRUE consider distinct placement optimization _optimizer_distinct_agg_transform FALSE TRUE Transforms Distinct Aggregates to non-distinct aggregates _optimizer_connect_by_elim_dups FALSE TRUE allow connect by to eliminate duplicates from input _optimizer_coalesce_subqueries FALSE TRUE consider coalescing of subqueries optimization _optimizer_adaptive_cursor_sharing FALSE TRUE optimizer adaptive cursor sharing _nlj_batching_enabled 0 1 enable batching of the RHS IO in NLJ _first_k_rows_dynamic_proration FALSE TRUE enable the use of dynamic proration of join cardinalities _connect_by_use_union_all OLD_PLAN_MODE TRUE use union all for connect by _bloom_pruning_enabled FALSE TRUE Enable partition pruning using bloom filtering _bloom_folding_enabled FALSE TRUE Enable folding of bloom filter _and_pruning_enabled FALSE TRUE allow partition pruning based on multiple mechanisms _aggregation_optimization_settings 32 0 settings for aggregation optimizations 35 rows selected.
35 parameters changed and we already now bugfix is not our problem.
Lets build our test case and run it. (check the time, only 0.1 seconds to understand which parameter is driving my problem)
!!! I am testing 11.2.0.1 parameters when I am on 10.2.0.4
HR@ORACOS> @build_optimizer_param_test_e Enter value for before_ofe: 10.2.0.4 Enter value for after_ofe: 11.2.0.1 HR@ORACOS> @optimizer_param_test_e PD_NAME PD_VALUE PLAN_HASH_VALUE -------------------------------------------------- ---------- ------------------ _optimizer_native_full_outer_join FORCE 1183327479 _aggregation_optimization_settings 0 2581814110 _optimizer_use_cbqt_star_transformation TRUE _optimizer_unnest_disjunctive_subq TRUE _optimizer_unnest_corr_set_subq TRUE _optimizer_try_st_before_jppd TRUE _optimizer_table_expansion TRUE _optimizer_null_aware_antijoin TRUE _optimizer_multi_level_push_pred TRUE _optimizer_join_factorization TRUE _optimizer_improve_selectivity TRUE _optimizer_group_by_placement TRUE _optimizer_fast_pred_transitivity TRUE _optimizer_fast_access_pred_analysis TRUE _optimizer_extended_stats_usage_control 224 _optimizer_extended_cursor_sharing_rel SIMPLE _optimizer_extend_jppd_view_types TRUE _optimizer_enable_extended_stats TRUE _optimizer_enable_density_improvements TRUE _optimizer_eliminate_filtering_join TRUE _optimizer_distinct_placement TRUE _optimizer_distinct_agg_transform TRUE _optimizer_connect_by_elim_dups TRUE _optimizer_coalesce_subqueries TRUE _optimizer_adaptive_cursor_sharing TRUE _nlj_batching_enabled 1 _first_k_rows_dynamic_proration TRUE _connect_by_use_union_all TRUE _bloom_pruning_enabled TRUE _bloom_folding_enabled TRUE _and_pruning_enabled TRUE _replace_virtual_columns TRUE _optimizer_use_feedback TRUE 33 rows selected. Elapsed: 00:00:00.01 Session altered. Elapsed: 00:00:00.01
Our plan suddenly reverts back to the wrong plan when _optimizer_native_full_outer_join is force.
Lets turn it off and see the effect to confirm our test environment is right
HR@ORACOS> alter session set "_optimizer_native_full_outer_join"=OFF;
Session altered.
HR@ORACOS> @test_e
Explained.
HR@ORACOS> @e
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2581814110
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8052 | 4 (25)| 00:00:01 | | |
| 1 | VIEW | | 2 | 8052 | 4 (25)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | HASH JOIN OUTER | | 1 | 4035 | 3 (34)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE | | 1 | 2024 | 1 (0)| 00:00:01 | KEY | KEY |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID | T1 | 1 | 2024 | 1 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | TEST1_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 7 | PARTITION RANGE SINGLE | | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | T2 | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
|* 9 | INDEX SKIP SCAN | TEST2_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 10 | FILTER | | | | | | | |
| 11 | NESTED LOOPS ANTI | | 1 | 2020 | 1 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE SINGLE | | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID| T2 | 1 | 2011 | 1 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX SKIP SCAN | TEST2_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 15 | PARTITION RANGE SINGLE | | 1 | 9 | 0 (0)| 00:00:01 | KEY | KEY |
|* 16 | INDEX FULL SCAN | TEST1_PK | 1 | 9 | 0 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ASOF"="T2"."ASOF"(+))
6 - access("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
9 - access("T2"."ASOF"(+)=TO_DATE('15Sep10','DDMONYY'))
filter("T2"."ASOF"(+)=TO_DATE('15Sep10','DDMONYY'))
10 - filter(CAST(NULL AS DATE)=TO_DATE('15Sep10','DDMONYY'))
14 - access("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T2"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
16 - access("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
filter("T1"."ASOF"=TO_DATE('15Sep10','DDMONYY'))
37 rows selected.
Perfect, in less then 5 minutes we spot the difference . Actually this was the issue which started everything for this series.
I spent 1 full day on the real sql and 10053 traces and suddenly I checked the sql carefully again (another “invalid dba exception”, should have done before )
and it reminded me Christian Antognini’s post about native full outer join support (Being a blog reader is good thing !!!)
then I checked the parameter and immediatelly realized that it was my issue.
After I automate all the things I have tried for this issue now I really feel confident for %99 of the issues caused during upgrades with bugfix and optimizer_parameter changes. 10053 with many joins makes me feel really dumb š
Thank god at least for the upgrades I don’t have to go through it again.
For this particular case because I was ready with all the information I needed, I raised the issue with Oracle and got response back on the “same day” that
there is a bug with a patch 9287401 available, and once I patched it worked like charm.
Unfortunatelly, not eveything is clear as daylight like on this example:( There can be combination of parameters effecting the generation of the plan.
Controlled environment like the one above (one parameter at a time) only reveals simple issue. We may need to run non_controlled version
to see how things can differ when we keep the parameter at the value we set.
Non controlled version for the script is (sorry for the dodgy naming)
build_optimizer_param_test_2 : Same logic this time we keep the parameter with the setting we set.
build_optimizer_param_test_e_2: Same logic this time we keep the parameter with the setting we set.
There is also one more thing I discovered, you need to re-connect after your tests because there is no “alter session reset” option for session level parameters.
When you explicitly set a parameter, changing OFE does not adjust that parameter so better refresh your session environment by re-connect
Lets see how non controlled test case work.
This time I don’t have a test case but a real case where things went wrong after 11.2.0.1 upgrade from 10.2.0.4.
A report query suddenly started to run like a dog. From 10 minutes to 10 hours. I went through all bugfixes and it did not help again.
Plan differences for versions were like below.
For 11.2.0.1
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 769921106
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 795K (22)| 00:28:06 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 30M| 795K (22)| 00:28:06 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 790K (22)| 00:27:54 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 790K (22)| 00:27:54 | | |
| 8 | VIEW | | 4841 | 1096K| | 760K (23)| 00:26:51 | | |
| 9 | HASH GROUP BY | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
|* 10 | HASH JOIN | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 4841 | 756K| 2368K| 760K (23)| 00:26:51 | | |
| 13 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
|* 14 | HASH JOIN | | 219K| 23M| 20M| 759K (23)| 00:26:49 | | |
|* 15 | HASH JOIN | | 219K| 17M| | 738K (23)| 00:26:04 | | |
| 16 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 18 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 19 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 22 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 23 | PARTITION LIST SINGLE | | 3215K| 88M| | 17265 (15)| 00:00:37 | KEY | KEY |
|* 24 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17265 (15)| 00:00:37 | 34 | 34 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29859 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29853 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29230 (2)| 00:01:02 | | |
| 30 | HASH GROUP BY | | 43364 | 4319K| 4968K| 29230 (2)| 00:01:02 | | |
| 31 | NESTED LOOPS | | | | | | | | |
| 32 | NESTED LOOPS | | 43364 | 4319K| | 28309 (2)| 00:01:00 | | |
| 33 | VIEW | VW_GBF_14 | 9021 | 669K| | 1065 (12)| 00:00:03 | | |
| 34 | HASH GROUP BY | | 9021 | 916K| 1072K| 1065 (12)| 00:00:03 | | |
|* 35 | HASH JOIN | | 9021 | 916K| | 868 (13)| 00:00:02 | | |
|* 36 | HASH JOIN | | 9021 | 643K| | 736 (13)| 00:00:02 | | |
|* 37 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 38 | PARTITION RANGE SINGLE | | 17495 | 871K| | 733 (13)| 00:00:02 | 34 | 34 |
|* 39 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 733 (13)| 00:00:02 | 34 | 34 |
| 40 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 41 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
| 42 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_YYYYYYYYY_SPREADS | 5 | 130 | | 3 (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
14 - access("TERM"."INSTRUMENT"="I"."PKEY")
15 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
17 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
18 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - access("CLIENTS"."NAME"='Xxxxxxxx')
24 - filter("I"."DOCCLAUSE" IS NOT NULL)
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
35 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
36 - access("M"."RANGE"="VALUE")
37 - access("PROPERTY"='PercentileInTblReport')
39 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
41 - access("ITEM_1"="BA"."YYYYYYYY_METRICS_ID")
74 rows selected.
For 10.2.0.4
SQL> alter session set optimizer_features_enable='10.2.0.4';
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3853593815
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 794K (22)| 00:28:04 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 61M| 794K (22)| 00:28:04 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 789K (22)| 00:27:52 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 789K (22)| 00:27:52 | | |
| 8 | VIEW | | 1770 | 401K| | 759K (23)| 00:26:49 | | |
| 9 | HASH GROUP BY | | 1770 | 295K| | 759K (23)| 00:26:49 | | |
|* 10 | HASH JOIN | | 1770 | 295K| | 759K (23)| 00:26:49 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 1770 | 276K| | 759K (23)| 00:26:49 | | |
| 13 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 15 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 16 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 2066K| 201M| | 758K (23)| 00:26:48 | | |
|* 19 | HASH JOIN | | 14382 | 1067K| 2368K| 20817 (15)| 00:00:45 | | |
| 20 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
| 21 | PARTITION LIST SINGLE | | 3215K| 88M| | 17213 (15)| 00:00:37 | KEY | KEY |
| 22 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17213 (15)| 00:00:37 | 34 | 34 |
| 23 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 24 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29935 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29929 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29306 (2)| 00:01:03 | | |
| 30 | HASH GROUP BY | | 43364 | 5759K| 12M| 29306 (2)| 00:01:03 | | |
| 31 | TABLE ACCESS BY GLOBAL INDEX ROWID| TBL_YYYYYYYYY_SPREADS | 5 | 160 | | 3 (0)| 00:00:01 | ROWID | ROWID |
| 32 | NESTED LOOPS | | 43364 | 5759K| | 28118 (2)| 00:01:00 | | |
|* 33 | HASH JOIN | | 9021 | 916K| | 874 (14)| 00:00:02 | | |
|* 34 | HASH JOIN | | 9021 | 643K| | 742 (14)| 00:00:02 | | |
|* 35 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 36 | PARTITION RANGE SINGLE | | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
|* 37 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
| 38 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
14 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
15 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
17 - access("CLIENTS"."NAME"='Xxxxxxxx')
18 - access("TERM"."INSTRUMENT"="I"."PKEY")
19 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
33 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
34 - access("M"."RANGE"="VALUE")
35 - access("PROPERTY"='PercentileInTblReport')
37 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - access("M"."YYYYYYYY_METRICS_ID"="BA"."YYYYYYYY_METRICS_ID")
70 rows selected.
The plan_hash_value I was chasing for is 3853593815
I ran the controlled version and the results were like below.
SQL> @optimizer_param_test_e
PD_NAME PD_VALUE PLAN_HASH_VALUE
-------------------------------------------------- ---------- ------------------
_nlj_batching_enabled 0 49955995
_aggregation_optimization_settings 32 769921106
_optimizer_use_cbqt_star_transformation FALSE
_optimizer_unnest_disjunctive_subq FALSE
_optimizer_unnest_corr_set_subq FALSE
_optimizer_try_st_before_jppd FALSE
_optimizer_table_expansion FALSE
_optimizer_null_aware_antijoin FALSE
_optimizer_native_full_outer_join OFF
_optimizer_multi_level_push_pred FALSE
_optimizer_join_factorization FALSE
_optimizer_improve_selectivity FALSE
_optimizer_fast_pred_transitivity FALSE
_optimizer_fast_access_pred_analysis FALSE
_replace_virtual_columns FALSE
_optimizer_use_feedback FALSE
_and_pruning_enabled FALSE
_bloom_folding_enabled FALSE
_optimizer_extended_cursor_sharing_rel NONE
_optimizer_extend_jppd_view_types FALSE
_optimizer_enable_density_improvements FALSE
_optimizer_eliminate_filtering_join FALSE
_optimizer_distinct_placement FALSE
_optimizer_distinct_agg_transform FALSE
_optimizer_connect_by_elim_dups FALSE
_optimizer_coalesce_subqueries FALSE
_optimizer_adaptive_cursor_sharing FALSE
_first_k_rows_dynamic_proration FALSE
_connect_by_use_union_all OLD_PLAN_M
ODE
_bloom_pruning_enabled FALSE
_optimizer_group_by_placement FALSE 1801558718
_optimizer_enable_extended_stats FALSE 1803847571
_optimizer_extended_stats_usage_control 255
It did not help me for the right plan, and to be honest I wasn’t expecting that it will help in this case because of the extra nexted loop which is controlled
by “_nlj_batching_enabled”.
I again tried it with the non_controlled version of the test builder to see if the parameters above will still effect.
SQL> @build_optimizer_param_test_e_2 Enter value for before_ofe: 11.2.0.1 Enter value for after_ofe: 10.2.0.4 SQL> @optimizer_param_test_e_2 PD_NAME PD_VALUE PLAN_HASH_VALUE -------------------------------------------------- ---------- ------------------ _replace_virtual_columns FALSE 769921106 _optimizer_use_feedback FALSE _optimizer_use_cbqt_star_transformation FALSE _optimizer_unnest_disjunctive_subq FALSE _optimizer_unnest_corr_set_subq FALSE _optimizer_try_st_before_jppd FALSE _optimizer_table_expansion FALSE _optimizer_null_aware_antijoin FALSE _optimizer_native_full_outer_join OFF _optimizer_multi_level_push_pred FALSE _optimizer_join_factorization FALSE _optimizer_improve_selectivity FALSE _optimizer_group_by_placement FALSE 1801558718 =>change _optimizer_fast_pred_transitivity FALSE _optimizer_fast_access_pred_analysis FALSE _optimizer_extended_stats_usage_control 255 1340272812 =>change _optimizer_extended_cursor_sharing_rel NONE _optimizer_extend_jppd_view_types FALSE _optimizer_enable_extended_stats FALSE _optimizer_enable_density_improvements FALSE _optimizer_eliminate_filtering_join FALSE _optimizer_distinct_placement FALSE _optimizer_distinct_agg_transform FALSE _optimizer_connect_by_elim_dups FALSE _optimizer_coalesce_subqueries FALSE _optimizer_adaptive_cursor_sharing FALSE _nlj_batching_enabled 0 3853593815 =>change _first_k_rows_dynamic_proration FALSE _connect_by_use_union_all OLD_PLAN_M ODE _bloom_pruning_enabled FALSE _bloom_folding_enabled FALSE _and_pruning_enabled FALSE _aggregation_optimization_settings 32
For non controlled version the order of the output above is the order of insert to the table so from first row to the last row test builder
reverts the parameter back to 10.2.0.4 value and goes on with the new parameter without changing it back to 11.2.0.1 value .
Till “_optimizer_group_by_placement” we have 11.2.0.1 default plan. After that one, we see another change on “_optimizer_extended_stats_usage_control”
and when nested loop behaviour reverts back with “_nlj_batching_enabled “ to the original we find our right plan. So possibilities drop down from 35 to 3.
If we are lucky enough combination of these 3 parameters will give us the right plan.
First start with extra nested loop controller “_nlj_batching_enabled” and we revert it back to pre-11g behavior
SQL> alter session set "_nlj_batching_enabled"=0;
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 49955995
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 795K (22)| 00:28:06 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 30M| 795K (22)| 00:28:06 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 790K (22)| 00:27:54 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 790K (22)| 00:27:54 | | |
| 8 | VIEW | | 4841 | 1096K| | 760K (23)| 00:26:51 | | |
| 9 | HASH GROUP BY | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
|* 10 | HASH JOIN | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 4841 | 756K| 2368K| 760K (23)| 00:26:51 | | |
| 13 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
|* 14 | HASH JOIN | | 219K| 23M| 20M| 759K (23)| 00:26:49 | | |
|* 15 | HASH JOIN | | 219K| 17M| | 738K (23)| 00:26:04 | | |
| 16 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 18 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 19 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 22 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 23 | PARTITION LIST SINGLE | | 3215K| 88M| | 17265 (15)| 00:00:37 | KEY | KEY |
|* 24 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17265 (15)| 00:00:37 | 34 | 34 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29859 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29853 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29230 (2)| 00:01:02 | | |
| 30 | HASH GROUP BY | | 43364 | 4319K| 4968K| 29230 (2)| 00:01:02 | | |
| 31 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_YYYYYYYYY_SPREADS | 5 | 130 | | 3 (0)| 00:00:01 | ROWID | ROWID |
| 32 | NESTED LOOPS | | 43364 | 4319K| | 28309 (2)| 00:01:00 | | |
| 33 | VIEW | VW_GBF_14 | 9021 | 669K| | 1065 (12)| 00:00:03 | | |
| 34 | HASH GROUP BY | | 9021 | 916K| 1072K| 1065 (12)| 00:00:03 | | |
|* 35 | HASH JOIN | | 9021 | 916K| | 868 (13)| 00:00:02 | | |
|* 36 | HASH JOIN | | 9021 | 643K| | 736 (13)| 00:00:02 | | |
|* 37 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 38 | PARTITION RANGE SINGLE | | 17495 | 871K| | 733 (13)| 00:00:02 | 34 | 34 |
|* 39 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 733 (13)| 00:00:02 | 34 | 34 |
| 40 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 41 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
14 - access("TERM"."INSTRUMENT"="I"."PKEY")
15 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
17 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
18 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - access("CLIENTS"."NAME"='Xxxxxxxx')
24 - filter("I"."DOCCLAUSE" IS NOT NULL)
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
35 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
36 - access("M"."RANGE"="VALUE")
37 - access("PROPERTY"='PercentileInTblReport')
39 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
41 - access("ITEM_1"="BA"."YYYYYYYY_METRICS_ID")
73 rows selected.
Now its time to go with the “_optimizer_group_by_placement”. This is about “consider group-by placement optimization” and We have many group bys in our query
as inner query. I was at first under the impression that this is about “_optimizer_extend_jppd_view_types” like Dion Cho explained here
and Maria Colgan (she is also a great presented please do not miss her sessions) from optimizer group explained here
but actually that parameter is not changing the behavior. I wish there were more information about “_optimizer_group_by_placement” but there aren’t š¦
SQL> alter session set "_optimizer_group_by_placement"=FALSE;
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3419104514
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 795K (22)| 00:28:06 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 30M| 795K (22)| 00:28:06 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 790K (22)| 00:27:54 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 790K (22)| 00:27:54 | | |
| 8 | VIEW | | 4841 | 1096K| | 760K (23)| 00:26:51 | | |
| 9 | HASH GROUP BY | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
|* 10 | HASH JOIN | | 4841 | 808K| | 760K (23)| 00:26:51 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 4841 | 756K| 2368K| 760K (23)| 00:26:51 | | |
| 13 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
|* 14 | HASH JOIN | | 219K| 23M| 20M| 759K (23)| 00:26:49 | | |
|* 15 | HASH JOIN | | 219K| 17M| | 738K (23)| 00:26:04 | | |
| 16 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 18 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 19 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
| 21 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 22 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 23 | PARTITION LIST SINGLE | | 3215K| 88M| | 17265 (15)| 00:00:37 | KEY | KEY |
|* 24 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17265 (15)| 00:00:37 | 34 | 34 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29883 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29877 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29254 (2)| 00:01:02 | | |
| 30 | HASH GROUP BY | | 43364 | 5505K| 6208K| 29254 (2)| 00:01:02 | | |
| 31 | TABLE ACCESS BY GLOBAL INDEX ROWID | TBL_YYYYYYYYY_SPREADS | 5 | 130 | | 3 (0)| 00:00:01 | ROWID | ROWID |
| 32 | NESTED LOOPS | | 43364 | 5505K| | 28118 (2)| 00:01:00 | | |
|* 33 | HASH JOIN | | 9021 | 916K| | 874 (14)| 00:00:02 | | |
|* 34 | HASH JOIN | | 9021 | 643K| | 742 (14)| 00:00:02 | | |
|* 35 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 36 | PARTITION RANGE SINGLE | | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
|* 37 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
| 38 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
14 - access("TERM"."INSTRUMENT"="I"."PKEY")
15 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
17 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
18 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - access("CLIENTS"."NAME"='Xxxxxxxx')
24 - filter("I"."DOCCLAUSE" IS NOT NULL)
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
33 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
34 - access("M"."RANGE"="VALUE")
35 - access("PROPERTY"='PercentileInTblReport')
37 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - access("M"."YYYYYYYY_METRICS_ID"="BA"."YYYYYYYY_METRICS_ID")
71 rows selected.
Still not helping with the exact plan I am looking for. Last try will be “_optimizer_extended_stats_usage_control”
This parameter is normally not available on 10.2.0.4 so probably setting it to 255 actually means disabling it.
Even we don’t have any extended stats on our database somehow it effects optimizer. Lets see how it effects
SQL> alter session set "_optimizer_extended_stats_usage_control"=255;
Session altered.
SQL> @test_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3853593815
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 794K (22)| 00:28:04 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 30M| 794K (22)| 00:28:04 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 789K (22)| 00:27:52 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 789K (22)| 00:27:52 | | |
| 8 | VIEW | | 1683 | 381K| | 759K (23)| 00:26:49 | | |
| 9 | HASH GROUP BY | | 1683 | 281K| | 759K (23)| 00:26:49 | | |
|* 10 | HASH JOIN | | 1683 | 281K| | 759K (23)| 00:26:49 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 1683 | 262K| | 759K (23)| 00:26:49 | | |
| 13 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 15 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 16 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 1964K| 191M| | 758K (23)| 00:26:48 | | |
|* 19 | HASH JOIN | | 14382 | 1067K| 2368K| 20870 (16)| 00:00:45 | | |
| 20 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
| 21 | PARTITION LIST SINGLE | | 3215K| 88M| | 17265 (15)| 00:00:37 | KEY | KEY |
|* 22 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17265 (15)| 00:00:37 | 34 | 34 |
| 23 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 24 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29883 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29877 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29254 (2)| 00:01:02 | | |
| 30 | HASH GROUP BY | | 43364 | 5505K| 6208K| 29254 (2)| 00:01:02 | | |
| 31 | TABLE ACCESS BY GLOBAL INDEX ROWID| TBL_YYYYYYYYY_SPREADS | 5 | 130 | | 3 (0)| 00:00:01 | ROWID | ROWID |
| 32 | NESTED LOOPS | | 43364 | 5505K| | 28118 (2)| 00:01:00 | | |
|* 33 | HASH JOIN | | 9021 | 916K| | 874 (14)| 00:00:02 | | |
|* 34 | HASH JOIN | | 9021 | 643K| | 742 (14)| 00:00:02 | | |
|* 35 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 36 | PARTITION RANGE SINGLE | | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
|* 37 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
| 38 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
14 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
15 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
17 - access("CLIENTS"."NAME"='Xxxxxxxx')
18 - access("TERM"."INSTRUMENT"="I"."PKEY")
19 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
22 - filter("I"."DOCCLAUSE" IS NOT NULL)
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
33 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
34 - access("M"."RANGE"="VALUE")
35 - access("PROPERTY"='PercentileInTblReport')
37 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - access("M"."YYYYYYYY_METRICS_ID"="BA"."YYYYYYYY_METRICS_ID")
71 rows selected.
As you can see after the 3th parameter we have our plan back
One more thing to show is that how will it be with extra nested loop (preperation for the next post š ).
SQL> alter session set "_nlj_batching_enabled"=1;
Session altered.
SQL> @tesT_e
Explained.
SQL> @e
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1340272812
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43364 | 28M| | 794K (22)| 00:28:04 | | |
| 1 | SORT ORDER BY | | 43364 | 28M| 30M| 794K (22)| 00:28:04 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 43364 | 28M| | 789K (22)| 00:27:52 | | |
| 3 | VIEW | | 17518 | 975K| | 62 (25)| 00:00:01 | | |
|* 4 | HASH JOIN | | 17518 | 615K| | 62 (25)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL | STATIC_CLASSIFICATIONS | 658 | 14476 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | ORGS_CLASSIFICATIONS | 17518 | 239K| | 57 (23)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 43364 | 26M| | 789K (22)| 00:27:52 | | |
| 8 | VIEW | | 1683 | 381K| | 759K (23)| 00:26:49 | | |
| 9 | HASH GROUP BY | | 1683 | 281K| | 759K (23)| 00:26:49 | | |
|* 10 | HASH JOIN | | 1683 | 281K| | 759K (23)| 00:26:49 | | |
| 11 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 12 | HASH JOIN | | 1683 | 262K| | 759K (23)| 00:26:49 | | |
| 13 | PARTITION RANGE SINGLE | | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | FC_TBL_XXXXXXXXXX_CURVE | 10667 | 604K| | 353 (3)| 00:00:01 | 34 | 34 |
|* 15 | INDEX RANGE SCAN | FC_TBL_XXXXXXXXXX_CURVE_PK | 48 | | | 34 (6)| 00:00:01 | 34 | 34 |
| 16 | TABLE ACCESS BY INDEX ROWID | CLIENTS | 1 | 20 | | 2 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | CLIENTS_NAME_UNQ | 1 | | | 1 (0)| 00:00:01 | | |
|* 18 | HASH JOIN | | 1964K| 191M| | 758K (23)| 00:26:48 | | |
|* 19 | HASH JOIN | | 14382 | 1067K| 2368K| 20870 (16)| 00:00:45 | | |
| 20 | TABLE ACCESS FULL | ORGS | 41094 | 1886K| | 229 (12)| 00:00:01 | | |
| 21 | PARTITION LIST SINGLE | | 3215K| 88M| | 17265 (15)| 00:00:37 | KEY | KEY |
|* 22 | TABLE ACCESS FULL | INSTRUMENTS | 3215K| 88M| | 17265 (15)| 00:00:37 | 34 | 34 |
| 23 | PARTITION RANGE ALL | | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
| 24 | TABLE ACCESS FULL | FC_TBL_XXXXXXXXXX_TERMS | 255M| 6345M| | 717K (21)| 00:25:19 | 1 | 40 |
|* 25 | HASH JOIN | | 43364 | 16M| | 29883 (2)| 00:01:04 | | |
| 26 | TABLE ACCESS FULL | STATIC_COUNTRIES | 239 | 2629 | | 2 (0)| 00:00:01 | | |
|* 27 | HASH JOIN | | 43364 | 16M| 2736K| 29877 (2)| 00:01:04 | | |
| 28 | TABLE ACCESS FULL | ORGS | 41094 | 2247K| | 229 (12)| 00:00:01 | | |
| 29 | VIEW | | 43364 | 13M| | 29254 (2)| 00:01:02 | | |
| 30 | HASH GROUP BY | | 43364 | 5505K| 6208K| 29254 (2)| 00:01:02 | | |
| 31 | NESTED LOOPS | | | | | | | | |
| 32 | NESTED LOOPS | | 43364 | 5505K| | 28118 (2)| 00:01:00 | | |
|* 33 | HASH JOIN | | 9021 | 916K| | 874 (14)| 00:00:02 | | |
|* 34 | HASH JOIN | | 9021 | 643K| | 742 (14)| 00:00:02 | | |
|* 35 | INDEX RANGE SCAN | TBL_METRICS_CONFIG_UNIQUE | 2 | 44 | | 1 (0)| 00:00:01 | | |
| 36 | PARTITION RANGE SINGLE | | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
|* 37 | TABLE ACCESS FULL | TBL_YYYYYYYYY_METRICS | 17495 | 871K| | 739 (14)| 00:00:02 | 34 | 34 |
| 38 | TABLE ACCESS FULL | TBL_TTTTT_TIER_TTTTTTTT_CONV | 42010 | 1271K| | 127 (10)| 00:00:01 | | |
|* 39 | INDEX RANGE SCAN | TBL_YYY_SPREADS_UNIQUE | 5 | | | 2 (0)| 00:00:01 | | |
| 40 | TABLE ACCESS BY GLOBAL INDEX ROWID| TBL_YYYYYYYYY_SPREADS | 5 | 130 | | 3 (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORGS"."PKEY"="CLASSIFICATIONS"."ORG"(+))
4 - access("ORGS_CLASSIFICATIONS"."SECTOR"="STATIC_CLASSIFICATIONS"."PKEY")
6 - filter("ORGS_CLASSIFICATIONS"."PROVIDER"='ICB')
7 - access("YYYYYYYY_CURVE"."ASOF"="CURVES"."ASOF"(+) AND "YYYYYYYY_CURVE"."ENTITY"="CURVES"."ISSUER"(+) AND
"YYYYYYYY_CURVE"."TIER"="CURVES"."TIER"(+) AND "YYYYYYYY_CURVE"."RUNNING_COUPON"="CURVES"."RUNNING_COUPON"(+))
10 - access("O"."COUNTRY"="C"."ID")
12 - access("CURVE"."ROWKEY"="TERM"."CURVE_KEY")
14 - filter("CURVE"."RUNNING_COUPON" IS NOT NULL AND "CURVE"."SOURCE"='LEGACY' AND "CURVE"."OWNER"= (SELECT "CLIENTS"."PKEY" FROM
"CORE"."CLIENTS" "CLIENTS" WHERE "CLIENTS"."NAME"='Xxxxxxxx'))
15 - access("CURVE"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
17 - access("CLIENTS"."NAME"='Xxxxxxxx')
18 - access("TERM"."INSTRUMENT"="I"."PKEY")
19 - access("I"."ISSUER"="O"."PKEY" AND "I"."CCY"="O"."DEFAULTCCY" AND "I"."DOCCLAUSE"="O"."DEFAULTDOCCLAUSE")
22 - filter("I"."DOCCLAUSE" IS NOT NULL)
25 - access("ORGS"."COUNTRY"="STATIC_COUNTRIES"."ID")
27 - access("YYYYYYYY_CURVE"."ENTITY"="ORGS"."PKEY")
33 - access("M"."TRADING_CONV_ID"="C"."TBL_E_T_TRADING_CONV_ID")
34 - access("M"."RANGE"="VALUE")
35 - access("PROPERTY"='PercentileInTblReport')
37 - filter("M"."ASOF"=TO_DATE(' 2010-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
39 - access("M"."YYYYYYYY_METRICS_ID"="BA"."YYYYYYYY_METRICS_ID")
72 rows selected.
This plan is the plan I would like to see once I upgraded the DB but due to the new optimizations I saw something completelly different.
Once I disabled the right ones we have a solution to generate the right plan.
If I have time to deal with it (maybe I have in the future) I could show you the differences on 10053 file for this case, but it can only happen when I finish this series.
This post and the previous posts about bugfixes are just for you to give better understanding of what is effecting your plan change
and how you can feedback oracle support with the finding you found out. The non-scientific methods here are for automating your researches and saving you some time and
giving you the idea where to look when you look at the 10053 file if you want to dig further.
Normally shortest way to create the plan you are looking after is Using OFE and Plan Baselines together and job will be done for %99 of the cases.
!!!!!These are again undocumented parameters They can be solution to wrong results or they can be cause of wrong results
please do a research on MOS and ask Oracle support before you use them
Scripts:
All in one
full_outer_join_test_case.sql
build_opt_param_table.sql
before_after_opt_param.sql
build_optimizer_param_test.sql
build_optimizer_param_test_e.sql
build_optimizer_param_test_2.sql
build_optimizer_param_test_e_2.sql
<–Previous Post in This Series —————- Ā Ā Next Post in this series–>
Coskan's Approach to Oracle 
[…] <-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 17, 2011 @ 8:19 pm
[…] 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 parameters : how you can find which parameter change/addition may caused your plan change 5-Why is my plan […]
Pingback by Plan stability through Upgrade to 11G-Introduction « Coskan’s Approach to Oracle — February 17, 2011 @ 8:59 pm
A great article !
Thanks
Comment by AlexG — February 22, 2011 @ 2:05 pm
[…] Plan Stability Through Upgrade-Why is my plan changed?-new optimizer parameters […]
Pingback by Plan stability through Upgrade to 11G-Why is my plan changed?-extra nested loop « Coskan’s Approach to Oracle — March 4, 2011 @ 1:30 pm
Very useful post!
Currently implementing an Extended RAC in 11gR2 and was trying to figure out what changes had been introduced in the CBO, since the behavior of legacy applications (10g) had changed substantially.
Congratulations on your great work and thanks a lot for sharing it.
Comment by Xavier — April 2, 2011 @ 10:29 am
Nice
Comment by pramod — November 11, 2011 @ 9:36 pm
Nice article, noticed one small issue in script build_opt_param_table.sql, it should be &optimizer_feature_version instead of &optimizer_feature.
Thanks.
Comment by Raju. — December 8, 2011 @ 7:46 pm
Hi,
It’s really nice article and it’s helped me to solve problem fastly in our critical database.
1)recently we upgraded our database to 11.2.0.2 from 10.2.0.4 and queries taking 3hrs where the same query completed in 1min in 10.2.0.4.
2) If we put OFE to 10.2.0.4 queries completing in 1min. By using your scripts,I have found that parameters which are changed and disabled all the parameters in session level except OFE but still query taking more time.
3)Can you kindly help me here to find out and solve the problem permanently
Comment by Srinivas — February 27, 2012 @ 2:09 pm
[…] any control via an optimizer parameter. How do I know it ? If you follow what I have done in the optimizer features post you can see there is no controlling on this feature via a parameter. So we only have optimizer […]
Pingback by Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling « Coskan’s Approach to Oracle — May 31, 2012 @ 7:59 pm
This site was… how do you say it? Relevant!! Finally I have found something that helped me.
Thanks a lot!
Comment by acne cure — September 22, 2014 @ 3:46 am