New version 4.1

4.1:  June 2014

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in case you want to use your own custom
monitoring repository that copies data from GV$SQL_MONITOR and
GV$SQL_PLAN_MONITOR in order to keep/persist monitoring data. The tables
need to have at least those columns that are used by XPLAN_ASH from the
original views

- The "Activity Timeline based on ASH" for RAC Cross Instance Parallel
Execution shows an additional line break for the GLOBAL summary

- Fixed various bugs related to some figures when analyzing Cross
Instance RAC Parallel Execution

- The new "GLOBAL" aggregation level for Cross Instance RAC Parallel
Execution (see version 4.0 change log below) is now also shown in the
"Information on Parallel Degree based on ASH" section

- The "Parallel Distribution ASH" column on execution plan line level
now can show process information taken from Real-Time SQL Monitoring for
those processes that are not found in ASH samples.
This effectively means that with monitoring information available for
every plan line every involved process will now be shown along with its
ASH sample count and rows produced
So some processes will show up now with a sample count of 0.
The idea behind this is to provide more information about row
distribution even for those lines/processes that are not covered by the
ASH samples.
Previously the rowcount produced was only shown for those processes
covered in ASH samples

The new behaviour is default - if you find the output messy you can
return to previous behaviour (show only rowcounts for processes found in
ASH samples) by setting the new configuration switch
"show_monitor_rowcount" to any other value than the default of "YES"

- The "Real-Time SQL Monitoring" information on execution plan line
level now includes the read and write request information ("ReadReq",
"WriteReq")

- The I/O figures based on ASH now include the new
"DELTA_READ_MEM_BYTES" information that was added in 12c. This applies
to the following sections:
- SQL Statement I/O Summary based on ASH
- Parallel Worker activity overview based on ASH
- Activity Timeline based on ASH

The "Read Mem Bytes" seems to correspond to the "logical read bytes from
cache" statistics, so any direct path reads are not covered by this
value

- Added some more verbose description in the "Note" sections how to
handle long lines. XPLAN_ASH now does a SET TRIMSPOOL ON if you want to
spool the output to a file

- Whenever the output referred to DFOs this was changed to "DFO TREE",
which is the correct term

- The "Parallel Worker activity overview based on ASH" section now shows
a blank line between the sections which should make this section more
readable

- Adaptive plans are now supported by XPLAN_ASH

Note they don't work well with previous versions, the formatting of the
inactive lines breaks and the overall information can be misleading if
you don't add manually the "ADAPTIVE" formatting option

If XPLAN_ASH detects an adaptive plan, it will always force the ADAPTIVE
formatting option
This also means that Adaptive plans for the time being won't work with
SASH as SASH doesn't collect the OTHER_XML column from GV$SQL_PLAN
You could manually add that column to SASH_SQLPLANS and add the column
to the "sash_pkg.get_sqlplans" procedure - this is a CLOB column, but
INSERT / SELECT should work I think
The view SASH_PLAN_TABLE needs also to be modified to select the
OTHER_XML column instead of a dummy NULL

Although this output is less readable than the "faked" output that shows
only the plan operations that are actually in use,
it is the only simple way how ASH/MONITOR data can be related to
execution plan lines, as these hold the information with the actual plan
line, not the one that is made up by DBMS_XPLAN.DISPLAY* based on the
DISPLAY_MAP information in the OTHER_XML column

Hence I decided for the time being to use the same approach as 12c
Real-Time SQL Monitoring and always show the full/adaptive shape of the
plan

Another challenge for XPLAN_ASH with adaptive plans is the possibly
changing PLAN_HASH_VALUE during execution

XPLAN_ASH extracts the PLAN_HASH_VALUE from ASH/MONITOR when trying to
get the plan from DBA_HIST_SQL_PLAN

Hence XPLAN_ASH now needs to take care to extract the most recent
PLAN_HASH_VALUE, previously it didn't matter as it wasn't supposed to
change during execution
This seems to work based on my tests, but it's something to keep in mind

- The new "gather stats on load" 12c feature implies for INSERT...SELECT
statements that the cursor will immediately be invalidated/removed from
the Library Cache after (successful) execution. So now such
INSERT...SELECT behave like CTAS which also gets removed immediately.
This is a pity as you won't be able to pick up the plan from the Library
Cache after the execution completes using XPLAN_ASH (or any other tool
using DBMS_XPLAN.DISPLAY*).

Although V$SQL_PLAN_MONITOR might keep plan for some time after the
execution, it can't be used as input to DBMS_XPLAN.DISPLAY*, hence this
isn't a viable workaround
In principle however this isn't a good thing as the SQL and plan
information might be missing from AWR / STATSPACK reports due to the
immediate invalidation/removal

At the time being the only viable workaround known to me for this is to
prevent the "gather stats on load" feature either via parameter
"_optimizer_gather_stats_on_load" or hint
"no_gather_optimizer_statistics", or via using pre-12c optimizer feature
settings which implicitly disables the feature which is of course not
really a good workaround as the feature itself might be rather desirable