Re: Question on db_cache_size

Assuming you are using sga_target or memory_target, then using db_cache_size isn't incompatible, but more of a tweak. That is, your db_cache_size parameter is used as a lower bound.

I'd use db_cache_size if it helped performance of an important application/database process, but only after I'd addressed (increased) memory_target/sga_target "as much as I could", as I'd suspect Oracle has determined it needed more of SGA elsewhere.

Cheers, Wayne

On Sun, Jul 17, 2011 at 4:35 AM, Kumar Madduri <ksmadduri_at_gmail.com> wrote:

> 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