I have a directory where I try to keep all the queries I write for diagnosing performance issues, these have evolved over time to help identify different problems. These scripts typically use v$active_session_history in some way. I wanted to share some of them with my colleagues, but found they aren’t so confident with AWR and find it difficult to reach conclusions with so much information.
I took a step back and tried to make some accessible reports in SQL Developer that hopefully anyone can run (they do need to have the diagnostics pack licence though). I’m offering these reports to the community, hopefully you can use them in your own work, possibly make your own enhancements to them (feel free to share even further). Yes, there are already built-in ASH reports in SQL Developer, but I don’t feel they’re done at the same level I would want them. Plus, DIY is always more rewarding.
Currently there are two main reports, one that shows you what’s been sampled in ASH in the last 10 minutes. It allows you to drill into a module, action, event combination to see what SQL it was executing and for how long. Just click on the bar you find interesting. I expect you might want to modify how it groups (perhaps schema, client_identifier or program etc is more appropriate for your use)
The second report is an adaption of my sql_ash.sql script that I shared in Not all indexes are created equally.
In order to run the report as-is you will need to be on at least 220.127.116.11, you can modify the SQL to remove the concept of full_plan_hash_value if you are on a version below this If you aren’t on 12c yet then run the pre 12c report, no features are missing, the 12c version needed modifying due to adaptive plans. The gauge will show you which part of the plan is getting most time allocated to it. Unfortunately, this screenshot is from my sandbox system so it was not a very interesting piece of SQL I’m looking at here.
–I couldn’t figure out how to call a child report from a child report. If you do know, then please let me know 🙂 For now, you would just copy and paste the sql_id value.
In order to set the reports set up, you’ll need to copy the below xml into an xml file and import it to your SQL Developer – Right-click on User Defined Reports and choose the Open Report… option.
Get the XML report here quick_ash_queries_report_export.xml
GIF of the whole process:
Hope you find this useful.
I’ve made some adjustments and also included a pre 12c version of the SQL ASH report on the request of a comment. There’s also a gif to explain the install and running of the reports.