Teszten gyors, élesen lassú? Van megoldás! SQL Plan Baseline másolása adatbázisok között Oracle Standard Edition-ben.

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:

DECLARE
  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 count(*) FROM dba_sql_plan_baselines;
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:

DECLARE
  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):

BEGIN
  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):

DECLARE
  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):

exp BASELINE_MOVE@forras_db tables=SPM_STAGETAB file=SPM_STAGETAB.dmp log=exp_baseline.log

Operációs rendszer parancssorban (Cél oldal):

imp BASELINE_MOVE@cel_db tables=SPM_STAGETAB file=SPM_STAGETAB.dmp log=imp_baseline.log

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:

SELECT count(*) FROM dba_sql_plan_baselines;

Csomagoljuk ki (Unpack) a Baseline-t az importált staging táblából az adatbázis Data Dictionary-jébe:

SET SERVEROUTPUT ON
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:

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, reproduced 
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:

SET SERVEROUTPUT ON
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!