Question on db_cache_size

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Sun, 17 Jul 2011 01:35:13 -0700
Message-ID: <CAHDOOG6Wgsi5wTaUip4C5ZrTKpyfrLY6gwS2d4y-t_jBpdym0w_at_mail.gmail.com>

Hi
I am upgrading from 11gR1 to 11gR2. As part of this, I am cleaning up some init.ora parameters that exists from the older versions. As per note 396009.1, the parameter db_cache_size should not be set. But I noticed that when I unset it performance suffers for operations like data pump and concurrent jobs (this is an ebiz 12.1.2 env) that fetch lot of data. Some concurrent programs dont even run if the db_cache_size is not set.
The predicament is that, Oracle Ebiz development swears by the document 396009.1 (
http://www.oracle.com/technetwork/apps-tech/collab2011-tuning-ebusiness-421966.pdf). In this article, reference to 396009.1 is given and in this http://blogs.oracle.com/stevenChan/entry/recommended_database_parameters_updated_for_ebs_11

 "Don't be fooled -- these changes are extremely important and can have profound impact on the performance of your Apps database. All Apps DBAs should spend some quality time comparing your current database settings with the latest recommendations in this document." but when you dont set them there are issues. I want to follow oracle's recommendation but our experience shows that db_cache_size cannot be unset.
Pasting this information from the note

4.2 Parameter Removal List for Oracle Database 11*g* Release 2

If they exist, you should remove the following parameters from your database initialization parameters file for Oracle Database 11*g* Release 2 (11.2.X).
_always_anti_join
_always_semi_join
_complex_view_merging
_index_join_enabled
_kks_use_mutex_pin
_new_initial_join_orders
_optimizer_cost_based_transformation
_optimizer_cost_model
_optimizer_mode_force
_optimizer_undo_changes
_or_expand_nvl_predicate
_ordered_nested_loop
_push_join_predicate
_push_join_union_view
_shared_pool_reserved_min_alloc
_sortmerge_inequality_join_off
_sqlexec_progression_cost
_table_scan_cost_plus_one
_unnest_subquery
_use_column_stats_for_function

always_anti_join
always_semi_join
background_dump_dest
core_dump_dest
db_block_buffers
*db_cache_size*
db_file_multiblock_read_count
DRS_START
enqueue_resources

event="10932 trace name context level 32768"
event="10933 trace name context level 512"
event="10943 trace name context forever, level 2"
event="10943 trace name context level 16384"
event="38004 trace name context forever, level 1"
hash_area_size

java_pool_size
job_queue_interval
large_pool_size
max_enabled_roles
nls_language

optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
optimizer_mode
optimizer_percent_parallel
plsql_compiler_flags
plsql_native_library_dir
plsql_native_library_subdir_count

plsql_optimize_level
query_rewrite_enabled
rollback_segments
row_locking
sort_area_size
sql_trace
SQL_VERSION
timed_statistics

undo_retention
undo_suppress_errors
user_dump_dest

Want to know the experience of others in setting/unsetting db_cache_size

Thank you
Kumar

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

Received on Sun Jul 17 2011 - 03:35:13 CDT