Re: Hidden paramters issue
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Mon, 8 Mar 2010 09:22:27 +0100
Hi
These parameters has following default values:
_fast_full_scan_enabled TRUE
_b_tree_bitmap_plans TRUE
_optimizer_cost_based_transformation LINEAR
_sort_elimination_cost_ratio 0
So some of them is default already (most actually).
You hahve said that _fast_full_scan_enabled has to be set to FALSE because
some ML notes are saying so, can you reference some note numbers? Because I
havent seen such notes.
Thanks
--
LSC
On Mon, Mar 8, 2010 at 3:46 AM, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
> Hi
> Our reporting developers/management started to complain about their reports
> running slow after upgrade to 11.1.0.7. Not only is the upgrade teh only
> change, but they are moving their reports from a single standalone instance
> to a rac instance. This is for saving costs and get rid of the stand alone
> database. The idea is to use the second node of the rac database for
> reporting purpose (we dont agree with this but we have no choice. If you ask
> question, they say just do your job 'lowly' dba :) ).
>
> But that apart, the users started complaining about rac and 11g. When we
> did further investigation we found that the user who runs the report has a
> logon trigger defined like this
>
> if user = 'REP_USER' then
> execute immediate 'alter session set "_b_tree_bitmap_plans" = true';
> execute immediate 'alter session set "_fast_full_scan_enabled" = true';
> execute immediate 'alter session set
> "_optimizer_cost_based_transformation"
>
> = linear';
> execute immediate 'alter session set "_sort_elimination_cost_ratio" =
> 0';
> execute immediate 'alter session set optimizer_features_enable =
> "10.2.0.4"'
> ;
>
> The default value for b_tree_bitmap_plans" is false and
> fast_full_scan_enabled is false too.
>
> But what made most difference is with _fast_full_scan_enabled. I set it
> back to false after logging in as 'REP_USER' and the reports start running
> better. The other parameters that they set in my opinion dont matter (I mean
> you can leave them as is is to their default values is probably the best).
> For some reason, they got this from a DBA who insisted that these should be
> set in 10g and it would improve performance.
>
> I saw a few Metalink notes which explicity mention about
> "_fast_full_scan_enabled" and they also mention that it should be set to
> false especially in a rac instance.
>
> My question is why is it important to set this to false especially in a rac
> instance (eventhough I agree that these should not be (_ parameters) set at
> all unless we know what we are doing exacctly).
> When I was playing around with this parameter at my session level, I
> noticed that this parameter does not make much difference in the performance
> of the report in a non-rac instance and makes a huge difference in a rac
> instance.
>
> Also, can anyone provide me sources where I can read about these init.ora
> parameters (I dont want to see a listing of init.ora parameters with _ in
> them. I have seen some web sites already have them :) ).
>
> Thank you
> Kumar
>
Other related posts:
