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.