Re: Question on db_cache_size

  Answer to your question depends upon the workload in your database. Most EBiz database will work just fine with sga_target and sga_max_size. If your workload is higher, then you do need to customize these parameters.

  I tend to take a safer approach and avoid excessive allocation changes between buffer cache and shared pool etc. I calculate, size of buffer cache, shared pool size, java pool size, stream pool size, and log buffer; then set values for these areas. Then leave some free space (few GBs) between the sum of these areas and sga_max_size. With this approach, we can use dynamic SGA feature and reduce the risk of memory moving between the areas excessively. Of course, you should monitor v$sga_dynamic_components and monitor the allocation change activity.

   Except memory area size parameters, I adhere to other parameters listed in bde_chk_cbo script.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>

On Sun, Jul 17, 2011 at 3: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