7

Radosław Boroński Politechnika Koszalińska, Wydział Elektroniki i Informatyki

E-mail: radoslaw.boronski@tu.koszalin.pl

Wpływ ustawień parametru wieloblokowego sekwencyjnego czytania danych

na czas wykonywania zapytania SQL w bazie danych Oracle 11g

Streszczenie: Przedmiotem badań jest wpływ wielkości parametru inicju-jącego odpowiedzialnego za maksymalną ilość bloków danych czytanych jednocześnie w pojedynczej operacji wieloblokowego czytania sekwencyj-nego (db_file_multiblock_read_count) i statystyk systemowych oraz staty-styk obiektowych na czas wykonania zapytania SQL do bazy danych Oracle 11g. Badaniu poddano również różnice wykonania zapytania po-między domyślnym ustawieniem parametru db_file_multiblock_read_count a jego ręczną modyfikacją i pośredni wpływ statystyk.

1. Środowisko produkcyjne Stanowisko dla przeprowadzenia badań składało się z jednego serwera o konfiguracji: system operacyjny Red Hat Enterprise Linux Server release 5.5, z jądrem 2.6.18-194.26.1.el5, pamięć operacyjna 48 GB, cztery 4-rdzeniowe procesory Intel(R) Xeon(R) CPU E5620 @ 2.40 GHz, jedna partycja dyskowa o pojemności 535 GB. Na serwerze zainstalowano produkcyjną bazę danych Oracle w wersji 11.1.0.7, instancję ASM z dostępem do łącznej pojemności dysków 5,6 TB (macierz RAID).

2. Wieloblokowe czytanie danych Parametr db_file_multiblock_read_count odpowiedzialny jest za ustawienie możliwej, największej liczby bloków czytanych w operacji I/O (czytanie z dysku) w pojedynczej operacji czytania sekwencyjnego. Parametr ten jest pomocny przy minimalizacji bądź maksymalizacji operacji czytania danych z tabeli przy pełnym przeszukiwaniu tabeli. Całkowita liczba operacji I/O potrzebna do pełnego czytania tabeli uzależniona jest od takich operacji jak całkowity rozmiar przetwarzanej tabeli, wielkości rozmiaru czytania wieloblokowego czy operacji równoległego czytania tabeli. Zaczynając od wersji bazy danych Oracle 10g, domyślne ustawienie parametru db_file_multiblock_read_count odpowiada największej ilości operacji I/O dozwolonych przez system operacyjny. Wy-

8

sokość jej jest uzależniona jest od platformy i w większości przypadków równa się 1 MB danych czytanych równocześnie. Parametr ten wyrażany jest w blokach danych a jego wartość równa się ilorazowi największej ilości operacji I/O dozwolonych przez system operacyjny i rozmiarowi bloku danych [1]. W przypadku nieznanej wartości x(I/O), silnik bazy danych Oracle ustala ten parametr wg reguły [2]:

��_����_������� _����_��� � ��� � 1048576��_���� _���� ,

��_�����_������������ · ��_���� _�����

Wieloblokowe czytanie danych uzależnione jest również od ilości ekstentów w danym segmencie. W przypadku, gdy segment zbudowany jest z ekstentów zawierających mniejszą ilość bloków danych niż parametr db_file_multiblock_read_count, nastąpi czytanie bloków danych do maksymalnej wartości ilości bloków danych w danym eks-tencie. Dodatkowo, nagłówek segmentu danych zawsze czytany jest pojedynczo, blok po bloku i nigdy nie bierze udziału w czytaniu wieloblokowym. Kolejnym wyjątkiem jest sytuacja w której iloraz całkowitej ilości bloków danych w ekstencie i wielkość parametru db_file_multiblock_read_count nie jest liczbą całkowitą. W takim przypadku następuje czytanie wszystkich pozostałych, nieprzeczytanych bloków danych z eksten-tu, a kolejny do przeczytania ekstent jest traktowany jako osobny obiekt, czyli maksy-malna ilość czytanych bloków danych nie może być rozdzielona na następujące po sobie ekstenty [3].

3. Przedmiot badań Badanie zostało przeprowadzone na niepartycjonowanej tabeli o wielkości 9576 MB, zawierającej 102,5 miliona rekordów. Tabela przechowywana jest w przestrzeni tabel, w jednym pliku, zarządzanym przez system plików ASM (Automatic Storage Manage-ment), o łącznej pojemności dysków macierzy 5,6 TB i zastosowanej jednostce alokacji 1 MB (au_size). Oprócz tabeli testowej, w przestrzeni tabel znajdują się też inne tabele biorące udział w procesach prod produkcyjnych. Całkowite miejsce zajęte przez wszystkie obiekty w bazie danych wynosi 1,2 TB.

Logiczna budowa tabeli testowej składa się z 332 ekstentów, zawierających 306434 bloków danych o wielkości 32768 bajtów. Pierwszy ekstent tabeli ma rozmiar 192 kb. Średnia ilość bloków w ekstencie wynosi 923. Rozkład ekstentów w testowanym seg-mencie zarządzany jest automatycznie (lokalnie) przez silnik bazy danych.

Bloki występujące w pamięci podręcznej bufora danych bazy danych nie są brane pod uwagę jako kandydaci do czytania z ekstentu. W takim przypadku, następuje czytanie maksymalnej, dopuszczalnej ilości bloków danych do buforowanego bloku.

4. Badanie modelowe Badanie testowe polegało na 32-krotnym, pełnym (full table scan) czytaniu tabeli te-stowej z zastosowaniem różnych wartości parametru db_file_multiblock_read_count. Najmniejszą ustawioną wartością było 0 bloków (co wymusza zastosowanie wartości

9

domyślnej parametru), największą zaś 256 bloków przy jednorazowym czytaniu, co przy ustawionym rozmiarze bloku danych (32 kb) generowało operację odczytu o wiel-kości 8 MB. Krok zmiany parametru wynosił 8 w każdej operacji (0, 8, 16, 24, 32, 40, …. , 256). Parametr maksymalny został ustawiony na wartość 256 po to, aby nie prze-ciążyć systemu operacyjnego i procesów I/O potrzebnych do wykonania innych równo-ległych operacji w bazie danych. System operacyjny nie miał ustawionej dopuszczalnej wartości maksymalnej dla operacji I/O. Przed każdą operacją odczytu danych czyszczo-ny był bufor pamięci podręcznej bazy danych. Zapytanie testowe do bazy danych za-wierało nakaz wygenerowania ilości wszystkich wierszy zawartych w tabeli (SELECT count(*) FROM test).

W zapytaniu, dodatkowo zastosowano serię wskazówek (hints). Pierwszą wskazówką (nocache) było wymuszenie pomijania odczytu danych z bufora danych. Miało to na celu upewnienie się, że wszystkie czytane przez bazę bloki danych są blokami pochodzącymi z dysku a nie bufora pamięci podręcznej. Kolejną wskazówką (full) zastosowaną w zapy-taniu było wymuszenie pełnego czytania tabeli (blok po bloku). Ostatnią zaś wskazówką (noparallel) było wymuszenie niestosowania przetwarzania równoległego, które rozdzieli-łoby operacje czytania bloków danych na procesy podrzędne. Operacja taka mogłaby zapobiec wykorzystania maksymalnej wartości parametru czytania wieloblokowego.

Pełną iterację testową (32 kroki) podzielono na 2 nadrzędne serie różniące się sposobem rozproszonego ładowania czytanych bloków do buforu danych (db file scattered read) i z pominięciem takiego ładowania (direct path read). Celem takiego kroku było spraw-dzenie czy przy sekwencyjnym czytaniu wieloblokowym, ładowanie danych do bufora danych ma wpływ na czas wykonania zapytania. Rozproszone ładowanie do bufora danych polega na sekwencyjnym czytaniu danych z dysku a następnie dyslokacyjnemu załadowaniu tych danych do bloków pamięci podręcznej bazy danych. Operacja taka ma na celu przechowywanie bloków danych w pamięci do ponownego użycia przez inne procesy w przyszłości, ze względu na szybszy dostęp do komórek pamięci niż do bloków dysku.

Direct path read natomiast całkowicie pomija bufor danych i ładuje czytane bloki bez-pośrednio do pamięci PGA (Program Global Area) – części pamięci wydzielonej do obsługi sesji i przechowywania elementów potrzebnych do jej poprawnej obsługi [4].

Nadrzędne serie podzielono na serie podrzędne różniące się zastosowaniem aktualnych statystyk systemowych i obiektowych. Statystyki systemowe mogą mieć duże znaczenie przy opracowywaniu najlepszego planu wykonania zapytania przez optymalizator kosz-towy. Zawierają one dane nt. szybkości procesorów, uśrednionych czasów potrzebnych wykonania pojedynczej operacji czytania jednego bloku lub operacji czytania wieloblo-kowego, lub uśrednioną ilość bloków czytanych w jednej operacji [5]. Statystyki syste-mowe nie są generowane automatycznie przy tworzeniu bazy danych. Należy je wyge-nerować ręcznie. Tabela 1 przedstawia parametry statystyk systemowych wygenerowa-ne podczas jednego kroku wykonywania zapytania testowego.

10

Tab. 1. Wygenerowane parametry statystyk systemowych na środowisku testowym

Parametr Wartość

CPUSPEEDNW 2178.366

IOSEEKTIM 82220.839

IOTFRSPEED 1.05.754

SREADTIM 31.12.390

MREADTIM .578

CPUSPEED 2060

MBRC 4

MAXTHR 940670976

SLAVETHR 315887616

Statystyki obiektów są również niezbędne do oszacowania poprawnego planu wykony-wania zapytania. Brane są pod uwagę ilość rekordów w tabelach, liczba użytych i pu-stych bloków, średnia zajętość bloków danych czy średnia długość rekordu [6].

Zastosowanie statystyk obiektowych i systemowych miało na celu sprawdzenia czy dla tej samej liczby bloków czytanych naraz (w danej iteracji), owe statystyki mają znaczą-cy wpływ na czas wykonania zapytania. Czy poprzez zwiększoną ilość czytanych blo-ków i z jednocześnie lepszą wiedzą na temat testowanego obiektu, optymalizator kosz-towy jest w stanie wygenerować lepszy plan wykonania zapytania [7].

5. Przebieg badania Każdą serię testów powtórzono pięciokrotnie. Sprawdzony został czas systemowy przed wykonaniem każdej z iteracji, wykonano krok iteracji (przeczytano całą tabelę) z ustawionym w zależności od kroku parametrem db_file_multiblock_read_count, a następnie znowu sprawdzono czas systemowy. Różnica czasu systemowego jest wynikiem końcowym dla danego pomiaru. Wartości tego czasu z każdej iteracji zosta-ły uśrednione. Badanie przeprowadzono najpierw na obiekcie z niedostępnymi staty-stykami systemowymi i obiektowymi, z zastosowaną metodą ładowania czytanych bloków do bufora danych (db file scattered read). Następnie wygenerowano statystyki systemowe i przeprowadzono kolejną serię testów. W kolejnej serii wygenerowano statystyki obiektowe dla tabeli testowej. Po każdej serii sprawdzono maksymalną ilości bloków czytanych w danej serii. Rysunek 1 przedstawia wyniki pomiaru ilości czytanych jednorazowo bloków dla serii bez statystyk systemowych i obiektowych. Rysunek 2 przedstawia uśrednione wartości pomiaru czasu wykonywania zapytania dla każdej z serii.

11

Rys. 1. Ilość jednorazowo czytanych bloków danych dla operacji czytania z ładowaniem do bufora danych

Rys. 2. Uśrednione wartości pomiaru czasu wykonywania zapytania dla różnych warto-ści parametru db_file_multiblock_read_count z ładowaniem do bufora danych

Te same kroki powtórzono dla metody z pominięciem ładowania bloków do bufora danych (direct path read). Rysunek 3 przedstawia wyniki pomiaru ilości czytanych jednorazowo bloków dla serii bez statystyk systemowych i obiektowych dla tej metody. Rysunek 4 przedstawia uśrednione wartości pomiaru czasu wykonywania zapytania dla każdej z serii tej metody.

20240

460680

9001120

13401560

17802000

22202440

26602880

31003320

35403760

39804200

44204640

48605080

53005520

57405960

61806400

66206840

70607280

75007720

79408160

83808600

88209040

92609480

0

10

20

30

40

50

60

70

80

90

100

110

120

130

SR 0

SR 256

SR 128

SR 64

Liczba operacji czytania bloków

Iloś

ć p

rze

czyt

an

ych

blo

ków

0 8 16 24 32 40 48 56 64 72 80 88 96 104 112 120 128 136 144 152 160 168 176 184 192 200 208 216 224 232 240 248 256

23,5

24

24,5

25

25,5

26

26,5

27

27,5

28

28,5

29

29,5

30

30,5

31

scatter reads / no system stats / no object stats scatter reads / system stats / no object stats

scatter reads / no system stats / object stats scatter reads / system stats / object stats

uśredniona wartość domyślna [0]

mbrc [blocks]

cza

s [s

]

12

Rys. 3. Ilość jednorazowo czytanych bloków danych dla operacji czytania bez ładowania do bufora danych

Rys. 4. Uśrednione wartości pomiaru czasu wykonywania zapytania dla różnych warto-ści parametru db_file_multiblock_read_count bez ładowania do bufora danych

6. Wnioski Z przeprowadzonych badań i uzyskanych wyników wnioskuje się, że:

• domyślnie ustawienie parametru db_file_multiblock_read_count nie jest ustawieniem optymalnym dla najszybszego czytania całej tabeli,

• dla wieloblokowego czytania danych z dysku z ładowaniem do bufora danych, zawsze otrzymuje się lepszy wynik dla ustawienia parametru db_file_multiblock_read_count powyżej 32 bloków,

• dla wieloblokowego czytania danych z dysku z pominięciem ładowania danych do bufora danych, otrzymuje się lepszy wynik dla ustawienia parametru db_file_multiblock_read_count powyżej 140 bloków, z pominięciem serii be staty-styk obiektowych i z zastosowaniem statystyk systemowych, dla której wartość jest bliska domyślnej wartości uśrednionej,

20240

460680

9001120

13401560

17802000

22202440

26602880

31003320

35403760

39804200

44204640

48605080

53005520

57405960

61806400

66206840

70607280

75007720

79408160

83808600

88209040

92609480

020406080

100120140160180200220240260

DR 0

DR 256

DR 128

DR 64

Liczba operacji czytania bloków

Iloś

ć p

rze

czyt

an

ych

blo

ków

0 8 16 24 32 40 48 56 64 72 80 88 96 104 112 120 128 136 144 152 160 168 176 184 192 200 208 216 224 232 240 248 256

13

14

15

16

17

18

19

20

21

direct reads / no system stats / no object stats direct reads / system stats / no object stats direct reads / no system stats / object stats

direct reads / system stats / object stats uśredniona w artość domyślna [0]

mbrc [blocks]

cza

s [s

]

13

• wartości 8 i 16 parametru db_file_multiblock_read_count nie są optymalne dla opera-cji czytania tabeli i uzyskany czas czytania jest zawsze gorszy od domyślnego usta-wienia parametru przez system bazy danych,

• wieloblokowe czytanie serii danych metodą z pominięciem ładowania do buforu jest w każdym przypadku szybsze niż czytanie wieloblokowe danych z ładowaniem da-nych do bufora danych,

• statystyki systemowe i obiektowe nie mają większego wpływu na szybkość wykona-nia zapytania i czasy wykonywania są porównywalne (dla tej samej wartości parame-tru wieloblokowego czytania danych) z czasami wykonywania zapytania na obiekcie z aktualnymi statystykami,

• najlepszy czas wykonywania uzyskano dla parametru db_file_multiblock_read_count wielkości 256 z zastosowaniem metody czytania z pominięciem bufora danych i zasto-sowaniem obydwu rodzajów statystyk. Czad wykonania zapytania wyniósł 13 sekund,

• dla metody db file scattered read, pomimo ustawionego parametru db_file_multiblock_read_count na wielkości powyżej 128, nie udało się uzyskać jed-noczesnego wieloblokowego czytania danych o ilości powyżej 128 bloków. Powo-dem może być blokada silnika bazy danych na jednoczesne czytanie ilości danych powyżej 4MB (128 bloków x 32768 bajtów/blok) [8],

• silnik bazy danych w każdym przypadku stara się czytać jednocześnie maksymalną dozwoloną ilość bloków i wielkości te odchylają się nieznacznie (rysunki 1 i 3).

Literatura 1. Oracle Database Reference 11g Release 1 (11.1), Part Number B28320-03.

2. Antognini C., Troubleshooting Oracle Performance, Apress, 2008.

3. Ibidem.

4. Oracle Database Performance Tuning Guide, 11g Release 1 (11.1), Part Number B28274-02.

5. Loney K., Oracle Database 11g. Kompendium Administratora, Helion, Warszawa, 2010.

6. Freeman Rober G., OCP: Oracle Database 11g Administrator Certified Profes-sional, Sybex, 2009.

7. Oracle Database Performance Tuning Guide, op.cit.

8. Burleson D., Oracle Tuning: The Definitive Reference, Rampant Techpress, 2011.