Egy PL/SQL kódot az optimalizáló úgy tud gyorsabbá tenni, optimalizálni, hogy a programozó által megadott kódot részben átstrukturálja, illetve a kódban szereplő kifejezések kiszámításához minél hatékonyabb módot választ anélkül, hogy a kód értelmét megváltoztatná. A fordító garantálja, hogy egyetlen átalakítás sem eredményezheti explicit alprogramhívás kihagyását, elő nem írt új alprogramhívás bevezetését vagy olyan kivétel kiváltását, ami a kódban eredetileg is ki nem váltódna. Azonban az optimalizálás eredményezheti azt, hogy az optimalizált kód nem vált ki kivételt ott, ahol az optimalizálatlan kód kiváltana (lásd 14.1. Objektumtípusok és objektumok – 10. példa).
Az optimalizálás mértékét szabályozhatjuk a PLSQL_OPTIMIZE_LEVEL inicializációs
paraméterrel. Ennek lehetséges értékei:
2 – teljes körű optimalizálás, ez az alapértelmezett,
1 – lokális optimalizálás, a kódsorok a megadott sorrendben kerülnek végrehajtásra, ekkor lényegében csak a számítások és kifejezések optimalizálására van lehetőség,
0 – az optimalizáló kikapcsolása, a korábbi verzióknak megfelelő viselkedés.
Amennyiben a kódot az optimalizáló bekapcsolása mellett, de nyomkövetési információkkal fordítjuk le (ALTER ... COMPILE DEBUG), a kód tényleges optimalizálása nem lehetséges, mert az optimalizálás során a kódsorok sorszáma megváltozhat. Ilyenkor csak a lokális optimalizálás néhány elemét használja a fordító, ezt az esetet így valahová a 0 és 1 beállításértékek közé sorolhatjuk. Éppen ezért törekedjünk arra, hogy nyomkövetési információkat csakis fejlesztői környezetekben használjunk. Éles adatbázisok esetén ez a beállítás mind a teljesítményre, mind a kód méretére negatív hatással van.
Az alapértelmezett beállításhoz képest kevesebb optimalizálást előíró 1 és 0 beállítások egyetlen gyakorlati előnye a kisebb fordítási idő. Ezért azok használata csak akkor tanácsos, ha ez igazán számít, azaz fontosabb a kódok rövid fordítási ideje, mint a lefordított kódok futási ideje. Ilyen helyzet adódhat (de nem feltétlenül adódik) a következő esetekben:
ejlesztői környezetekben, ahol a nagyon gyakori kódújrafordítások lényegesebbek, mint a futási idő;
nagyméretű, kevés optimalizálható kódot (kevés számítást és ciklust) tartalmazó generált kód fordításakor;
egyszer használatos generált kód esetén, ahol a fordítási idő nagyságrendileg is összemérhető a futási idővel.
A teljesség igénye nélkül felsoroljuk és demonstráljuk a fordító néhány legfontosabb optimalizáló műveletét. A példák egyszerű tesztek, ahol az időmérést a SET TIMING ON SQL*Plus parancs és a DBMS_UTILITY.GET_TIME eljárás egyikével végeztük és egy-egy tipikusnak mondható futási időt a példákban rögzítettünk. A teszt pontos körülményei nem lényegesek, céljuk csak az, hogy szemléltessék az egyes példákon belüli optimalizált és optimalizálatlan kód futási idejének arányát. Az optimalizáló fordító működéséről bővebben lásd [28/a] és [28/b].
Ez az optimalizálás abba a kategóriába esik, amelynek más magas szintű nyelvekben nagy hagyományai vannak. Az ilyen kódok optimalizálására sok technika ismeretes, nézzünk ezek közül néhányat:
a) Fordítási időben kiértékelhető kifejezések kiértékelése. Ez többet jelent a konstanskifejezések kiértékelésénél, hiszen például egy X+NULL kifejezés mindig NULL lesz, az X értékétől függetlenül. Ugyanakkor az egyes kifejezések átírásakor is kialakulhatnak hasonló, előre kiszámítható kifejezések, amelyek eredetileg nem konstans kifejezések. Például a 3+X+2 kifejezés helyettesíthető az X+5 kifejezéssel, mivel a PL/SQL nem rögzíti az azonos precedenciájú műveletek kötési sorrendjét.
b) Egy kevésbé hatékony művelet helyett egy hatékonyabb használata. Tegyük fel, hogy valamely típusra a 2-vel való szorzás hatékonyabb, mint az összeadás. Legyen ilyen típusú kifejezés X. Ekkor X+X helyett 2*X használata hatékonyabb. Megfontolandó azonban az, hogy ha például X egy általunk írt függvény hívása, akkor a csere eliminál egy hívást. Lehet azonban, hogy számunkra X mellékhatása is fontos. Ilyen esetben az optimalizáló biztosítja, hogy X ugyanannyiszor kerüljön meghívásra, mint optimalizálás nélkül. Mindemellett azonban megteheti az átalakítást, ha a számítás így gyorsabb lesz. Azaz X+X helyett használhat X*2-t és egy olyan X hívást, amelynek az eredményét egyszerűen ignorálja.
A fordított eset még könnyebben elképzelhető, amikor X+X hatékonyabb, mint X*2. Ekkor azonban X többszöri meghívása következne be, ami nem elfogadható. Használható azonban egy segédváltozó, ha még így is gyorsabb kódot kapunk. Vagyis az
Y := X*2;
utasítás helyett használható a
T := X; Y := T+T;
utasítássorozat.
A valóságban ennél jóval bonyolultabb esetek fordulnak elő.
c) Kereszteredmények számítása. Ez akkor történhet, ha egy vagy több kifejezés tartalmaz olyan azonos részkifejezést, amely a részkifejezést tartalmazó kifejezések kiértékelése között nem változik. Ekkor az első számítás során keletkezett részeredmény a másodikban már számítás nélkül újrahasznosítható. Például
C := A+B+X; D := (A+B)*Y;
helyett állhat
T := A+B; C := T+X; D := T*Y;
Ha A vagy B értéke a két kifejezésben nem garantáltan ugyanaz, akkor az átalakítás nem végezhető el. Ezt az okozhatja, hogy a két kifejezés közé ékelődik egy olyan utasítás, amelynek hatására (értékadás) vagy mellékhatásaként (alprogramhívás) A vagy B értéke megváltozhat, illetve az, hogy A vagy B maga is függvény.
d) A precedencia által nem kötött műveletek kiértékelési sorrendjének tetszőleges megválasztása. Ebbe a kategóriába tartozik az alprogramok paramétereinek kiértékelési sorrendje is.
Ez a lista közel sem teljes, célja csupán a lehetőségek szemléltetése volt.
1. példa (A következő kódban egy ciklusban a ciklusváltozótól is függő számításokat végzünk, ez lehetőséget ad a számítások optimalizálására.)
CREATE OR REPLACE PROCEDURE proc_szamitas(
p_Iter PLS_INTEGER
) IS
a PLS_INTEGER;
b PLS_INTEGER;
c PLS_INTEGER;
d PLS_INTEGER;
BEGIN
FOR i IN 1..p_Iter
LOOP
a := i+1;
b := i-2;
c := b-a+1;
d := b-a-1; -- ismétlődő kifejezés előfordulása
END LOOP;
END proc_szamitas;
/
SHOW ERRORS;
-- Fordítás optimalizálással, majd futtatás
ALTER PROCEDURE proc_szamitas COMPILE PLSQL_OPTIMIZE_LEVEL=2
PLSQL_DEBUG=FALSE;
SET TIMING ON
EXEC proc_szamitas(10000000);
-- Eltelt: 00:00:03.06
SET TIMING OFF;
-- Fordítás optimalizálás nélkül, majd futtatás
ALTER PROCEDURE proc_szamitas COMPILE PLSQL_OPTIMIZE_LEVEL=0
PLSQL_DEBUG=FALSE;
SET TIMING ON
EXEC proc_szamitas(10000000);
-- Eltelt: 00:00:05.54
SET TIMING OFF;
Az optimalizált kód majdnem kétszer gyorsabb. Sőt ha figyelembe vesszük azt is, hogy a ciklus futási ideje mindkét esetben ugyanakkora „szükséges rossz” (ezt a fix időt a következő példáknál pontosan lehet látni), az arány tovább javul az optimalizált kód javára.
Az előző példában a ciklusmagban a számítás hivatkozik a ciklus változójára. Tekintsünk egy módosított számítást, ami a ciklusmagtól független és figyeljük meg a futási időket.
2. példa
⋮
FOR i IN 1..p_Iter
LOOP
a := 3+1;
b := 3-2;
c := b-a+1;
d := b-a-1; -- ismétlődő kifejezés előfordulása
END LOOP;
⋮
-- Fordítás optimalizálással, majd futtatás
⋮
-- Eltelt: 00:00:00.46
-- Fordítás optimalizálás nélkül, majd futtatás
⋮
-- Eltelt: 00:00:05.53
A futási idők között hatalmas lett a különbség. Míg az optimalizálatlan kód futási ideje nem változott, az optimalizált kód ideje radikálisan csökkent, mivel az optimalizáló kiemelte a ciklusmagból a ciklustól független számításokat, így azokat csak egyszer (!) kell elvégezni, nem pedig minden iterációban. Ez a kódolási stílus valójában programozói figyelmetlenség, ami azonban a gyakorlatban gyakran előfordul.
A következő példában mi magunk emeljük ki a független számítást.
3. példa
⋮
BEGIN
a := 3+1;
b := 3-2;
c := b-a+1;
d := b-a-1; -- ismétlődő kifejezés előfordulása
FOR i IN 1..p_Iter
LOOP
NULL; -- ez maradt a ciklusmagból
END LOOP;
END proc_szamitas;
⋮
-- Fordítás optimalizálással, majd futtatás
⋮
-- Eltelt: 00:00:00.45
-- Fordítás optimalizálás nélkül, majd futtatás
⋮
-- Eltelt: 00:00:00.45
Most nem tapasztalunk különbséget, a számítás csak egyszer fut le mindkét esetben. Tehát a futási idő lényegében a ciklus költsége.
Megjegyzés: Valójában az így kialakult csak üres utasítást tartalmazó ciklus teljesen kihagyható lenne, ezt a lépést az optimalizáló azonban szemmel látható módon most még nem teszi meg.
A nevesített konstansok a deklaráció során kapnak kezdőértéket, és ezután nem változtatják meg értéküket. Ezt kifejezések optimalizálásakor figyelembe veheti az optimalizáló és egyes részkifejezéseket előre kiszámíthat. Ha nevesített konstans helyett változót használunk, akkor a fordító úgy gondolja, hogy a változó értékének stabilitása nem garantált a program két pontja között, ha a két pont között van olyan utasítás, amely mellékhatásával képes megváltoztatni a változó értékét. Ez vagy értékadást, vagy olyan alprogramhívást jelent, amelynek hatáskörében benne van a változó, még akkor is, ha az eljárás nem is hivatkozik a változóra, és ténylegesen nem változtatja meg annak értékét. Tegyük hozzá, hogy csomagbeli alprogram esetén egy ilyen feltétel ellenőrzése csak körülményesen volna lehetséges. (Vesd össze PRAGMA RESTRICT_REFERENCES).
A következő példában három esetet vizsgálunk:
Egy majdnem üres ciklust, ami csak egy eljáráshívást tartalmaz. Ez adja majd az összehasonlítás alapját.
Egy olyan ciklust, ahol egy változó szerepel, amelynek az értéke azonban soha nem változik. Mivel itt is szerepel majd az eljáráshívás, a fordító nem tekintheti konstansnak a ciklusmagon belül a kifejezést.
Egy olyan ciklust, ahol nevesített konstans szerepel az előző eset változója helyett.
Mindhárom eset futási idejét látjuk optimalizálással és anélkül, fordított kód esetén.
4. példa
CREATE OR REPLACE PROCEDURE proc_konstans(
p_Iter PLS_INTEGER
) IS
c_Konstans CONSTANT NUMBER := 98765;
v_Konstans NUMBER := 98765;
v1 NUMBER := 1;
v2 NUMBER;
-- Tesztelést segítő változók
t NUMBER;
t1 NUMBER;
t2 NUMBER;
v_Ures_ciklus_ideje NUMBER;
-- Eljárás esetleges mellékhatással
PROCEDURE proc_lehet_mellekhatasa IS BEGIN NULL; END;
-- Tesztelést segítő eljárások
PROCEDURE cimke(p_Cimke VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT(RPAD(p_Cimke, 20));
END cimke;
PROCEDURE eltelt(
p_Ures_ciklus BOOLEAN DEFAULT FALSE
) IS
BEGIN
t := t2-t1;
IF p_Ures_ciklus THEN
v_Ures_ciklus_ideje := t;
END IF;
DBMS_OUTPUT.PUT_LINE('- eltelt: ' || LPAD(t, 5)
|| ', ciklusidő nélkül:' || LPAD((t-v_Ures_ciklus_ideje), 5));
END eltelt;
-- Az eljárás törzse
BEGIN
cimke('Üres ciklus');
t1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..p_Iter
LOOP
proc_lehet_mellekhatasa;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt(p_Ures_ciklus => TRUE);
cimke('Változó használata');
t1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..p_Iter
LOOP
proc_lehet_mellekhatasa;
v2 := v1 + v_Konstans * 12345;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt;
cimke('Konstans használata');
t1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..p_Iter
LOOP
proc_lehet_mellekhatasa;
v2 := v1 + c_Konstans * 12345;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt;
END proc_konstans;
/
SHOW ERRORS;
SET SERVEROUTPUT ON FORMAT WRAPPED;
PROMPT 1. PLSQL_OPTIMIZE_LEVEL=2
-- Fordítás optimalizálással, majd futtatás
ALTER PROCEDURE proc_konstans COMPILE PLSQL_OPTIMIZE_LEVEL=2
PLSQL_DEBUG=FALSE;
EXEC proc_konstans(2000000);
PROMPT 2. PLSQL_OPTIMIZE_LEVEL=0
-- Fordítás optimalizálás nélkül, majd futtatás
ALTER PROCEDURE proc_konstans COMPILE PLSQL_OPTIMIZE_LEVEL=0
PLSQL_DEBUG=FALSE;
EXEC proc_konstans(2000000);
/*
Egy tipikusnak mondható kimenet:
...
1. PLSQL_OPTIMIZE_LEVEL=2
Az eljárás módosítva.
Üres ciklus - eltelt: 78, ciklusidő nélkül: 0
Változó használata - eltelt: 186, ciklusidő nélkül: 108
Konstans használata - eltelt: 117, ciklusidő nélkül: 39
A PL/SQL eljárás sikeresen befejeződött.
2. PLSQL_OPTIMIZE_LEVEL=0
Az eljárás módosítva.
Üres ciklus - eltelt: 79, ciklusidő nélkül: 0
Változó használata - eltelt: 246, ciklusidő nélkül: 167
Konstans használata - eltelt: 245, ciklusidő nélkül: 166
A PL/SQL eljárás sikeresen befejeződött.
*/
A futási időkből látszik, hogy a nevesített konstansok alkalmazása csak optimalizáló használata esetén van hatással a futási időre.
Úgy tapasztaltuk, hogy a fordító nem mindig használja ki teljes mértékben a nevesített
konstans adta lehetőségeket.
Egy inicializáló blokkal ellátott csomagban az inicializáló blokk futása hagyományosan akkor történik meg, amikor a munkamenetben a csomag egy elemére először hivatkozunk. Vannak azonban olyan csomagbeli elemek, amelyek nem függnek a csomag inicializálásától. Az optimalizáló felismeri, ha ilyen elemre hivatkozunk és az ilyen hivatkozás nem váltja ki a csomag inicializálását. Ha egy munkamenet csak ilyen elemekre hivatkozik, akkor a csomag egyáltalán nem kerül inicializálásra, ezzel futási időt takarítunk meg.
Valójában a fordító az inicializáló blokkot egy rejtett eljárássá alakítja, így az attól való függőséget ugyanúgy tudja kezelni, mint más csomagbeli eljárások esetén.
Az optimalizáló azon elemek esetében képes felismerni az inicializáló blokktól való függetlenséget, melyek csak a csomag specifikációjában lévő deklarációjuktól függnek, nem hivatkoznak például alprogramra, és az inicializáló blokkban sem módosulhatnak. Hivatkozhatnak viszont csomagbeli nevesített konstansokra, típusokra, kivételekre és a csomagbeli elemek típusára %TYPE, %ROWTYPE attribútumokkal. Úgy tapasztaltuk, hogy az optimalizáló még ezekben az esetekben sem képes mindig felismerni és felhasználni a függetlenséget, például egy nevesített konstansnál a kezdeti értéket meghatározó kifejezés bonyolultsága is befolyásolja az optimalizálót.
5. példa
-- Csomag inicializálással és az inicializálástól nem függő tagokkal
CREATE OR REPLACE PACKAGE csomag_inittel
IS
-- Néhány csomagbeli elem
c_Konstans CONSTANT NUMBER := 10;
v_Valtozo NUMBER := 10;
TYPE t_rec IS RECORD (id NUMBER, nev VARCHAR2(10000));
END csomag_inittel;
/
CREATE OR REPLACE PACKAGE BODY csomag_inittel
IS
-- Csomaginicializáló blokk
BEGIN
DBMS_OUTPUT.PUT_LINE('Csomaginicializáló blokk.');
-- Egy kis várakozás, sokat dolgozik ez a kód...
DBMS_LOCK.SLEEP(10);
-- A DBMS_LOCK csomag a SYS sémában van,
-- használatához EXECUTE jog szükséges.
END csomag_inittel;
/
-- Csomagot hivatkozó eljárás
CREATE OR REPLACE PROCEDURE proc_csomag_inittel
IS
-- Csomagbeli típus hivatkozása
v_Rec csomag_inittel.t_rec;
v2 csomag_inittel.v_Valtozo%type;
BEGIN
-- Csomagbeli konstans hivatkozása
DBMS_OUTPUT.PUT_LINE('Csomag konstansa: ' || csomag_inittel.c_Konstans);
END proc_csomag_inittel;
/
-- Tesztek
-- Új munkamenet nyitása, hogy a csomag ne legyen inicializálva
CONNECT plsql/plsql
SET SERVEROUTPUT ON FORMAT WRAPPED;
-- Fordítás optimalizálással, majd futtatás
ALTER PROCEDURE proc_csomag_inittel COMPILE PLSQL_OPTIMIZE_LEVEL=2
PLSQL_DEBUG=FALSE;
SET TIMING ON
EXEC proc_csomag_inittel;
-- Eltelt: 00:00:00.01
SET TIMING OFF;
-- Új munkamenet nyitása, hogy a csomag ne legyen inicializálva
CONNECT plsql/plsql
SET SERVEROUTPUT ON FORMAT WRAPPED;
-- Fordítás optimalizálás nélkül, majd futtatás
ALTER PROCEDURE proc_csomag_inittel COMPILE PLSQL_OPTIMIZE_LEVEL=0
PLSQL_DEBUG=FALSE;
SET TIMING ON
EXEC proc_csomag_inittel;
-- Eltelt: 00:00:10.01
SET TIMING OFF;
A példából látszik, hogy a hivatkozó kódot és nem a csomagot kell újrafordítani.
Mivel a csomag specifikációja nem függ a törzstől, így nem lehet ellenőrizni, hogy egy alprogram egy változót vagy kurzort ténylegesen módosít-e vagy sem. Azt tudjuk, hogy megteheti. Hasonló módon egy nevesített konstans függvénnyel történő inicializálásakor nem tudhatjuk, hogy a függvény nem változtatja-e meg a csomag más elemeit. Ezért az így inicializált nevesített konstansok, változók, kurzorok valamint alprogramok hivatkozásakor a csomaginicializálás megtörténik.
Az X(IND) indexet tartalmazó kifejezés többszöri használata esetén, ha a két használat között X és IND is garantáltan változatlan, az X(IND) által hivatkozott elem címének kiszámítását nem kell újra elvégezni.
6. példa
CREATE OR REPLACE PROCEDURE proc_indexes_kifejezes(
p_Iter PLS_INTEGER
) IS
TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_Tab t_tab;
v_Index NUMBER;
v_Dummy NUMBER;
-- Tesztelést segítő változók
t NUMBER;
t1 NUMBER;
t2 NUMBER;
v_Ures_ciklus_ideje NUMBER;
-- Tesztelést segítő eljárások
PROCEDURE cimke(p_Cimke VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT(RPAD(p_Cimke, 25));
END cimke;
PROCEDURE eltelt(
p_Ures_ciklus BOOLEAN DEFAULT FALSE
) IS
BEGIN
t := t2-t1;
IF p_Ures_ciklus THEN
v_Ures_ciklus_ideje := t;
END IF;
DBMS_OUTPUT.PUT_LINE('- eltelt: ' || LPAD(t, 5)
|| ', ciklusidő nélkül:' || LPAD((t-v_Ures_ciklus_ideje), 5));
END eltelt;
-- Az eljárás törzse
BEGIN
cimke('Üres ciklus értékadással');
t1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..p_Iter
LOOP
v_Index := i-i; -- Egy értékadás
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt(p_Ures_ciklus => true);
cimke('Változó index');
t1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..p_Iter
LOOP
v_Index := i-i; -- Egy értékadás
v_Tab(v_Index) := 10;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt;
cimke('Változatlan index');
t1 := DBMS_UTILITY.GET_TIME;
v_Index := 0;
FOR i IN 1..p_Iter
LOOP
v_Dummy := i-i; -- Egy értékadás, hogy ugyanannyi kód legyen.
v_Tab(v_Index) := 10;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
eltelt;
END proc_indexes_kifejezes;
/
SHOW ERRORS;
SET SERVEROUTPUT ON FORMAT WRAPPED;
PROMPT 1. PLSQL_OPTIMIZE_LEVEL=2
-- Fordítás optimalizálással, majd futtatás
ALTER PROCEDURE proc_indexes_kifejezes COMPILE PLSQL_OPTIMIZE_LEVEL=2
PLSQL_DEBUG=FALSE;
EXEC proc_indexes_kifejezes(2000000);
PROMPT 2. PLSQL_OPTIMIZE_LEVEL=0
-- Fordítás optimalizálás nélkül, majd futtatás
ALTER PROCEDURE proc_indexes_kifejezes COMPILE PLSQL_OPTIMIZE_LEVEL=0
PLSQL_DEBUG=FALSE;
EXEC proc_indexes_kifejezes(2000000);
/*
Egy tipikusnak mondható kimenet:
...
1. PLSQL_OPTIMIZE_LEVEL=2
Az eljárás módosítva.
Üres ciklus értékadással - eltelt: 40, ciklusidő nélkül: 0
Változó index - eltelt: 110, ciklusidő nélkül: 70
Változatlan index - eltelt: 83, ciklusidő nélkül: 43
A PL/SQL eljárás sikeresen befejeződött.
2. PLSQL_OPTIMIZE_LEVEL=0
Az eljárás módosítva.
Üres ciklus értékadással - eltelt: 50, ciklusidő nélkül: 0
Változó index - eltelt: 146, ciklusidő nélkül: 96
Változatlan index - eltelt: 148, ciklusidő nélkül: 98
A PL/SQL eljárás sikeresen befejeződött.
*/
A fordító képes a statikus kurzor FOR ciklust átírni úgy, hogy 1-1 sor beolvasása helyett bizonyos mennyiségű sort korlátozott méretű kollekciókba együttes hozzárendeléssel olvas be, majd ezeken futtat ciklust és hajtja végre a ciklusmagot.
A következő példában az idő mérése mellett egy fontosabb jellemzőt, a logikai blokkolvasások számát is láthatjuk. Megfelelően nagy számú blokkolvasást igénylő lekérdezésre is szükség volt, ehhez a KONYV tábla Descartes-szorzatait képeztük és ebből megfelelő számú sort olvastunk ki.
7. példa
CREATE OR REPLACE PROCEDURE proc_ciklus_bulk
IS
TYPE t_num_tab IS TABLE OF NUMBER;
TYPE t_char_tab IS TABLE OF VARCHAR2(100);
v_Num_tab t_num_tab;
v_Char_tab t_char_tab;
cur SYS_REFCURSOR;
-- Tesztelést segítő változók
t1 NUMBER;
t2 NUMBER;
lio1 NUMBER;
lio2 NUMBER;
v_Session_tag VARCHAR2(100);
-- Tesztelést segítő eljárások
PROCEDURE cimke(p_Cimke VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT(RPAD(p_Cimke, 20));
END cimke;
PROCEDURE tag_session
IS
BEGIN
v_Session_tag := 'teszt-' || SYSTIMESTAMP;
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(v_Session_tag);
END tag_session;
FUNCTION get_logical_io RETURN NUMBER
IS
rv NUMBER;
BEGIN
/*
Itt SYS tulajdonú táblákat hivatkozunk.
A lekérdezéséhez megfelelő jogosultságok szükségesek.
Például SELECT ANY DICTIONARY rendszerjogosultság.
*/
SELECT st.value
INTO rv
FROM v$sesstat st, v$session se, v$statname n
WHERE n.name = 'consistent gets'
AND se.client_info = v_Session_tag
AND st.sid = se.sid
AND n.statistic# = st.statistic#
;
RETURN rv;
END get_logical_io;
PROCEDURE eltelt
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('- eltelt: ' || LPAD(t2-t1, 5)
|| ', LIO (logical I/O) :' || LPAD((lio2-lio1), 7));
END eltelt;
-- Az eljárás törzse
BEGIN
tag_session;
cimke('Statikus kurzor FOR');
t1 := DBMS_UTILITY.GET_TIME;
lio1 := get_logical_io;
FOR i IN (
SELECT k1.id, k1.cim
FROM konyv k1, konyv, konyv, konyv, konyv, konyv
WHERE ROWNUM <= 100000
)
LOOP
NULL;
END LOOP;
t2 := DBMS_UTILITY.GET_TIME;
lio2 := get_logical_io;
eltelt;
cimke('Kurzor BULK FETCH');
t1 := DBMS_UTILITY.GET_TIME;
lio1 := get_logical_io;
OPEN cur FOR
SELECT k1.id, k1.cim
FROM konyv k1, konyv, konyv, konyv, konyv, konyv
WHERE ROWNUM <= 100000
;
LOOP
FETCH cur
BULK COLLECT INTO v_Num_tab, v_Char_tab
LIMIT 100;
EXIT WHEN v_Num_tab.COUNT = 0;
FOR i IN 1..v_Num_tab.COUNT
LOOP
NULL;
END LOOP;
END LOOP;
CLOSE cur;
t2 := DBMS_UTILITY.GET_TIME;
lio2 := get_logical_io;
eltelt;
EXCEPTION
WHEN OTHERS THEN
IF cur%ISOPEN THEN CLOSE cur; END IF;
RAISE;
ND proc_ciklus_bulk;
/
SHOW ERRORS;
SET SERVEROUTPUT ON FORMAT WRAPPED;
-- Fordítás optimalizálással, majd futtatás
PROMPT 1. PLSQL_OPTIMIZE_LEVEL=2
ALTER PROCEDURE proc_ciklus_bulk COMPILE PLSQL_OPTIMIZE_LEVEL=2
PLSQL_DEBUG=FALSE;
EXEC proc_ciklus_bulk;
PROMPT 2. PLSQL_OPTIMIZE_LEVEL=0
-- Fordítás optimalizálás nélkül, majd futtatás
ALTER PROCEDURE proc_ciklus_bulk COMPILE PLSQL_OPTIMIZE_LEVEL=0
PLSQL_DEBUG=FALSE;
EXEC proc_ciklus_bulk;
/*
Egy tipikusnak mondható kimenet:
...
1. PLSQL_OPTIMIZE_LEVEL=2
Az eljárás módosítva.
Statikus kurzor FOR - eltelt: 100, LIO (logical I/O) : 34336
Kurzor BULK FETCH - eltelt: 81, LIO (logical I/O) : 34336
A PL/SQL eljárás sikeresen befejeződött.
2. PLSQL_OPTIMIZE_LEVEL=0
Az eljárás módosítva.
Statikus kurzor FOR - eltelt: 636, LIO (logical I/O) : 133336
Kurzor BULK FETCH - eltelt: 80, LIO (logical I/O) : 34336
A PL/SQL eljárás sikeresen befejeződött.
*/
Az eredményből látszik, hogy az explicit együttes hozzárendelés volt még így is a leggyorsabb. Ehhez azonban jól el kellett találni a FETCH utasításban a LIMIT kulcsszó után megadott korlátot (100). A korlát változtatása ugyanis nagyban befolyásolja az eredményt, de ezen állítás ellenőrzését az Olvasóra bízzuk.
A logikai I/O azért kevesebb BULK COLLECT esetén, mert ilyenkor a szerver egy FETCH során beolvasott blokkokból több sort is kiolvas egyszerre, míg soronkénti betöltésnél, minden FETCH blokkolvasást eredményez akkor is, ha két egymás után beolvasott sor ugyanabban a blokkban tárolódik. Az együttes hozzárendelés hátránya, hogy több memóriát igényel. Azonban ha explicit módon használjuk, akkor a kollekciók méretét is tudjuk szabályozni a rendszer követelményeinek megfelelően.