Strojenie bazy

Autor: Marcin Kasiński
21.01.2011 13:24:00 +0200

Pakiet DBMS_STATS

Do generowania statystyk dla optymalizatora kosztowego wykorzystuje się procedury pakietu DBMS_STATS. Optymalizator kosztowy na podstawie bieżących statystyk generuje plan wykonania zapytań SQL. Ważne jest, aby nie zaniedbać generowania tych statystyk,aby danie w nich się znajdujące odzwierciedlały aktualna charakterystykę obiektów bazy danych. Ogólnie pakiet DBMS_STATS służy do zarządzania tymi statystykami. Można powiedzieć, że jest to swego rodzaju alternatywa dla polecenia ANALYZE. Do utworzenia tabeli na potrzeby statystyk można użyć procedury CREATE_STAT_TABLE.

Parametry tej procedury, to:

Przykład:

DBMS_STATS.CREATE_STAT_TABLE('scott','stats_table');

Do zbierania statystyk należy użyć procedury GATHER_TABLE_STATS pakietu DBMS_STATS.

Parametrami tej funkcji są:

Przykład:

DBMS_STATS.GATHER_TABLE_STATS ('scott','tab1',1, stattab=>'stats_table');

Pakiet STATSPACK

Pakiet STATSPACK jest jednym z kilku mechanizmów Oracle pozwalający na badanie wydajności bazy danych. Można go uznać za pewnego rodzaju udoskonalenie skryptów UTLBSTAT i UTLESTAT. Poza informacjami generującymi przez te skrypty pakiet STATSPACK dysponuje dodatkowymi informacjami.

W celu zainstalowania pakietu należy uruchomić skrypt spcreate.sql znajdujący się w standardowym katalogu ORACLE_HOME/rdbms/admin/. Skrypt ten tworzy specjalnego użytkownika PERFSTAT wraz z obiektami potrzebnymi do badania wydajności i za pomocą tego pakietu. Ze względu na bezpieczeństwo zaraz po uruchomieniu skryptu spcreate.sql należy zmienić hasło użytkownika PERFSTAT, które standardowo ustawiono na PERFSTAT. Wszystkie operacje za pomocą tego pakietu dalej powinny być wykonywane przez użytkownika PERFSTAT. Za pomocą tego pakietu generowane są specjalne migawki zawierające informacje wydajnościowe opisujące stan bazy podczas generowania tej migawki. Możemy generować kilka takich migawek, a następnie za pomocą pakietu STATSPACK możemy porównywać różnice pomiędzy migawkami wydajnościowymi zebranymi w rożnym czasie. Dzieje się tak dlatego, że każda migawka ma swój własny identyfikator, który w tabelach wydajnościowych utworzonych skryptem spcreate.sql grupuje informacje związane z jedna migawka.

Aby wygenerować migawkę należy uruchomić procedurę SNAP pakietu STATSPACK:

execute STATSPACK.SNAP;

Wszystkie parametry związane z generowaniem tej migawki można modyfikować procedura MODIFY_STATSPACK_PARAMETER w przypadku, kiedy nie odpowiadają nam domyślne wartości tych parametrów.

Parametrami tymi są:

Przykład:

execute STATSPACK.MODIFY_STATSPACK_PARAMETER( 
i_snap_level->4, i_modify_parameter->TRUE);

Aby wygenerować raport należy uruchomić skrypt spreport.sql. Ważne jest, aby przed uruchomieniem tego skryptu dokonać analizy schematu STATSPACK za pomocą polecenia:

execute DBMS_UTILITY_SCHEMA('PERFSTAT','COMPUTE');

W celu usunięcia starych niepotrzebnych danych statystycznych należy uruchomić skrypt sppurge.sql. Innym sposobem usunięcia niepotrzebnych danych znajdujących się w tabelach wydajnościowych jest obcięcie tabel za pomocą skryptu sptrunc.sql. W celu odinstalowania pakietu STATSPACK należy wykonać skrypt spdrop.sql.

Śledzenie zapytań - TKPROF

Często w celu poprawiania wydajności aplikacji analizuje się jakie zapytania SQL są generowane w systemie i jak one obciążają system. Dokonuje się tego poprzez włączenie monitorowania instancji, czy tez konkretnej sesji. Śledzenie powoduje zbieranie informacji o liczbie parsowań, wykonań i pobrań.

Ze śledzeniem zapytań SQL związane są następujące parametry instancji:

Włączenie śledzenia bieżącej sesji odbywa się poprzez polecenie:

ALTER SESSION SET SQL_TRACE = TRUE;

Inną opcją jest włączenie śledzenia poprzez procedurę DBMS_SESSION.SET_SQL_TRACE .

Aby włączyć śledzenie sesji innej niż bieżąca należy znać informacje o tej zewnętrznej sesji. Mając nazwę użytkownika tej zewnętrznej sesji informacje te możemy pobrać za pomocą polecenia:

SELECT sid, serial#, osuser FROM v$session WHERE osuser = 'scott';

Teraz mając te dane możemy włączyć lub wyłączyć śledzenie dla tej sesji za pomocą polecenia:

EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, TRUE );

Dwa pierwsze parametry są informacjami pobranymi z poprzedniego zapytania, trzeci parametr przyjmuje wartości TRUE lub FALSE i określa, czy włączamy,czy wyłączamy tą sesje.

W przypadku włączania śledzenia na poziomie całej instancji należy parametr konfiguracyjny SQL_TRACE ustawić na TRUE. Wartością domyślną jest tutaj FALSE. Włączenie śledzenia dla całej instancji powoduje dość duże obciążenie systemu dlatego raczej zaleca się stosowanie śledzenia sesji, a śledzenie całej instancji stosować w wyjątkowych sytuacjach.

Do zapisu danych ze śledzenia w postaci zrozumiałej dla człowieka służy polecenie TKPROF. Składnia tego polecenia ma postać:

TKPROF infile outfile [parametry]

gdzie:

Dla każdego zapytania znajdującego się w raporcie przedstawione są następujące informacje:

Strojenie operacji wejścia-wyjścia

W rozdziale tym zastanowimy się w jaki sposób możemy przyspieszyć operacje wejścia - wyjścia. Warto tu zauważyć iż operacji tych może być bardzo dużo, trwają one zdecydowanie dłużej niż operacje na pamięci i nawet drobne zmiany polepszające wydajność w tym zakresie może skutkować dużą poprawą wydajności.

Parametr DB_BLOCK_SIZE

Parametr ten określa wielkość bloku danych. Jest on bardzo ważny dla strojenia bazy, ponieważ właściwe jego ustawienie pozwoli ograniczyć liczbę niechcianych zjawisk takich jak migracja oraz łańcuchowanie,czy tez przyspieszyć operacje odczytu rekordów bazy danych. Warto tu zauważyć, ze parametr ten jest wykorzystywany podczas tworzenia bazy danych i nie można go zmienić już w trakcie pracy bazy danych. Wielkość tego parametru zależy od wielkości rekordów znajdujących się w bazie danych oraz sposobu dostępu do tych danych. Jeśli mamy do czynienia z systemem DSS, gdzie często mamy do czynienia z przeszukiwaniami całej tabeli wskazane jest, aby wielkość ta była duża, ponieważ wtedy jednorazowo będziemy odczytywali wiekszą porcję danych. Dla systemów OLTP z kolei zaleca się stosowanie mniejszych bloków danych. Większe bloki danych nie przyniosą ta żadnych korzyści. Wręcz przeciwnie mogą powodować pogorszenie wydajności, ponieważ jeśli blok danych będzie większy niż rekord w tabeli system niepotrzebnie będzie odczytywał te dodatkowe dane i niepotrzebnie te dodatkowe dane będą buforowane niepotrzebnie zajmując miejsce w buforze. Zbyt mała wartość tego parametru może spowodować zwiększenie operacji wejścia - wyjścia, ze względu na pojawienie się takich zjawisk jak migracja i łańcuchowanie. Mogą tu się pojawić problemy z zarządzaniem pamięcią ponieważ więcej czasu zajmuje wypełnienie mniejszych bloków w miarę optymalny sposób. Z kolei zbyt duża wartość tego parametru w odniesieniu do wielkości rekordów w bazie może spowodować zwieszenie operacji wejścia - wyjścia, ze względu na to, że bufor zmieści mniej takich bloków i czeskiej będziemy mieli do czynienia ze zwalnianiem miejsca dla nowych bloków w buforze. Zwalnianie to polega na zapisie najstarszych danych z bufora na dysk. Operacje dyskowe są bardzo czasochłonne, więc wydajność może spaść tu dość znacznie. Dodatkowa jedna operacja wejścia - wyjścia powoduje przetwarzanie większej ilości danych. Byłoby to zaleta, gdy w tej porcji danych znajda się tylko i wyłącznie interesujące nas danych. Jeśli parametr będzie zbyt duży podczas jednej operacji wejścia - wyjścia w odczytanej porcji danych znajda się dodatkowo dane nas nieinteresujące, które niepotrzebnie zostały odczytane.

UWAGA: Należy pamiętać, aby wielkość bloku bazodanowego była wielokrotnością bloku systemu operacyjnego.

Bloki o nie standardowej wielkości

Zastrzeżenie dotyczy bazy danych Oracle, mówiące o tym, ze wielkość bloku danych ustawiane jest podczas tworzenia bazy i nie można go zmienić już w trakcie pracy bazy danych dotyczy bazy danych w wersji 8i i niższych. W wersji 9i wprowadzono pojęcie bloków nie standardowej wielkości. Istnieje tu możliwość tworzenia przestrzeni tabel o nie standardowej wielkości bloków danych. W takiej sytuacji wszystkie obiekty umieszczone w tej przestrzeni tabel będą miały bloki danych o nie standardowej wielkości.

Przykład:

CREATE TABLESPACE table_space_dss
DATAFILE 'ts_dss_01.dat' SIZE 300M,
DATAFILE 'ts_dss_02.dat' SIZE 300M
BLOCKSIZE 8K;

CREATE TABLESPACE table_space_oltp
DATAFILE 'ts_oltp_01.dat' SIZE 100M,
DATAFILE 'ts_oltp_02.dat' SIZE 100M
BLOCKSIZE 2K;

Zaletą używania bloków o nie standardowej wielkości jest możliwość łączenia w jednej bazie systemów DSS oraz OLTP.

Migracja rekordów

Migracja rekordów występuje podczas operacji UPDATE. Jeśli aktualizacja rekordu powoduje iż nowy rekord nie mieści się w swoim bloku system musi odnależć nowy blok danych i tam przenieść zaktualizowany rekord. są to operacje, które dodatkowo obciążają procesor i dysk i wskazane jest, aby operacje takie się nie pojawiały lub było ich względnie mało.

Łańcuchy bloków

Łańcuchy bloków występują kiedy rekord bazodanowy znajduje się w więcej niż jednym bloku danych. W takim przypadku odczytanie takiego rekordu zajmuje więcej niż jedna operacje wejścia - wyjścia. Aby sprawdzić, czy w tabeli znajdują się rekordy łańcuchowe wykorzystujemy polecenie ANALIZE. Przed wykonaniem tego polecenia należy stworzyć odpowiednią tabelę, w której znajdą się wyniki polecenia ANALIZE. Dokonujemy tego poleceniem:

CREATE TABLE chained_rows (
owner_name VARCHAR2(30),
table_name VARCHAR2(30),
cluster_name VARCHAR2(30),
partition_name VARCHAR2(30),
subpartition_name VARCHAR2(30),
head_rowid ROWID,
analyze_timestamp DATE
);

Teraz mając już taka tabele stworzoną możemy uruchomić proces analizowania tabeli w poszukiwaniu rekordów łańcuchowych poleceniem:

ANALYZE TABLE tab1 LIST CHAINED ROWS;

Po tej operacji tabela chained_rows zawiera listę rekordów łańcuchowych tabeli tab1. Wskazane jest aby tabela ta była pusta bądż zawierała niewielką liczbę rekordów. Jeśli tych rekordów jest wiele wskazane jest, aby wyeksportować tabele, jeszcze raz przeanalizować wielkość bloku danych oraz utworzyć bazę od nowa. Warto tu zauważyć, ze są takie tabele, w których z założenia spodziewamy się, że będą zawierały rekordy łańcuchowe. Przykładem będą tu rekordy z kolumnami typu LONG lub długimi kolumnami CHAR lub VARCHAR2.

Dynamiczne rozszerzanie

Dynamiczne rozszerzanie jest specjalnym mechanizmem polegającym na automatycznym rozszerzeniu segmentu w przypadku kiedy jego wielkość osiągnie maksimum i należy dodać dodatkowe ekstenty. Operacja ta generuje dodatkowe operacje wejścia - wyjścia oraz dodatkowe systemowe zapytania SQL zwane zapytaniami rekursywnymi, co wpływa na wydajność.

Aby odnależć rekursywne zapytania w systemie należy odczytać dynamiczną perspektywę v$sysstat za pomocą polecenia:

SELECT name, value FROM v$sysstat WHERE name = 'recursive calls';

Rozmiar ekstentu powinien być ustalony w taki sposób, aby ograniczyć liczbę dynamicznych alokacji celem zwiększenia wydajności aplikacji. Innym rozwiązaniem jest monitorowanie wielkości ekstentów za pomocą perspektywy DBA_EXTENTS i kiedy zauważymy, że zbliża się on do wielkości, kiedy jest wykonywane dynamiczne rozszerzanie, możemy tego dokonać ręcznie, kiedy system będzie mniej obciążony, np. w godzinach nocnych.

Parametry PCTFREE oraz PCTUSED

Bardzo często do zmniejszenia liczby operacji wejścia - wyjścia i tym samym do polepszenia wydajności systemu stosuje się parametry PCTFREE oraz PCTUSED klauzuli STORAGE w poleceniach tworzących obiekty bazodanowe. Parametry te służą do zarządzania blokami danych przez system. Balansując pomiędzy rożnymi wartościami tych parametrów możemy w odniesieniu do danego obiektu kłaść większy nacisk na wydajność odwołań do tego obiektu, czy tez na ilość miejsca zajmowanego przez ten obiekt.

Parametr PCTUSED podawany w procentach określa ze do bloku bazodanowego beda dodawane nowe rekordy jesli poziom zajetosci spadnie ponizej tej wartosci. Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości.

Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości. Parametry PCTFREE oraz PCTUSED inaczej są nazywane wskażnikami niskiej i wysokiej wody. Suma tych parametrów nie może być większa niż 100, ale nic nie stoi na przeszkodzie, aby suma ta była mniejsza od 100. Warto tu zauważyć, że im suma ta bardziej zbliża się do 100 lub różnica parametrów PCTFREE oraz PCTUSED zbliża się do 0, tym bardziej obciążany jest system, natomiast przestrzeń dyskowa jest bardziej efektywnie wykorzystywane. Aby zachować umiar pomiędzy wydajnością systemu, a zajetością przestrzeni dyskowej wskazane jest aby pomiędzy tymi parametrami zachować różnice jednego rekordu. Jeżeli na danej tabeli dość często są wykonywane operacje UPDATE zwiększające wielkość rekordu wskazane jest aby parametr PCTFREE ustawić na wysoka wartość (np. 25), a PCTUSED na niską wartość (np. 40). W takiej sytuacji zawsze jest pozostawiona odpowiednia ilość miejsc w rekordzie na ewentualne operacje UPDATE zwiększające objętość tych rekordów.

W przypadku dużej ilości operacji INSERT i operacji UPDATE, które w małym stopniu zwiększają objętość rekordów wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na wartość względnie neutralną (np. 50). W sytuacji, kiedy najważniejsza jest wydajność systemu, a optymalne wykorzystanie przestrzeni dyskowej jest mało istotne wskazane jest aby obydwa te parametry ustawić na niską wartość (np. 30). Taka konfiguracja tych parametrów powoduje dość istotne marnowanie przestrzeni dyskowej, natomiast w znacznym stopniu ograniczamy tu takie zjawiska jak łańcuchowanie, czy też migracja. W sytuacji, kiedy najważniejsza jest optymalne wykorzystanie przestrzeni dyskowej, a wydajność systemu jest mniej istotne wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na dość dużą wartość (np. 90). W takiej sytuacji dbamy o maksymalne wykorzystywanie przestrzeni dyskowej, natomiast musimy zdać sobie sprawę z tego, że będą tu występowały takie zjawiska jak łańcuchowanie czy też migracja.

 powrót

Zachęcam do przedstawienia swoich uwag i opinii w polu komentarzy.

Komentarze

Dodaj Komentarz

Temat:

Treść

Podpis / Twój adres email: