Real-Time SQL Monitoring with Visual Studio Code
Use Oracle Developer Tools for VS Code to identify performance bottlenecks
Oracle Developer Tools for VS Code is Oracle’s free extension for Visual Studio Code that enables editing and execution of SQL and PL/SQL (and much more) with Oracle Database and Oracle Autonomous Database. You can run Visual Studio Code on Linux, macOS and Windows.
Oracle developers use this extension to edit and debug SQL and PL/SQL alongside the code they’ve written in Python, Java, .NET, and just about any other language. Sometimes there may be performance issues with the SQL or PL/SQL being developed and deployed. What then?
That’s where Real-Time SQL Monitoring comes in. It’s a feature of Oracle Database that provides an easy way to automatically identify run-time performance problems with resource intensive long-running and parallel SQL statements. Real-Time SQL Monitoring provides several advantages over traditional “explain plan” techniques. The biggest one is the ability to visualize where time was spent during the execution of the SQL statement.
With Oracle Developer Tools for VS Code 21.7.1 or later, you can use Visual Studio Code to view monitored SQL, view and save SQL Monitor Active Reports, and generate an Active Report for ad hoc SQL.
Prerequisites
You’ll need to install the Oracle Developer Tools for VS Code extension (version 21.7.1 or later). It’s easy to get: just click on the Extensions icon in the Activity Bar on the left side of VS Code to open the Extensions Marketplace pane. In the search box, type “Oracle Developer Tools” to find Oracle Developer Tools for VS Code (SQL and PLSQL). There’s other similarly named extensions so compare the extension name carefully to make sure you get the right one!
Once installed you will see a large database icon in the Activity Bar on the left side of Visual Studio Code. When you click it, it will show Oracle Explorer, where you can open the database connection dialog by hovering and then clicking the “+” (plus) icon. (To learn more about how to get started using this extension check out the Getting Started guide.)
To use Real-Time SQL Monitoring you’ll need a Tuning Pack license (unless you are using Oracle Database Free, which includes it) and the Tuning Pack will need to be enabled via the initialization parameter CONTROL_MANAGEMENT_PACK_ACCESS) if it isn’t already. I should also note that Real-Time SQL Monitoring is unavailable with Oracle Database Express Edition, Standard Edition and Standard Edition 2.
You don’t need any special database privileges to use this feature if you are only interested in monitoring SQL for schema objects that your user owns. If you want to view all SQL being monitored in the database you will need to ask your DBA to grant you the SELECT_CATALOG_ROLE role. (You’ll also need this role to use Real-Time SQL Monitoring on your own SQL if you are using Oracle Database 18c or earlier).
Viewing a list of Monitored SQL
Once you have a connection to a database in Oracle Explorer, you can right click on it and select Real-Time SQL Monitoring. It will open a list of monitored SQL.
As you can see in figure above, at the top of the list are several dropdown boxes. The Activity Last dropdown lets you choose the time range for which you would like to see the monitored SQL. It offers various choices from the past 24 hours up to the previous 1 hour.
Another key drop down is Top 100. This lets you choose the metric you wish to use when viewing the top 100 monitored SQLs. For example, you could view the top 100 SQLs by Duration. Some of the other choices include Database Time and Last Active Time.
Let’s take a look at the data in the list of monitored SQL. As you can see from the earlier screenshot you can view the SQL Status to know if the SQL is still running. To the right of the status is the Duration column. I should point out that you can hover your mouse over some of these metrics can to get more info. If you hover over Duration, it will show you the Start Time and End Time. Similarly, if you hover over the SQL ID or SQL Text field it will show you the entire SQL.
Hovering over the Database Time column will give you the break down of where the time was spent: CPU Time, User IO Wait Time, PL/SQL Execution Time and more….
You can sort the list by any of the metrics by either using the arrows in the column headers, or by right clicking on a cell and using one of the Sort menu items.
Viewing an Active Report
An Active Report is an offline interactive SQL monitoring report with interactive UI capabilities. You can view it in Visual Studio Code or save it as a standalone HTML file which can then be used for offline viewing or e-mailing to your coworkers, DBA, or Oracle Support.
From the list of monitored SQL we are viewing in Visual Studio Code, all you need to do is click on the SQL ID for the SQL that you are interested in and it will open the Active Report. Alternately, you can select the row that contains that SQL, right click and choose Open Detailed Report from the menu.
An example Active Report is shown in figure above. As you can see, you can hover over the bar graphs as well as other parts of the report, which provide more detailed breakdown of the performance metrics.
The beauty of a Graphical Execution Plan
In the figure above, a tabular execution plan is shown under the Plan Statistics tab. You can also use the dropdown control on that tab to select Graphical Execution Plan which will show you a visual representation of the plan that you can zoom in and out and pan around to really get an idea of how the plan is constructed!
As you can see in the large plan above, there’s a mini window in the lower right-hand corner of the graphical plan that lets you know where you are in the context of the entire plan as you move around. Very cool! There’s also a Rotate button that lets you turn the whole thing on its side. You can click on individual operations in the diagram to get details about the cost and much more.
Monitoring Ad-Hoc SQL
From Oracle Explorer, right click on a connection node and select Open New SQL File to start writing some SQL and PL/SQL. You can right click on the code you’ve written and choose Execute to execute an individual SQL statement or Execute All to execute the entire script.
Now, if you’re concerned about the performance of the SQL, you can right click and choose Execute and Monitor SQL to view the Active Report for the SQL. As shown in the figure above, you can also click the magnifying glass in the toolbar to do the same thing. Behind the scenes, the SQL is executed with a /*+Monitor*/ hint so that the Active Report can be generated. However, please note that the results of the query are not shown.
After you have closed the Active Report, if you decide you want to view it again, you can find it in the list of monitored SQL and then click on the SQL ID.
Saving and Sharing Active Reports
Active Reports can be saved and shared with co-workers or your DBA who can view them in any web browser. Whoever you share the interactive report with will see exactly what you see in Visual Studio Code — it’s great for troubleshooting!
To save an Active Report, view the list of monitored SQL and select the row in the list corresponding to the SQL you are interested in. Then, at the top of the screen, click the Save icon (which looks like a floppy disk). In the Save dropdown select Save as Active Report.
Wrapping up
I invite you to install Oracle Developer Tools for VS Code and try this for yourself. To learn more about Real-Time SQL Monitoring please check out the documentation and its product page.
