Znudzone procesory, albo krótka rozprawa o blokadach, kolejkach i oczekiwaniu.

Część 2

Bohdan Szymczak

 

W pierwszej części artykułu pokazano na prostym przykładzie, jak oczekiwania na pozyskanie blokady wpływają na obraz obciążenia procesora serwera bazy Oracle. Zjawisko spadku użycia procesora, pomimo aktywnego przetwarzania, dość żartobliwie określono jako stan „znudzenia” – jakkolwiek procesor ochoczo rwie się do pracy, to jednak nie dane jest mu rozwinąć skrzydła i zamiast ciężkiego znoju, aplikacja oferuje mu głównie stan bezczynności. Jednocześnie pokazano, jak złudne może być oparcie się wyłącznie na statystyce utylizacji procesora do oceny stopnia obciążenia serwera bazy danych Oracle.

 

Dział IT odpowiedzialny za utrzymanie aplikacji użytkowych, które obecnie stanowią często podstawę dla działalności biznesowej, musi swoim działaniem gwarantować pracę systemów informatycznych w trybie ciągłym. Poza standardowymi czynnościami, takimi jak: zabezpieczenia na wypadek awarii, czy zarządzanie dostępem do systemów, niezwykle ważne jest utrzymanie stałego poziomu wydajności systemu oraz przewidywanie potrzeb dla przyszłej eksploatacji. Planowanie przyszłych potrzeb w zakresie infrastruktury systemowej nazywane capacity planning, polega na przewidywaniu wymagań na przestrzeń dyskową, konfigurację sprzętową, łącza oraz oprogramowanie w przyszłym horyzoncie czasowym. Do prawidłowego przeprowadzenia tego procesu niezbędna jest, oprócz znajomości planu rozwoju biznesowego, prawidłowa ocena bieżącego stanu pracy systemu. Wracamy tym samym do punktu wyjścia, czyli oceny stopnia dostosowania sprzętu do wymagań aplikacji oraz jakości samej aplikacji, w rozumieniu optymalności wykorzystania przez nią dostępnej mocy maszyny.

Jak zawsze w takim przypadku pojawia się pytanie o sposoby oraz narzędzia, jakich można użyć do diagnostyki systemu. Oczywiście, dostępne są zarówno komercyjne, najczęściej drogie narzędzia, jak również własne sposoby oparte o zapytania do bazy. Pomiędzy tymi dwiema skrajnościami znajduje się doskonałe narzędzie, wchodzące w skład pakietu dostarczanego przez producenta wraz z instalacją bazy Oracle, w postaci pakietu statspack. Narzędzie to jest konsekwentnie rozwijane i dostarcza w kolejnych wydaniach coraz większą i przydatniejszą funkcjonalność. Należy również zauważyć, że kolejne wersje bazy Oracle są wyposażane w mechanizmy diagnostyczne; również podstawowe narzędzie do zarządzania bazą, jakim jest Oracle Enterprise Manager stało się rozsądną alternatywą dla komercyjnych narzędzi innych producentów.

Po starcie instancji Oracle, serwer zapisuje na bieżąco, w trybie przyrostowym wszystkie informacje statystyczne o pracy motoru bazy danych. W trybie on-line są one dostępne poprzez liczne perspektywy systemowe; kilka z nich zostało pokazanych w pierwszej części artykułu. Problem z ich bezpośrednim użyciem polega na tym, iż odczytana w danym momencie wartość jest sumą określonej statystyki w okresie czasu od startu instancji do wykonania zapytania, zatem pozyskana wartość liczbowa nie daje się łatwo zinterpretować. Chcąc ocenić wartość takiej statystyki w interesującym przedziale czasu, na przykład w okresie przetwarzania masowego, należy zapamiętać potrzebne wartości na początku i końcu przetwarzania, zaś do analizy posłużyć się różnicą tych dwóch wartości. Tę cechę wykorzystuje pakiet statspack, który opiera się o własne repozytorium danych, do którego w dowolnym momencie można przenieść wartości statystyk. Taki zrzut danych określony jest mianem migawki (snapshot) i może być wykonany w dowolnym momencie na polecenie użytkownika. Każda migawka jest zapamiętywana w repozytorium i utrzymywana w nim dowolnie długo, do momentu usunięcia przez użytkownika. Największą zaletą pakietu jest produkowany przez niego raport, będący opisem stanu różnicowego bazy danych pomiędzy dwiema, dowolnie wybranymi migawkami. Statspack jest dostarczany w postaci pakietu składowanego w bazie danych, repozytorium tworzy się w schemacie użytkownika perfstat. Całe sterowanie odbywa się z poziomu SQL*Plus, poprzez wywołanie składowanych procedur, zaś raport ma postać pliku tekstowego.

Statspack jest rozwinięciem historycznej już metody analizy pracy bazy, opartej o dwa skrypty UTLBSTAT oraz UTLESTAT. Starsi użytkownicy pamiętają, a młodszym można przypomnieć, że ta metoda polegała na utworzeniu jednokrotnej migawki z danych i porównaniu jej z danymi aktualnymi. Po uruchomieniu pierwszego skryptu UTLB(egin)STAT tworzone były potrzebne tabele i zapamiętywane w nich wartości statystyk z tego momentu. Używano następujących statystyk:

  • v$waitstat
  • v$session
  • v$session_event
  • v$system_event
  • v$rollstat
  • v$filestat
  • v$rowcache
  • v$sysstat
  • v$librarycache
  • v$latch

Uruchomienie drugiego skryptu UTLE(nd)STAT powodowało zapis wartości tych samych statystyk do drugiego kompletu tabel, a następnie utworzenie tabel zawierających wartości różnic z tabel utworzonych przez drugi i pierwszy skrypt. Ostatnim krokiem było utworzenie raportu jako listy selectów z tabel wynikowych. Skrypt UTLESTAT „sprzątał” po sobie, to znaczy wszystkie tabele robocze wraz z zawartością były usuwane, co powodowało, że jego użycie było jednorazowe. Obydwa skrypty są ciągle dostarczane przez Oracle, ale ich znaczenie obecnie jest już niewielkie. Podstawowa wada, jaką był brak możliwości zbierania i gromadzenia danych w dłuższym okresie czasu, połączenie w jednym kroku zbierania danych i tworzenia raportu, oraz niemożność decydowania post factum o punktach, pomiędzy którymi ma być wykonany raport, została wyeliminowana właśnie w pakiecie statspack.

Repozytorium danych tworzone przez statspack zostało wykorzystane jako źródło danych dla Oracle Enterprise Managera w wersji 9i bazy danych, umożliwiając między innymi graficzną prezentację zebranych danych. Jednak, począwszy od wersji 10g bazy, Oracle Enterprise Manager korzysta z osobnego repozytorium danych utrzymywanego przez nowe narzędzie – Automatic Workload Repository. Całość konfiguracji, w tym również włączenie i wyłączenie zbierania danych, odbywa się z poziomu Oracle Enterprise Managera lub za pomocą funkcji dostępnych w pakiecie DBMS_WORKLOAD_REPOSITORY. Eksploatując aplikacje na bazie Oracle 10g, należy – wobec podobnej funkcjonalności – rozważyć zasadność stosowania jednego z tych narzędzi. Wydaje się zasadne, aby docelowo używać funkcjonalności dostarczanej przez Automatic Workload Repository, ze względu na jego większą integrację z bazą danych i orientację producenta na jego dalszy rozwój. Ponieważ raporty wyjściowe z obydwu narzędzi są podobne, zaś obecnie bazy w wersji 9i są ciągle w powszechnym użyciu, w niniejszym opracowaniu zostanie omówiony pakiet statspack, zaś na końcu pokrótce zostaną opisane najważniejsze cechy Automatic Workload Repository.

Przed omówieniem przydatności pakietu statspack do celów oceny stanu bazy i aplikacji oraz wykrywania zjawisk oczekiwania, powodujących stan bezczynności procesora, pokażemy na przykładzie proces instalacji i użycie jego funkcjonalności. Przykład zostanie przeprowadzony na wersji bazy Oracle 10gR2 posadowionej na systemie operacyjnym Windows XP.

Przed instalacją należy zaplanować sposób eksploatacji pakietu, w szczególności określić częstotliwość wykonywania migawek, oraz okres czasu, za jaki mają być przechowywane dane. Pozwoli to określić przestrzeń tabel, w której będzie przechowywane repozytorium danych, oraz zapotrzebowanie na przestrzeń dyskową w planowanym okresie czasu.

Za instalację pakietu odpowiada dostarczony przez Oracle skrypt spcreate.sql znajdujący się w katalogu %ORACLE_HOME%rdbmsadmin. Jak wspomniano powyżej, skrypt instalacyjny zakłada użytkownika perfstat, w schemacie którego będą zbierane dane. Na konto tego użytkownika należy również się logować w celu zrzucenia migawki danych, wykonania raportu, bądź innych dostępnych funkcji.

W celu instalacji należy przy użyciu SQL*Plus zalogować się jako użytkownik sys z prawami SYSDBA:

SQL> conn sys as sysdba
Proszę podać hasło: **********
Połączono.

W drugim kroku należy uruchomić skrypt spcreate:

SQL> @C:oracleproduct10.2.0db_1RDBMSADMINspcreate

Należy wprowadzić hasło dla użytkownika perfstat:

Choose the PERFSTAT user's password
------------
Not specifying a password will result in the installation FAILING
Proszę podać wartość dla perfstat_password: perfstat
perfstat

Kolejnym krokiem jest wybranie przestrzeni tabel, w której zostaną wygenerowane tabele i indeksy schematu perfstat:

Choose the Default tablespace for the PERFSTAT user
----------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
----------------------

EXAMPLE PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Proszę podać wartość dla default_tablespace: SYSAUX

Using tablespace SYSAUX as PERFSTAT default tablespace.

W analogiczny sposób należy wybrać tymczasową przestrzeń tabel dla użytkownika perfstat:

Choose the Temporary tablespace for the PERFSTAT user
-----------------

Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
---------------------
TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Proszę podać wartość dla temporary_tablespace: TEMP
Using tablespace TEMP as PERFSTAT temporary tablespace.

Dalsze działanie skryptu jest już automatyczne:

... Creating PERFSTAT user
... Installing required packages
...
...
...
Creating Package STATSPACK...

Pakiet został utworzony.
Nie ma błędów.

Creating Package Body STATSPACK...

Ciało pakietu zostało utworzone.
Nie ma błędów.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>
SQL> show user
użytkownik to "PERFSTAT"

Jak widać z powyższego przykładu, instalacja jest bardzo prosta. Potrzebne wartości są wprowadzane przez użytkownika w trybie konwersacyjnym, przy czym dla ułatwienia skrypt wypisuje podpowiedzi dla wprowadzanych wartości. Po prawidłowym podaniu wszystkich wymaganych wartości skrypt uruchamia kolejno trzy następujące skrypty: spcusr – zakładający użytkownika perfstat i nadający mu wymagane przywileje w bazie, spctab – zakładający tabele, w których będą gromadzone dane, oraz spcpkg – tworzący pakiet o nazwie STATSPACK, zawierający wszystkie potrzebne do jego pracy procedury i funkcje. Na zakończenie warto jeszcze zajrzeć do plików logów: spcusr.lis, spctab.lis, spcpkg.lis, w celu potwierdzenia poprawności instalacji. Logi są tworzone w katalogu %ORACLE_HOME%BIN.

Zainstalowany pakiet jest gotowy do użycia, w przypadku chęci rezygnacji z jego dalszego stosowania, Oracle dostarcza gotowy skrypt, usuwający zarówno tabele i inne obiekty ze schematu PERFSTAT, jak i samego użytkownika PERFSTAT. Skrypt nosi nazwę spdrop, znajduje się w katalogu %ORACLE_HOME%rdbmsadmin i powinien być wykonany po zalogowaniu na użytkownika sys z prawami SYSDBA.

Jak wspomniano wcześniej, dane ze statystyk systemowych mogą być w dowolnym momencie zapisane w postaci migawki. Wykonuje się to wywołując z pakietu statspack procedurę snap. Procedurę wywołuje się każdorazowo, dla interesującego nas przedziału czasu:

SQL> connect perfstat
Proszę podać hasło: ********
Połączono.
SQL> execute statspack.snap

Procedura PL/SQL została zakończona pomyślnie.

Każda migawka utworzona w ten sposób otrzymuje swój identyfikator; jeżeli jest istotne poznanie jego wartości, na przykład w celu późniejszego jego użycia jako parametru raportu, zamiast procedury można użyć analogicznej funkcji, zwracającej wartość identyfikatora:

SQL> variable Snap_ID number;
SQL> begin :Snap_ID := statspack.snap; end;
2 /

Procedura PL/SQL została zakończona pomyślnie.

SQL> print Snap_ID
SNAP_ID
-----
3

Ilość i szczegółowość zbieranych danych może być różna w zależności od ustawionego poziomu szczegółowości. Dostępna w pakiecie procedura MODIFY_STATSPACK_PARAMETER, umożliwia nie tylko zmianę domyślnego poziomu parametru i_snap_level (poziom szczegółowości, standardowo równy 5), ale również progów wartości granicznych, od których dany parametr jest raportowany.

Warto wspomnieć, że postać raportu, jego funkcjonalność i szczegółowość różnią się w kolejnych wersjach bazy danych, dlatego też warto na wstępie zapoznać się z możliwościami używanej wersji. Szczegółowe informacje są zawsze zawarte w pliku tekstowym spdoc.txt, obecnym w katalogu ze skryptami instalacyjnymi.

Utworzenie raportu obejmującego okres czasu pomiędzy dwiema dowolnie wybranymi migawkami polega na uruchomieniu skryptu spreport, znajdującego się w katalogu %ORACLE_HOME%rdbmsadmin. Utworzony raport w formie pliku tekstowego jest domyślnie generowany w katalogu %ORACLE_HOME%BIN, zaś standardowo jego nazwa jest tworzona na bazie szablonu sp_NrMigawkiPoczątku_NrMigawkiKońca.lst.

Na początku skrypt wyświetla parametry identyfikujące instancję, oraz dostępne migawki:

SQL> @C:oracleproduct10.2.0db_1RDBMSADMINspreport.sql

Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
-------------------
1146033537 ORCL 1 orcl
...
Listing all Completed Snapshots

Instance DB Name Snap Id Snap Started Snap Level Comment
------------------------
orcl ORCL 1 09 Paź 2007 22:37 5
2 09 Paź 2007 22:42 5
3 09 Paź 2007 22:51 5

Po podaniu dwóch wartości: identyfikatora początkowej i końcowej migawki, raport zostaje wygenerowany:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~
Proszę podać wartość dla begin_snap: 2
Begin Snapshot Id specified: 2

Proszę podać wartość dla end_snap: 3

Specify the Report Name
~~~~~~~~~
The default report file name is sp_2_3. To use this name,
press <return> to continue, otherwise enter an alternative.

Proszę podać wartość dla report_name:
...

Jak wspomniano wcześniej, w wersji Oracle10g funkcjonalność pakietu statspack została włączona do systemu bazy danych i może być zarządzana z poziomu Enterprise Managera.

 


Rys. 1. Konfiguracja parametrów dla AWR

 

Jakkolwiek raport generowany z Automatic Workload Repository jest dostępny z poziomu Enterprise Managera w przeglądarce, to dla administratora (analogicznie jak w statspacku) pozostaje cały czas możliwość tworzenia raportu poprzez wywołanie skryptu awrrpt.sql z SQL*Plusa. Dodatkową funkcjonalnością jest tu możliwość wyboru typu raportu – tekstowego lub HTML, który jest szczególnie wygodny w nawigowaniu po dokumencie.

 


Rys. 2. Początek raportu generowanego przez AWR

 


Rys. 3. Fragment raportu zawierający 5 głównych oczekiwań

 

Gotowy raport jest podzielony na sekcje opisujące parametry lub statystyki określonego rodzaju. Ponieważ jednak naszym obszarem zainteresowania są oczekiwania, więc dalej zajmiemy się tylko tymi częściami raportu.

Syntetyczny poziom oczekiwań znajduje się na początku raportu w sekcji oznaczonej jako: „Top 5 Timed Events”, gdzie pokazane jest 5 procesów o najwyższym poziomie oczekiwań. Szczegółowe statystyki oczekiwań pokazane są w kolejnej sekcji „Wait Events”, zawierającej wszystkie zdarzenia oczekiwań, których czas w analizowanym przedziale czasu przekroczył wartość progową. W nowszych wersjach pakietu znajduje się sekcja „Wait Event Histogram” pokazująca rozkład czasów oczekiwań dla poszczególnych zdarzeń, co jest bardzo przydatne do wnioskowania przyczyn oczekiwań. Dodatkowo informacje o oczekiwaniach można również znaleźć w sekcji „Instance Activity Stats”, choć tu są one przemieszane z innymi statystykami. Osobną sekcją przeznaczoną wyłącznie do raportowania oczekiwań na pozyskanie rezerwacji w shared_pool (latcha) jest „Latch Activity”

Powróćmy teraz do przykładu z pierwszej części artykułu i powtórzmy przykład z testu numer 2, zapisując migawkę na początku i końcu testu, a następnie wykonajmy raport z pakietu statspack. Pamiętamy, że test nr 2 polegał na uruchomieniu równoległego przetwarzania w dwóch sesjach, w wyniku którego występowała blokada na poziomie wiersza tabeli. Czas oczekiwania drugiej sesji na pozyskanie blokady wyłącznej wynosił 15 sek. Wykres obciążenia procesorów pokazany został na rysunku nr 4.

 


Rys. 4. Wykres obciążenia procesorów w teście 2

 

W raporcie statspack znajdują się następujące zapisy w sekcji pięciu najdłużej trwających zdarzeń:

Top 5 Timed Events Avg %Total
~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
-----------------------
CPU time 155 90.3
enq: TX - row lock contention 6 15 2501 8.7
os thread startup 2 1 364 .4
control file sequential read 247 0 2 .3
control file parallel write 45 0 4 .1
-----------------------

Jak widać najwięcej czasu trwało aktywne przetwarzanie procesora – 155 s, co odpowiada pracy procedury workload i jest zgodne z oczekiwaniem. Drugim zdarzeniem jest „enq: TX – row lock contention„, czyli oczekiwanie na pozyskanie blokady wyłącznej do wiersza, trwające 15 s, co w odniesieniu do czasu trwania testu (czasu pomiędzy migawkami) stanowiło 8,7%. Wartości te są zgodne z wyliczonymi wcześniej z perspektyw systemowych, co nie budzi zdziwienia, gdyż raport statspack korzysta z tych samych danych źródłowych.

W kolejnej sekcji raportu znajdują się szczegóły tego zdarzenia:

Wait Events DB/Inst: ORCL/orcl Snaps: 11-12
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
-----------------------
enq: TX - row lock contention 6 83 15 2501 0.2

Wracając do głównego nurtu artykułu – wspierając analizę obciążenia procesora chociażby pobieżnym przeglądem raportu statspack, można szybko określić efektywność przetwarzania aplikacji na serwerze bazy danych, sprawdzając poziom oczekiwań na zdarzenia w sekcji „Top 5 Timed Events” i w – przypadku stwierdzenia występowania niepokojących wartości – spróbować zdiagnozować przyczynę przy pomocy dalszych części raportu. Niedoświadczonym administratorom chciałbym jednak w tym miejscu przypomnieć, że poziom oczekiwań jest tylko jednym z parametrów miary efektywności pracy aplikacji i bazy danych.

Decydując się na wdrożenie pakietu statspack do ciągłej diagnostyki systemu najlepiej zdefiniować procesy jobowe wywołujące w określonym interwale czasowym zrzut migawki. Typowym zakresem czasu dla migawki jest 1 godzina, taki też czas jest domyślnie przyjęty dla Automatic Workload Repository w wersji Oracle 10g. By ograniczyć czas codziennej analizy, warto rutynowo generować raporty tylko dla pewnych charakterystycznych okresów czasu, typowych dla określonych aplikacji, np. jeden raport z okresu dnia operacyjnego i jeden z przetwarzań masowych. Jednocześnie opłaca się przechowywać w bazie historyczne migawki przez dłuższy okres np. 6-12 miesięcy. Pozwoli to na porównanie zmian w pracy aplikacji w dłuższym okresie czasu, zaś w razie niespodziewanych zdarzeń, potrzebne dane będą zawsze dostępne. W większości przypadków, zmienność środowiska informatycznego wyklucza użyteczność danych starszych niż 1 rok, dlatego dla oszczędności przestrzeni dyskowej, niepotrzebne już dane należy usuwać. Służy do tego funkcja statspack.purge.

 

Podsumowanie.

  • Pakiet STATSPACK jest wygodnym narzędziem do monitorowania oczekiwań w bazie danych, umożliwiającym w szybki sposób uzyskanie syntetycznych i analitycznych danych o najważniejszych zdarzeniach powodujących oczekiwania.
  • Pakiet STATSPACK wchodzi w skład instalacji bazy danych.
  • Szczegółowość rejestrowanych danych jest parametryzowana.
  • Od wersji bazy 10g równoważna funkcjonalność pakietu jest dostępna poprzez narzędzie Automatic Workload Repository, które może być zarządzane zarówno z poziomu Oracle Enterprise Manager jak i funkcji pakietu DBMS_WORKLOAD_REPOSITORY.

 

Bibliografia:

  1. Bohdan Szymczak – Wydajność systemów bazodanowych Oracle. PLOUG’tki nr 33 i 34
  2. Oracle® Database Performance Tuning Guide 10g Release 1 (10.1). Part No. B10752-01
  3. Oracle® Database Reference10g Release 1 (10.1). Part No. B10755-01
  4. Oracle® Database Concepts 10g Release 1 (10.1). Part No. B10743-01
  5. Statistics Package (STATSPACK) Guide. Note: 394937.1