Semantyka bajtowa i znakowa a poprawność kodu

Jest to temat szeroki... Należy zacząć od tego, że problem zależy od

• ustawień bazy - jeśli parametr NLS_LENGTH_SEMANTICS ma na poziomie bazy wartość CHAR, i nie jest przesłaniany w sesjach, to problem nie występuje, jeśli ma wartość BYTE to jest to źródło wielu dokuczliwych kłopotów, występujących przy masowym przetwarzaniu lub sporadycznie przy ręcznym procesowaniu
• definicji tabel - należy definiować kolumny literałów w tabelach jako NAZWISKO VARCHAR2(50 CHAR), a nie NAZWISKO VARCHAR2(50 ) lub o zgrozo NAZWISKO VARCHAR2(50 BYTE). Niektóre narzędzia CASE nie są świadome tego problemu
•  definicji zmiennych znakowych w kodzie - przy definicji zmiennych należy w sposób jawny ( tylko spacja i cztery dodatkowe litery CHAR) deklarować semantykę znakową

Ustawmy sobie parametr na poziomie sesji
ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE
i wykonajmy poniższy blok

DECLARE 

   vc_zmienna VARCHAR(3  );
BEGIN
   vc_zmienna :='ość';
END;
otrzymamy wyjątek::
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
bo

 SELECT vsize('ość') FROM dual
zwraca 7 (funkcja vsize zwraca wielkość zmiennej w bajtach)..
Okazuje się, że w przypadku ustawień mojej bazy polskie ogonki kodowane są na 3 bajtach, na innych bazach z innymi ustawieniami NLS może to być inna wartość..
Dopiero kiedy poprawimy   wartość wielkość zmiennej vc_zmienna na wartość zwracaną przez funkcję vsize, blok zadziała poprawnie..
Powyższy blok zadziała poprawnie, jeśli parametr NLS_LENGTH_SEMANTICS będzie mieć wartość CHAR po wykonaniu instrukcji
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR
Poprawmy blok

DECLARE 

   vc_zmienna VARCHAR(3 CHAR );
BEGIN
   vc_zmienna :='ość';
END;
Powyższy bok zawsze zadziała poprawnie na bazach o różnch ustawieniach i tak też należy kodować..
Opisywany problem jest szczególnie dokuczliwy przy przenoszeniu danych pomiędzy bazami o różnych ustawieniach semantyki oraz dystrybucji kodu po różnych firmach.. Często pierwszym ratunkiem jest bezmyślne zwiększanie wielkości pól, aż dana funkcjonalność dobrze zadziała

Czy to Java czy C - porównanie wydajności procedur zewnętrznych w Windows

Całość eksperymentów przeprowadziłem na Oracle 11g 64 bit zainstalowanym na Windows 7.

Napisałem niezwykle prostą funkcję w C

int _stdcall sum(int x , int y)

{

return x+y;

}

Korzystając z kompilatora Microsoft Visual C++ opakowałem powyższą w bibliotekę DLL  na platformę x64( 32 bitowe biblioteki nie współdziałają z Oracle 64 bit), wygenerowałem plik test_dll.dll.
Kilka chwil zajęło zmuszenie listenera do współpracy, ale  przy odrobinie dobrych chęci nie jest to trudne. Natępnie podłączyłem zewnętrzny kod:

CREATE OR REPLACE LIBRARY MyC_LIB 

  AS 'D:\Projects\CPP\test_dll\Release\test_dll.dll';

CREATE OR REPLACE FUNCTION test_CSUM

(x BINARY_INTEGER, y BINARY_INTEGER

RETURN BINARY_INTEGER

AS LANGUAGE C

LIBRARY MyC_LIB

NAME "sum";

Następnie wykonałem zapytanie:
 

SELECT SUM(TEST_CSUM(2,1)) /3 FROM ALL_TAB_COLS

 Wynik z zapytania zwraca liczę rekordów z view: wynik 90451 rekordów został otrzymany w czasie 30 sekund. Wychodzi około 3 tysięcy wywołań rekordów na sekundę ..

Pokombinujmy, jeśli do definicji funkcji dodamy modyfikator DETERMINISTIC , to czas wykonania spadnie do 1s. Ale:

• funkcje deterministyczne to szczególny przypadek i dosyć rzadki w przyrodzie
• funkcje deterministyczne warto stosować, jeśli zbiór parametrów wejściowych jest niewielki, dla obliczeń numerycznych dla co najwyżej kilkunastotysięcznego zbioru parametrów wejściowych
•  Oracle w przypadku zewnętrznych procedur nie zwróci nam uwagi, że wynik funkcji zależy od daty lub generatora liczb losowych

Mogłoby się wydawać, że natywne niskopoziomowe biblioteki DLL powinny być niezwykle szybkie, a jednak nie nadają się do wykorzystania w przypadku zapytań obrabiających  duże zbiory danych...

Wykonajmy analogiczny eksperyment z Java...

public class SumTest {
 public static int sum (int a, int b)
{
    return a+b;
    }
} 

Załadujmy klasę:

loadjava -thin -user  user/pwd@test d:\PLSQL\SumTest.java 

Dla Oracle 64 bit dla Windows loadjava działa tylko w wersji thin..

Kompilujemy pakiet i mapujemy funkcję

alter java class user."SumTest" compile;

CREATE OR REPLACE FUNCTION user.test_jsum

(a NUMBER, b Number)

RETURN NUMBER

AS

LANGUAGE JAVA

NAME 'SumTest.sum

(int, int) return int';

I sprawdzenie

SELECT SUM(TEST_JSUM(2,1)) /3 FROM ALL_TAB_COLS

Wynik  90451 rekordów pojawił się po 5 sekundach.. Jest pięć razy szybciej około 18 tysięcy rekordów na sekundę.. Dla niewielkich zbiorów danych można ścierpieć...

 Podsumowując: do przetwarzanie  masowego wielkich zbiorów danych funkcje zewnętrzne się nie nadają..  Najlepiej sprawdzają się w rzadkich i nieczęsto stosowanych przypadkach

Zabawa klockami - przyśpieszamy budowę wieży Hanoi

Wieża  Hanoi jest  algorytmem bardzo krótkim i zwięzłym o wykładniczo rosnącej złożoności, wraz z liczbą krążków.

Implementacja w języku PL/SQL jest bardzo prosta..

CREATE OR REPLACE PROCEDURE Pdb_HanoiTower

( vn_Level NUMBER, vn_From NUMBER, vn_To NUMBER,

vn_Helper NUMBER) IS

 BEGIN

  IF vn_Level > 0 THEN

   Pdb_HanoiTower( vn_Level-1, vn_From, vn_Helper, vn_To);

    Pdb_HanoiTower(vn_Level-1, vn_Helper, vn_To, vn_From);

  END IF;

  END;

Do  testowania wydajności procedury wykorzystajmy poniższy blok:

DECLARE

 vn_Start NUMBER;

BEGIN

 vn_Start := dbms_utility.get_time;

 PdB_HanoiTower(25,1,3,2);

 DBMS_OUTPUT.PUT_LINE( round( (dbms_utility.get_time- vn_start)/100, 2 ) || ' seconds...' );

END;

Blok wykonał się w 16,38 s (średnia z kilku pomiarów).

Spróbujmy przyśpieszyć… Zauważmy, że do przekazywania parametrów wykorzystywany jest typ number, który zapewnia bardzo duży zmiennoprzecinkowy zakres liczbowy, a w naszym przypadku wystarczy zakres  typu SIMPLE_INTEGER – jest to odpowiednik czterobajtowego typu signed  int  z języka C bez sprawdzenia przepełniania  ( dla zwiększenia wydajności).. Unikniemy także niepotrzebnych konwersji między typami numerycznymi zmiennopozycyjnymi i całkowitymi..

CREATE OR REPLACE PROCEDURE Pdb_HanoiTower

( vn_Level SIMPLE_INTEGER, vn_From SIMPLE_INTEGER, vn_To SIMPLE_INTEGER,

vn_Helper SIMPLE_INTEGER) IS

 BEGIN

  IF vn_Level > 0 THEN

    Pdb_HanoiTower( vn_Level-1, vn_From, vn_Helper, vn_To);

    Pdb_HanoiTower(vn_Level-1, vn_Helper, vn_To, vn_From);

  END IF;

  END;

Blok testowy dla procedury w powyższej postaci wykonał się w 15.08 s (czas uśredniony).

Przyśpieszajmy dalej..  Ponieważ w trakcie działania procedury wywoływane są  dwie procedury z czterema parametrami, zastosujmy mechanizm inline znany z języka C++ - zamiast wskaźnika do funkcji, wstawiane jest rozwinięcie funkcji..

CREATE OR REPLACE PROCEDURE Pdb_HanoiTower

( vn_Level SIMPLE_INTEGER, vn_From SIMPLE_INTEGER, vn_To SIMPLE_INTEGER,

vn_Helper SIMPLE_INTEGER) IS

 BEGIN

  IF vn_Level > 0 THEN

    PRAGMA INLINE (Pdb_HanoiTower, 'YES');

    Pdb_HanoiTower( vn_Level-1, vn_From, vn_Helper, vn_To);

    PRAGMA INLINE (Pdb_HanoiTower, 'YES');

    Pdb_HanoiTower(vn_Level-1, vn_Helper, vn_To, vn_From);

  END IF;

  END;

Blok testowy wykonał się w 8.66 s – to prawie dwukrotne przyśpieszenie..

Domyślnie funkcjonalności PL/SQL kompilowane są do kodu pośredniego wykonywanego na maszynie wirtualnej.. Oracle 11g oferuje kompilaję do kodu natywnego dla danego systemu operacyjnego.. Wykorzystajmy to

ALTER PROCEDURE PdB_HanoiTower COMPILE PLSQL_CODE_TYPE = NATIVE

Czas wykonania zmniejszył się  do 3.83 s. – przyrost ponad czterokrotny..

Usuńmy teraz  wskazówki do rozwinięcia funkcji inline (dodawanie ich jest mocno uciążliwe), zachęćmy  kompilator do samodzielnego rozwinięcia i innych sztuczek optymalizacyjnych( parametr kompilacji PLSQL_OPTIMIZE_LEVEL = 3).

Kod wraca do czytelnej postaci:

CREATE OR REPLACE PROCEDURE Pdb_HanoiTower

( vn_Level SIMPLE_INTEGER, vn_From SIMPLE_INTEGER, vn_To SIMPLE_INTEGER,

vn_Helper SIMPLE_INTEGER) IS

 BEGIN

  IF vn_Level > 0 THEN

    Pdb_HanoiTower( vn_Level-1, vn_From, vn_Helper, vn_To);

    Pdb_HanoiTower(vn_Level-1, vn_Helper, vn_To, vn_From);

  END IF;

  END;

Po kompilacji

ALTER PROCEDURE PdB_HanoiTower COMPILE PLSQL_CODE_TYPE = NATIVE  PLSQL_OPTIMIZE_LEVEL = 3

Czas  wykonania zmniejszył  się do 3.43..

Warto było?? Uważam, że tak!

Uwagi:

·         Powyższy przykład dotyczy Oracle 11g

·         Po sprawdzeniu na platformach  Windows 64 i HP Unix 64 otrzymałem dość zbliżone przyrosty, wstawiłem z pierwszego środowiska

·         Optymalizacja powyższa przeznaczona jest  raczej dla algorytmów obliczeniowych lub operujących ma łańcuchach, w mniejszym stopniu opierających się na częstych operacjach  bazodanowych

Zasięg zmiennych i bloki anonimowe w procedurach

Przykład wszystko tłumaczy...

CREATE OR REPLACE PROCEDURE PDB_Test IS

   vn_I  PLS_INTEGER := 0;

BEGIN

   vn_I := 1;

   <<JUMP_OUT>>

   DECLARE

      vn_I PLS_INTEGER;

   BEGIN

       vn_I := 2;

       <<JUMP_IN>>

       DECLARE

        vn_I PLS_INTEGER;

       BEGIN

         vn_I := 3;

        dbms_output.put_line('vn_I=' || vn_I);

        dbms_output.put_line('JUMP_OUT.vn_I=' || JUMP_OUT.vn_I);

        dbms_output.put_line('JUMP_OUT.JUMP_IN.vn_I=' ||    JUMP_OUT.JUMP_IN.vn_I);

        dbms_output.put_line('PDB_Test.vn_I=' || PDB_Test.vn_I);

      END JUMP_IN;

   END JUMP_OUT;

END ;