Oracle tuning

1. Oracle tuning http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/toc.htm

2. Teljesítményhangolás eszközei:Diagnostic pack

3. ADDM • Automatic Database Diagnostic Monitor: a folyamatos teljesítményanalízis alapján felhívja az adatbázis adminisztrátor, vagy a fejlesztő figyelmét azokra a szűk keresztmetszetekre, ajánlásokat tesz.

4. Tuning pack

5. Az optimalizáló

6. Query Transformer – lekérdezés átalakító • View Merging (nézet összeolvasztás) • Predicate Pushing (feltétel áthelyezés) • Subquery Unnesting (allekérdezés kibontás) • Query Rewrite with Materialized Views (lekérdezés átírás materializált nézetekkel)

7. Estimator – becslő • Selectivity – szelektivitás • Cardinality – számosság • Cost – költség

8. Selectivity – szelektivitás • A szelektivitás egy nullától egyig terjedő intervallumba eső szám. • Mindig egy feltételhez kötődik, és azt reprezentálja, hogy sorok egy halmazából hány felel meg a feltételnek.

9. Cardinality – számosság • A számosság a sorok számát mutatja sorok egy halmazában. • Alap számosság (Base Cardinality) • Effektív számosság (Effective Cardinality) • Összekapcsolási számosság (Join Cardinality) • Egyediségi számosság (Distinct Cardinality) • Csoport számosság (Group Cardinality)

10. Cost – költség • A költség a munka egységét, vagy a felhasznált erőforrásokat reprezentálja. • Az optimalizáló a munka egységének a diszk I/O-t, a CPU felhasználást és a memória felhasználást tekinti.

11. Plan Generator – végrehajtási terv generátor • Kifejezések és feltételek kiértékelése • Utasítás transzformáció • Optimalizálási cél meghatározása • Hozzáférés módja (Access Path) • Összekapcsolási sorrend (Join Order) • Összekapcsolási mód (Join Method)

12. Optimalizálási cél • válaszidőre (response time) • áteresztő képességre (throughput)

13. Optimizer_mode • CHOOSE -deprecated • ALL_ROWS • FIRST_ROWS_n • FIRST_ROWS • RULE -deprecated

14. CHOOSE • Az optimalizáló választ a költség alapú és a szabály alapú megközelítés között attól függően, hogy rendelkezésre állnak-e statisztikák, vagy nem.

15. ALL_ROWS • Az optimalizáló mindenféleképpen költség alapú optimalizálási módot választ függetlenül attól, hogy rendelkezésre állnak-e statisztikák vagy sem. A cél az áteresztő képesség fokozása. Alapértelmezett.

16. FIRST_ROWS_n • Az optimalizáló itt is mindenképpen költség alapú optimalizálást választ. A cél a leggyorsabb válaszidő, az első n sor leggyorsabb megkapása; • n=1, 10, 100, 1000

17. FIRST_ROWS • Költség és heurisztika keverékét használja az optimalizáló ahhoz, hogy az első néhány sort a leggyorsabban tudja visszaadni.

18. RULE • Ennek az értéknek a hatására az optimalizáló mindenképpen szabály alapú optimalizálási módot választ függetlenül attól, hogy rendelkezésre állnak-e statisztikák vagy sem.

19. Végrehajtási terv (Execution Plan)

20. Végrehajtási terv lekérése • SQL Commands/Explain • Vagy: Explain plan for sql_utasítás;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

21. Végrehajtási terv részei • Művelet (operation) • Hozzáférés módja (option) • Művelet eredménysorainak száma (rows) • Eredményhalmaz mérete (bytes) • Művelet költsége (cost) • Feltételek (predicates)

22. Feltételek (predicates) • Hozzáférési feltétel (Access predicate) a művelet végrehajtása közben kerül kiértékelésre, csak a feltételnek megfelelő sorok kerülnek be az eredményhalmazba. • Szűrőfeltétel (Filter predicate) az eredményhalmazra utólagosan kerül kiértékelésre a szűrőfeltétel, amely alapján tovább szűkülhet az eredményhalmaz.

23. Hozzáférési módok (néhány) • Teljes tábla olvasás (Full Table Scan) • Index olvasás (Index Scan) • Sorazonosító olvasás (Rowid Scan) • Táblaminta olvasás (Sample Table Scan)

24. Teljes tábla olvasás (Full Table Scan) • A teljes tábla olvasás azt jelenti, hogy egy adott táblát a végrehajtás során az elejétől a végéig bejárunk, és minden egyes sorra külön-külön eldöntjük, hogy megfelel-e a WHERE utasításrészben szereplő feltételeknek.

25. Optimalizálás full table scan esetén DB_FILE_MULTIBLOCK_READ_COUNT paraméter: a pufferbe előreolvasott blokkok száma a full scan során. Operációs rendszer és puffer cache méret függvénye

26. Sorazonosító olvasás (Rowid Scan) • Ha egyetlen sort keresünk, akkor a sorazonosító olvasás a leggyorsabb mód. • Általában a sorazonosító olvasás a második lépés az index olvasás után, ha az index nem tartalmazza az eredményhalmazban látni kívánt oszlopokat.

27. Index olvasás (Index Scan) • Az index olvasás egy olyan művelet, amely bejárja az indexet az indexelt oszlopok alapján, majd visszaadja azoknak a sorazonosítók a halmazát, amelyek megfeleltek a keresési kritérium(ok)nak.

28. Index olvasás fajtái • Index Unique Scan • Index Range Scan • Index Skip Scan • Full Scan • Fast Full Index Scan • Index Joins • Bitmap Joins

29. Több tábla összekapcsolása • Nested Loop • Hash Join • Sort-merge Join

30. Nested Loop • ha kis méretű adathalmazokat akarunk összekapcsolni és a kapcsolási feltétel segítségével gyorsan el tudjuk érni a második táblát. • A külső tábla minden sorára megnézzük, hogy a belső tábla valamely sora hozzákapcsolható-e.

31. Hash Join • A végrehajtás során egy hash tábla épül fel a kisebbik adathalmaz kapcsolási kulcsa alapján, majd bejárjuk a nagyobbik adathalmazt és a kapcsolási kulcs alapján megtaláljuk az összekapcsolt sorokat. • Akkor érdemes Hash Join-t használni, ha a hash tábla elfér a memóriábanPGA

32. Sort-merge Join • Először rendezzük (sort) mindkét adathalmazt, • majd összeolvasztjuk (merge) a két adathalmazt a kapcsolófeltétel(ek) alapján.

33. Sort-merge Join használataa USE_MERGE ajánlással (hint) • SELECT /*+ USE_MERGE(employees departments) */ * FROM employees, departments WHERE employees.department_id = departments.department_id;

34. Rendezések • Sort Unique • Sort Aggregate • Sort Group by • Sort Join • Sort Order by

35. Sort Unique • Ha DISTINCT kulcsszóval előírjuk az egyediséget, vagy valamilyen művelet számára biztosítani kell az értékek egyediségét. • Pl: select distinct email from employees;

36. Sort Aggregate • Valójában nem igényel rendezést, csupán az összesítő műveleteket jelzi. • PL: select MAX(salary) from employees

37. Sort Group by • A rendezéshez el kell különíteni a sorokat különböző csoportokba. • PL: select COUNT(*),job_id from employees group by job_id

38. Sort Join • Sort-Merge Join összekapcsolási mód esetén használja ezt a műveletet, ha az alap adathalmazt rendezni kell.

39. Sort Order by • Akkor kerül végrehajtásra ez a művelet, ha az eredményhalmaz sorait rendezni kell, és ezt a rendezést nem lehet megoldani indexek felhasználásával. • PL: select * from employees order by hire_date

40. Statisztikák • Tábla statisztikák • Oszlop statisztikák • Index statisztikák • Rendszer statisztikák

41. Tábla statisztikák • Sorok száma (számosság – cardinality) • Blokkok száma • Átlagos sorhosszúság

42. Oszlop statisztikák • Egyedi értékek száma (egyediségi számosság – Number of distinct values (NDV) ) • NULL értékek száma az adott oszlopban • Eloszlás (hisztogram)

43. Index statisztikák • Levélblokkok száma • Szintek száma • Fürtözési tényező (Clustering factor)

44. Rendszer statisztikák • I/O hatékonysága és kihasználása • CPU hatékonysága és kihasználása • A lekérdezés optimalizálónak segítenek az adatok jobb lekérdezési tervek választásában.

45. Automatikus statisztika gyűjtése • Alapesetben engedélyezett a statisztikai adatok gyűjtése új adatbázis létrehozásakor, vagy régi adatbázis frissítésekor. • Leellenőrizhető:SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME='GATHER_STATS_JOB'; • Letiltható:BEGIN DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); END;