II. Témakör: Optimalizálás

_______________________________________________________
4. gyak. (X.04)  - Optimalizálás: Adattárolással kapcsolatos példák

- Az adatbázis fizikai szerkezete (AB, táblatér, fájl, extents, blokk/lap)
- Adatszótárnézetek (rendszerkatalógusok)
DBA_TABLES, DBA_DATA_FILES, DBA_TABLESPACES,
DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE

Segédanyagok:
- dba1_Tablak.doc  (Nikovits Tibor összefoglalója, jelszóval) 
Oracle Database 10g Documentation Library:
- Oracle Database Concepts 10g (pp732)
- Oracle Database Administrator's Guide 10g (pp1078)

Feladatok az egyszerűsített egyetemi tantárgyfelvétel példában
4tab_01
    - lásd Az órai adatbázissémák 2. példáját (E/K modell, rel.sémák)
    - Hozzuk létre a tanar, hallg, targy, felv táblákat:
      CREATE TABLE,INSERT utasítások: 01_sql.txt (jelszóval)
    - Vizsgáljuk meg a táblák szerkezetét, integritási megszorításokat!
    - Egészítsük ki a CREATE TABLE vagy ALTER TABLE
      utasításokat az adattárolással kapcsolatos paraméterekkel
      lásd dba1_Tablak.doc  vagy SQL reference on-line docu
      (CREATE TABLE)

Feladatok az adatszótárnézetek lekérdezésére
Adattárolással kapcsolatos fogalmak
4tab_02
   - Adjuk meg az adatbázishoz tartozó adatfile-ok nevét és méretét
      méret szerint csökkenő sorrendben
      (DBA_DATA_FILES,ORDER BY)

4tab_03
   - Adjuk meg, hogy milyen táblaterek vannak létrehozva az adatbázisban,
      az egyes táblaterek hány adatfájlból állnak, és mekkora az összméretük.
      (DBA_DATA_FILES,COUNT,SUM, csoportképzés táblaterenként)

4tab_04
    - Mekkora a blokkok mérete a USERS táblatéren?
  (DBA_TABLESPACES)

4tab_05
    - Melyik a legnagyobb méretű tábla szegmens az adatbázisban és
       hány extensből áll? (A partícionált táblakat most ne vegyük figyelembe)
       (DBA_SEGMENTS, vagy a MAX-ot alkérdésben keressük meg
        vagy lerendezhetjük az eredménytáblát csökkenőleg  ROWNUM<2)

4tab_06
   - Melyik a legnagyobb méretű index szegmens az adatbázisban és
      hány blokkból áll? (A partícionált indexeket most ne vegyük figyelembe)
      (DBA_SEGMENTS, SEGMENT_TYPE='INDEX')

4tab_07
   - Melyik a legnagyobb méretű LOB szegmens az adatbázisban és
      mekkora az első extensének mérete? (INITIAL_EXTENTS)
      (DBA_SEGMENTS, SEGMENT_TYPE='LOBSEGMENT')

4tab_08
   - Melyik a legnagyobb méretű tábla partíció az adatbázisban és
      mekkora lesz a következő extensének a mérete? (Ha NULL
      érték a válasz, akkor a táblatér a default-ot örökli az obj.)     
      (DBA_SEGMENTS, 'TABLE PARTITION')

4tab_09
   - Adjuk meg adatfájlonként, hogy az egyes adatfájlokban
     mennyi a foglalt hely összesen, írassuk ki a fájlok méretét is.
     (lépésenként DBA_EXTENTS nagy katalógustáblát nézzük,
      kapcsoljuk a DBA_DATA_FILES táblával, csoportosítás)

4tab_10
   - Melyik felhasználó objektumai foglalnak összesen a legtöbb helyet
      az adatbázisban?
     (Miért gyorsabb a DBA_SEGMENTS, mint a DBA_EXTENDS?)

4tab_11
   - Melyik táblatéren van az ORAUSER felhasználó dolgozo táblája?

4tab_12
   - Folytassuk a 4a óra eleji példát táblatér létrehozásával!
      lásd dba1_Tablak.doc  vagy SQL reference on-line docu
      (CREATE TABLESPACE)


Feladatok a táblák oszlopaival
DBA_TAB_COLUMNS

4col_01
   - Adjuk meg azoknak a tábláknak a tulajdonosát és nevét, amelyeknek
      van 'Z' betűvel kezdődő oszlopa.
      (DBA_TAB_COLUMNS, LIKE 'Z%')

4col_02
   - Adjuk meg azoknak a tábláknak a nevét, amelyeknek legalább 8 darab
     dátum típusú oszlopa van.
     (GROUP BY owner, table_name, HAVING COUNT(*)>=8)

4col_03
   - Adjuk meg azoknak a tábláknak a nevét, amelyeknek 1. es 4. oszlopa is
     VARCHAR2 típusú.

_____________________________________________________
5. gyak. (X.11)  - Optimalizálás: Indexek, partícionálás

- Indexek. B, B+, B* fa szerkezetű indexek.
- Bittérkép (bitmap) indexek.
- Partícionált táblák indexelése.
- folyt. Adatszótárnézetek (rendszerkatalógusok)
USER_INDEXES vagy DBA_INDEXES,
DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS,
DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS

Segédanyagok:
- dba2_Indexek.doc (Nikovits Tibor összefoglalója, jelszóval)
Oracle Database 10g Documentation Library:
- Oracle Database Concepts 10g (pp732)
- Oracle Database Administrator's Guide 10g (pp1078)


Házi feladat (beadandó +pontért) ROWID adattípusra
Határidő: október 17 szerda 23h

ROWID adattípus formátuma és jelentése

18 karakteren íródik ki, a következő formában:
OOOOOOFFFBBBBBBRRR, ahol
OOOOOO -  az objektum azonosítója
FFF    -  fájl azonosítója (táblatéren belüli relatív sorszám)
BBBBBB -  blokk azonosító (a fájlon belüli sorszám)
RRR    -  sor azonosító (a blokkon belüli sorszám)

A ROWID megjelenítéskor 64-es alapú kódolásban jelenik meg.
Az egyes számoknak (0-63) a következő karakterek felelnek meg:
A-Z -> (0-25), a-z -> (26-51), 0-9 -> (52-61), '+' -> (62), '/' -> (63)

Pl. 'AAAAAB' -> 000001

5rid_01
  - Írjunk egy olyan PL/SQL függvényt, amely a ROWID részeit értelmezi,
    vagyis amely a fenti 64-es kódolásnak megfelelő számot adja vissza.
    A függvény paramétere egy karakterlánc, eredménye pedig a kódolt
    numerikus érték legyen. (Elég ha a függvény max 6 hosszú, helyesen
    kódolt karakterláncokra működik, hosszabb karakterláncra vagy
    rosszul kódolt paraméterre adjon vissza -1-et.)
  - Ennek a fv-nek a segítségével adjuk meg egy táblabeli sor pontos
     fizikai elhelyezkedését. (Melyik fájl, melyik blokk, melyik sora)
     Példaul az  emp tábla azon sorára, ahol a dolgozó neve 'KING'.
  - Nagyon értékelem, aki az Oracle dokumentaciókban otthonosan mozog,
    de most gyakorlásként írjuk meg ezt a PL/SQL függvényt es alkalmazzuk.
  - A megírt függvényünk működése ellenőrizhető az alábbi függvényekkel
     DBMS_ROWID.rowid_object(),        DBMS_ROWID.rowid_relative_fno()
     DBMS_ROWID.rowid_block_number(),  DBMS_ROWID.rowid_row_number()
     lásd Oracle Database Online Documentation Library 10g könyvtárban
           Administration felsőmenü alatt az "SQL and PL/SQL" részben
           PL/SQL Packages and Types Reference HTML    PDF
           könyvnek a "80 DBMS_ROWID" fejezete

  - Kérem, hogy a megoldást egy szövegfájlba másoljuk be, amelynek a neve
    5rid_EHAKOD.txt legyen, ahol az EHAKOD helyere írja be a saját ETR
    azonosítóját (.ELTE nélkül). Ezt a szövegfájlt ELTE IP című gépről, például
    a pandoráról anonymous FTP-vel töltse fel az ablinux.inf.elte.hu gépre a
    sila alkönyvtárba! (egyszer küldhető be, az nem írható felül, nem olvasható!)

További feladatok az adatszótárnézetek lekérdezésére
USER_INDEXES vagy DBA_INDEXES,
DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS,
DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS

Feladatok indexekre (INDEX)
5ind_01   
   - Folytassuk a múlt heti 4tab_01 illetve 4tab_12 feladatokat,
      hozzunk létre a táblákhoz különböző indexeket,
      az egyik legalább két oszlopot is tartalmazzon.
 
   - Például a hallg tábla létrehozásakor HAZON PRIMARY KEY
      Készítsünk indexeket (HAZON,SZAK) és  (SZAK,HAZON) is,
      és BITMAP INDEX-et a SZAK-ra. Készítsünk  egy olyan
      nézettáblát a hallgatóhoz, ahol a Beíratkozási_dátumuk alapján
      soroljuk évfolyamokba a hallgatókat. Erre is BITMAP INDEX.
      lásd dba2_Indexek.doc vagy Oracle Concepts  HTML  PDF
     (CREATE INDEX, CREATE BITMAP INDEX)

5ind_02
   - Állapítsuk meg, hogy egy táblához milyen indexek vannak létrehozva.
      (index neve, indexbeli oszlopok neve és sorrendje, index fizikai
       tárolásának paraméterei -> INITIAL, NEXT, PCT...)

5ind_03
   - Adjuk meg azoknak a tábláknak a nevét, amelyeknek van
      csökkenő sorrendben indexelt oszlopa.

5ind_04
   - Adjuk meg azoknak az indexeknek a nevét, amelyek legalább
      10 oszloposak.

5ind_05
   - Adjuk meg, hogy hány bitmap index van az adatbázisban.

5ind_06
   - Adjuk meg a kétoszlopos függvény alapú indexek nevét.
   - Adjuk meg az egyikükre, hogy milyen kifejezések szerint vannak
      indexelve a soraik (vagyis mi az a függvény, ami alapján a
      bejegyzések készülnek.)

5ind_07
   - Adjuk meg az adatbázisban egy IOT tábla nevet, a hozzá tartozó
      túlcsordulási szegmens nevet, valamint mindkettő objektum
      azonosítóját és adat-objektum azonosítóját (ha van).

5ind_08
    - Írjunk meg egy PL/SQL procedúrát, amelyik a paraméterül kapott
      index szervezett tábláról kiírja a tábla méretét.
     PROCEDURE iot_meret(p_owner VARCHAR2, p_tabla VARCHAR2)
     Vigyázzunk, mert a táblának lehet index és túlcsordulási szegmense is.

5ind_09
   - Adjuk meg azokat az index szervezett táblákat, amelyeknek pontosan
     egy dátum típusú oszlopa van.

5ind_10
   - Adjuk meg egy partícionált tábla logikai és fizikai részeit. Maga a tábla
      most is logikai objektum, a partíciói vannak fizikailag tárolva.

5ind_11
   - Melyik a legnagyobb méretű partícionált tábla az adatbázisban
      (a partíciók összméretét tekintve)?

5ind_12
   - Melyik a legnagyobb méretű partícionált index azok közül, amelyeknek
      legalább 12 partíciója van?

_______________________________________________________
6. gyak. (X.18)  - folyt. Klaszterek. Adatbázis objektumok.

Segédanyagok:
- dba3_Clusterek.doc (Nikovits Tibor összefoglalója, jelszóval)
Oracle Database 10g Documentation Library:
- Oracle Database Concepts 10g (pp732)
- Oracle Database Administrator's Guide 10g (pp1078)


Feladatok klaszterekre (CLUSTER)
DBA_CLUSTERS, DBA_CLU_COLUMNS, DBA_TABLES,
DBA_CLUSTER_HASH_EXPRESSIONS

6cls_01
   - Hozzunk létre egy DOLGOZO(dazon, nev, beosztas, fonoke, fizetes, oazon)
     és egy OSZTALY(oazon, nev, telephely) nevű táblát.
    A két táblának az osztály azonosítója (oazon) lesz a közös oszlopa. A két táblát
    egy CLUSTEREN hozzuk létre. (Előbb persze létre kell hozni a clustert is.)

6cls_02
   - Adjunk meg egy olyan clustert az adatbázisban (ha van ilyen), amelyen még
     nincs egy tábla sem.

6cls_03
   - Adjunk meg egy olyant, amelyiken legalább 6 darab tábla van.

6cls_04
   - Adjunk meg egy olyan clustert, amelynek a cluster kulcsa 3 oszlopból áll.
     (Vigyázat!!! Több tábla is lehet rajta)

6cls_05
   - Hány olyan hash cluster van az adatbázisban, amely nem az oracle
     alapértelmezés szerinti hash függvényén alapul?

6cls_06
   - Hozzunk létre egy hash clustert es rajta két táblát, majd szúrjunk be a
     táblákba sorokat úgy, hogy a két táblának 2-2 sora ugyanabba a blokkba
     kerüljön. Ellenőrizzük is egy lekérdezéssel, hogy a 4 sor valóban
     ugyanabban a blokkban van-e. (A ROWID lekérdezésével)
     (A sorok elhelyezését befolyásolni tudjuk a HASH IS megadásával.)

Feladatok adatbázis objektumokkal
DBA_OBJECTS

6obj_01
   - Kinek a tulajdonában van a DBA_TABLES nevű nézet (ill a DUAL nevű tábla)?

6obj_02
   - Kinek a tulajdonában van a DBA_TABLES nevű szinonima (ill a DUAL nevű)?
     (Az iménti két lekérdezés megmagyarázza, hogy miért tudjuk elérni őket.)

6obj_03
   - Milyen típusú objektumai vannak az orauser nevű felhasználónak az adatbázisban?

6obj_04
   - Hány különböző típusú objektum van nyilvántartva az adatbázisban?

6obj_05
   - Melyek ezek a típusok?

6obj_06
   - Kik azok a felhasználók, akiknek több mint 10 féle objektumuk van?

6obj_07
   - Kik azok a felhasználók, akiknek van triggere és nézete is? (INTERSECT)

6obj_08
   - Kik azok a felhasználók, akiknek van nézete, de nincs triggere? (MINUS)

6obj_09
   - Kik azok a felhasználók, akiknek több mint 40 táblájuk, de
      maximum 37 indexük van?

6obj_10
   - Melyek azok az objektum típusok, amelyek tényleges tárolást igényelnek,
      vagyis tartoznak hozzájuk adatblokkok? (A többinek csak a definíciója
      tárolódik adatszótárban)

_______________________________________________________
7. gyak. (X.25)  - Lekérdezés-optimalizálás, végrehajtási tervek + hint

Segédanyagok:
- Kiss Attila előadásaihoz kapcsolódó anyag: az előadó honlapján
- Oracle Database Online Documentation Library 10g könyvtárban  
       Administration felsőmenü alatt az "Performance" részben
       Performance Tuning Guide   HTML   PDF
       Part IV. Optimazing SQL Statements

Feladatok a lekérdezések optimalizálására

Algebrai optimalizálás (heurisztikán alapuló optimalizálás)
- lásd Az órai adatbázissémák 2. példáját (E/K modell, rel.sémák)
- Hozzuk létre a tanar, hallg, targy, felv táblákat:
  CREATE TABLE,INSERT utasítások: 01_sql.txt (jelszóval)
- Vizsgáljuk meg a táblák szerkezetét, integritási megszorításokat!
- Fejezzük ki (többféleképpen is) SQL SELECT-tel:
  Kik (HAZON, NEV) azok a 'PTM'-szakos hallgatók, akiknek
  az ösztöndíjuk nagyobb, mint 10000 és  2005. évben  3-nál
  jobb jegyet szereztek 'Adatbázisok1' elnevezésű tantárgyból?

7opt_01
- A fenti több táblás lekérdezés algebrai optimalizációjára példa
  segédanyag: lásd rAlgOpt.rtf

Lekérdezések végrehajtási terve (költség alapú optimalizálás)
- A lekérdezések végrehajtási tervét az Oracle egy PLAN_TABLE nevű
  vagy ezzel azonos szerkezetű táblába teszi.
- PLAN_TABLE létrehozása -> ablinux gépen található script futtatásával
  /opt/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql
- Végrehajtási terv létrehozása a PLAN_TABLE nevű táblába:
   EXPLAIN PLAN SET statement_id='utasitas_egyedi_neve'
   FOR
   SELECT ...
- Majd a végrehajtási terv fa struktúrájának kiíratása egy hierarchikus
   lekérdezéssel:
   PROMPT "Adja meg az utasítás nevét:  "
   ACCEPT ut
   SELECT LPAD(' ', 2*level)||operation||' '||options||' '||object_name terv
   FROM plan_table
   START WITH id = 0 AND statement_id = '&&ut'
   CONNECT BY PRIOR id = parent_id AND statement_id = '&&ut'; 7opt_01

7opt_02
- Lekérdezésekre és a végrehajtási tervekre példák: tervek1.txt

7opt_03
- Az ORAUSER felhasználó EMP, DEPT es SALGRADE (fizetési kategóriak)
   tábláiból hozzatok létre saját példányokat, ezekre vonatkozóan adjátok meg
   az alábbi lekérdezést, majd derítsétek ki a végrehajtási tervek fa struktúráját.
- A fa csomópontjaiban az ott elvégzett műveletet es azt az objektumot kell
   megadni, amin a műveletet az adatbáziskezelő végzi.
A lekerdezés:
- Adjuk meg azoknak az osztályoknak a neveit, amelyeknek van olyan dolgozója,
  aki az 1-es fizetési kategóriába esik.
- Adjuk meg, hogy milyen végrehajtasi tervet készít a rendszer.
- Hozzunk létre indexet valamelyik táblához, majd adjuk meg a rendszer
  által létrehozott új végrehajtási tervet. Olyan indexet hozzunk létre, amit
  a lekérdezésben használni tud a rendszer.
- Az EMP es a DEPT táblát most úgy hozzuk létre (pl. EMP2 es DEPT2 néven),
   hogy azok egy clusteren legyenek. Adjuk meg az ehhez létrehozott végrehajtási tervet.

stb. Végrehajtási tervek megváltoztatása tippek (hint) segítségével

______________________________________________________
8. gyak. (XI.8)  - I.ZH

Segédeszköz: ORACLE DATABASE DOCUMENTATION LIBRARY
használható a ZH alatt (és Oracle DBMS, sqlplus, isqlplus, SQL Developer)

Gépes feladatok (a gyakorlatok feladatai)
-- PL/SQL eljárások, függvények, triggerek (lásd 1-3 gyak. gépes feladatait)
-- Rendszerkatalógusok lekérdezése (lásd 4-6 gyak. gépes feladatait)
    (7.gyak. lekérdezések optimalizálása feladatai majd a 2zh-n lesznek)