Zabawy z Oracle SQL, PL/SQL i ODI

poniedziałek, 26 października 2015

Jeden parametr sekwencji a wydajność przy wstawianiu

Sekwencja zapewnia unikalność identyfikatorów, ale nie ich ciągłość na zbiorze liczb całkowitych. W zależności od liczby wstawianych rekordów warto buforować w pamięci kolejne wartości sekwencji, zwłaszcza przy masowym wstawianiu. Stwórzmy sekwencję
CREATE SEQUENCE SEQ_START START WITH 1 CACHE 2;
oraz prostą tabelę, gdzie w polu ID będziemy wstawiać kolejne wartości sekwencji
CREATE TABLE TBL_TEST
(
ID NUMBER NOT NULL,
DESCRIPTION_1 VARCHAR2(40 CHAR) NOT NULL,
DESCRIPTION_2 VARCHAR2(40 CHAR) NOT NULL,
DESCRIPTION_3 VARCHAR2(40 CHAR) NOT NULL
);

Poniższy blok wstawia do pustej tabeli sto tysięcy rekordów bez korzystania z sekwencji. Uśredniony czas wykonania to 4,3 sekundy..
DECLARE
    vn_Start NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TBL_TEST';
vn_Start := dbms_utility.get_time;

FOR EL IN 1..100000
   LOOP
       INSERT INTO ALTAIRDB.TBL_TEST (
        ID, DESCRIPTION_1, DESCRIPTION_2,
        DESCRIPTION_3)
    VALUES ( EL /* ID */,
        'DESCRPTION_1 ' || EL /* DESCRIPTION_1 */,
        'DESCRPTION_2 ' || EL /* DESCRIPTION_2 */,
        'DESCRPTION_3 ' || EL /* DESCRIPTION_3 */ );
   END LOOP;
   COMMIT;
dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );
END;

W poniższym przykładzie wstawiamy wielokrotnie do pustej tabeli po sto tysięcy rekordów z wykorzystaniem sekwencji z różnym parametrem buforowania.
DECLARE.
vn_Start NUMBER;
BEGIN
FOR EL_OUT IN (SELECT COLUMN_VALUE AS CACHE_SIZE,
                    DECODE( COLUMN_VALUE, 1 , 'NOCACHE', 'CACHE ' || COLUMN_VALUE)   AS CACHE_DDL
                 FROM TABLE(SYS.ODCINUMBERLIST(1, 2,5,10, 20, 50, 100, 500, 1000, 5000, 10000)))
LOOP
    BEGIN
        EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_START';
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;
EXECUTE IMMEDIATE 'TRUNCATE TABLE TBL_TEST';
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_START START WITH 1 ' || EL_OUT.CACHE_DDL;
vn_Start := dbms_utility.get_time;
    FOR EL_IN IN 1..100000
       LOOP
           INSERT INTO TBL_TEST (
            ID, DESCRIPTION_1, DESCRIPTION_2,
            DESCRIPTION_3)
        VALUES ( SEQ_START.nextval /* ID */,
            'DESCRPTION_1 ' || EL_IN /* DESCRIPTION_1 */,
            'DESCRPTION_2 ' || EL_IN /* DESCRIPTION_2 */,
            'DESCRPTION_3 ' || EL_IN /* DESCRIPTION_3 */ );
       END LOOP;
    COMMIT;
dbms_output.put_line( EL_OUT.CACHE_DDL || ' - ' || ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );
END LOOP;
END;

Wyniki:
NOCACHE - 26,9 sekund...
CACHE 2 - 17,82 sekund...
CACHE 5 - 10,82 sekund...
CACHE 10 - 8,78 sekund...
CACHE 20 - 7,79 sekund...
CACHE 50 - 7,17 sekund...
CACHE 100 - 7,01 sekund...
CACHE 500 - 6,94 sekund...
CACHE 1000 - 6,95 sekund...
CACHE 5000 - 6,87 sekund...
CACHE 10000 - 6,56 sekund...

To jest przykład na to, jak mało ważne parametry mogą istotnie zmniejszyć wydajność,zwłaszcza dla masowego przetwarzania...

poniedziałek, 12 października 2015

Algorytmy obliczeniowe - walka wydajności z przewidywalnością

Napiszmy krótki kawałek kodu, który wykonuje niezbyt sensowne obliczenia korzystając z wbudowanych funkcji matematycznych. Kod zostanie wykonany na serwerze Exadata

DECLARE
vn_Pom NUMBER :=0;
vn_Start NUMBER;
BEGIN
vn_Start := dbms_utility.get_time;
vn_Pom := 1.2;
FOR EL IN 1..100000
   LOOP
        vn_Pom := COS(SIN(vn_Pom));
        vn_Pom :=1+ vn_Pom + POWER(2, vn_Pom);
        vn_Pom := LOG(2, ABS(vn_Pom) + 3);
   END LOOP;
dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );
END;

Średni czas wykonania dla trzech przebiegów to 9,02 sekundy.

Zmieńmy teraz jedną podkreśloną linię w powyższym anonimowym bloku kodu.
vn_Pom BINARY_DOUBLE :=0;

Średni czas wykonania zmniejszył się do 0,06 sekundy.

Skąd wynika ta zmiana??

W pierwszym przypadku typ NUMBER jest uniwersalny i niezależny od maszyny. Jest obsługiwany za pomocą dołączonym bibliotek napisanych w C i na każdym serwerze dla jednakowych operacji powinny być zwracane jednakowe wartości. Bardzo czasochłonne są funkcje matematyczne wykorzystujące prawdopodobnie rozwinięcie w szereg funkcji trygonometrycznych, co jest dość zasobożerne. Ale zawsze otrzymamy ten sam wynik !!! Jest to cecha niezwykle istotna w przypadku rozproszonych systemów transakcyjnych korzystających z niejednorodnej infrastruktury sprzętowej. Jest to emulacja niezależna od typu serwera.

Natomiast w drugim przypadku deklaracja zmiennej vn_Pom BINARY_DOUBLE :=0 informuje serwer, że obliczenia będą przeprowadzone przy wykorzystaniu koprocesora matematycznego w obrębie procesora serwera. To odpowiednik typu double w C przy wykorzystaniu koprocesora. Jet to rozwiązanie bardzo szybkie, tym niemniej zachodzi uzasadniona obawa, ze dla rożnych platform sprzętowych, wyniki obliczeń będą się różnić ostatnimi cyframi. Należy tez zauważyć, że koprocesory nie obsługują wartości NULL, która to jest wysokopoziomowym wynalazkiem, stąd przymusowa inicjalizacja zmiennej. Ma to też swoją zaletę - taki kod na etapie testowania zawsze będzie generować wyjątki, kiedy napotka wartość NULL i ułatwi wykrycie miejsc, skąd przechodzą wartości niezainicjalizowane..

piątek, 23 maja 2014

Czy to numer z cegły - wyrażenia regularne w praktyce c.d.

Dawno dawno temu, kiedy monitory komputerów były bursztynowe, myszki miały ogonki i wkroczyła do Polski sieć NMT - posiadanie wielkiej cegłówki przy pasku to był wówczas szczyt snobizmu. Z czasem jednak zwiększyła się liczba sieci , prefiksów...
Ze względu na coraz większą liczbę prefiksów nie jest już łatwo ustalić czy dany nr jest komórkowy czy też stacjonarny. Posiłkując się Wikipedią postanowiłem napisać zgrabną funkcję zwracającą 1 jeśli podamy jako argument nr komórkowy lub zero w przeciwnym przypadku.
Założenia są następujące:

obsługujemy tylko polskie numery telefoniczne
wszystkie znaki które nie są cyframi usuwane są w trakcie walidacji
wiodące zera, liczba 48 i dowolna liczba zera po numerze kierunkowym Polski są usuwane
sprawdzam, czy otrzymany łańcuch zawiera dokładnie 9 znaków i rozpoczyna sie od którejś z cyfr 5,6,7 lub 8
zgodnie z danymi z Wikipedii sprawdzam czy dwa pierwsze znaki zgadzają się z dwoma pierwszymi znakami prefiksów z Wikipedii (dlaczego dwa, a nie trzy znaki - proponuję pogłówkować Czytelnikom)

Osiągnięte rozwiązanie jest bardzo łatwo rozszerzalne o kolejne prefiksy i w sposób nadzwyczaj zwięzły opisuje wiele prefiksów. Wyrażenie. 5[0,1,3,7] oznacza prefiksy 50, 51, 53, 57.
Dobrym pomysłem będzie zaszycie jako parametr wyrażenia regularnego do sprawdzania prefiksów..

CREATE OR REPLACE FUNCTION Fdb_IsGSMNumber( pc_Arg VARCHAR2) RETURN NUMBER
AS
   vc_Bufor VARCHAR2(15 CHAR);
   BEGIN
      vc_Bufor := REGEXP_REPLACE( REGEXP_REPLACE(pc_Arg, '[^[:digit:]]', ''), '^0*(48){0,1}0*', '');
      IF LENGTH(vc_Bufor) <> 9 OR SUBSTR( vc_Bufor, 1,1) NOT BETWEEN '5' AND '8' THEN
         RETURN 0;
      END IF;
      IF REGEXP_SUBSTR ( vc_Bufor, '^(5[0,1,3,7]|6[0,6,9]|7[2,3,8,9]|88)') IS NULL THEN
         RETURN 0;
      END IF;
   RETURN 1;
   EXCEPTION WHEN OTHERS THEN
      RETURN 0;
END;
/

sobota, 7 grudnia 2013

Wyszukiwanie z wykorzystaniem wyrażeń regularnych

Wyszukiwanie wzorów określonych za pomocą wyrażeń regularnych umożliwia znaczące uproszczenie algorytmów do przetwarzania tekstów. Różnica polega na tym, że definiujemy wzorzec wyszukiwania, testujemy i ciągle udoskonalamy..   Podczas rozpoczynania pracy z wyrażeniami regularnymi, okazuje się, że największym problemem jest prawidłowe zdefiniowanie wzorca.
Przeanalizujmy następujący przykład - w tekście wyszukujemy kod pocztowy..
Wyrażenie opisujące kod pocztowy z myślnikiem ma następującą postać:
[[:digit:]]{2}-[[:digit:]]{3} - to oznacza wzorzec składający się z dwóch dowolnych cyferek, znaku myślnika i trzech cyferek. Ale czy działa on poprawnie??
SELECT REGEXP_SUBSTR('Mój kod pocztowy to 02-768', '[[:digit:]]{2}-[[:digit:]]{3}',1 ,1) FROM DUAL --to polecenie wykona się poprawnie

SELECT REGEXP_SUBSTR('Mój telefon to to 502-559-373', '[[:digit:]]{2}-[[:digit:]]{3}',1 ,1) FROM DUAL --to polecenie zwróci 02-559 i bedzie przeszukiwać nr telefonów, nr kont.
Jeśli mamy wejściowy plik z adresami, to z reguły powinno wystarczyć. W innych przypadkach proponuję zastosowanie poniższego wzorca
(^|[[:blank:]])[[:digit:]]{2}-[[:digit:]]{3}($|[[:blank:]])
(^|[[:blank:]]) to podwyrażenie oznacza, że wzorzec powinien zaczynać się od początku przeszukiwanego tekstu lub powinien być poprzedzony znakiem niewidocznym, np. spacją
($|[[:blank:]])- to podwyrażenie oznacza, że wzorzec ma się kończyć na ostatnim znaku przeszukiwanego tekstu lub znakiem niewidocznym np. spacją

SELECT REGEXP_SUBSTR('Mój telefon to to 502-559-373', '(^|[[:blank:]])[[:digit:]]{2}-[[:digit:]]{3}($|[[:blank:]])',1 ,1) FROM DUAL --teraz wyrażenie zadziała poprawnie i nic nie zwróci, dobrze są opisane warunki brzegowe
Często nie da się stworzyć ogólnego wzorca dla poszukiwanych danych, ale można znacznie uprościć mechanizmy wyszukiwania. Dużo łatwiej jest modyfikować wyrażenia regularne niż kod do analizy zawartości tekstu.

Poniżej inne ciekawe przykłady:
Wyszukiwanie adresu poczty elektroniczne:
SELECT REGEXP_SUBSTR( 'Mój mail to szkaradnik@gmail.com', '[[:alpha:]]([[:alnum:]]|\.){0,30}[[:alnum:]]@[a-zA-Z0-9._%-]+\.[[:alpha:]]{2,4}') FROM DUAL;

SELECT REGEXP_SUBSTR( 'Nie jestem posiadaczem adresu Kazimierz.Szpyt@gmail.com.pl - bo nie istnieje', '[[:alpha:]]([[:alnum:]]|\.){0,30}[[:alnum:]]@[a-zA-Z0-9._%-]+\.[[:alpha:]]{2,4}') FROM DUAL;

Wyszukiwanie inicjałów z nazwiskiem, przy założeniu, że po inicjałach imion sa kropki, litery inicjałów i pierwsza litera nazwiska są duże:
SELECT REGEXP_SUBSTR( 'To nie sa moje inicjały to A.   D. C.Kowalski :) ', '([[:upper:]]{1}.{1}[[:blank:]]*)+[[:upper:]]{1}[[:lower:]]+', 1,1) FROM DUAL;
Należy zwrócić uwagę, że powyższe wyrażenie nie działa poprawnie dla np. nazwisk dwuczłonowych

Wykrywanie podstringów w formacie DDDDSDDSDD, DDDDSDSD, DDDDSDSDD lub DDDDSDDSD, gdzie D to cyfra, a S to separator w postaci któregoś z ukośników lub myślnika
SELECT REGEXP_SUBSTR('Co to za data 2000\11/23','[[:digit:]]{4}((\\)|-|/)[[:digit:]]{1,2}((\\)|-|/)[[:digit:]]{1,2}', 1, 1 ) FROM DUAL;
To wyrażenie jest pomocne w wykrywaniu wyrażeń będących datami.

Określanie, czy dany tekst jest o charakterze politycznym (zwracanie liczby wystąpień nazw partii w tekście)
SELECT REGEXP_COUNT( 'POlityczni POpaprańcy POpierający POlityczną POprawność i POdnoszący POdatki', 'PO|PiS|SLD|PSL', 1 ) FROM DUAL;

Wyszukiwanie w danym tekście kolejnych zdań(niepustych tekstów rozdzielonych kropkami)
DECLARE
vc_Wyrazenie VARCHAR2(75 CHAR) := 'Ala ma kota, psa i papugę.Ala ma także X-Box-a. Ala ma mnóstwo lalek.';
vn_Liczba NUMBER;
BEGIN
vn_Liczba := REGEXP_COUNT ( vc_Wyrazenie,'[^(\.)]+');
   FOR el IN 1.. vn_Liczba
   LOOP
     DBMS_OUTPUT.PUT_LINE( REGEXP_SUBSTR ( vc_Wyrazenie,'[^(\.)]+', 1, el));
   END LOOP;
END;
Zauważmy, że powyższy kod ma następujące ograniczenia:

zwracane zdania nie kończą się kropką
kod nie radzi sobie ze skrótowcami zawierającymi kropki, z inicjałami, do obróbki tekstów biorokratycznych sie nie nadaje, ale z beletrystyką poradzi sobie całkiem dobrze

Instrukcja REGEXP_SUBSTR nadaje się do parsowania tekstu z separatorami ale pod warunkiem, że wszystkie tokeny są niepuste lub pozycja poszczególnego tokena jest bez znaczenia.
Poprawnie zostanie przeprocesowany tekst:
Kazimierz|Szpyt|śpioch|łasuch|
natomiast poniższy tekst zostanie rozbity na tokeny, ale bez pustych tokenów
Kazimierz||Szpyt||||śpioch|łasuch|

środa, 28 sierpnia 2013

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..