пропозиції роботи
Kamil
Stawiarski
Oracle Certified
Master | Oracle ACE
Temat: library cache: mutex X i CURSOR_SHARING=FORCE
Witam,Chciałem się podzielić ciekawym przypadkiem, na który się natknąłem przy ostatniej optymalizacji.
Przełączyliśmy na 11.1.0.7 (testy wykonywane też na 11.2.0.2) CURSOR_SHARING=FORCE z myślą o testach Adaptive Cursor Sharing i ograniczeniu ilości twardych parsowań. Po kilku dniach instancja zaczęła wykazywać wysoką aktywność w klasie 'Concurrency' - po sprawdzeniu okazało się, że winowajcą jest wait event "library cache: mutex X". Po głębszym dochodzeniu znalazłem zapytanie, które powodowało nadmierne występowanie tego eventu a wyglądało ono mniej więcej tak:
SELECT *
FROM TABELA TR
WHERE TR.UPDATETIMESTAMP>=TIMESTAMP :"SYS_B_0"
Powyższe zapytanie miało około dwóch tysięcy potomnych kursorów a kolejne wykonywanie tego zapytania za każdym razem powoływało kolejny child-cursor. Wykluczyłem działanie Adaptive Cursor Sharing w tym przypadku, bo bind_sensitive i bind_aware były ustawione na 'N' a zresztą na przeszukiwanej kolumnie (typu TIMESTAMP) nie było histogramów.
Po przeszukaniu perspektywy v$sql_shared_cursor okazało się, że kursor nie jest współdzielony ze względu na HASH_MATCH_FAILED, co nie powinno się zdarzyć przy ustawieniu CURSOR_SHARING na FORCE i nie użyciu mechanizmu ACS.
Wykonałem to zapytanie przy ustawionym eventcie 10053 na levelu 1, żeby zobaczyć co tam optymalizator kombinuje pod spodem i zobaczyłem co następuje w fazie transformacji SQL:
WHERE SYS_EXTRACT_UTC("TR"."UPDATETIMESTAMP")>=SYS_EXTRACT_UTC(TIMESTAMP' 2012-02-15 16:08:00.679000000')
Okazało się, że transformator SQL olewał używanie zmiennych wiązanych i liczył plan zawsze z użyciem literału!!!
Kiedy zmieniłem predykat zapytania na:
WHERE TR.UPDATETIMESTAMP >= to_timestamp('2012-02-15 14:08:00.679','YYYY-MM-DD HH24:MI:SS.FF');
co daje takie samo znaczenie oraz wyniki jak poprzednia forma, można zobaczyć w trakcie transformacji co następuje:
WHERE SYS_EXTRACT_UTC("TR"."UPDATETIMESTAMP")>SYS_EXTRACT_UTC(TO_TIMESTAMP(:B1,:B2));
Widać tutaj ewidentnie, że tym razem transformator SQL użył zmiennej wiązanej. Przy takim zapisie nie pojawia się również nadmierna ilość kursorów potomnych - ergo wszystko działa jak powinno.
Konkluzja:
Nie znalazłem na Metalinku żadnego zarejestrowanego bug'a ale może to jest feature a nie bug ;P
Tak czy inaczej mechanizm, który miał w założeniu zmniejszyć ilość twardych parsowań, powoduje ustawiczne twarde parsowania przy użyciu jednej z dwóch, teoretycznie tożsamych klauzul WHERE dla typu danych timestamp :)
Pozdrawiam,
Kamil.Kamil Stawiarski edytował(a) ten post dnia 17.02.12 o godzinie 10:03
