Skip to main content

Hi all,

 

We experience a slower performance on our Test Environment, and when looking into what could have caused this, we ran into below query:

 

SELECT 1
FROM (SELECT M.APPLICATION_MESSAGE_ID,
XMLCAST(XMLQUERY('$mb/*:Envelope/*:Body/REPLICATION/SENDER_MESSAGE_ID' PASSING XMLTYPE(B.MESSAGE_VALUE, NLS_CHARSET_ID('AL32UTF8')) AS "mb" RETURNING CONTENT) AS NUMBER)
SENDER_MESSAGE_ID FROM FNDCN_APPLICATION_MESSAGE_TAB M, FNDCN_MESSAGE_BODY_TAB B, FNDCN_APPLICATION_MESSAGE_TAB Q
WHERE M.STATE = :B2
AND M.APPLICATION_MESSAGE_ID = B.APPLICATION_MESSAGE_ID
AND Q.QUEUE = M.QUEUE AND Q.APPLICATION_MESSAGE_ID = :B1
AND B.SEQ_NO = 1
ORDER BY M.APPLICATION_MESSAGE_ID DESC FETCH FIRST 100 ROWS ONLY)
WHERE SENDER_MESSAGE_ID = :B3

 

Anyone having any idea what this query is doing? We see this query on our two environments that have UPD9 installed, not on the environments that are still on UPD7.

 

That means your session is reading directly from the disk, bypassing the central cache that gets shared with other processes. It means you’re doing full table scans, probably in parallel. The database thinks those are cheaper than using indexes.

Understanding direct path read waits (dba-oracle.com)

This might be because you have stale table statistics.

Oracle dbms_stats tips (dba-oracle.com)

It might also be because the update introduced additional columns.

 

What do you see if you do an EXPLAIN PLAN on this query?

SELECT 1
  FROM (SELECT M.APPLICATION_MESSAGE_ID,
               XMLCAST(XMLQUERY('$mb/*:Envelope/*:Body/REPLICATION/SENDER_MESSAGE_ID'
                                PASSING XMLTYPE(B.MESSAGE_VALUE,
                                        NLS_CHARSET_ID('AL32UTF8')) AS "mb"
                                RETURNING CONTENT) AS NUMBER) SENDER_MESSAGE_ID
          FROM FNDCN_APPLICATION_MESSAGE_TAB M,
               FNDCN_MESSAGE_BODY_TAB        B,
               FNDCN_APPLICATION_MESSAGE_TAB Q
         WHERE M.STATE = :B2
           AND M.APPLICATION_MESSAGE_ID = B.APPLICATION_MESSAGE_ID
           AND Q.QUEUE = M.QUEUE
           AND Q.APPLICATION_MESSAGE_ID = :B1
           AND B.SEQ_NO = 1
         ORDER BY M.APPLICATION_MESSAGE_ID DESC FETCH FIRST 100 ROWS ONLY)
 WHERE SENDER_MESSAGE_ID = :B3

 

Terms & ConditionsCookie settings