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
@jloracle
------------------------------------------------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Tim Gorman [tim@xxxxxxxxx]
*Sent:* 30 September 2016 14:29
*To:* balwanthdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*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
