Just a quick post today to inspire you to look further than the standard options for performance monitoring. Quiz most DBAs on which performance reports they are using and they will quickly rattle off two:
- awrrpt.sql – for getting a performance report out of the Automatic Workload Repository
- ashrpt.sql – for getting a similar report directly from Active Session History
But there’s another report which when I mention it, just gets blank stares in return. And that is the performance hub, and its report perfhubrpt.sql. It gives you an interactive report that allows you to see current activity as well as being able to drill down into individual SQL’s. I like to think of this report as sitting “between” and ASH/AWR report and a SQL monitoring report, ie, instance wide activity with a focus on SQL statements generating that activity.
Ultimately, the report is just a call to the DBMS_PERF package, so you can simply write your own scripts to run the package in whichever way suits your instance. Check out the docs for all of the capabilities.