Adatbazisok_II

8.gyak. Bevezetés a költség alapú tervválasztásba I.
Feladatok végrehajtási tervek előállítására (PLAN_TABLE)

C (gépes feladatok)
> Előkészítés: Részletek a végrehajtási tervek szöveges megjelenítéséről
> C1. Lekérdezés optimalizálás: Feladatok végrehajtási tervek előállítására
-- folyt.köv. C2.Végrehajtási tervek megváltoztatása hintek segítségével, stb.

P (papíros feladatok)
> P1. Lekérdezések végrehajtása (I/O költségek) Tk.2.3. és 6.2.-6.9.
> P2. Lekérdezések feldolgozása, szabály alapú algebrai optimalizálás Tk.7.1.-7.3.
-- folyt.köv. P3. Költség alapú optimalizálás, output méretének becslése Tk.7.4.-7.7.

A félév során a gyakorlatok Kiss Attila AB2EA előadás anyagára épülnek,
kérem, hogy az előadások anyagból felkészülten jöjjön minden gyakorlatra!
A legjobb felkészülés: ha bejár az előadásokra és átnézi az előadás slide-okat!
További hasznos olvasmányok:
> Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, 2001.
> Oracle Dokumentáció >> Concepts, Reference, Performance Tuning Guide

Előkészítés: Részletek a végrehajtási tervek szöveges megjelenítéséről:

- A gyakorlatokon az ablinux és tomx Oracle adatbázisokban az sqldeveloper-el
  dolgozunk, ahol a SELECT-teket az Run Statement (Ctrl+Enter) hajtjuk végre és
  az eredményt a Result ablakban látjuk, a végrehajtási terveket az Explain Plan (F10)
  segítségével készítjük el, annak eredményét pedig az Explain ablakban találjuk.

- Mivel a Result és az Explain ablakok eredményét nem tudjuk zh-ban beküldeni,
  ezért a zh-n Run Script (F5)-tel futtassuk a SELECT utasítást és az eredményt
  a Script Output-ból másoljuk át a beadandó egyszerű szöveges .txt állományba.
- A végrehajtási tervek beküldéséhez a végrehajtási tervek fastruktúráját is szöveges
formában kell megjelenítenünk, amit át tudunk másolni a beküldendő szövegfájlba.

- Ez a szakasz egy rövid történeti áttekintés a végrehajtási tervek fastruktúrájának
szöveges megjelenítéséről. Átugorható, folyt.itt: C1. Feladatok végrehajtási tervekre

Hozzuk létre a PLAN_TABLE nevű táblát az utlxplan.sql scriptnek a futtatásával!

A1.) Végrehajtási tervek előállítása és kiíratása hierarchikus kérdés segítségével

-- Történetileg érdemes áttekinteni a végrehajtási tervek előállításának különböző módjait.
-- Két (2x2) módszert próbálunk ki (az sqldeveloperből a Run Script használatával) illetve
-- itt ennél az első A.) módszernél az sqlplus kliens programból is próbáljuk ki, amely
-- a panda.inf.elte.hu (pandora, panda, valerie) gépeken fut. Használata: AB_eleres.html

-- 1.lépés: Készítsük el az utasítások végrehajtási tervét a a PLAN_TABLE táblába.
-- Figyeljünk oda, hogy minden lekérdezéshez statement_id='utasitas_egyedi_neve'
-- 'utasitas_egyedi_neve'helyett minden feladatnál új egyedi nevet adjunk az utasításnak,
-- például 'ut1', 'ut2', stb... és arra is figyeljünk, hogy csak egyszer futtassuk a parancsot!

EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve' -- itt írjuk át ut1
INTO plan_table
FOR SELECT ... -- ide írjuk be a SELECT vagy a DML utasítást

-- 2.lépés: Ezután vegyük is ki a terveket a táblából a megfelelő lekérdezéssel.
   -- Emlékeztetőül a hierarchikus szerkezetű adatok lekérdezése SELECT
-- utasítás segítségével: a hierarchia gyökerét (gyökereit) a START WITH-el,
-- a szülő és gyermek sorok közti kapcsolatot a CONNECT BY-al kell megadni,
   -- Figyeljünk oda, hogy a végrehajtási tervek előállításánál amit a fenti EXPLAIN PLAN
-- parancsban használtunk az utasításoknak egyedi nevet (például 'ut1', 'ut2', stb...),
-- ugyanezt a nevet használjuk itt a tervek megjelenítésénél is (és kétszer is be kell írni)!

SET PAGESIZE 40
SET LINESIZE 132
COLUMN terv FORMAT A50
COLUMN feltetel FORMAT A80 TRUNCATED
COLUMN "feltetel (access--filter)" FORMAT A80 TRUNCATED

SELECT LPAD(' ', 2*(level-1))||operation||' + '||options||' + '||object_name terv
FROM plan_table
START WITH id = 0 AND statement_id = 'utasitas_egyedi_neve' -- egyedi neve szerepel
CONNECT BY PRIOR id = parent_id AND statement_id = 'utasitas_egyedi_neve' -- meg itt
ORDER SIBLINGS BY position;

A2.) valamint a PLAN_TABLE további hasznos oszlopai is kiírathatók

-- 1.lépés: Készítsük el az utasítások végrehajtási tervét a a PLAN_TABLE táblába.

EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve' -- itt írjuk át ut1
INTO plan_table
FOR SELECT ... -- ide írjuk be a SELECT vagy a DML utasítást

-- 2.lépés: Ezután vegyük is ki a terveket a táblából a megfelelő lekérdezéssel.
-- A PLAN_TABLEtábla további hasznos oszlopai is kiírathatók az alábbi lekérdezéssel,
-- ahol még az első eredményoszlop (terv) hosszát is korlátozzuk 50 karakterre, hogy
-- könyebben lehessen szöveges fájlba menteni.

SELECT SUBSTR(LPAD(' ', 2*(level-1))||operation||' + '||options||' + '||object_name, 1, 50) terv,
time, cost, io_cost -- cardinality, bytes, cpu_cost
FROM plan_table
START WITH id = 0 AND statement_id = 'utasitas_egyedi_neve' -- egyedi neve szerepel
CONNECT BY PRIOR id = parent_id AND statement_id = 'utasitas_egyedi_neve' -- meg itt
ORDER SIBLINGS BY position;

B1.) Végrehajtási tervek előállítása és kiíratása dbms_xplan package segítségével

-- 1.lépés: Készítsük el az utasítások végrehajtási tervét a PLAN_TABLE táblába:

EXPLAIN PLAN [SET statement_id='utasitas_egyedi_neve'] [INTO sajat_plan_table]
FOR SELECT ...

-- 2.lépés: A végrehajtási tervek megjelenítése a dbms_xplan package segítségével

set markup html preformat on

SELECT plan_table_output
FROM table(dbms_xplan.display('saját_plan_table','utasitas_egyedi_neve','all'));
-- paraméterek: első két paraméter lehet null vagy tábla_név vagy statement_id,
-- harmadik paraméter pedig a részletesség (basic, typical, all, serial)

set markup html preformat off

B2.) vagy egyszerűen csak (Ez a két utasítás a dolgozatlapon is szerepel majd emlékeztetőként)

-- 1.lépés: Készítsük el az utasítások végrehajtási tervét a PLAN_TABLE táblába:

EXPLAIN PLAN FOR SELECT ...

-- 2.lépés: A végrehajtási tervek megjelenítése a dbms_xplan package segítségével:

SELECT plan_table_output FROM table(dbms_xplan.display);

Fel a lap tetejére (mai gyak témakörei)

Vissza az AB2gyak kezdőlapjára

C1. Gépes feladatok végrehajtási tervek előállítására

1.feladat: Lekérdezések és végrehajtási tervek (index nélkül illetve
indexek létrehozásával) az EMP, DEPT, SALGRADE táblák alapján

Három lépésben végezzük el a végrehajtási tervek előállítására a feladatokat:
1.lépcső: Az ORAUSER felhasználó EMP,DEPT és SALGRADE (fizetési kategóriák)
tábláiból hozzunk létre saját példányokat, lásd create_dept_no_constr.txt  - fontos,
hogy először kulcs és integritási megszorítások nélkül hozzuk létre a saját tábláinkat!
Legelőször így megszorítások és indexek nélkül fogjuk használni a saját tábláinkat, és
minél változatosabb és érdekesebb lekérdezéseket próbáljunk ki a táblákon, miközben
nézzük meg a végrehajtási terveket (és mentsük is le egyszerű .txt szöveges állományba).
2.lépcső: Most megszorításokkal együtt hozzuk létre a táblákat, lásd create_dept_const.txt
és az előző lekérdezéseket újból futtatva nézzük meg mi változott a végrehajtási terveken.
3.lépcső: Ezután hozzunk létre olyan (különböző típusú) indexeket a táblákhoz, amelyeket
a fenti lekérdezésekben használni tud a rendszer, majd adjuk meg a fenti lekérdezésekhez
   a rendszer által létrehozott új végrehajtási terveket (most már az indexek használatával).
Figyeljük meg, hogy a különböző megszorítások és indexek hozzáadásával milyen
változások történnek a korábbi végrehajtási terveken? (ezeket a terveket is mentsük le).

Feladatok - lekérdezések:
- A saját EMP,DEPT,SALGRADE táblákra vonatkozóan adjunk meg lekérdezéseket
és a hozzájuk tartozó végrehajtási tervek fa struktúráját. Adjunk meg szűrőfeltételeket,
   különböző összekapcsolásokat (például külső joint is), majd adjunk meg bonyolultabb
lekérdezéseket, vagyis nézzük meg az alkérdéseket (NOT IN és NOT EXISTS is),
a korrelált alkérdéseket, csoportosítást, group by .. having .. záradékokat, halmaz- és
multihalmaz műveleteket, továbbá az inline nézeteket tartalmazó lekérdezéseket, stb,
és állítsuk elő (mentsük le szöveges fájlba) a hozzájuk tartozó végrehajtási terveket.

Példák a feladatokra:
(a.) Adjuk meg a dolgozók adatait és azt is, hogy milyen elnevezésű osztályon dolgoznak,
   vagyis az EMP és a DEPT táblák különböző összekapcsolásaira (left/right outer-joinra is)
   nézzük meg a régi illetve az új szintaxissal megadott lekérdezésekre a végrehajtási terveket.
A joint fejezzük ki IN (alkérdéssel) illetve korrelált EXISTS (alkérdéssel) is: semi-join.
(b.) Adjuk meg azoknak az osztályoknak a nevét és telephelyét (LOC), amelyeknek van
olyan dolgozója, aki hivatalnok (UPPER(JOB)=CLERK) és akinek a fizetése (SAL)
az 1-es fizetési kategóriába esik (vagyis GRADE=1 sorban megadott LOSAL és HISAL
értékek közé esik a fizetés: theta-join) és már legalább 20 éve munkában áll (HIREDATE).
(c.) Adjuk meg azoknak az osztályoknak az azonosítóját, nevét és az osztályon dolgozók
   átlagfizetését, amely osztályoknak nincs olyan dolgozója, aki a 3-as fizetési kategóriába
esik. Tehát itt a nincs olyan dolgozókra korrelált NOT EXISTS (alkérdés) lesz: anti-join.

- Ezután hozzunk létre olyan indexeket a táblákhoz, amelyeket a fenti lekérdezésekben
használni tud a rendszer, majd adjuk meg a fenti lekérdezésekhez a rendszer által
létrehozott új végrehajtási terveket (most már az indexek használatával).
A különböző megszorítások és indexek hozzáadásával milyen változások történnek
a korábbi végrehajtási terveken? (mentsük is el a végrehajtási terveket szöveges fájlba).

A II.ZH lekérdezések optimalizálása, végrehajtási tervek feladatoknál beküldendő:
1.) SELECT /*+ tipp lista */ ... vagyis a lekérdezés (hintekkel együtt),
2.) lekérdezés outputja (scriptként futtatva "SCRIPT OUTPUT"-ból bemásolva,
ha az eredménytábla nagy, akkor annak csak az első 5-6 sorát másolja be),
3.) végrehajtási tervek szöveges megjelenítése:
-- Ehhez 1.lépés: Készítsük el az utasítások végrehajtási tervét:
EXPLAIN PLAN FOR SELECT ...
-- 2.lépés: A végrehajtási tervek megjelenítése a dbms_xplan package segítségével:
SELECT plan_table_output FROM table(dbms_xplan.display);
(scriptként futtatva a "SCRIPT OUTPUT"-ból bemásolhatjuk a végrehajtási terveket)

Segédanyagok:
- Oracle 11g Doc. Performance Tuning Guide HTML PDF és itt 11-21.fejezetek,
lásd 11. fejezet Lekérdezések optimalizálása 11. The Query Optimizer
lásd 12. fejezet Végrehajtási tervek: 12. Using EXPLAIN PLAN
- Nikovits Tibor: expl.txt és tervek1.txt
- Kiss Attila Adatbázisok-2 előadása, lásd 7.előadás 9ituning-hu.ppt (Oracle 9i) anyagban
ezeket az Oracle 9i példákat futtassuk le Oracle 11g-ben! Változik-e a végrehajtási terv?
9ituning 6-8.o: Áttekintés.pdf, 9ituning 27-40.o: Indexes.pdf, 9ituning 48-55.o: Joins.pdf

Fel a lap tetejére (mai gyak témakörei)

Vissza az AB2gyak kezdőlapjára

P1. Lekérdezések végrehajtása (I/O költségek)

Segédanyagok: Nikovits Tibor összefoglalója: VegrKolts.pdf
- Kiss Attila Adatbázisok-2 előadása, lásd 5.előadás optimization-hu.ppt
- (illetve a korábbi félévek cAMÜ EA-ból: LekVegreh1.pdf, LekVegreh2.pdf )
Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, 2001.
- 2.3. A másodlagos tárolók hatékony használata, a számítás I/O modellje
- 6.3. Adatbázis-műveletek egymenetes algoritmusai egymenetes_algoritmusok.pdf
- 6.4. Beágyazott ciklusú összekapcsolások nested_loop_algoritmus.pdf
- 6.5. Rendezésen alapuló kétmenetes algoritmusok sort_join_algoritmus.pdf
- 6.6 Tördelésen alapuló kétmenetes algoritmusok hash_join_algoritmus.pdf
- 6.7 Index alapú algoritmusok index_alapu_algoritmus.pdf
- 6.9. Többmenetes algoritmusok tobbmenetes_algoritmus.pdf
Adjuk meg, hogy az egyes algoritmusok hogyan működnek és
adjuk meg a műveletek költségbecslését (I/O blokkok számát).

Tk.2.3. fejezete: Adatok rendezése másodlagos tárolókon
  - Tegyük fel, hogy a dolgozó tábla 140 sorból áll és 10 sora fér el 1 blokkban,
és a memóriánk 4 blokknyi. Rendezzük a tábla sorait fizetés szerint a
kétfázisú többutas összefésülő rendezéssel (sort-merge algoritmussal).
Adjuk meg az első menet után a rendezett részlistákat (elég a dnev, fizetes).
Hány menetes algoritmusra lesz szükségünk? optimization-pp17-22.pdf

Tk.6.fejezet: Lekérdezések végrehajtása, műveletek I/O költsége
  - Tegyük fel, hogy a dolgozó tábla 140 sorból áll és 10 sora fér el 1 blokkban,
és a memóriánk most 6 blokknyi és van még egy vásárlás tábla,  amelynek
a szerkezete a következő: VASARLAS(dkod, cikk, mennyiseg, ar).
Ennek a táblának is 10 sora fér el 1 blokkban, és a tábla kb.1200 sorból áll.
Mennyi a műveletigénye
- egy beágyazott ciklusú algoritmusnak nested_loop_algoritmus.pdf
- egy rendezés alapú algoritmusnak sort_join_algoritmus.pdf
- egy hash alapú algoritmusnak hash_join_algoritmus.pdf
amely egyenlőséges összekapcsolást (equijoin) végez a két táblán, arra a
lekérdezésre válaszol, hogy az egyes dolgozók összesen mennyit költöttek?
Feltehetjük, hogy az összegeket gyűjtő számlálók még beférnek a memóriába
a blokkok mellett. Írjuk le röviden, hogy az egyes algoritmusok hogyan fognak
működni. Adjuk meg a kosarakat a hasítás alapú algoritmus első menete után.

P2. Lekérdezések feldolgozása, algebrai optimalizálás

Segédanyagok: Nikovits Tibor összefoglalója: Lekerd, AlgOpt.pdf
- Kiss Attila Adatbázisok-2 előadása, lásd 4.előadás algebrai_opt.ppt (91-126.o.)
Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, 2001.

Tk.7.2-7.3: Logikai lekérdezéstervek javítása (algebrai szabályok)
-- A heurisztikus szabályokon alapuló algebrai optimalizálás menete:
Különböző lekérdezéseket (papíron) fejezzük ki SQL SELECT-tel,
majd írjuk át a SELECT utasítást kiterjesztett relációs algebrai kifejezéssé,
majd rajzoljuk fel a relációs algebrai kifejezést reprezentáló lekérdezőfát,
majd algebrai szabályok segítségével alakítsuk át a logikai lekérdezéstervet
hatékonyabb relációs algebrai kifejezést reprezentáló lekérdezőfává!
Milyen relációs algebrai azonosságokat használtunk fel az egyes lépéseknél?

Fel a lap tetejére (mai gyak témakörei)

Vissza az AB2 gyak kezdőlapjára