News zu Oracle

DBA Tipp: SQL-Sta­te­­ments mit Syntax-Fehlern (Failed Parses) identifizieren

Das Debugging von feh­ler­haf­ten SQL-State­ments in einer Anwendung kann, ins­be­son­de­re bei größeren Ap­pli­ka­tio­nen, ziemlich aufwendig sein, solange keine Pro­to­kol­lie­rung der feh­ler­haf­ten State­ments erfolgt. Dabei lohnt es sich, SQL-State­ments mit Syntax-Fehlern zu identifizieren.

Stan­dard­mä­ßig werden in einer Oracle Datenbank keine feh­ler­haf­ten State­ments fest­ge­hal­ten. Es werden aber, zumindest für State­ments mit Syn­tax­feh­lern, die Zähler „parse count (failures)“, „parse count (hard)“ und „parse count (total)“ für jedes feh­ler­haf­te Statement um eins erhöht.

Der Wert für diese Zähler kann z.B. mit dem folgenden Statement ermittelt werden:

COLUMN NAME FORMAT A30
SET PAGES 400
SELECT INST_ID, NAME, VALUE
FROM GV$SYSSTAT
WHERE NAME IN (
  'parse count (total)',
  'parse count (hard)',
  'parse count (failures)'
);

Im Folgenden werden wir das einmal an einem Beispiel erläutern.

Das feh­ler­haf­te SQL-Statement lautet:

SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE__ID = 100;

Kor­rek­ter­wei­se müsste das Statement aber so lauten: 

SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 100;

Mit diesem PL/SQL-Skript werden wir das feh­ler­haf­te SQL-Statement 10000 mal ausführen: 

SET TIMING ON;
SET SERVEROUTPUT ON;
DECLARE
  I NUMBER := 1;
  I_MAX NUMBER := 10000;
  FAIL_CNTR NUMBER := 0;
  EXEC_CNTR NUMBER := 0;
BEGIN
  WHILE I <= I_MAX LOOP
    BEGIN
      EXEC_CNTR := EXEC_CNTR + 1;
      EXECUTE IMMEDIATE '
SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE__ID=100
';
    EXCEPTION
      WHEN OTHERS
      THEN FAIL_CNTR := FAIL_CNTR + 1;
    END;
    I := I + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(CHR(10) || FAIL_CNTR || ' / ' || EXEC_CNTR || ' statements failed.');
END;
/
SET TIMING OFF;

Vorher sind die Werte für Parse count Total/Hard/Failures so verteilt: 

   INST_ID NAME                                VALUE
---------- ------------------------------ ----------
         1 parse count (total)                  5909
         1 parse count (hard)                   1444
         1 parse count (failures)                  0

Nachdem das korrekte SQL-Statement 10000 mal aus­ge­führt wurde (Dauer: 0,28 Sekunden): 

   INST_ID NAME                                VALUE
---------- ------------------------------ ----------
         1 parse count (total)                  7162
         1 parse count (hard)                   1740
         1 parse count (failures)                  0

Nachdem das feh­ler­haf­te SQL-Statement 10000 mal aus­ge­führt wurde (Dauer: 2,69 Sekunden): 

   INST_ID NAME                                VALUE
---------- ------------------------------ ----------
         1 parse count (total)                 17195
         1 parse count (hard)                  11746
         1 parse count (failures)              10000

Man sieht, dass für die Aus­füh­rung des korrekten SQL-State­ments kein Parse mehr notwendig ist, weil das Statement schon im Library Cache vorhanden ist. Das schlägt sich auch in einer schlech­te­ren Per­for­mance nieder, da für das Parsing der 10000 State­ments 2,41 Sekunden (2,69 – 0,28) benötigt werden.

Um jetzt die Sessions zu ermitteln, die für die vielen Failed Parses ver­ant­wort­lich sind, kann die View gv$sesstat verwendet werden: 

SELECT S.INST_ID, S.SID, S.SERIAL#, STATN.NAME, STAT.VALUE
FROM GV$SESSION S
JOIN GV$SESSTAT STAT
  ON S.SID = STAT.SID AND S.INST_ID = STAT.INST_ID
JOIN GV$STATNAME STATN
  ON STAT.STATISTIC# = STATN.STATISTIC# AND STAT.INST_ID = STATN. INST_ID
WHERE STATN.NAME = 'parse count (failures)' AND STAT.VALUE > 0
ORDER BY STAT.VALUE DESC;
   INST_ID        SID    SERIAL# NAME                                VALUE
---------- ---------- ---------- ------------------------------ ----------
         1        386      42069 parse count (failures)              30000

Mit Hilfe der SID und der SERIAL# kann man die SQL-State­ments mit den Syn­tax­feh­lern ermitteln, in dem man für die be­trof­fe­ne Session ein Tracing ein­rich­tet. Anhand des Beispiels wird das Tracing wie folgt eingerichtet. 

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(386, 42069, waits => false, binds => true);

Damit wird eine Trace-File auf dem Da­ten­bank­ser­ver im Trace-Ver­zeich­nis erstellt. Für jeden fehl­ge­schla­ge­nen Parse wird ein Log-Eintrag in der folgenden Form erzeugt: 

PARSE ERROR #140737352601368:len=70 dep=1 uid=0 oct=3 lid=0 tim=3707057109080 err=904
SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE__ID=100

Nachdem die be­trof­fe­nen State­ments ermittelt wurden, empfehlen wir das Tracing wieder zu de­ak­ti­vie­ren, um nicht unnötig viele Log-Einträge zu erzeugen. 

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(386, 42069);

Fazit

Syn­tak­tisch feh­ler­haf­te SQL-State­ments können gut über die Bord­mit­tel der Oracle Datenbank ermittelt werden. Dabei zeigt sich auch, dass ein Igno­rie­ren dieser Statement für einen erhöhten Par­se­auf­wand sorgt und dadurch die Per­for­mance leidet.

Es lohnt sich also, SQL-State­ments mit Syntax-Fehlern zu identifizieren. 

Hier findest du weitere in­ter­es­san­te Posts zu den Themen SQL Tuning oder DBA Tipps aus unserem News und Insights Bereich. 
icon-arrow_right_medium-violet-blue.svg

Share this article

Facebook 
Twitter 
LinkedIn 
XING 
WhatsApp 
Email