8.gyak.
Bevezetés
a költség alapú
tervválasztásba I.
Gépes feladatok végrehajtási
tervek
előállítására Végrehajtási
tervek megváltoztatása hintek
segítségével
Követelmények
(újra) ismertetése: Gyakorlati
követelmények és
ZH
infó Az I.ZH eredménye
és a tapasztalatok megbeszélése, ZH-ba
való betekintés
a mai gyakorlat második
felében mindenkinek csak rövid
egyéni konzultáció
formájában lesz, akinek hosszabb
konzultációra van szüksége,
keressen meg
fogadóóráimban
vagy konzultációs időpontokban: Elérhetőségem,
órarendem
A mai gyakorlat első felében
megnézzük hogyan lehet
előállítani és kimenteni
szöveges formában a
végrehajtási terveket, hogyan lehet indexek
létrehozásával
illetve hintekkel befolyásolni a
végrehajtási terveket. A gyakorlat
második felében
(az egyéni konzultáció
idejében) erre
szerezzenek minél több
önálló
tapasztalatot!
--- "Ne halat adj az éhezőnek, inkább
tanítsd meg halászni!"
- A gyakorlatokon a 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.
-- 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éseSELECT
-- 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);
C1.
Gépes feladatok
végrehajtási
tervek
előállítására
A
II.ZH
lekérdezések
optimalizálása, végrehajtási
tervek
feladatoknál
beküldendő:
1.) SELECT
/*+ tipp lista */ ... FROM ... [WHERE ...] [...];
Be kell küldeni a
lekérdezést
(a hintekkel együtt, lásd később C2.
feladatoknál),
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 (ha hiányzik ZH-n
-> pontlevonás!)
-- 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)
Előadásanyagból:
- Kiss
Attila Adatbázisok-2 EA,
lásd 7.előadás 9ituning.ppt -> innen az
Oracle 9i példákat
futtassuk le
a tomx-en Oracle 11gR2-ben, figyeljük meg, hogyan
változik a
végrehajtási terv!
Feladatok:
Lekérdezések
és
végrehajtási tervek az EMP,
DEPT,
SALGRADE táblák alapján a.) megszorítások és index
nélkül,
b.) megszorításokkal
(például elsődleges kulcs) és index
nélkül illetve
c.) indexek
létrehozásával
Három lépésben
végezzük el a végrehajtási
tervek előállítására a
feladatokat:
a.) 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).
b.) 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.
c.) 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:
1.1. 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.
1.2. 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).
1.3.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). C2.
Végrehajtási
tervek megváltoztatása hintek
segítségével
- A lekérdezésekhez NIKOVITS
felhasználó tulajdonában levő
táblákat használjuk,
vagyis most ne hozzunk létre
saját táblákat, hanem NIKOVITS
séma tábláit és
indextábláit
használjuk (azt, hogy
milyen indextáblák vannak az
adatszótárnézetekből
a megfelelő
katalógustáblákból
keressük meg, lásd az I.ZH anyagának
gépes
részét).
- Az adatbázis sémája:
CIKK(ckod,
cnev, szin, suly)
SZALLITO(szkod,
sznev, statusz, telephely)
PROJEKT(pkod,
pnev, helyszin)
SZALLIT(szkod,
ckod, pkod, mennyiseg, datum)
- 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>
előtaggal történik.
- 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
következő
lekérdezéseket és a
hozzá tartozó
végrehajtási tervet
(mentsük le
szövegfájlba).
Minden esetben
lehet hinteket használni. Lásd hintek.txt
(Nikovits
T.összefoglalója)
- Adjuk meg a piros cikkekre vonatkozó
szállitások
összmennyiségét.
a) Adjuk meg úgy a
lekérdezést, hogy egyik
táblára se
használjon indexet az oracle.
b) Adjuk meg úgy a
lekérdezést, hogy csak az egyik
táblára
használjon indexet az oracle.
c) Adjuk meg úgy a
lekérdezést, hogy mindkét
táblára
használjon indexet az oracle.
d) Adjuk meg úgy a
lekérdezést, hogy a két
táblát
SORT-MERGE
módszerrel kapcsolja össze.
e) Adjuk meg úgy a
lekérdezést, hogy a két
táblát
NESTED-LOOPS
módszerrel kapcsolja össze.
f) Adjuk meg úgy a
lekérdezést, hogy a két
táblát
NESTED-LOOPS
módszerrel kapcsolja össze,
és ne
használjon indexet.
2.2.feladat:
- Adjuk meg a Pecs-i telephelyű
szállítók által
szállított piros cikkek
összmennyiségét.
a) Adjuk meg úgy a
lekérdezést, hogy a
szallit táblát először a cikk
táblával join-olja az oracle.
b) Adjuk meg úgy a
lekérdezést, hogy a
szallit táblát először a szallito
táblával join-olja az oracle.
2.3.feladat:
- Adjuk meg azon
szállítások
összmennyiségét, ahol ckod=2
és szkod=2.
a) Adjuk meg úgy a
lekérdezést, hogy ne használjon
indexet.
b) A
végrehajtási tervben két
indexet használjon, és képezze a
sorazonosítók metszetét
(AND-EQUAL).
c) A végrehajtási
tervben két indexet
használjon, és képezze a kapott sorok
unióját
(CONCATENATION).
- Akinek nem sikerült a mai gyakorlaton megoldani ezeket a feladatokat, kérem, hogy
nézzék meg a következő gyakorlatig
és akkor fogjuk megbeszélni a tapasztalatokat!
I.ZH
9.gyak
OracleDoc
Fel a
lap
tetejére (mai gyak
témakörei)