Forum Stats

  • 3,780,462 Users
  • 2,254,398 Discussions
  • 7,879,337 Comments

Discussions

enq: SQ - contention -

0614
0614 Member Posts: 121 Red Ribbon

Hi,

we are currently using 19c DB - 4 node RAC cluster, we faced one issue enq: SQ - contention at the one-time frame,

though sequence has cache_size as 100000 got contention issue.

Please suggest and how to resolve it?

@Jonathan Lewis

Tagged:

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,935 Silver Crown

    Can you identify the Sequence causing the lock?

    Is it using ORDER or NOORDER?

    Please post CREATE SEQUENCE statement.

    tsangsir0614
  • 0614
    0614 Member Posts: 121 Red Ribbon

    Yes , i see one blocking session for that contention

    and sequence was created with no order share the screenshot

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,844 Gold Crown
    edited Nov 18, 2021 9:44AM

    Not much to go on.

    How much time are you losing to the SQ enqueue.

    You replied to @Mike Kutz that you see one blocking session for "that contention". Does this mean you have some ASH data which shows lots of sessions waiting for enq SQ, and the same blocking session for all of them? If so, do you have any information about the state of that blocking session ?

    Regards

    Jonathan Lewis


    Bear in mind that even with a cache size of 1e5 you will still get at least a moment of contention as the seq$ row is updated, and if you've got 4 nodes all working very hard you might find that it takes some time for a GC current get to pull the block to the right instance. On top of that, if you've got OTHER sequences in the same block in seq$ then updates to those other seq$ rows might delay the block's movement enough to make the caller see a significant delay.

    (The mental model here is that if you've set cache size = 100000 then every instance is making heavy demans on that sequence, but there may be several other sequences that are popular, but not so popular that they need to be updated frequently even to become visible.)


    Regards

    Jonathan Lewis