THIS USER ASKED 👇

Partitioned sequences to avoid hot block contention on indexes


Franck Pachot

Hi,

This idea is about something that exists already but is not documented – so we can’t use it. The idea is to make it available.

Populating a primary key from sequence can be bad for scalability because all new (and concurrent) inserts are updating the same index block. Reverse indexes is not a very good idea because it spreads the index updates on all leaf blocks, which is too much to be kept in cache. Hash partitioning the index is better, but still show contention in RAC because several instances are updating the same set of blocks.

A good solution is to prefix the number coming from the sequence by something that is determined by the session. Or have a composite primary key with first column coming from a hash of the instance+session number and the second one from the sequence.

This is possible only when the application code can be changed and nowadays some developers and frameworks don’t like composite keys.

The idea is to have sequences returning sequences is several ranges of values.

For example, one session selecting the nextval will get:

20000000000000000000000000001

20000000000000000000000000002

20000000000000000000000000003

20000000000000000000000000004

20000000000000000000000000005

20000000000000000000000000006

then another session will get:

10000000000000000000000000011

10000000000000000000000000012

another one:

00000000000000000000000000013

00000000000000000000000000014

and another one:

20000000000000000000000000015

20000000000000000000000000016

The number of hash values would be defined as a sequence attribute, to be set according to the expected number of sessions concurrently inserting.

The prefix should be hashed from instance number (to avoid for GC contention) and from session or process number (to avoid buffer contention).

More details in the following blog post about the undocumented 12c feature that does something like that:

Oracle Partitioned Sequences – a future new feature in 12c? – dbi services Blog

Regards,

Franck.

This has been implemented with Oracle Database 18c: 24.2.4.3 Making a Sequence Scalable

49
53 votes

Delivered ·
Last Updated Apr 20, 2020 7:58PM

«12»

Comments


  • BPeaslandDBA

    This is a good idea whose time has long come about. Right-sided index block contention in Oracle RAC environments is a problem. Reverse Key indexes, while a solution, rarely work in most cases because then one loses the ability to perform index range scans. Hash partitioning can help, but one needs to license that extra feature and then there’s that issue with index range scans again.


  • top.gun

    I see this as a work around for an application problem.

    Is there no other solution to fix the application?


  • Franck Pachot

    I see this as a work around for an application problem.

    Is there no other solution to fix the application?

    Right, but do you know any application that does that? Lot of applications are not designed for concurrency.


  • top.gun

    Right, but do you know any application that does that? Lot of applications are not designed for concurrency.

    So this feature would be for developers that don’t want to design their application for concurrency.

    I can just see developers go to town with this idea and not bothering to design their application for concurrency.


  • BPeaslandDBA

    So this feature would be for developers that don’t want to design their application for concurrency.

    I can just see developers go to town with this idea and not bothering to design their application for concurrency.

    The larger problem, from my experience, is that too many 3rd party vendors aren’t adequately coding their apps for Oracle RAC. Then when deployed on Oracle RAC, sequences are one of the first pain points. With @Franck Pachot’s idea, we could recreate the sequence to make it more RAC friendly. This will be more important as many companies move to DBaaS with Multitenant in their own private clouds.

    Cheers,
    Brian


  • top.gun

    The larger problem, from my experience, is that too many 3rd party vendors aren’t adequately coding their apps for Oracle RAC. Then when deployed on Oracle RAC, sequences are one of the first pain points. With @Franck Pachot’s idea, we could recreate the sequence to make it more RAC friendly. This will be more important as many companies move to DBaaS with Multitenant in their own private clouds.

    Cheers,
    Brian

    ... too many 3rd party vendors aren't adequately coding their apps for Oracle RAC.
    

    you’ve confirmed that this solution is a workaround for a vendor responsibility…


  • BPeaslandDBA

    ... too many 3rd party vendors aren't adequately coding their apps for Oracle RAC.
    

    you’ve confirmed that this solution is a workaround for a vendor responsibility…

    I don’t disagree with that sentiment. Too many 3rd party app vendors don’t write RAC-scalable applications. Thankfully Oracle Corp provides a number of workarounds that one can employ to (ahem) assist the vendor since we can’t touch their code. I know that I’ve used any number of the following to tune 3rd party app performance:

    1. Change the CACHE value on a sequence.
    2. Recreate an index as REVERSE KEY
    3. Plan Stability
    4. CURSOR_SHARING

    And so much more. Either most Oracle DBAs have used a workaround or two to get around 3rd party app performance issues, or they’ve been very lucky in their careers and not had to resort to such things.

    I don’t know about you, but whenever I have an issue with a 3rd party vendor with their app’s terrible performance, it often takes a very, very long time for them to get me a patch that addresses the problem. I’m happy when I can make my end user’s day by employing any such workaround until such time as the vendor gets me that patch.

    Now back to your earlier comment. “So this feature would be for developers that don’t want to design their application for concurrency.”

    If you developed an application that is deployed on Oracle RAC, and you used a sequence for primary key values, how would you avoid right-sided index contention for a high degree of concurrent inserts? You could use reverse key indexes or hash partition the index, but usage of either structure eliminates the use of index range scans. So those options may fix one performance problem, but give you other performance problems. Let’s take those two options off the table. So I’ll challenge you…how would you design your application for concurrency? What solution would you employ to reduce the impact of right-sided index contention for a high degree of concurrent inserts?

    Cheers,
    Brian


  • top.gun

    I don’t disagree with that sentiment. Too many 3rd party app vendors don’t write RAC-scalable applications. Thankfully Oracle Corp provides a number of workarounds that one can employ to (ahem) assist the vendor since we can’t touch their code. I know that I’ve used any number of the following to tune 3rd party app performance:

    1. Change the CACHE value on a sequence.
    2. Recreate an index as REVERSE KEY
    3. Plan Stability
    4. CURSOR_SHARING

    And so much more. Either most Oracle DBAs have used a workaround or two to get around 3rd party app performance issues, or they’ve been very lucky in their careers and not had to resort to such things.

    I don’t know about you, but whenever I have an issue with a 3rd party vendor with their app’s terrible performance, it often takes a very, very long time for them to get me a patch that addresses the problem. I’m happy when I can make my end user’s day by employing any such workaround until such time as the vendor gets me that patch.

    Now back to your earlier comment. “So this feature would be for developers that don’t want to design their application for concurrency.”

    If you developed an application that is deployed on Oracle RAC, and you used a sequence for primary key values, how would you avoid right-sided index contention for a high degree of concurrent inserts? You could use reverse key indexes or hash partition the index, but usage of either structure eliminates the use of index range scans. So those options may fix one performance problem, but give you other performance problems. Let’s take those two options off the table. So I’ll challenge you…how would you design your application for concurrency? What solution would you employ to reduce the impact of right-sided index contention for a high degree of concurrent inserts?

    Cheers,
    Brian

    I think it’s time to concede…..

    Even if the vendor were asked to fix the application, they can still just put in a few workarounds, and call it a solution.

    With this partitioned sequences idea, if it’s undocumented now, it’s likely to come online in a future release anyway.


  • Cherif bh

    Hi Franck,

    I think your idea was taken into account

    18c Scalable Sequences Part I (Saviour Machine)

    Thanks,

    Cherif BEN HENDA


  • Sven W.

    >>Or have a composite primary key with first column coming from a hash of the instance+session number and the second one from the sequence.
    This is almost excatly like scalable sequences have been implemented. And they are now documented with 18c.

    The idea should be considered to be “delivered”.

«12»

Via community.oracle.com