To co najważniejsze w 10g: część 3

Jadwiga Gnybek Jadwiga_Gnybek@nofer.lodz.pl

Nie wszystkie nowinki technologiczne znajdują od ręki zastosowanie w praktyce. Są jednak i takie, o których mówimy: „jak mogło nie być ich w poprzednich wersjach bazy”? Do takich nowinek bazy Oracle 10g z pewnością zaliczyć możemy system zarządzania segmentami; narzędzie pozwalające na efektywne wykorzystanie powierzchni dyskowej, reorganizację fizycznej lokalizacji obiektów bazodanowych w trybie on-line oraz obserwację trendów przyrostu liczby danych zapisywanych w bazie.

 

Wszyscy wiemy dobrze, że praca bazy danych to zapisywanie, odczytywanie, modyfikowanie i kasowanie danych. Zwłaszcza te ostatnie czynności skutkują niejakim bałaganem w wewnętrznej strukturze rozmieszczenia fizycznych zapisów rekordów w segmentach i blokach. Każde kasowanie, to dziura w skrupulatnie zapełnianej zapisami strukturze bloków ekstentów i segmentów. Niewiele lepiej rzecz ma się z modyfikacją danych. Wiele działań poprawnych pod względem logiki pracy motoru danych, powoduje powstawanie nieciągłości w fizycznym ich zapisie. Każda nieciągłość to albo strata czasu na poszukiwanie brakującego kawałka odczytywanego rekordu albo wolna, nie dająca się wykorzystać przestrzeń dyskowa zajmowana przez naszą bazę. Przez całe lata przestrzeń ta rezerwowana była na kolejne zapisy do tego obiektu. Ponadto baza zapamiętywała największy obszar zapisany w danym segmencie, a wartość ta oznaczana była jako HWM (High Water Mark).

Dlaczego było nam z tym źle?

Po pierwsze: każdorazowe wykonanie zapytania wymagającego pełnego skanu tabeli (full table scan) powodowało przeszukiwanie powierzchni segmentów do znacznika HWM. Często zatem szukaliśmy danych tam, gdzie ich już dawno nie było. Wydłużało to oczywiście czas realizacji takich zapytań.

Po drugie – jeśli wiersze wprowadzane były do bazy „krótką ścieżką” poprzez Direct Load Insert czyli insert z hintem APPEND lub za pomocą SQL*Leadera, nowo zapełniane bloki danych tworzone były „powyżej” HWM, a więc nie wykorzystując wolnego miejsca znajdującego się w segmencie „poniżej” tego znacznika.

Oczywiście administratorzy baz Oracle 9 i wcześniejszych znajdowali wiele sposobów na konsolidację danych. Do najprostszych należało skasowanie tabeli i założenie jej od nowa z jednoczesnym wypełnieniem jej nagromadzonymi bałaganiarsko danymi.

Innym, równie oczywistym sposobem, było przeniesienie tabeli do innej przestrzeni tabel poleceniem ALTER TABLE MOVE. Oba te sposoby solidarnie obarczone były tą samą niedogodnością: wykluczały jednoczesną eksploatację danego obiektu przez użytkowników bazy, czyli klasyczny off-line. Oczywiście, dostępne były również bardziej zaawansowane techniki dopuszczające działania on-line i wykorzystujące narzędzia do reorganizacji tabel. Tu niestety warunkiem koniecznym było posiadanie dużego zapasu powierzchni dyskowej, ponieważ podczas porządkowania struktury zapisu tabel niezbędne było wykonanie jej kopii.

To, co w dziewiątce było problemem, w dziesiątce wydaje się banalnie proste. Za odchudzanie obiektów bazodanowych i oddawanie zbędnych zasobów dyskowych do ponownego wykorzystania przez inne obiekty bazodanowe odpowiedzialny jest mechanizm Automatic Segment Space Management (ASSM).

Zarządzanie przestrzenią

Przyjrzyjmy się, jak to działa. Załóżmy, że w naszej bazie istnieje tablica zamowienia. Przechowuje ona informacje o zamówieniach składanych przez naszych klientów. Po realizacji zamówienia, dane zapisywane są do tabeli zamowienia_arch i usuwane z tabeli zamowienia. Ponieważ czas realizacji poszczególnych zleceń jest różny i różne jest rozłożenie w czasie intensywności składania nowych zleceń, nie można założyć, że liczba wykasowanych rekordów z tabeli zamowienia równa się zawsze liczbie nowo zapisanych zamówień.

W praktyce oznacza to, że wskaźnik HWM wędruje w górę, jeśli liczba zamówień zrealizowanych nie bilansuje liczby nowych zamówień. Musimy zatem od czasu do czasu posprzątać i odzyskać pozostawione w segmentach puste miejsca. Podobnie jak w przypadku sprzątania, musimy najpierw ocenić, czy już warto lub czy już musimy. W realiach wnętrza bazy Oracle oznacza to policzenie, ile wolnego miejsca w danym obiekcie bazodanowym można odzyskać. Zakładając, że na przestrzeni tabel w której znajdują się nasze przykładowe tablice, mamy włączony odpowiedni mechanizm, możemy posłużyć się procedurą SPACE_USAGE z pakietu DBMS_SPACE.

Piszemy zatem:

declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => ' zamowienia',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
end;
/

W odpowiedzi otrzymujemy:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 4148 Bytes = 0

Full Blocks = 2 Bytes = 16384

Jak to czytać? Aż 4148 bloki naszej tabeli posiadają 75-100% wolnej przestrzeni (FS4) i tylko 2 bloki są pełne w 100% (full blocks). Mamy zatem mnóstwo miejsca do odzyskania.

Od czego zaczynamy? Od wprawienia wierszy naszej tabeli „w ruch” czyli postawienia jej w stan:

alter table zamowienia enable row movement;

Musimy również zadbać aby triery operujące na tej tabeli, a wykorzystujące rowid zostały wyłączone. Podczas przemieszczania rekordów tabeli oznaczenia rowid ulegną bowiem zmianie.

Teraz czas na najważniejsze – wprawiamy wiersze naszej tabeli w ruch i odchudzamy zajmowaną przez nie przestrzeń dyskową:

alter table bookings shrink space compact;

A co teraz dzieje się we wnętrzu bazy? Wiersze naszej tabeli ponownie rozmieszczane są w tych blokach, w których zostały zapisane. W rezultacie tej operacji zwiększa się liczba wolnych bloków znajdujących się w segmencie poniżej HWM. Sam znacznik HWM nie zmienia jednak swojego położenia.

Wykonajmy teraz ponownie fragment PL/SQL’a, którym wywoływaliśmy działanie procedury dbms_space.space_usage. Tym razem odpowiedź wygląda znacznie lepiej:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384

Jak widać, liczba bloków posiadających 75-100% wolnej przestrzeni (FS4) z 4148 spadła do zera, pojawił się natomiast jeden blok posiadający 50% wolnej przestrzeni (FS3). Pamiętajmy jednak, że położenie HWM nie zmieniło się, zatem ogólna powierzchnia dyskowa zajmowana przez tę tabelę nie uległa zmianie. Zobaczmy ile to jest:

SQL> select blocks from user_segments where segment_name = 'zamowienia';
BLOCKS
-----
4224

Liczba bloków zajmowanych przez dane naszej ulubionej tabeli to 4224 – jak już wiemy, nie uległa ona zmianie na skutek przeprowadzonych przez nas ostatnio czynności. Wszystko to przez te nieszczęsne wysoko ustawione znaczniki HWM. Spróbujmy „wyzerować” ich położenie:

alter table zamowienia shrink space;

i zobaczmy czym to zaskutkowało. W tym celu powtórzymy zapytanie o liczbę wykorzystywanych przez tabelę bloków:

SQL> select blocks from user_segments where segment_name = 'zamowienia';
BLOCKS
-----
8

I o to właśnie nam chodziło! Tablica oddała niewykorzystywane bloki do ponownego użycia przez inne obiekty bazy. W dodatku – co nie mniej ważne – wszystko to dokonane zostało w trybie on-line z zachowaniem dostępności przebudowywanego obiektu. Teraz jeszcze kosmetyka indeksów:

alter table zamowienia shrink space cascade;

też skutkująca zmniejszeniem ilości zajmowanego na dysku miejsca. Ilość miejsca potrzebnego do zapisania wskazanej tabeli można nawet zgrubnie określić przy wykorzystaniu funkcji verify_shrink_candidate z pakietu dbms_space. Wykonajmy na początek następujący fragment kodu:

begin
if (dbms_space.verify_shrink_candidate
('ARUP',' zamowienia','TABLE',1300000)
) then
:x := 'T';
else
:x := 'F';
end if;
end;
/
PL/SQL procedure successfully completed.
SQL> print x
X
----------------
T

Jeśli odpowiedź brzmi T czyli TAK oznacza to, że wskazana przez nas tabela zamówienia może zostać odchudzona do wielkości 1 300 000 bajtów zajmowanej na dysku powierzchni dyskowej. Spróbujmy zacisnąć pasa mocniej, zmieniając parametr 1 300 000 na 30 000. Niestety, po ponownym uruchomieniu kodu parametr x przyjmie wartość F. Oznacza to ni mniej ni więcej, że nasza tabela na 30 000 bajtów raczej się nie zmieści.

Skoro wiemy już jak odchudzić tabele, przyjrzyjmy się obiektom „stowarzyszonym”. Zwykle obiektami zajmującymi drugie miejsce pod względem apetytu na powierzchnię dyskową są indeksy. Przed napisaniem polecenia create index… warto zastanowić się zatem, czy ten indeks zmieści się w dostępnej nam przestrzeni dyskowej. I tu znów odwołamy się do pakietu DBMS_SPACE. Załóżmy, że planujemy utworzenie nowego indeksu na tabeli zamówienia. Wykonajmy następujący skrypt:

declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_zamowienia_01 on zamowienia '||
'(zam_id, cust_name) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/

W odpowiedzi otrzymamy:

Used Bytes = 7501128
Allocated Bytes = 12582912

A gdyby tak pomajstrować przy parametrach? Przecież wielkość zapotrzebowania na powierzchnię dyskową zależy od kilku parametrów definiowanych podczas tworzenia indeksu. Załóżmy, że nasz indeks będzie miał ustawiony parametr INITRANS na 10. Wykonajmy ponownie kod z uwzględnieniem poczynionych założeń:

declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index in_zamowienia_01 on zamowienia '||
'(zam_id, cust_name) tablespace users initrans 10',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/

Tym razem odpowiedź brzmi:

Used Bytes = 7501128
Allocated Bytes = 13631488

 

Zapotrzebowanie indeksu na przestrzeń dyskową wzrosło i w zasadzie nic w tym dziwnego. Warto natomiast zapamiętać, że wykorzystując właściwości tego pakietu możemy niejako „na sucho” przećwiczyć konsekwencje doboru poszczególnych parametrów składowania indeksów.

Na koniec jeszcze łyżka dziegciu. Musimy pamiętać, że opisane procedury działają jedynie na tych przestrzeniach tabel, dla których uruchomione są mechanizmy zarządzania przestrzenią dyskową SEGMENT SPACE MANAGEMENT AUTO. Estymacja rozmiaru indeksów podaje przyzwoicie dokładne odpowiedzi tylko dla tabel, dla których posiadamy dostatecznie świeże dane statystyk tabeli. Jeśli statystyk takich nie mamy, procedura niestety nie zakończy się błędem. Błędne będą jedynie podawane przez nią liczby.

Przyjmując do serca te uwagi spróbujmy poestymować również fizyczne wielkości danych tabeli, w zależności od zadanych parametrów składowania. Załóżmy, że nasza tabela zamówienia ma średnio 30 000 wierszy. Obecnie parametr składowania PCTFREE ustawiony jest na wartość 20. Co stanie się z zapotrzebowaniem na powierzchnię dyskową, jeśli wartość tę zwiększymy do liczby 30? W zasadzie, można by podejrzewać, że różnica będzie oscylować w okolicach 10% ale spróbujmy sprawdzić to wykorzystując procedurę CREATE_TABLE_COST z pakietu DBMS_SPACE.

declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_table_cost (
tablespace_name => 'USERS',
avg_row_size => 30,
row_count => 30000,
pct_free => 20,
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line('Used: '||l_used_bytes);
dbms_output.put_line('Allocated: '||l_alloc_bytes);
end;
/

Odpowiedź procedury brzmi:

Used: 1261568
Allocated: 2097152

Jak łatwo przewidzieć, zmienimy teraz parametr pct_free procedury z 30 na 20. Co teraz odpowie procedura?

Used: 1441792
Allocated: 2097152

Przyrost zapotrzebowania na powierzchnię dyskową wynosi zatem nie 10%, a 14% – niby byliśmy blisko, ale jednak różnica intuicji i kalkulacji wyniosła w tym przypadku aż 40%. Chyba warto zatem liczyć.

Oczywiście zastosowań i możliwości wspomnianych tu pakietów jest znacznie więcej. Zachęcam więc do lektury dzieła „Technical White Paper – The Self-Managing Database: Proactive Space & Schema Object Management”.