Tuning Pack 11g : Real-Time SQL Monitoring

“Real Time SQL Monitoring” is a 11g Tuning Pack new feature. You can easily access it when the Tuning Pack is set. If a query is a “long” query, if it uses more than 5 seconds of CPU of I/O Wait or if it’s a parallel query (from the documentation), the plan execution statistics are kept by the engine and you can follow the query execution in Real Time. Note that it differs from V$SESSION_LONGOPS which enables you to follow one step of a query or other operations. Anyway, It’s very impressive even if it doesn’t look to be fully functional (Is it me ?). It’s just a shame that I won’t be able to access it for all the databases although we’ve already asked for.
I don’t want to paraphrase the documentation but just build a test case to illustrate this feature. For more information about Real Time SQL Monitoring see :
- Oracle 11.1 – Performance Guide : Real Time SQL Monitoring
- Oracle 11.1 – PL/SQL Package and Type Reference DBMS_SQLTUNE
- Oracle 11.1 – Reference V$SQL_MONITOR et V$SQL_PLAN_MONITOR
As always I guess Enterprise Manager provides a fantastic GUI for this feature. This post is for you if you don’t always use it…
Step 1 : Configuration, sample Schema and SQL
Check that the tuning pack is set as below :
SQL> show parameter control_management_pack_access NAME VALUE ------------------------------ ----------------- control_management_pack_access DIAGNOSTIC+TUNING
Create a sample table :
create table gark(a number, b number); begin for i in 1..100000 loop insert into gark values (mod(i,19), mod(i,17)); end loop; commit; end; /
To show how to use “Real Time SQL Monitoring”, we need a query that last minutes. Thanks Oracle to provide HINTs to make plan sub-optimal. Here is the one, I’ll use to show :
select /*+ use_nl(a b) */ count(*) from gark a, gark b where a.a=b.b;
Before you run it, prepare the battle field to monitor the query. In a real case, you would like to catch the query from a kind of “Top SQL” or “Top Session”. Because we already know what to track, we will not the session sid and serial# before we start. To do that, lets use the SCOTT schema ; grant SELECT on V_$SESSION to SCOTT :
sqlplus / as sysdba grant select on sys.v_$session to scott;
Before we run the query, let’s just note the session sid and serial# :
select sid, serial#, audsid from v$session where audsid=sys_context('USERENV','SESSIONID'); SID SERIAL# AUDSID ---------- ---------- ---------- 130 99 200038 select /*+ use_nl(a b) */ count(*) from gark a, gark b where a.a=b.b;
Step 2 : Monitor the query execution
Once the query started and after a while, the time for the feature to trigger, V$SQL_MONITOR will help you to track the query execution. While V$SQL shows cumulative statistics, V$SQL_MONITOR differentiates all the executions and show one row per query if it’s tracked. Moreover, V$SQL_PLAN_MONITOR shows deeper details of the query execution : each step of the plan. Here is a script to that enable you to find the right query from the sid and serial# :
accept v_sid prompt "Enter Session ID : " 130 accept v_serial prompt "Enter Session Serial# : " 200038 col key format 999999999999 set colsep '|' select key, sql_id, sql_exec_id, to_char(max(sql_exec_start) ,'DD/MM/YYYY HH24:Mi:SS') sql_exec_start, sql_child_address child_address from v$sql_monitor where sid=&v_sid and session_serial#=&v_serial group by key, sql_id, sql_exec_id, sql_child_address order by sql_exec_start; KEY|SQL_ID |SQL_EXEC_ID|SQL_EXEC_START |CHILD_AD ------------|-------------|-----------|-------------------|-------- 304942678017|4z9f29d23f72c| 16777216|12/08/2007 03:58:21|305C173C 519691042820|4z9f29d23f72c| 16777217|12/08/2007 06:00:43|331B81F8 408021893126|4z9f29d23f72c| 16777218|12/08/2007 08:05:42|30615618
Once you’ve found the query you want to monitor (SQL_ID, SQL_EXEC_ID and SQL_EXEC_START are a unique key to define the query execution), you can follow the query progress in “Real Time” :
accept v_sql_id prompt "Enter the SQL_ID to look at : " 4z9f29d23f72c accept v_sql_exec_id prompt "Enter the associated SQL_EXEC_ID to look at : " 16777218 accept v_sql_exec_start prompt "Enter the Execution Start Date (DD/MM/YYYY HH24:MI:SS) : " 12/08/2007 08:05:42 col id format 999 col operation format a25 col object format a6 set colsep '|' set lines 100 select p.id, rpad(' ',p.depth*2, ' ')||p.operation operation, p.object_name object, p.cardinality card p.cost cost, substr(m.status,1,4) status, m.output_rows from v$sql_plan p, v$sql_plan_monitor m where p.sql_id=m.sql_id and p.child_address=m.sql_child_address and p.plan_hash_value=m.sql_plan_hash_value and p.id=m.plan_line_id and m.sql_id='&&v_sql_id' and m.sql_exec_id=&&v_sql_exec_id and m.sql_exec_start=to_date('&&v_sql_exec_start', 'DD/MM/YYYY HH24:MI:SS') order by p.id; I|OPERATION |OBJECT| CARD|C|STAT|OUTPUT_ROWS -|-------------------------|------|----------|-|----|----------- 0|SELECT STATEMENT | | |X|EXEC| 0 1| SORT | | 1| |EXEC| 0 2| NESTED LOOPS | | 543129616|X|EXEC| 19857217 3| TABLE ACCESS |GARK | 100000|X|EXEC| 3772 4| TABLE ACCESS |GARK | 5431|X|EXEC| 19857217
If you re-run the query you can see the OUTPUT_ROWS progress as the STATUS column go from “EXECUTING” to “DONE” and monitor the query as never before :
/ I|OPERATION |OBJECT| CARD|C|STAT|OUTPUT_ROWS -|-------------------------|------|----------|-|----|----------- 0|SELECT STATEMENT | | |X|EXEC| 0 1| SORT | | 1| |EXEC| 0 2| NESTED LOOPS | | 543129616|X|EXEC| 167404623 3| TABLE ACCESS |GARK | 100000|X|EXEC| 31806 4| TABLE ACCESS |GARK | 5431|X|EXEC| 167404623 / I|OPERATION |OBJECT| CARD|C|STAT|OUTPUT_ROWS -|-------------------------|------|----------|-|----|----------- 0|SELECT STATEMENT | | |X|DONE| 1 1| SORT | | 1| |DONE| 1 2| NESTED LOOPS | | 543129616|X|DONE| 526317649 3| TABLE ACCESS |GARK | 100000|X|DONE| 100000 4| TABLE ACCESS |GARK | 5431|X|DONE| 526317649
Step 3 : to continue (and what I didn’t manage to do !)
You can use the hint /*+ MONITOR */ to make the execution statistics available from V$SQL_MONITOR and V$SQL_PLAN_MONITOR. /*+ NO_MONITOR */, on the opposite prevents the informations to be stored.
I don’t know why, there is no data in the WORKAREA_MEM, WORKAREA_MAX_MEM, WORKAREA_TEMPSEG and WORKAREA_MAX_TEMPSEG columns of V$SQL_PLAN_MONITOR. It is probably somehow related to the fact DBMS_SQLTUNE.REPORT_SQL_MONITOR doesn’t show anything either. From the documentation, the steps below would have been even more impressive that my home made query above :
variable v_report CLOB; set long 10000000 set longchunksize 10000000 set linesize 200 BEGIN :v_report :=DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>'&&v_sql_id', sql_exec_id=>&&v_sql_exec_id, sql_exec_start=>to_date('&&v_sql_exec_start', 'DD/MM/YYYY HH24:Mi:SS'), report_level=>'ALL'); END; / print :v_report
If you make it run with this query or another, I would be very interested ? I expect it has more to do with the DBA (me) limitations rather that the new release…
Step 4 : Before you quit
drop table gark purge;