wtorek, 26 czerwca 2012

Optymalizacja PLSQL: Operacje masowe

Link: Do pobrania kod PL/SQL który jest wykorzystywany w tym artykule


 Na czym polegają operacje masowe i jaka jest korzyść z ich stosowania? Najlepiej będzie pokazać to na przykładzie. Na co dzień pracujemy z dużymi zbiorami danych, a więc każda korzyść czasowa w ich przetwarzaniu będzie dla nas znacząca. Przygotowuję więc całkiem sporą tabelkę będącą kopią tabeli employees z przykładowego schematu HR. W dalszym etapie 14-stokrotnie dubluję jej wielkość. W efekcie tabelka ma ponad milion siedemset tysięcy wierszy, mamy więc na czym pracować.



Chcąc przetwarzać takie dane w PL/SQL najpierw musimy je wczytać, robimy to przy pomocy pętli kursorowej. W tym przykładzie ładuję wynik zapytania do tablicy zmiennych typu wierszowego odpowiadających konstrukcyjnie wierszom tabeli bigstuff (to właśnie z niej czytamy dane, wiersze kursora będą więc miały taką samą konstrukcję). Przed i po załadowaniu danych wypisuję na konsolę aktualny czas.





Po zakończeniu wykonywania skryptu na konsoli wyświetlone zostały wyniki. Widzimy że ładowanie prawie 2 milionów wierszy trwało 26 sekund.



Teraz nieco zmodyfikuję kod:


Jak widać zamiast pętli kursorowej (która przy okazji niejawnie otwiera i zamyka kursor) otwieram i zamykam kursor jawnie, ale zamiast fetchowania pojedynczych wierszy, fetchuję naraz cały kursor. W dodatku nie ma tu żadnej pętli... Konstrukcja konstrukcją, ale sprawdźmy efekty:





Czas ładowania spadł z 26 do 5 sekund. Czyli aktualnie czas ładowania to 1/6 poprzedniego czasu ładowania. Ale co się właściwie stało? Czy wiesz co się dzieje podczas otwarcia kursora? Wbrew powszechnemu przekonaniu nie są wtedy czytane dane. Na tym etapie wymyślany jest plan wykonania zapytania. Możesz to sprawdzić np. jedynie otwierając i zamykając kursor z zapytaniem na jakiejś wielkiej tabeli. Dane są czytane dopiero podczas operacji FETCH. Zauważ że sama konstrukcja pętli kursorowej powoduje FETCH pojedynczych wierszy. Co obrót pętli to pobranie jednego wiersza. Co z tego wynika? Następuje ciągła komunikacja pomiędzy bazą a PLSQL. Każdy kolejny wiersz musi zostać przeczytany i załadowany do tablicy, następnie kolejny wiersz jest czytany … itd. Długi czas wykonania wynika właśnie z tej ciągłej komunikacji. Spójrzmy teraz na kod w którym używam instrukcji BULK COLLECT. Nie ma tam żadnej pętli. Tym razem pobranie danych działa w ten sposób, że czytany i wrzucany na stos jest cały wynik zapytania. Następnie jako całość przekazywany do tablicy. Nie następuje ciągła komunikacja, stąd zysk czasowy.

Za dobrze by jednak było, gdyby nie wiązały się z tym pewne problemy. Jak można wywnioskować ze sposobu działania operacji masowych, cały wynik zapytania musi został załadowany do pamięci na raz. To oznacza że narażamy się na przepełnienie pamięci w sytuacji gdyby wielkość wyniku zapytania przerósł dostępną pamięć. Czyż nie? Sprawdźmy to. Jeszcze czterokrotnie powiększę wielkość tabeli z której czytane są dane:






W tej chwili tabela ma 7 milionów wierszy... Przy próbie powtórzenia wcześniejszej operacji ładowania danych dostaję taki oto komunikat:



Co mogę na to poradzić? Mogę przetwarzać dane po kawałku. Np. po 500 tysięcy wierszy. Do tego służy dodatkowa klauzula limit 500000. Muszę tylko zadbać by załadować cały wynik, dodaję więc również pętlę która będzie czytała dane po kawałku aż się skończą:









Oczywiście nie może się obyć bez problemów :) System nie może kolekcjonować wszystkiego naraz do jednej tablicy ponieważ ponownie doprowadziłoby to do przepełnienia pamięci. Rozwiązano to w ten sposób, że każda kolejna porcja nadpisuje poprzednią. W kodzie powyżej wypisywałem na ekran ilość niepustych elementów w tablicy przy użyciu tab.count. To mi daje obraz na temat tego ile znajduje się elementów w tablicy po każdym fetchu z limitem. Wynik możemy obejrzeć poniżej:





Z tego wynika że takie załadowane dane trzeba od razu przetwarzać, ponieważ przy kolejnym fetchu zostaną nadpisane.
Spostrzegawczy zauważą że czas przetwarzania wynosi znowu 26 sekund... Pamiętajcie jednak że teraz przetwarzamy nie 2 a 7 milionów wierszy ;)
Operacje masowe możemy również wykorzystywać przy operacjach DML. W końcu kiedy już skończymy przetwarzać dane, warto je gdzieś wrzucić.

Tworzę pustą tabelę na wyniki:




Do wrzucania danych zastosuję poprzednio załadowane dane, oczywiście mogą to być jakiekolwiek inne dane załadowane do tablicy. Najpierw przeprowadzam test z użyciem tradycyjnej metody tj. iteracji po tablicy i insertów wiersz po wierszu. Tym razem również przed całą operacją i po jej zakończeniu wypisuję czas na ekranie.





Całe załadowanie 7 milionów wierszy, a następnie ich wstawienie do tabeli trwało 7 minut i 13 sekund.








Ponownie modyfikuję kod :





Pojawiła się nowa instrukcja : FORALL. Działa podobnie jak BULK COLLECT przy ładowaniu danych do tablicy, tylko w drugą stronę. Cała tablica jest jednocześnie przekazywana do insertu. Ale jak wygląda zysk czasowy?



Zamiast 7 minut i 14 sekund mamy 2 minuty i 8 sekund. W sumie przy użyciu operacji masowych zyskaliśmy
  • przy ładowaniu danych do tablicy 1/6 czasu
  • przy wrzucaniu danych do bazy ok 1/3 czasu

Razem daje nam to całkiem przyzwoity wynik :)












Brak komentarzy:

Prześlij komentarz