Dlaczego 99 ze 100 to tylko 1%?

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%?

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *