Noworoczne postanowienia

Cześć Wam,

Niestety, z powodu prawdziwej lawiny pracy od października do grudnia, nie udało się dodać żadnej notki w roku 2011. Szkoda, bo każdy wpis tutaj działa na mnie mobilizująco. Aby ta sytuacja więcej się nie powtórzyła postanowiłem zrobić listę noworocznych postanowień zawodowych.

Oto ta lista:

1. Aktualizacja bloga, bez narzuconego reżimu czasowego (codziennie, co tydzień etc), za to z reżimem ilościowym i jakościowym.

Myślę, że dodawanie 4-6 postów miesięcznie, odpowiednio wyczerpujących poruszane w nich tematy, będzie dobrym początkiem.

2. Jedna książka techniczna na 2 miesiące.

W zeszłym roku rozpocząłem czytanie wielu pozycji, ale niewiele udało się doczytać do końca.

3. Jedna książka nie-techniczna na 2 miesiące.

Ze względu na nowe dla mnie role jakie zaczynam pełnić w projektach, chciałbym się podszkolić z zakresu umiejętności miękkich.

4. Napisanie chociaż jednego kursu/poradnika/szkolenia.

Jednym z moich celów w przyszłości jest prowadzenie szkoleń. Aby to osiągnąć muszę w końcu spróbować napisać krótki poradnik/tutorial – chociażby o podstawach SQL.

5. Segregowanie dokumentów.

To pierwszy rok prowadzenia przeze mnie działalności, i czasem zdarza się, że faktury „gubią się”, aby później się znaleźć np w dokumentacji technicznej projektu 🙂

6. Poznanie 2 zupełnie nowych dla mnie technologii.

Bądź niezupełnie nowych, ale takich, które znam na poziomie podstawowym.

7. Zrobienie (w końcu) certyfikatu OCA.

Planuję od dosyć dawna, ale nie mogę wykonać.

8. Lepsze poznanie Oracle Spatial i Oracle Text.

Znam w pewnym – niezadowalającym mnie stopniu.

9. Napisanie chociaż jednej aplikacji w Oracle APEX.

Jeden pomysł chodzi mi już po głowie.

10. Wykonanie próby codziennej pracy (przez np 2 tygodnie) wykorzystując inny OS niż Windows.

11. Regularne poszerzanie wiedzy związanej z działaniem bazy Oracle.

12. Zakończenie (z sukcesem!) obecnych projektów i rozpoczęcie kolejnych.

 

Trzymajcie kciuki! 🙂

Pozdrawiam

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)
2  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 😉 )

FAST REFRESH w widokach zmaterializowanych – nie taki szybki ?!

Witajcie,

Po ostatnim wybitnie nietechnicznym wpisie (musiałem się pochwalić 🙂 ), teraz wracam już do tego co lubię opisywać najbardziej.

Zajmuję się właśnie tworzeniem kilku widoków zmaterializowanych (w skrócie będę je oznaczał jako MV – materialized view). Są to widoki oparte na prostych zapytaniach zawierających jedynie złączenia (bez agregacji). Wydawałoby się, że zadanie jest trywialne. W rzeczywistości można jednak napotkać na bardzo paskudny problem.

Krótkie przedstawienie sytuacji:
1. MV jest utworzona w oparciu o zapytanie wykorzystujące wielokrotne łączenie tej samej tabeli (SELF JOIN) – ze względu na występowanie relacji parent-child w tabeli.
2. Tabela nie jest w ciągu dnia zbyt często aktualizowana, główne zmiany zachodzą w trakcie nocnego przeładowania.
3. Ilość wierszy w tabeli to ok 5 mln.
4. MV umożliwia odświeżanie w trybie FAST (sprawdzone wcześniej przy użyciu explain_mview i tabeli mv_capabilities_table).
5. Wszystkie potrzebne statystyki są zbierane przy pomocy pakietu dbms_stats.

Kod wykorzystany do stworzenia loga dla MV, oraz samej MV wygląda mniej więcej tak (zmieniłem nazwy tabel, i nie wypisuje wszystkich kolumn oraz warunków – nie jest to konieczne przy opisywaniu przypadku):

Log MV:

CREATE MATERIALIZED VIEW LOG ON table1
WITH ROWID, PRIMARY KEY,SEQUENCE ( some other fields)
INCLUDING NEW VALUES;


MV:

create materialized view test_mv
tablespace tablespace_test
build immediate
refresh fast on demand
as
select
A.rowid as a_rowid,
B.rowid as b_rowid,
C.rowid as c_rowid,
D.rowid as d_rowid,
E.rowid as e_rowid,
F.rowid as f_rowid,
some_other_fields
from table1 A
, table1 B
, table1 C
, table1 D
, table1 E
, table1 F
where conditions


Po utworzeniu odpowiednich obiektów rozpocząłem testowanie rozwiązania.
Sprawdzenie loga MV – 0 wierszy. Odświeżenie – bardzo szybkie.
Ponowne sprawdzenie loga MV po jakimś czasie – 2 wiersze. Odświeżenie – wolno…zbyt wolno.

Co się takiego stało? Przecież MV ma się odświeżać w trybie FAST, są założone wszystkie potrzebne indeksy (również na kolumnach z ROWID w samej MV – temat na osobną notkę), baza nie jest obciążona, nie występują żadne locki na tabelach źródłowych.
Odpowiedź na pytanie udało mi się znaleźć analizując raport wygenerowany przez TKPROF.
Wykonałem więc:

alter session set events '10046 trace name context forever, level 8';
alter session set tracefile_identifier='gd';

I w raporcie znalazłem sprawcę problemów. Oto on:

INSERT INTO test_mv
SELECT /*+ NO_MERGE("JV$") */
/*+ */ some_columns
FROM
( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
table1 "MAS$" WHERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM m_log$_table1
"MAS$" WHERE "MAS$".SNAPTIME$$ > :1)
) "JV$",
table1 "MAS$1",
table1 "MAS$2",
table1 "MAS$3",
table1 "MAS$4",
table1 "MAS$5"
WHERE conditions;

Kilka statystyk i plan zaptyania:

W planie zapytania można zauważyć bardzo paskudny TABLE ACCESS FULL na naszej źródłowej tabeli mającej 5 mln wierszy… Nic więc dziwnego, że odświeżanie przy małej ilości danych w logu MV było tak wolne.
Ale zaraz… Oracle użył w zapytaniu, które wykonuje FULL SCAN tabeli pewnego hinta – HASH_SJ – który zmienia plan zapytania. Wykonywany jest HASH JOIN SEMI, który w działaniu jest bardzo podobny do zwykłego HASH JOIN. Obydwie uczestniczące w złączeniu tabele skanowane są jedynie raz. Hint ten jest pomocny w momencie, gdy nie posiadamy indeksu na kolumnie występującej w warunku złączenia. W tym jednak przypadku, wykonywana jest konstrukcja WHERE ROWID IN … , która pozwala nam na zastosowanie dostępu do tabeli przez ROWID : TABLE ACCESS BY USER ROWID (nie mam dzisiaj dostępu do bazy, i nie pokażę wam planu zapytania – musicie uwierzyć na słowo 🙂 ).

Oracle sam więc podjął decyzję o zastosowaniu HASH JOIN RIGHT SEMI i wymuszeniu w ten sposób wykonania pełnego skanu tabeli. Chwila googlowania i jak się okazało, nie jestem osamotniony. Rozwiązanie problemu jest bardzo ładnie przedstawione na blogu Alberto Dell’Ery :  problem wolnego FAST REFRESH rozwiązany przez Alberto
Pierwsza rzecz, która rzuca się w oczy – problem dotyczy tylko niektórych wersji Oracla. Wśród nich jest jednak 10.2.0.4, na której próbuję wykonać to zadanie.
W moim przypadku zadziałało pierwsze rozwiązanie zaproponowane przez Alberto : użycie ukrytego parametru _mv_refresh_use_stats, i ustawienie go na wartość TRUE.

Drugą możliwością jest zebranie statystyk na pustym logu MV i zablokowanie tychże statystyk – tej opcji już nie musiałem próbować.

Po ustawieniu parametru _mv_refresh_use_stats na wartość TRUE, wykonałem ponownie test.
Wszystko odbyło się szybko, sprawnie i bezboleśnie. FAST REFRESH dla mojego widoku jest teraz naprawdę szybki.

Więcej informacji dotyczących wykorzystywanego ukrytego parametru można znaleźć na orafaq : mv_refresh_use_stats na orafaq
Na podanej stronie znajduje się też bardzo ładne zdanie napisane na czerwono… Warto o tym pamiętać 😉

Wniosek :
Przy projektowaniu i testowaniu MV opartych na złączeniach (bez agregacji) na wersjach Oracle od 9i wzwyż należy zwrócić szczególną uwagę na odświeżenie MV w trybie FAST. Jeżeli ilość danych do odświeżenia jest stosunkowo mała (np 2-3 wiersze w logu MV), tabela źródłowa jest dosyć duża (w moim przypadku 5 mln wierszy), i odświeżenie trwa zbyt długo (w moim przypadku „zbyt długo” to 10 sekund), warto skorzystać z TKPROF i przyjrzeć się, czy przy insertach Oracle nie używa hinta HASH_SJ. Jeżeli tak się dzieje, to należy skorzystać z dwóch rozwiązań opisanych w tym poście.

Dziękuję za uwagę,
Do następnego 😉

Panie i Panowie… pozwólcie, że przedstawię – DBAdvice

Witajcie po bardzo długim okresie przerwy od blogowania. Niestety… doba ma jedynie 24 godziny, i ciężko w tak krótkim czasie zrobić wszystkie te rzeczy, na które ma się ochotę. Bloga zaniedbałem, jednak można by powiedzieć, że w słusznej sprawie. Zdecydowałem się bowiem na założenie własnej działalności gospodarczej.
Do podjęcia decyzji przygotowywałem się pół roku i przez ostatnie miesiące zbierałem potrzebne informacje potrzebne do startu. Uwierzcie – to nie jest takie proste. Szczególnie w Polsce.  Cała procedura trwała ponad miesiąc. Ale… tak czy inaczej… udało się!

I oto jest. DBAdvice.
Firma, która na początku skupiać się będzie jedynie na kilku rzeczach. O tym jakie to sprawy można się dowiedzieć rozkładając nazwę na czynniki pierwsze. I tak:

1. DBA – administrowanie i zarządzanie bazami danych Oracle
2. Advice – doradztwo z zakresu projektowania i programowania baz danych Oracle.
3. trochę zabawy słownej: Advice –> Advisor –> SQL Tuning Advisor – optymalizacja RDBMS Oracle

Oczywiście można powiedzieć, że co to za firma… jednoosobowa? bez należytego doświadczenia? oferująca pomoc jedynie przy jednej technologii?

Spokojnie… bez nerwów… Zdaję sobie sprawę, że przede mną długa droga…
Odpowiadając na powyższe (najczęściej pojawiające się w trakcie rozmów ze znajomymi) pytania:
Firmy jednoosobowe wcale nie są takie złe.. Znam kilka przypadków, gdzie takie firmy prowadzą naprawdę duże i ciekawe projekty.
Kwestia doświadczenia – zawsze jest go za mało… cały czas trzeba się uczyć… a czy może być wspanialszy sposób na jego zdobywanie niż poprzez własną, ciężką pracę, jaka na mnie czeka w trakcie prowadzenia firmy?
Jedna technologia – nikt nie powiedział, że tak będzie zawsze. Obecnie, oprócz głównych zainteresowań, które opisywane są na tym blogu, uczę się kilku nowych, ciekawych rzeczy. W odpowiednim momencie (przy dojściu na chociażby średni poziom zaawansowania) na pewno się o tym dowiecie.

Dzisiejszy post jest wybitnie nietechniczny, za co z góry przepraszam.
Mogłem już jakiś czas temu wrzucić jakąkolwiek notkę, jednak chciałem się wstrzymać, i dokładnie 1 maja ogłosić radosną nowinę.

Ponieważ w planach DBAdvice jest świadczenie usług szkoleniowych, od dzisiaj chciałbym umieszczać czasem na tym blogu techniczne problemy w formie tutoriali i mini szkoleń. Pozwoli mi to zweryfikować, czy moje umiejętności przekazywania wiedzy są na tyle dobre, aby móc kiedyś takie szkolenie poprowadzić.

Oczywiście, jestem również otwarty na wszelką formę współpracy. Jeżeli więc macie jakiś ciekawy pomysł, ale brakuje Wam bazodanowca, lub też po prostu potrzebujecie kogoś do małej pomocy – piszcie.

Następny post będzie techniczny – obiecuję 🙂

Nie ufaj kosztowi zapytania cz.2

Dzisiejszy wpis będzie kontynuacją poprzedniego. Przypomnę, że chodziło o ustalenie przyczyny wyboru przez Oracle planu zapytania (z metoda łączenia HASH JOIN), który w ostateczności dawał gorsze rezultaty (niż wymuszony hintem NESTED LOOP). Przypadkowo rozwinęła się ciekawa dyskusja na ten temat na goldenline. Link do tematu: Dyskusja na GoldenLine .

Zgodnie ze wskazówkami wypowiadających się tam osób wykonałem kilka testów. Wyniki zaprezentuję poniżej.

Ułożyłem krótki scenariusz testowy, który polegał na wykonaniu takiej sekwencji zdarzeń:

• wyczyszczenie bufora
• truncate tabeli testowej
• ustawienie odpowiednich parametrów
• insert do tabeli testowej z selecta, który wywoływany jest z różnym planem wybieranym przez CBO

Wyczyszczenie bufora realizowałem za pomocą
alter system flush buffer_cache;

Truncate tabeli testowej to polecenie
truncate table test_2;

Dochodzimy teraz do kluczowego punktu – ustawienia parametrów. Zgodnie z sugestiami zainteresowałem się parametrami OPTIMIZER_INDEX_CACHING oraz OPTIMIZER_INDEX_COST_ADJ. Okazało się, że nie były one prawidłowo ustawione w chwili wykonywania pierwszego testu (poprzedni wpis).
OPTIMIZER_INDEX_CACHING ustawiony był na wartość 0, OPTIMIZER_INDEX_COST_ADJ na 100. Są to wartości domyślne, które w większości środowisk działających produkcyjnie powinny zostać stosownie dostrojone. Pokrótce tłumacząc – pierwszy parametr podpowiada CBO ile procent indeksu może znajdować się w buforze. Drugi natomiast ustala czy indeksy mają być bardziej przyjazne niż full scany tabeli.
Testy, które zostały wykonane zostały powtórzone dla dwóch przypadków:

1. Wartości domyślne parametrów. OPTIMIZER_INDEX_CACHING = 0 , OPTIMIZER_INDEX_COST_ADJ = 100
2. Wartości dostrojone. OPTIMIZER_INDEX_CACHING = 50, OPTIMIZER_INDEX_COST_ADJ = 15

Kolejnym etapem był insert do tabeli testowej, która znajdowała się w trybie LOGGING (cała baza w trybie ARCHIVELOG). Były więc generowane znaczne ilości redologów oraz archivelogów.  Insert ten korzystał z wyników zapytania, które analizowane było w poprzednim poście. W kontekście tego testu wybrałem jednak kilka możliwości wykonania zapytania bazowego. Były to:

1. Zapytanie bez hintów
2. Zapytanie używające USE_NL
3. Zapytanie używające PARALLEL 5
4. Zapytanie używające PARALLEL 5 , USE_NL

Poniższa tabela pokazuje uzyskane wyniki:

	Czas wykonania zapytania w sek. (średnia z 3 prób)
	brak hintów	parallel 5	use_nl	parallel 5 + use_nl
OPTIMIZER_INDEX_CACHING = 50	137	135	200	120
OPTIMIZER_INDEX_COST_ADJ = 15
OPTIMIZER_INDEX_CACHING = 0	145	140	200	120
OPTIMIZER_INDEX_COST_ADJ = 100

Pomimo modyfikacji parametrów w dalszym ciągu najlepiej prezentuje się opcja PARALLEL , USE_NL – z poprzedniego wpisu widać, że nie jest to dla Oracla najlepsza ścieżka wykonania zapytania. Warto zwrócić uwagę na kolumnę PARALLEL 5 i wiersz, w którym wspomniane wyżej parametry zostały zmodyfikowane – czas wykonania operacji zmniejszył się – zmienił się plan (dzięki modyfikacji parametrów, CBO wykorzystuje teraz INDEX RANGE SCAN przy HASH JOINie)  :

W dalszym ciągu jednak metoda NESTED_LOOPS okazuje się szybsza niż HASH JOIN.

Wykonałem zatem sprawdzenie tych dwóch opcji wykonania zapytania (za pomocą sqlplusa i autotrace).

Dla zmodyfikowanych parametrów OPTIMIZER_INDEX oraz z hintem PARALLEL (średni czas wykonania operacji – 135 sek.) otrzymałem:
Statystyki
-----------------------------
11574 recursive calls
585824 db block gets
586891 consistent gets
631910 physical reads

Dla opcji z domyślnymi wartościami parametrów OPTIMIZER_INDEX, hintami PARALLEL oraz USE_NL (średni czas wykonania operacji – 120 sek.):
Statystyki
------------------------------
11089 recursive calls
585807 db block gets
28610968 consistent gets
580144 physical reads

Logiczna ilość operacji I/O (db block gets + consistent gets) jest więc dużo gorsza dla zapytania, które wykonuje się szybciej!

Mimo wykonania serii testów, modyfikowania odpowiednich parametrów oraz sprawdzenia statystyk wykonywanych zapytań dalej nie mam jednoznacznej odpowiedzi czemu CBO zachował się w tym przypadku w ten sposób.

W dalszym ciągu jest to tylko wierzchołek góry lodowej…

Nie ufaj kosztowi zapytania

Witajcie. Dawno nie pisałem, głównie z braku weny… Dzisiaj jednak postanowiłem nadrobić zaległości. Padły pewne zarzuty, że blog jest mało konkretny/techniczny, więc dzisiejszy post niech będzie małą wprawką 🙂

Prosty przykład. Na razie bez szczegółów.

• Tabele oraz indeksy mają odpowiednio zebrane statystyki.
• Tabela A zawiera 14 184 446 wierszy.
• Tabela B zawiera 2 322 917 wierszy.
• Obszary SGA i PGA zarządzane są przez (nowy w Oracle 11g) parametr MEMORY_TARGET – ustawiony na 8GB
• Tabele nie są spartycjonowane
• W systemie nie działają inne znaczące procesy – jednym słowem cały procesor i cała przepustowość dysku jest do wykorzystania
• Używana jest macierz dyskowa, o bliżej nieznanych mi parametrach

Zapytanie bazowe:

SELECT A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

Warunek złączenia jest zwykłym warunkiem równościowym. Tabela A będzie zawsze czytana cała, więc brany będzie FULL SCAN tabeli. Można więc rozbić ten spory koszt poprzez dodanie hinta PARALLEL.

Zapytanie z hintem PARALLEL:

SELECT /*+ PARALLEL(A,5) */ A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

Mimo, że na tabela_b istnieje odpowiedni indeks, Oracle generuje plan zapytania, w którym go nie uwzględnia:

Jak widać – Oracle chce użyć metody HASH JOIN, i uważa, że ta metoda będzie szybsza. Można jednak wymusić użycie indeksu.

Zapytanie z hintami PARALLEL i USE_NL:

SELECT /*+ PARALLEL(A,5) USE_NL(B)*/ A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

USE_NL- podpowiada optymalizatorowi, że chcemy skorzystać ze złączenia typu NESTED LOOPS

Plan zapytania:

Mimo, że koszt drugiego zapytania jest dużo wyższy, zapytanie wykonywane jest szybciej. Uśrednione wyniki to ok 0.6 sekundy dla wersji z indeksem (i metodą złączenia NESTED LOOPS) oraz ok 10 sekund dla wersji bez indeksu (metoda złączenia HASH JOIN) i jednocześnie niższym kosztem. Wstępna konkluzja powinna być oczywista – optymalizator kosztowy czasem się myli – niższy koszt wcale nie musi oznaczać niższego czasu wykonania danej instrukcji SQL.

 

Ale to tylko wierzchołek góry lodowej….

Podróż w nieznane – CentOS (instalacja)

Witajcie ponownie … już w nowym roku (przedświąteczna i świąteczna zawierucha nie przysłużyła się wolnym czasem i możliwością dodania notki). Mam nadzieję, że imprezę sylwestrową spędziliście tak dobrze jak ja 🙂
Jak zawsze 1 stycznia ułożyłem listę noworocznych postanowień. Oprócz tych zupełnie prywatnych są też te zawodowe. Jednym z nich chciałem się dzisiaj z Wami podzielić.

Krótko.. chodzi o Linuxa..

Niestety w mojej karierze „królował” Windows z czego nie jestem zadowolony. Teraz odczuwam tego bolesne skutki. Nie udało mi się jeszcze znaleźć projektu, w którym Oracle postawiony byłby na OS z Redmond. A co to za oraclowiec, który gubi się w konfiguracji bazy i ogólnym monitoringu systemu? Czary goryczy przelała informacja o wypuszczeniu przez Oracle OEL – strona domowa OEL .
Trzeba było więc coś z tym zrobić.

Wybór padł na CentOS. Dlaczego? Przede wszystkim dlatego, bo jest dystrybucją w 100% zgodną z RHEL (za którego trzeba zapłacić), który jest polecany jako środowisko przyjazne Oracle’owi (gdzieś tak wyczytałem i tak sobie tłumaczę).

Jak się jednak zabrać za instalację w sytuacji zepsutego napędu cd? Z pomocą przychodzi netinstaller, który waży ledwie 10 mb.
ISO pobieramy ze strony: http://mirror.centos.org/centos/5/isos/ . W zależności od architektury wybieramy oczywiście i386 lub x86_64.
Następnie potrzebujemy programiku, który zrobi nam bootowalnego pendrive’a oraz ustawiamy odpowiednie opcje boota w biosie. Ładna instrukcja jest np tu : Bootowalny pendrive z CentOSem
Cała instalacja przebiega wg instrukcji : Instalacja CentOS 5.5
Mała różnica polega na tym, że w pewnym momencie zostaniemy przełączeni od razu na tryb graficzny (na podanej stronie wybrany jest tryb tekstowy).
To co mnie spotkało kilka razy to błąd braku odpowiedniego pliku. Okazało się jednak, że sieć zachowywała się trochę niestabilnie i ostatecznie wszystkie pliki zostały prawidłowo ściągnięte i zainstalowane.

Wszystko łatwo, prosto i przyjemnie… A mówią, że Linux jest trudny 😉
OK, była jedna trudność związana ze złym odczytem przez CentOS adresu MAC karty sieciowej.
Naprawa okazała się jednak banalna. Edycja pliku:

vim /etc/sysconfig/network-scripts/ifcfg-eth0

wartością:

MACADDR=xx:xx:xx:xx:xx:xx (ważne - dla CentOS używa się MACADDR a nie HWADDR)

Restart usług

sudo service network restart


Oczywiście pierwszą rzeczą, którą udało się zrobić to podpięcie do systemu przez tandem Putty+XMing.
Póki co wszystko działa jak należy, a ja już zaczynam rozpoznawać pierwsze przydatne komendy i narzędzia.

Tyle tytułem wstępu do nowego (dla mnie) OS. Kolejne notki będą pokazywać konkretne, ciekawe opcje, i zostaną pewnie okraszone obrazkami, żeby się lepiej czytało:)

Gdy zaczyna iść źle – próby optymalizacji cz.2

Dzisiejszy wpis będzie kontynuacją poprzedniej notki. Jak zaznaczyłem wcześniej – postaram się rozwinąć zaprezentowane punkty tak, aby można było dojść do wniosku, że wymienione rzeczy są dosyć istotne w próbie przywracania bazy danych do życia.

W moim odczuciu najważniejsze jest badanie problemów mając dany odpowiedni kontekst. Dlatego też, należy rozpocząć od analizy na wysokim poziomie abstrakcji i przechodzić coraz niżej.

Najwyższym poziomem abstrakcji – co zaznaczyłem w poprzednim wpisie – są ogólne informacje o bazie danych i jej przeznaczeniu.

Pierwsza „kropka” brzmiała :

• Wersja bazy danych (zakładam Oracle)

Nie ma w tym punkcie zbyt dużej filozofii. Musimy znać dokładną wersję bazy danych, aby mieć możliwość wyboru odpowiedniego „oręża” w walce z problemem. Przykładem z mojego skromnego doświadczenia niech będzie sytuacja, w której nie sprawdziłem wersji bazy (przecież wszystko w firmie stoi na 10g lub 11g…). Okazało się, że w tym przypadku była to 9i, i wszystkie przygotowane przeze mnie skrypty wykorzystujące mechanizm AWR można było wyrzucić do kosza.
W przypadku Oracle, z wersji na wersję uzyskujemy coraz większą automatyzację niektórych procesów. Warto wiedzieć, że np. w wersji 10 udostępniony jest wspomniany wcześniej AWR oraz ADDM, a w wersji 11 np. PL/Scope. Dokładny release bazy może też pomóc przy naprawianiu „znanych bugów”.

Druga kropka z poprzedniego wpisu:

• System operacyjny

Również dosyć oczywista kwestia. Inaczej wygląda środowisko Microsoftowe, inaczej Linuxowe bądź AIXowe. Znajomość systemu operacyjnego pozwoli na sprawdzenie parametrów środowiska.

Kolejny punkt:

• Charakter pracy bazy danych (OLTP, hurtownia)

Na ten temat można napisać książkę.
Wymienione typy systemów ( a istnieją przecież inne ), diametralnie się różnią. W systemach OLTP mamy do czynienia z dużą ilością krótkich transakcji, wykonywanych przez wielu użytkowników. Struktura tabel jest znormalizowana (aby uniknąć składowania tych samych danych w kilku miejscach) – najczęściej do 3 postaci normalnej. Istnieją dobrze określone indeksy – zazwyczaj na kluczach głównych i obcych (B-drzewa). Dane nie są agregowane, nie jest dostępna również historia. Środowisko hurtowni danych jest zupełnym przeciwieństwem – ilość równoległych zapytań jest stosunkowo mała. Są to głównie długie operacje odczytu, które wykonują skomplikowaną logikę. Aby uprościć zrozumienie struktury danych przez użytkowników biznesowych, a także, aby specyficzne typy zapytań pracowały szybciej, przeprowadza się denormalizację schematu. Koronnym przykładem w przypadku hurtowni danych jest struktura gwiazdy lub płatka śniegu. Hurtownia przechowuje dane historyczne, na odpowiednim poziomie agregacji. Zazwyczaj – tabela faktów (składająca się z kluczy obcych do wymiarów i miar) jest dużo większa od tabel wymiarów. Przez to, kluczowym elementem może tu być strategia partycjonowania tabel faktów. W przypadku hurtowni częściej korzysta się z indeksów bitmapowych, lub złączeniowych indeksów bitmapowych zamiast B-drzew.

Widać wyraźnie, jak środowisko, w którym dana baza danych pracuje, wpływa na kontekst oceny odpowiednich parametrów na niższym poziomie abstrakcji.

W poprzednim wpisie wspominam również o :

• Ilość użytkowników pracujących jednocześnie

Ilość użytkowników przywoływana była we wcześniejszym wpisie. Tutaj jednak chodzi mi o zaakcentowanie sposobu obsługi sesji. W Oracle oprócz dedykowanego serwera istnieje również opcja serwera współdzielonego. Ma to znaczenie przy określaniu potrzebnych parametrów instancji (w obszarze SGA oraz PGA), a także określeniu potrzebnego rozmiaru pamięci RAM i CPU.

Następnie:

• Standardowe procesy zaimplementowane w bazie danych

Należy zyskać maksymalną wiedzę dotyczącą obciążenia bazy danych w każdym okresie aktywności tej bazy. Przykładowo – w czasie dnia baza może pracować jako OLTP, jednak w nocy może istnieć potrzeba przeliczania pewnych raportów operacyjnych. Dodatkowo należy wziąć pod uwagę politykę wykonywania backup-ów (częstotliwość i średni czas trwania), a także czynności administracyjnych – przeliczania statystyk, przebudowywania indeksów, czyszczenie przestrzeni tabel itp. Pomysłem może być stworzenie dokumentu, który obrazuje zarys przebiegów czasowych poszczególnych operacji.

Ostatni punkt wysokopoziomowego spojrzenia na bazę danych mówi o przyroście danych, a dokładnie pyta:

• Jaki jest przyrost danych?

Tutaj schodzimy już na trochę niższy poziom, ponieważ pytanie to pozwoli nam określić ilość przestrzeni potrzebną w czasie wykonywania różnych operacji, a także pomoże zbudować wyobrażenie na temat ogólnej ilości danych w systemie za miesiąc, rok …
Ma to znaczenie np. przy wykonywaniu raportów (inaczej może zachować się raport operujący na 100 wierszach, a inaczej na 1 mln) lub też przy operacjach DML (przy dużych przyrostach danych warto spojrzeć na wydajność dysków).

Na dzisiaj to tyle. Kolejne wpisy już niebawem 😉

Gdy zaczyna iść źle – próby optymalizacji cz.1

Dzisiejszy wpis czysto teoretyczny. Oczywiście istnieje pewne nawiązanie do praktycznych sytuacji napotkanych w pracy.

Załóżmy, że pewna baza danych służąca w pewnym projekcie (którego nie znamy) zaczyna pracować nie tak jak powinna. Czasy odpowiedzi są zdecydowanie wydłużone, planowane raporty nie wykonują się w okienku czasowym, procesy integracji danych pomiędzy systemami również zwalniają, no i idzie rozkaz z góry – „trzeba to naprawić na wczoraj”.

Co w takiej sytuacji należy zrobić? Od czego zacząć? Nie znamy przecież kompletnie systemu, logiki tam wykonywanej, czy też parametrów fizycznych serwera aplikacyjnego i bazodanowego.

Stworzyłem sobie taką listę rzeczy, których powinienem się dowiedzieć (lub o które powinienem zapytać kogoś pracującego w danym projekcie). Lista ta została podzielona na odrębne kategorie, i tak:

Ogólne informacje o bazie danych i jej przeznaczeniu

• Wersja bazy danych (zakładam Oracle)
• System operacyjny
• Charakter pracy bazy danych (OLTP, hurtownia)
• Ilość użytkowników pracujących jednocześnie
• Standardowe procesy zaimplementowane w bazie danych (przeładowanie danych źródłowych, wystawiane danych do innych systemów itp.), oraz okienka czasowe, w czasie których są realizowane
• Jaki jest przyrost danych?

Informacje dotyczące konkretnych procesów wykonywanych w bazie danych

• W przypadku problemów wydajnościowych związanych z raportami – kod sql raportu
• Częstość i standardowe czasy działania funkcji oraz procedur PL/SQL
• Czynności wykonywane równocześnie
• Polityka audytu i logowania wykonywanych operacji

Parametry sprzętowe

• Dostępny RAM
• Dostępne procesory
• Dostępne dyski/macierze dyskowe

Parametry „bazodanowe”

• Metryki i statystyki dostępne w Oracle (ADDM, Statspack)
• Wielkość TEMP
• Wielkość UNDO
• Parametry instancji – rozmiar SGA, PGA, poszczególnych procesów (LGWR, DBWR)
• Plan zapytania, optymalizacja kodu
• Indeksy
• Partycjonowanie
• Ilość i wielkość datafile, redo logów itp.
• „Dodatki” np. używanie LogMinera

Powyższa lista to wnioski z mojego skromnego doświadczenia. Byłoby miło gdyby ktoś, kiedyś, mógł się ustosunkować do powyższego i podpowiedzieć, które rzeczy są zbędne, które są ważne, a które ważniejsze?
A może o czymś zapomniałem? Czegoś nie wiem (to to na pewno 🙂 )
Dzięki za wszelkie podpowiedzi i wskazówki.

W kolejnych wpisach postaram się rozwinąć wymienione punkty, i pokazać ich istotność w praktycznych działaniach.

Generowanie danych testowych

Dzisiaj krótki wpis.

Ciekawa rzecz – zdecydowanie przyspiesza tworzenie testowych danych.

Chcemy wygenerować 100 wierszy? Żaden problem:

select rownum from dual
connect by rownum <= 100;


Oczywiście możemy w select wrzucić dowolne, poprawne wyrażenie np:


select rownum, 'tekst ' || rownum from dual
connect by rownum <= 100;