Re: db block size

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 08:54:14 +0100
Message-ID: <00ff01c458f7$4756e520$7102a8c0@Primary>

For dereference, here are the 30 parameters that change their default values when you switch between o_f_e=8.1.7 and o_f_e = 9.2.0 (on the platform I happened to be using).

You might want to try setting o_f_e to 9.2.0 then setting all of these to the 8.1.7 values and selectively re-enabling the 9.2.0 value.

In passing, I haven't checked whether the values for o_f_e = 8.1.7 match the default values for a real 8.1.7 instance - any bets, anyone ?

(I don't think the last parameter changes makes much difference, by the way).

V NAME                                 VALUE

IS_DEF

- ------------------------------------ -------------------------------------


--- ---------

8 _always_anti_join                    OFF
TRUE
9 _always_anti_join                    CHOOSE

TRUE

8 _always_semi_join                    OFF
TRUE
9 _always_semi_join                    CHOOSE

TRUE

8 _b_tree_bitmap_plans                 FALSE
TRUE
9 _b_tree_bitmap_plans                 TRUE

TRUE

8 _complex_view_merging                FALSE
TRUE
9 _complex_view_merging                TRUE

TRUE

8 _cpu_to_io                           100
TRUE
9 _cpu_to_io                           0

TRUE 8 _generalized_pruning_enabled FALSE TRUE
9 _generalized_pruning_enabled TRUE TRUE 8 _gs_anti_semi_join_allowed FALSE TRUE
9 _gs_anti_semi_join_allowed TRUE TRUE

8 _index_join_enabled                  FALSE
TRUE
9 _index_join_enabled                  TRUE

TRUE

8 _load_without_compile                none
TRUE
9 _load_without_compile                NONE

TRUE 8 _new_initial_join_orders FALSE TRUE
9 _new_initial_join_orders TRUE TRUE 8 _new_sort_cost_estimate FALSE TRUE
9 _new_sort_cost_estimate TRUE TRUE 8 _optim_adjust_for_part_skews FALSE TRUE
9 _optim_adjust_for_part_skews TRUE TRUE 8 _optim_new_default_join_sel FALSE TRUE
9 _optim_new_default_join_sel TRUE TRUE 8 _optim_peek_user_binds FALSE TRUE
9 _optim_peek_user_binds TRUE TRUE

8 _optimizer_cost_model                IO
TRUE
9 _optimizer_cost_model                CHOOSE

TRUE 8 _optimizer_new_join_card_computation FALSE TRUE
9 _optimizer_new_join_card_computation TRUE TRUE 8 _optimizer_system_stats_usage 1 TRUE
9 _optimizer_system_stats_usage 0 TRUE 8 _optimizer_undo_cost_change 8.1.7 TRUE
9 _optimizer_undo_cost_change 9.2.0 TRUE

8 _ordered_nested_loop                 FALSE
TRUE
9 _ordered_nested_loop                 TRUE

TRUE 8 _parallel_broadcast_enabled FALSE TRUE
9 _parallel_broadcast_enabled TRUE TRUE 8 _pre_rewrite_push_pred FALSE TRUE
9 _pre_rewrite_push_pred TRUE TRUE

8 _pred_move_around                    FALSE
TRUE
9 _pred_move_around                    TRUE

TRUE

8 _push_join_predicate                 FALSE
TRUE
9 _push_join_predicate                 TRUE

TRUE

8 _push_join_union_view                FALSE
TRUE
9 _push_join_union_view                TRUE

TRUE 8 _table_scan_cost_plus_one FALSE TRUE
9 _table_scan_cost_plus_one TRUE TRUE

8 _union_rewrite_for_gs                OFF
TRUE
9 _union_rewrite_for_gs                CHOOSE

TRUE

8 _unnest_notexists_sq                 OFF
TRUE
9 _unnest_notexists_sq                 SINGLE

TRUE 8 optimizer_dynamic_sampling 0 TRUE
9 optimizer_dynamic_sampling 1 TRUE 8 optimizer_max_permutations 80000 TRUE
9 optimizer_max_permutations 2000 TRUE 8 query_rewrite_integrity enforced TRUE
9 query_rewrite_integrity ENFORCED TRUE Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Paul Drake" <discgolfdba_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Monday, June 21, 2004 7:38 PM Subject: Re: db block size

I kinda need to hijack an instance - kick cursor_sharing back to exact (can't really use similar yet, still on o_f_e = 8.1.7 - very long story) and grab some statements for testing.

We're still not native 9.2 across the board. Need to get over that hump.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Wed Jun 23 2004 - 02:50:58 CDT