Oracle SQL Tuning Advisor

SQL Tuning Advisor

W miejsce ręcznej optymalizacji zapytań SQL, można zastosować rozwiązania automatycznej optymalizacji dostarczane przez Oracle. Jednym z takich narzędzi jest SQL Tuning Advisor wprowadzony w Oracle 10g. Optymalizator kosztowy może działać w dwóch trybach:

• Normal mode
  
• Tuning mode
  

Normal Mode

Podczas zwykłego funkcjonowania, optymalizator kosztowy generuje dla zapytań plany wykonania. W tym trybie optymalizator ma narzucone ścisłe ograniczenia czasowe na działanie, zazwyczaj są to ułamki sekund. W tym czasie musi stworzyć jak najlepszy plan wykonania zapytania.

Tuning Mode

W trybie strojenia optymalizator wykonuje dodatkową analizę mającą na celu sprawdzenie czy plan wykonania zapytania wygenerowany w zwykłym trybie funkcjonowania można jeszcze ulepszyć. Wynikiem działania takiej dodatkowej analizy nie jest plan wykonania zapytania, a szereg proponowanych działań wraz z ich uzasadnieniem i szacowanymi korzyściami. Wywołany w trybie strojenia optymalizator nazywamy ATO (Automatic Tuning Optimizer). W trybie strojenia optymalizator może poświęcić nawet kilka minut na optymalizację zapytania. Ponieważ taka analiza również pochłania zasoby systemowe, ATO jest przeznaczony do wykorzystania dla złożonych i bardzo obciążających zapytań mających duże znaczenie dla całego systemu. Tryb strojenia jest włączany przez użytkownika dla wybranych przez niego zapytań.

Automatic Database Diagnostic Monitor (ADDM)

Poczynając od Oracle 10g, identyfikacją najbardziej obciążających system zapytań zajmuje się ADDM. Najbardziej obciążające zapytania to te zużywające najwięcej zasobów CPU, I/O i przestrzeni tymczasowej. Takie obciążające zapytania są dobrymi kandydatami do optymalizacji przez SQL Tuning Advisor. Otrzymuje on na wejściu zapytania wskazane przez ADDM i tworzy dla nich wskazówki optymalizacyjne wraz z uzasadnieniem, szacunkowymi korzyściami płynącymi z ich zastosowania i jeśli jest dostępna – komendą która wdroży daną wskazówkę (np. komendę tworzącą odpowiedni indeks). Użytkownik może akceptować wskazówki Advisor'a i w ten sposób optymalizować zapytania.

Sposób działania SQL Tuning Advisor'a

SQL Tuning Advisor jest jedynie kontrolerem procesu optymalizacji. Wywołuje on ATO (Optymalizator kosztowy działający w trybie strojenia) aby ten przeprowadził poniższe analizy:

• Analiza statystyk. ATO sprawdza obiekty biorące udział w zapytaniu pod kątem aktualności statystyk. Jeśli statystyki nie są aktualne, tworzy wskazówkę dotyczącą ich odświeżenia. Wykorzystując próbkowanie zbiera również dodatkowe informacje w celu dostarczenia brakujących lub niepoprawnych statystyk na wypadek gdyby wskazówki Advisor'a nie zostały zaimplementowane.
  
• Profilowanie SQL. Profil dla zapytania SQL jest tym , czym statystyki dla tabel. Bazując na kolejnych wykonaniach danego zapytania, ATO zbiera informacje na temat sposobu wykonania, i dzięki temu może lepiej dobrać podpowiedzi dla niego. Weryfikuje czy jego własne szacunki dotyczące wykonania zapytania pokrywają się z rzeczywistością. Profile SQL są formą indywidualnych dla zapytania informacji, statystyk i ustawień optymalizatora. ATO buduje profil zapytania SQL i sugeruje utworzenie go dla danego zapytania. Kiedy profil zostanie stworzony, będzie stosowany przez optymalizator kosztowy do tworzenia efektywniejszych planów wykonania danego zapytania w normalnym trybie działania optymalizatora. Dla użytkownika fakt korzystania z profilu SQL jest niezauważalny. Podczas budowania profilu, ATO testuje swoje własne wskazówki poprzez szacowanie obciążenia z nowymi ustawieniami, ale też częściowe realne wykonanie zapytania z użyciem nowych konfiguracji.
  
• Analiza ścieżek dostępu. ATO analizuje ścieżki dostępu w zapytaniu, i jeśli stworzenie indeksu poprawiłoby wydajność zapytania, rekomenduje jego utworzenie.
  
• Analiza struktury zapytania. Niekiedy sama struktura zapytania powoduje niską wydajność. W takich przypadkach ATO sugeruje zmianę zapytania. Wykrywa np. łączenia kartezjańskie, czy stosowanie UNION tam gdzie powinno się użyć UNION ALL.
  

Źródła zapytań podlegających optymalizacji przez ATO

Zapytania które będą analizowane i optymalizowane przez ATO mogą pochodzić z trzech źródeł:

• najbardziej obciążające zapytania zidentyfikowane przez ADDM
• zapytania SQL które znajdują się w cache'u
• wybrane zapytania podane przez użytkownika

W przypadku zapytać pochodzących z cache, lub zakwalifikowanych przez ADDM, użytkownik ma możliwość wybrania niektórych zapytań. Nie ma konieczności optymalizacji wszystkich.

Sposób użycia SQL Tuning Advisor'a

Aby przejść do STA, logujemy się do Enterprise Manager'a. Następnie klikamy link „Advisor Central” dostępny na dole strony:

Następnie wybieramy „SQL Advisors” w górnej lewej części strony:

Dalej wybieramy SQL Tuning Advisor:

Mamy tutaj możliwość wyboru źródła pochodzenia zapytań które mają podlegać optymalizacji:

Zapytania najbardziej obciążające

Po wyborze „Top Activity” na poprzednim ekranie, zobaczymy wykres obciążenia bazy na przestrzeni czasu, pod kątem zużycia różnego rodzaju zasobów.

W lewej dolnej części strony możemy wybrać spośród tych najbardziej obciążających, zapytania które chcemy optymalizować. Niekiedy tabela pozostaje pusta. Wystarczy wtedy odświeżyć stronę. Możemy podejrzeć treść zapytań klikając na link w kolumnie SQL ID:

Zaznaczamy je, upewniamy się że w selektorze „Actions” wybrane jest „Schedule SQL Tuning Advisor” i naciskamy „Go”:

Pojawi nam się ekran który służy do uruchomienia analizy i optymalizacji wybranego zapytania.

Mamy możliwość wybrania typu analizy spośród dwóch opcji:

• Limited. Jeśli wybierzemy tę opcję, nie zostanie utworzony profil SQL, ale za to analiza przebiega bardzo szybko.
  
• Comprehensive. Wykonywana jest pełna analiza, tworzony jest też profil SQL dla zapytania. Taki rodzaj analizy trwa jednak znacznie dłużej niż w przypadku opcji „Limited”.
  

Możemy też określić limit czasowy na analizę wszystkich wybranych do optymalizacji zapytań ( w oknie „Total Time Limit”), oraz limit jednostkowy dla pojedynczego zapytania. Niżej, w sekcji Schedule możemy wybrać czy analiza ma się rozpocząć teraz, czy też chcemy ją odłożyć na nieco później (np. wtedy kiedy baza będzie mniej eksploatowana). Po zatwierdzeniu przyciskiem „Submit” (znajdującym się w prawym górnym rogu), zobaczymy taki oto ekran:

Przez parę chwil, ATO będzie analizował nasze zapytania. Gdy skończy, zobaczymy listę proponowanych działań:

Dla niektórych podpowiedzi optymalizacyjnych będzie dodana opcja implementacji. O ile np. do podpowiedzi dotyczącej stworzenia indeksów czy odświeżenia statystyk ATO jest w stanie wygenerować komendę która od razu to wykona, o tyle do podpowiedzi np. dotyczących zmiany kształtu zapytania nie będzie to możliwe.

W prawym górnym rogu mamy przycisk „Show SQL”, który pozwoli nam podejrzeć treść komendy którą ATO nam sugeruje:

 

 

Wybrałem jedną z podpowiedzi i kliknąłem „Implement”. System przeszedł do okna umożliwiającego zastosowanie wskazówki – w tym przypadku odświeżenia statystyk tabel. Mamy tutaj też możliwość zastosowania wskazówki teraz, lub w innym terminie.

Przeprowadziłem jeszcze jeden test, tym razem wybierając nieco więcej zapytań. Proces analizy też trwał już dłużej.

Po zakończeniu pojawił się ekran z podsumowaniem statystyk analizy. Wybieram przycisk „Show all results”:

Przejdziemy wtedy do ekranu z propozycjami ATO:

Analiza zapytań historycznych

Przy użyciu ATO mamy też możliwość analizowania historycznych zapytań. Tym razem po przejściu do SQL Tuning Advisora link „Historical SQL”:

 

Zobaczymy listę zapytań które nadal rezydują w cache kursorowym. Mamy tam informacje o zapytaniach jako takich, ale też dane na temat czasu wykonywania zapytania, ilość wykonań danego zapytania etc.

 

Podobnie jak wcześniej, zaznaczam zapytania które chcę poddać analizie, po czym wybieram przycisk „schedule SQL Tuning Advisor”:

Tak jak i wcześniej przechodzę przez widok w którym określam rodzaj analizy i moment jej przeprowadzenia:

Na kolejnym ekranie wybieram „Show all results”:

i przechodzę do ekranu podpowiedzi, gdzie zaznaczam wybrane zapytanie i klikam „View Recomendations” aby zobaczyć podpowiedzi dla danego zapytania:

Należy traktować podpowiedzi ATO troszkę z dystansem, ponieważ jest to tylko program , a program nie myśli w taki sposób jak człowiek. Nigdy też człowieka nie zastąpi. Nie traktujmy więc wszystkich podpowiedzi bezkrytycznie i weryfikujmy czy jego sugestie są racjonalne. Pamiętajmy też, że ATO nie wymyśli też wszystkiego co wymyślić może człowiek. Nie raz więc będzie się zdarzać że po ATO trzeba będzie poprawiać :)