Zwiększenie wydajności Oracle WebDB

część 2 – Tworzenie
komponentów z zapytań.

Gdy tworzysz komponenty w WebBD, najczęściej spotykasz się z możliwością budowania ich przy pomocy interfejsu kreatora (wizard interface) lub poprzez wprowadzanie swoich własnych instrukcji SQL.
Z powodu dużej elastyczności języka SQL, często do zasobów pojedynczego komponentu może odwoływać się więcej niż jedna instrukcja. Zanim użyjesz swoich kwerend, powinieneś się upewnić, że są one zoptymalizowane najlepiej jak to możliwe. Następne paragrafy pomogą ci przy tworzeniu instrukcji SQL.

Pomimo tego, że dwie różne instrukcje SQL mogą dawać ten sam rezultat, Oracle może wykonywać jedną z nich szybciej niż drugą. W celu określenia, która z instrukcji jest bardziej wydajna, możesz użyć polecenia EXPLAIN PLAN.
Oto przykład ukazujący przetestowanie programów działania dwóch instrukcji SQL spełniających tę samą funkcję. Obie instrukcje zwracają wszystkie te działy w tabeli DEPT, które nie mają pracowników w tabeli EMP. Każda instrukcja przeszukuje tabelę EMP za pomocą podzapytania. Zakładamy, że w kolumnie DEPTNO tabeli EPM znajduje się indeks DEPTNO_INDEX.

Tak wyglądałoby pierwsze polecenie wraz z prześledzeniem jego wydajności (planem wykonania):

SELECT dname, deptno

   FROM dept

      WHERE deptno NOT IN

         (SELECT deptno FROM emp);

SELECT STATEMENT Optimizer = CHOOSE

  FILTER

     TABLE ACCESS (FULL) OF DEPT

     TABLE ACCESS (FULL) OF EMP


Krok 3 wyjścia oznacza, że Oracle wykonuje tę instrukcję poprzez wykonanie pełnego skanu tabeli w tabeli EMP pomimo indeksu w kolumnie DEPTNO. Skan całej tabeli może być czasochłonną operacją. Przyczyną, dla której Oracle nie korzysta z indeksu jest to, że podzapytanie przeszukujące tabelę EMP nie zawiera komendy WHERE czyniącej indeks dostępnym.

Jednakże, następująca instrukcja SQL odnajduje te same wiersze tabeli dzięki dostępowi do indeksu:



SELECT dname, deptno

   FROM dept

    WHERE NOT EXISTS

       (SELECT deptno

           FROM emp

WHERE dept.deptno = emp.deptno);

SELECT STATEMENT Optimizer=CHOOSE

  FILTER

    TABLE ACCESS (FULL) OF DEPT

    INDEX (RANGE SCAN) OF DEPTNO_INDEX (NON-UNIQUE)

Komenda WHERE odsyła do kolumny DEPTNO w tabeli EMP. Skutkuje to użyciem indeksu DEPTNO_INDEX. Fakt użycia indeksu jest ukazany w Kroku 3 planu wykonania. Skanowanie obszaru zaindeksowanego przez DEPTNO_INDEX zabiera mniej czasu niż skan całej tabeli EMP (w pierwszym przypadku). Co więcej, pierwsze zapytanie przeprowadza pełny skan całej tabeli EMP dla każdej komórki DEPTNO w tabeli DEPT. Z tych powodów druga omawiana instrukcja SQL jest szybsza niż pierwsza.

Instrukcja SELECT


Omówione teraz zostaną wzorce i wskazówki dla wszystkich głównych klauzul instrukcji SELECT.

  • Nie używaj klawisza skrótu (*) SQL aby zaznaczyć wszystkie kolumny tabeli. Wyszczególnij każdą wymaganą kolumnę indywidualnie w listę selekcyjną.
  • Kiedy zliczasz wiersze tabeli, użyj komendy count (1), która obejmuje wszystkie wiersze spełniające wymagane kryteria. Nie używaj count (column), gdyż obejmie ona jedynie wiersze spełniające zarówno te kryteria
  • jak i posiadające wartość niezerową w wyszczególnionej kolumnie. Nie używaj również count (*) gdyż jest mało wydajna.
  • Przeprowadź staranną konwersję formatu zapisu danych, aby Oracle mógł pobierać je bez przeszkód.
  • Unikaj specyfikacji funkcji PL/SQL jako części zaznaczonej listy w przypadku, gdy spodziewasz się obszernych zbiorów wyników.
  • Kiedy specyfikujesz wyrażenia w liście poleceń select, zwróć uwagę aby zmaksymalizować wydajność ich konstrukcji. Na przykład, gdy wymaganym rezultatem jest wartość maksymalna kolumny * 15, zastosuj współczynnik na zewnątrz grupy funkcji. Czyli, zastosuj max (column) * 15, a NIE max (column * 15)

Klauzula FROM


Porządek złączania może mieć znaczący wpływ na wydajność. Głównym celem tuningu SQL jest uniknięcie wykonywania niepotrzebnej pracy w celu dostępu do wierszy nie mających wpływu na wyniki. Prowadzi to do kilku generalnych reguł:

  • Unikaj skanowania całej tabeli jeżeli możliwe jest dotarcie do wymaganych wierszy za pomocą indeksu.
  • Unikaj używania indeksu, który sprowadza 10,000 wierszy z tabeli kierującej, podczas gdy możesz zamiast niego zastosować indeks sprowadzający 100 wierszy.
  • Ustanów porządek złączania tak, ażeby później dołączać mniej wierszy do tabel.

Następujący przykład pokazuje jak efektywnie dostroić porządek dołączania:

SELECT stuff

     FROM taba a, tabb b, tabc c

   WHERE a.acol between :alow and :ahigh

     AND b.bcol between :blow and :bhigh

     AND c.ccol between :clow and :chigh

     AND a.key1 = b.key1

     AND a.key2 = c.key2;

  • Wybierz tabelę kierującą i indeks kierujący (jeśli istnieje).

    Pierwsze trzy warunki w poprzednim przykładzie są warunkami filtra. Każdy z nich stosuje się jedynie do pojedynczej tabeli. Ostatnie dwa warunki są warunkami złączania.

    Warunki filtra narzucają wybór tabeli kierującej oraz indeksu. Z zasady tabelą kierującą powinna być ta, która zawiera taki warunek filtra, który usuwa największy procent tabeli. A zatem, jeżeli obszar od: alow do: ahigh jest wąski w porównaniu z obszarem acol, ale obszary :b* i :c* są stosunkowo duże, taba powinna być tabelą kierującą, pozostałe są równorzędne.
  • Wybierz odpowiednie indeksy.

    Gdy już znasz swoją tabelę kierującą, wybierz najbardziej selektywny indeks z dostępnych, ażeby przekierować go do tej tabeli. Alternatywnie, wybierz pełny skan tabeli, gdyby okazało się to bardziej wydajne. Odtąd połączenia powinny zachodzić poprzez indeksy złączania. Indeksami złączania są indeksy kluczy głównych lub obcych, które są używane do połączenia tej tabeli z tabelą usytuowaną wcześniej w hierarchii łączenia. Rzadziej powinieneś używać indeksów w warunkach innych niż łączenie, z wyjątkiem tabel kierujących. Tak więc, jeżeli taba została wybrana jako tabela kierująca, powinieneś używać indeksów w b.key1 i c.key2 w celu przekierowania odpowiednio do tabb i tabc.
  • Wybierz najkorzystniejszy porządek złączania, kierując najpierw do najlepszego nieużywanego filtra.

    Możesz zredukować ilość pracy potrzebnej na proces łączenia poprzez dołączanie najpierw tabeli z najlepszym wciąż nieużywanym filtrem. Zatem jeżeli
    „bcol between…” jest bardziej ograniczający (zwraca większy procent wierszy) niż
    „ccol between…”, ostatnie dołączenie może zostać wykonane łatwiej (z mniejszą ilością wierszy) jeżeli tabb zostanie dołączona przed tabc.
  • Używaj niezmienionych wartości kolumn. Na przykład, użyj:

    WHERE a.order_no = b.order_no

    raczej, niż:

    WHERE TO_NUMBER (substr (a.order_no, instr (b.order_no, ‚.’) – 1)

    = TO_NUMBER (substr (a.order_no, instr (b.order_no, ‚.’) – 1)

  • Nie używaj funkcji SQL w klauzulach predykatu lub w klauzulach WHERE.

    Użycie połączonych funkcji, szczególnie w podzapytaniach, często sygnalizuje, że powinieneś posiadać żądaną wartość w rekordzie głównym.

Gdy używasz przepisowego optymalizatora, porządek tabel zdefiniowanych w klauzuli FROM może znacznie wpłynąć na wydajność. Podczas gdy parser operuje z prawej do lewej, tabela określona później zostaje wybrana jako tabela kierująca, jeżeli system szeregowania optymalizatora tworzy dwie lub więcej tabel będących w najlepszym razie
„związanymi” w celu skierowania pytania. Tabele powinny być generalnie sortowane w malejącym porządku wyznaczonych wierszy w celu osiągnięcia lepszej wydajności. Zauważ, że instrukcja ta znajduje zastosowanie jedynie w przypadku, gdy istnieje
„wiązanie” do selekcji w tabeli kierującej.

Klauzula WHERE


Klauzula WHERE jest najważniejszą komendą w instrukcji SELECT. Definiuje ona kryteria, według których dokonywane jest wyszukiwanie. Komenda WHERE powinna być zrobiona tak restrykcyjnie jak to tylko możliwe zanim będą stosowane takie operacje jak
„group by” lub „order by”.



Podczas przeszukiwania, wykorzystanie indeksu uzależnione jest od jego głównych kolumn używanych przez klauzulę WHERE. Tak więc, jeżeli główne komponenty indeksu nie są kryteriami poszukiwanego tematu, ślepy predykat klauzuli WHERE odsyłający do głównych kolumn może zostać włączony w tym
„zawsze pomyślnym” przypadku by promować użycie indeksu.

Klauzula GROUP BY

  • Nie grupuj przy pomocy komend nie będących częścią listy SELECT, lub komend zmodyfikowanych przez wyrażenia lub funkcje.
  • Zamiast precyzować klauzulę HAVING, ograniczaj ilość wierszy zwracanych przez predykaty klauzuli WHERE. Pozwoli to zredukować ilość sortowanych danych po wyszukaniu.

Klauzula ORDER BY

  • Specyfikuj tę klauzulę w przypadku, gdy dane mają być prezentowane w określonym porządku.
  • Używaj cyfr w miejsce nazw kolumn jedynie w operacjach INTERSECT, MINUS, lub UNION.
  • Używaj skrótów ASC i DESC dla odpowiednio ASCENDING i DESCENDING. Gdy wymagane jest pomieszanie porządków, specyfikuj ASC lub DESC dla każdej wielkości w porządku przy pomocy klauzuli. Domyślną metodą sortowania jest porządek rosnący.

Podzapytania (subqueries)

  • Gdy tylko możliwe, staraj się zastąpić podzapytania złączeniami tabeli.
  • Gdy dopasowujesz zbiorowe wartości kolumny do podzapytania, użyj listy kolumn. Nie wiąż ze sobą wartości kolumn.
  • Gdy porównujesz listę kolumn z podzapytaniem, przeprowadź konieczną konwersję typu danych TYLKO w zaznaczonej liście podpytania.

Złączenia zewnętrzne
(outer joins)

  • Nie specyfikuj złączenia zewnętrznego kolumn w predykatach, które są odgałęzieniami OR.

Treewalking

  • Gdy wykonujesz operację treewalk, stosuj zawsze klauzulę START WITH w połączeniu z klauzulą CONNECT BY.

Operatory i funkcje

  • Zastąp występowanie operatora BETWEEN predykatem używającym operatorów >= i <=, zamiana taka będzie wykonywana też przez optymalizator.
  • Jeżeli zastosowana jest operacja BETWEEN, nie stosuj stałej jako testującej. Na przykład, nie stosuj 25 pomiędzy column1 i column2.

  • Unikaj stosowania operatora LIKE z danymi typu DATE oraz NUMBER. Jeżeli jednak jest to wymagane, dokonaj raczej jawnej konwersji typu danych, niż pozwól wykonać to niejawnie Oracle.
  • Kiedy używasz operatora OR, zawsze otaczaj argumenty nawiasami.
  • Każde wyrażenie angażujące kilka operatorów powinno używać nawiasów w celu uzyskania pierwszeństwa.
  • Bądź ostrożny gdy stosujesz operator NOT. Oracle zakłada, że warunki NOT NULL i != będą zwracać większość wierszy tabeli. Tak więc, nie zostaną użyte indeksy i zostanie przeprowadzony pełny skan tabeli.
  • Jeżeli wynikowa ilość danych jest taka, że użycie indeksu byłoby korzystne, możesz zwykle przerobić predykaty zawierające operator NOT aby wyeliminować jego użycie.

  • Gdy selekcjonujesz wybrane wiersze z połączonych tabel, zastosuj EXIST aby sprawdzić obecność w jednej tabeli podczas przeszukiwania innej. Preferowane jest użycie DISTINCT na połączonych tabelach, ponieważ polecenie EXIST natychmiast przerwie w przypadku sukcesu Đ oferując potencjalny zapis wyników.
  • Używaj zawsze operatora EXIST zamiast operatora IN. IN wymusza wewnętrzną operację sort/merge.
  • Kiedy łączysz dane z kilku poleceń SELECT używając UNION-ów, zawsze, jeśli to tylko możliwe, zastosuj operator UNION ALL. W przeciwieństwie do UNION, UNION ALL nie eliminuje powtarzających się danych z połączonego zapytania. Jeżeli eliminacja powtórzeń nie jest potrzebna, zastosowanie UNION ALL pozwoli uniknąć dodatkowego sortowania w celu usunięcia duplikatów.

Użycie indeksu.


Generalnie, Oracle będzie próbował użyć określonego indeksu na tabeli, jeżeli główne komponenty indeksu są określone w predykatach wykonywanego polecenia. Jednakże, kilka czynników może zmusić Oracle do nieużywania indeksów podczas wykonywania instrukcji. Oto kilka takich przypadków wyłączenia indeksu:

  • Wykonywanie obliczeń lub funkcji na kolumnie indeksowanej (np. WHERE col1 is null)
  • Przyrównanie kolumny indeksowanej do NULL (np. WHERE col1 is null)
  • Użycie operatora NOT na kolumnie indeksowanej (np. WHERE col1 != 0))
  • Użycie operatora OR na kolumnie indeksowanej (OR powoduje przeprowadzenie przez Oracle pełnego skanu tabeli, tak więc operatory UNION i UNION ALL powinny być używane w pierwszej kolejności)
  • Jawna lub niejawna konwersja danych w kolumnie indeksowanej.

Używaj wskazówek optymalizatora, takich jak /*+ORDERED */, aby kontrolować ścieżki dostępu. Jest to lepsze podejście niż używanie tradycyjnych technik lub trików, takich jak CUST_NO + 0.

Na przykład użyj:



SELECT /*+ FULL (EMP) */ E.ENAME

   FROM EMP E

 WHERE E.JOB = ‚CLERK’;



raczej, niż:



SELECT E.ENAME

   FROM EMP E

 WHERE E.JOB || ”= ‚CLERK’;

Unikaj wyrażeń mieszanych


Unikaj wyrażeń mieszanych i wystrzegaj się niejawnego typu konwersji. Kiedy chcesz użyć indeksu na VARCHAR2 kolumny charcol, ale klauzula WHERE wygląda tak:

AND charcol = <numexpr>


Gdzie numexp jest wyrażeniem numeru typu (na przykład, USERENV ('SESSIONID'),
numcol, numcol+0,...),


Oracle przetłumaczy to wyrażenie na:


AND to_number(charcol) = numexpr

Niesie to za sobą następujące konsekwencje:

  • Każde wyrażenie używające kolumny, jak np. funkcja posiadająca kolumnę jako swój argument,
  • spowoduje, że optymalizator zignoruje możliwość użycia indeksu na tej kolumnie, nawet indeksu unikalnego.

  • Jeżeli system przetwarza nawet pojedynczy wiersz mający charcol jako ciąg znaków nie przetłumaczonych w liczbę, zostanie wygenerowany błąd.

Możesz uniknąć tego problemu poprzez zastąpienie najwyższego wyrażenia konwersją jawną:



AND charcol = to_char(<numexpr>)



Alternatywnie, zrób wszystkie konwersje jawne. Użyj polecenia:



numcol = charexpr



Umożliwia to użycie indeksu na numcol, ponieważ domyślna konwersja jest zawsze typu znak-na-liczbę. Jednakże właściwość ta jest rzeczą do zmiany. Dokonanie typu konwersji jawnej czyni także jasnym, że charexpr powinien być zawsze przetłumaczony na liczbę.
Pisz oddzielne polecenia SQL dla poszczególnych wartości
SQL nie jest językiem proceduralnym. Używanie jednego kawałka SQL w celu zrobienia wielu różnych rzeczy nie jest dobrym pomysłem. Skutkuje to zwykle wynikami gorszymi od optymalnych dla każdego zadania. Jeżeli chcesz przy pomocy SQL realizować różne rzeczy, napisz dwie oddzielne instrukcje. Zrób tak, zamiast tworzyć jedno polecenie służące do wykonania kilku operacji zależnych od podanych przez ciebie parametrów.
Optymalizacja (badanie planu wykonania) zachodzi zanim baza danych pozna jakie wartości pojawią się w miejsce zapytania. Zatem plan wykonania nie powinien zależeć od tych wartości. Na przykład:

SELECT stuff from tables

  WHERE...

    AND somecolumn BETWEEN decode (:loval, 'ALL', somecolumn, :loval)

   AND decode (:hival, 'ALL', somecolumn, hival);

W takim przypadku baza danych nie może użyć indeksu na kolumnie somecolumn, ponieważ wyrażenie zawierające tę kolumnę używa tej samej kolumny po obu stronach komendy BETWEEN.
Nie jest to problemem w przypadku, gdy istnieje jakiś inny wysoce selektywny, indeksowalny warunek, którego możesz użyć dla dostępu do tabeli kierującej. Często jednak nie ma takiego warunku. Często możesz chcieć użyć indeksu na warunku takim jak pokazany, ale potrzebujesz znać z góry wartość :loval, etc. Z taką informacją możesz wykluczyć warunek ALL, który nie powinien używać indeksu.

Jeżeli chcesz użyć indeksu ilekroć zostaną podane prawdziwe wartości dla :loval i :hival (tzn. wtedy, gdy spodziewasz się wąskich zakresów, nawet takich gdzie :loval często pokrywa się z
:hival), możesz przepisać przykład w następującej, logicznie równoważnej formie:

SELECT /* change this half of union all if other half changes */ stuff

     FROM tables

  WHERE...

     AND somecolumn between :loval and hival

     AND (:hival != 'ALL' and :loval != 'ALL')

UNION ALL

SELECT /* Change this half of union all if other half changes. */ stuff

  FROM tables

WHERE...

  AND (:hival = 'ALL' OR :loval = 'ALL');

Jeżeli uruchamiasz EXPLAIN PLAN w nowym zapytaniu, może wydawać się, że otrzymasz zarówno pożądany jak i niepożądany plan egzekucji. Jednakże, pierwszym warunkiem dla którego baza danych ocenia obie części UNION ALL będzie połączony warunek czy :hival i loval są ALL. Baza danych bada ten warunek zanim właściwie pobierze jakikolwiek wiersz z planu wykonania dla tej części zapytania. W przypadku, gdy warunek okaże się fałszem dla jednej części zapytania UNION ALL, część ta przestaje być dalej oceniana. Kontynuowana jest jedynie ta część planu egzekucji, która stanowi optimum dla dostarczonych wartości. Ponieważ końcowe warunki na
:hival i :lowal są wzajemnie wykluczające się, właściwie jedynie jedna połowa UNION ALL zwróci wiersze. (ALL w UNION ALL jest logicznie uzasadniony ze względu na swoją wyłączność. Pozwala to na kontynuację wykonywania planu bez kosztownego sortowania w celu wykluczenia powtarzających się wierszy w dwóch połowach zapytania.)

Użyj CARE gdy osadzasz listy wartości danych w aplikacjach
Listy wartości danych są znakiem informującym, że brakuje encji (entity). Na przykład:

WHERE TRANSPORT IN ('BMW',

'CITROEN', 'FORD', HONDA')

Rzeczywistym celem w klauzurze WHERE powyżej jest określenie, czy sposobem transportu jest samochód, a nie identyfikacja określonej marki. Tabela odniesień powinna być udostępniona wtedy, gdy typ transportu='AUTOMOTIVE'.

Minimalizuj użycie komendy
DISTINCT

DISTINCT zawsze wywołuje SORT; wszystkie dane muszą być konkretyzowane, zanim będą mogły zostać zwrócone rezultaty. Jeżeli używasz DISTICT, istnieje podejrzenie, ze gdzieś w twoim modelu danych brakuje encji.

Użyj CARE podczas łączenia perspektyw (views)

Wspólny obszar SQL w Oracle redukuje koszt analizy składni zapytań powołujących się na perspektywy. W dodatku, usprawnienia optymalizatora czynią przetwarzanie predykatów na perspektywy bardzo wydajnym. Czynniki te razem umożliwiają użycie perspektyw do zapytań ad hoc. Pomimo tego, złączenia do perspektyw nie są zalecane, szczególnie złączenia jednej złożonej perspektywy z drugą.

Następujący przykład pokazuje zapytanie na kolumnie będącej rezultatem GROUP BY. Cała perspektywa jest najpierw konkretyzowana, a następnie zapytanie jest uruchamiane w stosunku do danych perspektywy.

CREATE VIEW DX(deptno, dname, totsal)

   AS SELECT D.deptno, D.dname, E.sum (sal)

      FROM emp E, dept D

     WHERE E.deptno = D.deptno

   GROUP BY deptno, dname

SELECT * FROM DX WHERE deptno=10;

Użyj CARE gdy wykonujesz złączenia zewnętrzne do perspektyw. Złączenie zewnętrze do perspektywy wielotabelowej może być problematyczne. Na przykład, możesz zacząć ze zwykłymi tabelami emp i dept z indeksami na e. empno, e. emptno i d. deptno, i utworzyć następującą perspektywę:

CREATE VIEW EMPDEPT (EMPNO, DEPTNO, ename,dname)

   AS SELECT E.EMPNO, E.DEPTNO,e.ename,d.dname

     FROM DEPT D, EMP E

    WHERE E.DEPTNO = D.DEPTNO (+);

Możesz następnie skonstruować najprostsze możliwe zapytanie aby utworzyć zewnętrzne złączenie do tej perspektywy na interesującej kolumnie (e.deptno) tabeli rozwarstwiając perspektywę:

SELECT e.ename, d.loc

   FROM dept d, empdept e

   WHERE d.deptno = e.deptno (+)

      AND d.deptno = 20;

Następujący plan wykonania daje:

MERGE JOIN OUTER

     TABLE ACCESS BY ROWID DEPT

      INDEX UNIQUE SCAN DEPT_U1: DEPTNO

   FILTER

     VIEW EMPDEPT

       NESTED LOOPS OUTER

          TABLE ACCESS FULL EMP

          TABLE ACCESS BY ROWID DEPT

             INDEX UNIQUE SCAN DEPT_U1:
DEPTNO

Dopóki obie tabele perspektywy są połączone, dopóty optymalizator nie wie, czy widok wygeneruje właściwy wiersz. Dlatego optymalizator musi wygenerować wszystkie wiersze perspektywy i wykonać MERGE JOIN OUTER z wszystkimi wierszami zwróconymi z reszty zapytania. Podejście takie jest skrajnie niewydajne w przypadku, gdy chcesz uzyskać jedynie kilka wierszy z perspektywy wielotabelowej z przynajmniej jedną bardzo dużą tabelą.
Stosunkowo łatwo jest rozwiązać ten problem. Drugie odniesienie do dept nie jest potrzebne, więc możesz wykonać zewnętrzne złączenie prosto do emp. W innych przypadkach, złączenie nie musi być złączeniem zewnętrznym. Wciąż możesz w prosty sposób użyć perspektywy przez pozbycie się (+) na złączeniu do perspektywy.

Na podstawie mądrości Internetu