Strojenie poleceń SQL – zadania

Wstęp

1. Utwórz w swoim schemacie dwie relacje, PRAC (10 000 rekordów) i ZESP (5 rekordów), potrzebne do ćwiczeń. Użyj do tego celu skryptu opt.sql.

2. Utwórz w swoim schemacie relację PLAN_TABLE, wykonując skrypt utlxplan.sql

3. Wyjaśnij plan poniższego zapytania, używając polecenia EXPLAIN_PLAN, nadaj wygenerowanemu planowi identyfikator „p1”:

select nazwa, count(*) from zesp natural join prac

group by nazwa

order by count(*) desc;

explain plan

set statement_id = ‘p1’

for

select nazwa, count(*) from zesp natural join prac

group by nazwa

order by count(*) desc;

Następnie odczytaj plan zapytania, używając:

- zapytania:

select operation,object_name,id,cost,parent_id
from plan_table where statement_id=’p1’ order by id;

- skryptu utlxpls.sql

4. Przyłącz się ponownie do bazy danych jako użytkownik OPT_USER, włącz dyrektywę SET AUTOTRACE ON EXPLAIN i pomiar czasu dyrektywą SET TIMING ON. Sprawdź ponownie plan wykonania zapytania z punktu 3., wykorzystując działanie zastosowanych dyrektyw.

Metody dostępu

5. Wyjaśnij plan poniższego zapytania. Jaką metodą dostępu wybrał optymalizator do wykonania tego zapytania?

select rowid, nazwisko, plec, placa

from prac

where id_prac = 900;

6. Zdefiniuj zapytanie, które odczyta te same dane, co zapytanie z zadania 5., posłuż się w tym celu adresem rekordu (rowid), odczytanym w zadaniu 5. Następnie wyjaśnij plan tego zapytania. Jaka metoda dostępu do danych została użyta?

select rowid, nazwisko, plec, placa

from prac

where rowid = ‘AAABbYAADAAAAA0AAN’;

7. Wyłącz dyrektywę AUTOTRACE. Sprawdź w słowniku bazy danych, jakie indeksy założono na relacji PRAC.

select index_name

from user_indexes

where table_name = ‘PRAC’;

8. Utwórz indeks typu B-drzewo o nazwie PRAC_IDX na atrybucie ID_PRAC relacji PRAC. Następnie ponownie wyjaśnij plan zapytania z punktu 5. Czym różni się otrzymany plan?

create index prac_idx on prac(id_prac);

9. Usuń indeks PRAC_IDX. Następnie na atrybucie ID_PRAC relacji PRAC zdefiniuj klucz podstawowy o nazwie PRAC_PK.

drop index prac_idx;

alter table prac add constraint prac_pk primary key(id_prac);

10. Wyłącz dyrektywę AUTOTRACE. Ponownie sprawdź w słowniku bazy danych indeksy dla relacji PRAC. Wyświetl dodatkowo nazwy poindeksowanych atrybutów. Co zauważyłeś/aś?

set autotrace off

select c1.index_name, uniqueness, column_name, column_position

from user_indexes c1, user_ind_columns c2

where c1.table_name = ‘PRAC’

and c1.index_name = c2.index_name;

11. Włącz dyrektywę AUTOTRACE. Ponownie wyjaśnij plan zapytania z punktu 5. Czym różni się otrzymany plan?

12. Utwórz indeks typu B-drzewo o nazwie PRAC_NAZWISKO_IDX na atrybucie NAZWISKO relacji PRAC. Następnie wykonaj poniższe zapytania, dla każdego wyjaśniając jego plan.

create index prac_nazwisko_idx on prac(nazwisko);

select * from prac where nazwisko = ‘Prac155’;

select * from prac where nazwisko like ‘Prac155%’;

select * from prac where nazwisko like ‘%Prac155%’;

select * from prac where nazwisko like ‘Prac155%’

or nazwisko like ‘Prac255%’;

Dlaczego w przedostatnim zapytaniu optymalizator nie użył indeksu?

13. Usuń indeks PRAC_NAZWISKO_IDX i na jego miejsce utwórz skonkatenowany indeks typu B-drzewo o nazwie PRAC_NAZW_PLACA_IDX na atrybutach NAZWISKO i PLACA relacji PRAC. Następnie wykonaj poniższe zapytania, dla każdego wyjaśniając jego plan.

drop index prac_nazwisko_idx;

create index prac_nazw_placa_idx on prac(nazwisko, placa);

select count(*) from prac where nazwisko like ‘Prac1%’;

select count(*) from prac where nazwisko like ‘Prac1%’ and placa > 100;

select count(*) from prac where placa > 100;

Dlaczego w ostatnim zapytaniu optymalizator nie użył indeksu?

14. Utwórz indeks typu B-drzewo o nazwie PRAC_PLEC_IDX na atrybucie PLEC relacji PRAC. Następnie wykonaj poniższe zapytanie.

create index prac_plec_idx on prac(plec);

select count(*) from prac where plec='M'

and id_prac between 100 and 110;

15. Przełącz się na optymalizator kosztowy i ponownie wyjaśnij plan zapytania z zadania 14. Czy zaobserwowałeś/aś jakąś różnice? Jaki typ optymalizatora został użyty do wykonania zapytania?

alter session set optimizer_goal = choose;

16. Wyłącz dyrektywę AUTOTRACE. Sprawdź, czy dla relacji PRAC zebrano statystyki. Skorzystaj z poniższych zapytań.

select table_name, last_analyzed, num_rows from user_tables

where table_name=’PRAC’;

select column_name, num_distinct, low_value, high_value

num_buckets from user_tab_columns where table_name = 'PRAC’;

select index_name, last_analyzed, num_rows from user_indexes

where table_name=’PRAC’;

select * from user_tab_histograms where table_name=’PRAC’;

17. Zbierz statystyki dla relacji PRAC (użyj polecenia ANALYZE TABLE). Następnie ponownie wykonaj zapytania z zadania 16.

analyze table prac compute statistics for table;

18. Zbierz statystyki dla indeksów relacji PRAC. Następnie ponownie wykonaj zapytania z zadania 16.

analyze table prac compute statistics for all indexes;

19. Zbuduj histogramy dla wszystkich poindeksowanych kolumn relacji PRAC. Następnie ponownie wykonaj zapytania z zadania 16.

analyze table prac compute statistics for all indexed columns;

20. Włącz dyrektywę AUTOTRACE. Powtórz zapytanie z punktu 14. Jakie zauważyłeś/aś różnice w stosunku do wyniku zadania 14, wykonywanego bez statystyk?

21. Wykonaj poniższe zapytanie. Co zauważyłeś/aś? Czy przy odpowiedzi na poniższe zapytanie optymalizator korzysta z relacji PRAC?

select count(*) from prac;

22. Utwórz indeks typu B-drzewo o nazwie PRAC_CZY_ETAT_IDX na atrybucie CZY_ETAT relacji PRAC. Zbierz statystyki dla tego indeksu. Następnie wykonaj poniższe zapytanie i zanalizuj jego plan.

create index prac_czy_etat_idx on prac(czy_etat) compute statistics;

select count(*) from prac where czy_etat='T' and plec='K';

23. Usuń indeksy na atrybutach PLEC i CZY_ETAT, wykonaj ponownie zapytanie z zadania 22. Czy widzisz różnice w obu planach?

drop index prac_plec_idx;

drop index prac_czy_etat_idx;

24. Utwórz indeksy bitmapowe na relacji PRAC, na atrybutach PLEC i CZY_ETAT, o nazwach odpowiednio PRAC_PLEC_BMP_IDX i PRAC_CZY_ETAT_BMP_IDX. Zbierz statystyki dla obu indeksów.

create bitmap index prac_plec_bmp_idx on prac(plec) compute statistics;

create bitmap index prac_czy_etat_bmp_idx on prac(czy_etat) compute statistics;

25. Włącz optymalizator regułowy, następnie wykonaj poniższe zapytanie:

alter session set optimizer_goal = rule;

select count(*) from prac where czy_etat='T' and plec='K';

26. Włącz optymalizator kosztowy, następnie wykonaj ponownie zapytanie z zadania 25. Jakie różnice zaobserwowałeś/aś?

alter session set optimizer_goal = choose;

27. Utwórz indeks typu B-drzewo o nazwie PRAC_PLACA_IDX na atrybucie PLACA relacji PRAC. Zbierz statystyki dla tego indeksu.

create index prac_placa_idx on prac(placa) compute statistics;

28. Sprawdź plany następujących zapytań:

select nazwisko from prac where placa < 2;

select nazwisko from prac where ROUND(placa) < 2;

Dlaczego drugie zapytanie nie korzysta z indeksu na płacy?

29. Ustaw parametry sesji, umożliwiające korzystanie z przepisywania zapytań (niezbędne w przypadku użycia indeksów funkcyjnych):

alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=trusted;

30. Utwórz indeks funkcyjny o nazwie PRAC_PLACA_FUN_IDX na relacji PRAC, który będzie używany przy zapytaniu o zaokrągloną wartość płacy pracownika. Wykonaj ponownie drugie zapytania z zadania 28. Jakie różnice zaobserwowałeś/aś?

create index prac_placa_fun_idx on prac(round(placa));

Sortowanie

31. Włącz dyrektywę AUTOTRACE z opcją TRACEONLY. Porównaj plany wykonania następujących zapytań:

set autotrace traceonly;

select * from prac order by id_prac;
select * from prac order by id_prac desc;
select * from prac order by nazwisko;
select distinct nazwisko from prac;
select nazwisko from prac group by nazwisko;

Tabele IOT

32. Zbuduj tabelę o organizacji indeksowej o nazwie PRAC_ZESP_IOT i zapełnij danymi z tabel prac i zesp, wykonując poniższe zapytanie.

CREATE TABLE prac_zesp_iot (

id_prac NUMBER PRIMARY KEY,

nazwisko VARCHAR2(20),

nazwa VARCHAR2(20) )

ORGANIZATION INDEX

PCTTHRESHOLD 20

OVERFLOW TABLESPACE USERS;

insert into prac_zesp_iot select id_prac, nazwisko, nazwa
from prac, zesp where prac.id_zesp = zesp.id_zesp;

Sprawdź plan wykonania poniższego zapytania do takiej tabeli.

select * from prac_zesp_iot where id_prac < 100;

Klastry

33. Zbuduj klaster indeksowy o nazwie PRAC_ZESP_CI do przechowywania danych pracowników i zespołów.

create cluster prac_zesp_ci(id_zesp number);

create index prac_zesp_ci_idx on cluster prac_zesp_ci;

create table zesp_ci

cluster prac_zesp_ci(id_zesp) as select * from zesp;

create table prac_ci

cluster prac_zesp_ci(id_zesp) as select * from prac;

34. Sprawdź plan wykonania zapytania do tabel klastrowanych:

select count(*) from zesp_ci natural join prac_ci

where nazwa = ‘ALGORYTMY’;

Tabele partycjonowane

35. Utwórz tabelę partycjonowaną PRAC_PART, w której dane pracowników są podzielone na następujące partycje zgodnie z wartością atrybutu PLACA:

- „biedni” – płaca mniejsza niż 300 zł,

- „sredni” – płaca większa lub równa 300 zł, mniejsza niż 800

- „bogaci” – płaca większa lub równa 800 zł.

Wstaw do tabeli dane z tabeli PRAC. Następnie zanalizuj wyniki i plany wykonania poniższych zapytań.

create table prac_part(

id_prac number,

nazwisko varchar2(50),

placa number)

partition by range(placa)

(partition biedni values less than (300),

partition sredni values less than (800),

partition bogaci values less than (maxvalue));

insert into prac_part

select id_prac, nazwisko, placa

from prac;

select count(*) from prac_part;

select count(*) from prac_part partition(biedni);

select count(*) from prac_part partition(sredni);

select count(*) from prac_part partition(bogaci);

Połączenia

36. Włącz optymalizator regułowy.

alter session set optimizer_goal = rule;

Ustaw tryb wyjaśniania zapytań dyrektywą

set autotrace traceonly explain

37. Sprawdź plan wykonania następujących zapytań z połączeniem naturalnym:

select count(*) from zesp natural join prac;

select count(*) from prac natural join zesp;

38. Utwórz indeks typu B-drzewo o nazwie ZESP_ID_ZESP_IDX na atrybucie ID_ZESP relacji ZESP. Wykonaj ponownie oba zapytania z zadania 37.

create index zesp_id_zesp_idx on zesp(id_zesp);

39. Utwórz indeks typu B-drzewo o nazwie PRAC_ID_ZESP_IDX na atrybucie ID_ZESP relacji PRAC. Wykonaj ponownie oba zapytania z zadania 37.

create index prac_id_zesp_idx on prac(id_zesp);

40. Włącz optymalizator kosztowy, zbierz statystyki dla tabeli ZESP i jej indeksów oraz tabeli PRAC i jej indeksu PRAC_ID_ZESP_IDX. Wykonaj ponownie oba zapytania z zadania 37.

alter session set optimizer_goal = choose;

analyze table zesp compute statistics;

analyze table zesp compute statistics for all indexes;

analyze table prac compute statistics;

analyze table prac compute statistics for all indexes;

41. Usuń wszystkie indeksy (poza indeksem na kluczu głównym tabeli PRAC). Wykonaj poniższe zapytanie i przeanalizuj jego plan wykonania.

drop index PRAC_CZY_ETAT_BMP_IDX;

drop index PRAC_ID_ZESP_IDX;

drop index PRAC_NAZW_PLACA_IDX;

drop index PRAC_PLACA_FUN_IDX;

drop index PRAC_PLACA_IDX;

drop index PRAC_PLEC_BMP_IDX;

select nazwa, count(*) from prac natural join zesp group by nazwa;

Statystyki, histogramy

42. Wyłącz dyrektywę AUTOTRACE. Usuń statystyki dla tabeli PRAC. Następnie dokonaj oszacowania statystyk dla tabeli PRAC na podstawie próbki 10%. Sprawdź informacje o statystykach dla tabeli PRAC.

set autotrace off

analyze table prac delete statistics;

analyze table prac estimate statistics sample 10 percent;

select table_name, last_analyzed, num_rows from user_tables

where table_name=’PRAC’;

select column_name, num_distinct, low_value, high_value

num_buckets from user_tab_columns where table_name = 'PRAC’;

select index_name, last_analyzed, num_rows from user_indexes

where table_name=’PRAC’;

select * from user_tab_histograms where table_name=’PRAC’;

43. Poznaj rozkład wartości atrybutu PLACA_DOD w tabeli PRAC.

select placa_dod, count(*)

from prac

group by placa_dod;

44. Usuń statystyki dla tabeli PRAC. Utwórz indeks typu B-drzewo o nazwie PRAC_PLACA_DOD_IDX na atrybucie PLACA_DOD tabeli PRAC. Włącz tryb wyświetlania plany zapytania.

analyze table prac delete statistics;

create index prac_placa_dod_idx on prac(placa_dod);

set autotrace traceonly explain

45. Wyświetl plany poniższych zapytań

select * from prac where placa_dod = 100;

select * from prac where placa_dod = 999;

Co zaobserwowałeś/aś?

46. Zbierz statystyki dla tabeli PRAC. Powtórz zapytania z zadania 45.

analyze table prac compute statistics;

47. Utwórz histogram dla atrybutu PLACA_DOD tabeli PRAC. Powtórz zapytania z zadania 45.

analyze table prac compute statistics for columns placa_dod;

Wskazówki

48. Cel optymalizacji. Wykonaj poniższe zapytanie, wyświetl plan tego zapytania.

select count(*) from prac where plec = 'K';

Następnie dodaj do zapytania taką wskazówkę, aby zapytanie to zrealizował optymalizator regułowy.

select /*+RULE*/ count(*) from prac where plec = 'K';

49. Jeśli istnieje, usuń indeks bitmapowy PRAC_PLEC_BMP_IDX, założony na atrybucie PLEC tabeli PRAC. Następnie utwórz na tym samym atrybucie indeks B-drzewo o nazwie PRAC_PLEC_IDX.

drop index PRAC_PLEC_BMP_IDX;

create index prac_plec_idx on prac(plec);

50. Dostęp do danych. Sprawdź plan poniższego zapytania. Następnie dodaj do zapytania taką wskazówkę, aby optymalizator kosztowy użył utworzonego w zadaniu 49. indeksu.

select count(*) from prac where id_prac < 100 and plec = ‘K’;

select /*+INDEX (prac prac_plec_idx)*/count(*)

from prac where id_prac < 100 and plec = ‘K’;

51. Dostęp do danych. Wykonaj ponownie zapytanie z zadania 50, tym razem dodaj wskazówkę, która spowoduję, że optymalizator nie użyje żadnego indeksu przy dostępie do tabeli PRAC.

select /*+NO_INDEX (prac)*/count(*)

from prac where id_prac < 100 and plec = ‘K’;

52. Dostęp do danych. Sprawdź plan poniższego zapytania.

select * from prac

where id_prac = 100 or id_prac = 200 or id_prac = 300;

Nastepnie dodaj do zapytania wskazówkę USE_CONCAT. Jak zmienił się plan zapytania?

select /*+ USE_CONCAT */ * from prac

where id_prac = 100 or id_prac = 200 or id_prac = 300;

53. Kolejność łączenia tabel. Sprawdź plan wykonania poniższego zapytania.

select * from prac natural join zesp;

Następnie dodaj do zapytania wskazówkę ORDERED. Co zaobserwowałeś/aś? Zmień kolejność relacji w klauzuli from i ponownie wykonaj zapytanie.

select /*+ ORDERED */ * from prac natural join zesp;

54. Porównaj koszty wykonania połączenia naturalnego tabel PRAC i ZESP za pomocą każdego algorytmu łączenia tabel i dla każdej kolejności łączonych tabel (6 kombinacji). Określ plan charakteryzujący się najniższym kosztem.

SQL*Trace

55. Wyłącz wyjaśnianie planu zapytania.

set autotrace off

56. Jako użytkownik SYS sprawdź wartość zmiennej USER_DUMP_DEST, wskazującej katalog, w którym zostanie umieszczony plik śladu.

show parameter user_dump_dest

57. Uruchom narzędzie SQL*Trace, wykonaj kilka zapytań, następnie wyłącz SQL*Trace.

alter session set sql_trace=true;

select count(*) from prac group by id_zesp;
update prac set placa=placa+34
where plec=’K’ and czy_etat=’T’ and id_prac < 100;
alter session set sql_trace=false;

58. Użyj programu TKProf do sformatowania wyniku, który znajduje się w pliku śladu

tkprof <plik_sladu> plik.txt aggregate=yes sys=no

explain=opt_user/opt_user

Obejrzyj wynik i usuń pliki

59. Jako użytkownik SYS odczytaj z perspektywy v$session dane o sesji użytkownika OPT_USER.

select sid, serial# from v$session
where username = ’OPT_USER’;

Następnie uruchom niewidocznie dla użytkownika OPT_USER śledzenie poleceniem (w miejsce sid i serial# wpisz wartości uzyskane w poprzednim zapytaniu):

exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,true);

60. Jako OPT_USER wykonaj kilka zapytań:

select count(*) from prac;
select max(placa) from prac;
delete from prac_zesp_iot;

61. Jako SYS zakończ śledzenie sesji użytkownika OPT_USER poleceniem:

exec dbms_system.set_sql_trace_in_session(<sid>,<serial#>,false);

62. Użyj programu TKProf do sformatowania wyniku, który znajduje się w pliku:

tkprof <plik_sladu> plik.txt aggregate=yes sys=no explain=opt_user/opt_user

Obejrzyj wynik i usuń pliki