PL-SQLDeveloper használat sql optimalizáció

Az Oracle adatbázis SQL optimalizálása kulcsfontosságú a teljesítmény növeléséhez, különösen nagy adathalmazok és komplex lekérdezések esetén. Az Oracle hint-ek és a SQL profilok két hatékony eszköz az SQL lekérdezések finomhangolására. Az alábbiakban részletesen bemutatom mindkettő működését, hatásait, korlátait, majd egy táblázatos összefoglalóval zárom a választ.

1. Oracle Hint-ek

Működés

Az Oracle hint-ek olyan direktívák, amelyeket az SQL lekérdezésbe ágyazunk, hogy befolyásoljuk az Oracle Cost-Based Optimizer (CBO) viselkedését. A hint-ek segítségével az adatbázis-motor számára pontos utasításokat adhatunk arra, hogyan hajtsa végre a lekérdezést, például milyen végrehajtási tervet (execution plan) használjon, milyen indexet preferáljon, vagy milyen join módszert alkalmazzon.

Szintaxis: A hint-eket a lekérdezésben egy speciális kommentben adjuk meg, közvetlenül a SELECT, INSERT, UPDATE vagy DELETE kulcsszó után. Példa:

---sql

SELECT /*+ INDEX(emp emp_idx) */ employee_id, first_name
FROM employees emp
WHERE employee_id = 100;

Ebben a példában az INDEX hint arra kéri az optimalizálót, hogy az emp_idx indexet használja az employees tábla lekérdezéséhez.

Főbb hint típusok:

Hozzáférési útvonalak: FULL (teljes tábla szkennelés), INDEX (index használata), INDEX_FFS (index gyors szkennelés).
Join módszerek: USE_NL (nested loop), USE_HASH (hash join), USE_MERGE (merge join).
Párhuzamos végrehajtás: PARALLEL (párhuzamos lekérdezés), NO_PARALLEL.
Optimalizációs célok: ALL_ROWS (teljes adatmennyiség optimalizálása), FIRST_ROWS(n) (első n sor gyors visszaadása).
Egyéb: LEADING (join sorrend meghatározása), ORDERED (táblák sorrendje a join-ban).

🔧 Tipikus hint típusok:

Hint	Funkció
`FULL(table)`	Teljes táblaszkennelést kér
`INDEX(table index)`	Megadott index használatát kéri
`USE_NL(table)`	Nested loop join használata
`USE_HASH(table)`	Hash join használata
`LEADING(table)`	Megadja a join sorrendet
`PARALLEL(table, n)`	Párhuzamos végrehajtás kérése
`MERGE(table)`	Merge join használata

Hatás

Teljesítményjavítás: A hint-ek segítségével kikényszeríthetjük a hatékonyabb végrehajtási tervet, ha az optimalizáló nem választja automatikusan a legjobb utat.
Rugalmas finomhangolás: Lehetővé teszik a lekérdezés specifikus körülményekhez igazítását (pl. adatmennyiség, indexek, hardver).
Gyors beavatkozás: Nem igényel séma- vagy adatbázis-struktúra módosítást.

Korlátozások

Statikus jelleg: A hint-ek fix utasítások, így ha az adatmennyiség vagy a statisztikák változnak, a hint elavulhat, és akár rontja is a teljesítményt.
Fenntartási teher: A hint-ek kódba ágyazása megnehezíti a karbantartást, különösen nagy rendszerekben.
Nem garantált hatás: Az optimalizáló figyelmen kívül hagyhatja a hint-eket, ha azok érvénytelenek vagy ellentmondásosak (pl. nem létező indexre hivatkoznak).
Szakértelem igénye: A helytelen hint-ek használata jelentősen ronthatja a lekérdezés teljesítményét.

Használati tanácsok

Csak indokolt esetben használjuk: Ha az optimalizáló rossz végrehajtási tervet választ, és a statisztikák naprakészek.
Tesztelés: Mindig teszteljük a hint-ek hatását különböző adatmennyiségeken és környezetekben.
Dokumentáció: Jól dokumentáljuk a hint-ek célját a kódon belül, hogy később érthető legyen a szándék.

Korlátok:

Rossz hint → rosszabb teljesítmény (több idő, erőforrás).
Version dependency: Új Oracle verzióban máshogy viselkedhet.
Nehéz karbantartani nagy rendszerekben.
Nem veszi figyelembe automatikusan a statisztikák változását.

2. SQL Profilok

Működés

Az SQL profilok az Oracle automatikus teljesítménynövelő eszközei, amelyeket az Oracle SQL Tuning Advisor generál.

Az SQL profil egy olyan metaadat-gyűjtemény, amely statisztikai információkat és javaslatokat tartalmaz a lekérdezés végrehajtási tervének javítására, anélkül, hogy magát a konkrét SQL kódot módosítanánk.

Tehát SQL Profile az Oracle optimalizálója által generált statisztikai vagy szelektivitási korrekciók halmaza egy adott SQL lekérdezéshez. Ezeket az SQL Tuning Advisor javasolhatja, és DBMS_SQLTUNE csomaggal alkalmazhatók.

✅ Működés:

Nem változtatja meg a SQL-t.
A háttérben finomhangolja a szelektivitási becsléseket és a költségmodellezést.
Hatására az optimalizáló jobb végrehajtási tervet választhat.

Korlátok:

Csak egy konkrét SQL statement-re vonatkozik (normál formázással).
Nem kényszerít végrehajtási tervet – csak javasol.
Nem exportálható könnyen más környezetbe (bár workaroundok léteznek).
Ha a statisztikák jelentősen változnak, újra kell generálni.

Létrehozás:
- Az SQL Tuning Advisor elemzi a lekérdezést, és javaslatokat tesz a végrehajtási terv javítására.
- Ha a javaslat elfogadásra kerül, az Oracle egy SQL profilt hoz létre, amely a lekérdezéshez kapcsolódik.
- A profil a DBA_SQL_PROFILES nézetben tárolódik, és a lekérdezés hash-értékéhez kötődik.
Példa SQL profil elfogadására:

---sql

BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'my_tuning_task');
END;

Tartalom: Az SQL profil tartalmazhat:
- Statisztikai korrekciókat (pl. jobb kardinalitásbecslések).
- Információkat a join sorrendről, indexhasználatról vagy párhuzamos végrehajtásról.
- Az optimalizáló számára "puha" javaslatokat, amelyeket az figyelembe vehet.

Hatás

Automatikus optimalizálás: Az SQL profil dinamikusan alkalmazkodik az adatbázis változásaihoz, mivel nem fix utasításokat tartalmaz, hanem statisztikai korrekciókat.
Karbantartásmentes: Nem igényel SQL kód módosítást, így könnyen kezelhető nagy rendszerekben.
Stabilitás: Segít fenntartani a jó végrehajtási tervet, még akkor is, ha az adatmennyiség vagy a statisztikák változnak.

Korlátozások

Nem minden esetben alkalmazható: Az SQL profilok csak akkor hatékonyak, ha a CBO rossz döntései statisztikai hibákból fakadnak.
Nem garantált javulás: Ha a lekérdezés strukturális problémákat tartalmaz (pl. rossz séma design), az SQL profil nem oldja meg a problémát.
Licencigény: Az SQL profilok használata az Oracle Enterprise Edition részeként az Oracle Tuning Pack licencet igényli.
Korlátozott hatókör: Nem helyettesíti a hint-eket olyan esetekben, ahol specifikus végrehajtási tervet kell kikényszeríteni.

Használati tanácsok

SQL Tuning Advisor használata: Rendszeresen futtassuk az SQL Tuning Advisort a problémás lekérdezések azonosítására.
Profilok monitorozása: Ellenőrizzük a DBA_SQL_PROFILES nézetet, hogy mely profilok aktívak, és szükség esetén frissítsük vagy töröljük őket.
Kombinálás más eszközökkel: Az SQL profilok jól működnek más optimalizációs eszközökkel, például indexekkel vagy particionálással.

Összehasonlíttás

Tulajdonság	Hint-ek	SQL Profilok
Működés	Direktívák az SQL kódban, amelyek közvetlenül befolyásolják a CBO-t.	Statisztikai metaadatok, amelyek finomhangolják a CBO döntéseit.
Hatás	Fix végrehajtási terv kikényszerítése, azonnali hatás.	Dinamikus, statisztikai alapú optimalizálás, hosszabb távú stabilitás.
Rugalmasság	Statikus, nem alkalmazkodik az adatváltozásokhoz.	Dinamikus, alkalmazkodik az adatbázis változásaihoz.
Karbantartás	Magas, a kód módosítása szükséges, elavulhat.	Alacsony, automatikus, nem igényel kódmódosítást.
Szakértelem igénye	Magas, mély adatbázis-ismeret szükséges a helyes hint kiválasztásához.	Közepes, az SQL Tuning Advisor automatikus javaslatokat ad.
Licencigény	Nincs, alap Oracle funkcionalitás.	Oracle Tuning Pack licenc szükséges (Enterprise Edition).
Korlátozások	Elavulhat, figyelmen kívül hagyható, karbantartási teher.	Nem minden lekérdezésre alkalmazható, nem helyettesíti a strukturális javításokat.
Használati esetek	Specifikus végrehajtási terv kikényszerítése, gyors beavatkozás.	Általános teljesítményjavítás, statisztikai hibák korrigálása.
Példa	/+ INDEX(emp emp_idx) /	DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'my_tuning_task')

📋 Összefoglaló táblázat: Oracle Hints vs SQL Profiles

Jellemző	Oracle Hints	SQL Profiles
Típus	Kézi utasítás az optimalizálónak	Automatikus, tanuló alapú korrekció
Hatás	Közvetlen (azonnal irányítja a CBO-t)	Közvetett (stat. alapú módosítás)
Láthatóság	SQL-ben benne van	Csak DBA-nak látható, metadata-ban tárolt
Alkalmazhatóság	Az adott SQL-ben	Az adott SQL ID-ra automatikusan
Karbantarthatóság	Nehézkes nagy rendszerekben	Automatizálható, jobban kezelhető
Garancia a végrehajtásra	Nincs (érvénytelen hint esetén elhagyja)	Nincs (csak tanács a CBO-nak)
Verziófüggőség	Igen	Igen
Tuning Advisor használja?	Nem (kézi)	Igen (automatikus ajánlás)
Kódot kell módosítani?	Igen	Nem
Használat tipikusan	Ad hoc tuning, tesztelés	Production tuning, hosszú távú optimalizálás

Ajánlások az SQL optimalizáláshoz

Először a statisztikákat ellenőrizd: Győződj meg róla, hogy az adatbázis statisztikái naprakészek (DBMS_STATS.GATHER_TABLE_STATS).
SQL Tuning Advisor használata: Először próbálj SQL profilt generálni, mert ez kevésbé invazív és dinamikusabb megoldás.
Hint-ek csak végső esetben: Használj hint-eket, ha a profil nem hoz eredményt, vagy pontosan tudod, milyen végrehajtási tervre van szükség.
Monitorozás és tesztelés: Mindig mérd a teljesítményt (EXPLAIN PLAN, SQL Trace, TKPROF) a változtatások előtt és után.
Kombinált megközelítés: Az SQL profilokat és hint-eket együtt is használhatod, ha szükséges, de figyelj az esetleges konfliktusokra.

Most nézzük tovább az Oracle SQL optimalizálási eszközeit, különösen a SQL Plan Management (SPM) részeként elérhető:

SQL Plan Baselines
SQL Plan Management maga

A végén csatolok egy összefoglaló táblázatot az eddig tárgyalt négy technikáról (Hints, SQL Profiles, SQL Plan Baselines, SPM) verziókkal és jellemzőkkel.

🧠 3. SQL Plan Baselines – Végrehajtási terv rögzítés

📌 Mi ez?

Az Oracle SQL Plan Baseline lehetővé teszi, hogy megbízható végrehajtási terveket rögzítsünk egy SQL lekérdezéshez, és megakadályozzuk, hogy az optimalizáló más, potenciálisan rosszabb tervet válasszon.

Ez különösen hasznos:

verzióváltások után,
statisztikai változásoknál,
változó környezeti feltételek esetén.

✅ Működés:

Amikor egy SQL-t először hajtunk végre, az Oracle létrehozhat egy baseline-t (ha engedélyezve van az SPM).
Ez a baseline tartalmazza az adott lekérdezéshez tartozó jóváhagyott végrehajtási terveket.
Csak ezek a jóváhagyott tervek hajthatók végre, hacsak nincs evolúció (új terv validálása és engedélyezése).

Alapfogalmak:

Baseline: Egy jóváhagyott végrehajtási terv, amelyet az adatbázis tárol és használhat a lekérdezésekhez. A baseline-ek tartalmazzák a végrehajtási tervet és a hozzá kapcsolódó statisztikai információkat.
SQL Plan History: Az adott SQL lekérdezéshez tartozó összes végrehajtási terv gyűjteménye, amelyeket az SPM tárol (elfogadott és nem elfogadott tervek egyaránt).
Enabled/Accepted: Egy baseline lehet engedélyezett (enabled) és elfogadott (accepted). Csak az elfogadott baseline-eket használja a CBO.
Fixed: Egy fix baseline-t az optimalizáló előnyben részesít más tervekkel szemben, így garantálja annak használatát.

Folyamat:

Rögzítés (Capture): Az SPM automatikusan vagy manuálisan rögzíti a végrehajtási terveket. Az automatikus rögzítés az OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES paraméter bekapcsolásával történik.
Tárolás: A rögzített tervek a DBA_SQL_PLAN_BASELINES nézetben tárolódnak, és az SQL lekérdezéshez kapcsolódnak a signature (hash érték) alapján.
Értékelés (Evaluation): Az SPM összehasonlítja az új végrehajtási terveket a meglévő baseline-ekkel, és csak akkor fogad el új tervet, ha az bizonyítottan jobb (pl. alacsonyabb költségű vagy gyorsabb).
Evolúció (Evolution): Az SPM lehetővé teszi az új, potenciálisan jobb tervek fokozatos bevezetését az DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE eljárással, amely teszteli az új terveket, mielőtt elfogadja őket.
Karbantartás: A baseline-eket törölhetjük, módosíthatjuk vagy fixálhatjuk a DBMS_SPM csomag segítségével.

🔧 Használat:

---sql
-- Elfogadunk egy végrehajtási tervet baseline-ként
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123xyz');

🕒 Elérhető verzió:

Oracle 11g óta elérhető.
Oracle 12c+ és 19c/21c-ben továbbfejlesztve (pl. automatikus evolúció).

⚠️ Korlátai:

Baseline karbantartás szükséges.
Növeli a tárhasználatot (metadata).
Ha túl sok baseline van egy SQL-hez, az lassítja a parse fázist.

🔐 4. SQL Plan Management (SPM) – Tervezett stabilitás

📌 Mi ez?

Az SQL Plan Management egy Oracle technológia, amely célja, hogy stabil és megbízható teljesítményt biztosítson az SQL lekérdezések számára az idő múlásával. Ez egy keretrendszer, amely a következőket foglalja magába:

SQL Plan Baselines
Evolúciós folyamat (automatikus vagy manuális)
Plan capture és választás szabályai

✅ Működés:

SPM automatikusan rögzíti az SQL-eket és azok terveit.
Alapértelmezetten csak elfogadott baseline hajtható végre.
Az új tervek csak tesztfázis után válhatnak elfogadottá.

⚙️ Beállítások:

---sql
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

🔍 SQL Plan evolúció:

Az Oracle 12c+ verziók automatikus evolúciót támogatnak (DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE).
A tuning advisor és automatikus background task is végzi.

🕒 Elérhető verzió:

Alap: Oracle 11g
Automatikus evolúció és profil integráció: Oracle 12c+

⚠️ Korlátai:

Bevezetése komplexitást jelenthet.
Nem végtelenül skálázható: baseline menedzsment fontos.
Lassíthatja a parse időt, ha túl sok baseline van.

🧾 Végső táblázatos összefoglaló: Oracle SQL optimalizálási eszközök

Jellemző	Oracle Hints	SQL Profiles	SQL Plan Baselines	SQL Plan Management (SPM)
Bevezetés verzió	Mindig elérhető	Oracle 10g	Oracle 11g	Oracle 11g
Típus	Kézi, SQL szintű	Automatikus, statisztikai	Rögzített végrehajtási terv	Keretrendszer: baseline + szabályok
Hatás	Azonnali irányítás	Szelektivitás korrekció	Csak jóváhagyott terv fut	Stabil teljesítmény hosszú távon
Kódmódosítás szükséges?	Igen	Nem	Nem	Nem
DBA eszköz szükséges?	Nem	Igen	Igen	Igen
Automatizálható?	Nem igazán	Igen (Tuning Advisor)	Igen (evolúcióval)	Igen (12c+ automatikus evolúció)
Statisztika változás hatása	Nem érzékeny	Igen (új profilt igényel)	Rögzített → stabil marad	Stabil marad, amíg új baseline nincs elfogadva
Használat célja	Teszt, gyors tuning	Finomhangolás	Stabil, bevált terv zárolása	Központi teljesítménykontroll
Export/import lehetőség	N/A	Bonyolult	Igen (`DBMS_SPM`)	Igen

Készítettem egy gyakorlati példát, amely végigvezet egy egyszerű SQL lekérdezésen, és bemutatja a következő Oracle optimalizálási eszközöket:

HINT használata
SQL PROFILE létrehozása
SQL PLAN BASELINE rögzítése
SQL PLAN MANAGEMENT beállítása és használata

A példát egy employees tábla alapján készítem, amely tartalmazza a következő mezőket:

---sql
CREATE TABLE employees (
  emp_id     NUMBER PRIMARY KEY,
  name       VARCHAR2(100),
  dept_id    NUMBER,
  salary     NUMBER
);

CREATE INDEX emp_dept_idx ON employees(dept_id);

Tegyük fel, hogy a következő lekérdezésünk van, amit szeretnénk optimalizálni:

---sql
SELECT * FROM employees WHERE dept_id = 10;

🛠 1. Hint használata – kézi optimalizáció

Cél: Index használatának kényszerítése.

---sql
SELECT /*+ INDEX(employees emp_dept_idx) */ * 
FROM employees 
WHERE dept_id = 10;

🔍 Ezzel megmondjuk az Oracle-nek, hogy a emp_dept_idx indexet használja.
⚠️ Ha a hint nem valid (pl. rossz indexnév), az Oracle figyelmen kívül hagyja.

🤖 2. SQL Profile létrehozása – SQL Tuning Advisor segítségével

Lépések:

Először létrehozunk egy tuning task-et:

sql
BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM employees WHERE dept_id = 10',
    user_name => 'HR',
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'emp_tune_task'
  );
END;
/

Lefuttatjuk a tuningot:

---sql
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'emp_tune_task');

Megnézzük az ajánlásokat:

---sql
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('emp_tune_task') FROM dual;

Ha a rendszer ajánl SQL PROFILE-t, elfogadhatjuk:

---sql
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'emp_tune_task', name => 'emp_profile');

🧠 Ekkor az Oracle belsőleg finomítja a költségmodellezést (szelektivitási becslést), de nem módosítja az SQL-t.

📦 3. SQL Plan Baseline létrehozása

Cél: A jelenlegi, jól teljesítő végrehajtási terv rögzítése.

Engedélyezzük a baseline-ok automatikus rögzítését (ha nem aktív):

---sql
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

Végrehajtjuk a lekérdezést (Oracle ekkor létrehozhat baseline-t).
Alternatív mód: baseline manuális betöltése a cache-ből:

---sql
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'az_sql_sql_id_je', 
    plan_hash_value => NULL);
END;
/

Megnézhetjük a baseline-t:

---sql
SELECT * FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT * FROM employees%';

🔐 4. SQL Plan Management (SPM) használata

Beállítás:

---sql
-- Globálisan engedélyezve:
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

Evolúció (új terv elfogadása baseline-hoz):

---sql
DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_XXXXXX',
    plan_name  => NULL,
    time_limit => 60,
    verify     => 'YES',
    commit     => TRUE);
  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

Az SPM gondoskodik arról, hogy csak validált tervek fussanak, még akkor is, ha az Oracle új tervet találna, de az lassabb lenne.

🧾 Összegzés – egy SQL lekérdezés és az optimalizációs lépések:

Eszköz	Hatás	SQL módosítást igényel?	Automatizálható?
Hint	Index vagy join típus kényszerítése	Igen	Nem
SQL Profile	Szelektivitási becslések pontosítása (jobb terv)	Nem	Igen
SQL Plan Baseline	Csak a jóváhagyott végrehajtási terv használható	Nem	Igen
SPM	Stabil végrehajtási terv kontroll hosszú távon, evolúcióval	Nem	Igen

Most modellezzük le részletesen, hogyan tudjuk vizsgálni és összehasonlítani egy SQL lekérdezés végrehajtási tervét (execution plan) az Oracle optimalizálási technikák használata előtt és után.

Két módszert mutatok be:

EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY
SET AUTOTRACE ON SQL*Plus-ban vagy SQLcl-ben

🎯 Alap lekérdezés

---sql
SELECT * FROM employees WHERE dept_id = 10;

Tegyük fel, hogy a dept_id mezőre van egy index:

---sql
CREATE INDEX emp_dept_idx ON employees(dept_id);

🔍 1. EXPLAIN PLAN – optimalizáció előtt (baseline, profile, hint nélkül)

A. Parancs:

---sql
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept_id = 10;

Ezután nézzük meg a tervet:

---sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🧾 Példa kimenet:

---pgsql
-------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  1000 | 13000 |    12  (10)| 00:00:01 |
|*  1 | TABLE ACCESS FULL | EMPLOYEES      |  1000 | 13000 |    12  (10)| 00:00:01 |
-------------------------------------------------------------------------------------

🔴 FULL TABLE SCAN → az Oracle nem használja az indexet.

✅ 2. Hint használatával (kézzel irányítjuk az optimalizálót)

---sql
EXPLAIN PLAN FOR
SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📄 Várható terv:

---pgsql
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   10  |   130 |     3  (0) | 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |   10  |   130 |     3  (0) | 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_IDX   |   10  |       |     1  (0) | 00:00:01 |
-----------------------------------------------------------------------------------------

🟢 Az indexet már használja → gyorsabb végrehajtás.

🧠 3. SQL Profile vagy Baseline után

Miután a tuning advisor javasolt egy SQL PROFILE-t, vagy létrehoztál egy baseline-t, a futtatás után így vizsgálhatod:

---sql
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Az Oracle figyelembe veszi a profilokat és baseline-okat is, így a terv automatikusan változhat:

📄 Várható output SQL Profile vagy Baseline után:

---pgsql
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   10  |   130 |     3  (0) | 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |   10  |   130 |     3  (0) | 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_IDX   |   10  |       |     1  (0) | 00:00:01 |
-----------------------------------------------------------------------------------------

🧩 A tuning hatására a rendszer magától választotta a jobb tervet, még ha nem is írtunk hint-et.

⚙️ 4. AUTOTRACE – gyors teszt SQL*Plus-ban vagy SQLcl-ben

---sql
SET AUTOTRACE ON
SELECT * FROM employees WHERE dept_id = 10;

Ez lefuttatja a lekérdezést ÉS mutatja az execution plan-t + statisztikákat:

Példa kimenet:

---pgsql
Execution Plan
----------------------------------------------------------
Plan hash value: 2934858392

----------------------------------------------------------
| Id  | Operation                   | Name           |
----------------------------------------------------------
|   0 | SELECT STATEMENT            |                |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |
|*  2 |   INDEX RANGE SCAN          | EMP_DEPT_IDX   |
----------------------------------------------------------

Statistics
----------------------------------------------------------
     5  recursive calls
     0  db block gets
    12  consistent gets
     0  physical reads

🧾 Összegzés – mit látunk a végrehajtási tervben?

Elem	Jelentés
`TABLE ACCESS FULL`	Teljes táblaszken → lassabb
`INDEX RANGE SCAN`	Index alapján keresés → gyorsabb
`PLAN_HASH_VALUE`	Azonosító, hogy két terv azonos-e
`Cost` / `Rows` / `Bytes`	Oracle becslése a lekérdezés erőforrásigényére
`*` a predicate sorban	Szűrés (WHERE feltétel) alkalmazva

az SQL optimalizálási lépések automatizálása valóban kulcsfontosságú lehet, főleg nagy rendszerekben vagy gyakran változó SQL-ek esetén.

Ebben a válaszban bemutatom, hogyan lehet a következőket automatizálni:

🎯 Célfeladatok automatizálása:

Feladat	Automatizálható?	PL/SQL példa	Python példa
SQL Tuning Task létrehozása + SQL Profile elfogadása	✅	✅	✅ (cx_Oracle)
Execution Plan kiírása és naplózása	✅	✅	✅
SQL Baseline létrehozása	✅	✅	✅
SPM evolúció indítása	✅	✅	✅

🧪 1. PL/SQL automatizálás – SQL Profile példával

---plsql
DECLARE
  l_sql_text   CLOB := 'SELECT * FROM employees WHERE dept_id = 10';
  l_task_name  VARCHAR2(100) := 'auto_tune_emp';
BEGIN
  -- 1. Tuning task létrehozása
  DBMS_SQLTUNE.CREATE_TUNING_TASK (
    sql_text   => l_sql_text,
    scope      => 'COMPREHENSIVE',
    time_limit => 60,
    task_name  => l_task_name);

  -- 2. Futtatás
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);

  -- 3. Eredmény kiírása
  DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(l_task_name));

  -- 4. SQL PROFILE elfogadása (ha ajánlott)
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => l_task_name, name => 'emp_profile', force_match => TRUE);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('HIBA: ' || SQLERRM);
END;
/

📌 Ezt beütemezheted pl. DBMS_SCHEDULER vagy cron + SQL*Plus segítségével.

🐍 2. Python (cx_Oracle) példa – SQL tuning automatikusan

Telepítés:

---bash
pip install cx_Oracle

Példa kód:

---python
import cx_Oracle

sql = "SELECT * FROM employees WHERE dept_id = 10"
task_name = "emp_auto_task"

conn = cx_Oracle.connect("hr/password@//localhost/XEPDB1")
cur = conn.cursor()

# 1. Create tuning task
cur.execute("""
    BEGIN
      DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text   => :sql,
        scope      => 'COMPREHENSIVE',
        time_limit => 60,
        task_name  => :task);
    END;
""", sql=sql, task=task_name)

# 2. Execute tuning task
cur.execute("BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:task); END;", task=task_name)

# 3. Get tuning report
cur.execute("SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual", task=task_name)
report = cur.fetchone()[0]
print(report)

# 4. Accept SQL Profile
try:
    cur.execute("BEGIN DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => :task, name => 'emp_profile'); END;", task=task_name)
    print("SQL Profile accepted.")
except Exception as e:
    print("Profile already exists or not recommended.")

conn.commit()
cur.close()
conn.close()

Ezt egy Python szkriptként vagy ütemezett cron/batch feladatként is futtathatod.

📦 3. SQL Plan Baseline automatizálása (PL/SQL)

---plsql
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'gxy7x32fkdpqs',  -- előre ismert SQL ID
    plan_hash_value => NULL);
END;
/

⚙️ 4. Evolúció (SPM auto-validáció új tervhez)

---plsql
DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_xxxxx',  -- DBA_SQL_PLAN_BASELINES alapján
    time_limit => 60,
    verify     => 'YES',
    commit     => TRUE);

  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

🗂 Automatizálási Összefoglaló Táblázat

Funkció	PL/SQL támogatás	Python támogatás	Verzió
SQL Tuning Task létrehozása	✅	✅	10g+
SQL Profile elfogadása	✅	✅	10g+
SQL Plan Baseline rögzítése	✅	✅	11g+
Baseline evolúció	✅	✅	11g+
Execution Plan kinyerése	✅	✅	9i+
DBMS_OUTPUT / naplózás	✅	✅	8i+
SQL_ID alapú cursor cache vizsgálat	✅	✅	11g+

Ha szeretnéd, össze tudok állítani egy teljes Python + PL/SQL pipeline példát, amely:

Lekérdezi az aktív SQL-eket (v$sql),
Elemzi őket tuningra,
Elfogad SQL Profile-t vagy Baseline-t.

Most bemutatok egy Oracle SQL optimalizálási folyamatot automatizáló ütemezhető szkriptet, amely Python és/vagy PL/SQL segítségével:

🎯 Cél:

Automatizáltan elvégezni egy lekérdezés SQL tuningját.
Elfogadni a javasolt SQL Profile-t (ha van).
Kinyerni és naplózni az execution plan-t.
Mindezt naponta (vagy tetszőleges időzítéssel).

🧩 Megoldás 1: Python szkript ütemezése `cron` vagy `Task Scheduler` segítségével

📄 Fájl neve: `sql_tuning_runner.py`

----python
import cx_Oracle
from datetime import datetime

sql_text = "SELECT * FROM employees WHERE dept_id = 10"
task_name = "auto_tune_emp"
profile_name = "emp_profile"

conn = cx_Oracle.connect("hr/password@//localhost/XEPDB1")
cur = conn.cursor()

log_file = f"/tmp/sql_tuning_log_{datetime.now().strftime('%Y%m%d')}.log"
with open(log_file, "w") as f:

    try:
        # 1. Létrehozás
        cur.execute("""
            BEGIN
              DBMS_SQLTUNE.CREATE_TUNING_TASK(
                sql_text   => :sql,
                scope      => 'COMPREHENSIVE',
                time_limit => 60,
                task_name  => :task);
            END;
        """, sql=sql_text, task=task_name)

        # 2. Futtatás
        cur.execute("BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:task); END;", task=task_name)

        # 3. Jelentés lekérése
        cur.execute("SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual", task=task_name)
        report = cur.fetchone()[0]
        f.write("=== SQL TUNING REPORT ===\n")
        f.write(report + "\n")

        # 4. SQL Profile elfogadása
        try:
            cur.execute(f"""
                BEGIN
                  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => :task, name => :pname, force_match => TRUE);
                END;
            """, task=task_name, pname=profile_name)
            f.write(f"\n[✓] SQL Profile '{profile_name}' accepted.\n")
        except Exception as e:
            f.write(f"\n[!] SQL Profile not accepted: {str(e)}\n")

    except Exception as e:
        f.write(f"\n[!] ERROR: {str(e)}\n")

    finally:
        conn.commit()
        cur.close()
        conn.close()

🕒 Linuxon ütemezés `cron`-nal:

----bash
crontab -e

Tegyük be pl. naponta 2:00-kor:

---ruby
0 2 * * * /usr/bin/python3 /home/user/sql_tuning_runner.py

🪟 Windows ütemezés:

Használj Task Schedulert, és állítsd be a szkript futtatását pl. python sql_tuning_runner.py paranccsal, egy adott időpontra naponta.

🧩 Megoldás 2: PL/SQL ütemezés Oracle belső időzítővel

Használhatod az DBMS_SCHEDULER-t az Oracle-en belül:

---plsql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'auto_tuning_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        l_task_name VARCHAR2(100) := 'plsql_tune_task';
        l_sql_text  CLOB := 'SELECT * FROM employees WHERE dept_id = 10';
      BEGIN
        DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => l_sql_text, task_name => l_task_name);
        DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
        DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => l_task_name, name => 'emp_profile', force_match => TRUE);
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Napi automatikus SQL tuning feladat.'
  );
END;
/

🧾 Naplózás – javasolt kimenet formátum

Az output fájlban vagy az Oracle USER_SCHEDULER_JOB_RUN_DETAILS nézetében követheted a futásokat.

🧠 Tuning Jelentés ellenőrzése

A tuning report kiemeli, ha az Oracle javasol:

Index létrehozást
SQL Profile elfogadását
Hibatűrési optimalizálást
Join újrasorrendezését

🔚 Összegzés

Megoldás	Technológia	Időzítés	Naplózás	Profil használat
Python szkript + cron	`cx_Oracle`	OS szinten	fájlba (`.log`)	✅
PL/SQL blokk + DBMS_SCHEDULER	Oracle belső	Oracle szinten	Oracle nézet	✅

IT, BI, DWH, DM, AI