Script SQL pour tracer un évènement générant du latch

Version imprimable

08/09/2009, 23h28
deupac

Script SQL pour tracer un évènement générant du latch

Bonjour à tous.

Je viens tout juste de m'inscrire sur ce forum (même si j'en suis un lecteur occasionnel) car je n'arrive pas à trouver ce que je cherche :)

Je rencontre actuellement, dans un cadre professionnel, des problèmes de contention au niveau de la library cache, lors des périodes de forte charge. Outre le fait que cela génère des temps d'exécutions de certaines requêtes un peu plus long que la normale, cela engendre un effet boule de neige qui au final écroule totalement la machine (CPU à fond, swap, timeout de l'application lors des connexions à la base etc... ces problèmes sont en cours d'analyse...)

En interrogeant les vue v$sql, v$sessions et v$session_waits dans un script qui tourne en crontab, j'arrive à récupérer les évènements sur lesquelles les requêtes attendent (comme par exemple la requête X attend sur un évènement latch free - attente de pouvoir poser un latch).

Ce que je n'arrive pas à faire par contre, c'est de pouvoir dire de façon irréfutable que telle requête a généré tel évènement... J'ai un nombre d'attentes (et un temps d'attente total) absolument hallucinant sur des latch library cache, mais pas moyen de savoir quelle(s) requête(s) génère(nt) ces latch en library cache.

Si quelqu'un a en sa possession un script miracle qui puisse me donner satisfaction, je lui en serait éternellement reconnaissant (je n'ai que ca à offrir sur le net, désolé, sinon un coup au bar du coin dans la vraie vie ;) )

Merci d'avance !

Deupac (un pseudo à la con je vous l'accorde).

Bonjour

N'oubliez pas de préciser votre version d'Oracle et votre OS, qui sont des informations à joindre à toute question.

Les attentes de type "library cache latch" indiquent que vous analysez très fréquemment de nouvelles requêtes. C'est usuellement le signe que vous n'utilisez pas de variables de liaison, et que vous répétez inutilement des requêtes qui ne différent que par une valeur dans une clause WHERE.

Code:

1
2
3
4
SELECT * FROM SCOTT.EMP WHERE EMPNO=100;
SELECT * FROM SCOTT.EMP WHERE EMPNO=101;
SELECT * FROM SCOTT.EMP WHERE EMPNO=102;
etc

Vous pouvez utiliser le code suivant (issu de Tom Kyte) pour identifier les requêtes de ce type. En l'état, on ne remonte que les requêtes qui se sont exécutées avec au moins 100 valeurs différentes, mais vous pouvez l'adapter à votre gré en changeant le HAVING final.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-----------------------------------
-- Détection variables non liées --
-----------------------------------
create table t1 as select sql_text from v$sqlarea;
 
alter table t1 add sql_text_wo_constants varchar2(1000);
 
create or replace function 
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);
 
select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

Le mieux serait d'effectuer une trace 10046 de niveau 12 pour avoir les évènements d'attente de chaque requête ainsi que les binds variables.
Code:

1 2 ALTER SESSION SET events '10046 trace name context forever, level 12'
tu peux également utiliser DBMS_SUPPORT:
Code:

1 2 3 4 5 6 7 8 dbms_support.start_trace_in_session(sid => 127, serial => 29, waits => TRUE, binds => TRUE) dbms_support.stop_trace_in_session(sid => 127, serial => 29)

Bonjour,

Si v$session n'est pas suffisant (avec le sql_id du statement en cours lors de ce wait event) l'autre solution est de prendre des systemstate:

Code:

ALTER SYSTEM SET EVENTS 'immediate trace name systemstate level 10'

à quelque secondes d'intervalle. En cherchant bien, on peut y retrouver les latch et les sessions associées.
Ou les envoyer au support Oracle pour analyse.

Pomalaix,
A partir de la 10g, voici ce que j'utilise pour faire l'equivallent:
Code:

1 2 3 4 5 6 7 8 9 10 select count(*)-1 number_of_statements_overhead, sum(loads)-max(loads) hard_parses_overhead, round((sum(sharable_mem)-max(sharable_mem))/1024/1024,3) mbytes_overhead, max(sql_text) sql_text_for_one_statement,force_matching_signature from v$sqlarea where force_matching_signature<>0 group by force_matching_signature,plan_hash_value,optimizer_env_hash_value having count(*) > 1 order by count(*) desc
Car avec FORCE_MATCHING_SIGNATURE, plus besoin de chercher à remplacer les constantes. Oracle le fait lui même (utilisé par cursor_sharing)

Cordialement,
Franck.

09/09/2009, 11h24
pifor

Avec Oracle 10, DBMS_MONITOR permet aussi de tracer une session avec des paramètres pour les waits events et pour les bind variables.
09/09/2009, 11h50
Pomalaix

Citation:

Envoyé par pachot

...
A partir de la 10g, voici ce que j'utilise pour faire l'equivallent...

Merci, je rajoute ça dans ma boîte à outils !
10/09/2009, 15h25
deupac

Merci à tous pour vos réponses...

Il se trouve que la cause de mes problèmes semble finalement bien plus éloignée et bien moins simple que ce que je pensais au départ.

Je rencontre, en plus de ces problèmes au niveau d'Oracle, de gros problèmes au niveau matériel. Je vais donc commencer par corriger ce qui supporte Oracle avant de mettre Oracle en cause.

Merci à tous pour vos réponses, je reviendrais ;)

PS : Pachot, je n'ai pas réussi à faire fonctionner ta requête, il y a des group by sur des champs qui ne sont pas appelés dans le select, j'ai eu un peu la flemme de creuser pour comprendre et corriger. Merci à toi pour ta réponse en tous les cas.

Deupac.
10/09/2009, 15h35
pachot

Citation:

il y a des group by sur des champs qui ne sont pas appelés dans le select

Mais c'est pas interdit ça ;) Le problème doit venir d'ailleurs.