Hajas Csilla Adatbazis-kezeles gyakorlatai

V.Témakör: Lekérdezések optimalizálása

[Az V. és a VI. Témakört párhuzamosan dolgozzuk fel! ]

7.gyak. - Szabály alapú optimalizálás (2010.márc.25)
>> 5.1. Feladatok a kiterjesztett relációs algebrai műveletekkel
>> 5.2. Heurisztikus szabályokon alapuló algebrai optimalizálás

8.gyak. - Költség alapú optimalizálás (2010.ápr.8.)
>> 5.3. Lekérdezések végrehajtása. Műveletek költségbecslése
>> 5.4. Feladatok végrehajtási tervek előállítására: PLAN_TABLE

9.gyak. - Végrehajtási tervek megváltoztatása (2010.ápr.15)
>> 5.5. Végrehajtási tervek előállítása és megváltoztatása hintek segítségével

10.gyak. - Végrehajtási tervek megváltoztatása (2010.ápr.22)
>> 5.6. Végrehajtási tervek megváltoztatása statisztikák létrehozásával és törlésével

11.gyak. - További példák végrehajtási tervekre (2010.ápr.29)
>> 5.7. SQL utasítás rekonstruálása a végrehajtási terv alapján

2.ZH (12.gyak - 2010.máj.6.)

_______________________________________________________
Feladatok és segédanyagok:
> Nikovits Tibor: Információkezelés gyakorlatok feladatainak felhasználásával
> Performance Tuning Guide 13-19.fejezete alapján: ORA_Optim.pdf
> További ajánlott irodalom és hasznos linkek:
Lásd 1.1 Tankönyvek az előadásokhoz
Lásd 1.3 Tankönyvek a gyakorlatokhoz
Lásd 4.1 Oracle Online Documentation

Oracle adatbázis mintasémái	download.oracle.com		ablinux.inf.elte.hu
Samples Schemas	HTML	PDF	HTML	PDF
1.félév: IP-cATP anyagához	download.oracle.com		ablinux.inf.elte.hu
SQL Quick Reference SQL Reference PL/SQL User's Guide and Reference PL/SQL Packages and Types Ref.	HTML HTML HTML HTML	PDF PDF PDF PDF	HTML HTML HTML HTML	PDF PDF PDF PDF
2.félév: IP-cAMÜ anyagához	download.oracle.com		ablinux.inf.elte.hu
Concepts Reference 2 Day DBA Administrator's Guide 2 Day + Performance Tuning Guide Performance Tuning Guide	HTML HTML HTML HTML HTML HTML	PDF PDF PDF PDF PDF PDF	HTML HTML HTML HTML HTML HTML	PDF PDF PDF PDF PDF PDF
Oracle környezethez javasolt doksik	download.oracle.com		ablinux.inf.elte.hu
SQLPlus Quick Reference SQLPlus User's Guide and Reference Oracle SQL Developer User's Guide SQL Developer Installation Guide	HTML HTML HTML HTML	PDF PDF PDF PDF	HTML HTML HTML HTML	PDF PDF PDF PDF

_______________________________________________________
7. gyak. - Szabály alapú optimalizálás

>> 5.1. Feladatok a kiterjesztett relációs algebrai műveletekkel
>> 5.2. Heurisztikus szabályokon alapuló algebrai optimalizálás

5.1. Feladatok a kiterjesztett relációs algebrai műveletekkel

Relációs műveletek multihalmazokon. Kiterjesztett műveletek a relációs algebrában.
Feladatok: Ullman-Widom: Adatbázisrendszerek. Alapvetés. 5.1.-5.2.fejezet
Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása 6.1 fejezet alapján
Segédanyag: >> Lekerdezes.pdf   >> 1UW05_KitRelAlg.pdf

Az alábbi lekérdezéseket 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 kifejezésfát is.

51alg_1
- Adjuk meg osztályonként az osztály nevét és az ott dolgozók számát
a dolgozók száma szerint növekvő sorrendben.

51alg_2
- Adjuk meg azoknak az osztályoknak a nevét, ahol az átlagfizetés nagyobb mint 2000.

51alg_3
- Adjuk meg 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.

5.2. Heurisztikus szabályokon alapuló algebrai optimalizálás

Kifejezésfák felrajzolása. Heurisztikus szabályokon alapuló algebrai optimalizálás.
Feladatok: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása
a "zöld könyv" 7.2 és 7.3 fejezetei alapján
Segédanyag: rAlgOpt.pdf >> 2MU07_LekFord2.pdf

52opt_1
- Hozzunk létre a 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)
- Fejezzük ki SQL SELECT-tel (alkérdések illetve nézetek használata nélkül) és
projekció-szelekció-direktszorzat alakú kiindulási relációs algebrai kifejezéssel,
hogy kik azok a dolgozók (mi a nevük), akik tanárok, a fizetésük a 2-es
kategóriába esik és valamelyik ’Debrecen’ helyszínű osztályon dolgoznak.
- 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?

_______________________________________________________
8. gyak. - Költség alapú optimalizálás

>> 5.3. Lekérdezések végrehajtása. Műveletek költségbecslése
>> 5.4. Bevezetés a költség alapú tervválasztásba. Végrehajtási tervek előállítása

5.3. Lekérdezések végrehajtása. Műveletek költségbecslése

Feladatok - Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása
a "zöld könyv" 6.3-6.7 valamint 7.4-7.8 fejezetei alapján
Join műveletek algoritmusai. Hash alapú (Hash-Join), rendezés alapú (Sort-Merge),
beágyazott ciklus (Nested Loop) algoritmusok és azok I/O költségei.

Segédlet: VegrKolts.pdf

53opt_1
- Tegyük fel, hogy a dolgozó tábla 14 sorból áll és sorai egyenként 1 blokkot
foglalnak el, és a memóriánk 4 blokknyi.
Rendezzük a tábla sorait fizetés szerint egy rendezés alapú 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?

53opt_2
- Tegyük most fel, hogy a memóriánk 6 blokknyi és van még egy vásárlás tábla,
aminek a szerkezete a következő: VASARLAS(dkod, cikk, mennyiseg, ar).
Ennek a táblának a sorai is 1 blokkot foglalnak és a tábla kb. 120 sorból áll.
Mennyi a műveletigénye
- egy hash alapú,
- egy rendezés alapú és
- egy beágyazott ciklusos algoritmusnak,
ami arra 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.

5.4. Bevezetés a kölség alapú tervválasztásba.
Végrehajtási tervek előállítása: PLAN_TABLE

Feladatok - Nikovits Tibor Információkezelés gyakorlatai alapján
Segédanyagok:
>> Oracle Doc. - Performance Tuning Guide HTML PDF és itt 13-19. fejezetek,
lásd 19.fejezet Végrehajtási tervek: 19. Using EXPLAIN PLAN [ex_plan.htm]
>> Lekerdezes.pdf és tervek1.txt

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Előkészítés:
- A gyakorlatokon az oradb adatbázisban az sqldeveloper-rel dolgozunk, ahol
a SELECT-teket az Execute Statement (F9) hajtjuk végre és az eredményt
a Result ablakban látjuk, a végrehajtási terveket az Execute Explain Plan (F6)
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 zh illetve röpzh feladatoknál egy szöveges állományba bemásolva beküldendő:
1.) SELECT /*+ tipp lista */ ... vagyis a lekérdezés (hintekkel együtt),
2.) lekérdezés outputja (ha az eredménytábla nagy, akkor annak az első 5 sora),
3.) végrehajtási tervek fastruktúrájának szöveges megjelenítése (lásd köv.részt)

   - Ehhez, vagyis 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.

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

- 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' INTO plan_table
FOR SELECT ...

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

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,
access_predicates||' -- '||filter_predicates "feltetel (access--filter)"
FROM plan_table
START WITH id = 0 AND statement_id = 'utasitas_egyedi_neve'
CONNECT BY PRIOR id = parent_id AND statement_id = 'utasitas_egyedi_neve'
ORDER SIBLINGS BY position;

   - Figyeljünk oda, hogy a végrehajtási tervek előállításánál a fenti EXPLAIN PLAN parancsban
EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve' INTO plan_table
FOR SELECT ...
az 'utasitas_egyedi_neve'-k helyére minden feladatnál új statement_id-t adjunk meg,
és ugyanezt az egyedi nevet haszáljuk a tervek megjelenítésénél (kétszer is begépelve)
SELECT... FROM plan_table
START WITH ... statement_id = 'utasitas_egyedi_neve'
CONNECT BY PRIOR ... statement_id = 'utasitas_egyedi_neve'...

B.) 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 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 ill. statement_id,
harmadik paraméter pedig a részletesség (basic, typical, all, serial)

set markup html preformat off

C.) vagy egyszerűen csak

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Lekérdezések az Oracle EMP, DEPT, SALGRADE alaptáblái alapján
55plan_1
- Az ORAUSER felhasználó EMP, DEPT és SALGRADE (fizetési kategóriák)
tábláiból hozzatok létre saját példányokat, és ezekre vonatkozóan:
  - Adjuk meg azoknak az osztályoknak a neveit, amelyeknek van olyan dolgozója,
aki az 1-es fizetési kategóriába esik.

55plan_2
- Ezután hozzunk létre indexet valamelyik táblához, majd adjuk meg a rendszer
által létrehozott új végrehajtási tervet. Olyan indexeket hozzunk létre, amit
a lekérdezésben használni tud a rendszer és ez legyen is látható az új tervből.
_______________________________________________________
9. gyak.  - Végrehajtási tervek megváltoztatása

5.5. Végrehajtási tervek előállítása és megváltoztatására hintek segítségével

Feladatok - Nikovits Tibor Információkezelés gyakorlatai alapján
A zh illetve röpzh feladatoknál egy szöveges állományba bemásolva beküldendő:
1.) SELECT /*+ tipp lista */ ... vagyis a lekérdezés (hintekkel együtt),
2.) lekérdezés outputja (ha az eredménytábla nagy, akkor annak az első 5 sora),
3.) végrehajtási tervek fastruktúrájának szöveges megjelenítése (lásd előkészítés)

Segédanyagok:
>> Oracle Doc. - Performance Tuning Guide HTML PDF és itt 13-19. fejezetek,
lásd 16. fejezet: Hintek használata: 16. Using Optimizer Hints [hintsref.htm]
>> Performance Tuning Guide 13-19.fejezete alapján: ORA_Optim.pdf
>> hintek.txt és tervek2.txt

Lekérdezések NIKOVITS.CIKK, SZALLITO, PROJEKT, SZALLIT táblái alapján
NIKOVITS felhasználó tulajdonában vannak a következő táblák:
CIKK(ckod, cnev, szin, suly)
SZALLITO(szkod, sznev, statusz, telephely)
PROJEKT(pkod, pnev, helyszin)
SZALLIT(szkod, ckod, pkod, mennyiseg, datum)
A táblákhoz különböző indexek is vannak létrehozva, ezek tulajdonságait
a katalógusokból (dba_nézetekből) nézhetitek meg, ha szükségetek van rá.

56plan_1
- Adjuk meg a következő lekérdezéseket és a hozzájuk tartozó végrehajtási
tervek fa struktúráját. Minden esetben lehet hinteket használni.
  - 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.

56plan_2
- Adjuk meg azon szállítások összmennyiségét, ahol ckod=1 é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).

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

56plan_4
- Adjuk meg azon szállítások összmennyiségét, ahol ckod=1 vagy 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 kapott sorok unióját
(CONCATENATION).

_______________________________________________________
10. gyak.  - Végrehajtási tervek megváltoztatása

5.6. Végrehajtási tervek megváltoztatása statisztikák létrehozásával és törlésével

Segédanyagok:
>> Oracle Doc. - Performance Tuning Guide HTML PDF és itt 13-19. fejezetek,
lásd 14. fejezet: Statisztikák kezelése: 14. Managing Optimizer Statistics  [stats.htm]
>> Performance Tuning Guide 13-19.fejezete alapján: ORA_Optim.pdf
>> tervek3.txt

57stat_1 (Papíron oldjuk meg!)
- Hozzunk létre 10 intervallumos magasság alapú hisztogramot az alábbi eloszlású
adatokra vonatkozóan, vagyis adjuk meg az egyes intervallumok végpontjait.
     1-100 (2), 101-300 (1), 301-400 (4), 401 (200)
   (Az előfordulások száma zárójelben szerepel, pl. 1-100 (2) azt jelenti, hogy
   1 és 100 között minden érték kétszer fordul elő, vagyis 1 és 100 között 200
előfordulás van, tehát a táblának összesen 1000 sora van).

57stat_2 (Papíron oldjuk meg!)
- Hozzunk létre 10 intervallumos szélesség (gyakoriság) alapú hisztogramot is
ugyanezekre az adatokra.
     1-100 (2), 101-300 (1), 301-400 (4), 401 (200)

57stat_3 (Papíron oldjuk meg! Ez egy korábbi zh feladat volt)
- Mi a különbség a szélesség (gyakoriság) és a magasság alapú hisztogramok között?
   - Hozzon létre 5 intervallumos magasság alapú hisztogramot az alábbi eloszlású
adatokra vonatkozóan, vagyis adja meg az egyes intervallumok végpontjait.
1-100 (4), 101-300 (1), 301-400 (4)   (Az előfordulások száma zárójelben szerepel.)
- Hozzon létre 5 intervallumos szélesség alapú hisztogramot is a fenti adatokra.

57stat_4
- Hozzatok létre egy saját példányt a nikovits.szallit táblából és indexet a datum oszlopra,
majd adjatok meg egy olyan lekérdezést, amelyik egy általatok választott napra vonatkozóan
a szállítások összmennyiségét adja meg. Statisztikák létrehozásával illetve törlésével
(lásd tervek3.txt) érjétek el (hint használata nélkül), hogy az Oracle egyszer használjon
indexet, máskor pedig (hisztogram létrehozása vagy törlése után) ne használjon.

_______________________________________________________
11. gyak.  - További példák végrehajtási tervekre

5.7. SQL utasítás rekonstruálása a végrehajtási terv alapján

Feladatok - Nikovits Tibor Információkezelés gyakorlatai alapján
A zh illetve röpzh feladatoknál egy szöveges állományba bemásolva beküldendő:
1.) SELECT /*+ tipp lista */ ... vagyis a lekérdezés (hintekkel együtt),
2.) lekérdezés outputja (ha az eredménytábla nagy, akkor annak az első 5 sora),
3.) végrehajtási tervek fastruktúrájának szöveges megjelenítése (lásd előkészítés)

Segédanyagok:
>> Oracle Doc. - Performance Tuning Guide HTML PDF és itt 13-19. fejezetek
>> tervek4.txt

Lekérdezések az Oracle demo táblák alapján (lásd AB_plsema10f.html)

58vterv_1
- Adjuk meg azoknak a vevőknek a nevét (SH.CUSTOMERS), akik
nőneműek (cust_gender = 'F') és szinglik (cust_marital_status = 'single'),
vagy 1917 és 1920 között születtek.
   a) Vegyük rá az Oracle-t, hogy a meglévő bitmap indexek alapján érje el a tábla sorait.
   b) Vegyük rá, hogy ne használja ezeket az indexeket.

58vterv_2
- Adjuk meg egy féléves időszakra (illetve egy másik féléves időszakra) vonatkozóan az
    eladások (SH.SALES) összmennyiségét, úgy, hogy az Oracle ne használjon indexet és
   a) pontosan 2 partíciót olvasson a táblából, illetve
b) pontosan 3 partíciót olvasson a táblából.
Lásd partition_start és partition_stop oszlopokat a PLAN_TABLE-ben!

58vterv_3
- Adjunk meg egy olyan lekérdezést az sh tábláira (hintekkel együtt ha szükséges),
aminek az alábbi lesz a végrehajtási terve:

   TERV (OPERATION + OPTIONS + OBJECT_NAME)
--------------------------------------------------------------
SELECT STATEMENT + +
   SORT + ORDER BY +
     TABLE ACCESS + BY INDEX ROWID + CUSTOMERS
     BITMAP CONVERSION + TO ROWIDS +
         BITMAP AND + +
           BITMAP INDEX + SINGLE VALUE + CUSTOMERS_MARITAL_BIX
           BITMAP OR + +
             BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX
             BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX
             BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX

58vterv_4
- Adjunk meg egy olyan lekérdezést az sh tábláira (hintekkel együtt ha szükséges), aminek
az alábbi lesz a végrehajtási terve:

  TERV (OPERATION + OPTIONS + OBJECT_NAME)
-------------------------------------------------------------
  SELECT STATEMENT + +
HASH + GROUP BY +
HASH JOIN + +
      INLIST ITERATOR + +
          TABLE ACCESS + BY INDEX ROWID + CUSTOMERS
            BITMAP CONVERSION + TO ROWIDS +
              BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX
       PARTITION RANGE + ALL +
        TABLE ACCESS + FULL + SALES

58vterv_5
- Adjunk meg egy olyan lekérdezést az sh tábláira (hintekkel együtt ha szükséges), aminek
   az alábbi lesz a végrehajtási terve:

  TERV (OPERATION + OPTIONS + OBJECT_NAME)
------------------------------------------------------------
  SELECT STATEMENT + +
SORT + AGGREGATE +
HASH JOIN + +
      TABLE ACCESS + FULL + PRODUCTS
    HASH JOIN + +
        TABLE ACCESS + BY INDEX ROWID + CUSTOMERS
            BITMAP CONVERSION + TO ROWIDS +
              BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX
          PARTITION RANGE + ALL +
        TABLE ACCESS + FULL + SALES

58vterv_6
   - Adjunk meg egy olyan lekérdezést az sh tábláira (hintekkel együtt ha szükséges), aminek
   az alábbi lesz a végrehajtási terve:

TERV (OPERATION + OPTIONS + OBJECT_NAME)
-------------------------------------------------------------
SELECT STATEMENT + +
SORT + ORDER BY +
    HASH + GROUP BY +
      HASH JOIN + ANTI +
        PARTITION RANGE + SINGLE +
          TABLE ACCESS + BY LOCAL INDEX ROWID + SALES
            BITMAP CONVERSION + TO ROWIDS +
              BITMAP INDEX + SINGLE VALUE + SALES_TIME_BIX
          TABLE ACCESS + FULL + CHANNELS

KÖV.GYAK. II.ZH (12.gyak)