Release Update 19.22 Features

Enabling Scrambled Dequeues and Dequeue Flow Control

You can enable or disable the scrambling and flow control feature using the queue level parameter:

• To spread the concurrent dequeuers across different blocks or buffers, enable scrambled dequeues using the following queue parameter:

  DBMS_AQADM.SET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_SCRAMBLED_DEQ', 1);
  

  To disable scrambled dequeues, use the following queue parameter:

  DBMS_AQADM.UNSET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_SCRAMBLED_DEQ');
  

• To internally restrict the number of concurrent sessions that execute the dequeue select for update on a queue, enable dequeue flow control using the following queue parameter:

  DBMS_AQADM.SET_QUEUE_PARAMETER('<schema_name>.<queue_name>', 'CQ_DEQ_FLOWCONTROL', *N*);
  

  Where N is the number of internal dequeue sessions allowed to do the core dequeue work concurrently, which causes waits and contentions. Note that N must be less than the number of actual external dequeue sessions.

  To disable dequeue flow control, use the following queue parameter:

  DBMS_AQADM.UNSET_QUEUE_PARAMETER('MY_QUEUE', 'CQ_DEQ_FLOWCONTROL');
  

Using these new parameters, you can now spread concurrent dequeuers across different blocks or buffers to reduce buffer busy waits contention and to avoid row lock contention and buffer busy waits contention.

Enhanced Online DDL Capabilities in the Presence of Supplemental Logging

Numerous DDL operations were blocking operations when supplemental logging was enabled. You can use the OGG_ONLINE_DDL initialization parameter to lift this restriction for several previously blocking DDL operations when minimal supplemental logging is enabled. Those commands are namely:

• ALTER TABLE ADD COLUMN
• ALTER TABLE SET COLUMN UNUSED
• ALTER TABLE ADD CONSTRAINT ENABLE NOVALIDATE
• ALTER TABLE DROP CONSTRAINT

Non-blocking DDL operations are crucial for mission-critical environments where it is hard to impossible to guarantee an idle time window for blocking DDL operations to succeed. Supplemental logging is a key requirement for GoldenGate replication support, which creates a dilemma for customers to choose between non-blocking DDL or using GoldenGate. Enabling more non-blocking DDL operations in the presence of supplemental logging make such a decision void. Customers benefit from non-blocking DDL operations and GoldenGate.

View Documentation