Az Oracle SQL 15.

2006. december 2. Markó Tamás, PTE TTK 1

Az Oracle SQL 15.

Hangolás


A rádiótelefonokat kérem

KIKAPCSOLNI!

2006. november 26. 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)


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


Az SQL utasítások végrehajtásaS

QL

com

pile

r


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


Az optimalizáló


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


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


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ű


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


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


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


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


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


Az utasítás végrehajtási terve


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


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


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árokközé esne


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


Az adatok értelmezése 1.ID OPERATION

---------------------

0 SELECT STATEMENT

1 FILTER

2 NESTED LOOPS

3 TABLE ACCESS

4 TABLE ACCESS

5 INDEX

6 TABLE ACCESS

1FILTER

2NESTED LOOPS

6TABLE ACC.(salgrade, full)

3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX

(pk_deptno, unique scan)fa-szerkezetszintjei

minden lépésegy sorforrás

2006. december 2. 22

Az adatok értelmezése 2.1

FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX

(pk_deptno, unique scan)




AND NOT EXISTS



hisal AND lowsal);

a szürke lépések azelőző lépések adatait

dolgozzák fel

a kék lépések azadatbázisból veszik

az adatokat

a DEPTNO kulcs,egyedi index van hozzá

az index rowid-tad vissza

a legfelső szinta felhasználónakadja az adatokat


A végrehajtási sorrend 1.1

FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

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)



FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

A 2-es lépéstől kapott DEPTNO-tmegkeresi az indexben és a hozzá tartozó

ROWID-t átadja a 4-es lépésnek.



FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

Az 5-ös lépéstől kapott ROWID alapjánmegtalálja a részleg adatait és

DNAME-et átadja a 2-es lépésnek.



FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

A 3-as és 4-es lépéstől kapott egy-egy sortegybefűzi, ezzel előáll a fenti SELECT

egy lehetséges sora.Ezt átadja az 1-es lépésnek.



FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

Az 1-es lépéstől kapott EMP.SAL használatával végrehajtja a beágyazottlekérdezést, az összes megfelelő sort

visszaadja az 1-es lépésnek.



FILTER

2NESTED LOOPS


3TABLE ACC.

(emp, full)

4TABLE ACC.

(dept, by rowid)

5INDEX





AND NOT EXISTS



hisal AND lowsal);

1

2

3

4 5

6

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).

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 azelső sorokat a teljes utasítás

végrehajtása előtt is


Az SQL utasítások optimalizálása


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


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


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



• 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



• 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


A rendszer működési filozófiája megérthető és kihasználható!

Az Oracle SQL 15.

Documents

Transcript of Az Oracle SQL 15.