W bazie danych umieszczamy tabelę zawierającą sto rekordów, które w kolumnie PŁEĆ posiadają 99 razy wartość ‘M’ i jeden raz wartość ‘K’. Gromadzimy statystyki dla tej tabeli, gromadzimy histogram dla kolumny PŁEĆ. Następnie wykonujemy zapytanie, które w klauzuli WHERE posiada predykat UPPER(PŁEĆ) = ‘M’. Jaka jest selektywność tego predykatu? Oczywiście 99%. Jaką selektywność oszacuje optymalizator kosztowy? … 1%.
Przyjrzyjmy się poniższemu przykładowi. Kolumna Rows w planie wykonania zapytania pokazuje oczekiwaną przez optymalizator liczbę rekordów spełniających nasz predykat (pamiętajmy, że plan wykonania zapytania jest generowany _przed_ wykonaniem zapytania).
SQL> exec dbms_stats.gather_table_stats('SYS','ARMIA',method_opt=>'FOR COLUMNS SIZE 2 PLEC');
SQL> set autotrace on;
SQL> select * from armia where upper(plec)='M';
...
99 wierszy zostało wybranych.
...
Plan wykonywania
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL| ARMIA | 1 | 5 | 2 (0) | 00:00:01 |
---------------------------------------------------------------------------
Rows=1! A jak widać, zapytanie naprawdę zwróciło 99 rekordów. Co jest przyczyną tak poważnej pomyłki optymalizatora? Otóż sam fakt posiadania histogramu dla kolumny nie umożliwia szacowania selektywności dla wyrażenia funkcyjnego, w którym taka kolumna uczestniczy. Bo oczywiście selektywność predykatu PLEC=’M’ (a dla tego predykatu mamy histogram) nie musi być taka sama jak predykatu UPPER(PLEC)=’M’. W ostatnim przypadku serwer Oracle Database automatycznie przyjmuje selektywność „z kapelusza” wynoszącą 1% (lub 5% dla predykatów nierównościowych).
Czy można w jakiś sposób pomóc optymalizatorowi we właściwym szacowaniu selektywności predykatów opartych na wyrażeniach? Można. W grę wchodzi albo wymuszenie dynamicznego próbkowania, albo zebranie histogramu dla wyrażenia funkcyjnego UPPER(PLEC) – jawnie lub w wyniku utworzenia indeksu funkcyjnego. Sprawdźmy.
SQL> alter system set optimizer_dynamic_sampling=3;
SQL> select * from armia where upper(plec)='M';
...
99 wierszy zostało wybranych.
...
Plan wykonywania
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 495 | 2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL| ARMIA | 99 | 495 | 2 (0) | 00:00:01 |
---------------------------------------------------------------------------
Rows=99. Teraz estymacja jest poprawna. Wprawdzie w mojej prostej demonstracji plan wykonania zapytania nie uległ zmianie, lecz w „życiowych” zastosowaniach konsekwencje byłyby dużo poważniejsze.
1 thought on “Dlaczego 99 ze 100 to tylko 1%?”