A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.
As usual the latest version can be downloaded here.
This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 18.104.22.168).
As an extra however, this version now differentiates between general CPU usage and in-memory CPU usage (similar to 22.214.171.124 Real-Time SQL Monitoring). This is not done in all possible sections of the output yet, but the most important ones are already covered.
So if you already use the 126.96.36.199 in-memory option this might be helpful to understand how much of your CPU time is spent on in-memory operations vs. non in-memory. Depending on your query profile you might be surprised by the results.
Here are the notes from the change log:
- Forgot to address a minor issue where the SET_COUNT determined per DFO_TREE (either one or two slave sets) is incorrect in the special case of DFO trees having only S->P distributions (pre-12c style). Previous versions used a SET_COUNT of 2 in such a case which is incorrect, since there is only one slave set. 12c changes this behaviour with the new PX SELECTOR operator and requires again two sets.
- For RAC Cross Instance Parallel Execution specific output some formatting and readability was improved (more linebreaks etc.)
- Minor SQL issue fixed in "SQL statement execution ASH Summary" that prevented execution in 10.2 (ORA-32035)
- The NO_STATEMENT_QUEUING hint prevented the "OPTIMIZER_FEATURES_ENABLE" hint from being recognized, therefore some queries failed in 188.8.131.52 again with ORA-03113. Fixed
- "ON CPU" now distinguishes between "ON CPU INMEMORY" and "ON CPU" for in-memory scans
Slides for OOW16 Session Preventing Bad SQLs
2 days ago