Statystyki – DBMS_STATS
Statystyki są zbiorem informacji opisującym dane w bazie danych jak i samą bazę danych. Na ich podstawie optymalizator kosztowy tworzy plany zapytań i wybiera ten, który jest najbardziej optymalny. To co brane jest pod uwagę podczas zbierania statystyk to m.in. ścieżki dostępu (ang. access path), kolejności złączenia tabel (ang. join ordering), oraz metody złączenia (ang. join method).
Zbieranie statystyk jest procesem niezwykle czasochłonnym i obciążającym bazę, dlatego nie zaleca się zbierania statystyk w czasie rzeczywistym na działającym środowisku. Wszystkie instrukcje DML na tabeli powodują, że statystyki z czasem tracą na aktualności i należy je co jakiś czas odświeżać. Robi się to zazwyczaj w określonych okienkach technicznych, w których ruch na bazie jest najmniejszy. Od wersji 10g zbieranie statystyk odbywa się automatycznie w cyklach na tabelach, dla których statystyki nie zostały zebrane, bądź są nieaktualne.
Które dane są zbierane w statystykach?
Począwszy od wersji bazy danych 8.0 zbieranie statystyk jest możliwe dzięki wykorzystaniu wbudowanego pakietu DBMS_STATS. W bazie danych Oracle można wyróżnić dwie zasadnicze grupy statystyk:
- Statystyki obiektowe
- Statystyki systemowe
Na te pierwsze składają się takie informacje jak:
- Informarcja o ilości rekordów
- Srednia długość rekordu
- Ilość bloków, które zajmuje segment tabeli
Nierzadko również na potrzeby wyliczenia optymalnego planu zapytania zbierane są informacje na poziomie pojedynczej kolumny tabeli jak:
- ilość pustych wartośći
- ilość unikalnych wartości
- histogramy, czyli rozłożenie danych w tabeli
W przypadku indeksów brane są pod uwagę takie dane jak:
- ilość bloków liści
- ilość poziomów w drzewiastej strukturze
- ilość uniklanych wartości klucza
Zbiór powyższych informacji pozwala optymalizatorowi kosztowemu na wybranie optymalnego planu zapytania.
Gromadzenie statystyk
Do gromadzenia statystyk wykorzystuje się wbudowany pakiet DBMS_STATS, za pomocą którego można gromadzić statystyki na poziomie
- pojedynczych obiektów
- schematów
- całej bazy danych
DROP TABLE T1 / CREATE TABLE T1(id NUMBER, status VARCHAR2(100)) / INSERT INTO T1 SELECT ROWNUM, CASE WHEN MOD(ROWNUM, 2) = 0 THEN 'VALID' ELSE 'INVALID' END FROM dual CONNECT BY ROWNUM <= 1000 / COMMIT; / SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len FROM user_tables WHERE table_name = 'T1' /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
-------- ------ ------------ --------- --------- -----------
(null) (null) (null) (null) (null) (null)
W powyższym przykładzie utworzono nową tabelę T1 i nie zebrano statystyk. Widać, że podstawowe informacje o tabeli nie są zwrócone i to, że optytmalizator kosztowy nic nie wie o danych w tej tabeli.
Poniżej definicja metody gather_table_stats
z najczęściej wykorzystywanymi parametrami.
dbms_stats.gather_table_stats( ownname =>o /*varchar2*/, --Nazwa wlsciciela tabeli tabname =>t /*varchar2*/, --Nazwa tabeli estimate_percent =>e /*number*/, --Wspolczynnik probkowania danych wyrazona w procentach np 20, 50, 100 degree =>d /*number*/, --Zrownoleglenie operacji zbierania statystyk cascade =>c /*pl/sql boolean*/, --Zbieranie statystyk indeksow przynaleznych do tabeli );
BEGIN dbms_stats.gather_table_stats( ownname => USER, --Nazwa wlsciciela tabeli tabname => 'T1', --Nazwa tabeli estimate_percent => 100, --Wspolczynnik probkowania danych wyrazona w procentach np 20, 50, 100 degree => 8, --Zrownoleglenie operacji zbierania statystyk cascade => FALSE --Zbieranie statystyk indeksow przynaleznych do tabeli ); END; / SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len FROM user_tables WHERE table_name = 'T1' /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
-------- ------ ------------ --------- --------- -----------
1000 28 0 0 0 11
Zebranie statystyk zadziałało prawidłowo i choć nie wszystkie dane są od razu czytelne, to i tak można z nich już wiele wyczytać. W powyższych wynikach widać, że tabela posiada 1000 rekordów, dane z tych rekordów zajmują 28 bloków pamięci, a średnia długość wiersza wynosi 11.
Szymon