Forum Stats

  • 3,827,999 Users
  • 2,260,846 Discussions
  • 7,897,427 Comments

Discussions

Partitioned sequences to avoid hot block contention on indexes

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Apr 20, 2020 7:58PM in Database Ideas - Ideas

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

Franck PachotMartin PreissDavid HueberDani SchniderUser_T3EKAnico_nijhervedbi880720Lothar FlatzberxIvica ArsovGugs-Oraclevinaykumar2pankajrangacaadecarvalhoPravin TakpirectriebManish ChaturvedibhagatsinghBPeaslandDBARandolf GeistborneselsysassysdbaJagdeep Sangwanuser3897193991901TribeinRyszard Styczynski-OracleSven W._LC_Emad Al-MousaBeGinkutrovskypattonjgMarco MischkeRajeshwaran, JeyabalsensoftAparna Dutta-OracleMKJ10930279KayKrohittihorLukas Ederuser13081214user12294827MhAGOUUser_JDNQ2blessed DBACherif bh3870470Loïc Lefèvre-Oraclejormart-Oracle
49
53 votes

Delivered · Last Updated

«1

Comments

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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
    top.gun Member Posts: 3,666 Gold Crown

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

    Is there no other solution to fix the application?

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    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
    top.gun Member Posts: 3,666 Gold Crown
    edited May 10, 2015 3:50AM

    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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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
    top.gun Member Posts: 3,666 Gold Crown

    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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    ... 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

    Franck Pachot
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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
    Cherif bh Member Posts: 158 Bronze Badge
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jan 29, 2019 11:39AM

    >>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".

    Peter Hraško