Dlaczego polecenie SELECT powoduje zapisy dyskowe? Delayed Block Cleanout!

Przyjrzyjmy się ciekawemu zjawisku, które przejawia się tym, że czasami wykonanie zapytania SELECT powoduje modyfikacje bloków dyskowych oraz zapisy danych powtórzenia (Redo). W konsekwencji , wydajność polecenia SELECT staje się nieakceptowalna.

W poniższym eksperymencie posługuję się tabelą zawierającą milion rekordów, zajmująca 130MB. W pierwszym kroku, na tabeli wykonuję polecenie UPDATE, które modyfikuje co piąty rekord. Zagregowane statystyki sesji słusznie ukazują wykonanie modyfikacji bloków danych oraz wygenerowanie danych powtórzenia. Zatwierdzam transakcję, kończę sesję. W drugim kroku, rozpoczynam nową sesję i wykonuję proste zapytanie SELECT do tej samej tabeli. Uwaga! Tym razem zagregowane statystyki sesji pokazują, że zmodyfikowano ponad 10 tysięcy bloków danych i wygenerowano ok. 750KB danych powtórzenia!! Dlaczego SELECT powoduje zapisy dyskowe?? SELECT przecież powinien wyłącznie odczytywać dane! Co więcej, ponowne wykonanie takiego samego polecenia SELECT nie powoduje już generowania dodatkowych zapisów.

Byliśmy właśnie świadkami ciekawego mechanizmu wydajnościowego stosowanego przez klika dotychczasowych wersji serwera Oracle Database: Delayed Block Cleanout. Mechanizm ten wiąże się z realizacją operacji COMMIT (zatwierdzenia transakcji). Pamiętamy, że zatwierdzenie transakcji powinno powodować zdjęcie wszystkich założonych blokad na rekordach i tabelach (algorytm 2PL). Wiemy też, że informacje o blokadach i transakcjach posiadających te blokady są zapisywane w nagłówkach bloków danych zawierających rekordy będące przedmiotem blokady. To oznacza, że zdejmowanie blokad pociąga za sobą modyfikacje bloków (mogą znajdować się już nawet poza Buffer Cache), a tym samym generowanie danych powtórzenia (Redo). Aby zaoszczędzić na czasie wykonywania operacji COMMIT, serwer bazy danych „formalnie” zdejmuje wszystkie blokady transakcyjne, ale nie dokonuje stosownego wyczyszczenia nagłówków bloków danych. Za takie czyszczenie (Block Cleanout) odpowiada najbliższa operacja SQL, która odczytuje blok np. z dysku (statystyka „cleanout – number of ktugct calls”). Efektem Delayed Block Cleanout jest sprawniejsze wykonywanie operacji COMMIT ale jednocześnie pogorszenie wydajności najbliższej operacji odczytującej bloki tabeli. Jeśli chcielibyśmy uniknąć negatywnych konsekwencji Delayed Block Cleanout, to po zatwierdzaniu szczególnie długich transakcji należałoby wykonać sztuczne zapytanie SELECT dokonujące pełnego odczytu zmodyfikowanej tabeli, a tym samym „sprzątające” bloki po transakcji.

SQL> connect hr
SQL> select name, value from v$mystat natural join v$statname
where name in ('cleanout – number of ktugct calls', 'redo size','db block changes');
NAME                              VALUE
--------------------------------- ----------
db block changes                  0
redo size                         0
cleanout - number of ktugct calls 0
SQL> update test_dbc set id=id where mod(id,5)=0;
...
SQL> commit;
SQL> select name, value from v$mystat natural join v$statname
where name in ('cleanout – number of ktugct calls', 'redo size','db block changes');
NAME                              VALUE
--------------------------------- ----------
db block changes                  445399
redo size                         67331540
cleanout - number of ktugct calls 0
SQL> connect hr
SQL> select name, value from v$mystat natural join v$statname
where name in ('cleanout – number of ktugct calls', 'redo size','db block changes');
NAME                              VALUE
--------------------------------- ----------
db block changes                  0
redo size                         0
cleanout - number of ktugct calls 0
SQL> select count(*) from test_dbc;
...
SQL> select name, value from v$mystat natural join v$statname
where name in ('cleanout – number of ktugct calls', 'redo size','db block changes');
NAME                              VALUE
--------------------------------- ----------
db block changes                  10425
redo size                         750644
cleanout - number of ktugct calls 10425
SQL> select count(*) from test_dbc;
...
SQL> select name, value from v$mystat natural join v$statname
where name in ('cleanout – number of ktugct calls', 'redo size','db block changes');
NAME                              VALUE
--------------------------------- ----------
db block changes                  10425
redo size                         750644
cleanout - number of ktugct calls 10425