Hidden paramters issue
- From: Kumar Madduri <ksmadduri@xxxxxxxxx>
- To: oracle Freelists <Oracle-L@xxxxxxxxxxxxx>
- Date: Sun, 7 Mar 2010 18:46:35 -0800
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:
