RE: Increasing partitions and creating partitions

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 1 Oct 2016 08:13:11 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90150341D48_at_exmbx05.thus.corp>

I agree whole-heartedly with Tim's analysis of the situation.

Here's a little thought - if your LARGEST table is 450M rows then splitting it into 1024 partitions leaves you with an average of 450,000 rows per table which (unless you've got a very unusual system - or a table with some LOB columns and you're trying to reduce the size of the LOB segment) is a very small partition in terms of rowcount.

I'm guessing that the advice is hash partitioning (given the power of 2 for the number of partitions) and I'm guessing that the advice is based on some wait time report about contention on indexes leaf blocks. But you need to have a report which says the consultant understands why the SPECIFIC problem exists (not just a "this is the type of thing that can happen if ...") and why more partitions will have a significant impact on the problem, and (ideally) a good estimate of how much benefit it will give, and a reminder of the unfortunate side effects it will cause, and why they shouldn't matter in your case (or why they will matter but less so than the current problem).

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Tim Gorman [tim_at_evdbt.com] Sent: 30 September 2016 14:29
To: balwanthdba_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Increasing partitions and creating partitions

Hopefully the "senior consultants" did more than just wave their hands and mysteriously intone "add more partitions"? They should have explained exactly why more partitions would help, and how to do implement their suggestions, else they aren't who they claim to be.

Can you share the reasoning they provided?

It is a charlatan's trick to recommend a lengthy and complex course of action, and then be long gone with the money before the recommendation is attempted. In all likelihood, little or no improvement will be realized. Then, of course, those who attempted to implement the vague recommendation will be cited for having missed some important detail or other.

Can you also provide more information about the 10 tables?

• what type of partitioning is used on these 10 tables (i.e. range, hash, list, or if composite, range-hash, etc)?
• are all indexes local or are some global, particularly the unique indexes enforcing the primary- or unique-key constraints?
• how much downtime is available to implement table and index rebuilds?

On 9/30/16 05:45, Balwanth B wrote:

Please need your suggestions for below

Hello everyone,

Need your suggestion on below

Version 11.2.0.4

We are having 10 production database tables with millions of records( biggest one is 450 million records... highly transaction databases).. some tables are already partitioned( now we have to increase the number of partitions up to 1024 partitions advice given by senior consultants based on same running at different environment)... All this tables have referential integrity between them.. can you please advice me what is best practice to increasing number of partitions and creating the partitions for existing tables...

Your suggestions will be much helpful.

Thanks,

Bobilli

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Oct 01 2016 - 10:13:11 CEST