Czy kiedykolwiek zdarzyło Ci się trafić na problem, który wywołał u Ciebie uczucie jakby nocnego koszmaru, z którego nie możesz się obudzić? Ostatnio przejąłem stary kod PL/SQL, który ładował dość dużą ilość danych z jednej tabeli do drugiej z niewielką ilością transformacji. To był prosty przypadek: weź podzbiór danych z jednej tabeli i wrzuć do innej. Operacja ta była wykonywana w pętli, która iterowała po kilku tysiącach wierszy w zdefiniowanym kursorze:
DECLARE
CURSOR c_cur IS
SELECT id FROM table_t;
r_cur c_cur%ROWTYPE;
BEGIN
FOR r_cur IN c_cur LOOP
INSERT INTO destination_t
SELECT some_fields
FROM source_t s
WHERE s.id = r_cur.id;
END LOOP;
END;
Tabela SOURCE_T zawierała ok. 20 milionów wierszy ale na kolumnie ID utworzony był indeks, więc całość powinna wykonać się w przyzwoitym czasie. Zgodnie z otrzymaną instrukcją uruchomiłem procedurę. Wielkim zaskoczeniem był fakt, że na drugi dzień, po 30 godzinach od startu, wykonane było dopiero ok. 40% całej roboty! Coś tam zdecydowanie było nie tak. Sprawdziłem plan wykonania dla działającego zapytania i okazało się, że w każdej iteracji wykonywany jest skan po pełnej tabeli! Uruchomiłem zbieranie statystyk dla tabeli i indeksu, ale niczego to nie zmieniło. Co więcej, okazało się, że to samo zapytanie wycięte z PL/SQLa na zewnątrz dawało plan wykonania, w którym indeks był używany! Dlaczego więc w PL/SQLu indeks leżał odłogiem?!
Zrobiłem nieco refaktoryzacji, zoptymalizowałem procedurę (tabela SOURCE_T skanowana była 2 razy przy każdej iteracji, raz jak pokazałem wyżej, drugi raz dla SELECT DISTINCT w innym zapytaniu). Zaczęło działać to szybciej, ale przewidywany czas zakończenia nie był satysfakcjonujący… Co gorsza, full table scan wciąż tam był… Naprawdę zacząłem dostawać szału… W końcu wezwałem na pomoc Ducha Świętego i wreszcie znalazłem rozwiązanie!
Powodem problemu była różnica typów w klauzuli WHERE. Okazało się, że r_cur.id to liczba a SOURCE_T.id było tekstem! Index był więc utworzony dla tekstów! Niestety, optymalizator oraclowy sobie z tym nie poradził. Mała zmiana:
WHERE s.id = TO_CHAR(r_cur.id)
załatwiła sprawę.
Dlaczego jednak plan wykonania był inny poza PL/SQLem? Narzędzie do jego analizy traktowało r_cur.id jako stałą i zakładało zgodność typów z SOURCE_T.id. Jednak w boju optymalizator nie potrafił powtórzyć tego założenia.
Nie pamiętam bym kiedykolwiek tak się cieszył z uzyskania poniższego planu wykonania:
INSERT STATEMENT + TABLE ACCESS BY INDEX ROWID SOURCE_T ++ INDEX RANGE SCAN IND_SRC_ID
Więc lekcja na dzisiaj:
To powyższe prawdziwe jest także w innych przypadkach. Kiedyś jeszcze o tym napiszę. Kończąc, zamieszczam kilka wycinków kodu, które przydadzą się do wyciągania informacji o uruchomionym zapytaniu SQL.
Wyciąganie informacji o aktualnie uruchomionym w sesji zapytaniu:
SELECT SQ.SQL_TEXT, S.* FROM V$SESSION S, V$SQL SQ WHERE S.SQL_ID = SQ.SQL_ID AND LOWER(S.USERNAME) = 'username' AND s.SID= <session ID>;
Wyciąganie informacji o długo trwających operacjach (np. skanowanie tabeli):
SELECT * FROM V$SESSION_LONGOPS L WHERE L.SID = <sid> AND SOFAR <> TOTALWORK -- daje tylko aktualnie trwające operacje ORDER BY LAST_UPDATE_TIME DESC;
Plan wykonania dla aktualnie działającego zapytania:
SELECT ID,
PARENT_ID,
LPAD(' ', LEVEL - 1) || OPERATION || ' ' || OPTIONS OPERATION,
OBJECT_NAME
FROM (SELECT ID, PARENT_ID, OPERATION, OPTIONS, OBJECT_NAME
FROM V$SQL_PLAN SP, V$SESSION A, V$SQL B
WHERE SP.ADDRESS = B.ADDRESS
AND SP.HASH_VALUE = B.HASH_VALUE
AND SP.CHILD_NUMBER = B.CHILD_NUMBER
AND A.SQL_ADDRESS = B.ADDRESS
AND a.SID= <sid>
)
START WITH ID = 0
CONNECT BY PRIOR ID = PARENT_ID;
Lista ostatnio uruchamianych zapytań:
SELECT sq.sql_id,
sq.sql_fulltext,
sq.sql_text,
se.username,
sq.last_active_time
FROM v$sql sq, v$session se, v$open_cursor oc
WHERE sq.sql_id = oc.sql_id
AND se.saddr = oc.saddr
AND se.sid = oc.sid
AND LOWER(se.username) = 'username'
AND se.sid = <sid>
ORDER BY sq.last_active_time DESC;