Re: Hidden paramters issue

From: LS Cheng <>
Date: Mon, 8 Mar 2010 09:22:27 +0100
Message-ID: <>


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.



On Mon, Mar 8, 2010 at 3:46 AM, Kumar Madduri <> wrote:

> Hi
> Our reporting developers/management started to complain about their reports
> running slow after upgrade to 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 =
> ""'
> ;
> 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
Received on Mon Mar 08 2010 - 02:22:27 CST

Original text of this message