Re: Increasing partitions and creating partitions

It is important that they have correctly identified the problem by narrowing down to a specific set of SQL statements, upon which their proposed solution can then be proved or disproved by anyone.

There are a variety of tools discussed on this list to first identify the problem SQL, and then there is a plethora of expertise available on this list to help diagnose each of the identified SQL statements.

If they have not identified specific SQL statements, then they are guessing, and that plus four bucks will buy a cappuccino.

On 10/1/16 02:13, Jonathan Lewis wrote:
>
>
> 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