Oracle adatbázis SQL profile alapok

Az Oracle adatbázisban az SQL Profile egy olyan optimalizálási eszköz, amelyet a lekérdezések teljesítményének javítására használnak. Az EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE parancs egy konkrét SQL utasításhoz (sql_id alapján azonosítva) hoz létre egy SQL profilt manuálisan vagy automatikusan, ha azt egy tuning folyamat részeként hívják meg. Máskép mondva az SQL Profile az Oracle SQL Tuning Advisor egyik eszköze, amely az SQL végrehajtási tervét optimalizálja anélkül, hogy módosítani kellene magát a SQL lekérdezést vagy az adatbázis objektumokat (pl. indexeket).

📌 Főbb előnyei:
✅ Nem kell módosítani az SQL lekérdezést vagy az alkalmazás kódját.
✅ Segít a nem optimális végrehajtási tervet gyorsabbra cserélni.
✅ Automatikusan figyelembe veszi az aktuális adatbázis-statisztikákat.

Mi az SQL Profile?

Az SQL Profile egy metaadat-gyűjtemény, amelyet az Oracle Optimizer használ a lekérdezések végrehajtási tervének (execution plan) finomhangolására. Nem írja felül az SQL szövegét, hanem kiegészítő statisztikai információkat biztosít az Optimizer számára, például:

Jobb becslések a szelektivitásról ( selectivity).
Pontosabb kardinalitás (cardinality) előrejelzések.
Információk az oszlopok közötti korrelációkról.

Ezek az adatok segítenek az Optimizernek abban, hogy hatékonyabb végrehajtási tervet válasszon, például jobb indexhasználatot vagy optimális JOIN sorrendet.

Mire szolgál az EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE?

A DBMS_SQLTUNE.CREATE_SQL_PROFILE eljárás egy SQL Profile létrehozására szolgál egy megadott sql_id azonosítójú lekérdezéshez. Az sql_id egy egyedi azonosító, amelyet az Oracle minden SQL utasításhoz generál, és megtalálható például a V$SQL nézetben.

Főbb célok:

Teljesítmény javítása: Ha egy lekérdezés lassú vagy suboptimalis végrehajtási tervet használ, az SQL Profile segíthet jobb tervet generálni anélkül, hogy az SQL szövegét módosítani kellene.
Automatikus tuning részeként: Az Oracle SQL Tuning Advisor (pl. DBMS_SQLTUNE csomag használatával) javasolhat SQL Profile létrehozását, ha elemzése során azt találja, hogy a lekérdezés profitálhat belőle.
Stabilitás: Az SQL Profile használata stabilabb végrehajtási terveket biztosít, csökkentve a teljesítményingadozásokat (pl. statisztikák változása miatt).

Hogyan működik?

Előfeltételek:
- Az sql_id-t ismerned kell (pl. lekérdezheted a V$SQL vagy DBA_HIST_SQLSTAT nézetekből).
- A DBMS_SQLTUNE csomag futtatásához megfelelő jogosultságok szükségesek (pl. ADVISOR vagy DBA szerepkör).
- Az SQL Tuning Advisor előzetesen elemezhette a lekérdezést, és javasolhatott profilt.
Létrehozás:
--- sql

EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_id => 'your_sql_id');

Itt a 'your_sql_id' helyére az adott lekérdezés sql_id-jét kell beilleszteni (pl. '7x9kabcd1234').
Az eljárás létrehoz egy SQL Profile objektumot az adatbázisban, amelyet az Optimizer a következő futtatásoknál használhat.

Eredmény:
- A profil létrehozása után az Optimizer automatikusan figyelembe veszi azt a megadott sql_id-hez tartozó lekérdezések optimalizálásakor.
- A profilok megtekinthetők a DBA_SQL_PROFILES nézetben.

Példa

Tegyük fel, hogy van egy lassú lekérdezésed:

SELECT * FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;

Megkeresed az sql_id-t:
--- sql

SELECT sql_id, sql_text FROM V$SQL WHERE sql_text LIKE '%employees e%'; -- Tegyük fel, hogy az sql_id: '7x9kabcd1234'
Létrehozod az SQL Profilt:
--- sql

EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_id => '7x9kabcd1234');
Ellenőrzöd:
--- sql

SELECT name, status FROM DBA_SQL_PROFILES WHERE sql_id = '7x9kabcd1234';
A következő futtatásnál az Optimizer a profil adatait használja a jobb végrehajtási tervhez.

Automatikus generálás kontextusa

Az SQL Profile általában nem "automata" abban az értelemben, hogy magától létrejönne. Az automatikus generálás akkor lép életbe, ha:

Az Automatic SQL Tuning feladat fut (pl. DBMS_AUTO_SQLTUNE), és az elemzés során javasolja egy profil létrehozását.
Az adminisztrátor elfogadja a Tuning Advisor ajánlásait:
--- sql

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'my_tuning_task');

A CREATE_SQL_PROFILE parancs inkább manuális lépés, de egy automatizált folyamat részeként is meghívható.

Data Vault kapcsolat

A Data Vault környezetben, ahol nagy mennyiségű adatot mozgatunk (pl. HUB, LINK, SAT táblák betöltése), az SQL Profile különösen hasznos lehet az összetett JOIN-okat és aggregációkat tartalmazó lekérdezések optimalizálására. Például egy adatpiacra (Data Mart) történő adatkinyerés során a profil javíthatja a teljesítményt anélkül, hogy a betöltési logikát át kellene írni.

Összegzés

Az EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_id => 'your_sql_id') parancs egy SQL Profile létrehozására szolgál, amely finomhangolja az adott sql_id-hez tartozó lekérdezés végrehajtási tervét az Oracle Optimizer számára. Fő célja a teljesítmény javítása és a stabilitás növelése, különösen komplex vagy rosszul teljesítő SQL-ek esetén.

Ez a funkcio az oracle 10.-től létezik

Oracle 10g (10.1): Ebben a verzióban jelent meg először a DBMS_SQLTUNE csomag, amely lehetővé tette az SQL Profile-ok létrehozását és kezelését. Az SQL Profile a Tuning Advisor ajánlásainak részeként jött létre, hogy javítsa az Optimizer becsléseit.

SQL Profile haladó

SQL Profile manuális létrehozása SQL Tuning Advisorral

Indítsd el az SQL Tuning Advisor-t egy adott SQL_ID-hez:

--- sql

DECLARE
  l_tuning_task VARCHAR2(30);
BEGIN
  l_tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'your_sql_id',
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'my_sql_tuning_task');
END;
/

Futtasd az elemzést:

--- sql

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_sql_tuning_task');

Nézd meg az ajánlásokat:

---sql

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;

Ha az ajánlások között szerepel SQL Profile, alkalmazd:

--- sql

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'my_sql_tuning_task');

2.3. SQL Profile ellenőrzése és törlése

📌 Meglévő SQL Profile-ok listázása:

--- sql

SELECT NAME, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

📌 Egy adott SQL Profile törlése:

--- sql

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('your_profile_name');

3. SQL Profile vs. SQL Plan Baseline vs. SQL Patch

Az Oracle többféle SQL optimalizálási mechanizmust kínál:

Mechanizmus	Verzió	Működési elv	Módosítja a SQL-t?	Automatikus?
SQL Profile	10g+	Statisztikai korrekciókat alkalmaz az optimalizálónál.	❌	✅
SQL Plan Baseline	11g+	Meghatározza, mely végrehajtási tervet fogadja el az Oracle.	❌	✅
SQL Patch	12c+	Fixál egy végrehajtási tervet speciális módon.	❌	🚫

Mikor érdemes SQL Profile-t használni?

Ha egy lekérdezés rossz végrehajtási tervet választ.
Ha a statisztikai adatok frissítése után lassabb lett egy SQL.
Ha nincs lehetőség az SQL módosítására az alkalmazásban.

Mikor NEM jó SQL Profile?

Ha egy SQL már stabil és optimalizált.
Ha egy SQL Plan Baseline jobban illik az adott problémára.

IT, BI, DWH, DM, AI

Keresés ebben a blogban