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.
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.
Serwer bazy danych Oracle używa następujących rodzajów plików bazy danych i instancji.
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.
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
domyślnie SYSTEM
DEFAULT TABLESPACE Human_resources --
TEMPORARY TABLESPACE Temp
-- domyślnie SYSTEM
QUOTA 10M ON Cases_ts
QUOTA 5M ON Temp;
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).
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.
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:
PCTFREE – parametr określający procent miejsca w bloku pozostawiany dla wierszy zwiększających swoje rozmiary przez przyszłe operacje UPDATE. Np. parametr PCTFREE = 20 określa, że około 20% miejsca musi pozostać wolne w bloku do użycia przez przyszłe operacje UPDATE. W rezultacie, gdy ilość wolnego miejsca spadnie poniżej 20% blok zostaje usunięty z listy wolnych bloków, do których instrukcje INSERT mogą wstawiać kolejne wiersze. PCTFREE musi być liczbą całkowitą z przedziału od 0 do 99. Wartość 0 umożliwia zapełnianie każdego bloku w całości przy wykonywaniu INSERT. Wartością domyślną jest 10. Wysokie PCTFREE jest dobre dla segmentów, do których wykonuje się często aktualizacje, jak również w sytuacji wykonywania zapytań lub transakcji READ ONLY, gdy jednocześnie działa wiele transakcji innych użytkowników jeszcze nie zatwierdzonych - informacje o transakcjach są zapisywane razem z danymi. Niskie PCTFREE oszczędza miejsce na dysku.
PCTUSED – parametr określający procent zajętego miejsca w bloku, po
przekroczeniu którego (w dół) blok zostaje z powrotem wstawiony na listę
wolnych bloków. Np. PCTUSED = 60 oznacza, że gdy procent zajętego w bloku
miejsca spadnie poniżej 60% (z powodu DELETE i UPDATE), blok zostaje z
powrotem wstawiony na listę wolnych bloków - aby mogły w nim umieszczać
wiersze kolejno wykonywane instrukcje INSERT. Wartością domyślną jest 40. Suma
PCTFREE+PCTUSED musi być mniejsza niż 100. Wysokie PCTUSED oszczędza miejsce na dysku, niskie przyśpiesza UPDATE i
INSERT.
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 |
2. Często wykonywane są instrukcje INSERT i DELETE oraz UPDATE - które nie zwiększają rozmiaru wierszy.
PCTFREE= 5, PCTUSED=60 |
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 |
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ć:
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.
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:
INITRANS – parametr określający, dla ilu transakcji są z góry wyznaczone ich pozycje w nagłówku bloku; dla pozostałych transakcji alokacja w bloku jest dynamiczna. INITRANS może mieć wartość od 1 do 255, domyślnie 1.
MAXTRANS - parametr określający maksymalną liczbę transakcji, które mogą
równocześnie korzystać z tego samego bloku. Liczba INITRANS transakcji ma
statycznie przydzielaną pamięć w bloku - dla pozostałych pamięć jest alokowana
dynamicznie. Domyślna wartość MAXTRANS jest ustalana przez system i zależy od
wielkości bloku danych.
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;
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:
EXTENT MANAGEMENT LOCAL
oznacza automatyczną alokację i
dealokacje ekstentów.
W nagłówku pliku z danymi umieszcza się wektor bitowy (bitmapę) określający,
które ekstenty zostały przydzielone, a które są do przydzielenia.SEGMENT SPACE MANAGEMENT AUTO
oznacza automatyczne zarządzanie wolnymi blokami.
Nie są stosowane listy bloków z wolnymi miejscami, tylko katalog wszystkich bloków
w danym segmencie z zapisanym procentem zajętego miejsca.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;
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;
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.
Partycje są też określane dla indeksów (klucz partycji = klucz indeksu):CREATE Klienci(
-- klucz partycji: najlepiej aby nie ulegał zmianie
Id NUMBER(5) PRIMARY KEY,
Kraj CHAR(2), ......)
PARTITION BY RANGE (Kraj)
(PARTITION p1 VALUES LESS THAN ('C')
TABLESPACE Data01;
PARTITION p2 VALUES LESS THAN ('I')
TABLESPACE Data02;
...
PARTITION p19 VALUES LESS THAN MAXVALUE
TABLESPACE Data19);
CREATE INDEX Ind_Klienci_Kraj ON
Klienci(kraj)
LOCAL(PARTITION p1 TABLESPACE Ind01;
....
PARTITION p19
TABLESPACE Ind19);
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:
Instancja bazy danych jest to kolekcja procesów i struktur pamięci używanych do zarządzania dostępem do bazy danych.
Uruchomienie serwera bazy danych (operacja STARTUP) obejmuje uruchomienie instancji bazy danych, zamontowanie (podłączenie) bazy danych, otwarcie bazy danych.
Zamknięcie serwera bazy danych (operacja SHUTDOWN) obejmuje zamknięcie bazy danych, zdemontowanie (rozłączenie bazy danych), zatrzymanie instancji.
Awaria serwera jest to anormalne zamknięcie serwera bazy danych
spowodowane niemożliwością dalszego jego działania np. z powodu awarii jednego z procesów serwera na
poziomie systemu operacyjnego. Pozostaje wtedy
ponowne uruchomienie serwera z odtworzeniem bazy danych (efekt wszystkich
zatwierdzonych transakcji zostanie zachowany).
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.
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:
db_block_buffers – liczba buforów bazy danych - powinna być jak największa;
shared_pool_size – rozmiar puli współdzielonej - powinien być jak największy;
sort_area_size – wielkość pamięci używanej do sortowania (np. przy realizacji ORDER BY, GROUP BY, DISTINCT, tworzeniu indeksów);
open_cursors - dozwolona maksymalna liczba kursorów tworzonych do wykonania instrukcji SQL na jedną sesję; gdy jest za mała dla sesji, aplikacja zatrzymuje się; domyślna wartość wynosi 64;
processes - maksymalna liczba procesów (procesów serwera i drugoplanowych);
db_writer_processes - liczba procesów zapisujących do bazy danych (zakres od 1 do 10) – domyślnie 1;
timed_statistics – czy obliczać statystyki czasowe wykonywania instrukcji SQL (zalecane TRUE przy testowaniu i uruchamianiu, FALSE - przy normalnej eksploatacji);
sql_trace – czy wpisywać ślad obliczeń w sesji do pliku;
optimizer_mode – wybór trybu optymalizacji:
FULL_ROWS lub ALL_ROWS - użycie optymalizatora opartego na analizie statystyk;
RULE - użycie optymalizatora opartego na regułach ustalających priorytety;
CHOOSE – tryb optymalizacji dobiera system.
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.
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;
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-UNIQUEoznaczają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. |
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 */ |
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.
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.
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;
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:
|
Program administracyjny Oracle
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.
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.
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.