Wprowadzenie do optymalizacji SQL

Wprowadzenie do optymalizacji SQL

Niniejszy rozdział jest jedynie ogólnikowym wprowadzeniem do optymalizacji. Taki ogólny zarys, którego zasadniczym celem jest danie czytelnikowi poglądu na całość.

Zasadniczo wszelkie optymalizacyjne problemy wynikają ze sposobu wykonywania zapytań. Zanim po wykonaniu zapytania otrzymamy z powrotem dane, musi zostać wykonane kilka czynności.

• Musi zostać sprawdzone czy obiekty do których się odwołujemy w ogóle istnieją i czy np. tabele posiadają kolumny do których chcemy uzyskać dostęp. W tym celu przeszukiwane są słowniki systemowe.
• Jeśli obiekty istnieją , musi zostać sprawdzone czy mamy uprawnienia do obiektów.
• Musi zostać sprawdzona poprawność składniowa zapytania.
• Muszą zostać sprawdzone dostępne struktury (np. indeksy) które można wykorzystać by pobrać dane.
• Musi zostać sprawdzone czy dane które chcemy pobrać znajdują się może w buforze db_cache.
• Muszą zostać wymyślone plany wykonania zapytania w oparciu o dostępne możliwości. Dla tych planów muszą zostać oszacowane koszty, które następnie są porównywane w celu wybrania najlepszego.
• Wreszcie dane muszą zostać pobrane , przetworzone i zwrócone.

To w jaki sposób zapytanie jest wykonywane (myślę tutaj o algorytmach) zależy od kilku czynników. Na niektóre z nich możemy wpływać. O sposobie wykonania zapytania w nowszych wersjach Oracle decyduje optymalizator kosztowy. Opiera się on na informacjach statystycznych na temat obiektów do których odwołujemy się w zapytaniach. Dobiera odpowiednie algorytmy i metody dostępu do danych ,w taki sposób, by w efekcie wykonanie zapytania jak najmniej obciążało serwer, a jednocześnie wyniki zapytań były zwracane jak najszybciej. Efektywność wykonywania zapytania zależy w dużej mierze od zastosowanych do pobrania danych algorytmów, ale również od innych czynników. Zdarza się też, że optymalizator kosztowy nie może zastosować wydajnego algorytmu z powodu źle zaprojektowanych lub ubogich struktur danych. Optymalizator kosztowy dobiera algorytmy m.in. na podstawie statystyk. Statystyki najogólniej mówiąc są to informacje na temat obiektów np. tabel. Zawierają wiadomości na temat np. ilości wierszy w tabeli, ich zróżnicowania.

Będę starał się jak najwięcej tłumaczyć metodą analogii. Odnieśmy się do pobierania danych jak do podróży. Jeśli znajdujemy się w jakimś miejscu - dajmy na to w Gdyni i chcemy dostać się np. do Wrocławia musimy wybrać środek lokomocji ( odpowiedni algorytm dostępu do danych). W zależności od tego jaki wybierzemy, czas naszej podróży będzie krótszy lub dłuższy, oraz stopień naszego wysiłku poświęconego na dotarcie do celu będzie mniejszy lub większy. Wybierzemy oczywiście najszybszy i najmniej męczący sposób ( najmniej kosztowny explain plan – plan wykonania zapytania złożony z wybranych algorytmów). Warunkiem który musi być spełniony by nasza podróż przebiegała wygodnie i szybko, jest przede wszystkim możliwość wyboru jakiegoś środka lokomocji. Wyobraźmy sobie , że nie ma takich połączeń kolejowych, lotniczych ani autobusowych którymi moglibyśmy dotrzeć na miejsce. Nie dysponujemy też żadnym pojazdem silnikowym. Ponieważ infrastruktura jest uboga i nasze możliwości są bardzo ubogie – musimy wybrać najbardziej męczącą i najdłuższą , ale jednocześnie jedyną metodę – pójść na piechotę. Znajduje to oczywiście odzwierciedlenie w optymalizacji. Jeśli nie zadbamy o to by dać optymalizatorowi kosztowemu właściwą strukturę (np. indeksy) , nie będzie mógł wykorzystać optymalnej metody dostępu do danych i wszędzie będzie chodził na piechotę. To właśnie powód dlaczego zadbanie o odpowiednie struktury jest tak istotne.

Weźmy teraz do analizy inny przypadek. Znajdujemy się w pewnym miejscu. Chcemy dotrzeć do Wrocławia. Myślimy że znajdujemy się od niego 500 metrów więc podejmujemy spacer. Gdybyśmy znajdowali się 100km od Wrocławia, oczywiście wsiedlibyśmy w samochód i nim byśmy podróżowali. Co jednak gdybyśmy myśleli że znajdujemy się 500 metrów od celu a w rzeczywistości byli 10 km dalej? Wybralibyśmy nieoptymalny plan wykonania zapytania – poszli na piechotę. W efekcie nasza podróż niepotrzebnie by się wydłużyła. Wszystko przez to, że podjęliśmy decyzję od doborze algorytmu na podstawie nieprawdziwej informacji. Wybrany plan wykonania zapytania byłby optymalny gdyby nasze przypuszczenia co do odległości okazały się prawdziwe. Taką informację o odległości do celu (ilości danych do pobrania/przetworzenia) optymalizator kosztowy podejmuje na podstawie statystyk. Jeśli więc nasze statystyki będą

nieaktualne , np. będą zawierały informacje o małej ilości wierszy w tabeli a w rzeczywistości okaże się że tych wierszy jest znacznie więcej, optymalizator kosztowy wybierze właściwy algorytm dla małych porcji danych, ale zdecydowanie nie właściwy dla dużych. Z tego powodu musimy dbać o to , by statystyki były jak najświeższe i zawierały informacje zgodne z prawdą. Oczywiście statystyki zawierają znacznie więcej informacji niż tylko ilość wierszy w tabeli, o tym będzie w kolejnych rozdziałach.

Przejrzyjmy więc kilka powodów nieefektywnego wykonywania się zapytań:

• Ubogie struktury
• Nieaktualne statystyki
• Wybór nieoptymalnego planu wykonania zapytania
• Źle skonstruowane zapytanie SQL.

Co więc możemy zrobić by zapytania na naszej bazie danych wykonywały się efektywnie?

• Poszukać tych zapytań które najbardziej obciążają
• Odświeżyć statystyki obiektów
• Odświeżyć statystyki systemowe
• Potworzyć indeksy tam gdzie są potrzebne lub odbudować tam gdzie nie działają efektywnie.
• Poprawić struktury tam gdzie to potrzebne.

Nie zawsze jednak słaba wydajność wynika z w.w powodów. Możemy przecież mieć za mało pamięci RAM, słaby procesor lub nawet źle poustawiane bufory w SGA.

Skąd możemy wiedzieć co jest przyczyną? By znaleźć pierwszy trop spójrz na poniższy wykres:

Jeśli sposób wykonywania zapytań zbliżony jest do sytuacji A tj. zapytania nie trwają długo, ale bardzo obciążają serwer, to przyczyna leży raczej w strukturach i statystykach. Jeśli bliżej do B tj.

zapytania wykonują się długo, ale za to obciążenie jest niskie – to problem najprawdopodobniej wynika z słabego sprzętu lub złych ustawień buforów SGA. Wystarczy nawet nie dość szybki dysk twardy by zapytania długo trwały z powodu długiego oczekiwania na odczyt danych.

Pamiętajmy też o tym, że wiele nieefektywnych zapytań obciążających procesor powoduje sytuację zbliżoną do B – po prostu pozostałe zapytania muszą poczekać na zwolnienie zajętych zasobów.

Może też zdarzyć się tak, że do utrzymania dostajemy system napisany przez innego dostawcę i system ten działa nieoptymalnie. Przykłady takich przyczyn:

• Złe zarządzanie połączeniami – np. wielokrotne łączenie się i rozłączanie tam gdzie można by było wykorzystać jedną sesję.
• Nie wykorzystywanie możliwości jakie daje nam shared_pool, brak współdzielenia kursorów. Niepotrzebne wielokrotne wymuszanie parsowania zapytań dla których mógłby zostać wykorzystany ten sam plan wykonania.
• Wielkość plików dziennika powtórzeń jest zbyt mała i powoduje to częsty zrzut danych z db_cache na dysk.
• Niepotrzebne pobieranie całej zawartości tabeli, po to by potem wyświetlić z tego np. dwie kolumny.
• Pakiety skompilowane w trybie interpretted

Kilka wskazówek w projektowaniu struktur danych

• Twórz proste tabele jeśli to możliwe.
• Stosuj przemyślane zapytania SQL i nie pobieraj danych których nie potrzebujesz.
• Stosuj indeksy tylko tam gdzie to potrzebne i w taki sposób by miały szansę być wykorzystane (np. indeks podwójny jeśli zachodzi taka potrzeba zamiast indeksu pojedynczego).
• Stosuj klucze obce i główne, by dane były spójne. Dzięki temu nie będzie duplikatów i „śmieciowych” danych.
• Wykorzystuj struktury takie jak widoki zmaterializowane czy tabele tymczasowe, jeśli mogą one zastąpić wielokrotne niepotrzebne wykonywanie podzapytań w kilku zapytaniach.
• Wykorzystuj tablice partycjonowane przy dużych wolumenach danych.
• Zakładaj indeksy na kluczach obcych.
• Zakładaj indeksy na często przeszukiwanych kolumnach
• Pisz SQL tak by wykorzystywał indeksy
• Pisz zapytania tak, by mogły wykorzystywać wcześniej stworzone plany wykonania
  
  
  
  
  
  
  Zdaję sobie sprawę że wiele z wymienionych tutaj rzeczy będzie dla Ciebie niejasne. Potraktuj to jako checkpoint do późniejszej optymalizacji Twoich rozwiązań. Przy okazji następnych rozdziałów wszystko się wyjaśni.
  .