Add Advanced Queueing Support in Thin Mode

[mxdev88]

The documentation specifies that Advanced Queueing (AQ) is only supported in thick mode. The request is to add support to thin mode to be able to use asynchronous operations within the same application, which are not possible in thick mode.

[anthony-tuininga]

Work has been in progress on this front for the past number of weeks. I can't say which version will contain this support yet, but once it is available for testing I will let you know!

[cjbj]

@mxdev88 what specific AQ functionality are you using? E.g. array enq/deq, recipient lists, what kind of payload(s) (RAW, JSON), etc?

[mxdev88]

Initially on the consumer side and using deqone / deqmany with RAW payload.

[mkmoisen]

Hi @anthony-tuininga, @cjbj

I'm also excited to have AQ in thin mode, thank you for working on it.

So far I am using object payloads and RAW payloads. I would love to use JSON payloads but don't have the latest Oracle version that supports this.

I use single consumer queues, single enqueues and single dequeues. No agents, No recipient lists. I think in the future array enqueue and dequeue would be important.

I also set enqueue options and dequeue options.

[mkmoisen]

@anthony-tuininga @cjbj

Would you please explain what the cost is by using AQ in thin mode in the following manner, compared to using thick mode's native AQ support? My guess is that this work around requires twice as much network IO compared to the thick mode's native AQ support.

conn = oracledb.connect(...)
my_custom_type = conn.gettype('MYSCHEMA.MY_CUSTOM_TYPE')

while True:
    cur = con.cursor()
    
    payload = my_custom_type.newobject()
    
    cur.execute(
        '''
            DECLARE
                l_dequeue_options dbms_aq.dequeue_options_t;
                l_message_properties dbms_aq.message_properties_t,
                l_msgid RAW(16);
                l_payload my_custom_type;
            BEGIN
                dbms_aq.dequeue(
                    queue_name => 'MYQUEUE',
                    dequeue_options => l_dequeue_options,
                    payload => l_payload,
                    msgid => l_msgid
                );
                
                :payload := l_payload;
            END;
        ''',
        dict(
            payload=>payload
        )
    )
    
    do_something(payload)

My guess is that this results in twice as much network IO as compared to using native AQ in thick mode.

This thin-mode workaround will need to invoke an IO from the application server to Oracle, and then when Oracle has a new event, it will send it back over the same IO channel, for a total of two IO's per message.

With Native AQ in thick mode, I would guess that a socket is opened when the queue is created. Then the application server can go to sleep listening to the local socket. When Oracle gets a new message, it pipes it over the network to this socket, at which point the application server wakes up and reads it. So a total of one IO per message.

Is that right?

[anthony-tuininga]

I am sure there is some overhead in calling PL/SQL as opposed to using AQ natively, but I don't think it is quite as bad as you think. When you call dequeue(), if there is a message available it will be copied immediately into the output variable. If there is no message available and the mode is to wait for one to be available, then the PL/SQL block will "hang" until such time as the message is available and then copy it into the output variable. A similar thing happens when using AQ natively -- you simply avoid the overhead of using PL/SQL. In both native and PL/SQL the connection is not usable until such time as a message is available. I don't know exactly what happens internally, but that much is certain at least!

If you want to know for sure what the overhead is, you can try with thick mode -- both natively and with PL/SQL and see what the difference in performance is.