Oracle

ARCHITEKTURA

SYSTEMU

ZARZĄDZANIA BAZĄ

DANYCH ORACLE

Przygotował Lech Banachowski na podstawie:
1.

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

Dokumentacja Oracle.

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.

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.

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.

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.

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;

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.

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)

INITIAL

- rozmiar w bajtach pierwszego alokowanego

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

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

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

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.

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;

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.

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.

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ę

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;

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;

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

bazy danych.

Procesy

•Proces

użytkownika

(klienta)

tworzony

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.

Procesy drugoplanowe

Dyspozytor

(D)

–

przekazywanie

zleceń

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

serwera

oraz

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

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.

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.

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.

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

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;

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

Poprawienie parametrów działania bazy
danych

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.

Document Outline