Re: Hidden paramters issue

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_at_gmail.com> 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

>


--
http://www.freelists.org/webpage/oracle-l