Összetett makrók készítése LuaPlSql Plug-In for PL/SQL Developer - rel

OWB - mapek teljesítményhangolása közben kezdtem beleunni abba, hogy rutinszerű feladatokat ismételgetek újra és újra. A beépített makro funkciót használva az alap dolgaimat már sikerült automatizálnom, viszont egykét művelet, amit csinálok már túlmutat a makró rögzítés lehetőségein. A beépített makró csak billentyűzet inputokat tud rögzíteni.

A kísérletezgetés közben rátaláltam egy beállításra, ami makrók készítésénél jól tud jönni. Ha arra van szükségünk, hogy a result grid adatait is elérjük, akkor ezt el kell tudnunk érni billentyűzetkombinációk használatával. Be lehet állítani egy billentyűzetkombinációt a Key Configuration alatt az SQL Window: Switch between editor/results sorában. Ezt használva már képesek leszünk olyan makrókat is készíteni, amelyek felhasználják a futási eredményeket is.

Az egyik feladat, amit automatizálni akartam az, hogy könnyen meg tudjam nézni a MAP - em milyen konkrét SQL - t futtat. A manuális eljárás úgy nézett ki, hogy egy v%session lekérdezésből megtudom a futtatott sql_id azonosítót, ezt kimásolom, a v$sql sql_fulltext mezőből kikapom a teljes sql szöveget, ezt berakom egy SQL Window - ba és futtatok rá egy Beautifier - t, hogy olvasható legyen az egyébként generált kód. Ezt a műveletsort már több százszor el kellet játszanom, kezdtem unni. Makróval azonban nem tudtam megoldani a feladatot, mert sajnos a result grid CLOB mező tartalmához nem tudtam csak billentyűkkel hozzáférni.

Felmentem az fejlesztők oldalára, az allroundautomations.com - ra és elkezdtem kutakodni, létezik - e valamilyen Plug-In, amivel komolyabb feladatokat is automatizálni tudok. Így találtam rá a LuaPlSql Plug-In - re. Ez a program lehetővé teszi, hogy interpretált script nyelven programozva szabadon meghívhassuk a PL/SQL Developer Plug-In Interface függvényeit. Nem kell semmit sem fordítgatnunk, a Lua scripteket magában a Developer - ben is megszerkeszthetjük.

A mintapéldákat átnézve, kb. 2 óra alatt eljutottam odáig, hogy megírtam életem első, tökéletesen működő Lua script - jét, mely annyit tesz, hogy egy SQL Window - ban levő sql_id szöveget lecserél az adott sql_id - hoz tartozó sql teljes, formázott szövegére. Íme a script:

do

  local function f1(menuItem)

  SQL.Execute("select sql_fulltext from v$sql where sql_id = '" .. IDE.GetText() .. "'")

  IDE.SetText(SQL.Field(1))

  IDE.BeautifyWindow()

  end

  AddMenu(f1, "&Lua / v$sql")

end

Nem is olyan bonyolult.

Ennek felhasználásával már el tudtam készíteni a komplett makrómat, így kibővült az eszköztáram s most már egy klikkeléssel meg tudom nézni a futó sql - ek formázott szövegét, végrehajtási tervét, temp használatát és igény esetén egy klikk távolságra van a feladat megszakítása is.

Aki hozzám hasonló cipőben jár, azaz úgy érzi, rutin dolgokat csinál újra és újra, annak bátran ajánlom a LuaPlSql Plug-In – t! A makrókkal kombinálva egyszerűen és gyorsan lehet automatizálni akár komplex műveleteket is.

Parallel_enable opció alkalmazása PL/SQL függvényeknél

Az adattárházunk teljesítmény-hangolása közben sikerült kiszúrnom egy újabb apróságot. 10G R2 adatbázison dolgozunk, az egyik feldolgozásunkhoz szükségünk volt karakter aggregálásra, a group by csoportba tartozó szöveges mezők tartalmának összefűzésére. Ez az ORACLE-BASE cikk nagyon szépen összefoglalja milyen lehetőségeink vannak a probléma megoldására.

Mi az egyik legelterjedtebb megoldás választottuk, azt, hogy létrehozunk egy "user defined aggregate function" - t, melyben megadjuk hogyan kell a karakteres mezőt aggregálni. Ebben a megoldásban definiálni kell egy függvényt, amit a group by lekérdezéseinkben használhatunk. Ennek a definíciója valahogy így néz ki:

Create Or Replace Function SUM_VARCHAR2(pv_input Varchar2) Return Varchar2 parallel_enable Aggregate Using SUM_VARCHAR2_TYPE;

Mi elkövettük azt a hibát, hogy a definícióból véletlenül lemaradt a parallel_enable. Ennek következtében a feldolgozásunk átment soros végrehajtásba, és annak rendje és módja szerint lelassult.

A tapasztalat ebből annyi, hogy ha a végrehajtási tervben azt látjuk, hogy a feldolgozásunk egy része soros végrehajtásra fut, akkor célszerű ellenőrizni, hogy az általunk írt és felhasznált függvényeknél engedélyeztük - e a párhuzamos végrehajtást.

További gondolatok a szekvenca cache size beállításról.

Tovább kutakodtam a szekvencia cache méretezés témakörében. Az adattárház üzemeltetők részéről felmerült az aggodalom, hogy ha túl magas értékre állítom be a cache értéket az felemészti a memóriát. Kiderítettem, hogy ez az aggodalom alaptalan. Az Oracle support [ID 378302.1] bejegyzéséből megtudtam, hogy a memóriában csupán két szám tárolódik, a szekvencia aktuális értéke és egy high water mark érték, a cache konkrét méretétől függetlenül:

A fentieket alátámasztó idézetek az említett support bejegyzésből:
"a sequence is typically cached in memory as a last used value and a high water mark value"
"NOTE: increasing the cache size of a sequence does not waste space, the cache is still defined by just two numbers"

Készítettem egy teszt scriptet, amivel demonstrálni lehet a különböző beállításokhoz tartozó teljesítmény különbséget:


drop sequence test_seq;
drop table seq_test_table;

create sequence test_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

create table seq_test_table as
select /*+ seq_nocache */ test_seq.nextval val
from dba_objects
where rownum < 100000;

select s.DIRECT_WRITES, s.BUFFER_GETS, s.USER_IO_WAIT_TIME, s.CPU_TIME, s.ELAPSED_TIME
from v$sql s
where s.sql_text like ' create table seq_test_table as select /*+ seq_nocache */%';

drop sequence test_seq;
drop table seq_test_table;

create sequence test_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 100000;

create table seq_test_table as
select /*+ seq_cache */test_seq.nextval val
from dba_objects
where rownum < 100000;

select s.DIRECT_WRITES, s.BUFFER_GETS, s.USER_IO_WAIT_TIME, s.CPU_TIME, s.ELAPSED_TIME
from v$sql s
where s.sql_text like ' create table seq_test_table as select /*+ seq_cache */%'; 

A fejlesztői rendszerünkben a fenti script nocache esetén 43 másodpercig futott, míg 100000 - es caceh esetén mindössze 2 másodpercig.

A fentiek alapján érdemes az alábbi lekérdezés eredményén elmeditálni egy kicsit:

select *
from dba_sequences s
where s.cache_size = 0


Hogyan állítsuk be a szekvenciák cache size paraméterét DW környezetben?

A mai napon új dolgot tanultam az adattárházak teljesítmény-hangolásáról. Szétszedtem egy párhuzamos végrehajtási tervvel futó, parallel DML - t is használó lekérdezést darabokra, mely érzésem szerint a kelleténél tovább futott. Azt találtam, hogy végrehajtás részeredményei viszonylag hamar előállnak, az eredmények táblába írása viszont borzalmasan lassú. További kutakodás után kiderült, hogy a végleges rekordokhoz egy szekvenciából vesz az SQL - em egy sorszámot, ha ezt a műveletet kihagyom, akkor pillanatok alatt lefut a lekérdezésem, ha beveszem akkor a végtelenségig fut.

Kicsit megvizsgálva a szekvenciát, kiderült róla, hogy nocache opcióval lett létrehozva. Ez rögtön rossznak tűnk, úgyhogy beállítottam a szokásos 20 - as értéket a cache - nek, ez azonnali jelentős javulást eredményezett. Arra gyanakodtam, hogy a sorszámok generálása, a sorszám cache feltöltése gátolja a párhuzamosan futó feldolgozó szálakat. Tovább kísérleteztem a cache méret növelésével és azt tapasztaltam, hogy egészen 100000 - es cache méretig újabb jelentős sebesség javulásokat tud előidézni a cache méret emelése. Érzékeltetés képen, a konkrét feldolgozásom végrehajtási ideje két és fél óráról 15 - percre csökkent le a megfelelő cache érték beállítása után.

Egy szó mint száz, adattárház környezetben a szekvenciák cache méretét másként kell méretezni, érdemes leellenőrizni a beállításainkat, mert jelentős gátjai lehetnek a gyors, párhuzamos végrehajtásnak.