Kalibrowanie systemu przy użyciu DBMS_STATS

Paweł Barut
pawel@barut.info

Autor jest projektantem systemów bazodanowych. Posiada tytuł Oracle Certified Professional DBA 8 oraz 9i, a także wieloletnie praktyczne doświadczenie z bazami danych Oracle.

Najważniejszym modułem bazy danych Oracle jest silnik wykonujący zapytania SQL. Sercem motoru SQL jest optymalizator planu wykonania. W przeszłości był to optymalizator regułowy, który począwszy od wersji 7 jest stopniowo wypierany przez optymalizator kosztowy. Ostatecznie w Oracle 10g optymalizator regułowy został wyeliminowany całkowicie. Zadaniem każdego administratora Oracle jest dbanie o prawidłowe działanie systemu, a w szczególności optymalną pracę silnika SQL. Jeśli korzystamy z optymalizatora kosztowego, to musimy dbać o regularnie liczenie statystyk dla tabel i indeksów. Jest to niezbędne do określenia prawidłowego planu wykonania dla zapytań.

Jednakże po zainstalowaniu bazy danych Oracle, niezależnie od tego czy działa na nowoczesnym serwerze, czy starym PC, jest ona domyślnie tak samo skonfigurowana. Aby Oracle prawidłowo działał, należy go odpowiednio dostroić do sprzętu, na którym jest zainstalowany. Przeważnie jest to żmudny proces dobierania parametrów bazy danych, np. OPTIMIZER_INDEX_COST_ADJ, czy DB_MULTIBLOCK_READ_COUNT, które pozwalają wskazać optymalizatorowi właściwsze ścieżki dostępu do danych. W wersjach wcześniejszych niż 9i koszt zapytań był wyznaczany wyłącznie w oparciu o szacowany koszt operacji wejścia/wyjścia. Od wersji 9i optymalizator szacuje koszt zapytania, uwzględniając także dostępną moc procesora. Aby szacunki były prawidłowe, należy "poinformować" Oracle'a na jakim sprzęcie został zainstalowany. W tym celu w Oracle 9i został wprowadzony mechanizm wyznaczania Statystyk Systemowych.

Dlaczego kalibracja jest ważna

Optymalizator kosztowy, podczas określania planu wykonania dla zapytania, wyznacza koszt pojedynczej operacji I/O oraz koszt przetwarzania danych przez CPU. Domyślna instalacja Bazy Danych Oracle'a nie ma określonej charakterystyki konkretnego sprzętu, na którym została zainstalowana. Dlatego optymalizator przyjmuje uśrednione wartości dla kosztu operacji I/O oraz wydajności procesora. Na parametry te można wpływać modyfikując ustawienia wspomnianych wcześniej parametrów: OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING czy DB_MULTIBLOCK_READ_COUNT.

Jednakże zmiana tych parametrów wymaga czasochłonnego procesu, polegającego na:

zmianie parametrów,
restarcie bazy danych,
testowaniu bazy z nowymi parametrami.

Proces ten jest czasochłonny i często opiera się na dotychczasowym doświadczeniu administratora.

Baza danych Oracle 9i ma możliwość dostosowania planu wykonania do konkretnego sprzętu. W tym celu konieczne jest określenie charakterystyki dla:

średniego czasu odczytu pojedynczego bloku (single block readtime in ms)
średniego czasu wieloblokowego (multiblock readtime in ms)
taktowanie procesora w MHz (cpu speed in mhz)
średniej liczby bloków czytanych w trybie wieloblokowym (average multiblock_read_count in number of blocks)

Oczywiście, nikt nie wymaga, aby takie parametry ustawiać ręcznie; choć jest to także możliwe. Do wyznaczenia charakterystyki sprzętu używana jest procedura
GATHER_SYSTEM_STATS zawarta w pakiecie DBMS_STATS.

Jak skalować system

Aby statystyki były adekwatne do konkretnego systemu, muszą być zebrane podczas typowej aktywności serwera. Dotyczy to zarówno bazy danych, jak i innej aktywności systemu operacyjnego. Wynika to z faktu, iż inne procesy także wykorzystują CPU, co wpływa na czas dostępności CPU dla procesów Oracle'a. Także operacje wejścia/wyjścia generowane przez inne procesy wpływają na czasy dostępu do danych procesów Oracle'a.

Aby uruchomić zbieranie należy najpierw przygotować tabele, w której przechowywane będą statystyki:

begin

dbms_stats.create_stat_table

( ownname => 'SYSTEM'

, stattab => 'MY_STAT_TAB');

end;

/

Oczywiście możliwe jest zapamiętanie statystyk w tabelach słownikowych Oracle'a.

Statystyki systemowe są zbierane w określonym okresie czasu. Można uruchomić statystyki, odczekać odpowiedni okres czasu i ręcznie zakończyć zbieranie statystyk, lub podczas startu określić okres, przez który będą zbierane statystyki.

Tak więc startujemy:

begin

DBMS_STATS.gather_system_stats (

gathering_mode => 'START',

interval => null,

stattab => 'MY_STAT_TAB',

statid => 'ST1',

statown => 'SYSTEM');

end;

/

W tabeli przechowującej statystyki pojawiają się dwa rekordy. Możemy sprawdzić aktualny status zebranych statystyk:

SQL> select STATID, C1, C2

2 from MY_STAT_TAB

3 where C1 is not null;

STATID C1 C2 ------- ---------------- ------------------ ST1 MANUALGATHERING 09-18-2004 14:55

Po upłynięciu okresu czasu, przez który były zbierane statystyki, kończymy zbieranie statystyk wykonując:

begin

DBMS_STATS.gather_system_stats (

gathering_mode => 'STOP',

interval => null,

stattab => 'MY_STAT_TAB',

statid => 'ST1',

statown => 'SYSTEM');

end;

Ponowne kontrolujemy status zebranych statystyk:

SQL> select STATID, C1, C2

2 from MY_STAT_TAB

3 where C1 is not null;

STATID C1 C2 ---------- -------------------- --------------------- ST1 COMPLETED 09-18-2004 14:55

Teraz możemy zweryfikować zebrane dane przy użyciu procedury DBMS_STATS.GET_SYSTEM_STATS:

declare

status varchar2(20);

dstart date;

dstop date;

pvalue number;

begin

dbms_stats.get_system_stats(status, dstart, dstop

, pname => 'cpuspeed'

, pvalue => pvalue

, stattab => 'MY_STAT_TAB'

, statid => 'ST1'

, statown => 'SYSTEM');

dbms_output.put_line('status : '||status);

dbms_output.put_line('cpu [MHz] : '||pvalue);

end;

/

status : COMPLETED

cpu [MHz] : 334

Jako parametr pname można podać:

cpuspeed - wyliczone taktowanie w MHz,
sreadtim - średni czas odczytu bloku w milisekundach,
mreadtim - średni czas odczytu wielu bloków,
mbrc - średnia liczba bloków w przy odczycie wielu bloków,
maxthr - maksymalna przepustowość systemu I/O w bajtach na sekundę,
slavethr - średnia przepustowość I/O dla odczytów parallel slave w bajtach na sekundę.

Jeśli chodzi o wyliczoną częstotliwość taktowania procesora, to niezbyt to odpowiada rzeczywistej wartości. W rzeczywistości procesor taktowany był 1400 MHz. Wyliczona wartość bardziej odpowiada częstotliwości głównej magistrali. Inne materiały firmy Oracle mówią, że parametr zawiera liczbę cykli procesora wyrażoną w milionach.

Może się zdarzyć, że wartość statystyki będzie miała wartość -1. Oznacza to, że podczas analizy nie było możliwe wyliczenie charakterystyki. Np. baza danych nie wygenerowała żadnej operacji odczytu wielu bloków (multiblock read), przez co nie było możliwe wyliczenie tego parametru. W takim przypadku należy powtórzyć operację zbierania statystyk, i dodatkowo wydłużyć czas. Taki przypadek raczej nie ma prawa wystąpić w bazie produkcyjnej o dużym obciążeniu.

Inną metodą zbierania statystyk jest wywołanie gather_system_stats z parametrem gathering_mode ustawionym na wartość 'INTERVAL', oraz określeniem czasu zbierania statystyk w parametrze interval (czas podany w minutach):

begin

DBMS_STATS.gather_system_stats (

gathering_mode => 'INTERVAL',

interval => 120,

stattab => 'MY_STAT_TAB',

statid => 'ST2',

statown => 'SYSTEM');

end;

/

Spowoduje to uruchomienie procesu zbierania statystyk, a dodatkowo zostanie wstawione zadanie (JOB), które wykona za nas zakończenie zbierania statystyk w odpowiednim momencie czasu:

SQL> select to_char(next_date, 'HH24:MI') next_date, what from user_jobs

NEXT_ ------- WHAT ---------------------------------------- 17:15

dbms_stats.gather_system_stats(gathering_mode => 'AUTO_STOP', stattab => 'MY_STAT_TAB', statid => 'ST2', statown => 'SYSTEM');

Proszę tylko pamiętać o sprawdzeniu parametru JOB_QUEUE_PROCESSES - musi mieć wartość większą od zera.

Jak użyć statystyk systemowych

Statystyki, które zostały zebrane do utworzonej tabeli, nie mają wpływu na działanie optymalizatora. Aby poinformować optymalizator o wyliczonych statystykach, należy zaimportować je do słownika systemowego. Można to zrobić instrukcją:

begin

dbms_stats.import_system_stats (

stattab => 'MY_STAT_TAB'

, statid => 'ST1'

, statown => 'SYSTEM');

end;

/

Powoduje to skopiowanie wyliczonych statystyk, które są zapisane w tabeli SYSTEM.MY_STAT_TAB, pod identyfikatorem ST1 do słownika systemowego Oracle, co powoduje, że będą uwzględniane przez optymalizator.

Istnieje także możliwość zmodyfikowania dowolnej ze statystyk. W tym celu używamy procedury set_system_stats, np:

begin

dbms_stats.set_system_stats

(pname => 'cpuspeed'

,pvalue => 333);

end;

/

powoduje to ustawienie szybkości procesora na 333 miliony cykli na sekundę. Procedura set_system_stats może także dokonać modyfikacji statystyk zawartych w tabeli statystyk, poprzez określenie opcjonalnych parametrów: stattab, statid, statown.

Sprawdźmy, jak wyglądają obecnie statystyki zawarte w słowniku systemowym:

SQL> select pname, pval1

2 from sys.aux_stats$

3 where sname = 'SYSSTATS_MAIN';

PNAME PVAL1 ---------- -------- CPUSPEED 339 MAXTHR 3067904 MBRC 16 MREADTIM 49,569 SLAVETHR -1 SREADTIM 21,34

6 wierszy zostało wybranych.

Przydatne może się także okazać przeniesienie statystyk ze słownika systemowego Oracle do własnej tabeli ze statystykami. W tym celu należy zastosować procedurę export_system_stats.

Jak statystyki wpływają na optymalizator kosztowy

No dobrze, mamy zebrane statystyki systemu, ale jaki jest ich wpływ na działanie optymalizatora?

Oracle podczas tworzenia planu wykonania zapytania rozważa różne możliwe ścieżki wyboru danych. Decyduje, czy wykonać przeszukanie całej tabeli (Table Full Scan), czy szukać wg klucza w indeksie. Statystyki, które odpowiadają rzeczywistym parametrom systemu, na którym jest zainstalowana baza Oracle, pozwalają na dobranie właściwej ścieżki dostępu do danych.

Wpływ poszczególnych parametrów jest następujący:

SREADTIM - duża wartość tej statystyki w stosunku do MREADTIM powoduje, że optymalizator bardziej preferuje przeszukiwanie całej tabeli (Full Scan), niż korzystanie
z indeksu,
MBRC - większa wartość powoduje większe preferencje dla przeszukiwania całej tabeli,
CPUSPEED - większa wartość powoduje, że optymalizator wybiera takie operacje, które mogą być wykonane przez procesor, minimalizując odczyty dyskowe, np. preferuje HASH_JOIN, lub operacje sortowania w pamięci.

Sprawdźmy to w praktyce

Najpierw dla pewności usuwamy statystyki systemowe.

begin

dbms_stats.delete_system_stats;

end;

/

Wykonajmy poniższy skrypt, który utworzy przykładowe tabele i wypełnijmy je danymi. Na koniec zostaną wyliczone statystyki dla tabel, aby mógł zadziałać optymalizator kosztowy.

create table my_small (id number(10) primary key, tekst varchar2(100));

create table my_big (sm_id number(10) references my_small(id), tekst_big varchar2(100));

create index my_big_i on my_big (sm_id);

declare

i integer;

j integer;

begin

for i in 1..50 loop

insert into my_small values(i, 'SMALL '||to_char(i));

for j in 1..100 loop

insert into my_big values(i, 'BIG '||to_char(j));

end loop;

end;

/

begin

dbms_stats.gather_table_stats

(USER, 'MY_BIG', null,

DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,

'FOR ALL INDEXED COLUMNS SIZE 1',

DBMS_STATS.DEFAULT_DEGREE,

'ALL', TRUE, null, null, null, FALSE);

dbms_stats.gather_table_stats

(USER, 'MY_SMALL', null,

DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,

'FOR ALL INDEXED COLUMNS SIZE 1',

DBMS_STATS.DEFAULT_DEGREE,

'ALL', TRUE, null, null, null, FALSE);

end;

/

Zobaczmy jak wygląda plan zapytania dla przykładowego zapytania na tych tabelach:

select * from my_small, my_big

where sm_id = id

and id < 5

and tekst_big = 'BIG 10';

i plan wykonania dla tego zapytania:

SELECT STATEMENT Optimizer Mode=CHOOSE

HASH JOIN

TABLE ACCESS BY INDEX ROWID PAWEL.MY_SMALL

INDEX RANGE SCAN PAWEL.SYS_C0054927

TABLE ACCESS BY INDEX ROWID PAWEL.MY_BIG

INDEX RANGE SCAN PAWEL.MY_BIG_I

Jednak, aby zaprezentować wpływ statystyk systemowych proponuję, aby plan zapytania generować przy użyciu poniższego zapytania:

select

substr(lpad(' ',(level-1)*2)||operation||' '||options||' ',1,50) OPERATION,

object_name ,

cost,

CPU_COST,

IO_COST,

ACCESS_PREDICATES ||' '||FILTER_PREDICATES PREDICATES

from plan_table

start with id = 0 and STATEMENT_ID = :ST_ID

connect by prior id = parent_id and prior STATEMENT_ID = STATEMENT_ID

order by id;

W przypadku braku statystyk systemowych zapytanie zwróci:

W powyższym zapytaniu wartość 5 została tak dobrana, aby wymusić korzystanie z indeksów w przypadku braku statystyk systemowych. Po zmianie o jeden (z 5 na 6) plan zapytania wygląda inaczej - występuje pełne przeglądanie tabeli MY_BIG. W przypadku innej instalacji bazy Oracle, inne wartości mogą być graniczne.

Należy zwrócić uwagę, że w przypadku braku statystyk systemowych, do wyliczenia kosztu zapytania nie jest brany pod uwagę koszt CPU, a jedynie koszt operacji I/O.

Teraz importujemy statystyki, które zostały wcześniej wyliczone i ponownie generujemy plan wykonania. Oto efekt:

Zwróćmy uwagę, że w tym przypadku została zmieniona kolejność wyszukiwania danych w tabelach i na podstawie innej tabeli jest tworzona tablica hash'ująca. Proszę zwrócić uwagę, że spadł nieco koszt operacji I/O, oraz został wyznaczony koszt CPU (liczba proporcjonalna do liczby potrzebnych cykli procesora)

Uwaga! Efekt jest zależny od konkretnej instalacji Oracle - na innej maszynie efekt może nie być widoczny, lub będzie inny.

Zróbmy jeszcze mały eksperyment. Przekonajmy Oracla, że dysponujemy bardzo słabym procesorem:

begin

dbms_stats.set_system_stats

(pname => 'cpuspeed'

,pvalue => 1);

end;

/

Teraz plan wykonania wygląda tak:

W tym przypadku Oracle zmienił całkowicie plan wykonania zapytania tak, aby maksymalnie zmniejszyć koszt CPU. W efekcie wzrosła ilość operacji I/O i przez to wzrósł koszt I/O.

Po wykonaniu powyższego eksperymentu należy jak najszybciej przywrócić rzeczywiste wartości statystyk, gdyż wartości eksperymentalne mogą prowadzić do spowolnienia działania bazy.

Zastosowania praktyczne

Aby uzyskać największe korzyści ze statystyk systemowych należy regularnie wyliczać ich wartości i porównywać, czy nie zmieniły się znacząco w stosunku do obecnie obowiązujących. W przypadku zaobserwowania znacznych zmian, należy rozważyć podmianę na nowe wartości.

Jeżeli nasza baza pracuje w kilku trybach, np. w ciągu dnia działa jako baza OLTP, a w nocy wykonywane jest na niej masowe przetwarzanie danych, to trzeba rozważyć zebranie osobnych zestawów statystyk systemu. Następnie należy zaimplementować przełączanie, np. poprzez importowanie odpowiedniego zestawu statystyk do pracy OLTP i pracy wsadowej. Ponieważ obydwa te tryby pracy charakteryzują się inną specyfiką wykonywanych zapytań, optymalizator kosztowy będzie lepiej dobierał plan wykonania w każdym z trybów pracy bazy danych.

Oczywiście, aby osiągnąć zamierzony efekt, baza danych musi pracować w trybie optymalizatora kosztowego, oraz muszą być regularnie analizowane tabele i liczone histogramy.

Co się zmieniło w 10g?

W bazie danych Oracle 10g są niewielkie zmiany w obsłudze statystyk systemowych. Jedyną nowością są nowe statystyki:

ioseektim - średni czas odszukania danych w milisekundach, który jest wyznaczany jako czas wyszukania (seek time) + opóźnienie i narzut systemu operacyjnego,
iotfrspeed - I/O transfer speed - określa, ile danych może być odczytanych w pojedynczym żądaniu - liczba określa liczbę przesłanych bajtów na milisekundę,
cpuspeednw - częstotliwość taktowania procesora w trybie bez obciążenia ('NOWORKLOAD').

Nowe statystyki wynikają ze zmian w konstrukcji optymalizatora i mają na celu bardziej efektywne jego działanie.

Podsumowanie

Statystyki systemowe nie są powszechnie używane, często są pomijane w procesie optymalizacji serwera Oracle. O ile w wersji 9i mają one znaczny wpływ na optymalizator, to w wersji 10g, która nie posiada optymalizatora regułowego, dbanie o właściwe wartości statystyk jest krytyczne dla poprawnego działania. Statystyki systemowe odzwierciedlają rzeczywiste zachowanie systemu, na którym jest zainstalowana baza danych, przez co są nieocenioną wskazówką dla optymalizatora, jak najefektywniej wykorzystać możliwości sprzętu. Dzięki nim Oracle lepiej rozkłada obciążenie pomiędzy procesor, a system I/O. Dodatkowo optymalizator może także zmieniać kolejność obliczania warunków tak, aby zminimalizować czas CPU.

Dodaj swój komentarz

wróć

Do góry

Drukuj