Many times, Oracle generates a new cursor for a query that already has a child cursor in the shared pool. When this happens, people often ask the question “why is my cursor not being shared?”

Fortunately, there’s an Oracle view that can help us: v$sql_shared_cursor.

The layout of this view is such that the “primary key” is SQL_ID, ADDRESS, and CHILD_NUMBER. Also, dozens of “Y” or “N” flags are in each row. Each of these flags specify the reasons that Oracle wasn’t sharing your cursor. Because of these many Y/N columns, it’s often tricky to query and read the view. To find the reason your cursor wasn’t shared, you end up looking along looking for one or more “Y” columns across a slew of “N”s. This becomes difficult to digest with your eyes.

You can make things much easier with an unpivot! Now you can see exactly why Oracle was using a new child cursor. All without straining your eyes to read across dozens of columns.

Unfortunately, the columns within the view change between various versions of Oracle. So if I posted a version of the query that works in one version or Oracle, it may not work in another.

Therefore, I’ve done the next best thing!

I created a query…
That will generate a query…
…That will work with any modern version of Oracle.

 select q'<select *
 from gv$sql_shared_cursor
 unpivot ( flag for reason_not_sharing in (>' || listagg(column_name, ', ') within group (order by null) || q'<) )
 where sql_id = nvl('>' || '&' || q'<v_sql_id.', sql_id)
   and flag = 'Y'>'x
 from dba_tab_columns
 where owner = 'SYS'
   and table_name = 'V_$SQL_SHARED_CURSOR'
   and data_type = 'VARCHAR2'
   and data_length = 1;

With this query, you can quickly know why a new child cursor was created.

Hopefully you found this useful! Comment below if you the script gave you any difficulties, or if you’ve found a version of Oracle that this query doesn’t work with, and I’ll be happy to make the necessary adjustments.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here:

7 SQL Tuning Secrets You can Use Immediately, Even If You've Never Tuned A Query In Your Life

Leave a Reply

Your email address will not be published. Required fields are marked *