One of the most interesting section of a SQL Monitor report is IMHO the Metrics page where we can see on a timeline the impact / requirement on an execution in terms of CPU, IOPS, MBPS, PGA and TEMP.
Starting 11gR2, we can get something similar (not exactly the same) from ASH, this is from DBA_HIST_ACTIVE_SESS_HISTORY
For each TM_DELTA_TIME we know how much CPU and DB Time has been spent, as well as how many RW IOPS and RW and Interconnect (M)BPS per DELTA_TIME. The metrics are accumulated and reported at the time of the ASH sample “over the Delta Time” that roughly matches with the ASH sampling one, so ie. from V$ACTIVE_SESSION_HISTORY we are able to see how many RW IOPS per second (since the DELTA_TIME is roughly going to be the second) are accounted by the session we focus on. Also each time the ASH sample is taken the PGA and TEMP consumed by the session at that time is tracked.
Starting SQLd360 v1511 those columns are now collected and aggregated/charted over time so we can look at the single consumption via SQL Monitor (or from raw ASH data) and at the same time evaluate the historical trend from SQLd360. The PlansAnalysis page in SQLd360 now looks something like this
Other new additions since the last post (v1507) are:
- SQLd360 now uses a configuration file to determine how many days to collect info for and which “major” external APIs to collect / ignore
- Several reports have been rewritten to better represent info from Parallel Executions
- New sections provide info on
- Fix Controls (default and non-default) settings
- NLS settings
- Adaptive Cursor Sharing info
- SQL Monitoring info
- Partition Statistics history info
- SQLs with the same Force Matching Signature
plus as usual bug fixes here and there.
Thanks to everybody who reported issues and provided feedback for new features! 🙂