« Real Time SQL Monitoring » est activée par défaut si Oracle Tuning Pack 11g est configuré. Quand un ordre s’exécute en parallèle ou si une requête utilise plus de 5 secondes de CPU ou d’I/O, vous pouvez visualiser les informations associées à ce nouvel outil. Comme souvent dans ce Blog, je ne parlerai pas de ce que vous pouvez faire avec Enterprise Manager mais plutôt avec SQL*Plus. Ainsi, alors que V$SESSION_LONGOPS permet de suivre des opérations longues y compris une étape d’un plan, V$SQL_PLAN_MONITOR permet de suivre l’évolution de l’ensemble d’un plan. L’exemple qui suit illustre cette nouvelle capacité mais avant tout, reportez-vous à la documentation associée regroupée ci-après :
- Oracle 11.1 – Performance Guide
- Oracle 11.1 – PL/SQL Package and Type Reference
- Oracle 11.1 – Reference V$SQL_MONITOR et V$SQL_PLAN_MONITOR
Etape 1 : Configuration, Schéma exemple et SQL
Vérifiez que les packs Diagnostic et Tuning sont activés. Attention vous devez avoir une licence :
SQL> show parameter control_management_pack_access
NAME VALUE
------------------------------ -----------------
control_management_pack_access DIAGNOSTIC+TUNING
Créez un schéma exemple
create table gark(a number,
b number);
begin
for i in 1..100000 loop
insert into gark values (mod(i,19), mod(i,17));
commit;
end loop;
end;
/
Pour utiliser « Real Time SQL Monitoring », nous allons lancer une requête longue. Voici l’exemple que je vous propose d’utiliser celle qui suit :
select /*+ use_nl(a b) */ count(*)
from gark a, gark b
where a.a=b.b;
Avant de l’exécutez, préparez le terrain pour superviser la requête; Dans un vrai cas, vous repérerez la requête au moyen d’un « Top SQL » ou d’un « Top Sessions ». Pour cet exemple nous allons repérer la session avant de commencer. Par exemple si on execute notre exemple sous le schéma SCOTT, connectez-vous d’abord DBA et donnez le privilège « SELECT on SYS.V_$SESSION » à SCOTT
sqlplus / as sysdba
grant select on sys.v_$session to scott;
Enfin repérez la session à partir de laquelle vous allez exécuter la requête :
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;
Etape 2 : Suivre l’évolution de votre requête
Une fois l’ordre lancé, V$SQL_MONITOR conservera ses statistiques pendant son exécution et sans doute quelques minutes après sauf si vous avez vraiment beaucoup d’activité. Bien sur, ceci n’est vrai que parce que l’ordre est très long (merci le HINT). Contrairement à V$SQL, les statistiques ne sont pas cumultives et vous aurez une entrée par exécution. V$SQL_PLAN_MONITOR stocke un niveau de détail encore supérieur puisqu’elle conserve des statistiques pour chacune des étapes du plan de la requête. Repérez d’abord l’ordre SQL qui vous intéresse (Il faut plusieurs secondes pour qu’il apparaisse) :
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
Une fois que vous avez repéré l’ordre qui vous intéresse (SQL_ID, SQL_EXEC_ID et SQL_EXEC_START forment une clé de pour indetifier cet ordre), vous pouvez suivre l’evolution de l’exécution de la requête :
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;
ID|OPERATION |OBJECT| CARD| COST|STAT|OUTPUT_ROWS
--|-------------------------|------|----------|----------|----|-----------
0|SELECT STATEMENT | | | 6718856|EXEC| 0
1| SORT | | 1| |EXEC| 0
2| NESTED LOOPS | | 543129616| 6718856|EXEC| 19857217
3| TABLE ACCESS |GARK | 100000| 69|EXEC| 3772
4| TABLE ACCESS |GARK | 5431| 67|EXEC| 19857217
Vous pouvez simplement réexécuter l’ordre SQL pour suivre l’évolution de votre requête :
/
ID|OPERATION |OBJECT| CARD| COST|STAT|OUTPUT_ROWS
--|-------------------------|------|----------|----------|----|-----------
0|SELECT STATEMENT | | | 6718856|EXEC| 0
1| SORT | | 1| |EXEC| 0
2| NESTED LOOPS | | 543129616| 6718856|EXEC| 167404623
3| TABLE ACCESS |GARK | 100000| 69|EXEC| 31806
4| TABLE ACCESS |GARK | 5431| 67|EXEC| 167404623
/
ID|OPERATION |OBJECT| CARD| COST|STAT|OUTPUT_ROWS
--|-------------------------|------|----------|----------|----|-----------
0|SELECT STATEMENT | | | 6718856|DONE| 1
1| SORT | | 1| |DONE| 1
2| NESTED LOOPS | | 543129616| 6718856|DONE| 526317649
3| TABLE ACCESS |GARK | 100000| 69|DONE| 100000
4| TABLE ACCESS |GARK | 5431| 67|DONE| 526317649
Etape 3 : plus d’informations
A noter que si vous voulez forcer l’affichage des statistiques d’une requête dans les vues V$SQL_MONITOR et V$SQL_PLAN_MONITOR, vous pouvez utiliser le HINT /*+ MONITOR */. Le HINT /*+ NO_MONITOR */ empêche au contraire cet affichage. D’autre part, avec cette requête et mon paramètrage les colonnes WORKAREA_MEM,
WORKAREA_MAX_MEM, WORKAREA_TEMPSEG et WORKAREA_MAX_TEMPSEG de V$SQL_PLAN_MONITOR ne s’affiche pas. J’imagine que c’est lié, la fonction DBMS_SQLTUNE.REPORT_SQL_MONITOR n’affiche rien non plus. Dommage la documentation est très prometteuse :
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
Si vous avez une idée pour le faire fonctionner, je suis preneur ? J’hésite entre une limitation de la Developer Release ou du DBA (moi)
Etape 4 : Quand vous avez fini
drop table gark purge;
