Using Database Partitioning with the E-Business Suite

[Nov 13, 2009 Update: Removed outdated reference to alterations to preseeded partitioned objects.]

[May 27, 2008 Update:  Added link to latest official documentation for DB partitioning for Apps 11i]

A frequently-asked question is, "Can I use the database partitioning feature in my E-Business Suite environment?"  The answer to this question is yes:  the use of custom partitioning with the E-Business Suite is fully supported.  In addition, several Apps modules take advantage of partitioning right out of the box.

Database Partitioning Methods:

What Does "Fully Supported" Mean?

If custom partitioning causes a particular E-Business Suite flow or transaction to fail and the failure is caused by standard Apps product code, it is considered a product defect, as Oracle Applications is committed to being transparent to custom partitioning.  Oracle Development will issue patches or workarounds for all reported issues with standard Apps product code.

What's Custom Partitioning?

Custom partitioning applies when an existing standard Apps product table is not partitioned and the table is redefined as a partitioned table by:
  1. Using the range, list, hash, or composite partitioning method; or,
  2. The partition scheme and/or partitioning method of an existing standard product table which is already partitioned (as part of the standard product) is altered from that which is included in the base product.
Database Partitioning Methods 2:

Examples of Custom Partitioning

For example, if you choose to partition the table OE_ORDER_LINES_ALL which is currently not partitioned in the standard product, then this is an example of custom partitioning. 
Standard Apps Partitioning "Out of the Box"

Oracle Applications utilizes partitioning in the standard product including the following modules:
  • Advanced Planning and Scheduling
  • Payables (Trial Balances)
  • Projects Resources
  • Workflow
  • Directory Services
  • Daily Business Intelligence
  • HR (Employee Directory)
  • Engineering
  • and other products...
In cases where an Apps table has a natural and logical partition key and the majority of the runtime access path (of the standard product) is based on this natural partition key, Oracle Applications will investigate delivering the table as a partitioned table as part of the standard installation.  

Partitioning Based on Functional Implementation

Many E-Business Suite tables do not have a natural partitioning key which would apply to all customers, simply because the data distribution and access path is highly dependent on each customer's functional implementation.  However, you are free to partition the tables in a logical manner based on your own requirements.

For example, many of our Oracle Financials customers partition the table GL_BALANCES by either period_name or set_of_books_id, depending on their implementation:
  • Some customers have a large number of books for which partitioning by set_of_books_id makes sense
  • Other customers use a single or a few set of books for which period_name makes sense. 
Since the majority of the GL runtime SQL includes the filters period_name or set_of_books_id, these columns are logical choices for the partition key.

Using Partitioning to Boost Performance

The use of custom partitioning can improve the performance and manageability of your E-Business Suite environment, and many customers are already seeing the numerous benefits of custom partitioning.  Choosing the optimal partitioning method and partition key requires careful and thorough analysis of your system including the access paths of the relevant tables.  For examples and guidelines, refer to the presentation and forma ldocumentation:
If you're interested in more details about partioning and purging Apps environments, Ahmed Alomari is reprising this popular session at OpenWorld 2006, also.

Testing & Licencing

It is important that you thoroughly test the affected Apps modules after implementing custom partitioning, to ensure that your objectives of employing custom partitioning -- including performance and manageability -- have been achieved.

Just one final thing:  if you implement custom partitioning, you must license the database partitioning option.  Your Oracle account representative is always your best source for licensing details. 

References
Related Articles

Comments:

Excellent entry, as always. Thanks.

Posted by Kevin Krause on September 07, 2006 at 01:05 AM PDT #

Good post on partitioning, but that said, what is in plan for 12i regards partitioning? Especially around core modules such as GL,AP,AR, etc?

Posted by Chris Balodis on September 07, 2006 at 03:40 AM PDT #

Thank you for the feedback Kevin. In R12, you can employ partitioning on the financials tables including GL, AP, and AR as per your requirements. As mentioned in the writeup, it is difficult to deliver a standard partitioning scheme, which will work for every customer as the functional implementation differs between customers.

Posted by Ahmed Alomari on September 07, 2006 at 05:51 PM PDT #

Steve,

This is an excellent article on partitioning in apps.

I have been an active reading of yours and others on this blog site. Given the inspiration, I have started my own "Oracle APPS Technical" blog to help the learners of Oracle Apps.
My blog is...
http://appstechnical.blogspot.com
or
http://oracle.anilpassi.com ( to keep menu's per module)

Is there somehow my blog can be considered for listing on the listing in external list of blogs?

Thanks,
Anil

Posted by Anil Passi on September 07, 2006 at 09:29 PM PDT #

Anil, thanks for the feedback and welcome to the Apps blogging community.I've added your blog to my blogroll and have notified the OTN Blogging Editors of your site, for consideration for the master Oracle Blogs index.Regards,Steven

Posted by Steven Chan on September 07, 2006 at 11:56 PM PDT #

dear Ahmed alomari,
i wish to see posts for you soon on steven's blog about preformance tuning new hints and tips.

thanks
fadi hasweh

Posted by Fadi Hasweh on September 10, 2006 at 02:12 AM PDT #

Jenny,You've been informed correctly:  in general, if you wish to create new table partitions or change partitioning keys, you need to licence the database partitioning option separately.  E-Business Suite Release 11i licencing does not generally include the database partitioning option.  I would recommend checking with your IT Procurement department or Oracle account manager if you'd like confirmation on what's included in your current E-Business Suite contract.Regards,Steven

Posted by Steven Chan on September 12, 2006 at 03:40 AM PDT #

Steve,
Excellent article as usual.
I want to partition some of GL tables like GL_BALANCES. I was told by Oracle we have to have partition license if we want to use "custom partitioning". That includes create new partitions and change current partition keys. Can you verify this for me please? Also, is the11i license includes partition license?
Thanks,
Jenny

Posted by Jenny Yan on September 12, 2006 at 04:12 AM PDT #

hi,

is it possible to do partition(range,hash,list) on base tables in advance collection module in oracle apps 11i.
because our reports are generated from the base tables.its performance is low and now i am using global temporary table,
i m planning to do partition on base table.. so suggest me for increase the report performance..

kindly help me out.

thanks,
kanna

Posted by kannapiran on December 18, 2008 at 01:59 AM PST #

Hi, Kanna,

I don't have sufficient experience with the Advanced Collection Module to comment on whether partitioning the base table would improve performance for you.

It should be technically feasible to partition any large EBS table, including the one that you're considering here. Your performance will depend on your specific setup. If you've already licenced the Partitioning option, then it can't hurt to give it a try and benchmark your before/after results. As with all customizations, I'd recommend testing carefully before rolling this out in production.

Regards,
Steven

Posted by Steven Chan on December 29, 2008 at 05:50 AM PST #

Hi steve,
the link "Partitioning and Purging Best Practices for Oracle E-Business Suite" is giving a 404 error. can you update it ? its the last link in the article.

Posted by guest on October 11, 2011 at 10:09 PM PDT #

Hi, Robin,

That presentation is now outdated. I would recommend referring to Note 554539.1 directly at this point.

Regards,
Steven

Posted by Steven Chan on October 14, 2011 at 06:39 AM PDT #

Hi Steve,

We are looking into subpartiton XLA tables that are already partition by Oracle EBS, is this considered as the need for the partition license? Since the tables are already partition?

And secondly, will this means we get desupported by the following statement from Doc ID 554539.1 - file: Using_Database_Partitioning_with_Oracle_E-Business_Suite.pdf (598.54 KB)

Page 10: "Modifying existing base product indexes and tables that have already been partitioned is not recommended or supported..."

With this mean adding sub-partition to an already partitioned table? Please advice.

Best regards, Carlos Jimenez.

Posted by Carlos Raul Jimenez on February 29, 2012 at 11:23 AM PST #

Hi, Carlos,

My lay understanding is that any custom partitioning schemes will trigger a requirement to purchase a full-use licence.

That said, I'm in Development and can't comment authoritatively on licencing issues. For an official statement on licencing, you would need to confirm licencing terms with your Oracle account manager.

We spend a significant amount of effort in performance tuning the prepackaged partitioning schemes on high-volume EBS tables. If you change our seeding partitioning definitions, there is a chance that your changes may cause performance degradations.

If you report a performance issue that is traced back to your custom partitions, our default recommendations would likely be to revert back to the partitioning definitions that we seed out of the box.

Regards,
Steven

Posted by Steven Chan on March 01, 2012 at 09:18 AM PST #

Hi, Steve,

We're using Oracle EBS R11. The Order Management is running so slow because of the hug amount data. So, can I submit a SR to ask Metalink provide the code to partition the oe_order_headers_all table and oe_order_lines_all table?

Best Regards,
Long Nguyen.

Posted by Long Nguyen on May 07, 2012 at 01:51 AM PDT #

Hi, Long,

If you're having performance issues, you should definitely log a Service Request. It's a bit premature to speculate on what the fix might be -- it might be a code update, or require a new partitioning approach, or something else.

Regards,
Steven

Posted by Steven Chan on May 30, 2012 at 09:01 AM PDT #

Hi Steven,
With R12, I am seeing that XLA schema is having exponential growth. We see a need for sub-partition and compression as there is no standard archive and purge for XLA schema.You see any issues if we do this?

I am not sure how other customers are surviving without any better solution in this area. It will help if you can share your perspective.
Regards,
Rupak

Posted by Rupak on November 29, 2012 at 10:19 PM PST #

Rupak,

Thanks for the inquiry. We spend a great deal of time tuning Oracle E-Business Suite which includes partitioning schemes. Unfortunately, our default partitioning schemes do not always meet all of the needs of our customers.

Please note any additional partitioning you deploy, including the XLA schema is considered a customization. If you report a performance degradataion, most likely we will request that the custom partitioning be removed.

Advanced compression is certified for Oracle E-Business Suite. Advanced compression is transparent to Oracle E-Business Suite and can assist in controlling database growth.

Best of luck with your plans.
Regards,
Elke

Posted by Elke Phelps (Oracle Development) on December 10, 2012 at 07:40 AM PST #

Hi Steven,
I have a very specific question regarding the definition of "custom partitioning" on EBiz. If you have an OOTB partitioned table partitioned by module name - XLA_AE_HEADERS. Can you add a partitioned index on the table using the same partitioning key without having to licence the partitioning option? My guess is that as you'll be adding a custom object to the database which happens to be a partitioned object then it'll need to be licensed. Will also follow this up with an SR but was interested if there were any existing examples of this particular scenario?
Thanks
Guy

Posted by guest on April 25, 2013 at 06:46 AM PDT #

Hi, Guy,

I'm in EBS Development and am not a licencing specialist, so if you're looking for an authoritative answer, you should contact someone in the InfoPrice licencing team.

Here's my lay understanding: any modifications to the shipped EBS database schema are considered to be customizations and trigger the requirement for a full use Database licence.

Regards,
Steven

Posted by Steven Chan on April 25, 2013 at 10:36 AM PDT #

Hi,

I can't seem to find an answer to this and I was wondering whether you can answer it Steve. Is Oracle database partitioning required/mandatory for 11i? If so does the customer have to purchase partitioning licenses. If not then what's the best way to remove the database partition option from an EBS environment without impacting anything - you can give a doc note for this if you find it handy.

Regards,
Dharma

Posted by Dharma on June 19, 2013 at 06:25 PM PDT #

Hi, Dharma,

As noted in the article above, EBS includes partitioned tables out-of-the-box. EBS customers receive a Restricted Use licence for using those partitioned tables.

If you make any modifications to your schema, then you must purchase a Full Use licence for the Database and for the Partitioning option. You can follow up with your Oracle account manager for licencing questions.

Regards,
Steven

Posted by Steven Chan on June 20, 2013 at 09:50 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
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today