News & Events
SQL-Statements mit Syntax-Fehlern (Failed Parses) identifizieren

Bereits mit Oracle Bordmitteln lassen sich syntaktisch fehlerhafte SQL-Statements ermitteln.

Icon Unternehmen

Das Debugging von fehlerhaften SQL-Statements in einer Anwendung kann, insbesondere bei größeren Applikationen, ziemlich aufwendig sein, solange keine Protokollierung der fehlerhaften Statements erfolgt.

Standardmäßig werden in einer Oracle Datenbank keine fehlerhaften Statements festgehalten.  Es werden aber, zumindest für Statements mit Syntaxfehlern, die Zähler „parse count (failures)“, „parse count (hard)“ und „parse count (total)“ für jedes fehlerhafte 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 fehlerhafte SQL-Statement lautet:

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

Korrekterweise 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 fehlerhafte 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 ausgefü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 fehlerhafte SQL-Statement 10000 mal ausgefü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 Ausführung des korrekten SQL-Statements kein Parse mehr notwendig ist, weil das Statement schon im Library Cache vorhanden ist. Das schlägt sich auch in einer schlechteren Performance nieder, da für das Parsing der 10000 Statements 2,41 Sekunden (2,69 - 0,28) benötigt werden.

Um jetzt die Sessions zu ermitteln, die für die vielen Failed Parses verantwortlich 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-Statements mit den Syntaxfehlern ermitteln, in dem man für die betroffene Session ein Tracing einrichtet. 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 Datenbankserver im Trace-Verzeichnis erstellt. Für jeden fehlgeschlagenen 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 betroffenen Statements ermittelt wurden, empfehlen wir das Tracing wieder zu deaktivieren, um nicht unnötig viele Log-Einträge zu erzeugen.

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(386, 42069);

Fazit:
Syntaktisch fehlerhafte SQL-Statements können gut über die Bordmittel der Oracle Datenbank ermittelt werden. Dabei zeigt sich auch, dass ein Ignorieren dieser Statement für einen erhöhten Parseaufwand sorgt und dadurch die Performance leidet.


Newsletter-Archiv