Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Recenzija knjige: Troubleshooting Oracle Performance

Wednesday, 24.06.2009 – Dejan

Čuo sam već od nekoliko osoba, da je knjiga “Troubleshooting Oracle Performance” od Christiana Antogninija odlična, pa sam se i sam odlučio uvjeriti u to.

 

Knjiga je zahtjevna i nije namijenjena početnicima, nego naprednijim Oracle stručnjacima, koji žele još više proširiti svoje znanje. U mnogim slučajevima autor opisuje materiju, podrazumijevajući da čitalac vlada dotičnom tematikom i stručnim pojmovima, tako da se ne troši prostor na osnovne stvari, nego se fokus stavlja baš na komplikovane i skrivene (tj. malo poznate) mogućnosti Oracle baze.

Christian već na početku postavlja pitanje – kada treba trošiti vrijeme na poboljšanje performansi? Odgovor je jednostavan – prvenstveno onda kada se korisnici počnu žaliti ili kada se povrijedi neka tačka ugovora (Service Level Agreement). Pri objašnjavanju načina nadgledanja performansi kompletnog sistema, upozorava da mnogi administratori boluju od tzv.  “compulsive tuning disorder“, tj. od pretjeranog provjeravanja statističkih podataka vezanih za performanse baze i pokušavanja konstantnog poboljšanja rada baze, pri čemu se “gubi” mnogo vremena, a to vrijeme je skupo. Nekad je svejedno da li neki SQL upit vraća rezultat za 5 sekundi ili za 50, a pri čemu gazdi nije svejedno da mu DBA potroši dan-dva na optimizaciju tog upita. Dakle, bitan je “feedback” i od korisnika, i od naručioca aplikacije, kako biste mogli ustanoviti kada trebate trošiti vrijeme na poboljšanje performansi.

Nakon uvodnog opisa pristupu problemima i kada ih pravovremeno uočiti, počinju poglavlja sa pretežno tehničkom pozadinom.

U drugom poglavlju možete saznati npr. šta su selektivnost i kardinalnost (moram priznati da su ova dva pojma bolje i detaljnije opisana u knjizi “Cost Based Optimizer” od Jonathana Lewisa). Potom slijedi odličan opis životnog ciklusa jednog cursora, popraćen detaljnim objašnjenjem  parsovanja nekog SQL upita, te prednostima i manama korištenja bind varijabli. Na kraju ovog poglavlja su navedeni tipovi I/O operacija u bazi (logical reads, physical reads, physical writes, direct reads i direct writes).

Treće poglavlje sadrži informacije o ispravnoj identifikaciji problema. Kada vam korisnik kaže:”Jooooj, jest’ ovo generisanje izvještaja traje dugo… daj pogledaj, možeš li imalo ubrzati…“, morate se odlučiti gdje i kako početi sa traženjem uzroka tog usporenja. Pronalaženje pravog uzroka ovog problema može nekad biti dugotrajan i zahtjevan proces, a autor u ovom poglavlju daje savjete, koje metode i alate koristiti u tu svrhu. Tako možete pročitati o instrumentalizaciji i profilisanju neke aplikacije, tj. o dvije metode skupljanja informacija potrebnih za pronalaženje problematične komponente dotične aplikacije. Zatim možete saznati čemu služi “tracing” i kako protumačiti rezultat dobijenih podataka u trace datotekama. Za analizu tih podataka autor je čak napravio vlastiti alat pod nazivom TVD$XTAT, a usput je opisao i korištenje TKProf alata, kao i Profilera (DBMS_PROFILER).

U narednim poglavljima (4. i 5. poglavlje), autor objašnjava kako funkcioniše “query optimizer“, te naglašava veliki značaj redovnog prikupljanja i održavanja sistemskih i objektnih statistika, koje bitno utiču na rad “cost based optimizera“. Možete saznati, koji sistemski parametri se mogu konfigurisati, da bi performanse odgovarale vašim potrebama, npr. podešavanje parametra db_file_multiblock_count – tako u OLTP sistemu ovaj parametar treba biti što manji, dok u Datawarehouse sistemu ovaj parametar treba biti što veći.

Iz 6. poglavlja pod nazivom “Execution plans” sam lično naučio dosta novih stvari od kojih bih izdvojio objašnjenje operacija NESTED LOOPS i CONNECT BY WITH FILTERING, a vama će vjerovatno biti interesantan opis korištenja komande EXPLAIN PLAN i paketa DBMS_XPLAN, koji je popraćen odličnim tumačenjem pravilne interpretacije “execution plana”. Za sve “tjunere” je ovo jako bitno poglavlje, tako da ga pažljivo čitajte.

Na red dolazi poglavlje o tehnikama SQL “tjuniranja”, odnosno poboljšanja performansi nekog SQL upita. Navedene tehnike obuhvataju hintove, podešavanje okruženja u kojem se SQL izvršava, zatim SQL profile, “Stored outlines“, te korištenje SQL Tuning Advisora. Ja lično rijetko kad koristim hintove, a stored outline sam koristio za jednu third-party aplikaciju, čiji source code nisam mogao mijenjati. SQL Tuning Advisor koristim tu i tamo u Grid Controlu, kada poželim vidjeti prijedloge, koje taj “savjetnik” generiše.

Da biste mogli uspješno optimizovati neki SQL upit, morate znati kako neke stvari “interno” funkcionišu. Osmo poglavlje govori o parsovanju SQL upita. Parsovanje SQL upita se može najjednostavnije nazvati internom analizom SQL upita, sa ciljem pronalaska najboljeg execution plana za taj upit, pri čemu se pored ostalog provjeravaju prava pristupa ciljanim objektima, kao i postojanje odgovarajućih sistemskih i objektnih statistika. Prekomjerno parsovanje upita se može spriječiti ispravnim podešavanjem parametara open_cursors i session_cached_cursors (kod mene u bazi sam ovu vrijednost podesio na 200). Takođe bitan uticaj ima i parametar cursor_sharing .

Deveto poglavlje “Optimizing Data Access” govori o odabiru pravilnog “pristupa” podacima. Uspoređeni su Full table scan i korištenje particija u tabeli, sa napomenama šta je kad i kako bolje koristiti. Potom dolazi poređenje B-Tree i Bitmap indeksa, takođe sa primjerima i savjetima, npr. koji indeks se u kojoj situaciji treba koristiti. Jako zanimljivo poglavlje, sa odličnim primjerima particionisanja i korištenja indeksa. Jedina zamjerka u ovom poglavlju je nedovoljno detaljno opisano funkcionisanje “hash clustera“, pošto autor na više mjesta u knjizi spominje kako je “hash cluster” jako performantan u odnosu na neke druge opcije.  Sve u svemu, ako radite sa velikim tabelama, onda ovo poglavlje nudi pregršt korisnih informacija.

“Optimizing Joins“, odnosno optimizacija priduživanja tabela u SQL upitima, prilično detaljno je obuhvaćena u 10. poglavlju. Pored osnovnih vrsta join-a (self-join, equi-join, cross-join, outer join), tu su još opisani “nested loop join“, “merge join“, “hash join“, “partition-wise join” i td. Možete saznati kako pojedine join metode interno funkcionišu i kako možete da utičete na odabir prave join metode. Datawarehouse specijaliste će sigurno zanimati optimizacija “star transformation” upita.

Predzadnje poglavlje pod nazivom “Beyond Data Access and Join Optimization” govori o korištenju slijedećih “tehnika”: “materialized views“, keširanje rezultata SQL upita (“result caching“), paralelno izvršavanje upita, “direct-path insert“, “row prefetching” i “array interface“. Pročitajte šta autor kaže o dotičnim tehnikama u tom poglavlju, a ja ću prokomentarisati svaku od njih na osnovu ličnog iskustva:

– materialized views: koristim ih u velikom broju i zaista doprinose bržem izvršavanju pojedinih SQL upita. Ako vam je parametar query_rewrite_enabled podešen na TRUE, onda možete još više da uočite korisnost, koju oni nude. Dodatak pod nazivom “materialized view log” je šlag na tortu. Međutim, nisu uvijek idealni, npr. ako imate ogromnu tabelu u koju često unosite velike količine podataka (10-20 GB dnevno) i ako želite za tu tabelu kreirati jedan materialized view plus materialized view log, onda ćete se iznenaditi suprotnim učinkom. U tom slučaju unos podataka traje prilično duže, a znalo se desiti da čitav proces jednostavno pukne. Ja sam tu pojavu prijavio Oracle Supportu i rekli su da je bug, zbog čega sam čak neko vrijeme morao deaktivirati parametar query_rewrite_enabled sve dok nije izašao odgovarajući patch za taj bug.

– result caching: pošto ova opcija postoji tek od verzije 11g, nemam s njom nikakvog iskustva, ali čitajući u dokumentaciji i po komentarima drugih, izgleda da keširanje rezultata u određenim slučajevima znatno poboljšava performanse nekog SQL upita

– paralelno izvršavanje upita: eee, koje sam muke imao sa ovim čudom… Nekad su pojedini upiti stvarno bili tako brzi, da se čovjek oduševi, ali previše puta su se znali zablokirati sa wait eventima “PX Deq Credit: send blkd” i “px – waiting on query coordinator” (ili tako nekako, ne znam sad napamet), da sam morao praktično odustati od te metode poboljšavanja performansi. Pisao sam o tome u par navrata(“ORA-00600: internal error code, arguments: [pxTmpAlo_pkey]” i “Oracle: paralelno izvrsavanje upita (Parallel Query Execution) “).

– direct-path insert: uuuuu brale, kako unos podataka ovom metodom LETI!!! Jako korisna metoda za unos velikih količina podataka, pogotovo uz NOLOGGING opciju.Ali avaj… sve ima svoje vrline i mane. Problem je što se na ovaj način zauzima samo novi prostor iznad high watermarka, pa se eventualno slobodni prostor ispod high watermarka ignoriše. Ako brišete puno podataka iz te tabele u koju ih unosite “direct-path insert” metodom, onda ćete biti suočeni sa konstantnim povećanjem prostora, koju ta tabela zauzima. Može da se dogodi kontraefekat pri selektovanju podataka iz te tabele, jer se moraju “pročitati” i prazni blokovi iz kojih su podaci obrisani, a nisu naknadno popunjeni zbog “direct-path insert” metode. Osim toga, ako koristite NOLOGGING opciju, onda neće biti generisani Redo podaci, pa se u slučaju havarije može desiti da izgubite te podatke. Znači oprezno sa ovom metodom!!!! Koristite ju npr. za “batch” prebacivanje ogromne količine podataka iz eksterne tabele u neku permanentnu tabelu u bazi.

– row prefetching: ukoliko SQL upit vraća više redova odjednom, onda će vam opcija biti od pomoći. Ja lično obožavam BULK COLLECT i koristim ju u svakoj situaciji, kada moram u nekoj uskladištenoj proceduri obraditi stotine, hiljade ili čak milione redova. Ograničenje broja selektovanih redova pomoću opcije LIMIT daje vam mogućnost da ispravno koristite fizičke resurse (CPU, I/O operacije i network bandwidth). Extra stvar!!

– array interface: ova opcija je takođe korisna u situacijama kada klijent šalje veliku količinu podataka bazi i obrnuto. Ako imate neki batch job, koji preko sqlplus-a izvršava neku proceduru, podesite arraysize u sqlplusu na veću vrijednost (set arraysize 100), pa usporedite vrijeme izvršavanja prije i nakon toga. Kod mene svaki batch job ima podešen arraysize na minimalno 100, a neki čak i na 500. Osim toga, u PL/SQL procedurama, pored BULK COLLECT obavezno isprobajte FORALL komandu. Piči kao zmaj, zar ne?

Zadnje poglavlje, 12. po redu, pod nazivom “Optimizing the Physical Design“, objašnjava o pravilnom odabiru tipa podataka i rasporedu kolona u tabeli, zatim o nus-pojavama “row migration” i “row chaining“, te o “block contention” konfliktu kada više transakcija pristupa istom bloku. Veoma korisno poglavlje, kako za administratore, tako i za programere.

 

I tako poglavlje po poglavlje dođe kraj knjige. Šta da vam kažem – iako lično imam dosta iskustva sa Oracle bazom, našao sam prilično dosta korisnih i novih informacija. Ne kaže se džabe – čovjek uči dok je živ. Knjiga je zanimljiva i sadrži dosta detalja, dijagrama i ostalih grafičkih prikaza, koje upotpunjuju tekstualna objašnjenja i primjere. Moja srdačna preporuka!

Ocjena: 10/10