Realtime SQL Monitoring – Designed with Exadata in Mind

One of the best new features of 11g from a diagnostic standpoint is the Real Time SQL Monitoring capabilities. I did a post about it a couple of years ago here: Oracle 11g Real Time SQL Monitoring In that post I talked about a procedure (DBMS_SQLTUNE.REPORT_SQL_MONITOR) that provides a very nicely formatted explain plan type output which contains quite a bit of useful information. Well, it has recently come to my attention that the report contains a column that shows cell offloading. That’s pretty cool. Here’s a script to call that procedure, report_sql_monitor.sql, and an example (note the format is really wide so be sure and use the scroll bar at the bottom to see the columns on the right of the output):

SYS@SANDBOX1> @report_sql_monitor.sql
Enter value for sid: 
Enter value for sql_id: 2hzzka3071hkj
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select /*+ INDEX (SKEW2 SKEW2_COL1) */ avg(pk_col) from kso.skew2
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (3:2673)
 SQL ID              :  2hzzka3071hkj
 SQL Execution ID    :  16777216
 Execution Started   :  03/13/2011 17:02:35
 First Refresh Time  :  03/13/2011 17:02:35
 Last Refresh Time   :  03/13/2011 17:02:48
 Duration            :  13s
 Module/Action       :  sqlplus@Kerry-Osbornes-MacBook-Pro-4.local (TNS /-
 Service             :  SANDBOX
 Program             :  sqlplus@Kerry-Osbornes-MacBook-Pro-4.local (TNS
 Fetch Calls         :  1
 
Global Stats
============================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Fetch | Buffer | Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |
============================================================================================================================
|      96 |      92 |     2.33 |        0.00 |        0.80 |     0.00 |     0.47 |     1 |   653K | 6136 |   5GB |  71.83% |
============================================================================================================================
 
Parallel Execution Details (DOP=8 , Servers Allocated=8)
===================================================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  |  Cell   |        Wait Events        |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Offload |        (sample #)         |
===================================================================================================================================================================================
| PX Coordinator | QC    |         |    0.83 |    0.03 |     0.00 |        0.00 |        0.80 |     0.00 |          |      7 |    3 | 24576 |    NaN% |                           |
| p000           | Set 1 |       1 |      12 |      11 |     0.29 |             |             |          |     0.07 |  81601 |  770 | 637MB |  71.83% | cell smart table scan (1) |
| p001           | Set 1 |       2 |      12 |      11 |     0.31 |             |             |          |     0.06 |  81601 |  775 | 637MB |  71.83% |                           |
| p002           | Set 1 |       3 |      12 |      12 |     0.32 |             |        0.00 |          |     0.06 |  81601 |  764 | 637MB |  71.83% | cell smart table scan (1) |
| p003           | Set 1 |       4 |      12 |      11 |     0.25 |             |             |          |     0.04 |  81601 |  768 | 637MB |  71.83% |                           |
| p004           | Set 1 |       5 |      12 |      12 |     0.30 |             |             |          |     0.06 |  81601 |  757 | 637MB |  71.83% | cell smart table scan (1) |
| p005           | Set 1 |       6 |      12 |      11 |     0.26 |             |             |          |     0.05 |  81601 |  755 | 637MB |  71.83% |                           |
| p006           | Set 1 |       7 |      12 |      12 |     0.28 |             |             |          |     0.07 |  81601 |  769 | 637MB |  71.83% |                           |
| p007           | Set 1 |       8 |      12 |      12 |     0.33 |             |             |          |     0.06 |  81560 |  775 | 637MB |  71.83% |                           |
===================================================================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2117817910)
==============================================================================================================================================================================
| Id |            Operation             |   Name   |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   | Activity |      Activity Detail      |
|    |                                  |          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload |   (%)    |        (# samples)        |
==============================================================================================================================================================================
|  0 | SELECT STATEMENT                 |          |         |       |         1 |    +13 |     1 |        1 |      |       |         |          |                           |
|  1 |   SORT AGGREGATE                 |          |       1 |       |         1 |    +13 |     1 |        1 |      |       |         |          |                           |
|  2 |    PX COORDINATOR                |          |         |       |         1 |    +13 |     9 |        8 |      |       |         |          |                           |
|  3 |     PX SEND QC (RANDOM)          | :TQ10000 |       1 |       |         2 |    +12 |     8 |        8 |      |       |         |          |                           |
|  4 |      SORT AGGREGATE              |          |       1 |       |        13 |     +1 |     8 |        8 |      |       |         |    50.52 | Cpu (49)                  |
|  5 |       PX BLOCK ITERATOR          |          |    128M | 24700 |        13 |     +1 |     8 |     128M |      |       |         |    45.36 | Cpu (44)                  |
|  6 |        TABLE ACCESS STORAGE FULL | SKEW2    |    128M | 24700 |        13 |     +1 |   104 |     128M | 6133 |   5GB |  71.83% |     3.09 | cell smart table scan (3) |
==============================================================================================================================================================================

So as you can see, this is a parallel query. For parallel queries there is a separate section that shows what each slave process did. There are a couple of columns that are particularly interesting in this section. The Cell Offload column shows the percentage reduction in data transferred to the database tier that resulted from offloading. The Wait Events column shows what the slaves waited on. In the Plan Details section you can see that the plan included a full table scan and that it was offloaded. This section has columns that show the same basic information (although the column names are slightly different), but it’s organized by plan step. This is extremely useful on complex statements that have multiple steps which may be offloaded. This section also clearly shows which steps are taking the most time (Activity %).

Here’s a more complicated example. The statement in this example doesn’t have a lot of steps, but the output is very useful in understanding what is happening. Again, be sure to scroll over to the right to see the interesting bits.

SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: fm7y4nqu4tbv3
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
update kso.skew_hcc1 set col1 =col1*1
 
Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  SYS (451:1843)
 SQL ID              :  fm7y4nqu4tbv3
 SQL Execution ID    :  16777216
 Execution Started   :  04/10/2011 17:41:24
 First Refresh Time  :  04/10/2011 17:41:24
 Last Refresh Time   :  04/10/2011 18:07:16
 Duration            :  1552s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 
Global Stats
=========================================================================================================================================
| Elapsed | Queuing |   Cpu   |    IO    | Application | Concurrency | Cluster  | PL/SQL  |  Other   | Buffer | Read  | Read  |  Cell   |
| Time(s) | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Offload |
=========================================================================================================================================
|    1583 |    0.00 |    1442 |      132 |        0.00 |        0.03 |     2.35 |    0.00 |     7.01 |   231M | 63553 | 784MB | -29.87% |
=========================================================================================================================================
 
Parallel Execution Details (DOP=32 , Servers Allocated=32)
====================================================================================================================================================================================================================
|      Name      | Type  | Server# | Elapsed | Queuing |   Cpu   |    IO    | Application | Concurrency | Cluster  | PL/SQL  |  Other   | Buffer | Read  | Read  |  Cell   |              Wait Events              |
|                |       |         | Time(s) | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Offload |              (sample #)               |
====================================================================================================================================================================================================================
| PX Coordinator | QC    |         |    1551 |    0.00 |    1425 |      117 |        0.00 |        0.03 |     2.23 |    0.00 |     6.69 |   231M | 58878 | 460MB |    NaN% | gc current grant 2-way (1)            |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | log buffer space (5)                  |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | log file switch completion (3)        |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | enq: FB - contention (2)              |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | cell single block physical read (122) |
| p000           | Set 1 |       1 |    0.93 |         |    0.53 |     0.39 |             |             |     0.00 |         |          |   1682 |   120 |  10MB | -72.41% | cell smart table scan (2)             |
| p001           | Set 1 |       2 |    0.98 |         |    0.51 |     0.48 |             |             |          |         |          |   1389 |   107 |  10MB | -72.41% | cell single block physical read (1)   |
| p002           | Set 1 |       3 |    0.97 |         |    0.57 |     0.41 |             |             |     0.00 |         |          |   2954 |   181 |  10MB | -66.67% |                                       |
| p003           | Set 1 |       4 |    0.91 |         |    0.51 |     0.40 |             |             |     0.00 |         |          |   1412 |   119 |  10MB | -72.41% |                                       |
| p004           | Set 1 |       5 |    0.92 |         |    0.52 |     0.40 |             |             |     0.00 |         |          |   1415 |   121 |  10MB | -69.49% | cell single block physical read (1)   |
|                |       |         |         |         |         |          |             |             |          |         |          |        |       |       |         | cell smart table scan (1)             |
| p005           | Set 1 |       6 |    1.31 |         |    0.54 |     0.77 |             |             |     0.00 |         |          |   7985 |   389 |  12MB | -58.73% | cell single block physical read (2)   |
| p006           | Set 1 |       7 |    0.98 |         |    0.48 |     0.50 |             |             |     0.00 |         |          |   2653 |   123 |  10MB | -72.41% |                                       |
| p007           | Set 1 |       8 |    0.96 |         |    0.49 |     0.46 |             |             |     0.00 |         |          |   2926 |   175 |  10MB | -69.49% |                                       |
| p008           | Set 1 |       9 |    0.84 |         |    0.53 |     0.30 |             |             |     0.00 |         |          |   1380 |    98 |  10MB | -75.44% | cell smart table scan (1)             |
| p009           | Set 1 |      10 |    0.81 |         |    0.48 |     0.33 |             |             |     0.00 |         |          |   1389 |   108 |  10MB | -72.41% |                                       |
| p010           | Set 1 |      11 |    1.32 |         |    0.54 |     0.46 |             |             |     0.00 |         |     0.32 |   2283 |    90 |  10MB | -75.44% | cell smart table scan (1)             |
| p011           | Set 1 |      12 |    1.13 |         |    0.44 |     0.68 |             |             |     0.00 |         |          |   1382 |    92 |  10MB | -75.44% | cell smart table scan (1)             |
| p012           | Set 1 |      13 |    1.12 |         |    0.58 |     0.54 |             |             |     0.00 |         |          |   5818 |   238 |  11MB | -66.67% |                                       |
| p013           | Set 1 |      14 |    0.92 |         |    0.55 |     0.37 |             |             |     0.00 |         |          |   1408 |   114 |  10MB | -72.41% |                                       |
| p014           | Set 1 |      15 |    1.12 |         |    0.53 |     0.59 |             |             |     0.00 |         |          |   3604 |   217 |  11MB | -66.67% |                                       |
| p015           | Set 1 |      16 |    0.98 |         |    0.53 |     0.45 |             |             |     0.00 |         |          |   1973 |   110 |  10MB | -75.44% |                                       |
| p016           | Set 1 |      17 |    0.97 |         |    0.51 |     0.46 |             |             |     0.00 |         |          |   1407 |   106 |  10MB | -72.41% | cell single block physical read (2)   |
| p017           | Set 1 |      18 |    0.95 |         |    0.50 |     0.45 |             |             |     0.00 |         |          |   5017 |   210 |  11MB | -69.49% |                                       |
| p018           | Set 1 |      19 |    1.08 |         |    0.52 |     0.56 |             |        0.00 |     0.00 |         |          |   2305 |   156 |  10MB | -69.49% |                                       |
| p019           | Set 1 |      20 |    0.92 |         |    0.49 |     0.42 |             |             |          |         |          |   1366 |    87 |  10MB | -75.44% |                                       |
| p020           | Set 1 |      21 |    0.89 |         |    0.55 |     0.33 |             |             |     0.00 |         |          |   2804 |   107 |  10MB | -72.41% |                                       |
| p021           | Set 1 |      22 |    0.89 |         |    0.53 |     0.35 |             |             |     0.00 |         |          |   1392 |   110 |  10MB | -72.41% | cell smart table scan (1)             |
| p022           | Set 1 |      23 |    0.94 |         |    0.53 |     0.40 |             |             |     0.00 |         |          |   1384 |    97 |  10MB | -72.41% |                                       |
| p023           | Set 1 |      24 |    0.93 |         |    0.52 |     0.41 |             |             |     0.00 |         |          |   9753 |   177 |  10MB | -66.67% |                                       |
| p024           | Set 1 |      25 |    1.07 |         |    0.53 |     0.53 |             |             |     0.00 |         |          |   7194 |   275 |  11MB | -66.67% |                                       |
| p025           | Set 1 |      26 |    1.15 |         |    0.54 |     0.53 |             |             |     0.08 |         |          |   2394 |    96 |  10MB | -75.44% |                                       |
| p026           | Set 1 |      27 |    1.13 |         |    0.54 |     0.59 |             |             |     0.00 |         |          |   5178 |   224 |  11MB | -63.93% | cell smart table scan (1)             |
| p027           | Set 1 |      28 |    0.94 |         |    0.48 |     0.45 |             |             |     0.00 |         |          |   3969 |   151 |  10MB | -69.49% |                                       |
| p028           | Set 1 |      29 |    0.99 |         |    0.54 |     0.45 |             |             |     0.00 |         |          |   1387 |    97 |  10MB | -75.44% | cell smart table scan (1)             |
| p029           | Set 1 |      30 |    0.80 |         |    0.54 |     0.26 |             |             |     0.00 |         |          |   1375 |    90 |  10MB | -75.44% |                                       |
| p030           | Set 1 |      31 |    1.10 |         |    0.62 |     0.48 |             |             |     0.00 |         |          |   2958 |   184 |  10MB | -69.49% | cell smart table scan (1)             |
| p031           | Set 1 |      32 |    1.03 |         |    0.52 |     0.51 |             |             |     0.00 |         |          |   1389 |   106 |  10MB | -69.49% | cell smart table scan (1)             |
====================================================================================================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=3842559352)
==========================================================================================================================================================================================
| Id |            Operation            |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Cell   | Activity |            Activity Detail            |
|    |                                 |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Offload |   (%)    |              (# samples)              |
==========================================================================================================================================================================================
|  0 | UPDATE STATEMENT                |           |         |      |           |        |     1 |          |       |       |         |          |                                       |
|  1 |   UPDATE                        | SKEW_HCC1 |         |      |      1552 |     +1 |     1 |        0 | 58650 | 458MB |         |    94.61 | gc current grant 2-way (1)            |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | log buffer space (5)                  |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | log file switch completion (3)        |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | Cpu (1359)                            |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | enq: FB - contention (2)              |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell single block physical read (122) |
|  2 |    PX COORDINATOR               |           |         |      |      1551 |     +2 |    33 |      32M |       |       |         |     3.68 | Cpu (58)                              |
|  3 |     PX SEND QC (RANDOM)         | :TQ10000  |     32M |  375 |      1551 |     +2 |    32 |      32M |       |       |         |     0.38 | Cpu (6)                               |
|  4 |      PX BLOCK ITERATOR          |           |     32M |  375 |      1551 |     +2 |    32 |      32M |       |       |         |          |                                       |
|  5 |       TABLE ACCESS STORAGE FULL | SKEW_HCC1 |     32M |  375 |      1551 |     +2 |   417 |      32M |  4675 | 324MB | -69.49% |     1.33 | Cpu (4)                               |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell single block physical read (6)   |
|    |                                 |           |         |      |           |        |       |          |       |       |         |          | cell smart table scan (11)            |
==========================================================================================================================================================================================
 
 
1 row selected.

In this example, we have an update statement that took a while, even though it was using Smart Scans. So what gives? When we looked at the output from SQL Monitor it was clear that it wasn’t spending all it’s time looking for the records to update, but rather on the update itself. As you might have guessed from the name of the table, this statement was attempting to update every row in a table that had been compressed using HCC (not something you would normally want to do). This caused a lot of extra work, migrating the rows to OLTP compressed blocks. So that’s what’s going on under the covers. The point of the example is that the report quickly focuses our attention on the area that needs investigation.

By the way, SQL Monitoring is supposed to kick in automatically for long running statements (over 5 seconds by default) and for parallel statements. Occasionally though we would like to see the SQL Monitoring Report on a statement that is not being monitored for some reason. Fortunately, there is a hint (MONITOR) that can be used to force SQL Monitoring. In situations where we don’t have access to the code, like a packaged app for example, we can apply the hint via a SQL Profile. I created a little script called fm.sql to do just that. And no fm doesn’t stand a radio frequency range, and no it doesn’t stand for some kind of magic, and no it’s not the name of a Steely Dan song (well it is, but that’s not why I called it that). It stands for “force monitoring”. Here’s an example:

 
 
*******************
Force Monitoring via a SQL Profile
*******************
 
 
SYS@SANDBOX1> select count(*) from kso.little_skew a;
 
  COUNT(*)
----------
   1100098
 
1 row selected.
 
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.little_skew a
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8xrg1nunxn181      0 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
 
1 row selected.
 
SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: 8xrg1nunxn181
Enter value for sql_exec_id: 
 
REPORT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
1 row selected.
 
SYS@SANDBOX1> -- It's a short duration non-PX statement so it wasn't monitored
SYS@SANDBOX1> -- let's force monitoring via a profile (we'll create the profile with fm.sql)
SYS@SANDBOX1> 
SYS@SANDBOX1> @fm
Enter value for sql_id: 8xrg1nunxn181
 
Profile PROFILE_8xrg1nunxn181_MONIOTR created.
 
SYS@SANDBOX1> select count(*) from kso.little_skew a;
 
  COUNT(*)
----------
   1100098
 
1 row selected.
 
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.little_skew a
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8xrg1nunxn181      0 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
8xrg1nunxn181      1 3498336203      1        .02      0 No             .00 select count(*) from kso.little_skew a
 
2 rows selected.
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 8xrg1nunxn181
Enter value for child_no: 1
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8xrg1nunxn181, child number 1
-------------------------------------
select count(*) from kso.little_skew a
 
Plan hash value: 3498336203
 
----------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |       |  1546 (100)|          |
|   1 |  SORT AGGREGATE            |             |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  1546   (1)| 00:00:19 |
----------------------------------------------------------------------------------
 
Note
-----
   - SQL profile PROFILE_8xrg1nunxn181_MONITOR used for this statement
 
 
18 rows selected.
 
SYS@SANDBOX1> @report_sql_monitor
Enter value for sid: 
Enter value for sql_id: 8xrg1nunxn181
Enter value for sql_exec_id: 
 
REPORT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select count(*) from kso.little_skew a
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (451:1845)
 SQL ID              :  8xrg1nunxn181
 SQL Execution ID    :  16777217
 Execution Started   :  04/10/2011 19:08:17
 First Refresh Time  :  04/10/2011 19:08:17
 Last Refresh Time   :  04/10/2011 19:08:17
 Duration            :  .020997s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 Fetch Calls         :  1
 
Global Stats
======================================
| Elapsed |   Cpu   | Fetch | Buffer |
| Time(s) | Time(s) | Calls |  Gets  |
======================================
|    0.02 |    0.02 |     1 |   5612 |
======================================
 
SQL Plan Monitoring Details (Plan Hash Value=3498336203)
=========================================================================================================================================
| Id |          Operation           |    Name     |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                              |             | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=========================================================================================================================================
|  0 | SELECT STATEMENT             |             |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE             |             |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    TABLE ACCESS STORAGE FULL | LITTLE_SKEW |      1M | 1546 |         1 |     +0 |     1 |       1M |          |                 |
=========================================================================================================================================

So there you have it. A quick and dirty way to enable Real Time SQL Monitoring on a production statement that wasn’t being monitored by default.

I have really been using this capability a lot lately. Being able to see which steps in a complex plan are offloaded and what wait events are affecting which steps is very useful. The “Activity %” column which shows which steps the statement spent all it’s time on is also pretty helpful. It’s a tool that has moved very close to the top of my bag.

P.S. Greg Rahn has been a proponent of this report for some time and has been blogging about it for several years. Here’s one from Jan of 2008: Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR

Leave a Reply