Re: Question on db_cache_size
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Mon, 18 Jul 2011 09:21:43 -0500
Kumar
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@xxxxxxxxx> 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
>
>
Other related posts:
