Mi is az SQL Plan Baseline?
Oracle Standard Edition (SE) környezetben az adatbázis teljesítményhangolása sokszor igazi kihívást jelent. Míg Enterprise Edition alatt kényelmesen nyúlhatunk a Diagnostic és Tuning Pack nyújtotta eszközökhöz, addig SE-ben ezek a kényelmi funkciók licencelési okokból nem elérhetőek.
Azonban van egy kiváló és gyakran alulértékelt eszköz a kezünkben, ami Standard Edition esetén is használható a végrehajtási tervek (execution plan-ek) rögzítésére: ez pedig az SQL Plan Baseline (SQL Plan Management).
Ebben a bejegyzésben egy klasszikus problémára mutatok be egy működő megoldást: Mi a teendő, ha egy lekérdezés a teszt/fejlesztői adatbázison tökéletesen fut, de az éles környezetben az Optimizer valamiért egy teljesen más, rossz plan-t választ neki?
A megoldás: áthozzuk a jól teljesítő plan-t az egyik adatbázisból, betöltjük a cél adatbázisba, és fixáljuk, hogy az Optimizer garantáltan ezt használja a jövőben.
A sikeres másolás és működés feltételei
Mielőtt belevágunk a technikai lépésekbe, fontos tisztázni, hogy az áthozott Baseline csak akkor fog működni a cél adatbázison, ha az alábbi feltételek hiánytalanul teljesülnek:
- optimizer_use_sql_plan_baselines paraméter: Ennek az initora / spfile paraméternek TRUE értéken kell lennie a cél adatbázison (ez az alapértelmezett beállítás, de érdemes leellenőrizni a show parameter optimizer_use_sql_plan_baselines paranccsal).
- Adatbázis verzió egyezés: Ideális esetben a forrás és a cél adatbázis verziója (és patch szintje) megegyezik. Egy régebbi verzióból újabba történő mozgatás általában működik, de visszafelé kompatibilitási problémák léphetnek fel.
- Objektumok fizikai megléte: A cél adatbázison is léteznie kell minden érintett táblának, nézetnek, és ami a legfontosabb: az indexeknek, amiket a jó plan használni akar. Ha a teszt rendszeren van egy index, ami az élesről hiányzik, a Baseline nem fog tudni reprodukálódni.
- Pontos SQL szöveg egyezés: Az SQL utasítás szövegének (SQL_TEXT) karakterre pontosan meg kell egyeznie a két rendszeren. A szóközök, sortörések és a kisbetű/nagybetű eltérések is számítanak!
- Azonos parsing schema: A lekérdezést futtató felhasználónak (és a default sémának) egyeznie kell, hogy a táblanevek feloldása ugyanazokra az objektumokra mutasson.
A másolás folyamata lépésről lépésre
(Megjegyzés: Az alábbi kódblokkokban szereplő konkrét azonosítókat – mint például az SQL_ID, SQL_HANDLE, PLAN_NAME – minden esetben cseréld ki a te saját rendszeredből kinyert valós értékekre!)
A folyamatot két részre bontjuk: mit kell csinálnunk a forrás (pl. teszt) adatbázison, és mit a cél (pl. éles) adatbázison.
1. Műveletek a Forrás adatbázison (ahol a jó plan található)
Először is keressük ki a kívánt lekérdezés SQL_ID-ját (például a V$SQL nézetből), majd töltsük be a memóriából (Cursor Cache) egy új Baseline-ba:
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'f9zrm235uxz05' -- FIGYELEM: Cseréld ki a saját lekérdezésed SQL_ID-jára!
);
DBMS_OUTPUT.put_line('Betöltött plan-ek száma: ' || l_plans_loaded);
END;
/
Ellenőrizzük, hogy elkészült-e a Baseline, és jegyezzük fel a PLAN_NAME és SQL_HANDLE értékeket, mert ezekre a későbbiekben szükségünk lesz:
SELECT plan_name, sql_handle, sql_text, enabled, accepted, fixed, reproduced FROM dba_sql_plan_baselines WHERE created > sysdate-1;
Hogy az Optimizer a cél rendszeren (és itt is) mindenképpen ezt a tervet preferálja, "Fixáljuk" (rögzítsük) a Baseline-t. Az imént lefuttatott lekérdezésből kapott értékeket itt kell felhasználnunk:
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_386dcefdd6d9ed0e', -- FIGYELEM: Cseréld ki a saját sql_handle értékedre!
plan_name => 'SQL_PLAN_3hvffzrbdmv8f7e560d95', -- FIGYELEM: Cseréld ki a saját plan_name értékedre!
attribute_name => 'fixed',
attribute_value => 'YES'
);
DBMS_OUTPUT.put_line('Módosított plan-ek: ' || l_plans_altered);
END;
/
A Baseline-ok közvetlenül nem exportálhatók, ezért létre kell hoznunk egy úgynevezett "staging" (átmeneti) táblát. Ebben a példában egy BASELINE_MOVE nevű felhasználóhoz hozzuk létre a táblát (Természetesen itt tetszőleges sémát, tábla nevet és táblateret is választhatsz):
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
table_owner => 'BASELINE_MOVE',
tablespace_name => 'USERS'
);
END;
/
Csomagoljuk (Pack) bele a fixált Baseline-unkat az imént létrehozott staging táblába (itt is a saját azonosítóinkat kell megadni):
my_plans NUMBER;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
table_owner => 'BASELINE_MOVE',
enabled => 'yes',
plan_name => 'SQL_PLAN_3hvffzrbdmv8f7e560d95', -- FIGYELEM: Cseréld ki a saját plan_name értékedre!
sql_handle => 'SQL_386dcefdd6d9ed0e' -- FIGYELEM: Cseréld ki a saját sql_handle értékedre!
);
DBMS_OUTPUT.put_line('Csomagolt plan-ek: ' || my_plans);
END;
/
2. Az adatok átmozgatása (Export / Import)
A staging táblát át kell vinnünk a cél adatbázisba. Ezt megtehetjük Data Pump (expdp/impdp) segítségével is, de ha egyszerűbb (vagy nem férünk hozzá a szerver fájlrendszeréhez), a jól bevált hagyományos exp/imp kliens parancsok is tökéletesek – arra figyeljünk, hogy az export/import kliens verziója lehetőleg egyezzen az adatbázis verziójával.
Operációs rendszer parancssorban (Forrás oldal):
Operációs rendszer parancssorban (Cél oldal):
3. Műveletek a Cél adatbázison (ahol a rossz plan fut)
Miután az import lezajlott, jelentkezzünk be a cél adatbázisba. Érdemes megnézni, vannak-e már korábbi Baseline-ok:
Csomagoljuk ki (Unpack) a Baseline-t az importált staging táblából az adatbázis Data Dictionary-jébe:
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'SPM_STAGETAB',
table_owner => 'BASELINE_MOVE'
);
DBMS_OUTPUT.put_line('Kicsomagolt plan-ek: ' || l_plans_unpacked);
END;
/
Ellenőrizzük, hogy a Baseline sikeresen létrejött-e és aktív-e az új környezetben:
FROM dba_sql_plan_baselines
WHERE created > sysdate-1;
Fontos: A REPRODUCED oszlop értéke YES kell, hogy legyen. Ez jelenti azt, hogy az Optimizer sikeresen újra tudta építeni az áthozott tervet a cél adatbázis meglévő objektumai és indexei alapján.
Ha a feltételek adottak voltak, és a lekérdezés legközelebb lefut, az adatbázis már ezt, a rögzített Baseline-t fogja használni!
Mit tegyünk, ha mégsem vált be? (Rollback)
Ha bármilyen oknál fogva a Baseline problémát okoz, vagy mégsem hozza a várt teljesítményt az adott rendszeren, egyszerűen kidobhatjuk a rendszerből a következő PL/SQL blokk futtatásával:
DECLARE
drop_result PLS_INTEGER;
BEGIN
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_386dcefdd6d9ed0e', -- FIGYELEM: Cseréld ki a saját sql_handle értékedre!
plan_name => 'SQL_PLAN_3hvffzrbdmv8f7e560d95' -- FIGYELEM: Cseréld ki a saját plan_name értékedre!
);
DBMS_OUTPUT.put_line('Törölt plan-ek száma: ' || drop_result);
END;
/
Remélem, ez a kis útmutató sokatoknak segít a Standard Edition alatti teljesítményhangolás rögös útján. Ha kérdésetek van, vagy elakadtatok a folyamat közben, tegyétek fel kommentben!
Segítségre lenne szükséged?
Szabadúszó, vizsgázott Oracle szakértőként szívesen segítek Oracle adatbázis hangolással, tuningal kapcsolatban is.
Oracle DBA Szolgáltatásaim a következőket tartalmazzák:
- Telepítés és konfigurálás
- Adatbázis migrálás
- Hibaelhárítás és optimalizálás
- Tanácsadás és oktatás
Vedd fel velem a kapcsolatot e-mailben a palffy.peter@oracle-szakerto.hu címen, ha bármilyen kérdésed van, vagy árajánlatot szeretnél kérni.
Segítek elérni az Oracle adatbázisokkal kapcsolatos céljaid!
