Strona główna > Oracle Database > OPT_PARAM, czyli testujemy parametry

OPT_PARAM, czyli testujemy parametry

Ile to już czasu minęło od ostatniego wpisu… Nie chcę nawet liczyć… Bardzo was za to przepraszam, ale mam jedno usprawiedliwienie – wcale się nie obijałem🙂 Pracy jest całe mnóstwo, problemów do rozwiązania drugie tyle, a kłopotów przy wdrażaniu rozwiązań jeszcze raz tyle🙂

Dzisiaj przypatrzymy się hintowi OPT_PARAM.

OPT_PARAM działa podobnie jak ALTER SESSION, ale jedynie w zakresie jednego zapytania SQL. Pozwala to, na testowanie zapytań bez pamiętania o tym, aby ustawić komendą ALTER SESSION odpowiednią wartość dla jakiegoś parametru.  Zamiast więc:

ALTER SESSION SET nazwa_parametru = nowa_wartość;
SELECT * FROM ...
ALTER SESSION SET nazwa_parametru = stara_wartość;

Możemy napisać:

SELECT /*+ opt_param('nazwa_parametru','wartość') */ * FROM ...

i nie musimy pamiętać o ALTERowaniu przed i po wykonaniu zapytania.

Jak to działa w praktyce?
Prosty przykład z wykorzystaniem przykładowego schematu HR.

SQL> select * from
2  employees e, departments d
3  where e.department_id = d.department_id;


Plan wykonywania
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=106 Bytes=9328
1    0   NESTED LOOPS (Cost=4 Card=106 Bytes=9328)
2    1     TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 Bytes=7276)
3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=20)
4    3       INDEX (UNIQUE SCAN) OF 'DEPT_ID_PK' (INDEX (UNIQUE))  (Cost=0 Card=1)

Plan wykonania zawiera metodę złączenia NESTED LOOPS. Jednym z czynników, dlaczego tak się dzieje jest odpowiednia wartość parametru optimizer_index_cost_adj. Przy próbie zdecydowanego zwiększenia parametru, Oracle stwierdzi, że korzystanie z indeksu nie jest tak atrakcyjne, i powinien zmienić plan wykonania zapytania. Popatrzmy :

SQL> select /*+ opt_param('optimizer_index_cost_adj',1000) */ * from
2  employees e, departments d
3  where e.department_id = d.department_id;


Plan wykonywania
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=106 Bytes=328)
1    0   HASH JOIN (Cost=7 Card=106 Bytes=9328)
2    1     TABLE ACCESS (FULL) OF 'DEPARTMENTS' (TABLE) (Cost=3 Card=27 Bytes=540)
3    1     TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 Bytes=7276)

Jak widzimy, po zastosowaniu nienaturalnie dużej wartości parametru optimizer_index_cost_adj, plan zapytania zmienił się, i Oracle zdecydował się na wybór HASH JOINa.

Gdy jednak dodamy kolejną zmianę, i zechcemy testować powyższe zapytanie uniemożliwiając optymalizatorowi wykorzystanie HASH JOIN :

SQL> select /*+ opt_param('optimizer_index_cost_adj',1000)
opt_param('hash_join_enabled','false') */ *
3  from employees e, departments d
4  where e.department_id = d.department_id;


Plan wykonywania
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=106 Bytes=9328)
1    0   MERGE JOIN (Cost=8 Card=106 Bytes=9328)
2    1     SORT (JOIN) (Cost=4 Card=27 Bytes=540)
3    2       TABLE ACCESS (FULL) OF 'DEPARTMENTS' (TABLE) (Cost=3 Card=27 Bytes=540)
4    1     SORT (JOIN) (Cost=4 Card=107 Bytes=7276)
5    4       TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 Bytes=7276)

Optymalizator, biorąc pod uwagę ustawienie dwóch parametrów podanych poprzez hint OPT_PARAM, wybierze jeszcze inny plan wykonania – wykorzystując MERGE JOIN.

Tak jak wspomniałem – hint ten jest najczęściej stosowany przy testowaniu zapytań. Oczywiście, nic nie stoi na przeszkodzie, aby zapytania z takimi wskazówkami działały produkcyjnie, jednak radziłbym uważać z takim wykorzystaniem tej funkcjonalności.

Na koniec lista zalet i wad tego rozwiązania wg niezawodnego Toma Kyle :

pro’s
——–
you can use this to override bad „non-default” settings like you did

you can use this to see ‚what would happen’

it can be used in many cases to tell the optimizer „don’t consider doing X, we know X is bad right now” (as opposed to ‚bad’ hints which tell the optimizer „don’t consider anything BUT x”)

con’s
———-
you could use this to set non-default optimizer parameters….

we could end up with developers using this as a first approach to „tuning” when it should be way at the end of the list

Cała dyskusja na AskTom.com : Tom Kyle o hincie OPT_PARAM

To na tyle dzisiaj. Do następnego (mam nadzieję, że tym razem nie będzie 2 miesięcznej przerwy😉 )

  1. Brak komentarzy.
  1. No trackbacks yet.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

%d bloggers like this: