Too many child cursors with reasons unknown
559677Jun 2 2008 — edited Jun 2 2008Hi,
I am looking at excessive child cursors for a particular SQL even after setting the CURSOR_SHARING parameter to SIMILAR. I am working on Oracle 10.2.0.4.0 hosted on a Windows 2003 Server. Since the application does not use bind variables I decided to test the efficiency of modifying the CURSOR_SHARING parameter to buy some time before the root cause is fixed.
On querying V$SQL_SHARED_CURSOR I find that none of the flag columns which document the reason for the formation of a child cursor is Y. They are all flagged as N. So, I am perplexed as to why so many child cursors are being generated.
Some statistical information below:
Version Count for the SQL_ID = 23506
Count of hash values for the SQL_ID = 1234048
Total (sum) chunk size for the SQL_ID = 112,436,220
Any clues/directions on how to get about this issue?
Regards