FAST REFRESH w widokach zmaterializowanych – nie taki szybki ?!
Witajcie,
Po ostatnim wybitnie nietechnicznym wpisie (musiałem się pochwalić 
), teraz wracam już do tego co lubię opisywać najbardziej.
Zajmuję się właśnie tworzeniem kilku widoków zmaterializowanych (w skrócie będę je oznaczał jako MV – materialized view). Są to widoki oparte na prostych zapytaniach zawierających jedynie złączenia (bez agregacji). Wydawałoby się, że zadanie jest trywialne. W rzeczywistości można jednak napotkać na bardzo paskudny problem.
Krótkie przedstawienie sytuacji:
1. MV jest utworzona w oparciu o zapytanie wykorzystujące wielokrotne łączenie tej samej tabeli (SELF JOIN) – ze względu na występowanie relacji parent-child w tabeli.
2. Tabela nie jest w ciągu dnia zbyt często aktualizowana, główne zmiany zachodzą w trakcie nocnego przeładowania.
3. Ilość wierszy w tabeli to ok 5 mln.
4. MV umożliwia odświeżanie w trybie FAST (sprawdzone wcześniej przy użyciu explain_mview i tabeli mv_capabilities_table).
5. Wszystkie potrzebne statystyki są zbierane przy pomocy pakietu dbms_stats.
Kod wykorzystany do stworzenia loga dla MV, oraz samej MV wygląda mniej więcej tak (zmieniłem nazwy tabel, i nie wypisuje wszystkich kolumn oraz warunków – nie jest to konieczne przy opisywaniu przypadku):
Log MV:
CREATE MATERIALIZED VIEW LOG ON table1
WITH ROWID, PRIMARY KEY,SEQUENCE ( some other fields)
INCLUDING NEW VALUES;
MV:
create materialized view test_mv
tablespace tablespace_test
build immediate
refresh fast on demand
as
select
A.rowid as a_rowid,
B.rowid as b_rowid,
C.rowid as c_rowid,
D.rowid as d_rowid,
E.rowid as e_rowid,
F.rowid as f_rowid,
some_other_fields
from table1 A
, table1 B
, table1 C
, table1 D
, table1 E
, table1 F
where conditions
Po utworzeniu odpowiednich obiektów rozpocząłem testowanie rozwiązania.
Sprawdzenie loga MV – 0 wierszy. Odświeżenie – bardzo szybkie.
Ponowne sprawdzenie loga MV po jakimś czasie – 2 wiersze. Odświeżenie – wolno…zbyt wolno.
Co się takiego stało? Przecież MV ma się odświeżać w trybie FAST, są założone wszystkie potrzebne indeksy (również na kolumnach z ROWID w samej MV – temat na osobną notkę), baza nie jest obciążona, nie występują żadne locki na tabelach źródłowych.
Odpowiedź na pytanie udało mi się znaleźć analizując raport wygenerowany przez TKPROF.
Wykonałem więc:
alter session set events '10046 trace name context forever, level 8';
alter session set tracefile_identifier='gd';
I w raporcie znalazłem sprawcę problemów. Oto on:
INSERT INTO test_mv
SELECT /*+ NO_MERGE("JV$") */
/*+ */ some_columns
FROM
( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
table1 "MAS$" WHERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM m_log$_table1
"MAS$" WHERE "MAS$".SNAPTIME$$ > :1)
) "JV$",
table1 "MAS$1",
table1 "MAS$2",
table1 "MAS$3",
table1 "MAS$4",
table1 "MAS$5"
WHERE conditions;
Kilka statystyk i plan zaptyania:
W planie zapytania można zauważyć bardzo paskudny TABLE ACCESS FULL na naszej źródłowej tabeli mającej 5 mln wierszy… Nic więc dziwnego, że odświeżanie przy małej ilości danych w logu MV było tak wolne.
Ale zaraz… Oracle użył w zapytaniu, które wykonuje FULL SCAN tabeli pewnego hinta – HASH_SJ – który zmienia plan zapytania. Wykonywany jest HASH JOIN SEMI, który w działaniu jest bardzo podobny do zwykłego HASH JOIN. Obydwie uczestniczące w złączeniu tabele skanowane są jedynie raz. Hint ten jest pomocny w momencie, gdy nie posiadamy indeksu na kolumnie występującej w warunku złączenia. W tym jednak przypadku, wykonywana jest konstrukcja WHERE ROWID IN … , która pozwala nam na zastosowanie dostępu do tabeli przez ROWID : TABLE ACCESS BY USER ROWID (nie mam dzisiaj dostępu do bazy, i nie pokażę wam planu zapytania – musicie uwierzyć na słowo ).
Oracle sam więc podjął decyzję o zastosowaniu HASH JOIN RIGHT SEMI i wymuszeniu w ten sposób wykonania pełnego skanu tabeli. Chwila googlowania i jak się okazało, nie jestem osamotniony. Rozwiązanie problemu jest bardzo ładnie przedstawione na blogu Alberto Dell’Ery : problem wolnego FAST REFRESH rozwiązany przez Alberto
Pierwsza rzecz, która rzuca się w oczy – problem dotyczy tylko niektórych wersji Oracla. Wśród nich jest jednak 10.2.0.4, na której próbuję wykonać to zadanie.
W moim przypadku zadziałało pierwsze rozwiązanie zaproponowane przez Alberto : użycie ukrytego parametru _mv_refresh_use_stats, i ustawienie go na wartość TRUE.
Drugą możliwością jest zebranie statystyk na pustym logu MV i zablokowanie tychże statystyk – tej opcji już nie musiałem próbować.
Po ustawieniu parametru _mv_refresh_use_stats na wartość TRUE, wykonałem ponownie test.
Wszystko odbyło się szybko, sprawnie i bezboleśnie. FAST REFRESH dla mojego widoku jest teraz naprawdę szybki.
Więcej informacji dotyczących wykorzystywanego ukrytego parametru można znaleźć na orafaq : mv_refresh_use_stats na orafaq
Na podanej stronie znajduje się też bardzo ładne zdanie napisane na czerwono… Warto o tym pamiętać 
Wniosek :
Przy projektowaniu i testowaniu MV opartych na złączeniach (bez agregacji) na wersjach Oracle od 9i wzwyż należy zwrócić szczególną uwagę na odświeżenie MV w trybie FAST. Jeżeli ilość danych do odświeżenia jest stosunkowo mała (np 2-3 wiersze w logu MV), tabela źródłowa jest dosyć duża (w moim przypadku 5 mln wierszy), i odświeżenie trwa zbyt długo (w moim przypadku “zbyt długo” to 10 sekund), warto skorzystać z TKPROF i przyjrzeć się, czy przy insertach Oracle nie używa hinta HASH_SJ. Jeżeli tak się dzieje, to należy skorzystać z dwóch rozwiązań opisanych w tym poście.
Dziękuję za uwagę,
Do następnego
