Recommended Database Parameters Updated for EBS 11i

Experienced Apps DBAs know that there are often compelling reasons to tweak the E-Business Suite's database initialization parameters from the defaults.  The master source-of-truth for whether certain parameter settings will help or hurt your EBS environment performance is published here:

Our EBS database architects have just released an updated version of that Note.  Recent updates over the last month include a number of important changes and additions to our recommendations for:

  • Database parameters that should be removed for 10gR2 (10.2.x) databases
  • Database parameters that should be removed for 11gR1 (11.1.x) databases
  • Advanced Queuing (AQ) additions to 10gR1, 10gR2, and 11gR1 recommendations
  • Database initialization parameter sizing recommendations for processes, sessions, db_block_buffers, db_cache_size, sga_target, undo_retention, shared_pool_*, pga_aggregate_target, and total memory required for different numbers of concurrent users
  • New links to related documents

One of our architects wryly observed that this relatively-short list of changes belies the actual importance and impact of the changes to our recommendations. 

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.

Related Articles

 

Comments:

Hi Steven

can we assume a similar review of parameters for R12 is imminent ?

Thanks

Neil

Posted by Neil on August 13, 2009 at 11:04 AM PDT #

ML Note 216205.1 is my fav. note on ML. Some time I refer this doc for init.ora setting for non-apps DB ( Except Siebel DB ) too, Because these parameters are well tested.

Posted by Virag Sharma on August 13, 2009 at 01:21 PM PDT #

Note 216205.1 has this update in the change log.

20-Jul-2009 Added db_file_multiblock_read_count to 10gR2 (10.2.X) removal list.

The parameter is still in the common settings section but then added to the removal list under Section 2.4.1: Removal List for Oracle Database 10gR2 (10.2.X).

If this is true for 10.2x database applications will the bde_chk_cbo.sql script also be updated to reflect this recommendation? I have ran the latest updated bde_chk_cbo.sql dated August 5th and it still shows that db_file_multiblock_read_count is a mandatory setting.

Thanks!

Posted by Jody on August 13, 2009 at 10:24 PM PDT #

Steven,

The mandatory parameters for 11gR1 still refer to sga_target and pga_target rather than memory_target. Is that an implication that you don't support the use of automatic memory management on 11g for EBusiness Suite?

Regards,
Alan.

Posted by Alan Goodall on August 13, 2009 at 11:22 PM PDT #

Has there been any thought to putting recommendations in the note for using automatic memory management?

Posted by Jay Weinshenker on August 13, 2009 at 11:24 PM PDT #

Thanks for everyone's comments so far. I've asked our database architects to comment on your questions about their recommendations for automatic memory management, the bde_chk_cbo.sql script, and whether they're planning a similar review for the R12-equivalent of this Note.

I'll post updates here as soon as I get responses from them.

Regards,
Steven

Posted by Steven Chan on August 14, 2009 at 02:44 AM PDT #

Alan, Jay,

I received an update on automatic memory management. Our EBS database architects have been in deep discussions with the Server Technologies team and AMM developers on the use of this feature in EBS environments. These discussions are still underway. We are conducting internal tests using Oracle's own multi-terabyte production EBS environment to quantify the benefits and come up with specific recommendations on the use of AMM.

In the meantime, if you've conducted any AMM benchmarks for your EBS environments, I know that our architects would be very interested in hearing about your experiences.

Regards,
Steven

Posted by Steven Chan on August 14, 2009 at 04:19 AM PDT #

Hi, Jody,

Thanks for letting me know about the bde_chk_cbo.sql inconsistency. Notes 216205.1 and 396009.1 are being updated right now by that parameter's owner to correct that error. The new docs will be updated and rereleased shortly.

Regards,
Steven

Posted by Steven Chan on August 17, 2009 at 02:49 AM PDT #

Hi, Neil,

Our database architects have confirmed that they're working on updates to the R12 version of this Note, too. Stay tuned; I'll post an announcement here as soon as the new version is released.

Regards,
Steven

Posted by Steven Chan on August 17, 2009 at 02:53 AM PDT #

Hi Steven,

I’m struggling with one of the updates to note 216205.1 recommending that you to remove the db_file_multiblock_read_count parameter. We are on 10.2.0.4 and have had this parameter set to “8” for almost a year now. Our performance tests, thus far have not been conclusive about which setting performs better. There is a lot of information out there on Metalink and the Internet about this parameter with differing opinions. I opened an SR to try and get some definitive info. If you remove that parameter and then see what Oracle sets it to, it comes back as 128. According to my SR, that’s a little high. They recommend updating my system statistics. However, I found an article at http://hungrydba.com/10gsystemstats.aspx that, according to their tests, setting db_file_multiblock_read_count manually to 128 produces a different explain plan than does letting Oracle set db_file_multiblock_read_count to 128.

I have not reproduced these tests myself but wanted to get your opinion on this. Perhaps it would help if to know what happened or was discovered that prompted the recommendation to remove this parameter.

Thanks…

Posted by Chris Kettner on August 25, 2009 at 12:25 AM PDT #

Hi, Chris,

I've asked our database architects to comment on the EBS tests that led to the recommendation to remove this parameter. I'll post any responses that I might get from them.

Regards,
Steven

Posted by Steven Chan on August 25, 2009 at 04:52 AM PDT #

Hi, Chris,

Our architects got back to me on this. The removal of db_file_multiblock_read_count does warrant more background information, and we're looking into getting our documentation updated accordingly.

In advance of those updates, here's the scoop:

The db_file_multiblock_read_count parameter used to perform two functions: control both the physical multi-block I/O count and the optimizer cost weighting for full tables scans. These functions were split. If unset, the optimizer uses 8 [ the value we used to recommend for db_file_multiblock_read_count ] and the physical multi-block count to whatever the port can support [ usually 128 ]. But as per the docs, if you explicitly set db_file_multiblock_read_count then this becomes the cost input as well as physical i/o count. See http://st-doc.us.oracle.com/10/102/server.102/b14237/initparams047.htm#CHDFAFHE

Regards,
Steven

Posted by Steven Chan on August 26, 2009 at 04:09 AM PDT #

Steven,

Finally had the chance to review the document, and I have found it a very contradictory. How can _sqlexec_progression_cost be a "common database initialization parameter" in Section 1, but also show up on the "removal list for Oracle Database 11g R1" in Section 2.5.1? This is just an example; there are others such as db_block_buffers, dump_dests, etc. Seems like a very awkward way of organizing the listing.

Posted by Kevin Kempf on August 26, 2009 at 04:38 AM PDT #

Hi Steven,

We are starting a project to implement a CRM solution with our Oracle ERP system. What are some of your integration experiences with Siebel CRM On Demand and Salesforce.com with Oracle ERP?

Thanks in advance,

Best Regards

Martha W.

Posted by Martha on September 15, 2009 at 11:11 AM PDT #

Hi, Martha,

Glad to hear that you're embarking on this road. I don't have a lot of experience or visibility into other customers' projects in this area. You might wish to ask the Application Integration Architecture (AIA) whether they can share their insights or tips on their blog:

http://blogs.oracle.com/aia/

Good luck with your project.

Regards,
Steven

Posted by Steven Chan on September 16, 2009 at 04:49 AM PDT #

Hi Steven,
I have installed ebsR12 on a server running oel 5.5.
Now this linux machine is cable connected to my home office lynksys router.
How do I connect my laptop as a client to the Linux server R12.1.1

Thank you, in advance for your help.

Regards,
Manny

Posted by Manny on August 23, 2011 at 02:29 PM PDT #

Hi, Manny,

You can connect your laptop to the same router via a cable or wireless connection.

You might find this R12 Upgrade discussion forum useful:

https://forums.oracle.com/forums/forum.jspa?forumID=395

There a lot of tips there from users on setting up small EBS 12 test environments on personal systems.

Regards,
Steven

Posted by Steven Chan on August 24, 2011 at 04:03 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today