ARCHITEKTURA

SYSTEMU

ZARZĄDZANIA BAZĄ

DANYCH ORACLE

Przygotował Lech Banachowski na podstawie:
1.

Lech Banachowski, Bazy danych – tworzenie aplikacji, PLJ, 1998.

2.

Dokumentacja Oracle.

2

Serwer bazy danych

•

Baza danych

- zbiór plików na dysku zawierających

tabele z danymi i inne obiekty bazy danych oraz
pomocnicze struktury danych.

•

Instancja

(jedna lub więcej) - oprogramowanie

operujące na bazie danych: 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. Instancja może
otworzyć i używać dokładnie jednej bazy danych w
jednej chwili.

3

Rodzaje plików bazy danych i
instancji

Plik inicjalizacyjny

instancji (np. initorcl.ora) – z

parametrami.

Plik kontrolny –

z podstawowymi informacjami o

bazie danych jak: nazwa, data utworzenia, nazwy
fizycznych plików.

Pliki bazy danych

(z danymi) - każdy z nich

przydzielony do jednej przestrzeni tabel.

4

Rodzaje plików bazy danych c.d.

Pliki kopii zabezpieczającej bazy danych

-

uzyskane najprościej przez skopiowanie plików bazy
danych pod systemem operacyjnym.

Pliki dziennika powtórzeń i zarchiwizowanego
dziennika powtórzeń

Pliki śladu

– pliki do których jest wpisywana

historia działań procesów instancji bazy danych.

5

Przestrzenie tabel

Przestrzeń tabel

to struktura pośrednia między

strukturą logiczną (tabelami, indeksami) a fizyczną
(plikami danych).
• Jednej przestrzeni tabel odpowiada z jednej strony
jeden lub więcej obiektów w bazie danych (np. tabela,
indeks) a z drugiej strony jeden lub więcej plików
systemu operacyjnego.
• Baza danych Oracle zawiera co najmniej jedną
przestrzeń tabel o nazwie

SYSTEM

. Ta przestrzeń tabel

zawiera słownik danych (tabele i perspektywy),
definicje wszystkich przechowywanych procedur i
pakietów, a także wyzwalaczy bazodanowych. Jest
zalecane

aby

nie

zawierała

żadnych

danych

przechowywanych przez użytkowników.

6

Przykłady - użycie przestrzeni
tabel

CREATE TABLESPACE human_resources
DATAFILE 'd:\orant\logbase\tabspace_f1.dat' SIZE 20M;

CREATE TABLE Salgrade
(Grade NUMBER

CONSTRAINT PK_salgrade PRIMARY KEY

USING INDEX TABLESPACE Ind_ts,
Losal NUMBER, Hisal NUMBER)
TABLESPACE Human_resources;

CREATE USER Bolek -- lub ALTER USER
IDENTIFIED BY xyz
DEFAULT TABLESPACE Human_resources -- domyślnie SYSTEM
TEMPORARY TABLESPACE Temp -- domyślnie SYSTEM
QUOTA 10M ON Cases_ts -- UNLIMITED - bez ograniczeń
QUOTA 5M ON Temp;

7

Organizacja zapisu bazy danych na
dysku

• Strona (blok)

danych - 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.

•Oracle

:

• dynamicznie przydziela miejsce na dysku, gdy
bieżące ekstenty w segmencie zostaną wypełnione,

• automatycznie dodaje nowy ekstent do
istniejącego segmentu.

8

Ustawienia dotyczące alokacji
ekstentów

 

STORAGE (

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

 
1.  

INITIAL

- rozmiar w bajtach pierwszego alokowanego

ekstentu (domyślnie 5 bloków).
2.  

NEXT

- 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ć Oracle dla obiektu.

9

Przykład –alokacja ekstentów w
segmencie

CREATE TABLE Dept(

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

 
1. 

MINEXTENTS=1

<-> dla tabeli Dept jest tworzony segment

złożony z dokładnie jednego ekstentu.
2.  

INITIAL=100K

<-> rozmiar pierwszego ekstentu wynosi 100

kilobajtów.
3.  

NEXT=50K

<-> gdy przepełni się pierwszy ekstent, następny

alokowany ekstent będzie miał rozmiar 50 kilobajtów.
4. 

PCTINCREASE=5

<-> 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

<-> maksymalnie można alokować do

segmentu tabeli Dept łącznie 50 ekstentów.

10

CREATE TABLE z parametrami

 

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;

 

11

Ustawienia stopnia zajętości miejsca
w blokach

Dla każdego segmentu (tabeli, klastra, indeksu, migawki)
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).

PCTFREE

– parametr określający procent miejsca w bloku

pozostawiany dla wierszy zwiększających swoje rozmiary przez
przyszłe operacje UPDATE.

Np.

PCTFREE = 20

określa, że 20% miejsca musi pozostać wolne

w bloku do użycia przez przyszłe operacje UPDATE. W rezultacie
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.

12

Ustawienia stopnia zajętości miejsca
w blokach – c.d.

PCTUSED

– parametr określający procent zajętego miejsca w bloku,

po przekroczeniu którego (w dół) blok zostaje 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.

13

Podział wiersza między różne
bloki

•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 - nazywa się

to

migracją wiersza

– zostawiając w starym bloku wskaźnik do

nowego położenia wiersza.

•Jeśli nie znajdzie, dzieli wiersz na kawałki i zapisuje je w

różnych blokach - nazywa się to

łańcuchowaniem wierszy

.

Powoduje to pogorszenie czasu działania. Może tu pomóc dobranie
odpowiednich parametrów PCTUSED i PCTFREE a także zwiększenie
wielkości bloku lub usunięcie i wstawienie od nowa podzielonych
na łańcuchy wierszy.

•Do sprawdzenia jak dużo jest podzielonych między blokami

wierszy używa się instrukcji ANALYZE z klauzulą LIST CHAINED
ROWS:

 

ANALYZE TABLE Nasza_tabela
LIST CHAINED ROWS INTO Chained_rows;

14

Opcja CACHE

Dla małych tabel słownikowych często używanych w
aplikacji przy ich tworzeniu można określić opcję

CACHE

umożliwiającą przechowywanie ich całych w

buforach danych pamięci wewnętrznej np.
 

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

15

Instancja bazy danych

SGA

Procesy systemu:
serwera i
drugoplanowe

Globalny Obszar Systemu (SGA)

16

Globalny Obszar Systemu
(SGA)

•

Bufory bazy danych

- zawierają używane ostatnio bloki

danych z bazy danych.

• Bufor dziennika powtórzeń

- zawiera dziennik zmian

wykonanych ostatnio na bazie danych. Pozycje dziennika
zapisane w buforze dziennika powtórzeń są przepisywane
do plików dziennika powtórzeń.
•

Pula dzielona

- obszary pamięci wspólnie używane

przez procesy:

• Dzielony obszar SQL

- przetwarzanie instrukcji SQL;

zawiera informacje takie jak drzewo analizy
składniowej i plan wykonania odpowiedniej instrukcji;
jest współużywany przez wiele aplikacji, które
przekazują do wykonania tę samą instrukcję.

• Bufor słownika danych.

• Kolejki zleceń

do bazy danych i

kolejki odpowiedzi

z

bazy danych.

17

Procesy

•Proces

użytkownika

(klienta)

tworzony

do

wykonania kodu programu aplikacyjnego

•Procesy systemu

– wywoływane przez inne procesy

w celu wykonania funkcji na rzecz wywołujących je
procesów.

• Procesy serwera

– tworzone przez system do

obsługi zleceń od zgłaszających się przez sieć
procesów użytkowników.
• 

Procesy drugoplanowe

są to stałe procesy

tworzone przez Oracle dla każdej instancji
przeznaczone do wykonywania rutynowych zadań
systemu zarządzania bazą danych.

18

Procesy drugoplanowe

Dyspozytor

(D)

–

przekazywanie

zleceń

od

połączonych procesów użytkowników do kolejki zleceń,
z której są one pobierane przez dostępne procesy
dzielone

serwera

oraz

za

zwracanie

wyników/odpowiedzi z powrotem do odpowiednich
procesów użytkowników.

Pisarz

bazy

danych

(DBWR)

-

przepisuje

zmodyfikowane bloki danych z buforów bazy danych w
SGA do plików na dysku. DBWR nie zapisuje bloków w
chwili, gdy transakcja kończy się zatwierdzeniem, co
pozwala minimalizować liczbę zapisów na dysk. DBWR
zapisuje zmienione bloki na dysk tylko wtedy, kiedy
inne dane muszą zostać sprowadzone do SGA i za mało
jest wolnych buforów danych, aby je pomieścić.

19

Procesy drugoplanowe c.d.

Pisarz dziennika (LGWR)

- przepisuje pozycje bufora

dziennika powtórzeń do pliku dziennika powtórzeń. 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. Dopiero w chwili
przesłania zawartości buforów 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 dysku).

Działania procesów drugoplanowych DBWR i LGWR są
niezależne od siebie.

20

Procesy drugoplanowe
c.d.

Monitor systemu (SMON)

– ogólne operacje jak odtwarzanie

instancji przy uruchamianiu instancji, kompaktyfikacja wolnych
obszarów na dysku (ekstentów) w celu uspójnienia wolnych
obszarów i ułatwienia kolejnych alokacji miejsca na dysku.

Monitor procesów (PMON)

– ogólne operacje jak odtwarzanie

procesu użytkownika po awarii, zwalnianie zasobów, których
używał ten proces, kontrola działania dyspozytorów i procesów
serwera i wznawianie ich, jeśli się zawiesiły.

Archiwizator (ARCH)

- 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)

- stałe zadania

zaplanowane do wykonywania przez system jak odświeżanie
migawek czy zadania tworzone za pomocą pakietu DBMS_JOB.

Odtwarzacz (RECO)

– odtwarzanie transakcji rozproszonych przy

awarii sieci lub systemu.

21

Ilustracja jak działa serwer Oracle

1.    Aplikacja kliencka ustanawia połączenie z serwerem za pomocą
sterownika Oracle Net.
2.   Serwer wykrywa żądanie przychodzące z aplikacji i tworzy
dedykowany proces działający na serwerze w imieniu procesu
użytkownika.
3.   Użytkownik chce wykonać instrukcję SQL i ją zatwierdzić, np. zmienić
adres osoby.
4.    Proces serwera otrzymuje instrukcję i sprawdza czy w którymś z
dzielonych obszarów 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 dzielonego obszaru SQL,
w którym instrukcja SQL zostaje poddana analizie składniowej i
przetworzona.
5.    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.
6.   Proces serwera modyfikuje dane w SGA. Proces DBWR zapisze
zmodyfikowane bloki na stałe na dysk, dopiero wtedy kiedy będzie to
konieczne. Ponieważ transakcja zostaje zatwierdzona, proces LGWR
natychmiast zapisuje zmiany dokonane przez transakcję do pliku
dziennika powtórzeń.
7.  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.
8.  W czasie całej tej procedury, procesy drugoplanowe działają w tle,
obserwując czy czasem nie zaszły warunki wymagające ich interwencji.

22

Oglądanie planu wykonania instrukcji
SQL

Istnieje możliwość obejrzenia planu wykonania instrukcji SQL,
który wybiera system. Analizując plan, administrator lub
programista aplikacji może znaleźć przyczynę zbyt wolnego
działania aplikacji.
 

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

 

Po przeanalizowaniu planu projektant systemu

może wybrać inną

metodę wykonania danej instrukcji np. aby użyć indeksu:

SELECT --+INDEX(Klienci Kraj)

FROM Klienci

WHERE Kraj = 'Polska';

23

Kontrolowanie 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:

       użytkownika wykonującego operację,

       typ operacji,

       obiekt, którego dotyczy operacja,

       datę i godzinę operacji,

Np.

AUDIT UPDATE TABLE, DELETE TABLE;

AUDIT SELECT ON Scott.Emp;

24

Poprawienie parametrów działania bazy
danych

1. Poprawny schemat bazy danych - ewentualnie ze
świadomie

przeprowadzoną

denormalizacją

lub

dekompozycją horyzontalną;
2. Poprawna organizacja poziomu fizycznego: klastry,
indeksy, rozłożenie plików do różnych stacji
dyskowych,

zastosowanie

architektury

wieloprocesorowej;
3. Odpowiednie parametry zapisu w przestrzeniach
tabel (pomocą może być instrukcja ANALYZE):
       rekordów w blokach (np. PCTFREE i PCTUSED),
       alokacji ekstentów do segmentów (np.
parametry STORAGE);

25

Poprawienie parametrów działania bazy
danych

4.

Odpowiednie wartości parametrów inicjalizacyjnych

instancji np. liczba buforów danych, maksymalna liczba
kursorów.
5.  Odpowiednie instrukcje SQL poprzez:
  analizę planu wykonywania przez system instrukcji
SQL (jest możliwe zamieszczanie wskazówek dla
optymalizatora),
      analizę czasu wykonywania instrukcji SQL
dostępną w plikach śladu,
     oglądanie zawartości Dzielonego Obszaru SQL i
standaryzację zapisu instrukcji;
6. Kontrola (audyt) sposobu korzystania z bazy danych
przez użytkowników i przyjęcie odpowiedniego systemu
ról, uprawnień, perspektyw, profili i kwot;
7. Realizacja

transakcji:

SERIALIZABLE/READ

COMMITED.