Statystyki (1)
" Informacje, opisujące dane i struktury obiektów bazy danych.
" Przechowywane w słowniku danych.
Optymalizacja poleceń SQL
" Używane przez optymalizator do oszacowania:
Część 2.
" selektywności predykatów polecenia,
" kosztu użycia ścieżek dostępu,
" kosztu operacji I/O i czasu procesora,
Statystyki i histogramy,
" kosztu planu wykonania polecenia.
metody dostępu do danych
" Tylko aktualne statystyki użyteczne!
" Statystyki są statyczne  nie są automatycznie uaktualniane przy
zmianie danych.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
1 2
Statystyki (2) Statystyki (3)
" Przykłady statystyk: " Statystyki mogą być gromadzone automatycznie (przez
dedykowany proces SZBD) lub ręcznie (na żądanie użytkownika)
" dla relacji:
przy użyciu pakietu DBMS_STATS.
" liczba rekordów,
" liczba bloków,
" średnia długość rekordu,
" W przypadku braku statystyk dla obiektów używanych w zapytaniu
" dla atrybutu relacji:
przed wykonaniem zapytania optymalizator realizuje dynamiczne
" liczba różnych wartości,
próbkowanie statystyk.
" liczba rekordów, w których atrybut ma wartość pustą,
" rozkład wartości (histogram),
" dla indeksu:
" liczba bloków-liści,
" wysokość drzewa,
" wskaz
nik zgrupowania indeksu,
" statystyki systemowe:
" wykorzystanie procesora,
" liczba operacji we/wy.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
3 4
Histogramy (1) Histogramy (2)
" Histogram  szczegółowa statystyka opisująca rozkład wartości " Rodzaje (cd):
określonej kolumny relacji.
" histogram częstotliwości (ang. frequency)  każda wartość kolumny
odpowiada jednemu przedziałowi, każdy przedział zawiera liczbę
" Rodzaje:
wystąpień tej wartości; tworzony wtedy, gdy liczba wartości kolumny
" histogram o zrównoważonej wysokości (ang. height balanced)  zbiór
jest mniejsza bądz
równa żądanej liczbie przedziałów histogramu.
wartości kolumny dzielony jest na przedziały o tej samej (w
przybliżeniu) liczbie rekordów; przykład (zakres wartości: <1, 100>,
liczba przedziałów: 10):
" Histogramy należy tworzyć tylko dla kolumn z nierównomiernym
" równomierny rozkład wartości atrybutu:
rozkładem wartości (ang. skewed data), często używanych w
warunkach zapytania.
1 10 20 30 40 50 60 70 80 90 100
" Gdy zmieni się rozkład danych kolumny, konieczne jest ponowne
" nierównomierny rozkład wartości atrybutu:
wygenerowanie histogramu,
1 10 10 10 30 40 40 40 65 80 100
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
5 6
Ręczne zbieranie statystyk Zbieranie statystyk dla indeksu
" Metody: exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => , indname => ,
" na podstawie pełnych danych,
estimate_percent => );
" szacowanie na podstawie próbki, próbka określana w
procentach liczby rekordów.
" jeśli wartość określono jako:
" Procedury zbierające statystyki: " null, wówczas statystyki zbierane na podstawie pełnych danych,
" liczbę z przedziału <0,00001; 100>, wówczas szacowanie na podstawie
" DBMS_STATS.GATHER_INDEX_STATS  dla indeksu,
próbki o zadanym rozmiarze,
" DBMS_STATS.GATHER_TABLE_STATS  dla relacji.
" DBMS_STATS.AUTO_SAMPLE_SIZE  rozmiar próbki dobiera system.
" Procedury usuwające statystyki:
" DBMS_STATS.DELETE_INDEX_STATS  dla indeksu,
" DBMS_STATS.DELETE_TABLE_STATS  dla relacji, exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCOTT', indname => 'PK_PRAC', estimate_percent => 20);
" DBMS_STATS.DELETE_COLUMN_STATS  dla kolumny.
" Uwaga! Od Oracle10g statystyki dot. indeksów są gromadzone automatycz-
nie podczas tworzenia lub przebudowy indeksu.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
7 8
Zbieranie statystyk dla relacji (1) Zbieranie statystyk dla relacji (2)
exec DBMS_STATS.GATHER_TABLE_STATS(
"  SIZE { liczba | REPEAT | AUTO | SKEWONLY }:
ownname => , tabname => ,
" liczba  liczba przedziałów w histogramie, zakres: <1, 254>,
estimate_percent => ,
" REPEAT  powtórzenie zbierania histogramów dla kolumn, które mają już
method_opt => ,
histogramy,
cascade => );
" AUTO  SZBD określi, dla których kolumn zbierać histogramy na podstawie
obciążenia i rozkładu danych kolumny,
" METHOD_OPT  określa zakres zbieranych statystyk:
" SKEWONLY  SZBD określi, dla których kolumn zbierać histogramy tylko na
" FOR TABLE  tylko statystyki dla tabeli bez statystyk dla kolumn,
podstawie rozkładu danych kolumny (bez analizy obciążenia).
" FOR ALL COLUMNS []  statystyki dla tabeli i statystyki
dla wszystkich kolumn,
" FOR ALL COLUMNS SIZE AUTO  wartość domyślna dla par. METHOD_OPT:
" FOR ALL INDEXED COLUMNS []  statystyki dla tabeli i
" statystyki tabeli,
statystyki dla poindeksowanych kolumn,
" podstawowe statystyki wszystkich kolumn tabeli,
" FOR COLUMNS [] kolumna1 [], kolumna2
[], ...  statystyki dla tabeli i statystyki dla wskazanych
" histogramy dla kolumn wyznaczonych na podstawie wcześniejszych
kolumn.
obserwacji dotyczących obciążenia i rozkładu wartości.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
9 10
Zbieranie statystyk dla relacji (3) Statystyki w słowniku bazy danych
exec DBMS_STATS.GATHER_TABLE_STATS(
" Dla relacji:
ownname => 'SCOTT', tabname => 'PRACOWNICY',
" USER_TABLES, USER_TAB_STATISTICS
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR COLUMNS placa_pod SIZE AUTO, nazwisko SIZE AUTO');
" Dla kolumn:
" USER_TAB_COLUMNS, USER_TAB_COL_STATISTICS,
exec DBMS_STATS.GATHER_TABLE_STATS( USER_TAB_HISTOGRAMS
ownname => 'SCOTT', tabname => 'PRACOWNICY',
" Dla indeksów:
method_opt => 'FOR ALL INDEXED COLUMNS',
" USER_INDEXES, USER_IND_STATISTICS
cascade => TRUE);
SELECT num_rows, blocks, last_analyzed, sample_size
FROM USER_TAB_STATISTICS
" Uwaga! Od Oracle12c statystyki dotyczące tabel zostają zebrane automatycznie
WHERE table_name = 'PRACOWNICY';
w sytuacji, gdy tabela, do której ładowane są dane ścieżką bezpośrednią
(polecenie INSERT /*+ APPEND */, dane umieszczane od razu w plikach bazy
SELECT num_distinct, low_value, high_value, num_buckets, histogram
danych z pominięciem bufora bazy danych), była poprzednio pusta:
FROM USER_TAB_COL_STATISTICS
" tabela została dopiero co utworzona i nie posiada jeszcze rekordów, lub
WHERE table_name = 'PRACOWNICY'
" usunięto z tabeli wszystkie rekordy.
AND column_name = 'NAZWISKO';
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
11 12
Usuwanie statystyk Metody dostępu do danych
" Określają, w jaki sposób dane polecenia SQL są odczytywane z
exec DBMS_STATS.DELETE_INDEX_STATS(
miejsca ich fizycznej lokalizacji.
ownname => , indname => );
" Dostęp do tabeli:
" pełne przeglądnięcie,
exec DBMS_STATS.DELETE_TABLE_STATS(
" dostęp przy pomocy adresu rekordu.
ownname => , tabname => );
" Dostęp do indeksu:
" unikalne przeglądnięcie indeksu,
exec DBMS_STATS.DELETE_COLUMN_STATS(
" (odwrócone) zakresowe przeglądnięcie indeksu,
ownname => , tabname => ,
" przeglądnięcie indeksu z pominięciem kolumn,
colname => , col_stat_type => );
" pełne przeglądnięcie indeksu,
" szybkie pełne przeglądnięcie indeksu,
" COL_STAT_TYPE:
" dostęp do indeksu bitmapowego,
" HISTOGRAM  usuwany jest histogram dla kolumny, podstawowe
" połączenie indeksów.
statystyki kolumny pozostają, Przy dostępie do indeksu dane zwykle zwracane w kolejności rosnącej.
" Ogólne zasady dostępu do danych:
" ALL  usuwane są wszystkie statystyki dla kolumny (wartość
" odczyt dużej części rekordów relacji  pełne przeglądnięcie relacji,
domyślna).
" odczyt pojedynczych rekordów relacji  dostęp za pomocą indeksu.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
13 14
Dostęp do tabeli Dostęp do tabeli
Pełne przeglądniecie tabeli Dostęp przy pomocy adresu rekordu
" Ang. full table scan " Ang. rowid scan
" Sekwencyjny odczyt wszystkich bloków danych, w których tabela " Odszukanie rekordu relacji na podstawie dostarczonego adresu
przechowuje swoje rekordy, odfiltrowanie rekordów nie rekordu (rowid).
spełniających zdefiniowanych w poleceniu SQL kryteriów selekcji
" Najszybszy sposób dostępu
(np. w klauzuli WHERE).
do rekordów tabeli.
" Stosowane gdy:
" y
ródło adresu rekordu:
" brak indeksu dla relacji lub
" warunek selekcji polecenia SQL,
nie można użyć istniejących
indeksów,
SELECT * FROM pracownicy
DB_FILE_MULTIBLOCK_READ_COUNT
" zostanie odczytana duża część
WHERE rowid =  AAAMMUAAEAAAAAtAAG ;
wszystkich bloków, w których tabela składuje swoje dane,
" rozmiar tabeli jest niewielki.
" pobranie z indeksu tabeli.
" Możliwy odczyt wieloblokowy  pobranie w jednej operacji I/O wielu
przyległych bloków danych, bardziej efektywne niż wiele odczytów
pojedynczych bloków.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
15 16
Dostęp do indeksu Dostęp do indeksu
Unikalne przeglądnięcie indeksu Zakresowe przeglądnięcie indeksu
" Ang. index unique scan " Ang. index range scan
" Dostęp do indeksu unikalnego, operacja zwraca co najwyżej jeden " Dostęp do indeksu unikalnego (warunek inny niż równościowy) lub
adres rekordu. nieunikalnego, operacja zwraca zakres adresów rekordów.
" Stosowane, gdy w poleceniu SQL zastosowano warunek " Stosowane, gdy w poleceniu SQL:
równościowy z atrybutem, na którym zdefiniowano indeks unikalny
" warunek selekcji z kolumnami z części wiodącej indeksu, takie jak:
(również ograniczenia klucz podstawowy i klucz unikalny).
" kolumna =  wartość , kolumna >  wartość , kolumna <  wartość
(również kombinacje powyższych)
" kolumna like  ABC% (% nie może być na początku wzorca),
" warunek złożony z ww. warunków ze spójnikiem AND,
" klauzula ORDER BY lub GROUP BY z atrybutami z części wiodącej
indeksu.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
17 18
Dostęp do indeksu Dostęp do indeksu
Odwrócone zakresowe przeglądnięcie indeksu Przeglądnięcie indeksu z pominięciem kolumn
" Ang. index range scan descending " Ang. index skip scan
" Odmiana zakresowego przeglądnięcia indeksu. " Operacja korzystająca z indeksu złożonego dla polecenia, w którym
nie występuje kolumna z początku części wiodącej klucza
" Dane zwracane w kolejności malejącej .
indeksowego:
" Stosowane, gdy:
" indeks dzielony jest na mniejsze podindeksy, liczba podindeksów jest
" w poleceniu konieczne posortowanie danych w porządku malejącym,
równa liczbie wartości pierwszej kolumny w kluczu indeksowym,
" przy poszukiwaniu wartości mniejszych niż wartość wyspecyfikowana.
" podindeksy skanowane są kolejno  operacja zastępuje pełne
przeglądnięcie relacji.
" Przykład:
" relacja Pracownicy(id_prac, adres, płeć), indeks o strukturze klucza:
(płeć, id_prac), zapytanie: select * from Pracownicy where id_prac =
100
" indeks zostaje podzielony na dwa podindeksy: dla wartości płeć =  M i
dla wartości płeć =  K , podindeksy zostają przeskanowane kolejno.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
19 20
Dostęp do indeksu Dostęp do indeksu
Pełne przeglądnięcie indeksu Szybkie pełne przeglądnięcie indeksu
" Ang. full index scan " Ang. fast full index scan
" Stosowane, gdy: " Stosowane, gdy:
" w warunku polecenia SQL odwołania do kolumn z klucza indeksowego, " wszystkie kolumny, które są używane w poleceniu SQL, występują w
kolumny nie muszą być częścią wiodącą klucza, kluczu indeksowym,
" brak odwołań do poindeksowanych kolumn w warunku polecenia, ale: " co najmniej jedna z tych kolumn ma zdefiniowane ograniczenie NOT
NULL.
" wszystkie kolumny, do których występuje odwołanie w poleceniu (np. w
klauzuli SELECT), znajdują się w kluczu indeksowym,
" Zastępuje pełne przeglądnięcie relacji  wynik polecenia SQL
" przynajmniej jedna z tych kolumn nie jest pusta.
uzyskuje się bezpośrednio z indeksu, bez konieczności dostępu do
" Odczytywane są wszystkie liście indeksu w porządku, bloki indeksu
relacji.
odczytywane pojedynczo.
" Odczytywane są wszystkie liście indeksu przy zastosowaniu
" Używane głównie do eliminacji operacji sortowania relacji  dane są
odczytu wieloblokowego  większa wydajność niż pełne
posortowane wg klucza indeksowego.
przeglądnięcie indeksu, ale nie zostaje zachowane uporządkowanie.
" Nie może być używany do eliminacji operacji sortowania relacji 
dane nie są posortowane wg klucza indeksowego.
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
21 22
Dostęp do indeksu Dostęp do indeksu
Dostęp do indeksu bitmapowego Połączenie indeksów
" Składa się z dwóch kroków: " Ang. index join
1. dostęp do bitmapy, " Stosowane w przypadku, gdy wszystkie kolumny, używane w
poleceniu SQL, znajdują się w kluczach kilku różnych indeksów.
2. konwersja bitmapy do adresów rekordów (krok opuszczany w
przypadku możliwości realizacji polecenia bez dostępu do " Wynik polecenia uzyskuje się tylko z indeksów, bez konieczności
relacji). dostępu do relacji.
" W przypadku poleceń z warunkiem złożonym (spójniki AND i OR, " Nie może być stosowane do eliminacji operacji sortowania relacji.
negacja), operacje koniunkcji, alternatywy i negacji wykonywane
" Przykład:
bezpośrednio na bitmapach (widoczne w planie wykonania
SELECT id_prac FROM pracownicy WHERE placa_pod >1000;
polecenia).
Range scan(indeks na placa_pod) Fast Full Scan(indeks na id_prac)
placa_pod
ROWID
id_prac ROWID
1600 00000001.001.001
1600 00000001.001.001
120 00000001.0A1.01E
120 00000001.0A1.01E
... ...
... ...
... ...
... ...
2000 00000001.0A1.01E
2000 00000001.0A1.01E
140 00000001.001.001
140 00000001.001.001
120
120
join (hash)
...
...
140
140
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
23 24
Wskaz
nik zgrupowania indeksu (1) Wskaz
nik zgrupowania indeksu (2)
" Minimalną jednostką operacji I/O jest blok dyskowy a nie rekord " Interpretacja:
" mała wartość (równa lub bliska liczbie bloków tabeli)  dobrze, użycie
indeksu jest korzystne w stosunku do pełnego przeglądnięcia tabeli z
" Statystyka, pozwalająca na porównanie kosztu operacji
powodu konieczności wykonania mniejszej liczby operacji odczytu
przeglądnięcia indeksu z kosztem pełnego przeglądnięcia tabeli
bloków tabeli (odczytu danych) po dostępie do indeksu (po odczycie
adresów rekordów)
" Określa, jak mocno indeks jest "zsynchronizowany" z tabelą:
" mała wartość  rekordy tabeli z tymi samymi (lub zbliżonymi) " duża wartość (równa lub bliska liczbie rekordów tabeli)  z
le, użycie
wartościami poindeksowanej kolumny są skupione w niewielkiej liczbie indeksu jest niekorzystne w stosunku do pełnego przeglądnięcia tabeli
bloków z powodu konieczności wykonania większej liczby operacji odczytu
bloków tabeli po dostępie do indeksu
" duża wartość  rekordy tabeli z tymi samymi (lub zbliżonymi)
wartościami poindeksowanej kolumny są rozproszone w dużej liczbie
" Słownik danych
bloków
SELECT clustering_factor FROM user_indexes
WHERE index_name =  PRAC_PK ;
(c) Instytut Informatyki Politechniki Poznańskiej (c) Instytut Informatyki Politechniki Poznańskiej
25 26
Wskaz
nik zgrupowania indeksu (3)
" Przykład  tabela posiada 9 rekordów, poindeksowana kolumna K1
posiada trzy wartości A, B i C (po trzy rekordy), rekordy zajmują 3
bloki.
" Przypadek 1. Mała wartość wskaz
nika. Niski koszt skanu indeksu 
odczyt A wymaga dostępu do jednego bloku tabeli
BLOK 1 BLOK 2 BLOK 3
A A A B B B C C C
" Przypadek 2. Duża wartość wskaz
nika. Wyższy koszt skanu indeksu
 odczyt A wymaga dostępu do wszystkich trzech bloków tabeli
BLOK 1 BLOK 2 BLOK 3
A B C A B C A B C
(c) Instytut Informatyki Politechniki Poznańskiej
27