Solved

Direct Path Read - what is this doing?

  • 21 September 2021
  • 1 reply
  • 161 views

Userlevel 4
Badge +9
  • Sidekick (Customer)
  • 75 replies

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.

 

icon

Best answer by durette 21 September 2021, 17:21

View original

This topic has been closed for comments

1 reply

Userlevel 7
Badge +18

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