- About Us
- Events and Webinars
- Contact Us
Contrary to V$SQL, V$SQL_MONITOR tracks performance statistics for each execution of a SQL statement, not a summary of accumulated statistcis.
You can use DBMS_SQLTUNE.REPORT_SQL_MONITOR, Grid Control, or EM Database Control to report on V$SQL_MONITOR and V$SQL_PLAN_MONITOR.
Using Enterprise Manager
Enterprise Manager Grid Control or Database Control has a SQL Monitoring link in the Performance tab that can be used to show SQL Monitoring details. See below:
When you click on it, it’ll show SQL Monitoring statistics for anything running over 5 seconds or running in parallel:
You can see the performance-related timings and details from the screen, and if you click on the SQL_ID link, it will show various details of the SQL statement’s performance profile.
As you can see, there is a tremendous amount of information contained in various tabs for each SQL_ID monitored. Also, note the graphical representation of time-per-step – this should provide the SQL optimizer a very good idea of what to look for when optimizing.
Let’s take a look at a slightly more complicated query that does a handful of joins:
From the above and below, we can clearly see the step that’s taking the most time.
You can also click on “View Report” to view the SQL Monitoring report. The output looks like the below (I’ve got several screen shots, but this all shows up on one page in EM):
The screen shot below shows the SQL Monitoring display after several long-running jobs have run:
What if you don’t have EM Grid Control or DB Control? Oracle provides a means to report on SQL Monitoring details using DBMS_SQLTUNE.REPORT_SQL_MONITOR. Below, I’ll show this after an execution of a long-running piece of SQL:
<< output deleted >>
As you can see from the above, DBMS_SQLTUNE.REPORT_SQL_MONITOR picked up the last SQL statement that ran and qualified for SQL monitoring, since we didn’t provide a SQL_ID.
Run SQL Monitor Report for “Last SQL”
Let’s do a shorter-running SQL statement so other SQL statements don’t enter the queue first:
As you can see, there is a great deal of information in the report, but it’s missing a nice graphical time representation of how long each step took.
You’ll also likely think to yourself – how can I get a SQL report for a given SQL ID? The next section goes into this.
Run SQL Monitor Report for a given SQL_ID
If you know the SQL_ID ahead of time, you can enter it as a parameter to the call to DBMS_SQLTUNE.REPORT_SQL_MONITOR:
Let’s run a SQL statement, get the SQL_ID in question (which we’ll get from V$SESSION.PREV_SQL_ID)
Run SQL Monitor Report and generate HTML Report
You can also generate an HTML file output. This is helpful because it shows a graphical representation of how much time each step in the execution plan spent.
Some handy SQL Monitor scripts
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.