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: tuningsql.com/secrets
