Strona główna > Oracle Database > FAST REFRESH w widokach zmaterializowanych – nie taki szybki ?!

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 ;)

About these ads
  1. Brak komentarzy.
  1. No trackbacks yet.

Dodaj komentarz

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

WordPress.com Logo

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Twitter picture

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

Follow

Otrzymuj każdy nowy wpis na swoją skrzynkę e-mail.

%d bloggers like this: