Zarówno SQL Profiles, jak i Hints to mechanizmy pozwalające sterować zachowaniem optymalizatora zapytań tak, aby wydajność generowanych planów była jak najlepsza. Byłyby one niepotrzebne, gdyby optymalizator potrafił zawsze trafnie wybrać optymalny plan wykonania. Ponieważ jednak nierzadko zdarza się optymalizatorowi „chybić”, to takie lekarstwa są niezbędne.
Hinty to zapisane w treści polecenia SQL wskazówki (zalecenia), instruujące optymalizator zapytań na temat tego, jakie kroki powinny, lub nie powinny znaleźć się w planie wykonania. Umożliwiają one zawężenie przestrzeni poszukiwań planów do wybranego przez nas podzbioru możliwych rozwiązań.
Natomiast SQL Profiles to „erraty” służące do korekcji nietrafnych szacunków optymalizatora zapytań dokonywanych podczas generowania planu wykonania (kardynalność, selektywność). SQL Profiles są zwykle efektem ubocznym działania SQL Tuning Advisora, który eksperymentalnie odkrywa niezgodność szacowanych selektywności z rzeczywistym przebiegiem wykonania zapytania. Istnieje też możliwość całkowicie automatycznego generowania profili dla trudnych zapytań SQL w ramach nocnego zadania Automatic SQL Tuning (ACCEPT_SQL_PROFILES=TRUE).
Ważna różnica pomiędzy hintami a profilami dotyczy zatem sposobu zapisu informacji korygujących działanie optymalizatora: hinty wymuszają konkretne kroki w planie wykonania, natomiast profile dostarczają optymalizatorowi skorygowanych wartości selektywności, dzięki czemu optymalizator samodzielnie znajduje dobry plan.
Główne korzyści wynikające ze stosowania profili to zatem:
- w przeciwieństwie do hintów, profile SQL nie zmuszają optymalizatora do stosowania konkretnego planu wykonania zapytania
- w przeciwieństwie do hintów dla optymalizatora, profile SQL nie wymagają modyfikacji kodu aplikacji
W większości zastosowań powyższe cechy sprawiają, że profile są rozwiązaniem skuteczniejszym i łatwiej wdrażalnym.
Zademonstrujmy przykłady rozwiązań prostego problemu wydajnościowego zarówno za pomocą profili, jak i hintów. Mamy tabelę ARMIA(ID, PLEC) z podstawowymi statystykami, zawierającą 3200 rekordów, w tym 1% kobiet, 99% mężczyzn. Na kolumnie PLEC istnieje index B*-drzewo. Wykonujemy wysoce selektywne zapytanie wyszukujące wyłącznie kobiet. Ze względu na brak histogramu dla kolumny PLEC, optymalizator wyznacza selektywność predykatu PLEC=’K’ na poziomie 50%, co skutkuje wyborem nieoptymalnego planu opartego na pełnym odczycie tabeli (FULL TABLE SCAN) zamiast użycia indeksu.
SQL> select sum(id) from armia where plec='K';
...
Plan wykonywania
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| ARMIA | 1600 | 4800 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Aby zbudować profil SQL, który skoryguje błędne szacunki selektywności, przygotujemy i wykonamy zadanie SQL Tuning Advisora:
SQL> var task_name varchar2(30);
SQL> EXEC :task_name := dbms_sqltune.create_tuning_task(
sql_text => 'select sum(id) from armia where plec=''K''');
...
SQL> EXEC dbms_sqltune.execute_tuning_task(:task_name);
...
Następnie odczytamy raport wygenerowany przez SQL Tuning Advisora. Zauważmy, że zaproponowany został profil SQL, który pozwoli poprawić rzeczywisty czas wykonania zapytania o ponad 76%, a ponadto odciąży procesor i zredukuje liczbę odczytywanych bloków. Widzimy też jaki będzie skutek zastosowania tego profilu z punktu widzenia planu wykonania zapytania – zamiast pełnego odczytu tabeli pojawił się zakresowy odczyt indeksu.
SQL> SELECT dbms_sqltune.report_tuning_task(:task_name) FROM dual;
...
SQL Text : select sum(id) from armia where plec='K'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
Dla tej instrukcji znaleziono potencjalnie lepszy plan wykonywania.
Recommendation (estimated benefit: 14.17%)
------------------------------------------
- Proszę rozważyć zaakceptowanie zalecanego profilu SQL.
execute dbms_sqltune.accept_sql_profile(task_name => 'ZADANIE_1649',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
SQL profile został przetestowany przez wykonanie zarówno jego planu, jak i
planu oryginalnego oraz przez pomiar ich statystyk wykonywania. Plan mógł
być wykonany tylko częściowo, jeśli ukończenie drugiego planu nastąpiło
szybciej.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(us): 594 138 76.76 %
CPU Time(us): 1560 0 100 %
User I/O Time(us): 0 0
Buffer Gets: 8 7 12.5 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Najpierw wykonano original plan w celu uaktywnienia buforowej pamięci
podręcznej.
2. Statystyki dla original plan zostały uśrednione na podstawie 9 następnych
uruchomień.
3. Najpierw wykonano SQL profile plan w celu uaktywnienia buforowej pamięci
podręcznej.
4. Statystyki dla the SQL profile plan zostały uśrednione na podstawie 9
następnych uruchomień.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| ARMIA | 32 | 96 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
...
2- Using SQL Profile
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)|00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ARMIA | 32 | 96 | 2 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | ARMIA_IDX | 32 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Zaakceptujmy propozycję utworzenia profilu SQL i zaobserwujmy jak ten profil wpłynie na przyszły plan wykonania naszego zapytania.
SQL> EXEC dbms_sqltune.accept_sql_profile(:task_name);
...
SQL> select sum(id) from armia where plec='K';
...
Plan wykonywania
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)|00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ARMIA | 32 | 96 | 2 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | ARMIA_IDX | 32 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
...
Note
-----
- SQL profile "SYS_SQLPROF_01430a0f52b30000" used for this statement
Udało nam się pomyślnie skorygować nieoptymalny plan wykonania zapytania dzięki utworzeniu profilu SQL.
A jak moglibyśmy uzyskać taki sam efekt za pomocą hintów? Poniżej treść poprawionego zapytania. Oczywiście podstawową wadą takiego rozwiązania jest konieczność ingerowania w kod źródłowy aplikacji w celu dopisania wskazówek dla optymalizatora.
SQL> select /*+ INDEX(ARMIA ARMIA_IDX)*/ sum(id) from armia where plec='K';
...
Plan wykonywania
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 3 | 10 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|
| 2 | TABLE ACCESS BY INDEX ROWID| ARMIA | 1600 | 4800 | 10 (0)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | ARMIA_IDX | 1600 | | 4 (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
...