TUNING session_cached_cursor with PL/SQL procedure

Dopo aver rivisto i miei post riguardanti session_cached_cursor ho deciso di provare a mettere insieme una procedura PL/SQL che oltre a trovare gli utenti che con la loro sessione superano il 100% di hit/parse, desse informazioni aggiuntive come per esempio il nome, l’id_sql utilizzato o precedente, sql_text, event e altro in modo da dare un quadro completo e più dettagliato.
Forse lo stesso risultato si poteva ottenere con un buon codice SQL ma ho preferito creare una procedure anche per rispolverare la mia programmazione PL/SQL.

Praticamente esegue dei loop su entrambi i cursori C1 e C2 in modo da mettere in luce solo quelle sessioni che superano il 95% di hit/parse.
Una volta compilata dando ovviamente i grant di select di un utente che non sia SYS alle tabelle di sistema come per esempio V$SESSION,v$sqlarea ecc. si può facilemente lanciare con:

CREATE OR REPLACE
PROCEDURE session_mon
IS
  CURSOR c1
  IS
    SELECT hits.sid,
      hits,
      parses,
      ROUND(hits/parses*100)
    FROM
      (SELECT sid,
        s.value hits
      FROM v$statname n,
        v$sesstat s
      WHERE s.statistic# = n.statistic#
      AND n.name         = 'session cursor cache hits'
      ) hits ,
    (SELECT sid,
      s.value parses
    FROM v$statname n,
      v$sesstat s
    WHERE s.statistic# = n.statistic#
    AND n.name         = 'parse count (total)'
    ) parses
  WHERE hits.sid             = parses.sid
  AND parses.parses          > 0
  AND ROUND(hits/parses*100)>=95
  ORDER BY 1 DESC;
  c1_sid sys.v_$sesstat.sid%TYPE;
  c1_hits v$sesstat.value%TYPE;
  c1_parses v$sesstat.value%type;
  c1_hp NUMBER :=ROUND(c1_hits/c1_parses*100);
  CURSOR c2
  IS
    SELECT s.sid,
      P.SPID,
      S.SERIAL#,
      S.OSUSER,
      SA.SQL_TEXT,
      SA.SQL_ID,
      s.prev_sql_id,
      S.STATUS,
      S.USERNAME,
      SW.STATE,
      SW.EVENT,
      S.MACHINE,
      S.PROGRAM
    FROM V$SESSION S,
      V$PX_SESSION PX,
      V$SESSION_WAIT SW,
      V$PROCESS P,
      V$SQLAREA SA,
      (SELECT * FROM V$SESSION_LONGOPS WHERE TIME_REMAINING <> 0
      ) SLO,
    (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1
    ) OWNSESSION
  WHERE s.sql_address=sa.address(+)
  AND S.SID          = SW.SID(+)
  AND S.PADDR        =P.ADDR
  AND (S.SID         = SLO.SID(+)
  AND S.SERIAL#      = SLO.SERIAL#(+))
  AND (S.SID         = PX.SID(+)
  AND S.SERIAL#      = PX.SERIAL#(+))
  AND S.TYPE         = 'USER'
  AND S.OSUSER      <> 'jdeb7333'
  AND S.OSUSER      <> 'oracle'
  ORDER BY 1 DESC;
  c2_sid v$session.sid%type;
  c2_spid V$PROCESS.spid%type;
  c2_serial V$SESSION.serial#%type;
  c2_osuser v$session.osuser%type;
  c2_SQL_TEXT v$sqlarea.sql_text%type;
  c2_SQL_id v$sqlarea.sql_id%type;
  c2_prev_SQL_id V$SESSION.prev_sql_id%type;
  c2_status v$session.status%type;
  c2_username v$session.username%type;
  c2_state v$session_wait.state%type;
  c2_event v$session_wait.event%type;
  c2_machine v$session.machine%type;
  c2_program v$session.program%type;
BEGIN
  OPEN C1;
  OPEN C2;
  <>
  LOOP
    FETCH C1 INTO c1_sid,c1_hits, c1_parses,c1_hp;
    EXIT
  WHEN C1%NOTFOUND;
    LOOP
      FETCH C2
      INTO c2_sid,
        c2_spid,
        c2_serial,
        c2_osuser,
        c2_SQL_TEXT,
        c2_SQL_id,
        c2_prev_SQL_id,
        c2_status,
        c2_username,
        c2_state,
        c2_event,
        c2_machine,
        c2_program;
      IF c2_sid = c1_sid THEN
        DBMS_OUTPUT.ENABLE (buffer_size => NULL);
        DBMS_OUTPUT.PUT_LINE(c1_hp||'% ;'|| c2_sid||'; '|| c2_spid||'; '|| c2_serial ||'; '|| c2_osuser||'; '||c2_SQL_TEXT ||'; '||c2_SQL_id ||'; '|| c2_prev_SQL_id||'; ' || c2_status||'; '|| c2_username||'; '|| c2_state||'; '|| c2_event||'; '|| c2_machine||'; '|| c2_program);
        CLOSE C2;
        OPEN C2;
        GOTO lp;
      ELSE
        IF C2%NOTFOUND THEN
          CLOSE C2;
          OPEN C2;
          GOTO lp;
        ELSE
          EXIT
        WHEN C1%NOTFOUND;
        END IF ;
      END IF ;
    END LOOP;
    EXIT
  WHEN C2%NOTFOUND;
  END LOOP;
  CLOSE C1;
  CLOSE C2;
END session_mon;

set serveroutput on;
exec session_mon;


blocco anonimo completato
  
153% ;165; 23930; 29654; mario.rossi; ; ; 7nacmhkk9c6rh; INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

99% ;143; 24207; 62019; roberto.verdi; INSERT INTO ..... ; 8x6ddxfcuwqna; 8x6ddxfcuwqna; INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

100% ;137; 13757; 40756; giovanni.neri; DELETE FROM ...... ; 2apjjpak50830; 2apjjpak50830; INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

96% ;89; 13484; 37497; maria.beralda; SELECT * FROM .....; 2f3a5zqx90uxc; 2f3a5zqx90uxc;  INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

167% ;74; 26811; 11150; guido.lavespa; ; ; 6cyvu230zxnx8; INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

107% ;44; 14366; 41924; giovanna.coscialunga; ; ; 6cyvu230zxnx8; INACTIVE; *_D**_*I*; WAITING; SQL*Net message from client; domain\servername; o*xp***e.ex*

Notiamo che la prima colonna, anche se la formattazione non è delle migliori è rappresentata della percentuale hit/parse del sid che supera in questo caso il 95%, le altre colonne inserite rispettivamente, spid, serial, osuser,  SQL_TEXT, SQL_id,prev_SQL_id,status,username,state,event,machine e infine il program.



Ho diviso le colonne con il ; in modo da poter importare il risultato in un altro file software per avere una maggiore leggibilità e per eseguire ulteriori analisi.



Questa procedura potrebbe essere un buon inizio per un tuning, mettendo in luce gli utenti che hanno un hit/parse elevato, sfuttando la Session Cursor Cache.



Aspetto commenti...