IP-08abctAB2G   Adatbázisok-2 gyakorlat
ELTE, 2014/2015.tanév I.félév      Hajas Csilla gyak.vez.
Hétfő 12:15-13:45 PC3, 14:00-15:30 PC3, Szerda 10:15-11:45 PC9
 backAB2gyak (főmenü)     AB2ea     8.gyak     10.gyak    OracleDoc
     
9.gyak. Bevezetés a költség alapú tervválasztásba II.
Végrehajtási tervek előállítása és megváltoztatása
   
C (gépes feladatok) -- II.ZH gépes részében: C4 és C5 témák!
> C3. Statisztikák kiszámítása és kezelése (ez nem lesz a zh-án)
> C4. Végrehajtási tervek az Oracle demo táblái alapján 
> C5. SQL utasítás rekonstruálása a végrehajtási terv alapján
      
P (papíros feladatok)
> P1. Lekérdezések végrehajtása és I/O költségek -- Tk.2.3. és 6.2.-6.9. 
> P2. Lekérdezések feldolgozása, algebrai optimalizálás -- Tk.7.1.-7.3.
> P3. Költség alapú optimalizálás, output méretének becslése -- Tk.7.4.-7.7.
   
C3. Statisztikák kiszámítása és kezelése
    
Segédanyagok: 
- Oracle 11g Doc. Performance Tuning Guide HTML   PDF  és itt 11-21.fejezetek,
   lásd 13. fejezet: Statisztikák kezelése: 13. Managing Optimizer Statistics 
- Kiss Attila Adatbázisok-2 előadása, lásd 7.előadás 9ituning 77-78.o: Analyze.pdf
- Exec_Plans_Stats.ppt
   
Előkészítésként nézzünk meg két papíron megoldandó feladatot a statisztikákról:
Tankönyv 7.5. szakasz - Statisztikák kiszámítása (Papíron oldjuk meg!)
3.1.feladat:   
 - Hozzunk létre 5 intervallumos magasság alapú hisztogramot az alábbi eloszlású
   adatokra vonatkozóan, vagyis adjuk meg az egyes intervallumok végpontjait.
   1-100 (3), 101-300 (2), 301-500 (1), 501-600 (3)
   (Az előfordulások száma zárójelben szerepel, pl. 1-100 (3) azt jelenti, hogy
   1 és 100 között minden érték háromszor fordul elő, vagyis 1 és 100 között
   300 előfordulás van. Ezek szerint a táblának összesen hány sora is van?) 
 
3.2.feladat:
- Hozzunk létre 5 intervallumos szélesség (gyakoriság) alapú hisztogramot is
  ugyanezekre az adatokra: 1-100 (3), 101-300 (2), 301-500 (1), 501-600 (3)
            
Ezután nézzük meg gépnél az Oracle megoldásait (ez nem szerepel a zárthelyi dolgozatban)
3.3.feladat:
   - Hozzunk létre egy saját példányt a nikovits.szallit táblából és indexet a datum oszlopra,
     majd adjunk meg egy olyan lekérdezést, amelyik egy általunk 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ük el (hintek 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 indexet.
     
C4. Végrehajtási tervek az Oracle demo táblái alapján
   
A zh és beküldendő 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 ("SCIPT OUTPUT"-ból az eredménytábla első 5 sorát), 
   3.) végrehajtási tervek szöveges megjelenítése  ("EXPLAIN PLAN OUTPUT"-ból)
        -- Ehhez az 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);
_______________________________________________________________
Segédanyagok:
- Oracle Database Documentation Library: Sample Schemas HTML   PDF
   HR - Human Resource: Figure 4-1 HR and OE Schemas
   SH -Sales History: Figure 4-3 SH Schema
- Oracle 11g Doc. Performance Tuning Guide 11-21.fejezetek HTML   PDF
   Emlékeztető: Hintekről: 19. Using Optimizer Hints >> hintek.txt (Nikovits T.)
_______________________________________________________________
Feladatok:
- A lekérdezésekhez az Oracle demo tábláit, a HR és SH sémához tartozó táblákat
   használjuk, 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: HR.<táblanév>, SH.<táblanév>
- A táblákhoz különböző indexek is vannak létrehozva, ezek tulajdonságait a
   katalógusokból (dba_ind_columns) nézhetjük meg, amikor szükségünk van rá.
  
4.1.feladat:
   - 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.
 
4.2.feladat:
   - 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!
   
- Lásd még a tervek4.txt és a hint_peldak.txt (Nikovits Tibor) példáit!
   
C5. SQL utasítás rekonstruálása a végrehajtási terv alapján
 
Feladatok: Adjunk meg olyan lekérdezéseket az SH tábláira (és indextábláira)
(hintekkel együtt, ha szükséges), aminek az alábbi lesz a végrehajtási terve:
     
5.1. (a)-(c) feladat 
TERV (OPERATION + OPTIONS + OBJECT_NAME)
----------------------------------------
SELECT STATEMENT + + 
 HASH JOIN + + 
 TABLE ACCESS + FULL + COUNTRIES 
 TABLE ACCESS + FULL + CUSTOMERS


SELECT STATEMENT + + 
 NESTED LOOPS + + 
 TABLE ACCESS + FULL + COUNTRIES 
 TABLE ACCESS + FULL + CUSTOMERS


SELECT STATEMENT + + 
 MERGE JOIN + + 
 TABLE ACCESS + BY INDEX ROWID + COUNTRIES 
 INDEX + FULL SCAN + COUNTRIES_PK 
 SORT + JOIN + 
 TABLE ACCESS + FULL + CUSTOMERS
5.2. (a)-(b) feladat: 
TERV (OPERATION + OPTIONS + OBJECT_NAME)
----------------------------------------
SELECT STATEMENT + + 
 HASH + GROUP BY + 
 HASH JOIN + + 
 TABLE ACCESS + FULL + COUNTRIES 
 TABLE ACCESS + FULL + CUSTOMERS


SELECT STATEMENT + + 
 HASH + GROUP BY + 
 HASH JOIN + + 
 TABLE ACCESS + FULL + COUNTRIES 
 TABLE ACCESS + BY INDEX ROWID + CUSTOMERS 
 BITMAP CONVERSION + TO ROWIDS + 
 BITMAP INDEX + SINGLE VALUE + CUSTOMERS_YOB_BIX
5.3.feladat:
 
   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
 
5.4.feladat:

  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
 
5.5.feladat:
 
  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
 
5.6.feladat:
 
  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
     
Fel a lap tetejére (mai gyak témakörei)      Vissza az AB2gyak kezdőlapjára  
    
P3. Költség alapú optimalizálás, output méretének becslése
 
Segédanyagok: - Nikovits Tibor (Kiss Attila kézirata alapján) Tetel9_kidolgozott.pdf
- Kiss Attila Adatbázisok-2 előadása, lásd 7.előadás QueryProcessingWithSemijoin-hu.ppt
Tankönyv: Molina-Ullman-Widom: Adatbázisrendszerek megvalósítása, Panem, 2001.
7.4-7-7. szakasz - Költség alapú optimalizálás 7_4_outputmeret_becsles.pdf
 
Feladatok: (Papíron oldjuk meg!)
P3.1.feladat: (lásd 7.4.4. Összekapcsolás méretének becslése, 7.25. példa)  
- Vegyük az R(a, b), S(b, c) és U(c, d) relációkat, ahol az T(R)=1000, T(S)=2000,
   T(U)=5000 (ennyi sora van), továbbá V(R,a)=100, V(R,b)=20, V(S,b)=200 és
   V(S,c)=500, V(U,c)=100 és V(U,d)=400 (a reláció attribútumának ennyi
   értéke van, R-nek 1000 sora van és R.a-nak 100 különböző értéke, stb).
   Adjuk meg a legjobb becslés az (R(a, b) join S(b, c) join U(c, d)) méretére!
   
P3.2.feladat: (lásd 7.4.3. Kiválasztás méretének becslése, 7.24. példa)
- Adjuk meg a legjobb becslést szigmaa=1 (R(a, b) join S(b, c) join U(c, d))-ra is!
   
P3.3.feladat: (lásd 7.4.5. Term.összekapcs. több összekapcs.attribútummal, 7.28. példa)   
- Vegyük az R(a, b, c), S(b, c, d) és U(b, e) relációkat, ahol az T(R)=1000, T(S)=2000,
   T(U)=5000 (ennyi sora van), továbbá V(R,a)=100, V(R,b)=20, V(R,c)=200, V(S,b)=50,
   V(S,c)=100, V(S,d)=400, V(U,b)=200 és V(U,e)=500 (a reláció attribútumának
   ennyi értéke van, R-nek 1000 sora van és R.a-nak 100 különböző értéke, stb).
   Adjuk meg a legjobb becslés az (R(a, b, c) join S(b, c, d) join U(b, e)) méretére!
   
Fel a lap tetejére (mai gyak témakörei)      Vissza az AB2gyak kezdőlapjára