Recommended Database Parameters Updated for EBS 12

Close on the heels of our recent updates to the recommended EBS 11i Database parameters, our database and applications performance architects have published updates for EBS Release 12. The updated note is now available here:

Our architects have refreshed a large number of recommended settings based upon customer feedback and their ongoing performance and scalability tests.

ShowParam.png

New updates

  • Removal of Application Upgrade Considerations section.
  • Addition of Advanced Queuing (AQ) note to common parameters.
  • Addition of note to the job_queue_processes
  • Addition of diagnostic_dest to 11gR1-specific parameters.
  • Addition of background_dump_dest, user_dump_dest, core_dump_dest to 11gR1 removal list.
  • Addition of plsql_native_library_dir, plsql_native_library_subdir_count, plsql_optimize_level to 11gR1 removal list.
  • Addition of #MP to _optimizer_autostats_job=false.
  • Updates to undo_retention section.
  • Addition of Timed_Statistics.
  • Addition of nls_language, nls_length_semantics, _sqlexec_progression_cost.
  • Removal of nls_length_semantics.
  • Removal of SGA_TARGET from Sections 2, 3, and 4, as this is in the common parameters.
  • Removal of db_block_buffers row from sizing table.
  • Updates to "Specific Notes on Table."
  • Updates to common parameters introduction.

As we've noted before, 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.

Related Articles

Comments:

Thank you for the update Prasad.

I have one question and one comment on these parameters.

First the question, is the use of AMM (memory_target) supported or recommended for use when running R12 on 11g? We recently went live on 11.1.0.6 using AMM but reverted back to ASMM (sga_target) due to performance issues in our custom programs. We have recently upgraded to 11.1.0.7 but have not tested going back to AMM.

The comment, for those upgrading from 11.1.0.6 to 11.1.0.7 remember to set all of the hidden parameters back to the recommended values. Interoperability Notes Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (Doc 735276.1) does not mention it but you will find that the upgrade will reset these parameters and if you run your production without these parameters you will experience very poor system performance.

Posted by Joshua Hernandez on September 16, 2009 at 11:01 PM PDT #

Joshua,

Thanks for the comment. MEMORY_TARGET is not certified with EBS yet. Our architects would like to hold off on it until large workloads are run. Looks like it will be of less benefit for EBS customers unless they use it in virtualized environments.

Regarding the resetting of parameters, I will have it checked and updated in the interop note.

-Prasad

Posted by Prasad Akkiraju on September 16, 2009 at 11:17 PM PDT #

Hi, slightly off-topic but ...
The O/S requirements have changed from 11i -> 12 with regards to the ulimits.
the ulimit -s for 11i seems to be the same as for 10gR2 DB i.e ulimit -s => 32768
For Apps 12 the documentation has changed to ulimit -s unlimited.
This is causing me some problems on a Solaris 9/10 box as the stack is reserving 2G of space and in a JVM this leaves only about 1.7G for heap space. This size JVM is too small for some BI Publisher reports.

Is there a neccesity for ulimit -s to be unlimited ? If not can you recommend a value? . An SR has already been raised with Support (and Sun) but there didn't seem to be a definitive answer.

Thanks
dominic

Posted by Dominic West on November 17, 2009 at 08:00 PM PST #

Has the MEMORY_TARGET been certified with EBS yet?

Posted by Carl Busch on February 20, 2011 at 11:38 PM PST #

Not yet. Our performance team's general guideline is that it will not be of benefit for medium to large systems, might be useful in small or development deployments. There is a plan to add details in the init.ora note, but it is not of high priority.

Posted by Prasad Akkiraju on February 20, 2011 at 11:58 PM PST #

Any update on Memory_target certification?

Posted by guest on March 07, 2012 at 02:59 PM PST #

Hi, Guest,

Note 396009.1 was revised last December with this addition:

Footnote 1
----------
The parameter sga_target should be used for Oracle 10g or 11g based environments such as Release 12. This replaces the parameter db_cache_size, which was used in Oracle 9i based environments. Also, it is not necessary to set the parameter undo_retention for 10g or 11g-based systems, since undo retention is set automatically as part of automatic undo tuning.

Enabling the 11g Automatic Memory Management (AMM) feature is supported in EBS, and has been found to be useful in scenarios where memory is limited, as it will dynamically adjust the SGA and PGA pool sizes. AMM is enabled by using the memory_target and memory_max_target initialization parameters. MEMORY_TARGET specifies the system-wide sharable memory for Oracle to use when dynamically controlling the SGA and PGA as workloads change. The memory_max_target parameter specifies the maximum size that memory_target may take. AMM has proven useful for small to mid-range systems as it simplifies both the configuration and management. However, many customers with large production systems have experienced better performance with manually sized pools (or large minimum values for the pools). On Linux, Hugepages has resulted in improved performance; however, this configuration is not compatible with AMM. For large mission-critical applications systems, it is advisable to set sga_target with a minimum fixed value for shared_pool_size and pga_aggregate_target.

Regards,
Steven

Posted by Steven Chan on March 09, 2012 at 10:33 AM PST #

Hi Steven,

We are in the middle of the 11.5.10 to 12.1.3 upgrade. Oracle database is 11.1.0.7. If we use AMM by setting the memory_target, we are debating whether there is any issue/need in setting the SGA_TARGET?

*.sga_target=7G
*.memory_target=16G

Thanks
Allison

Posted by guest on October 31, 2012 at 03:21 PM PDT #

Allison,

The response from the performance team is that memory_target is not recommended for large production systems. sga_target is a subset of memory_target. If you have specific values set for shared pool size and db cache, those values together would limit to sga_target if you set, despite having a higher memory_target. So better not set sga_target in case you want to use memory_target.

Regards,
Prasad

Posted by Prasad Akkiraju on November 05, 2012 at 08:39 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today