News zu Oracle

DBA Tipp: SQL-Statements mit Syntax-Fehlern (Failed Parses) identifizieren

Das Debugging von fehler­haften SQL-Statements in einer Anwendung kann, insbe­sondere bei größeren Applikationen, ziemlich aufwendig sein, solange keine Protokollierung der fehler­haften Statements erfolgt. Dabei lohnt es sich, SQL-Statements mit Syntax-Fehlern zu identifizieren.

Standardmäßig werden in einer Oracle Datenbank keine fehler­haften Statements festge­halten. 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 fehler­hafte 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 fehler­hafte 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 fehler­hafte 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 ausge­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 fehler­hafte SQL-Statement 10000 mal ausge­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 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 schlech­teren 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 verant­wortlich 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 fehlge­schla­genen 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 betrof­fenen Statements ermittelt wurden, empfehlen wir das Tracing wieder zu deakti­vieren, um nicht unnötig viele Log-Einträge zu erzeugen. 

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(386, 42069);

Fazit

Syntaktisch fehler­hafte 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.

Es lohnt sich also, SQL-Statements mit Syntax-Fehlern zu identifizieren. 

Hier findest du weitere inter­es­sante 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