Oracle has introduced new column "REASON" to V$SQL_SHARED_CURSORS. I came across this extremely useful column while debugging multiple child cursor issue ( seems to have too many bugs which can cause multiple child cursors).

As per documentation , REASON column has been introduced in

REASON - CLOB	Child number, id, and reason the cursor is not shared. The content of this column is structured using XML.

Reason column is helpful as we can easily interpret the reason why child cursors are not being shared. e.g

<reason>PQ Slave mismatch(5)</reason>
<reason>Optimizer mismatch(2)</reason>
<ID>9</ID><reason>PQ Slave mismatch(5)</reason><size>2x4</size><ctxpq_StmtId_pqctx

We can verify the same thing by checking PQ_SLAVE_MISMATCH column

select sql_id,PQ_SLAVE_MISMATCH,OPTIMIZER_MODE_MISMATCH from  V$SQL_SHARED_CURSOR where sql_id='b9uz0akdcx58q' and child_number=151;

SQL_ID	      P O
------------- - -
b9uz0akdcx58q Y N

There is one more column PURGED_CURSOR which is new (I guess it came from which tells if the cursor has been purged using dbms_shared_pool.purge package.

Leave a Reply