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.
cool, I can’t recall seeing that previously
This is really cool but the documentation appears to suggest that DBMS_PERF needs DBA role which would mean it can’t be used by developers/application DBAs in most workplaces due to security guidelines in place. https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_PERF.html#GUID-8B9E94EC-DB0A-4232-9989-BFD6898483D4
Is the correct or just a documentation bug?
Unfortunately no bug. You need DBA privs to run the script, or even to run DBMS_PERF directly.