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?

Oracle database features wish list – 1

Sometimes it just happens when you want some functionality built into Oracle database. I think I’ll put on the blog my “wish list” items to not forget about them. Today’s one: I want to have an option to reduce bind variables dumping in the SQL trace based on SQL depth level. For instance, to dump only those variables which were used in top level calls only. It could be useful in case of queries with PL/SQL function calls with SQL in it, producing enormous amount of info in the trace file which you probably don’t want to see because you are interested in the “driving” bind variables only. Or in case of globally enabled tracing – again it’s possible that you probably don’t need bind variables from all executed statements and/or want to reduce generated trace files total size.