Today here’s a shorter post about my experiments with Oracle SQL Developer’s user-defined reports: A report on all long running operations (“LongOps”) with details on session wait events, explain plans and live SQL monitoring.
“Wait a minute”, you might say, “there’s already the session report in SQL Developer’s standard reports that shows Session_LongOps”! – and you’re right, it’s been integrated in SQLDev for quite some time.
BUT: I wanted to view the LongOps from a different perspective: To find out what’s taking so long in the database generally, it would be better in my humble opinion to start with ALL LongOps and drill down from there.
Here’s how it looks on a live system:
The main SQL of this report basically reads from v$session_longops which contains information about the session id, the sql_id associated to this LongOp, the work done so far, the total work expected as well as elapsed and estimated remaining time. The unit of “Work” is defined separately and shown in the message – it can be amongst others “tables”, “indexes”, “blocks” and so on.
SQLDev’s gauge feature comes in quite handy as a progress bar for this report. “Mr. SQLDev” Jeff Smith described its usage in his article “SQL Developer Query & Grid Tricks“, so I don’t need to dive deeper here.
The detail tabs explained:
- Session Detail: Everything v$session can show about the session running the selected LongOp.
- Wait Summary: The waits for this specific session, longest wait times on top. Idle waits are pushed down to the end of the list.
- Explain Plan: DBMS_XPLAN output for the sql_id associated with the selected LongOp. It shows all child cursors and cumulative statistics for this sql_id.
- SQL Monitor: Live SQL Monitoring report, shown either in plain text, html or html with embedded Flash animation. The Flash report is equivalent to the monitoring feature in Oracle Enterprise Manager and has many nitty-gritty extras, but it needs an internet connection to download the embedded renderer from Oracle.
Beware, though, that SQL Monitor is part of the Diagnostics Pack and needs to be properly licensed!
I’ve commented out some filters or additional columns in this report, mainly for compatibility with older versions of Oracle. So check out the SQL behind the report(s) and fit them to your purpose.
Here’s how it looks when only active LongOps are shown and the SQL Monitor (html) child tab is selected:
(It seems that you cannot change the super small font size in the html window. Go vote for my enhancement request at SQL Developer Exchange)
This report is available for download in my SQL Developer Repository on GitHub. Feel free to download this report from there (right-click and “save as”; then, in SQLDev’s reports tab, right-click on the “User Defined Reports” folder and select “Open Report”).
And if you’d like to see more features in this report, feel free to have your say in the comments below.
Oraculix (en) 
When I download the XML file and run the report, I do not get the Details section, only the main section.
Also, I modified the SQL statement to filter on TIME_REMAINING > 0. I rarely, if ever, care about long operations that have completed.
LikeLike
Brian,
which version of SQL Dev are you on? And which platform?
It’s strange that just the detail tabs don’t work for you. I’ve started with this report in SQLDev v3, maybe even earlier and had to re-import it on different systems and different versions ever since with no issues so far. The latest version on GitHub was saved from SQLDev 4.1.1, now I wonder if there might be a compatibility issue.
For my part, I actually do care about completed LongOps, that’s why I chose not to filter them.
Cheers,
Uwe
LikeLike
I’m running SQL Dev 4.1.2 on Windows 7.
LikeLike
Hi Brian,
you have mail. ;-)
Interestingly, SQLDev 4.1.2 added some more XML tags to the report definition when I saved it again. As far as I remember my last change, save and commit was from v 4.1.1.
I pushed that to my repository, hoping it will be backwards compatible to earlier releases of SQLDev.
Please let me know whether it works for you now.
LikeLike
This latest version worked very well for me.
A few days prior to your blog post, I had done something similar on my blog: http://www.peasland.net/2015/12/11/longopswatcher-in-sql-dev/
But my work isn’t nearly as good as what you have here. I like the detail sections for drilling into the activity even more.
LikeLiked by 1 person
Thanks for the thumbs up, Brian! I saw your post today – it’s probably LongOps season of the year :-)
In other news, I should be worried about report compatibility. I’ll hand that over to the SQLDev community.
Cheers, Uwe
LikeLike
Pingback: LongOpsWatcher in SQL Dev » Peasland Database Blog
Ha! I just found this cool code snippet on Carlos Sierra’s blog that shows SQL plans from every node in a RAC: https://carlos-sierra.net/2013/06/17/using-dbms_xplan-to-display-cursor-plans-for-a-sql-in-all-rac-nodes/
It’s integrated in the child reports now, along with other RAC-specific enhancements.
LikeLike
Is the Report Diagnostic-Pack safe if you delete the SQL-Monitor Parts?
LikeLike
Hi Arne,
yes it is. It doesn’t make use of any Diagnostics Pack feature in the other parts. You could remove the child reports, comment out the SQL or simply not click on the tab.
And to be extra safe: set “CONTROL_MANAGEMENT_PACK_ACCESS=NONE” in your DB from 11g onwards (I guess you are aware of that parameter but other readers might not).
Cheers, Uwe
LikeLike
This report is too cool to have 🙂 Thanks a ton!!
LikeLiked by 1 person