Hidden paramters issue

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Sun, 7 Mar 2010 18:46:35 -0800
Message-ID: <a2b1e7611003071846r32e6a691u31c70bfcaa7e5d42_at_mail.gmail.com>

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

Received on Sun Mar 07 2010 - 20:46:35 CST