Przyjrzyjmy się dziś dość popularnemu problemowi wydajności masowej operacji UPDATE dokonującej modyfikacji bardzo dużej liczby rekordów. Spotykają się z nim najczęściej programiści implementujący zadania wsadowe. Dla celów demonstracji posłużymy się tabelą UPD_TEST o następującej strukturze:
create table upd_test (c1 number(10), c2 char(1018));
Rozmiar kolumny C2 został tak dobrany, aby średnia długość rekordu oscylowała w okolicach 1 kB. Na początek tabela zostanie wypełniona 100 000 nieistotnych rekordów, dzięki czemu jej rozmiar wyniesie 100 MB.
begin
for i in 1..100000 loop
insert into upd_test values (i, rpad('X',1018,'X'));
end loop;
end;
Wykonamy teraz polecenie UPDATE, które przeprowadzi modyfikację wszystkich rekordów tabeli UPD_TEST – w naszym przykładzie modyfikacja polega na zmianie ostatniej litery tekstu w kolumnie C2. Przy tej okazji zmierzymy czas wykonania i niektóre statystyki wydajnościowe (np. za pomocą v$mystat).
update upd_test set c2=substr(c2,1,1017)||'Y';
- DB Time: 16.28 s
- Undo change vector size: 116 MB
- Redo size: 250MB
W tym przypadku tabela jest niewielka, więc czas 16.28 s pewnie nie powoduje większego niepokoju jednak zastanówmy się, z czego on konkretnie wynika. Otóż modyfikacja każdego rekordu powoduje: (1) zapis modyfikacji do dziennika powtórzeń (Redo), (2) zapis wcześniejszego obrazu danych do bloku wycofania (Undo), (3) zapis zmiany bloku wycofania do dziennika powtórzeń (Redo dla Undo), wreszcie (4) zapis nowego obrazu rekordu do bloku tabeli. W rezultacie, oprócz zapisu ok. 100MB zmodyfikowanych danych, serwer bazy danych Oracle Database musi dodatkowo zapisać podobną ilość danych wycofania i do tego zapisać około dwukrotność tych danych do dziennika powtórzeń. Przez serwer „przechodzi” więc ok. 400MB danych (z dokładnych pomiarów wynika, że nawet ponad 450 MB), które ostatecznie trafiają na dysk (pliki danych, przestrzeń wycofania, dzienniki powtórzeń). Stąd taka wydajność…
Czy istnieje sposób na szybsze wykonanie takiej modyfikacji, z pominięciem wszystkich tych zapisów do dziennika powtórzeń i przestrzeni wycofania? Oczywiście! Zamiast modyfikować rekordy „w miejscu”, spróbujmy przepisać je do nowej tabeli, wykonując stosowne modyfikacje danych „w locie”. Czy to coś zmieni? Jak najbardziej, gdyż skorzystamy wtedy z polecenia CTAS (Create Table As Select), które stosuje ścieżkę bezpośrednią zapisu danych (Direct Path), a ta znacząco redukuje zapisy typu Undo i Redo. Naturalnie na końcu nasze zmodyfikowane dane znajdują się w innej tabeli, więc konieczne będzie teraz usunięcie starej tabeli, zmiana nazwy nowej na starą i ewentualnie przywrócenie wszystkich indeksów i wyzwalaczy.
create table upd_test_dup
as select c1, substr(c2,1,1017)||'Y' as c2 from upd_test;
- DB Time: 2.74 s
- Undo change vector size: 42 kB
- Redo size: 272 kB
Jest szybciej, prawda…? Imponująco wyglądają ilości danych wycofania i powtórzenia, mierzone w KILOBAJTACH! Jakim jednak cudem serwer zapisał dane wycofania dla 100 000 rekordów za pomocą 42 kilobajtów (4 bity na rekord)??? To skutek optymalizacji rekordów Undo, jaką Oracle potrafi zastosować dla poleceń INSERT oraz INSERT posługujących się ścieżką bezpośrednią (a nie potrafi dla UPDATE i DELETE) – raz wspomniał o tym Jonathan Lewis (https://community.oracle.com/thread/2506291?tstart=0).
Efekt wydajnościowy powyższego polecenia będzie zależny od trybu pracy serwera bazy danych – ARCHIVELOG/NOARCHIVELOG. Tylko w trybie NOARCHIVELOG ścieżka bezpośrednia pomija zapisy do dziennika powtórzeń. Tak właśnie było w naszym powyższym przykładzie. natomiast w trybie ARCHIVELOG zaobserwowałem następujące wskaźniki wydajnościowe:
- DB Time: 4.67 s
- Undo change vector size: 42 kB
- Redo size: 112 MB
Pięćset razy więcej danych Redo w porównaniu z CTAS w trybie NOARCHIVELOG…, ale nadal jest to ponad połowa mniej niż dla pierwszego polecenia UPDATE. No i czas wykonania czterokrotnie lepszy.
Dla pełnego obrazu rzućmy jeszcze okiem na wskaźniki wydajnościowe polecenia DELETE, które usunęłoby wszystkie rekordy tabeli testowej.
delete from upd_test;
- Undo change vector size: 108 MB
- Redo size: 127 MB
Z czego wynikają te wartości? Z usunięciem rekordu wiąże się: (1) zapis operacji usunięcia w dzienniku powtórzeń (Redo), (2) zapis obrazu usuwanego rekordu do bloku wycofania (Undo), (3) zapis zmiany bloku wycofania do dziennika powtórzeń (Redo dla Undo), wreszcie (4) usunięcie rekordu z bloku tabeli. Usuwane rekordy mają objętość 100MB – tyle danych znajdzie się w przestrzeni wycofania i w dzienniku powtórzeń. Dodatkowo, do dziennika powtórzeń trafi opis operacji usuwania rekordów, ale jest on stosunkowo niewielki (zawiera tylko wskazania usuwanych rekordów).
Wniosek końcowy – ilekroć zamierzamy zmodyfikować lub usunąć bardzo duże liczby rekordów, sprawdźmy, czy o wiele bardziej wydajnym rozwiązaniem nie jest zastąpienie operacji UPDATE/DELETE operacją INSERT (CTAS). Zwracajmy przy tym uwagę na ilość danych Undo i Redo, których zapis musi towarzyszyć operacjom modyfikacji danych, bo przekłada się ona bezpośrednio zarówno na wydajność konkretnej operacji, jak i całej instancji.