In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.
Continue reading →
The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 11.2.0.3 instance.
Continue reading →
February 21, 2012 – 12:55
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?
Timur Akhmadeev's blog 