Nice Additions For Troubleshooting

This is just a short note that Oracle has added several nice details to 11.2.0.1 and 11.2.0.2 respectively that can be helpful for troubleshooting.

ASH, PGA Memory And TEMP Consumption

Since 11.2.0.1 the V$ACTIVE_SESSION_HISTORY view (that requires Enterprise Edition plus Diagnostic License) contains the PGA_ALLOCATED and TEMP_SPACE_ALLOCATED columns.

In particular the latter closes an instrumentation gap that always bothered me in the past: So far it wasn't easy to answer the question which session used to allocate TEMP space in the past. Of course it is easy to answer while the TEMP allocation was still held by a session by looking at the corresponding V$ views like V$SORT_USAGE, but once the allocation was released answering questions like why was my TEMP space exhausted three hours ago was something that couldn't be told by looking at the information provided by Oracle.

So these two columns now should allow to easily analyze PGA memory and TEMP space issues that happened in the past.

Time Model And I/O Delta

Furthermore there are two new classes of columns: TM_DELTA_TIME / TM_DELTA_CPU_TIME / TM_DELTA_DB_TIME as well as the DELTA_TIME plus the remaining DELTA_*_IO_* columns. These allow to analyze which sessions contributed to what extend to the overall Time Model statistics DB_TIME as well as the IO related statistics. Again a nice addition that allows for more sophisticated analysis based solely on ASH data. In the past such analysis was only possible when persisting the session level statistics.

Reasons For Non-Shared Cursors

So far the V$SQL_SHARED_CURSOR view represented a user-friendly output of a bitmask (taken from X$KKSCS) telling why child cursors couldn't be shared, although unfortunately many of the "reasons" are not documented very well. With every release Oracle adds more columns to the view trying to keep up with the ever growing number of reasons why child cursors couldn't be shared.

With now more than 60 possible documented reasons determining why a child cursor wasn't shared becomes more and more cumbersome and can only be done reasonably by using some kind of script that turns this view into some more human-readable format, for example like Dion Cho did here or MOS note "High SQL Version Counts - Script to determine reason(s) ID 438755.1".

In 11.2.0.2 another new column has been introduced: REASON.

This column is in XML format and seems to be an attempt to allow for a more straightforward understanding why a cursor wasn't shared. I don't know if there are any Oracle tools that already process this column, but at present it looks like there is still room for improvement. First of all the XML generated isn't even valid XML, so creating a XMLType based on the contents regularly fails with parsing errors, mainly due to a missing root node of the document in my cases.

But also the generated content seems to be questionable, for example 11.2.0.2 added the new column USE_FEEDBACK_STATS indicating that a new child cursor was deliberately created due to the new Cardinality Feedback feature. Nevertheless the REASON column only shows a generic "OptimizerEnvironmentMismatch" reason. Although this isn't wrong it would helpful if the column at least showed the same level of information that could be told from looking at the individual columns.

Nevertheless the new column seems to be a move into the right direction. In particular it looks like that it not only states the different reasons but also further details for each reason that might allow getting a better understanding why cursors weren't shared. At present these details sometimes look quite cryptic, however time will tell if these will be more user-friendly or someone will come up with further explanations what these mean.

Certainly I've already seen cases where the REASON column explicitly stated optimizer related parameters that were different and hence allowed a straightforward understanding why cursors weren't shared rather than just indicating a Y in one of the remaining columns.