Oracle database features wish list – 2 (V$SQL_UNSHARED_CURSOR)

It’s been too long from a previous wish for Oracle. While reading a MOS updated articles (in a new HTML interface which looks nice, much better than flash-based introduction) I’ve seen this document that made me write this blog post.
As you know, Oracle has V$SQL_SHARED_CURSOR view that helps to understand why a particular child cursor has been built due inability to share existing child cursor. Since 11.2.0.2 there’s also a REASON column that “helps” by storing more data in an XML. The thing is: in my opinion this view usability is far from perfect. Why? Because 1) usually application could suffer from different issues related to cursor sharing 2) child cursors come and go as garbage without traces left in the dynamic views. So, if you are interested in just one particular child cursor, then V$SQL_SHARED_CURSOR will work just fine for you (don’t forget to use nonshared.sql from TPT). But it’s not too useful for a statistical analysis on SQL or instance level. I want to know Top 10 causes why a given SQL had unshared cursors during its lifetime. Or the same information on the instance level. These questions are impossible to answer easy way & correctly right know. That’s why it would be nice to have V$SQL_UNSHARED_CURSOR with columns SQL_ID, REASON, VALUE. For each SQL_ID it would report how many times a particular REASON of non-shared cursor was encountered. Right now there’re 64 reasons in V$SQL_SHARED_CURSOR, so to support V$SQL_UNSHARED_CURCOR it would be necessary to add at least 64 bytes to each parent cursor plus some infrastructure overhead. I don’t know too much details but compared to a normal child cursor size of 15K, 100-ish bytes doesn’t look much to me. And the profit would be huge.
To support similar counters on the system level it’s logical to add them as statistics to V$SYSSTAT. It’s easy and fits perfectly well to AWR reports. Let’s say, you see substantial hard parse (sharing criteria) elapsed time or “hard parse (bind mismatch) elapsed time” reported in AWR report. Then you just scroll down to statistics section of the report and see Top reasons for these issues.
What do you think?

EXPLAIN PLAN shared memory

Recently I did an investigation of an ORA-04031 which happens almost regularly on a 10.2.0.4 Oracle database server with 9G of memory allocated to shared pool and disabled Automatic Shared Memory Management (sga_target=0). The problem query is a report that is a very big SQL query – more than 200K of plain text. The exact error message is “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”. Here I’ll briefly describe what I did to identify root cause of the error and will talk about one interesting detail of the EXPLAIN PLAN as it was used to reproduce the issue.