Ebben a részben a hatékony kód írásához adunk néhány rövid tanácsot.
Főbb hangolási alapelvek
Mérjünk. Mérjünk hangolás előtt, hogy legyen referenciánk.
Tűzzük ki a célt, amivel elégedettek leszünk. Próbáljuk felmérni, hogy ez a teljesítménynövekedés mennyi munkát igényel és döntsünk, hogy megéri-e.
Keressük meg a legszűkebb keresztmetszetet. A Pareto-elv, másik nevén a 80/20 szabály szerint általánosságban egy rendszerben a tényezők egy kisebbik része (körülbelül 20%) felelős a jelenségek nagyobbik részéért (körülbelül 80%). Ezt hatékonyságra úgy fordíthatjuk le, hogy a futási idő 80%-ában a szerver a kód ugyanazon 20%-át futtatja. Az elv iteratív alkalmazásával már 64/4 arányt kapunk. A hangsúly tulajdonképpen nem a pontos számokon van, jelentése az, hogy a megfelelő helyen történő kis módosítással is nagy eredményt érhetünk el.
Egyszerre egy módosítást végezzünk. Az összetett hatásokat megjósolni és mérni is nehezebb. A mérési eredmény rossz értelmezése viszont félrevezető lehet.
A módosítás hatását jósoljuk meg, állítsunk fel hipotézist. Ez könnyű, hiszen azért módosítunk, mert várunk tőle valamit. Ezt az elvárást tudatosítsuk, pontosítsuk.
Mérjünk. Mérjünk minden egyes módosítás után, hogy a hipotézisünket bizonyítsuk, vagy cáfoljuk. Ezzel egyben a módosítás hatására bekövetkező teljesítményváltozást is dokumentáljuk.
A fejlesztés során minél később gondolunk a teljesítményre, annál költségesebb a hangolás.
Ha a fejlesztés során túl korán kezdünk el a teljesítménnyel foglalkozni az a tervezést félreviheti, az architektúra túlzott megkötését eredményezheti.
A méréshez és a szűk keresztmetszet kereséséhez használhatjuk a legegyszerűbb eszközöket, mint a SET TIMING parancs, vagy a DBMS_UTILITY.GET_TIME függvény, de komolyabb eszközök is rendelkezésre állnak, mint a DBMS_PROFILER, DBMS_TRACE csomagok, a dinamikus teljesítménynézetek (V$SESSTAT, V$SYSSTAT stb.), vagy a tágabb értelemben vett adatbázis szintű optimalizálást támogató STATSPACK programcsomag, a beépített Automatic Workload Repository (AWR) vagy az SQL trace eszközrendszer.
Ezek az eszközök minden Oracle-adatbázisnál megtalálhatók.
Tanácsok hangolási tanácsok megfogadásához
Egészséges kételkedés – Minden hangolási tanácsot fenntartással kezeljünk. Az Oracle adatbázis-kezelő folyamatos fejlesztés alatt áll. Ami egy korábbi verzióban igaz lehetett, az már nem biztos, hogy megállja a helyét. Az internetes fórumok is bizonytalan forrásnak tekintendők.
Bizonyosság – A tanácsokat igyekezzünk egyszerű mérésekkel ellenőrizni mielőtt teljesen megbíznánk bennük.
Hatékonyságnövelő tanácsok
Hangoljuk a kódba ágyazott SQL utasításokat. A PL/SQL kódban kiadott SQL utasításokat (akár statikusak, akár dinamikusak) az SQL motor hajtja végre ugyanúgy, mintha azt más nyelvből adtuk volna ki. Az SQL utasítások hangolása nem triviális feladat, a hivatalos dokumentáció itt is jó kiindulási pont lásd [17].
Használjuk a RETURNING utasításrészt DML utasításoknál a sor adatainak visszaolvasására. Beszúrásnál például a beszúrt sor egészét visszaolvashatjuk, nem kell az oszlopok adatait megadó kifejezéseket a beszúrás előtt változókban tárolni, a nem explicit módon inicializált oszlopértékeket utólag lekérdezni.
SELECT t_seq.NEXTVAL
INTO v_Id
FROM dual;
INSERT INTO t(id, oszlop_explicit_ertekkel)
VALUES(v_Id, 'ERTEK');
SELECT oszlop_default_ertekkel
FROM t
INTO v_Mi_lett_az_erteke
WHERE id = v_Id;
-- helyette
INSERT INTO t(id, oszlop_explicit_ertekkel)
VALUES(t_seq.NEXTVAL, 'ERTEK')
RETURNING id, oszlop_default_ertekkel
INTO v_Id, v_Mi_lett_az_erteke;
Használjunk együttes hozzárendelést. Ciklusban elhelyezett DML utasításoknál a FORALL különböző változatait, míg SELECT, FETCH és RETURNING esetén a BULK COLLECT utasításrészt használhatjuk statikus és dinamikus SQL esetén egyaránt. Láthattuk, hogy az optimalizáló már megtesz bizonyos lépéseket ennek automatizálásában, ugyanakkor még mindig az explicit együttes hozzárendelés teljesítménye a legjobb, ezenfelül a kód így jobban is érthető. Hátránya, hogy több kódolással jár, deklarálnunk kell a változókat és esetleg a típusokat is, a végrehajtható kód is hosszabb lesz. A nagyobb méretű, bonyolultabb kód nehezebben olvasható és tartható karban.
Ha lehet, használjunk SQL-t PL/SQL helyett (!). Ez igen furcsa tanács egy PL/SQL-ről szóló könyvben, mégis helytálló. A PL/SQL egy imperatív nyelv, ezért majdnem mindent meg tudunk benne oldani egytáblás SQL utasításokkal és ciklusokkal is. A kezdő PL/SQL programozó hajlamos ezeket az imperatív eszközöket túlzott mértékben használni, előnyben részesíteni az SQL megfelelőjükkel szemben. Az Oracle minden új verziójában az SQL nyelv nagyon sok olyan új eszközzel gazdagodott, melyek a nyelv kifejezőerejét és hatékonyságát is növelik. Ezért PL/SQL programozóként törekedjünk az SQL minél több lehetőségének megismerésére, és mindig tegyük fel a kérdést, hogy melyik világ eszközeit tudnánk az adott helyzetben hatékonyabban, szebben, a célnak leginkább megfelelő módon használni.
Néhány fontosabb eset, ahol a PL/SQL kód sokszor helyettesíthető SQL használatával:
– Szűrjünk SQL-ben. A WHERE korábban szűr, mint egy IF. A felesleges sorok nem kerülnek betöltésre és az SQL utasítás végrehajtási terve is javulhat.
– Kurzor ciklusába ágyazott lekérdezéseknél a kurzor SELECT-je és a magban szereplő lekérdezés esetlegesen egyetlen SELECT-be is összevonható allekérdezések és összekapcsolások használatával.
FOR v_Konyv IN (
SELECT id, cim FROM konyv
) LOOP
FOR v_Kocsonzo IN (
SELECT kolcsonzo FROM kolcsonzes WHERE konyv = v_Konyv.id
) LOOP
.
.
.
END LOOP;
END LOOP;
-- helyette
FOR v_Konyv_kolcsonzo IN (
SELECT kv.id, kv.cim, ko.kolcsonzo
FROM konyv kv, kolcsonzes ko
WHERE ko.konyv = kv.id
) LOOP
.
.
.
END LOOP;
Itt még tovább tudnánk növelni a teljesítményt együttes hozzárendelés alkalmazásával.
Hátránya akkor van, ha az új SELECT végrehajtása lassabb lesz a nagyobb bonyolultság miatt, vagy ha az így létrejövő hosszabb sorok jelentősen nagyobb adatmozgást eredményeznek.
– Kurzor ciklusába ágyazott DML utasításoknál a ciklus és a DML utasítás összevonható egyetlen MERGE utasításba. A két művelet közötti korreláció a MERGE feltételeivel megfogalmazható: az esetleges ciklusbeli IF feltételek a USING ON feltételévé, vagy a MERGE ágainak WHEN feltételeivé alakíthatók.
Hátránya, hogy a tömören fogalmazó MERGE nem mindenki számára olvasható könnyen, és ha a ciklusban több független DML művelet is van, akkor a ciklus kiemelő hatása általában erősebb, a ciklus használata ilyenkor jellemzően elegánsabb és hatékonyabb kódot eredményez.
– Néha a több ciklussal összekombinált lekérdezések, esetleg a ciklusmagba ágyazott elágaztató utasítások lényegében halmazműveleteket valósítanak meg. Ilyenkor gondoljunk az SQL INTERSECT, UNION, UNION ALL, MINUS, NOT EXISTS, IN stb. eszközeire.
– Használjuk SQL-ben az analitikus függvényeket, aggregálásnál a KEEP utasításrészt. Nagyon gyakran lehet sok-sok lekérdezést megspórolni ezekkel.
– Használjuk ki a SELECT lehetőségeit, az új csoportképző eszközöket, a CONNECT BY, MODEL utasításrészeket stb.
– Használjuk a feltételes INSERT-et olyan kurzort használó ciklus helyett, ahol a magban bizonyos feltételektől függően végzünk beszúrást egy vagy több táblába.
– Néha ciklusok helyett használhatunk sorszámokat és egyéb adatokat generáló lekérdezéseket, amiket aztán más utasításokban alkérdésként használhatunk.
FOR i IN 1..100
LOOP
INSERT INTO t(sorszam, paros_paratlan)
VALUES(i, DECODE(MOD(ROWNUM,2),0, 'páros', 'páratlan'))
;
END LOOP;
-- helyette
INSERT INTO t(sorszam, paros_paratlan)
SELECT ROWNUM sorszam
, DECODE(MOD(ROWNUM,2),0, 'páros', 'páratlan') paros_paratlan
FROM dual
CONNECT BY LEVEL <= 100
;
– Használjuk DML utasításoknál a hibanaplózás funkciót ahelyett, hogy minden utasítás előtt explicit ellenőriznénk a DML utasítás előfeltételeit, vagyis azt, hogy a művelet nem fog-e sérteni megszorítást a konkrét sor esetében. Így összevonhatunk több műveletet esetleg együttes hozzárendelés használatával. A hibanaplót aztán elemezzük szükség esetén és csak a hibás sorokra hajtsuk végre újra a műveleteket. Használjuk ezt a lehetőséget a FORALL utasítás SAVE EXCEPTIONS utasításrészéhez hasonlóan.
SQL-ben használt függvényeknél, ha lehet, adjuk meg a DETERMINISTIC kulcsszót. Ilyenkor az SQL motor egy gyorsítótáblában tárolja a már kiszámolt értékeket, és ezzel függvényhívást takaríthat meg. WHERE feltételben szereplő oszlopra hivatkozó determinisztikus függvénykifejezések esetén használhatunk függvényalapú indexet, ilyenkor a függvényhívás a beszúrás vagy módosítás során történik meg, nem a lekérdezés végrehajtásakor.
SQL-ben szereplő függvényhívások vagy bonyolult kifejezések esetén törekedjünk a hívások/kiértékelések számának minimalizálására. Ha a függvény oszlopra hivatkozik, használjunk alkérdést, mert az csökkentheti a hívás alapjául szolgáló sorok számát, így a kiértékelések számát.
SELECT DISTINCT SQRT(x) FROM ...
-- helyette
SELECT SQRT(distinct_x)
FROM (SELECT DISTINCT x AS distinct_x FROM ... )
Vegyük észre, hogy a fenti példában alkalmazott átalakítás nem tehető meg automatikusan,
nem lenne azonos átalakítás például MOD(x, k) hívás mellett.
Nagyméretű paraméterek átadásakor fontoljuk meg a NOCOPY használatát. Nagyméretű típus lehet rekord, kollekció, objektum, LOB, karakteres típus. A NOCOPY használható objektumtípusok metódusaiban is.
A későbbiekben viszont vegyük figyelembe a megváltozott paraméterátadás módjából adódó viselkedést.
Logikai operátorokkal képzett bonyolult kifejezésekben használjuk ki a rövidzár kiértékelést. Vegyük figyelembe a részkifejezések költségét és a rövidzárt kiváltó eredményük valószínűségét.
Kerüljük az adatkonverziót, főleg az implicit adatkonverziót. Használjunk megfelelő típusú literálokat és változókat.
A számítások eredményeit gyorsítás céljából őrizzük meg. Használhatunk lokális változókat, kollekciókat, ideiglenes vagy normál táblákat. A csomagban deklarált változók a munkameneten belül hívások közt is megtartják értéküket. Gyorsítótár gyanánt deklarálhatunk egy csomagban karakteres indexű asszociatív tömböket, amik használhatók kulcs-érték párok memóriában való tárolására. Ilyen kollekcióknál figyeljünk oda a memóriahasználatra.
.
.
.
TYPE t_num_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(1000);
v_Cache_tab t_num_tab;
.
.
.
FUNCTION fn(p1 NUMBER, p2 VARCHAR2) RETURN NUMBER
IS
.
.
.
FUNCTION cached_fn(p1 NUMBER, p2 VARCHAR2) RETURN NUMBER
IS
v_Key VARCHAR2(1000);
rv NUMBER;
BEGIN
v_Key := p1 || '#' || p2;
IF v_Cache_tab.EXISTS(v_Key) THEN
rv := v_Cache_tab(v_Key);
ELSE
rv := fn(p1, p2);
v_Cache_tab(v_Key) := rv;
END IF;
RETURN rv;
END cached_fn;
.
.
.
A példa csak az idiómát mutatja be, a memóriahasználatra nem ügyel, és nem is elég általános.
Számításoknál használjunk megfelelő típust:
– Használjunk PLS_INTEGER típust egész számításokhoz.
– Korlátozott altípusok használata felesleges ellenőrzésekkel járhat a számítások során, ilyen típusok például INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN.
– Használjunk BINARY_FLOAT vagy BINARY_DOUBLE típust valós számításokhoz. Üzleti számításoknál ez a megkövetelt pontosság miatt sokszor nem jó megoldás, mert kettes számrendszerben már egy tized (1/10) is végtelen bináris tört, így a konverziók során pontosságot veszíthetünk.
DECLARE
a BINARY_DOUBLE := 1;
b BINARY_DOUBLE := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Egy tized: ' || TO_NUMBER(a/b));
END;
/
-- Egy tized: ,10000000000000001
Lokális VARCHAR2 típusú változók hosszát válasszuk elég nagyra a futási idejű hibák megelőzése céljából. Egy VARCHAR2 típusú változó memóriában lefoglalt mérete mindig az aktuális értéktől függ. A nagyon megszorító korlátozásnak általában akkorvan értelme, ha a változó adatbázisbeli tábla oszlopának beszúrás előtti értékét tárolja. Ilyenkor használjunk %TYPE attribútumot.
A hatékonyabb memóriahasználat és a függőségek egyszerűsítése érdekében a logikailag összetartozó alprogramokat tegyük egy csomagba. Ha egy csomag egy alprogramját meghívjuk, az egész csomag betöltődik a memóriába. Ha ezután egy ugyanebben a csomagban definiált alprogramot hívunk meg, már annak a kódja is a memóriában lesz.
A munkamenetek által visszatérően és elég gyakran használt nagyméretű csomagokat rögzítsük a memóriába a DBMS_SHARED_POOL csomaggal. Az ilyen csomag kódja mindig a memóriában, az osztott tartományban marad, függetlenül az osztott tartomány telítődésétől és a csomag használatának sűrűségétől.
Ne használjuk a COMMIT parancsot túl gyakran és fölöslegesen. Ha tehetjük, tegyük a COMMIT-ot cikluson kívülre, vagy cikluson belül valamilyen gyakorisággal hajtsuk csak végre. Esetleg használjuk a COMMIT WRITE BATCH NOWAIT utasítást, ha elfogadható a működése.
Memóriában rendezhetünk asszociatív tömbbel. Ha egy asszociatív tömbbe elemeket szúrunk be és az elemeken a FIRST, NEXT vagy a LAST, PRIOR párokkal iterálunk, a kulcsokon rendezetten haladunk végig. Karakteres kulcsok esetén vegyük figyelembe, hogy a rendezést az NLS_COMP és az NLS_SORT inicializációs paraméterek befolyásolják, a rendezés a kis- és nagybetűkre is érzékeny.
ALTER SESSION SET NLS_COMP=ANSI;
DECLARE
s VARCHAR2(100);
TYPE t_karakteres_kulcsok IS
TABLE OF BOOLEAN INDEX BY s%TYPE;
v_Rendezo_tabla t_karakteres_kulcsok;
BEGIN
v_Rendezo_tabla('öszvér') := TRUE;
v_Rendezo_tabla('ló') := TRUE;
v_Rendezo_tabla('szamár') := TRUE;
s := v_Rendezo_tabla.FIRST;
WHILE s IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(s);
s := v_Rendezo_tabla.NEXT(s);
END LOOP;
END;
/
Natív dinamikus SQL használatával meghívhatunk egy a nevével paraméterként átvett alprogramot (callback function). Ehelyett objektumtípust és késői kötést is alkalmazhatunk. A hívás így sokkal gyorsabb és ellenőrzöttebb lesz.
CREATE OR REPLACE PROCEDURE proc(p_Number_fuggveny VARCHAR2)
IS
v_Num NUMBER;
BEGIN
EXECUTE IMMEDIATE 'BEGIN :x := ' || p_Number_fuggveny || '; END;'
USING OUT v_Num;
DBMS_OUTPUT.PUT_LINE('Num: ' || v_Num);
END proc;
/
CREATE OR REPLACE FUNCTION a_number_fuggveny_1 RETURN NUMBER
IS
BEGIN
RETURN 1;
END a_number_fuggveny_1;
/
SET SERVEROUTPUT ON FORMAT WRAPPED
EXEC proc('a_number_fuggveny_1');
-- Num: 1
--------------
-- helyette --
--------------
CREATE OR REPLACE TYPE T_Obj1 IS OBJECT (
attr CHAR(1),
MEMBER FUNCTION a_number_fuggveny RETURN NUMBER
)
NOT FINAL NOT INSTANTIABLE
/
CREATE OR REPLACE TYPE T_Obj2 UNDER T_Obj1(
CONSTRUCTOR FUNCTION T_Obj2 RETURN SELF AS RESULT,
OVERRIDING MEMBER FUNCTION a_number_fuggveny RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY T_Obj2 IS
CONSTRUCTOR FUNCTION T_Obj2 RETURN SELF AS RESULT
IS BEGIN RETURN; END;
OVERRIDING MEMBER FUNCTION a_number_fuggveny RETURN NUMBER
IS
BEGIN
RETURN 1;
END a_number_fuggveny;
END;
/
CREATE OR REPLACE PROCEDURE proc(p_Obj T_Obj1)
IS
v_Num NUMBER;
BEGIN
v_Num := p_Obj.a_number_fuggveny;
DBMS_OUTPUT.PUT_LINE('Num: ' || v_Num);
END proc;
/
SET SERVEROUTPUT ON FORMAT WRAPPED
EXEC proc(T_Obj2());
-- Num: 1
Explicit OPEN utasítással megnyitott kurzor esetén a használó blokk kivételkezelőjének valamennyi ágában biztosítsuk, hogy a kurzor véletlenül se maradhasson nyitva. A nyitott kurzorok száma adatbázis példányonként limitált, telítettsége kényszerleálláshoz is vezethet. Kurzor FOR ciklus esetén erre nincs szükség, a kurzor automatikusan kerül megnyitásra és lezárásra, függetlenül attól, hogy a ciklus szabályosan vagy kivétellel fejeződik-e be.
BEGIN
.
.
.
OPEN cur;
.
.
.
CLOSE cur;
.
.
.
EXCEPTION
WHEN kivétel THEN
IF cur%ISOPEN THEN CLOSE cur; END IF;
… -- Kivétel kezelése
WHEN OTHERS THEN
IF cur%ISOPEN THEN CLOSE cur; END IF;
RAISE; -- Kivétel továbbadása a kurzor lezárása után.
END;
Készítsük fel a programjainkat arra, hogy támogassák az előre nem látott hibák könnyű lokalizálását és javítását. Naplózzuk, vagy jelentsük a bekövetkezett kivételeket. Ha máshol nem, legalább a legkülső hívó programegység szintjén tegyük ezt meg. A hívási láncról és a bekövetkezett hibákról, azok helyéről alapvető információkkal szolgálnak a DBMS_UTILITY csomag FORMAT_CALL_STACK, FORMAT_ERROR_BACKTRACE és FORMAT_ERROR_STACK függvényei. A hibanapló bejegyzéseinek méretén ne spóroljunk, több információ mellett kevesebb idő alatt megtalálhatjuk a hiba valódi okát.
A kód átláthatóságát növeli és a „copy-paste” hibák előfordulását csökkenti az, ha az alprogramokat, triggereket és csomagokat záró END utasításban használjuk a programegység nevét.
Értelmezzük a fordító figyelmeztetéseit, a kód javításával törekedjünk azok megszüntetésére.
Ismerjük meg a szerver alapcsomagjait. Sokszor olyan műveletekre is van már alapcsomagban eszköz, amire nem is gondolnánk: mátrixszámításokhoz hatékony implementációt tartalmaz az UTL_NLA, webszolgáltatások használatát támogatja az UTL_DBWS, időzíthetünk költséges batch műveleteket a DBMS_JOB segítségével stb.
Ismerjük meg és használjuk a beépített SQL függvényeket. Használjuk ezeket ahelyett, hogy saját magunk újraimplementálnánk a funkcionalitásaikat. A meglevő függvények alapos teszteléseken mentek át, megbízhatóan működnek, sok esetben hatékonyabbak, mivel az implementációjuk gyakran C nyelvű, ilyen hatékony kódot PL/SQL-ben nem is tudnánk írni. Különösen igaz ez a karakteres függvényekre.
További tanácsok és ajánlások a dokumentációban (lásd [19] 11. fejezet) és az interneten
(lásd [26], [27]) találhatók.