Do procedur PL/SQL możemy przekazać parametry jedną z dwóch metod:
BY VALUE - wartość domyślna dla parametrów IN OUT , OUT. Tworzona jest kopia przekazywanego parametru, na tej kopii przeprowadzane są wszelkie wyliczenia w procedurze, a na koniec wartość z powrotem jest kopiowana do oryginalnej zmiennej.
BY REFERENCE - jeśli użyjemy hintu NOCOPY - Oracle zamiast kopiować zmienną - użyje referencji/wskaźnika do oryginalnej zmiennej.
UWAGA:
W przypadku wystąpienia wyjątku w procedurze - wartość zmiennej przekazanej BY VALUE zostaje przywrócona do stanu sprzed uruchomienia procedury, zaś w przypadku zmiennej przekazanej BY REFERENCE - wartość zmiennej nie zostaje przywrócona i ma wartość taką, jak w momencie wystąpienia wyjątku.
Dobra praktyka jest taka, żeby używać hintu NOCOPY w celu optymalizacji użycia pamięci PGA. Ale jaki jest faktyczny hintu NOCOPY na użycie pamięci? Sprawdźmy.
Do testu zainspirował mnie wpis na blogu Oracle-base Dostosowałam test do swoich danych oraz dodałam kilka nowych przypadków testowych.
Testy przeprowadziłam na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production. Testy przeprowadziłam na schemacie HR. Wszystkie skrypty testowe można ściągnąć z linków na końcu wpisu.
Wyniki pierwszych testów zdawały się potwierdzać ogólną opinię na temat, w jaki sposób hint NOCOPY wpływa na użycie pamięci PGA. Przeprowadziłam dwa testy:
TEST DEFAULT
test, w którym przekazuję kolekcję globalną (zadeklarowaną w specyfikacji pakietu) do procedury za pomocą parametru IN OUT.
TEST NOCOPY
test, w którym przekazuję kolekcję globalną (zadeklarowaną w specyfikacji pakietu) do procedury za pomocą parametru IN OUT z dodatkowym hintem NOCOPY.
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
PGA_IN - użycie pamięci PGA w podprocedurze, do której przekazujemy parametrem wejściowym IN OUT kolekcję.
PGA - użycie pamięci PGA na koniec procesu.
Jak widać z wyników po dodaniu hintu NOCOPY - użycie PGA spadło drastycznie z 361758720 do 65536 , i to w momencie uruchomienia podprocedury jak i na koniec procesu. Czas przetwarzania także się skrócił z 57 do 0 setnych sekundy.
Jak do tej pory, wszystko zgodnie z planem.
Ale co jeśli nie chcemy korzystać z kolekcji globalnej/publicznej tylko chcemy zadeklarować tę kolekcję w procedurze? Czy to wpływanie na wyniki testów? W sumie czemu miałyby wpłynąć!
Ale sprawdźmy.
TEST LOCAL
kolekcja zadeklarowana w procedurze przekazywana jest do pod-procedury za pomocą parametru IN OUT.
TEST NOCOPY LOCAL
kolekcja zadeklarowana w procedurze przekazywana jest do pod-procedury za pomocą parametru IN OUT z hintem NOCOPY.
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL | 131072 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL | 131072 | 65536 | 2 | 535000 |
+-------------------------+-----------+-----------+------+--------+
Wow! Wyniki obu testów się niczym nie różnią!
Wprawdzie jeśli chodzi o użycie PGA - wartość jest dwa razy większa niż przy teście NOCOPY z kolekcją globalną, ale i tak jest to zdecydowanie mniej od testu z kolekcją globalną bez hintu (test DEFAULT) i to bez względu czy użyjemy hintu NOCOPY czy też nie!
Za to czas przetwarzania - 0.
Co ciekawe, użycie PGA w podprocesurze jest dwukrotnie wyższe niż na koniec procesu bez względu na sposób przekazywania kolekcji do procedury.
Robi się ciekawie. No to lecimy dalej!
Kolejne testy to:
TEST ROW BY ROW
przekazywany jest wiersz po wierszu (%rowtype) z kolekcji globalnej za pomocą parametru IN OUT.
TEST NOCOPY ROW BY ROW
przekazywany jest wiersz po wierszu (%rowtype) z kolekcji globalnej za pomocą parametru IN OUT NOCOPY
TEST LOCAL ROW BY ROW
przekazywany jest wiersz po wierszu (%rowtype) z kolekcji lokalnej za pomocą parametru IN OUT.
TEST LOCAL NOCOPY ROW BY ROW
przekazywany jest wiersz po wierszu (%rowtype) z kolekcji lokalnej za pomocą parametru IN OUT NOCOPY
+=========================+===========+===========+======+========+
| TEST | PGA_IN | PGA | TIME | COUNT |
+=========================+===========+===========+======+========+
| DEFAULT | 323026944 | 323026944 | 57 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY | 65536 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL | 131072 | 65536 | 0 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL | 131072 | 65536 | 2 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| ROW BY ROW | | 65536 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY ROW BY ROW | | 65536 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| LOCAL ROW BY ROW | | 131072 | 34 | 535000 |
+-------------------------+-----------+-----------+------+--------+
| NOCOPY LOCAL ROW BY ROW | | 131072 | 33 | 535000 |
+-------------------------+-----------+-----------+------+--------+
Wyniki są arcy ciekawe.
Testy ROW BY ROW na kolekcji globalnej ma takie same wyniki użycia PGA jak test kolekcji globalnej z hintem NOCOPY - bez względu czy użyjemy w procesie ROW BY ROW hintu NOCOPY czy nie! Za to czas przetwarzania znacznie się wydłużył, choć i tak jest to połowa czasu testu DEFAULT - kolekcja globalna bez hintu NOCOPY.
Przetwarzanie zaś ROW BY ROW na kolekcji lokalnej ma identyczne wyniki użycia pamięci PGA jak test z kolekcją lokalną (LOCAL i NOCOPY LOCAL), ale mierzony w podprocedurze! Jeśli zaś spojrzymy na czasy - to czasy są znacznie wydłużone, tak jak przetwarzanie ROW BY ROW kolekcji globalnej.
Podsumowując:
Jeśli w procesie używamy i przekazujemy kolekcję zadeklarowaną globalnie - to użycie hintu NOCOPY powinno znacznie zmniejszyć użycie pamięci PGA a także czas przetwarzania.
W pozostałych przypadkach nie ma znaczenia czy użyjemy hintu NOCOPY czy też nie. Większe znaczenie ma, czy deklarujemy kolekcję globalnie czy lokalnie oraz czy przekazujemy kolekcję do pod-procedury wiersz po wierszu - czy jako kolekcję.
Skrypty:
uruchamia wszystkie testy w osobnych sesjach.
Obiekty i pakiety potrzebne do testów.
Komentarze
Prześlij komentarz