Real-Time SQL Monitoring est une des fonctionnalités les plus Rock’N Roll de Oracle 11g. En 11.2, la fonction dbms_sqltune.report_sql_monitor permet très simplement d’utiliser l’outil sans avoir à écrire de requête sur v$SQL_PLAN_MONITOR; mais savez-vous qu’il est possible de créer un rapport qui inclut la plug-in Flash depuis cette même fonction ? Et qu’il n’est pas nécessaire d’installer Enterprise Manager, ni Grid Control, ni Database Console ?
Dans cet article, vous trouverez un exemple simple qui illustre l’utilisation de Real-Time SQL Monitoring d’abord en mode « Text » puis en mode « Flash ». Quel Intérêt ? Disons que, si vous travaillez avec des développeurs, des développeuses ou des fonctionnels Business Intelligence, vous pourrez leur en mettre plein les yeux. Et à part ça ? Rien de plus que vous ne pourrez pas faire avec le mode « Texte », certes…
Une requête qui dure…
Pour les besoins de cet exemple, nous avons besoin d’une requête consommatrice… Si vous lisez mon blog, c’est que vous savez « tuner une requête » ou au moins que vous savez écrire les pires requêtes ; Personnellement, j’aime assez la solution ci-dessous qui peut facilement épuiser les plus grands serveurs avec quelques centaines de milliers de lignes et pour un peu que vous en lanciez plusieurs en parallèle. Comme quoi un « hint » et votre vie devient un cauchemar :
create table X
(col1 number,
col2 number);
insert into X
(select level, level+1 from dual connect by level <=100000);
commit;
set timing on
select /*+ use_nl(x y) */ count(y.col2)
from x, x y
where x.col2 = y.col1;
Et vous voilà parti pour 8 minutes sur mon laptop… Mettez 1 millions de lignes pour plus d’1H30 de traitements ; Qui a dit que les processeurs actuels étaient puissants ?
Monitorer le SQL en mode texte
Pendant que votre requête fonctionne, vous allez la superviser à partir du même utilisateur. Pour cela, récupérez le SID et SERIAL# de la session qui tourne en parallèle :
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
col sid new_value sid format 999999999
col serial# new_value serial format 999999999
select sid, serial#, logon_time
from v$session
where status='ACTIVE'
and username=user
order by logon_time desc;
Puis toujours dans la même session, lancez le rapport Real-Time SQL Monitor en mode texte comme ci-dessous :
set head off
set long 50000
set longchunksize 50000
set lines 20000
col report format a20000
set pages 1000
set feedback off
set verify off
set trims on
select dbms_sqltune.report_sql_monitor( -
session_id=>&&sid,
session_serial=>&&serial,
report_level=>'all',
type=>'text') report
from dual;
set head on
set lines 140
col report format a120
set verify on
set feedback on
set echo off
set pages 1000
SQL Monitoring Report
SQL Text
------------------------------
select /*+ use_nl(x y) */ count(y.col2) from x, x y where x.col2 = y.col1
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SYS (125:5)
SQL ID : fmuavhzvd3nsa
SQL Execution ID : 16777218
Execution Started : 04/11/2011 20:20:10
First Refresh Time : 04/11/2011 20:20:18
Last Refresh Time : 04/11/2011 20:20:36
Duration : 26s
Module/Action : sqlplus@red.easyteam.fr (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@red.easyteam.fr (TNS V1-V3)
Global Stats
=========================================
| Elapsed | Cpu | Other | Buffer |
| Time(s) | Time(s) | Waits(s) | Gets |
=========================================
| 25 | 24 | 0.20 | 1M |
=========================================
SQL Plan Monitoring Details (Plan Hash Value=4026484343)
================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows |
| | | | (Estim) | | Active(s) | Active | | (Actual) |
================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | |
| -> 1 | SORT AGGREGATE | | 1 | | 19 | +8 | 1 | 0 |
| -> 2 | NESTED LOOPS | | 104K | 6M | 19 | +8 | 1 | 5775 |
| -> 3 | TABLE ACCESS FULL | X | 104K | 59 | 19 | +8 | 1 | 5775 |
| -> 4 | TABLE ACCESS FULL | X | 1 | 57 | 26 | +1 | 5776 | 5775 |
================================================================================================
Rapport en mode Flash
Le rapport en mode Flash est un rapport HTML qui télécharge depuis le site OTN la plugin associée ; Pour le visualiser, il faut donc générer le fichier HTML puis l’afficher depuis un navigateur qui accède à Internet ; la commande ci-dessous se charge de générer le fichier gg.html :
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
col sid new_value sid format 999999999
col serial# new_value serial format 999999999
select sid, serial#, logon_time
from v$session
where status='ACTIVE'
and username=user
order by logon_time desc;
set head off
set long 50000
set longchunksize 50000
set lines 20000
col report format a20000
set pages 1000
set feedback off
set verify off
set trims on
set echo off
spool gg.html
select dbms_sqltune.report_sql_monitor( -
session_id=>&&sid,
session_serial=>&&serial,
report_level=>'all',
type=>'active') report
from dual;
spool off
set head on
set lines 140
set verify on
set feedback on
set echo off
set pages 1000
exit
Une fois le fichier générés, vous pouvez supprimer les lignes avant la balise <html> et après la balise </html> à l’aide de la commande ed:
LINE=`grep -n "<html>" gg.html |cut -d ':' -f 1|head -n 1`
LINE=`echo "$LINE-1" | bc`
ed gg.html <<EOF
1,${LINE}d
w
q
EOF
LINE=`grep -n "</html>" gg.html |cut -d ':' -f 1|tail -n 1`
LINE=`echo "$LINE+1" | bc`
ed gg.html <<EOF
${LINE},$d
w
q
EOF
Et voilà ; il ne reste plus qu’à visualiser votre rapport en ouvrant le fichier comme ci-dessous :
