Wykład dodatkowy

Architektura i dostrajanie bazy danych na przykładzie serwera bazy danych Oracle

 

Streszczenie

Na ostatnich wykładach czytelnik zapoznał się z podstawami architektury i działania systemu zarządzania bazą danych. Na tym wykładzie przedstawimy, jak te ogólne zasady wyglądają w konkretnym przypadku – mianowicie systemu Oracle.

W pierwszej części jest przedstawiony zarys architektury systemu zarządzania bazą danych Oracle. W drugiej części jest omówione wprowadzenie do problematyki dostrajania bazy danych Oracle.

 


12.1 Architektura bazy danych

W tym punkcie podamy podstawowe informacje na temat budowy systemu zarządzania bazą danych Oracle nazywanego serwerem bazy danych Oracle.

Serwer bazy danych Oracle składa się z:

1. bazy danych czyli plików na dysku zawierających tabele z danymi i inne obiekty bazy danych oraz pomocnicze struktury danych. Każda baza danych ma swoją nazwę.

2. instancji (bazy danych) czyli oprogramowania operującego na bazie danych. Mówiąc dokładniej, instancja jest to zbiór procesów i wspólna pamięć umożliwiająca użytkownikom korzystanie z bazy danych. Najpierw uruchamia się instancję a następnie podłącza się do niej bazę danych. Każda instancja może działać tylko na jednej bazie danych. Natomiast na jednej bazie danych może operować wiele instancji. Na jednym komputerze może być założone wiele baz danych i działać jednocześnie wiele instancji. Każda instancja jest identyfikowana przez swój identyfikator np. domyślny identyfikator  w Oracle ORCL.
 

Rodzaje pików

Serwer bazy danych Oracle używa następujących rodzajów plików bazy danych i instancji.

  1. Plik inicjalizacyjny instancji (np. initORCL.ora) – z parametrami instancji.
  2. Plik kontrolny bazy danych – z podstawowymi informacjami o bazie danych jak: nazwa, data utworzenia, nazwy fizycznych plików.
  3. Pliki bazy danych (z danymi) - każdy z nich przydzielony do jednej z przestrzeni tabel (pojęcie przestrzeni tabel zostanie omówione poniżej). Segmenty wycofań (czyli dziennik wycofań) są zapisywane w plikach bazy danych.
  4. Pliki kopii zabezpieczającej bazy danych - uzyskane albo przez skopiowanie plików bazy danych w systemie plików albo przez eksport całej lub części bazy danych wykonany z programu administracyjnego Oracle.
  5. Pliki dziennika powtórzeń i zarchiwizowanego dziennika powtórzeń - historia operacji na stronach (blokach) plików bazy danych.
  6. Pliki śladu – historia działania procesów instancji bazy danych.

Przestrzenie tabel

Obiekty w bazie danych Oracle są podzielone na logiczne jednostki przechowywania danych na dysku nazywane przestrzeniami tabel. Przestrzeń tabel to struktura pośrednia między strukturą logiczną (tabelami, indeksami) a fizyczną (plikami danych). Z jednej strony, jednej przestrzeni tabel odpowiada jeden lub więcej obiektów w bazie danych, jak np. tabela, indeks, procedura. Z drugiej strony, jednej przestrzeni tabel jest przyporządkowany jeden lub więcej plików systemu plików.

Każda baza danych Oracle zawiera jedną,  "systemową" przestrzeń tabel o nazwie SYSTEM. Ta przestrzeń tabel zawiera słownik danych (w postaci tabel i perspektyw), definicje wszystkich składowanych procedur i pakietów, a także wszystkich wyzwalaczy bazy danych. Jest zalecane, aby nie zawierała żadnych tabel i indeksów tworzonych przez użytkowników.
 

Struktura przestrzeni tabel

Jeden obiekt bazy danych należy zwykle do jednej przestrzeni tabel i jego zawartość może być zapisana w więcej niż jednym pliku fizycznym w systemie plików.

Podstawową jednostką fizyczną zapisu danych jest blok danych (strona). Jego rozmiar jest ustalany przy tworzeniu bazy danych i musi być wielokrotnością rozmiaru bloku systemu operacyjnego.

Ekstent jest określoną liczbą położonych obok siebie na dysku bloków danych – uzyskiwanych do zapisu danych w wyniku jednej alokacji i przeznaczonych do zapisu określonego typu informacji.

Segment jest zbiorem ekstentów alokowanych dla jednego obiektu bazy danych. Każda tabela i każdy indeks mają swój segment, w którym są zapisywane ich dane. System dynamicznie przydziela miejsce na dysku, gdy bieżące ekstenty w segmencie zostaną wypełnione. System automatycznie dodaje nowy ekstent do istniejącego segmentu. Ekstenty w ramach segmentu nie muszą być położone obok siebie na dysku.

Przykłady użycia przestrzeni tabel w SQL

1. Instrukcja zakładająca przestrzeń tabel:

CREATE TABLESPACE Human_resources
DATAFILE 'd:\orant\logbase\tabspace_f1.dat' SIZE 20M
EXTENT MANAGEMENT DICTIONARY;

Określona została nazwa pliku do przechowywania obiektów zapisywanych w tej przestrzeni tabel i jego rozmiar. EXTENT MANAGEMENT DICTIONARY określa opcję zarządzania alokacją ekstentów za pomocą słownika danych. Opcja ta daje możliwość dokładniejszego określenia procesu alokacji, który opiszemy poniżej. O drugiej opcji tzw. lokalnym zarządzaniu alokacją ekstentów będzie powiedziane w dalszej części wykładu.

2. Instrukcja zakładająca tabelę i indeks główny w określonych przestrzeniach tabel:

CREATE TABLE Salgrade(
  Grade NUMBER CONSTRAINT PK_salgrade PRIMARY KEY
               USING INDEX TABLESPACE Ind_ts,
  Losal NUMBER,
  Hisal NUMBER)
TABLESPACE Human_resources;

3. Instrukcja zakładająca konto użytkownika z określeniem dwóch przestrzeni tabel: domyślnej - na dane i tymczasowej - do przeprowadzania roboczych operacji jak sortowanie:

CREATE USER Filip -- lub ALTER USER
   IDENTIFIED BY xyz
  DEFAULT TABLESPACE Human_resources --
domyślnie SYSTEM
   TEMPORARY TABLESPACE Temp -- domyślnie SYSTEM
   QUOTA 10M ON Cases_ts
  QUOTA 5M ON Temp;
Parametry fizyczne składowania przestrzeni tabel

Ustawienia dotyczące alokacji ekstentów specyfikuje klauzula STORAGE określająca parametry przechowywania danych na dysku. Oto jej schemat:

STORAGE (
 INITIAL l.całkowita
 NEXT l.całkowita
 MINEXTENTS l.całkowita
 MAXEXTENTS l.całkowita
 PCTINCREASE l.całkowita)

gdzie:

1. INITIAL to rozmiar w bajtach pierwszego alokowanego ekstentu (domyślnie 5 bloków);

2. NEXT to rozmiar w bajtach następnego alokowanego ekstentu (domyślnie 5 bloków);

3. PCTINCREASE to procent, o jaki wzrasta rozmiar kolejno alokowanego ekstentu po drugim (domyślnie 50%);

4. MINEXTENTS to całkowita liczba ekstentów alokowanych przy tworzeniu segmentu (domyślnie 1, dla segmentów wycofań 2);

5. MAXEXTENTS to maksymalna liczba ekstentów, jakie może przydzielić system dla segmentu obiektu.

Oto przykład:

CREATE TABLE Dept(
 Deptno NUMBER(2),
 Dname VARCHAR2(14),
 Loc VARCHAR2(13) )
STORAGE(INITIAL 100K NEXT 50K
        MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 5)
);

Oracle alokuje miejsce na dysku dla tej tabeli w następujący sposób: 

1. MINEXTENTS=1, więc dla tabeli Dept jest tworzony segment złożony z dokładnie jednego ekstentu.

2. INITIAL=100K, więc rozmiar pierwszego ekstentu wynosi 100 kilobajtów.

3. NEXT=50K, więc gdy przepełni się pierwszy ekstent, następny alokowany ekstent będzie miał rozmiar 50 kilobajtów.

4. PCTINCREASE=5, więc każdy kolejno alokowany ekstent będzie miał rozmiar większy o 5%. Zakładając, że rozmiar bloku danych wynosi 2 kilobajty, trzeci alokowany ekstent będzie mieć rozmiar 52 kilobajty, co stanowi zaokrąglenie wartości 50KB powiększonej o 5%.

5. MAXEXTENTS=50, więc maksymalnie można alokować do segmentu tabeli Dept łącznie 50 ekstentów.

Oracle alokuje odpowiedni segment z ekstentami, gdy obiekt jest tworzony. Gdy cała przydzielona pamięć zostanie zużyta, Oracle dynamicznie dodaje nowe ekstenty (co zwiększa czas działania – nowe ekstenty mogą być położone w innej części dysku).
 

Parametry fizyczne składowania tabeli

Podamy teraz rozszerzoną postać instrukcji CREATE TABLE. Obejmuje ona parametry związane z fizycznym zapisem danych na dysku.
 
CREATE TABLE nazwa_tabeli(
nazwa_kolumny typ_danych [DEFAULT wyrażenie] [więzy_kolumny], ...
więzy_tabelowe ...)
PCTFREE l_całkowita /* opcjonalne od tego miejsca */
PCTUSED l_całkowita
INITRANS l_całkowita
MAXTRANS l_całkowita
TABLESPACE przestrzeń_tabel
STORAGE klauzula_STORAGE
CLUSTER klaster (kolumna, ...)
DISABLE nazwa_więzów ....
CACHE
AS podzapytanie;

Znaczenie klauzuli STORAGE omówiliśmy powyżej. Wyjaśnimy teraz znaczenie parametrów PCTFREE, PCTUSED, INITRANS i MAXTRANS.
 

Parametry PCTFREE i PCTUSED

Ustawienia stopnia zajętości miejsca w blokach jest związane z pytaniem, kiedy dany blok ma się znaleźć na liście wolnych bloków, to znaczy bloków mających wolne miejsce do wpisania nowego wiersza.

Dla każdego segmentu (tabeli, klastra, indeksu) Oracle utrzymuje jedną lub więcej list wolnych bloków, w których jest miejsce do zapisu wierszy wpisywanych przez kolejne instrukcje INSERT. Oprócz tego w każdym bloku pozostawia się trochę wolnego miejsca na zwiększające się rozmiary zapisanych w nim wierszy jako rezultat UPDATE. Ustala się mianowicie dwa parametry:

Parametry PCTFREE i PCTUSED umożliwiają ''dostrojenie'' gospodarki blokami pamięci dyskowej do aplikacji - co może zwiększyć jej szybkość. Oto przykład użycia parametrów PCTFREE i PCTUSED przy tworzeniu tabeli:

CREATE TABLE Zamówienia( ........)
   PCTFREE 20
   PCTUSED 60;

Przykłady ustalania wartości parametrów PCTFREE i PCTUSED

1. Często są wykonywane instrukcje UPDATE zwiększające rozmiar wierszy.
PCTFREE= 20, PCTUSED=40
Ustawienie PCTFREE na 20 umożliwia pozostawianie dość miejsca na wiersze, których rozmiar wzrasta przy UPDATE. Ustawienie PCTUSED na 40 powoduje, że wykonuje się mniej przetwarzania przy UPDATE.
 

2. Często wykonywane są instrukcje INSERT i DELETE oraz UPDATE - które nie zwiększają rozmiaru wierszy.
PCTFREE= 5, PCTUSED=60
Ponieważ instrukcje UPDATE nie zwiększają rozmiaru wierszy, ustawienie PCTFREE na 5 jest dobre. Ustawienie PCTUSED na 60 powoduje szybsze użycie miejsca zwalnianego przez DELETE. Jednocześnie przetwarzanie jest zminimalizowane.
 

3. Tabela jest bardzo duża, więc pamięć jest krytycznym czynnikiem. Większość realizowanych transakcji to transakcje tylko z odczytem danych z tej tabeli.
PCTFREE=5, PCTUSED=90
Liczba operacji UPDATE jest mała, więc ustawienie PCTFREE na 5 jest poprawne. Ustawienie PCTUSED na 90 powoduje, że więcej miejsca w bloku zostaje użyte do zapisu danych - zmniejszając liczbę bloków potrzebnych do zapisu tabeli i zmniejszając liczbę bloków, które trzeba sprowadzać do pamięci wewnętrznej przy wykonywaniu instrukcji SELECT.
 

Migracja i łańcuchowanie wierszy

Gdy UPDATE zwiększa rozmiar wiersza tak, że nie mieści się już w bloku, Oracle szuka innego bloku wystarczająco dużego do pomieszczenia tego wiersza. Jeśli znajdzie taki blok, przenosi do niego wiersz z pozostawieniem wskaźnika do niego w starym miejscu - nazywa się to migracją wiersza. Jeśli nie znajdzie, dzieli wiersz na kawałki i zapisuje je w różnych blokach - nazywa się to łańcuchowaniem wierszy. W obu przypadkach powoduje to pogorszenie czasu działania aplikacji. Obu operacjom można zaradzić:

  1. dobierając odpowiednie wartości dla parametrów PCTUSED i PCTFREE, 
  2. usuwając i wstawiając od nowa zmigrowane lub podzielone na łańcuchy wiersze,
  3. ewentualnie, zwiększając rozmiar bloku.

Warto także wiedzieć, że NULL jest reprezentowany przez jeden bajt w zapisie wiersza tylko, jeśli po danej pozycji w wierszu występują pozycje nie NULL. Zatem opłaca się, kolumnę, w której może być dużo wartości NULL, umieszczać jako ostatnią w schemacie tabeli – jej reprezentacja nie będzie wtedy zajmować żadnego miejsca na dysku w przypadku wystąpienia NULL.
 

Zapis w bloku z danymi informacji o transakcjach

Informacja o transakcjach, które aktualnie korzystają z wierszy przechowywanych w danym bloku (przed COMMIT lub ROLLBACK), jest zapisywana w nagłówku bloku w pozycji Pozycje transakcji. Wielkość tej pozycji określają dwa parametry podawane przy tworzeniu tabeli:

Opcja CACHE

Jest możliwość przechowywania w buforze pamięci RAM wierszy tabel (np. słownikowych), które są często używane przez procesy użytkowników. Nie powinny one tylko zajmować zbyt dużo miejsca. Opcję CACHE określa się za pomocą klauzuli CACHE przy definiowaniu tabeli. Oto przykład:

CREATE TABLE Dzien(Numer INTEGER, Nazwa VARCHAR2(11))
.....
CACHE;
 

Automatyczne zarządzanie miejscem w przestrzeni tabel

W najnowszych wydaniach serwera Oracle jest dostępna wersja przestrzeni tabel, w której alokacja ekstentów i zarządzanie wolnymi blokami odbywa się automatycznie, bez potrzeby specyfikowania parametrów klauzuli STORAGE i parametrów PCTFREE, PCTUSED itd.

CREATE TABLESPACE data02 
DATAFILE '/u01/oradata/data02.dbf' SIZE 5M 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

gdzie:

Automatyczne zarządzanie plikami

Również zarządzanie plikami (tworzenie, zwiększanie rozmiaru, usuwanie) można zlecić systemowi w ramach opcji OMF (Oracle Managed Files). W pilku inicjalizacyjnym jako wartość parametru  DB_CREATE_FILE_DEST, podaje się tylko katalog, w którym mają być tworzone pliki z danymi. Tworzenie przestrzeni tabel znacznie się upraszcza:

CREATE TABLESPACE apps2_data;

Przebudowa indeksu

Przy usuwaniu pozycji indeksu następuje zaznaczenie pozycji jako usuniętej i fizyczne jej usunięcie ze strony dopiero w chwili, gdy wszystkie pozycje indeksu zapisane na tej stronie zostaną usunięte. Co jakiś czas należy powtórnie przebudować od początku indeks, w ten sposób usuwając fizycznie wszystkie jego pozycje zaznaczone jako usunięte:

ALTER INDEX Prac_nazw_idx REBUILD;
 

Tabele z partycjami

W przypadku bardzo dużych tabel wskazane jest równoległe wykonywanie operacji. Aby to umożliwić, trzeba podzielić tabelę na części, które będą mogły być przetwarzane równolegle - niezależnie od siebie. Ułatwia to także odtwarzanie danych w przypadku awarii jednej z przestrzeni tabel. Tabela z partycjami stanowi odejście od zasady, że każdy obiekt bazy danych jest zapisany w jednej przestrzeni tabel.

CREATE Klienci(
 Id NUMBER(5) PRIMARY KEY,
 Kraj CHAR(2), ......)
 PARTITION BY RANGE (Kraj) 
-- klucz partycji: najlepiej aby nie ulegał zmianie
  (PARTITION p1 VALUES LESS THAN ('C')
   TABLESPACE Data01;
 PARTITION p2 VALUES LESS THAN ('I')
   TABLESPACE Data02;
  ...
 PARTITION p19 VALUES LESS THAN MAXVALUE
  TABLESPACE Data19);
Partycje są też określane dla indeksów (klucz partycji = klucz indeksu):
CREATE INDEX Ind_Klienci_Kraj ON Klienci(kraj)
LOCAL(PARTITION p1 TABLESPACE Ind01;
....
PARTITION p19 TABLESPACE Ind19);

12.2 Architektura instancji bazy danych

System zarządzania bazą danych Oracle używa struktur danych w pamięci RAM i procesów do zarządzania i organizacji dostępu do danych w bazie danych. Procesy są realizowane w oparciu o te struktury pamięci. Oto podstawowe terminy:

Struktury pamięci

Podstawową strukturą danych instancji jest Globalny Obszar Systemowy, w skrócie SGA – obszar wspólnej pamięci, który zawiera dane i informacje kontrolne dla jednej instancji Oracle. Oracle tworzy strukturę SGA przy uruchamianiu instancji.

Użytkownicy połączeni w danej chwili z serwerem Oracle korzystają wspólnie z danych znajdujących się w SGA. Aby uzyskać optymalną wydajność, obszar SGA powinien być możliwie największy, tak aby mógł pomieścić jak najwięcej danych, minimalizując czas dostępu do pamięci dyskowych. Informacja przechowywana w SGA jest podzielona na kilka rodzajów struktur pamięci w tym pulę buforów bazy danych, bufor dziennika powtórzeń i obszar współdzielony. Obszary te są tworzone podczas uruchamiania instancji.
 

Pula buforów bazy danych w SGA zawiera używane ostatnio strony z danymi z bazy danych (także segmenty wycofań). W buforze bazy danych mogą występować zmodyfikowane dane, które jeszcze nie zostały przepisane na stałe na dysk.
 

Bufor dziennika powtórzeń w SGA zawiera dziennik zmian wykonanych ostatnio na bazie danych. Pozycje dziennika zapisane w buforze dziennika powtórzeń są przepisywane do plików dziennika powtórzeń. Rejestrowane zmiany dotyczą wszystkich możliwych zmian zachodzących w buforze bazy danych, a więc dotyczących zarówno tabel, indeksów, jak i segmentów wycofań.
 

Obszar współdzielony w SGA zawiera obszary pamięci wspólnie używane przez procesy, w tym współdzielony obszar SQL i bufor słownika danych. We współdzielonym obszarze SQL odbywa się przetwarzanie każdej instrukcji SQL dostarczonej do wykonania na bazie danych. Współdzielony obszar SQL zawiera informacje takie, jak drzewa analizy składniowej i plany wykonania instrukcji SQL. Pojedynczy, współdzielony obszar SQL jest używany przez wiele aplikacji, które przekazują do wykonania tę samą instrukcję – oszczędza to pamięć i czas. Oto ogólny schemat serwera bazy danych Oracle.

Rys. 12.1 Architektura serwera Oracle
 

W skład obszaru SGA wchodzą jeszcze kolejki zleceń do bazy danych i odpowiedzi z bazy danych używane przez procesy serwera wykonujące instrukcje SQL w imieniu procesów użytkownika (patrz niżej).
 

Globalny obszar programowy, w skrócie PGA, jest obszarem pamięci wewnętrznej, który zawiera informacje o danych i informacje sterujące dla procesów serwera realizujących instrukcje SQL (np. zmienne z pakietów używanych przez użytkownika). Dla każdego procesu serwera w chwili jego uruchamiania jest tworzony osobny obszar PGA.
 

Są dwa typy procesów: procesy użytkowników i procesy systemu. W systemie klient/serwer procesy użytkowników i systemu są wykonywane na oddzielnych komputerach.
 

Proces użytkownika (klienta) jest tworzony i utrzymywany do wykonania kodu programu aplikacyjnego (np. programu napisanego w języku Pro*C) lub narzędzia Oracle (np. Oracle Enterprise Manager). Proces użytkownika zarządza także komunikacją z procesami serwera. Procesy użytkowników komunikują się z procesami serwera za pomocą specjalnego interfejsu Oracle, którym jest program sieciowy Oracle Net.
 

Procesy systemu – są wywoływane przez inne procesy w celu wykonania funkcji na rzecz wywołujących je procesów. Są dwóch rodzajów: procesy serwera (usługowe, ang. server processes) i procesy tła (drugoplanowe, ang. background processes).
 

Procesy serwera (usługowe) – są tworzone przez system do obsługi zleceń od zgłaszających się przez sieć procesów użytkowników. Na przykład, jeśli użytkownik zgłasza zapotrzebowanie na dane, których nie ma w danej chwili w puli buforów bazy danych w SGA, realizujący to żądanie proces serwera odczytuje właściwe bloki danych z dysku i zapisuje je w SGA.

Proces serwera może zostać skonfigurowany na dwa sposoby albo jako proces dedykowany przeznaczony do obsługi żądania jednego procesu użytkownika albo jako proces współdzielony pobierający kolejne zgłoszenia do wykonania z kolejki zleceń.
 

Procesy tła (drugoplanowe) są to stałe procesy tworzone przez Oracle dla każdej instancji przeznaczone do wykonywania rutynowych zadań systemu zarządzania bazą danych. Procesy tła działając "w tle" wykonują asynchronicznie operacje wejścia/wyjścia i monitorują inne procesy Oracle dostarczając zwiększonego poziomu równoległego wykonywania operacji i w ten sposób zwiększając wydajność systemu.

Poniżej znajduje się opis działania poszczególnych procesów tła.

Dyspozytor (D, ang. Dispatcher) jest to proces używany przy stosowaniu opcji procesów współdzielonych. Każdy dyspozytor jest odpowiedzialny za przekazywanie zleceń od połączonych procesów użytkowników do kolejki zleceń, z której są one pobierane przez dostępne procesy współdzielone serwera oraz za zwracanie wyników/odpowiedzi do odpowiednich procesów użytkowników.
 

Pisarz bazy danych (DBWR, ang. Database Writer) przepisuje zmodyfikowane strony danych z bufora bazy danych w SGA do plików na dysku. Ze względu na sposób wykonywania zapisów do dziennika powtórzeń, DBWR nie musi zapisywać stron w chwili, gdy transakcja kończy się zatwierdzeniem, co pozwala minimalizować liczbę zapisów na dysk. DBWR zapisuje strony na dysk w zasadzie tylko wtedy, kiedy inne dane muszą zostać sprowadzone do SGA i za mało jest wolnego miejsca w buforze, aby je pomieścić.
 

Pisarz dziennika (LGWR, ang. Log Writer) zapisuje pozycje dziennika powtórzeń na dysk. Dane dziennika powtórzeń są tworzone w buforze dziennika powtórzeń w SGA. 

W chwili gdy transakcja zostaje zatwierdzona lub gdy bufor dziennika powtórzeń się zapełni, LGWR zapisuje zawartość tego bufora do pliku dziennika powtórzeń razem z informacją COMMIT i kolejnym numerem tej transakcji. Dopiero w chwili przesłania zawartości bufora dziennika powtórzeń do plików dziennika powtórzeń uważa się transakcję za zatwierdzoną z możliwością odtworzenia stanu bazy danych w przypadku awarii serwera lub dysku. Dziennik powtórzeń jest tylko jeden, więc może się zdarzyć, że nie zatwierdzone jeszcze zmiany wprowadzone przez inne transakcje także zostaną zapisane na dysk. W przypadku konieczności odtworzenia stanu bazy danych zmiany te trzeba będzie wycofać korzystając z zapisów w dzienniku powtórzeń dotyczących segmentów wycofań. Działania procesów tła DBWR i LGWR są niezależne od siebie.
 

Punkt kontrolny (CKPT, ang. Checkpoint). W pewnych specjalnych sytuacjach, jak zapełnienie i zmiana pliku, do którego przepisuje się zawartość bufora dziennika powtórzeń bądź jak zamykanie bazy danych - zawartość wszystkich zmodyfikowanych buforów bazy danych w SGA jest zapisywana na dysk - nazywa się to punktem kontrolnym. Proces CKPT jest odpowiedzialny za powiadamianie procesu DBWR o punkcie kontrolnym oraz za zapis informacji o punkcie kontrolnym (o kolejnym jego numerze) do plików z danymi i plików kontrolnych bazy danych – powiadamiając w ten sposób, że na dysku stan bloków z danymi jest zsynchronizowany ze stanem dziennika powtórzeń. CKPT jest opcjonalny, gdy go nie ma, DBWR przejmuje jego funkcje. Wykonanie punktu kontrolnego ułatwia wykonanie odtworzenia w przypadku awarii systemu - poprzez ograniczenie wielkości analizowanego dziennika powtórzeń.
 

Monitor systemowy (SMON, ang. System Monitor) wykonuje odtwarzanie instancji przy uruchamianiu instancji. W systemie bazy danych z wieloma instancjami SMON w jednej instancji może wykonać odtwarzanie innych instancji, jeśli uległy one awarii. SMON dokonuje też kompaktyfikacji wolnych obszarów na dysku (ekstentów) w celu uspójnienia wolnych obszarów i ułatwienia kolejnych alokacji pamięci dyskowej.
 

Monitor procesów (PMON, ang. Process Monitor) – wykonuje odtwarzanie procesu, gdy proces użytkownika ulegnie awarii. PMON odpowiada za zwalnianie zasobów, których używał ten proces. PMON kontroluje także działanie procesów dyspozytorów i procesów serwera i wznawia je, jeśli zostały zawieszone.
 

Archiwizator (ARCH, ang. Archiver) to proces, który w chwili zapełnienia plików dziennika powtórzeń kopiuje ich zawartość na nośnik archiwizacji.
 

Procesy kolejek zadań (SNPn, n=0,..,9, A,...,Z, ang. Job Queues) to procesy wykonujące stałe zadania zaplanowane do wykonywania przez system, jak odświeżanie migawek czy wykonywanie zadań tworzonych za pomocą pakietu DBMS_JOB. Różnią się od innych procesów tła tym, że ich awaria nie powoduje awarii całej instancji. W przypadku awarii zostają automatycznie podniesione przez system.
 

Odtwarzacz (RECO, ang. Recoverer) – zajmuje się odtwarzaniem transakcji rozproszonych w przypadku awarii sieci lub systemu.
 

Blokada (LCK, ang. Lock) jest to proces obsługujący blokady między instancjami w środowisku serwera równoległego (Oracle Parallel Server).
 

Procesy monitorowania kolejek (QMNn, ang. Queue Monitors) są używane w opcji kolejkowania komunikatów (Oracle AQ).

Procesy tła mogą być albo osobnymi procesami systemu operacyjnego (jak w Unix), albo wątkami jednego procesu (tak jest w Windows NT).

Ilustracja jak działa serwer Oracle

1. Instancja Oracle działa na jednym komputerze.

2. Aplikacja kliencka działa na drugim komputerze (stacji klienckiej) jako proces użytkownika. Aplikacja kliencka ustanawia połączenie z instancją za pomocą programu sieciowego Oracle Net.

3. Oprogramowanie sieciowe Oracle Net działające po stronie serwera wykrywa żądanie przychodzące z aplikacji i tworzy dedykowany proces działający na serwerze w imieniu procesu użytkownika.

4. Użytkownik wykonuje instrukcję SQL i zatwierdza transakcję. Na przykład, użytkownik zmienia adres osoby w jednym z wierszy tabeli Pracownicy.

5. Proces serwera otrzymuje instrukcję i sprawdza, czy we  współdzielonym obszarze SQL nie jest zapisana identyczna instrukcja SQL. Jeśli jest, proces serwera sprawdza uprawnienia użytkownika do żądanych danych i poprzednio używany obszar SQL zostaje jeszcze raz użyty do przetworzenia instrukcji; jeśli nie ma identycznej instrukcji, zadaniu zostaje przydzielony obszar pamięci w ramach współdzielonego obszaru SQL, w którym instrukcja SQL zostaje poddana analizie składniowej i przetworzona.

6. Proces serwera znajduje wszystkie potrzebne wartości danych lokalizując je od razu w SGA albo sprowadzając je z plików z danymi na dysku.

7. Proces serwera modyfikuje dane w SGA. Proces DBWR zapisze zmodyfikowane strony na stałe na dysk dopiero wtedy, kiedy będzie to konieczne (np. w czasie punktu kontrolnego). Ponieważ transakcja zostaje zatwierdzona, proces LGWR natychmiast zapisuje zmiany dokonane przez transakcję do pliku dziennika powtórzeń.

8. Gdy transakcja kończy się pomyślnie, proces serwera wysyła odpowiedni komunikat poprzez sieć do aplikacji. Gdy nie kończy się pomyślnie, przesyła odpowiedni komunikat o błędzie.

9. W czasie całej tej procedury, procesy tła działają w tle, obserwując czy czasem nie zaszły warunki wymagające ich interwencji. Dodatkowo, serwer bazy danych zarządza transakcjami wszystkich użytkowników i rozwiązuje konflikty między transakcjami żądającymi tych samych danych.

Oprogramowanie sieciowe Oracle Net stanowi mechanizm dostarczający interfejsu z protokołami komunikacyjnymi używanymi w sieciach. Serwer bazy danych Oracle komunikuje się ze stacjami klienckimi, jak i z innymi serwerami baz danych Oracle za pomocą tego oprogramowania.

 


12.3 Dostrajanie bazy danych

Ustalanie parametrów inicjalizacyjnych instancji

Jest wiele czynników, które wpływają na działanie aplikacji na bazie danych. Należą do nich odpowiednio dobrane wartości parametrów inicjalizacyjnych – zapisywane w tekstowym pliku inicjalizacyjnym instancji o nazwie np. initORCL.ora. Bieżące wartości tych parametrów można uzyskać albo bezpośrednio odczytując plik inicjalizacyjny albo odczytując za pomocą programu SQL*Plus perspektywę słownika danych v$parameter. Perspektywy słownika danych z prefiksem v$ są trzymane w pamięci wewnętrznej i dotyczą aktualnego stanu instancji i bazy danych. Nazywają się dynamicznymi perspektywami słownika danych.

Dla przykładu podajemy kilka wybranych parametrów inicjalizacyjnych:

Optymalizacja operacji wejścia/wyjścia

Krytyczną operacją serwera bazy danych jest przesłanie bloku między pamięcią wewnętrzną a dyskiem. Zmniejszenie liczby tych operacji powoduje przyśpieszenie działania aplikacji.

Dobór odpowiednich indeksów ma kluczowe znaczenie dla szybkości działania aplikacji. Problem ten został już dokładnie omówiony na poprzednich wykładach.

Oprócz odpowiednich indeksów, jednym ze sposobów przyśpieszających działanie jest rozłożenie różnych obiektów bazy danych (tabele, indeksy, segmenty wycofań) do różnych przestrzeni tabel – różnych od przestrzeni SYSTEM, a z kolei różnych przestrzeni tabel do różnych stacji dyskowych. Wtedy współbieżne operacje dyskowe będą mogły być wykonywane równolegle.
 

Generowanie statystyk

Jest możliwość generowania i analizy statystyk dotyczących zapisu danych na dysku. Do tego celu stosuje się instrukcję ANALYZE.

ANALYZE 
{INDEX indeks | TABLE tabela | CLUSTER klaster}
{COMPUTE STATISTICS | /* oblicza dokładne statystyki */
ESTIMATE STATISTICS /* oblicza przybliżone statystyki */}

W wyniku wykonania instrukcji ANALYZE z opcją COMPUTE STATISTICS zostają obliczone następujące statystyki dla danej tabeli:

Statystyki te są dostępne poprzez perspektywy słownika danych takie, jak USER_TABLES i USER_TAB_COLUMNS. Korzysta z nich również moduł optymalizatora zapytań.

Oto sposób generowania skryptu Skrypt_gen_statystki.sql, do wykonywania instrukcji ANALYZE w odniesieniu do tabel i indeksów użytkownika ERWIN:

SET PAGESIZE 0
-- wyłącz nagłówki
SET FEEDBACK OFF
-- wyłącz wypisywanie liczby zwracanych wierszy
SET ECHO OFF
-- wyłącz powtarzanie wypisywania poleceń na ekran
SPOOL Skrypt_gen_statystki.sql
SELECT 'ANALYZE TABLE ' || Owner || '.' || Table_name || ' ESTIMATE STATISTICS SAMPLE 20%;'
FROM All_tables
WHERE Owner = Upper('ERWIN');
SELECT 'ANALYZE INDEX ' || Owner || '.' || Index_name || ' COMPUTE STATISTICS;'
FROM All_indexes
WHERE Owner = Upper('ERWIN');
SPOOL OFF
Wynikiem wykonania tego skryptu jest skrypt o następującym zawartości:
ANALYZE TABLE ERWIN.BONUS ESTIMATE STATISTICS SAMPLE 20%;
ANALYZE TABLE ERWIN.DEPT ESTIMATE STATISTICS SAMPLE 20%;
ANALYZE TABLE ERWIN.DUMMY ESTIMATE STATISTICS SAMPLE 20%;
ANALYZE TABLE ERWIN.EMP ESTIMATE STATISTICS SAMPLE 20%;
ANALYZE TABLE ERWIN.SALGRADE ESTIMATE STATISTICS SAMPLE 20%;
ANALYZE INDEX ERWIN.IND_EMP_DEPTNO COMPUTE STATISTICS;
ANALYZE INDEX ERWIN.IND_EMP_ENAME COMPUTE STATISTICS;
 

Oglądanie planu wykonywania instrukcji SQL

Istnieje możliwość obejrzenia planu wykonywania instrukcji SQL, który wybiera system. W planie wykonania zapytania zostają ustalone:

Analizując plan, administrator lub programista aplikacji może znaleźć przyczynę zbyt wolnego działania aplikacji. Do tego celu służy następująca instrukcja:

EXPLAIN PLAN [SET Statement_Id = tekst]
[INTO tabela]
FOR instrukcja;

która podaje plan wykonania zamieszczonej instrukcji SQL (SELECT, INSERT, DELETE lub UPDATE). Każdy krok tego planu jest opisany przez jeden wiersz w podanej tabeli. Domyślnie jest to tabela Plan_table, tworzona przez skrypt UTLXPLAN.SQL.

Najpierw należy wykonać instrukcję EXPLAIN PLAN, w której określamy instrukcję, której plan wykonania chcemy poznać, np.

EXPLAIN PLAN SET Statement_Id = 'Nasza_instr' FOR
  SELECT a.Ename, b.Dname
  FROM Emp a, Dept b
  WHERE a.Deptno = b.Deptno;

W rezultacie, plan wykonania instrukcji SELECT znajduje się już w tabeli Plan_table. Trzeba go tylko odczytać, na przykład za pomocą następującej instrukcji SELECT, która formatuje wypisywanie zawartości tabeli Plan_table (można też skorzystać z gotowego skryptu UTLXPLS.SQL):

SELECT Lpad(' ',2*(Level-1))|| Level || '.'|| Position || ' '||Operation || ' ' || Options || ' ' || Object_Name || ' '||Object_Type "Plan wykonania zapytania"
 FROM Plan_table
 CONNECT BY PRIOR Id = Parent_Id AND Statement_Id = 'Nasza_instr'
 START WITH Id=0 AND Statement_Id='Nasza_instr';

Jeśli na kolumnie Deptno w tabeli Emp będzie założony indeks o nazwie IND_EMP_DEPTNO, otrzymamy następujący wynik:

Plan wykonania zapytania
-----------------------------------------------------
1. SELECT STATEMENT
  2.1 NESTED LOOPS
    3.1 TABLE ACCESS FULL DEPT
    3.2 TABLE ACCESS BY ROWID EMP
      4.1 INDEX RANGE SCAN IND_EMP_DEPTNO NON-UNIQUE
oznaczający sekwencyjne przejrzenie (3.1) wszystkich wierszy z tabeli Dept i dla każdego działu znalezienie (4.1) przez indeks - identyfikatorów wierszy (wartości typu ROWID) dla wszystkich pracowników, zatrudnionych w tym dziale, a następnie w oparciu o znalezione identyfikatory wierszy, sprowadzenie (3.2) samych wierszy tych pracowników.

Znaczenie poszczególnych sformułowań z powyższego planu wykonania zapytania wyjaśnia poniższa tabelka (dołączamy także kilka innych opcji używanych przez optymalizator):
 
OPERACJA               ZNACZENIE
--------------------- --------------------------------------------------------------------------------------
NESTED LOOPS           W pętli uzgadnianie dwóch zestawów wierszy.
TABLE ACCESS FULL      Odczytywanie kolejno wierszy z tabeli.
TABLE ACCESS BY ROWID  Odczytanie wiersza o danym identyfikatorze ROWID.
INDEX RANGE SCAN       Przekazanie jednego lub więcej identyfikatorów ROWID będącego(ych) wynikiem przeglądania indeksu.
INDEX UNIQUE SCAN      Wyszukanie pojedynczego ROWID przez indeks.
SORT JOIN              Sortowanie wierszy przed MERGE JOIN.
MERGE JOIN             Scalenie zbiorów wierszy (wcześniej uporządkowanych).
SORT GROUP BY          Sortowanie dzielące wiersze na grupy GROUP BY.
SORT AGGREGATE         Obliczanie wiersza dla grupy wierszy GROUP BY.
SORT UNIQUE            Sortowanie eliminujące duplikaty wierszy.
SORT ORDER BY          Sortowanie ORDER BY.
HASH JOIN              Haszowanie przy złączaniu.
FILTER                 Selekcja wierszy względem warunku.
UNION                  Wykonanie UNION.
REMOTE                 Sprowadzanie danych z odległej bazy danych.
 
Uwaga: Ustawiając:

SET AutoTrace ON
otrzymujemy plan i statystyki wykonania dla każdej instrukcji realizowanej poprzez SQL*Plus.

Jest możliwość przekazywania w komentarzach wskazówek (hints) do optymalizatora, jaką strategię ma zastosować w danej sytuacji, na przykład, czy ma zastosować indeks, czy nie:
 
SELECT /*+INDEX(Klienci Kraj)  - użyj indeksu Kraj w tabeli Klienci */

SELECT /*+FULL(Emp)  - przejdź całą tabelę Emp */

SELECT /*+USE_NL(Dept) - użyj algorytmu Nested Loops Join z tabelą Dept jako wewnętrzną tabelą złączenia*/

SELECT /*+USE_MERGE(Emp Dept) - użyj metody Sort-Merge Join do złączenia tabel Emp i Dept */

SELECT /*+USE_HASH(Emp Dept) - użyj metody Hash Join do złączenia tabel Emp i Dept */

 
Istnieje możliwość zapamiętania wygenerowanego planu wykonania zapytania i użycia go w przyszłości. Tworzymy mianowice tak zwany zarys (ang. outline) tego planu o nazwie Salaries za pomocą następującej instrukcji umieszczając go w kategorii Special:

CREATE OUTLINE Salaries FOR CATEGORY Special
ON SELECT Ename, Sal FROM Emp;

Za każdym razem, gdy ustawimy w czasie sesji

ALTER SESSION SET USE_STORED_OUTLINES = 'SPECIAL'

zapamiętany plan wykonania zapytania będzie użyty przez optymalizator zapytań do wykonania tego zapytania.
 

Oglądanie zawartości Współdzielonego Obszaru SQL

Można wyświetlić wszystkie instrukcje SQL, które w danej chwili znajdują się we Współdzielonym Obszarze SQL. Informacja jest zawarta w dynamicznej perspektywie słownika danych v$SQLarea. Perspektywę tę można użyć, na przykład, w następującym skrypcie:

COL SQL_Text FORMAT A80
SELECT SQL_Text
 FROM v$SQLarea
 WHERE Lower(SQL_Text) LIKE Lower('&Tekst'||'%');

gdzie jako Test możemy podać np. SELECT.

Należy starać się używać identycznie zapisanych instrukcji SQL, co można osiągnąć poprzez oparcie aplikacji na wyzwalaczach i procedurach/funkcjach/pakietach zapisanych w bazie danych (wielokrotne użycie instrukcji). Rozmiar Współdzielonego Obszaru SQL powinien być dostatecznie duży, aby pomieścić zapytania najczęściej używane w aplikacjach działających na bazie danych – zgodnie z zasadą: parsuj raz, wykonuj wiele razy.
 

Monitorowanie użycia bazy danych

Instrukcja

AUDIT rodzaj_operacji, ... ;
powoduje włączenie śledzenia operacji wykonywanych przez użytkowników bazy danych. Dla każdej operacji ORACLE tworzy rekord kontrolny zawierający:

Na przykład:

AUDIT UPDATE TABLE, DELETE TABLE;
AUDIT SELECT ON Scott.Emp;

Dodatkowe wskazówki optymalizacyjne

Aby zmniejszyć sumaryczny czas oczekiwania przez użytkowników na zasoby:

Podsumowanie dostrajania bazy danych Oracle

Poprawienie parametrów działania bazy danych w Oracle można uzyskać przede wszystkim poprzez:

  1. przygotowanie poprawnego schematu bazy danych (z ewentualną, świadomie przeprowadzoną denormalizacją w uzasadnionych przypadkach);
  2. poprawną ogólną organizację poziomu fizycznego: klastry, indeksy, rozłożenie plików do różnych stacji dyskowych, zastosowanie architektury wieloprocesorowej;
  3. ustalenie odpowiednich parametrów zapisu w przestrzeniach tabel (pomocą może być instrukcja ANALYZE);
  4. ustalenie odpowiednich parametrów zapisu rekordów w blokach takich, jak PCTFREE i PCTUSED;
  5. ustalenie odpowiednich parametrów alokacji ekstentów do segmentów takich, jak parametry klauzuli STORAGE;
  6. dobranie odpowiednich wartości parametrów inicjalizacyjnych instancji;
  7. dobranie odpowiednich instrukcji SQL poprzez:
    • analizę planu wykonywania przez system instrukcji SQL (jest możliwe zamieszczanie wskazówek dla optymalizatora),
    • oglądanie zawartości Współdzielonego Obszaru SQL i standaryzację zapisu instrukcji;
  8. wybór odpowiedniego poziomu izolacji realizacji transakcji (np. czy ma być zapewniana własność szeregowalności czy nie).
 

Program administracyjny Oracle

 


12.4 Podsumowanie

W wykładzie 12 przedstawiliśmy architekturę konkretnego SZBD, mianowicie Oracle. Zapoznaliśmy się z rozszerzeniami SQL w Oracle wspomagającymi administrowanie i dostrajanie bazy danych Oracle.

 


12.5 Słownik pojęć

przestrzeń tabel - struktura pośrednia w SZBD Oracle między strukturą logiczną (tabelami, indeksami) a fizyczną (plikami danych). Przy tworzeniu obiektu bazy danych ustala się przestrzeń tabel, w której dany obiekt (np. tabela) będzie przechowywany. Natomiast do przestrzeni tabel przypisuje się pliki systemu operacyjnego, w których system będzie przechowywać obiekty zaliczone do naszej przestrzeni tabel.

ekstent  - w Oracle określona liczba położonych obok siebie na dysku bloków danych – uzyskiwanych do zapisu danych w wyniku jednej alokacji i przeznaczonych do zapisu określonego typu informacji.

segment - w Oracle zbiór ekstentów alokowanych dla jednego obiektu bazy danych.

instancja bazy danych - kolekcja procesów i struktur pamięci używanych do zarządzania dostępem do bazy danych.

proces serwera (usługowy) – proces instancji bazy danych tworzony przez system do obsługi zleceń od zgłaszających się przez sieć procesów użytkowników.

proces tła (drugoplanowy) - stały proces tworzony przez system przeznaczony do wykonywania rutynowych zadań systemu zarządzania bazą danych jak np. zapisywanie zmienionych stron z danymi na dysk. Procesy tła działając "w tle" wykonują asynchronicznie operacje wejścia/wyjścia i monitorują inne procesy Oracle dostarczając zwiększonego poziomu równoległego wykonywania operacji i w ten sposób zwiększając wydajność systemu.

EXPLAIN PLAN - instrukcja SQL w Oracle umożliwiająca oglądanie planu wykonywania instrukcji SQL.

AUDIT - instrukcja SQL w Oracle umożliwiająca monitorowanie użycia bazy danych przez użytkowników.

 


12.6 Zadania

I. Dla bazy danych rezerwacji hotelowych ułóż zapytanie, wypisujące nazwiska aktualnych gości hotelu razem z numerem pokoju, w którym przebywają (złączenie tabel gości, rezerwacji i pokoi).

1. Przy założeniu, że w bazie danych nie ma żadnych indeksów z wyjątkiem założonych na kluczach głównych, przeanalizuj plan wykonania zapytania otrzymany w wyniku zastosowania instrukcji EXPLAIN PLAN.

2. Załóż indeksy na klucze obce i sprawdź czy plan wykonania zapytania zmieni się.

II. Ułóż zapytanie, wypisujące numer w pokoju, w którym został zameldowany gość o nazwisku 'Kowalski'.

1. Przy założeniu takim jak w I.1.

2. Załóż indeksy na klucze obce oraz indeks na kolumnie Nazwisko w tabeli gości i sprawdź, czy plan wykonania zapytania zmieni się.

III. Dany jest schemat bazy danych:
 

Pracownicy(Nazwisko, Kierownik (FK), Zarobki (IE))

Udziały(Nazwisko, Numer, Rola)

Projekty(Numer, Nazwa (IE), Budżet)

Ułóż zapytanie SQL wyznaczające wszystkich pracowników zaangażowanych w projekcie 'Wyprawa na Mars'. Dla każdego pracownika ma być wypisywane jego nazwisko, zarobki i jego rola w projekcie. Podaj efektywny plan wykonania tego zapytania przez system, zakładając, że dla kluczy głównych i obcych są założone indeksy na B-drzewach. Czy klaster (na B-drzewie bądź haszowany) (z jakim indeksem?) spowodowałby przyśpieszenie wykonywania tego zapytania - odpowiedź uzasadnij?

IV. W pewnej firmie zbudowano scentralizowaną bazę danych Oracle i opracowano szereg aplikacji korzystających z niej. Okazało się, że aplikacje działają zbyt wolno jak na potrzeby firmy. Wymień, jakie mogą być powody tego stanu rzeczy i co można byłoby zrobić, aby pomóc firmie.
 



Strona przygotowana przez Lecha Banachowskiego - ostatnia modyfikacja 03/01/05 .