Real Time SQL Monitoring, A Goodie Of 11g….

Oracle’s newest database release 11g is having lots of goodies stored in it. Since now even the release 2 of it, (11.2.0.1) is also out, the number of goodies has just gone more higher! There are some things which are really very nice and are worth checking at least for once. This post is just about one of those goodies. The idea of this post is not about a new invention but about the introduction a worth-to-know kind of feature introduced with the Oracle 11g. Interested, okay keep on reading then 🙂 .

Its always asked by the people who are involved in the query tuning that there should be something which should tell them the current statistics of the query, right at that time when the query is actually running! This kind of monitoring, if available, can help a lot in finding out that out of all the access paths, which one is taking more time  in addition to the time spent in the execution of various steps of the query. Also, there is one question which I am asked all the time that from where the query execution starts in the execution plan? The answer of that can be given looking at the execution plan though but if there can be something which can show us the same when the query is still “alive”, I guess that information can help in both understanding the execution pathway of the query and also can come handy in the troubleshooting part. I am not sure that is there is some tool which can do this kind of task ( if you know, please do post the name of it in the comments) but I believe, there is nothing which I should be worried about now since from 11g release 1, Oracle has given a solution for this with the Real Time SQL Monitoring!

Real-time sql monitoring does exactly what it says, it monitors a query when its actually running. The monitoring information gives a wide information about the query state that whether its over and some more general information about the query. It also brings out the information of the execution plan of the query and the statistics like that how much time is spent by each access path in the plan, also including the cost incurred at that step. This also shows with a nice arrow (->) marker that which step is executing at the moment when the query is still actually running!

Real time monitoring is enabled through the parameter CONTROL_MANAGEMENT_PACK_ACCESS which should be set to the value DIAGNOSTIC+TUNING( default) . In addition to it, the parameter STATISTICS_LEVEL is set to either ALL or TYPICAL(default) . If oracle finds out that the there is delay of more than 5 seconds happening for the query, it can monitor that query. The monitoring information is stored in two new views V$SQL_MONITOR and V$SQL_PLAN_MONITOR. V$SQL_MONITOR view would get populated when the monitoring of the query would start.

The monitoring is recorded through the package DBMS_SQLTUNE’s function, REPORT_SQL_MONITOR which does offer couple of switches that can help in getting the desired output needed to troubleshoot the query’s performance issues. The description of the entire number of options offered by the function is in the link given already. Let’s try to use this function to monitor a simple query. For this purpose, we would be using two sessions where one would be running the query which we want to monitor and the other would be executing the function which would capture the query’s information. First, we prepare the session that is supposed to monitor the running query,

Session-1>>

SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;

Now, we shall write a very simple query in the session 2 of ours which we would execute and than monitor it from the first session.

Session-2>>

SQL@AMAN>select count(*) from test;

  COUNT(*)
———-
   1101904

Since the query result set is not that much large, the query gets complete in a very less time but still it did take long enough so that Oracle could monitor it. After the query’s execution got over, this is what was reported for it through the monitoring.

Session-1>>

SQL@SYS>select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
———————————————————————————————————————————-
———————————————————————-
SQL Monitoring Report

SQL Text
———————————————————————————————————————————-
————————————————-
select count(*) from test
———————————————————————————————————————————-
————————————————-

Global Information
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session ID          :  131
SQL ID              :  7b2twsn8vgfsc
SQL Execution ID    :  16777216
Plan Hash Value     :  1950795681
Execution Started   :  03/04/2010 23:03:28
First Refresh Time  :  03/04/2010 23:03:31
Last Refresh Time   :  03/04/2010 23:03:35

——————————————————————–
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Reads |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  |       |
——————————————————————–
|    6.56 |    0.13 |     6.38 |     0.06 |     1 |  23976 | 16112 |
——————————————————————–

SQL Plan Monitoring Details

| Id |      Operation       | Name |  Rows   | Cost |   Time    | Start  | Starts |   Rows   | Activity  |   Activity Detail    |
|    |                      |      | (Estim) |      | Active(s) | Active |        | (Actual) | (percent) |      (sample #)      |
================================================================|  0 | SELECT STATEMENT     |      |         | 4655 |         1 |     +7 |      1 |        1 |        |                 |
|  1 |   SORT AGGREGATE     |      |       1 |      |         1 |     +7 |      1 |        1 |        |                 |
|  2 |    TABLE ACCESS FULL | TEST |   1097K | 4655 |         6 |     +2 |      1 |    1102K |    100.00 | direct path read (6) |
==================================================================

We can see that a lot of information is captured through the monitoring for us! The sections which presents the interesting outputs are Activity(percent) and Activity Detail(sample#) . The Acitivity percent shows that in terms of DB time, how much time was spent by each access path of the query and the Activity Details shows the detailed information about that activity. For our case, the biggest (and the only active) activity was table access full and this did happen through the Direct Path Read! Furthermore, you can even control the monitoring through two hints as well, MONITOR and NO_MONITOR! And last but certainly not the least, the real time monitoring is also a part of EM which means all of this can be done through nice and shiny colorful EM screens as well. Now that’s cool!

I believe that its a really useful feature! Once again, the idea of this post was not post something “internal” but just to bring this feature in front of you. If you already knew about it, cool, feel free to add more information about this feature over here via the comments. If you didn’t know about it before, well, now you know so enjoy it to the fullest 😀 . Oh and BTW, you can read about this feature in detail from here.