REVIZIJA ODZIVNOSTI PODATKOVNE BAZE ORACLE

1. REVIZIJA ODZIVNOSTI PODATKOVNE BAZE ORACLE Jože Senegačnik, S&T Slovenija, Hermes-Plus, d.d.

2. Vsebina predavanja • Teoretična izhodišča • Teorija vrst • Odzivni čas in njegovi sestavni deli • Odzivni čas v Oracle bazi • Kako izmeriti odzivni čas v Oracle bazi • Kaj vse vpliva na odzivni čas • Revizorjev pogled na odzivnost S&T System Integration & Technology Distribution AG

3. Zakaj je pomembna odzivnost IS? • Podjetje X • Centralizirano vodenje vseh datotek • Niso spremljali odzivnosti sistema • Ob povečani obremenitvi sistema se je sistem praktično zaustavil • Organ Y • Niso redno spremljali delovanja sistema • Dodatni podatki so poslabšali odzivnost sistema tako, da je izpis 1 strani potrdila trajal 15 minut S&T System Integration & Technology Distribution AG

4. Glavni krivci za slabo odzivnost • SQL ukazi z ne-optimalnim izvedbenim načrtom • Lahko spravijo na kolena še tako sposobno strojno opremo • Z optimizacijo zmanjšujemo prekomerno porabo resursov • Učinki optimizacije so očitni in enostavno merljivi (na nivoju modula oz. programa) • Neustrezne nastavitve baze podatkov • So le redko edini krivec za slabo odzivnost • Uspeh uglaševanja je relativno majhen (izboljšanje do 30%, izjemoma lahko tudi več). S&T System Integration & Technology Distribution AG

5. Strežna vrsta Strežnik Odhajajoči uporabniki Prihajajoči uporabniki Samostojno strežno mesto S&T System Integration & Technology Distribution AG

6. Odhajajoči uporabniki Diski CPU Prihajajoči uporabniki Omrežje strežnih mest S&T System Integration & Technology Distribution AG

7. Odzivni čas SQL ukaza • Dve komponenti odzivnega časa: • obdelovalni čas (service time) • čakalni čas (wait time) Rsql = čakalni čas + obdelovalni čas Rsql = pretečeni čas v čakalnih dogodkih + procesorski čas S&T System Integration & Technology Distribution AG

8. Komponente odzivnega časa Čakalni čas Servisni čas S&T System Integration & Technology Distribution AG

9. Spreminjanje odzivnega časa Spreminjanje odzivnega časa 9,000 8,000 7,000 6,000 5,000 Povprečni čakalni čas v čakalni vsrti M/M/2 4,000 3,000 2,000 koleno 1,000 0,000 0,490 0,527 0,564 0,600 0,637 0,674 0,711 0,747 0,784 0,821 0,858 0,894 0,931 0,968 Izkoriščenost S&T System Integration & Technology Distribution AG

10. Odzivni čas modula Rmodula = Rsql1 + Rsql2 + Rsql3 + .... + Rsqln+ ostali čas P1 P2 P1 P2 P3 Pred P1 P2 P1 P2 P3 Po Čas Odzivni čas pred in po optimizaciji S&T System Integration & Technology Distribution AG

11. Dekompozicija odzivnega časa modula Response Time Component Seconds Count% Time SQL*Net message from client8897.21 220528 94.90% CPU service227.35 348309 2.42% db file sequential read169.31 28873 1.81% un-allocated time65.351 0.70% db file parallel read13.723510.15% SQL*Net message to client 1.21 2205280.01% db file scattered read0.99 1570.01% latch free0.62540.01% file open0.03430.00% log file sync0.0230.00% Response time 9375.81s 818847 100.00% S&T System Integration & Technology Distribution AG

12. SQL statement Parser Parsed query Query Transformer Transformed Query Data Statistics Estimator Dictionary Query with estimates System Statistics Plan Generator Execution Plan Row Source Generator Results Procesiranje SQL ukaza S&T System Integration & Technology Distribution AG

13. Selektivnost, Kardinalnost, Strošek • Selektivnost • je kriterij kolikšni del vrstic iz določenega nabora vrstic, ki lahko predstavljajo določeno tabelo ali pa so rezultat predhodne operacije spajanja table ali podobno (grupiranje), ustreza določenemu pogoju. • obsega vrednosti med 0.0 in 1.0 in je funkcija števila različnih vrednosti (1/NDV). • histogrami pomagajo pri korektni oceni selektivnosti v pogojih, ko distribucija podatkov ni normalna (uniformna). • Kardinalnost • je število vrstic v določenem podatkovnem viru, ki je lahko tabela ali pa rezultat predhodne operacije. • ocenjena (izračunana) kardinalnost je zmnožek osnovne kardinalnosti tabele in kombinirane selektivnosti vseh pogojev. • Strošek • je ocenjeno število vhodno/izhodnih operacij (tudi preko omrežja) in porabe procesorskega časa potrebnega za izvedbo ukaza S&T System Integration & Technology Distribution AG

14. Vzroki za ne-optimalen izvedbeni plan • Nepravilno ocenjena selektivnost pogojev zaradi slabih statističnih podatkov ali medsebojnega vpliva vrednosti stolpcev. • Napačna osnovna kardinalnost – razlog neažurna statistika. • Ponavadi se posveča pozornost samo strošku izvajanja, ne pa ocenjeni kardinalnosti. Napačno ocenjena kardinalnost (previsoka ali prenizka) vodi k napačnemu izračunu stroška, ki je zgolj notranji način izbire plana. • Primerjava stroška izvajanja dveh stavkov ni smiselna in zelo verjetno vodi k napačnim zaključkom. • Ceneje ni vedno hitreje! • Zelo uspešna metoda uglaševanja stavkov je tudi prilagoditev statističnih podatkov zaradi medsebojnega vpliva kolon. S&T System Integration & Technology Distribution AG

15. Zmanjševanje čakalnega časa • Zmanjševanje čakalnega časa lahko dosežemo: • s številom klicev • z zmanjševanjem časa trajanja klica • Optimalni izvedbeni plan zmanjšuje prekomerno uporabo virov in s tem znižuje število klicev • Zmanjšana uporaba resursov pomeni manjšo stopnjo izkoriščenosti virov, kar izboljšuje odzivni čas vira (npr. diska) in s tem zmanjšuje skupni odzivni čas. • Vsak čakalni dogodek, ki bistveno doprinaša k čakalni komponenti odzivnega časa, je pomemben. Vedno poskušamo zmanjšati ali odpraviti tisti čakalni dogodek, ki ima največji delež. S&T System Integration & Technology Distribution AG

16. Dejavniki odzivnosti podatkovne baze • Namestitev baze podatkov • fizična namestitev • Vedno več baz je nameščenih na kompleksnejših diskovnih podsistemih • osnovni cilj je enakomerna razporeditev vhodno/izhodnih operacij med vse fizične enote • uspešnost razporeditve razvidna is statističnih obvestil • organizacija podatkov • parametri na posameznih tabelah, ki preprečujejo migracijo vrstic • Nastavitve PCTFREE, list prostih blokov, LMT • Velikost baznih blokov • za OLTP način dela je primernejši manjši blok (4k - 8k) • Za DSS sisteme večji blok (16 - 32k) zaradi dostopanja z branjem celotne tabele S&T System Integration & Technology Distribution AG

17. Dejavniki odzivnosti podatkovne baze • Nastavitve baze • Parametri delovanja • število nastavljivih parametrov se sicer povečuje, vendar ima večina predvsem administrativni namen • Pomembne nastavitve obsega različnih vmesnih in delovnih pomnilnikov (SGA) – podrobnejši pregled sledi • Nekatere parametre bomo pogledali kasneje • Izdelovanje statistike o podatkih • osnova za pripravo izvršilnih načrtov posameznih SQL ukazov, ko delamo s statističnim optimizatorjem (CBO) • Zbiranje statistike lahko povzroči nekontrolirano spremembo izvedbenih načrtov in s tem tudi bistveno poslabšanje odzivnosti sistema. • Oracle10g formalno ne podpira več RBO S&T System Integration & Technology Distribution AG

18. SGA – System Global Area SGA Shared pool Buffer Cache Dictionary Cache Library Cache Shared SQL Area – Cached SQL Statements Log buffer JAVA pool Large pool S&T System Integration & Technology Distribution AG

19. Nastavitve SGA • Nastavljamo samo velikost: • Vmesni pomnilnik (buffer cache) • Preverjanje ustreznosti nastavitve v V$DB_CACHE_ADVICE • Deljeni pomnilnik (shared pool) • večji so problemi s prevelikim deljenim pomnilnikom (zlasti v 8i), kot pa premajhnim (znana napaka ORA-4031) • Priporočljivo označevanje procedur in kurzorjev za permanentno hranjenje v deljenem pomnilniku (dbms_shared_pool.keep) • Preverjanje ustreznosti nastavitve v V$SHARED_POOL_ADVICE • Na notranjo uporabo deljenega pomnilnika neposredno nimamo vpliva – odvisna od trenutnih potreb baze • Large pool • Ko uporabljamo MTS ali RMAN nastavljamo large pool • Java pool • Kadar imamo javanske procedure in pakete shranjene v bazi in se izvajajo na nivoju baze S&T System Integration & Technology Distribution AG

20. Ostale pomembne nastavitve • V Oracle 8i • Velikost delovnih pomnilnikov • SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE • Zlasti prva dva sta pomembna v OLTP sistemih in v primeru, da sta premajhna zahtevata uporabo začasnega prostora. • Nastavitve optimizatorjevih parametrov: • OPTIMIZER_INDEX_COST_ADJ (default 100) – odvisno od sistema • OPTIMIZER_INDEX_CACHING ( default 0) – nastavitev na vrednost “buffer cache hit ratio” • SESSION_CACHED_CURORS (default 0) – nastaviti na število kurzorjev • Poceni mehko razčlenjevanje SQL ukaza S&T System Integration & Technology Distribution AG

21. Ostale pomembne nastavitve • Oracle 9i • PGA_AGGREGATE_TARGET namesto nastavljanja posameznih delovnih pomnilnikov • Posamezen proces lahko pri serijskem procesiranju porabi do 5% tega pomnilnika, pri paralelnem pa do 30% • WORKAREA_SIZE_POLICY = AUTO, sicer se uporabljajo nastavitve delovnih pomnilnikov kot v 8i • OPTIMIZER_INDEX_CACHING > 0 (recimo 90) • Sistemska statistika (dbms_stats.gather_system_stats) • Vključi novi model izračunavanja stroška izvedbe SQL ukaza, ki vključuje tudi strošek procesiranja • Optimizator pripravlja izvedbeni plan glede na dejanske podatke o sistemu (čas za vhodno izhodne operacije, hitrost procesorja,...) S&T System Integration & Technology Distribution AG

22. Nevarnosti – LIO • Preveliko število logičnih vhodno/izhodnih operacij (LIO) • SQL stavek opravi preveliko število LIO • Pokazatelja sta LIO/izvajanje oziroma LIO/vrnjeno vrstico • V Oracle9i na voljo podatke o porabljenem procesorskem času in pretečenem času (v$sqlarea) • Grobo merilo: 5 LIO na vrstico na tabelo • Razlog: ne-optimalen plan izvajanja SQL ukaza • Način odprave: uglaševanje plana izvajanja – vplivanje na pripravo optimalnega plana • NAMIGI (hints) • Prirejena statistika • “Tuning by cardinality feedback” • Pri optimizaciji vedno najprej odpravljamo prevelik LIO, kar posledično zmanjšuje tudi PIO S&T System Integration & Technology Distribution AG

23. Nevarnosti - PIO • Preveliko število fizičnih branj z zunanjega pomnilnika (PIO) • Povzroča preobremenjenost diskovnega podsistema • Razlogi: • najpogosteje ne-optimalen plan izvajanja, ki zahteva preveliko število LIO in posledično tudi PIO – ukrep: uglaševanje plana • branje celotne tabele znotraj zanke • redko premajhen vmesni pomnilnik (buffer cache) – povzroča, da bazni bloki ostajajo premalo časa v vmesnem pomnilniku - povečanje vmesnega pomnilnika • Posledično se pojavljajo problemi z “buffer busy waits” S&T System Integration & Technology Distribution AG

24. Porazdelitev vhodno/izhodnih operacij • Ročno • Izredno zahtevna operacija, zahteva stalni angažma in ni idealna. • Strojno • Danes se največ uporablja RAID 1+0, tudi RAID5 • RAID 5 ni primeren zaradi slabega odzivnega časa pri pisanju • če že, potem le za bralno intenzivne baze podatkov • redolog datoteke, kontrolne datoteke, archive log datoteke se hranijo na zrcaljenih diskih • Pri zunanjem pomnilniku je pomembno število operacij ne pa toliko propustnost • OLTP sistemi – večinoma indeksni pristop – branje 1 bloka • DSS sistemi – paralelno procesiranje, potrebna primerna organizacija podatkov (particioniranje) zaradi porazdelitve vhodno/izhodnih operacij. S&T System Integration & Technology Distribution AG

25. Načini spremljanja delovanja • Z orodji, ki so del Oracle baze • BSTAT/ESTAT • STATSPACK • Enterprise Manager • Druga Orodja za performančno spremljanje • Sodobna orodja se navezujejo neposredno na SGA in vzorčijo preseke stanj internih spremenljivk v deljenem pomnilniku (v$session_wait, v$session, v$sess_io) • Zmanjšan vpliv monitoringa S&T System Integration & Technology Distribution AG

26. STATSPACK • Je del standardne distribucije podatkovne baze. • Ima lasten repozitorij, kamor se shranjujejo statistični podatki. • Poročilo vsebuje osnovne podatke o obremenitvi baze, čakalnih dogodkih, SQL ukazih, ki so porabili največ resursov in porazdelitvi vhodno izhodnih/operacij. • Poročilo obsega preveč nepomembnih podatkov, ne prikazuje pa pomembnih podatkov na ustrezen način. • Interpretacija poročila je lahko zelo vprašljiva brez dodatnih preračunov podatkov. S&T System Integration & Technology Distribution AG

27. Kadar ni možnosti za izboljšanje? • Dva primera iz prakse: • diskovni podsistem je bil stalno preobremenjen – zamenjava z zmogljivejšim oz. razbremenitev. • koso izčrpane vse možnosti za optimizacijo – prenova poslovnega procesa in razvoj ponovni razvoj programske opreme za podporo kritičnim poslovnim procesom. S&T System Integration & Technology Distribution AG

28. Zamenjava diskovnega podsistema S&T System Integration & Technology Distribution AG

29. Pasti v StatspackuKdaj baza deluje bolje? 1 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 96.43 In-memory Sort %: 99.81 Library Hit %: 99.77 Soft Parse %: 97.74 Execute to Parse %: 96.57 Latch Hit %: 99.98 Parse CPU to Parse Elapsd %: 64.34 % Non-Parse CPU: 99.98 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 89.74 79.80 % SQL with executions>1: 93.67 89.54 % Memory for SQL w/exec>1: 93.05 90.11 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.95 Redo NoWait %: 99.99 Buffer Hit %: 93.72 In-memory Sort %: 99.98 Library Hit %: 99.87 Soft Parse %: 97.37 Execute to Parse %: 98.56 Latch Hit %: 99.98 Parse CPU to Parse Elapsd %: 16.10 % Non-Parse CPU: 99.99 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 71.43 72.39 % SQL with executions>1: 87.89 87.80 % Memory for SQL w/exec>1: 90.89 90.37 2 S&T System Integration & Technology Distribution AG

30. Dekompozicija odzivnega časa 2 1 S&T System Integration & Technology Distribution AG

31. Dekompozicija čakalnega časa 2 1 S&T System Integration & Technology Distribution AG

32. Uspeh po prenovi poslovnega procesa S&T System Integration & Technology Distribution AG

33. Usmeritve za revidiranje • Mednarodni standard 1003 • člen 18 predvideva spremljanje učinkovitosti delovanja sistema • Splošne kontrole: • standardizirani razvoj in vzdrževanje programske opreme, • podatkovni model in lastništvo podatkovnih virov, • dostop do baze podatkov, • delitev nalog, • upravljanje s podatkovnimi viri, • varnost podatkov in obnova podatkovne baze S&T System Integration & Technology Distribution AG

34. COBIT – Delivery & SupportManage Performance and Capacity • COBIT 3 – DS3: • Zahteve po razpoložljivosti in odzivnosti sistema • Planirana razpoložljivost • Nadzor in poročanje • Proaktivno upravljanje performans • Predvidevanje obremenitve • Upravljanje kapacitet in razpoložljivosti virov ter razporejanje virov S&T System Integration & Technology Distribution AG

35. Kaj bo revizor spremljal? • Opredelitev odzivnosti in razpoložljivosti v ustreznih dokumentih (planirana odzivnost, dogovorjena odzivnost v SLA) • Presojanje zmogljivosti strojne opreme • Spremljanje odzivnosti sistema in njegove razpoložljivosti • Poročanje o nepravilnostih in njihovo razreševanje • Standardi za definiranje posameznih programskih modulov in potrebnih sprememb in ali se nadzoruje njihova uporaba • Izdelava varnostnih kopij in okrevalni načrti S&T System Integration & Technology Distribution AG

36. Kontrole • Kontrole dostopa na različnih nivojih za preprečevanje nedovoljenega dostopa – tudi do nastavitvenih datotek • Kontrola zbiranja statističnih podatkov o podatkih • Kontrola zbiranja statističnih podatkov o performansah • Kontrole, ki zagotavljajo točnost, popolnost, in konsistentnost posameznih podatkov in njihovih medsebojnih povezav • Kontrola morebitne okvare podatkovnih virov • Kontrola upoštevanja v naprej predpisanih procedur ob izvajanjulogičnih, fizičnih in proceduralnih sprememb • Kontrola podvojenost kritičnih datotek baze podatkov • Kontrola optimalne organizacije podatkov • Analiza vpliva dodatne obremenitve podatkovne baze • Delitev nalog S&T System Integration & Technology Distribution AG

37. Pomembne ugotovitve za odzivnost • Ali so zlasti za kritične poslovne procese zahteve glede odzivnosti in razpoložljivosti jasno določene in poznane vsem ključnim uporabnikom? • Ali so zahteve glede odzivnosti vključene pri razvoju novih rešitev? • Ali je zagotovljeno obravnavanje problema odzivnosti v vseh fazah nabave in uporabe? • Ali je tehnološka infrastruktura redno podvržena pregledom zaradi ugotavljanja mogočih izboljšav, ki bi zagotovile optimalno odzivnost ob minimalnih stroških? S&T System Integration & Technology Distribution AG

38. Vprašanja ? S&T System Integration & Technology Distribution AG