Az Oracle SQL 15.

1. Az Oracle SQL 15. Hangolás Markó Tamás, PTE TTK

2. A rádiótelefonokat kérem KIKAPCSOLNI! Markó Tamás, PTE TTK

3. Olvasnivaló • Gyári dokumentáció: • Oracle 9i Database Performance Planning • Oracle 9i Database Performance Tuning Guide and Reference (1. és 6. fejezet) Markó Tamás, PTE TTK

4. A hangolás • Ugyanazt az eredményt sokszor több különböző utasítással is el lehet érni • Egy utasítást sokszor több különböző módon is végre lehet hajtani • A lehetőségek közötti választás nagyban befolyásolja a végrehajtási időt • Az (eredmény szempontjából) ekvivalens utasítások közül való választás a mi feladatunk • A végrehajtási lehetőségek közötti választást az Oracle végzi, de mi is beleszólhatunk Markó Tamás, PTE TTK

5. Az SQL utasítások végrehajtása SQL compiler Markó Tamás, PTE TTK

6. Az egyes részek feladatai • Elemző (parser): • szintaktikai elemzés • szemantikai ellenőrzés (a hivatkozott objektumok megléte) • Optimalizáló: • meghatározza a végrehajtás leghatékonyabb módját • két fajtája van • a költségalapú optimalizálót (CBO, cost based optimizer) javasolja az Oracle • Sorforrás-generátor (row source generator): • a sorforrás adatsorokat állít elő • az utasítás végrehajtási terve több sorforrást tartalmazhat Markó Tamás, PTE TTK

7. Az optimalizáló Markó Tamás, PTE TTK

8. Az optimalizáló munkája • Figyelembe veszi • az utasításban használt objektumok tulajdonságait • az utasításban előírt feltételeket • Az optimalizálás lépései • a konstansokat tartalmazó kifejezések és feltételek minél teljesebb kiértékelése • az utasítás átalakítása más ekvivalens formára (ha kell) • az optimalizálás módjának meghatározása • a táblák elérési módjának meghatározása • a táblák összekapcsolási sorrendjének meghatározása • a táblák összekapcsolási módjának meghatározása Markó Tamás, PTE TTK

9. Az optimalizáló munkájának befolyásolása • Megszabható az optimalizálás módja • az adatbáziskezelő futó példányának (instance) egészére vonatkozik (OPTIMIZER_MODE inicializáló paraméter) • A költségalapú optimalizálásnál megszabható az optimalizálás célja • az aktuális munkamenetre (session) vonatkozik (OPTIMIZER_GOAL paraméter az ALTER SESSION parancshoz) • Útmutatás (hint) adható az egyes utasítások végrehajtási módjához Markó Tamás, PTE TTK

10. Az optimalizálás célja • Alapértelmezett: az utasítás leggyorsabb teljes végrehajtása • a batch üzemmódú alkalmazásoknál (pl. jelentések készítése) ez a logikus választás • A másik lehetőség: a leggyorsabb válasz (az első sorok gyors megjelenítése) • interaktív alkalmazásoknál ésszerű Markó Tamás, PTE TTK

11. Az optimalizálást befolyásoló statisztikai adatok • Az Oracle statisztikákat gyűjt az adatok • változékonyságáról (eloszlásáról) • fizikai tárolásáról • Ezek ismerete nagymértékben javítja a költségalapú optimalizálás hatékonyságát • A lehetőségekre vonatkozóan lásd a DBMS_STATS programcsomagot Markó Tamás, PTE TTK

12. A táblák elérési módjai 1. • Full table scan • a tábla minden sorát feldolgozza • soros elérés, gyors • minden sort csak egyszer dolgoz fel • Sample table scan • a tábla véletlenszerűen kiválasztott sorait dolgozza fel • Rowid scan • a leggyorsabb mód egy sor eléréséhez Markó Tamás, PTE TTK

13. A táblák elérési módjai 2. • Index scan • adatelérés az indexelés alapjául szolgáló mezők értéke szerint • nem is olvassa az eredeti táblát, ha az utasításban csak az indexben lévő mezők fordulnak elő • több altípusa van • Cluster scan • egy clusterben tárolt összes sor elérése • Hash cluster scan • hash clusterben tárolt adatok elérésére Markó Tamás, PTE TTK

14. A táblák összekapcsolási sorrendje • Kettőnél több tábla összekapcsolása esetén érdekes • A rendszer először két táblát kapcsol össze • Ennek eredményét összekapcsolja a harmadikkal • Egyesével kapcsolja hozzá a többi szükséges táblát it Markó Tamás, PTE TTK

15. A táblák összekapcsolási módjai • Nyolc fajta van, különböző körülmények között hatásosak • nested loop joins • nested loop outer joins • hash joins • hash join outer joins • sort merge joins • sort merge outer joins • cartesian joins • full outer joins Markó Tamás, PTE TTK

16. Az utasítás végrehajtási terve Markó Tamás, PTE TTK

17. Az utasítás végrehajtási terve • Execution plan • Az optimalizáló munkájának eredménye • Az egyes utasítások végrehajtási terve lekérdezhető az EXPLAIN PLAN … utasítással Markó Tamás, PTE TTK

18. Az EXPLAIN PLAN utasítás • Egy SQL utasítás végrehajtási terve kérdezhető le vele • A végrehajtási terv adatait egy előre elkészített táblába teszi • A terv elkészülte után a tábla lekérdezhető • Az utasítás végrehajtásához megfelelő jogosultságok szükségesek • A részleteket lásd a gyári dokumentáció SQL Reference Manual című kötetében Markó Tamás, PTE TTK

19. Példa az EXPLAIN PLAN használatára EXPLAIN PLAN FOR SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); nincs olyan beosztás, ahol az illető fizetése az előírt határok közé esne Markó Tamás, PTE TTK

20. Az EXPLAIN PLAN által előállított adatok ID OPERATION OPTIONS OBJECT_NAME --------------------------------------------- 0 SELECT STATEMENT 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY ROWID DEPT 5 INDEX UNIQUE SCAN PK_DEPTNO 6 TABLE ACCESS FULL SALGRADE Markó Tamás, PTE TTK

21. Az adatok értelmezése 1. 1 FILTER ID OPERATION --------------------- 0 SELECT STATEMENT 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS 4 TABLE ACCESS 5 INDEX 6 TABLE ACCESS 2 NESTED LOOPS 6 TABLE ACC. (salgrade, full) 3 TABLE ACC. (emp, full) 4 TABLE ACC. (dept, by rowid) 5 INDEX (pk_deptno, unique scan) minden lépés egy sorforrás fa-szerkezet szintjei Markó Tamás, PTE TTK

22. a legfelső szint a felhasználónak adja az adatokat Az adatok értelmezése 2. 1 FILTER SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 6 TABLE ACC. (salgrade, full) 3 TABLE ACC. (emp, full) 4 TABLE ACC. (dept, by rowid) a szürke lépések az előző lépések adatait dolgozzák fel a kék lépések az adatbázisból veszik az adatokat 5 INDEX (pk_deptno, unique scan) az index rowid-t ad vissza a DEPTNO kulcs, egyedi index van hozzá

23. A végrehajtási sorrend 1. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 Végigolvassa a teljes EMP táblát. A sorokat egyenként átadja a 2-es lépésnek. (Az összes további lépés az EMP tábla minden sorára külön-külön végrehajtódik) 5 INDEX (pk_deptno, unique scan) 2

24. A végrehajtási sorrend 2. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 A 2-es lépéstől kapott DEPTNO-t megkeresi az indexben és a hozzá tartozó ROWID-t átadja a 4-es lépésnek. 5 INDEX (pk_deptno, unique scan) 2

25. A végrehajtási sorrend 3. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 Az 5-ös lépéstől kapott ROWID alapján megtalálja a részleg adatait és DNAME-et átadja a 2-es lépésnek. 5 INDEX (pk_deptno, unique scan) 2

26. A végrehajtási sorrend 4. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 A 3-as és 4-es lépéstől kapott egy-egy sort egybefűzi, ezzel előáll a fenti SELECT egy lehetséges sora. Ezt átadja az 1-es lépésnek. 5 INDEX (pk_deptno, unique scan) 2

27. A végrehajtási sorrend 5. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 Az 1-es lépéstől kapott EMP.SAL használatával végrehajtja a beágyazott lekérdezést, az összes megfelelő sort visszaadja az 1-es lépésnek. 5 INDEX (pk_deptno, unique scan) 2

28. A végrehajtási sorrend 6. 1 FILTER 6 SELECT ename, job, sal, dname FROM scott.emp, scott.dept WHERE emp.deptno=dept.deptno AND NOT EXISTS (SELECT * FROM scott.salgrade WHERE emp.sal NOT BETWEEN hisal AND lowsal); 2 NESTED LOOPS 4 6 TABLE ACC. (salgrade, full) 5 3 TABLE ACC. (emp, full) 1 4 TABLE ACC. (dept, by rowid) 3 Ha a 6-os lépéstől nem kapott adatot, akkor a 2-es lépéstől kapott sort átadja a felhasználónak (egyébként nem). 5 INDEX (pk_deptno, unique scan) 2

29. Szempontok a végrehajtásnál • A végrehajtás a fa leveleinél kezdődik • Ha egy szülő-lépés végrehajtható már a gyerek-lépések egy sorával is, akkor végrehajtódik (és az eredményt továbbadja az ő szülőjének • Soronként végrehajtható tipikus műveletek: • tábla elérése • index elérése • szűrés • Csak az összes sor ismeretében végrehajtható lépések: • rendezés • összesítő függvények kiszámítása felhasználó megkaphatja az első sorokat a teljes utasítás végrehajtása előtt is

30. Az SQL utasítások optimalizálása Markó Tamás, PTE TTK

31. A kritikus utasítások megtalálása adott programban • A statikus SQL utasítások közvetlenül láthatók • A dinamikus (az alkalmazás futási idejében felépített) SQL-ről az SQL_TRACE és a TK_PROF segítségével kaphatunk adatokat Markó Tamás, PTE TTK

32. A kritikus utasítások megtalálása egy teljes alkalmazásban • A Statspack alkalmazás segítségével adatok gyűjthetők a rendszer teljesítményéről • Az összegyűjtött adatok bizonyos nézettáblákon keresztül érhetők el • Legalapvetőbb a V$SQLAREA • utasításonként adja meg a használt erőforrásokat Markó Tamás, PTE TTK

33. Hatékonyságjavító intézkedések 1. • Az optimalizáló által használt statisztikai adatok felülvizsgálata • gyűjtsük őket az összes tábláról • legyenek az adatok frissek • Az utasítások végrehajtási tervének elemzése • néha okosabbak lehetünk a rendszernél • Az SQL utasítások átalakítása • tegyük lehetővé, hogy az Oracle használja az indexeket Markó Tamás, PTE TTK

34. Hatékonyságjavító intézkedések 2. • Minden feladatra külön utasítást írjunk • inkább több egyszerű utasítás, mint egy összetett • Beágyazott SELECT esetében a helyzettől függően válasszunk az IN és az EXISTS között • Adjunk útmutatást (hint) a rendszernek • mi többet tudhatunk az adatbázisról • Óvatosan használjuk a nézettáblákat • nagyon lelassíthatják a lekérdezéseket Markó Tamás, PTE TTK

35. Hatékonyságjavító intézkedések 3. • Tároljuk a közbülső eredményeket • előnyös, ha többször is felhasználjuk őket • Vizsgáljuk felül az indexeket • Vizsgáljuk felül a triggereket és a kényszereket • Vizsgáljuk felül az adatbázis szerkezetét • Őrizzük meg az utasítások végrehajtási tervét • legközelebb már nem kell előállítani • Lehetőleg csak egyszer érjünk el minden adatot Markó Tamás, PTE TTK

36. A rendszer működési filozófiája megérthető és kihasználható! Markó Tamás, PTE TTK