Wydajny jak CLOB

Jest to problem występujący często w przy implementacji algorytmów,
Tworzymy korzystając z bibliotek dostarczanych od producenta kawałek  kodu i okazuje się, że jest albo za wolno albo niestabilnie dla naszego zbioru danych. Czasami udaje się nam  błądzić po parametrach brzegowych lub znaleźć zastosowania dla funkcji bibliotecznych o jakich się nie śniło ich autorom.

  Przykładem jest nadzwyczaj nędzna wydajność operacji  łańcuchowych na polach typu CLOB. Rozważmy poniższy blok kodu.
Instrukcja dbms_random.string('p', 4000) oznacza stwórz losowy łańcuch tekstowy składający się z  4000 widocznych na wydruku znaków ASCII.
Kod poniższy tworzy losowy łańcuch  długości 32000 znaków i zlicza w nim cyferki dwoma metodami za pomocą  zliczania naiwnego i wyrażenia regularnego reagującego na cyferki..
Celem dalszych testów przypomnijmy sobie, że w kodzie PL/SQL maksymalna wielkość  zmiennej  typu VARCHAR2 wynosi 32767 znaków...


DECLARE
    vc_Clob   CLOB := EMPTY_CLOB();
    vn_Start NUMBER;
    vn_Count NUMBER := 0;
    vn_Size NUMBER;
BEGIN
  vc_Clob :=dbms_random.string('p', 4000);

  vc_Clob := vc_Clob || vc_Clob;
  vc_Clob := vc_Clob || vc_Clob;
  vc_Clob := vc_Clob || vc_Clob;
  vn_Start := dbms_utility.get_time;
 
  vn_Size := LENGTH(vc_Clob);
  FOR lp IN 1..vn_Size
    LOOP
        IF  SUBSTR( vc_Clob, lp, 1) BETWEEN '0'  AND  '9' THEN
           vn_Count := vn_Count + 1;
        END IF;
    END LOOP;
  dbms_output.put_line( 'Metoda 1. Długość : ' || LENGTH (vc_Clob) || ', liczba cyferek '  || vn_Count );
  dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );

  vn_Start := dbms_utility.get_time;
  vn_Count := REGEXP_COUNT( vc_Clob,'[[:digit:]]');
  dbms_output.put_line( ' Metoda 2. Długość : ' || LENGTH (vc_Clob) || ', liczba cyferek '  || vn_Count );
  dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...');
END;

Po uruchomieniu  na wydajnym serwerze, radzącym sobie z terabajtowymi bazami, otrzymujemy następujący wynik
Metoda 1. Długość : 32000, liczba cyferek 3224
8.8 sekund...
 Metoda 2. Długość : 32000, liczba cyferek 3224
0.1 sekund...

Różnica jest olbrzymia - ale oba rozwiązania są wolne..

Zamieńmy w powyższym bloku linię
vc_Clob   CLOB := EMPTY_CLOB();
na
 vc_Clob VARCHAR(32000 CHAR);szybko okażesię ,że nawet grudniowe noce sa za krótkie
Wówczas otrzymamy następujące czasy wykonania:


Metoda 1. Długość : 32000, liczba cyferek 3424
0.01 sekund...
 Metoda 2. Długość : 32000, liczba cyferek 3424
0.16 sekund...

Dla metody naiwnej przyrost wydajności jest  prawie tysiąckrotny, wyrażenia regularne dla obu typów działają wolno z podobną szybkością..

Co więc robić jeśli chcemy  napisać zaawansowane przetwarzanie obszernych pól CLOB??

• przenieść procesowanie na godziny nocne, szybko okaże się, że nawet grudniowe noce są za krótkie
• napisać to przetwarzanie w Java/C++ na kliencie na jakimś złomowatym serwerze. I tak będzie szybciej:)
• postarać się tak zmienić strukturę danych, aby korzystać tylko z typów VARCHAR2, co nie zawsze jest  wykonalne

Niektóre operacje  np. zaawansowana  obróbka dużej ilości danych w XML, algorytmy tekstowe na d polach CLOB powinny być wyprowadzone poza motor bazy danych..

Kto mnie trzyma??

 Problem blokad jest wpisany w naturę  przetwarzania bazodanowego.. Często powstają one w najmniej pożądanej chwili i są w stanie skutecznie  zdezorganizować kluczowe wielkie procesy np zamknięcie miesiąca, złożone raportowanie, przetwarzanie transakcyjne.. Dlatego warto takie sytuacje logować (wykorzystałem wcześniej opisany pakiet  PCKG_LOGGING).
Poniższa funkcja wykrywa blokady da danego obiektu  bazodanowego w określonym schemacie i  zapisuje informacje o blokadach z wykorzystaniem transakcji autonomicznych, nie ingeruje zatem w logikę transakcyjną kodu w którym jest użyta.
Co ciekawego można zrobić z poniższym kodem:

1. dodanie tabeli mapującej loginy użytkowników na adresy poczty elektronicznej  i wysyłanie maili do niesfornych użytkowników przy wykorzystaniu pakietu utl_smtp, warto także w ten sposób poinformować osobę bezpośrednio odpowiedzialną za bieżące przetwarzanie bazodanowe
2. jeśli  DBA opakuje ładnie kill session w procedurę PL/SQL - ubijać sesje tworzące nieporządane blokady

Wymagane uprawnienia - dostęp do widoków systemowych v$session, v$locked_object
CREATE OR REPLACE FUNCTION FDB_IsObjectUsed( pc_ObjectOwner VARCHAR2, pc_ObjectName VARCHAR2 ) RETURN NUMBER
AS
CURSOR C_MAIN IS
    SELECT 'Użytkownik bazodanowy ' || USERNAME || ' w sesji NR ' || SID
        || ' zalogowany do domeny jako '  || OSUSER ||  ' na komputerze ' || MACHINE
        || DECODE ( PROGRAM, NULL, NULL, ' korzystający z programu ' || PROGRAM) AS MSG,
        USERNAME,
        OSUSER 
 FROM v$session
 WHERE
   SID IN
 (SELECT DISTINCT SESSION_ID FROM v$locked_object WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = pc_ObjectName AND OWNER =pc_ObjectOwner))
AND SID <> sys_context('USERENV', 'SID')
ORDER BY OSUSER;
 vn_Result NUMBER := 0;
 vc_Body VARCHAR2(4000 CHAR);
 vc_LogMessage VARCHAR2( 4000 CHAR);
BEGIN
  vc_LogMessage := 'Następujący użytkownicy blokują obiekt ' ||  pc_ObjectOwner || '.' || pc_ObjectName ||  ': ';
  FOR EL IN C_MAIN
    LOOP   
        vn_Result := vn_Result +1;

           IF vc_Body IS NULL THEN
             vc_Body := vc_Body || EL.MSG;
          ELSE           
              vc_Body := vc_Body || CHR(13) || CHR(10) || EL.MSG;
          END IF;
                      
    END LOOP;
vc_LogMessage := vc_LogMessage || CHR(13) || CHR(10) || vc_Body;
   IF vn_Result > 0 THEN                  
    PCKG_LOGGING.PDB_ERror('Wykryto blokadę na obiekcie ' || pc_ObjectOwner || '.' || pc_ObjectName, vc_LogMessage);
   END IF;
        RETURN vn_Result;   
   EXCEPTION WHEN OTHERS THEN
        RETURN vn_Result;
END;
/

Kryzysowy( kredytowy ) SQL - symulacja spłaty kredytu

Post został  inspirowany  nocnymi dyskusjami na temat sensowności korzystania z kredytów hipotecznych. W pierwszym podejściu miał być to pakiet, ale postanowiłem ułatwić sobie życie i napisałem zapytanie wyświetlające plan spłaty kredytu  dla rat stałych i malejących.
Zapytanie można w łatwy sposób parametryzować (parametry mają kolor ciemnozielony) zmieniając:

• kwotę kredytu
• czas spłaty
• oprocentowanie w skali rocznej

Przyjęte zostały następujące założenia:

1. Oprocentowanie  podczas spłaty kredytu jest stałe
2. Raty są płatne z góry od  bieżącego dnia co miesiąc 
3. Zakładamy, że oprocentowanie w obrębie każdego miesiąca jest jednakowe

Poniższe zapytanie przy odrobinie  fantazji, można w ciekawy sposób rozwinąć, np zakładając  rózne oprocentowanie w kolejnych latach.

  SELECT d + 1 AS "Numer raty",
          DATA_RATY AS "Data raty",        
          ROUND (RATA_MAL_KAPITAL, 2) AS "Kapitał raty malejącej",
          ROUND (RATA_MAL_ODSETKI, 2) AS "Kwota odsetek",
          ROUND (RATA_MAL_KAPITAL + RATA_MAL_ODSETKI, 2) AS "Kwota raty  malejącej",
          ROUND (KWOTA_MAL_DO_SPLATY, 2) AS "Zadłużenie - raty malejące",
          ROUND (RATA_STALA_KAPITAL, 2) AS "Kapitał raty stałej",
          ROUND (RATA_STALA_ODSETKI, 2) AS "Odsetki raty stałej",
          ROUND (RATA_STALA, 2) AS "Kwota raty stałej",
          ROUND (KWOTA_STALA_DO_SPLATY, 2) AS "Zadłużenie - raty stałe"
     FROM (SELECT 1 FROM DUAL)
   MODEL
      DIMENSION BY (0 d)
      MEASURES (300000 KWOTA_KREDYTU, 6 OPROCENTOWANIE, 240 LICZBA_RAT,
      TRUNC (SYSDATE) DATA_RATY,
             0 KWOTA_MAL_DO_SPLATY,
             0 RATA_MAL_KAPITAL,
             0 RATA_MAL_ODSETKI,            
             0 RATA_STALA,
             0 RATA_STALA_KAPITAL,
             0 RATA_STALA_ODSETKI,
             0 KWOTA_STALA_DO_SPLATY
             )
      RULES
         ITERATE (10000) UNTIL (ITERATION_NUMBER = LICZBA_RAT[0] -1)
         (              
         LICZBA_RAT [ITERATION_NUMBER] =  
               NVL (LICZBA_RAT[ITERATION_NUMBER - 1], LICZBA_RAT[0]),
          OPROCENTOWANIE [ITERATION_NUMBER] =
               NVL (OPROCENTOWANIE[ITERATION_NUMBER - 1], OPROCENTOWANIE[0]/1200),
         RATA_MAL_KAPITAL [ITERATION_NUMBER] =
               KWOTA_KREDYTU[0] / LICZBA_RAT[0],
         DATA_RATY [ITERATION_NUMBER] =
               ADD_MONTHS (DATA_RATY[0], ITERATION_NUMBER),
         KWOTA_MAL_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_MAL_KAPITAL[ITERATION_NUMBER - 1], 0),
         RATA_MAL_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_MAL_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER]  ,                                       
         RATA_STALA_KAPITAL [ITERATION_NUMBER]              
         = KWOTA_KREDYTU[0] *OPROCENTOWANIE [ITERATION_NUMBER] *POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER], ITERATION_NUMBER)/
          ( POWER( 1+OPROCENTOWANIE [ITERATION_NUMBER],  LICZBA_RAT[ITERATION_NUMBER]) -1  ),
           KWOTA_STALA_DO_SPLATY [ITERATION_NUMBER] =
                 NVL (KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER - 1],
                      KWOTA_KREDYTU[0])
               - NVL (RATA_STALA_KAPITAL[ITERATION_NUMBER - 1], 0),
           RATA_STALA_ODSETKI [ITERATION_NUMBER] =
                 KWOTA_STALA_DO_SPLATY[ITERATION_NUMBER]
               * OPROCENTOWANIE[ITERATION_NUMBER],
           RATA_STALA[ITERATION_NUMBER] = RATA_STALA_KAPITAL[ITERATION_NUMBER] + RATA_STALA_ODSETKI[ITERATION_NUMBER]       
          )

Usuwanie indeksów z wysokim clustering factor

Opisany problem dotyczy bardzo niskiej wydajności operacji DML na  tabelach o trochę większym wolumenie (zetknąłem się problemem, kiedy wielkość tabel przekraczała około 300 mln rekordów), często partycjonowanych, zawierających klasyczne drzewiaste indeksy o  bardzo dużej selektywności. Wówczas najszybszą (w moi odczuciu :) ) metodą dokonania operacji DML na większym wolumenie danych jest:

1. Usunięcie indeksów o wysokim clustering factor
2. Wykonanie operacji DML
3. Ponowne stworzenie usuniętych indeksów 

W poniższym rozwiązaniu wykorzystałem własny pakiet logujący prosty logger wzorowany na log4j.
Do usuwania indeksów w innych schematach niż bieżący przydałyby się uprawnienia CREATE ANY INDEX i DROP ANY INDEX


CREATE TABLE TBL_INDEXES
(
  ID             NUMBER(10),
  SCHEMA_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  TABLE_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  INDEX_NAME     VARCHAR2(30 CHAR)              NOT NULL,
  DDL_STATEMENT  CLOB
);


ALTER TABLE TBL_INDEXES ADD (
  CONSTRAINT PK_INDEXES_COMPLEX
  PRIMARY KEY (ID, SCHEMA_NAME, TABLE_NAME, INDEX_NAME);

CREATE SEQUENCE SEQ_INDEXES START  WITH 1 NOCACHE NOCYCLE;

CREATE OR REPLACE PACKAGE PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN;
END PCKG_INDEXES;

CREATE OR REPLACE PACKAGE BODY PCKG_INDEXES
AS
FUNCTION Fdb_CreateIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_LiczbaBledow NUMBER(10) :=0;  
CURSOR C IS
    SELECT            
          INDEX_NAME,
          DDL_STATEMENT
        FROM TBL_INDEXES
        WHERE ID =
        (
            SELECT MAX(ID) FROM TBL_INDEXES WHERE TABLE_NAME = pc_TableName AND SCHEMA_NAME = pc_SchemaName
        );

BEGIN
    COMMIT; -- aby zamknąc wszystkie locki w danym połączeniu
     FOR EL IN C
        LOOP
            BEGIN              
                EXECUTE IMMEDIATE  EL.DDL_STATEMENT;
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_CreateIndexes', 'Pomyślnie utworzono indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_CreateIndexes', 'Błąd podczas tworzenia indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );              
                vn_LiczbaBledow := vn_LiczbaBledow +1;
            END;       
        END LOOP;  

    IF vn_LiczbaBledow > 0 THEN      
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;      
END;
------------------------------------------------------------------------------------------
FUNCTION Fdb_DropIndexes(pc_SchemaName VARCHAR2, pc_TableName VARCHAR2)   RETURN BOOLEAN
AS
vn_SeqId NUMBER(10):= SEQ_INDEXES.nextval;
CURSOR C IS
    SELECT
            vn_SeqId AS ID,
            OWNER,
            TABLE_NAME,
            INDEX_NAME,
            DBMS_METADATA.GET_DDL( 'INDEX', INDEX_NAME, pc_SchemaName)  AS DDL_STATEMENT
        FROM ALL_INDEXES -- Tutaj działającą perspektywą zawsze będzieALL_INDEXES, dla ALL_INDEXES trzeba mieć specjalne uprawnienia
        WHERE TABLE_NAME = pc_TableName
            AND OWNER = pc_SchemaName
            AND 4*CLUSTERING_FACTOR > NUM_ROWS
            AND INDEX_TYPE = 'NORMAL';
BEGIN
     FOR EL IN C
        LOOP
            BEGIN
                INSERT INTO TBL_INDEXES VALUES EL;
                EXECUTE IMMEDIATE 'DROP INDEX ' || EL.INDEX_NAME;--- DDL czyli niejawny COMMIT = zatwierdzainstrukcję  insert
                PCKG_LOGGING.PDB_INFO('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Pomyślnie usunięto indeks: ' || EL.INDEX_NAME);
            EXCEPTION WHEN OTHERS THEN          
                DELETE FROM TBL_INDEXES WHERE ID = vn_SeqId;
                PCKG_LOGGING.PDB_ERROR('PCKG_INDEXES.Fdb_DropExtractIndexes', 'Błąd podczas usuwania indeksu: ' || EL.INDEX_NAME || ', ' || SQLERRM );
                COMMIT;
                RETURN FALSE;
            END;       
        END LOOP;
    RETURN TRUE;
END;
END PCKG_INDEXES;

Unikalne nazwy dla obiektów

Poniższy trigger wynika z własnych przemyśleń na temat nazywania obiektów w bazie danych i mnóstwa problemów, jeśli w różnych schematach są obiekty o tej samej nazwie np.

• problemy ze scalaniem schematów, występujące w wyniku rotacji użytkowników baz danych
• redundancja danych i problemy związane z identyfikacją biznesowego znaczenia obiektów o takiej samej nazwie  
• kłopoty z utrzymaniem spójności kodu i odwołaniami do obiektów powtarzających się w wielu schematach
• nadmierne zużycie przestrzeni dyskowej

Poniżej przykłady  problematycznych instrukcji
CREATE TABLE DUAL AS
SELECT * FROM DUAL
        UNION
SELECT * FROM DUAL

RENAME myTable TO DUAL
 
Trigger TRG_CHECK_DUPLICATES jest przykładem rozwiązania omawianego problemu. Został napisany przy następujących założeniach:

• nie działa dla użytkowników SYS i SYSTEM
• działa przed operacjami CREATE i RENAME
• dla operacji RENAME dokonujemy prostej analizy polecenia DDL, dla polecenia CREATE nazwa obiektu pobierana jest z predefiniowanej zmiennej ORA_DICT_OBJ_NAME
• w komunikacie wyjątku podawane są nazwy już istniejących obiektów dla instrukcji DDL
• jest instalowany przez DBA w schemacie SYS

CREATE OR REPLACE TRIGGER SYS.TRG_CHECK_DUPLICATES
   BEFORE CREATE
   OR RENAME
   ON  DATABASE
DECLARE
    CURSOR C (pc_ObjectName VARCHAR2) IS
        SELECT OWNER|| '.' || OBJECT_NAME || ' typu '   || OBJECT_TYPE  AS MSG FROM DBA_OBJECTS
          WHERE OBJECT_NAME = pc_ObjectName
              AND OWNER <> ORA_DICT_OBJ_OWNER;
    vc_ErrorMsg VARCHAR2(500 CHAR);
    vc_SQLStatement VARCHAR2(200 CHAR);
    vc_ObjectName VARCHAR2(30 CHAR);
    vt_List DBMS_STANDARD.ORA_NAME_LIST_T := DBMS_STANDARD.ORA_NAME_LIST_T();
    vn_Size    PLS_INTEGER;
BEGIN
    IF USER IN ('SYS', 'SYSTEM') THEN
          RETURN;
    END IF;
    BEGIN
        IF ORA_SYSEVENT = 'RENAME' THEN
            vn_Size := ora_sql_txt (vt_List);
            FOR i IN 1 .. vn_Size
            LOOP
                vc_SQLStatement := vc_SQLStatement || vt_List (i);
            END LOOP;
           
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_SQLStatement), '[[:alnum:]]+[[:cntrl:]]*$',1,1);
            vc_ObjectName := REGEXP_SUBSTR(TRIM(vc_ObjectName), '[[:alnum:]]+',1,1);
         ELSIF ORA_SYSEVENT = 'CREATE' THEN
            vc_ObjectName:= ORA_DICT_OBJ_NAME;
        END IF;
       
        FOR EL IN C(vc_ObjectName)
         LOOP
           IF vc_ErrorMsg IS NULL THEN
                vc_ErrorMsg := 'istnieje już ' || CHR(13) || CHR(10) || EL.MSG;
            ELSE
                vc_ErrorMsg := vc_ErrorMsg || CHR(13) || CHR(10) || EL.MSG ;
            END IF;
         END LOOP;
       
     EXCEPTION WHEN OTHERS THEN
        NULL;
     END;
     IF  vc_ErrorMsg IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20101, 'Zdublowane nazwy obiektów: ' || vc_ErrorMsg );
     END IF;
END;
/