Adatbazisok_II

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

P (papíros feladatok)
> P1. Lekérdezések végrehajtása (I/O költségek) Tk.6.2.-6.9.
> P2. Lekérdezések feldolgozása, algebrai otimalizálás Tk.7.1.-7.3.
-- folyt.köv. P3. Költség alapú optimalizálás, statisztikák Tk.7.4.-7.7.
C (gépes feladatok)
> C1. Gépes feladatok végrehajtási tervek előállítására
> További részletek a végrehajtási tervek szöveges megjelenítéséről
-- folyt.köv. C2.Végrehajtási tervek megváltoztatása hintek segítségével

A gyakorlati foglalkozások az előadások anyagára épülnek
6.EA: A Q(A,B) JOIN R(B,C) JOIN S(C,D) háromféle kiszámítási módja
és költsége: NT_Tetel9_kidolgozott.pdf (hf.: a köv.gyakorlatra ezt átnézni!)
Az Oracle költségalapú és szabályalapú optimalizálása, lekérdezésterveinek
megjelenítése és értelmezése: 9ituning.ppt (1-47.o.)

-- előadáshoz kapcsolódó további olvasmány:
Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, Panem, 2001.
7.4. Műveletek költségének becslése
7.5. Bevezetés a költség alapú tervválasztásba
7.6. Összekapcsolások sorrendjének megválasztása

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

-- I.ZH megbeszélése (ez a P1.témakör az 5.gyak E5.témakörének ismétlése)
Segédanyagok:
lásd előadás: Műveleti költségek becslése: optimization.pdf (I.ZH-ban csak 1-32 oldalig)
+ Nikovits Tibor rövid összefoglalója: VegrKolts.pdf
(és 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, Panem, 2001.
"6.fejezet Lekérdezések végrehajtása" kidolgozott példái
- 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 egy
külső rendezés alapú (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? sort-merge_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.

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

Vissza az AB2gyak kezdőlapjára

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

Segédanyagok:
lásd előadás 9ituning-hu.ppt, pp6-8.(áttekintés), algebrai opt.ppt (91-126.o.)
Nikovits Tibor összefoglalója: Lekerd, AlgOpt.pdf

7.1. szakasz (Adatbázisok-1 ismétlés) - Kiterjesztett relációs algebra
7.3. szakasz - Logikai lekérdezéstervek javítása (Papíron oldjuk meg!)

- Hozzunk létre az ORAUSER.DEPT, EMP, SALGRADE táblákból a saját táblákat.
   Relációsémák:
   OSZTALY(OAZON, ONEV, HELY)
   DOLGOZO(DKOD, DNEV, FIZETES, FOGLALK, BELEPES, OAZON)
   FIZKAT(KATEGORIA, ALSO, FELSO)

Az alábbi lekérdezéseket (papíron) fejezzük ki SQL SELECT-tel, majd írjuk át
a lekérdezést kiterjesztett relációs algebrai operátorokat felhasználó kifejezéssé,
majd rajzoljuk fel a relációs algebrai kifejezést reprezentáló lekérdezőfát, majd
alakítsuk át hatékonyabb relációs algebrai kifejezést reprezentáló lekérdezőfává,
vagyis végezzük el a heurisztikus szabályokon alapuló algebrai optimalizálást!
(Milyen relációs algebrai azonosságokat használtunk fel az egyes lépéseknél?)
- Adjuk meg, hogy kik azok a dolgozók (a nevükkel), akik tanárok, a fizetésük
a 2-es kategóriába esik és valamelyik ’Debrecen’ helyszínű osztályon dolgoznak.
- Adjuk meg rendezve azoknak a foglalkozásoknak a nevét, amelyek a 10-es és
20-as osztályon is előfordulnak. Ismétlődések ne legyenek a végeredményben.
- Adjuk meg osztályonként az osztály nevét és az ott dolgozók számát.
- Adjuk meg azoknak az osztályoknak a nevét, ahol az átlagfizetés nagyobb mint 2000.

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

Segédanyagok:
>Kiss Attila Adatbázisok-2 előadása, lásd lásd előadás 9ituning-hu.ppt,
> 9ituning 6-8.o: áttekintés, 9ituning 17-18.o: ExplainPlan.pdf,
> 9ituning 27-40.o: Indexes, 9ituning 48-55.o: Joins
>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, tervek1.txt és tervek2.txt
_______________________________________________________________
Végrehajtási tervek előállítása:
A gyakorlatokon az tomx adatbázisban az sqldeveloper3-ban 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 pedig az Explain Plan (F10)
segítségével készítjük el, annak eredményét pedig az Explain ablakban találjuk.
_______________________________________________________________
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)
_______________________________________________________________
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
Adatbázisok-2 előadás  9ituning-hu.ppt példáit Oracle 11g-ben kipróbálni.

- 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_emp.txt  - fontos,
hogy kulcs és integritási megszorítások nélkül hozzuk létre a saját tábláinkat!
Először megszorítások és indexek nélkül fogjuk használni a saját tábláinkat,
majd csak később kell hozzá megadni megszorításokat, lásd cr_dept_emp.sql
és amikor megnéztük, hogy ezáltal mi változott a végrehajtási terveken, azután
hozzunk létre olyan indexeket, amely gyorsítja a lekérdezéseinket (és ekkor is
figyeljük majd meg, hogyan változik a végrehajtási terv).

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

- Lekérdezésre példa: 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é) és már legalább 20 éve munkában áll (HIREDATE).

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

- Lásd még a tervek1.txt példáit -- egyszerűbb és bonyolultabb lekérdezések
végrehajtási tervei, valamint különböző megszorítások és indexek hozzáadásával
milyen változások történnek ezeken a végrehajtási terveken?
_______________________________________________________________
2.feladat: Lekérdezések és végrehajtási tervek előállítása (folyt.köv.)
NIKOVITS.CIKK, SZALLITO, PROJEKT, SZALLIT táblái alapján

- A lekérdezésekhez NIKOVITS felhasználó tulajdonában levő táblákat használjuk:
CIKK(ckod, cnev, szin, suly)
SZALLITO(szkod, sznev, statusz, telephely)
PROJEKT(pkod, pnev, helyszin)
SZALLIT(szkod, ckod, pkod, mennyiseg, datum)

- Vagyis most ne hozzunk létre saját példányokat, hanem egy adott séma tábláit
használjuk a lekérdezéseknél, a táblák elérése: NIKOVITS.<táblanév>
- A táblákhoz különböző indexek is vannak létrehozva, ezek tulajdonságait
a katalógusokból (dba_indexes) nézhetjük meg, amikor szükségünk van rá.

- NIKOVITS felhasználó tulajdonában levő táblákra adjuk meg a lekérdezést
és a hozzá tartozó végrehajtási terv fa struktúráját (mentsük is le szövegfájlba).
- Adjuk meg a piros cikkekre vonatkozó szállitások összmennyiségét.
  Ezt a feladatot a következő héten folytatjuk a hintek hozzáadásával!

- Lásd még a tervek2.txt példáit, más felhasználó tábláit és indextábláit használjuk
az egyszerűbb és bonyolultabb lekérdezések végrahajtási tervének előállításánál.

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

Vissza az AB2gyak kezdőlapjára

További részletek a végrehajtási tervek fastruktúrájának szöveges megjelenítéséről:

Előkészítés:
- A gyakorlatokon az tomx adatbázisban az sqldeveloper3-ban 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 pedig 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 fastruktúrájának szöveges megjelenítéséhez:

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: lásd AB-elérés

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