EBS partitioning

[Laurel]

Hi Erman!
Let me ask you  about  partitioning on EBS. Yes, we are planning to buy partitioning license for customiz if we are go for it.
My customer having for a 1 YEAR:
GL_Balances  for a year 102 mln rows.
CST_AE_HEADERS 6 mln:
CST_AE_LINES 118 mln
MTL_PAC_ACTUAL_DETAILS - 56 mln
MTL_TRANSACTION_ACCOUNTS 7 mnl
MTL_PAC_TXN_COST_DETAILS 7 mnl

We cannot purge each year..
I have found the metalink note 554539.1 about EBS partitioning..

Have you done custom partitioning ? The key is to select the correct partition key and then test correctly afterwards.. :)
Any suggestions would be appreciated
Thx,Laurel

[ErmanArslansOracleBlog]

Hi,

Most of the time we leave this decision to the Developers+Functionals.
But, the document you sent already has the info about the design considerations.
What you can do is to monitor your database and queries for a while (during a period where you can see the peaks and the general load) and see the predicates and the columns visited by your queries. Thus, you can design your partitions according to your queries.

[Laurel]

Hi Erman!
Thx! In our case Developers and Functionals  expect DBAs to predict and be pro-active..
So, are these numbers look high for you  as you are experienced Apps Dba and has seeing alot of customers EBS databases. ?
Should I start preparations soon...
I dont have anything to compare with..
Thx,Laurel

[ErmanArslansOracleBlog]

50 million, 100 milllion, these are high and also those counts will be increasing as the time passes.
so if you are considering partitioning, why not..
But, remember the licenses. EBS comes with partitioning, but you do any partitioning yourself then you ll need to license..

You can compare it by doing a test in TEST env.
Check your queries before partitioning and after.  Check explain plans and real timings.
Check your routines(concurrents etc..), that do insert/update on the tables and ensure that they are also not getting affected negatively.
Then you can have a better understanding of its benefits, whether it is required or not..Again keep in mind that your data will be increasing as the time passes.