Optimalizace dotazu˚ a databázové transakce v Oracle Marek Rychlý Vysoké uˇcení technické v Brneˇ Fakulta informaˇcních technologií Ústav informaˇcních systému˚
Demo-cviˇcení pro IDS 22. dubna 2015
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
1 / 24
Obsah 1
Optimalizace plánu˚ vykonávání Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
2
Materializované pohledy Materializovaný pohled Vytvoˇrení materializovaného pohledu Použití materializovaného pohledu
3
Databázové transakce ACID a ovládáná databázové transakce Zamykání databázových objektu˚ ˇ dotazu˚ Optimalizace práce s databází bez zmeny
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
2 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Plán vykonávání dotazu (Execution Plan) SQL DML dotaz je po pˇrijetí db. serverem zpracován a proveden. Zpracování zahrnuje parsování, analýzu a pˇriˇrazení zdrojum ˚ dat. ˇ nejlepšího. Analýza zahrnuje tvorbu plánu˚ vykonávání a výber ˇ Plány vykonávání jsou sestaveny na základe: zpusobu ˚ použití tabulek a jejich sloupcu˚ (projekce, druhy spojení tabulek, predikáty, agregace, ˇrazení, atd.)
existujících indexu˚ (zvažuje se také typ a smysluplnost použítí indexu, ˚ nejen jejich existence)
pˇrítomnosti tzv. „hints“ v dotazu (tvurce ˚ dotazu pomocí nich pˇrikazuje, jak se dotaz má provést)
ˇ minulých úspešných plánu˚ provedení podobných dotazu˚ ˇ (plány jsou nejakou dobu uloženy v cache)
statistik nad existujícími daty v databázi i provedenými dotazy (poˇcet dat v tabulce, poˇcet ruzných ˚ hodnot ve sloupci tabulky, atd.) Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
4 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Zapojení optimalizátoru vykonávání dotazu˚
(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“ Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
5 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Komponenty optimalizátoru vykonávání dotazu˚
(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“ Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
6 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Transformace dotazu˚
pˇred generováním plánu˚ vykonávání je dotaz transformován ˇ dotaz na transformace rozdelí ˇ nekolik poddotazu˚ ˇ (pˇríklad vlevo s rozdelením podle OR)
transformace muže ˚ také nahradit dotaz cˇ i jeho cˇ ást (použité tabulky) dotazem na materializováný pohled (diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database
(pokud existuje takový pohled a je to povoleno)
Online Documentation 12cR1)“
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
7 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Odhad ceny vykonávání dotazu˚ ˇ nejlepšího plánu ohodnocení pro následný výber odhad ceny vychází z následujících údaju˚ selectivity – procento ze všech ˇrádku˚ dotaz cˇ i jeho cˇ ást vybírá cardinality – poˇcet ˇrádku˚ dotaz cˇ i jeho cˇ ást zpracovává ˇ dotazem cˇ i jeho cˇ ásti cost – použití I/O, CPU a pameti
(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
8 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
ˇ nejlepšího plánu vykonávání Generování a výber
1
ˇ pˇrístupové výber cesty k datum ˚
2
ˇ metody výber spojení tabulek
3
ˇ poˇradí výber spojení tabulek
(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“ Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
9 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Pˇrístupová cesta v plánu vykonávání (Access Path) pˇrímý pˇrístup k tabulkám (Heap-Organized Tables) Full Table Scans – procházení bez smysluplneˇ použitelného indexu Table Access by Rowid – dohledání po pruchodu ˚ indexem ˇ procentního vzorku z tabulky Sample Table Scans – výber
pˇrístup k tabulkám pˇres B-strom (B-Tree Index Access) Index Unique Scans – rovnost na sloupci s jedineˇcnými hodnotami Index Range Scans – test rozsahu cˇ i rovnost v sloupci s dupl. hod. Index Full Scans – výpis hodnot z indexovaných sloupcu˚ Index Fast Full Scans – výpis hodnot pouze ze sloupcu˚ indexu Index Skip Scans – dotazování pouze cˇ ásti složeného indexu Index Join Scans – výpis hodnot pouze z sloupcu˚ indexu˚
pˇrístup k tabulkám pˇres bitmapový index (Bitmap Indexes) Bitmap Index Single Value – rovnost na indexovaném sloupci Bitmap Index Range Scans – test rozsahu na indexovaném sloupci ˇ Bitmap Merge – použití nekolika bitmapových indexu˚
pˇrístup ke clusterum ˚ tabulek (Table Clusters) (Cluster Scans, Hash Scans) Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
10 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Metoda spojení v plánu vykonávání (Join Method) Nested Loops Joins – prohledávání spojovaných tabulek ve vnoˇrených cyklech (tj. každý rádek první tabulky se porovná se všemi ˇrádky druhé tabulky, atd.)
Hash Joins – párování záznamu˚ spojovaných tabulek pˇres hash klíˇce spojení (tj. spoˇcítají se hash pro sloupec klíˇce spojení v každém ˇrádku menší tabulky a pak ˇ tabulka a pro každý její ˇrádek se spoˇcítá opet ˇ hash klíˇce spojení, se prochází vetší pomocí kterého se nalezne odpovídající ˇrádek první tabulky, který má stejný hash)
Sort Merge Joins – párování seˇrazených záznamu˚ spojovaných tabulek (tj. záznamy spojovaných tabulek jsou seˇrazeny dle sloupcu˚ klíˇce spojení a ˇ eˇ a párují se záznamy shodných hodnot klíˇce spojení) prochází se soubežn
Cartesian Joins – výpis všech záznamy spojovaných tabulek ve vnoˇrených cyklech (bez klíˇce spojení; tj. každý rádek 1. tabulky se spojí se všemi ˇrádky 2. tabulky) Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
11 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Použití EXPLAIN PLAN. . . EXPLAIN PLAN neprovede dotaz, pouze vyvolá optimalizátor ˇ (prub ˚ ežné sledování výsledku˚ optimalizace provedených dotazu˚ se provádí jinak)
ˇ vysvetlení dotazu není zobrazeno, ale je uloženo do sys. tabulky (implicitneˇ se výsledky ukládají do tabuly „plan_table“)
ˇ vysvetlené dotazy lze získat tabulky s ruznou ˚ podrobností ( „basic“ pro výsledky optimalizátotoru, „typical“ vˇc. odhadu ceny, „all“ pro vše) EXPLAIN PLAN FOR SET STATEMENT_ID ’myexplainedquery’ SELECT prod_category, avg(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category; SELECT plan_table_output FROM table(dbms_xplan.display(’plan_table’,’myexplainedquery’,’typical’)); ------
output: ----------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------| ... | ... | ... | ... | ... | ... | Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
12 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Pˇríklad použití EXPLAIN PLAN drop table mark; drop table student; create table student(stdid int primary key, year int); create table mark(stdid REFERENCES student(stdid), subj char(3), mark char(1), primary key (stdid,subj)); create index mark_mark on mark(mark); insert all into student values (1, 2) into student values (2, 3) into mark values (1,’IDS’,’A’) into mark values (1,’PDB’,’B’) into mark values (2,’IDS’,’E’) into mark values (2,’PDB’,’F’) select * from dual; explain plan for select * from student natural join mark; select plan_table_output from table(dbms_xplan.display()); /* ^ HASH JOIN, 2x TABLE ACCESS FULL */ explain plan for select stdid,subj from student natural join mark; select plan_table_output from table(dbms_xplan.display()); /* ^ NO JOIN, 1x INDEX FAST FULL SCAN */ explain plan for select * from student natural join mark where mark != ’F’; select plan_table_output from table(dbms_xplan.display()); /* ^ NESTED LOOPS, INDEX UNIQUE SCAN & TABLE ACCESS FULL */ explain plan for select /*+ INDEX(mark mark_mark) */ * from student natural join mark where mark != ’F’; select plan_table_output from table(dbms_xplan.display()); /* ^ NESTED LOOPS, INDEX UNIQUE SCAN & INDEX FULL SCAN */ Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
13 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Plán vykonávání dotazu Optimalizace plánu˚ vykonávání dotazu˚ Výpis a analýza plánu vykonávání odtazu
Optimalizátor lze ovlivnit pomocí HINTs ˇ optimalizátor muže ˚ zvolit nejjednodušší plán, nikoliv nejsprávnejší (napˇr. pˇri nízkém poˇctu záznamu˚ prochází tabulky pomocí TABLE ACCESS FULL)
optimalizaci dotazu je možno upˇresnit pomocí HINTs (napˇr. zvolit konkrétní index cˇ i metodu spojení; vizte odkaz na seznam HINTs)
(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
14 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Materializovaný pohled Vytvoˇrení materializovaného pohledu Použití materializovaného pohledu
Materializovaný pohled uchovává výsledek dotazu, který vychází z jiných pohledu˚ a z tabulek ˇ (na rozdíl od bežného pohledu, který pouze provádí daný dotaz; tabulky v dotazu v mat. pohledu se nazývají „master“ tabulky a jejich db. je „master“ databáze)
lze nastavit vlastnosti materializovaného pohledu: LOGGING → db. zaznamenává operace s pohledem (v místním redo-logu; je-li potˇreba, pˇrehrává se, napˇr. pˇri chybeˇ cˇ i zrcadlení)
CACHE → db. postupneˇ optimalizuje cˇ tení z pohledu (ˇctené bloky umíst’ovány na zaˇcátek cache, kde jsou pˇríšteˇ hned dostupné)
REFRESH FAST → db. aktualizuje pohled dle logu˚ master tabulek ˇ celého dotazu pohledu) (pˇrehrávání redo-logu je rychlejší, než provádení
ON COMMIT → db. aktualizuje pohled po commitu master tabulek (zaruˇcuje automatickou aktualizaci dat v pohledu; opak je ON DEMAND)
BUILD IMMEDIATE → db. naplní pohled ihned po jeho vytvoˇrení (v opaˇcném pˇrípadeˇ se mat. pohled naplní až pˇríštím REFRESH)
ENABLE QUERY REWRITE → bude používán optimalizátorem (mat. pohled bude použit pro optimalizaci pˇresneˇ stejného dotazu)
ˇ ˇ dat v pohledu FOR UPDATE → db. aktualizuje umožnuje zmenu (musí mít také nastaveno WITH PRIMARY KEY nebo WITH ROWID) Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
16 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Materializovaný pohled Vytvoˇrení materializovaného pohledu Použití materializovaného pohledu
Pˇríklad definice materializovaného pohledu create table student(stdid int primary key, year int); create table mark(stdid REFERENCES student(stdid), subj char(3), mark char(1), primary key (stdid,subj)); -- create rowid master tables logs for fast refresh materialized view create materialized view log on student with rowid; create materialized view log on mark with rowid; -- create fast refresh materialized view from join of master tables create materialized view student_mark nologging cache build immediate refresh fast on commit enable query rewrite as select stdid, student.year, mark.subj, mark.mark, student.rowid as sudent_rowid, mark.rowid as mark_rowid from student natural join mark;
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
17 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
Materializovaný pohled Vytvoˇrení materializovaného pohledu Použití materializovaného pohledu
Pˇríklad použití materializovaného pohledu explain plan for SELECT * FROM student NATURAL JOIN mark; select plan_table_output from table(dbms_xplan.display(null,null,’basic’)); -- --------------------------------------- | Id | Operation | Name | -- --------------------------------------- | 0 | SELECT STATEMENT | | -- | 1 | HASH JOIN | | -- | 2 | TABLE ACCESS FULL| STUDENT | -- | 3 | TABLE ACCESS FULL| MARK | -- -------------------------------------alter session set query_rewrite_enabled = true; explain plan for SELECT stdid, student.year, mark.subj, mark.mark, student.rowid as sudent_rowid, mark.rowid as mark_rowid FROM student NATURAL JOIN mark; select plan_table_output from table(dbms_xplan.display(null,null,’basic’)); -- ------------------------------------------------------ | Id | Operation | Name | -- ------------------------------------------------------ | 0 | SELECT STATEMENT | | -- | 1 | MAT_VIEW REWRITE ACCESS FULL| STUDENT_MARK | -- -----------------------------------------------------
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
18 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
ACID a ovládáná databázové transakce Zamykání databázových objektu˚ ˇ dotazu˚ Optimalizace práce s databází bez zmeny
Databázové transakce a ACID Atomicity Atomiˇcnost transakcí, žádný rozpracovaný stav a to i ve vztahu k možné chybeˇ OS cˇ i HW. ˇ ˇ (probehne celá transakce, tj. všechny její zmeny, nebo nic)
Consistency V DB jsou pouze platná data dle daných pravidel. Transakce se neuskuteˇcní, pokud to nelze dodržet, jinak platí, že puvodní ˚ i nový stav je platný. ˇ ˇ Isolation Soubežné transakce se neovlivnují. Serializace. Poˇradí ˇ však není zajišteno. ˇ Durability Uskuteˇcnená transakce nebude ztracena (její projev). Podpora obnovy dat po pádu HW/SW.
ˇ dat (zamykání) a pˇrístup k datum ACID netriviální, omezuje zmeny ˚ (rychlost). Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
20 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
ACID a ovládáná databázové transakce Zamykání databázových objektu˚ ˇ dotazu˚ Optimalizace práce s databází bez zmeny
Ovládání databázové transakce transakce muže ˚ obsahovat jedno z následujících 1 2
jeden nebo více DML SQL dotazu˚ práveˇ jeden DDL SQL pˇríkaz
transakce zaˇcíná prvním odeslaným SQL dotazem/pˇríkazem transakci konˇcí jedno z následujících1 1 2 3
libovolný DDL SQL pˇríkaz → COMMIT chybné ukonˇcení session → ROLLBACK správné ukonˇcení session → COMMIT ˇ (COMMIT je v tomto pˇrípadeˇ výchozí chování, lze zmenit)
4
zadání COMMIT nebo ROLLBACK bez TO SAVEPOINT
SAVEPOINT vytvoˇrí místo v transakci, od kterého lze pˇred koncem transakce odvolat pomocí ROLLBACK TO SAVEPOINT (ROLLBACK TO SAVEPOINT se odvolá vše po daném místeˇ v transakci vˇc. zámku˚ od toho místa modifikovaných objektu, ˚ ale transakci neukonˇcí) 1 pozor
na autocommit, v sqlplus: SET AUTOCOMMIT OFF
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
21 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
ACID a ovládáná databázové transakce Zamykání databázových objektu˚ ˇ dotazu˚ Optimalizace práce s databází bez zmeny
Zamykání databázovou transakcí transakce zamˇce upravené ˇrádky cˇ i tabulky až do jejího konce úpravy zamˇcených objektu˚ v jiné transakci jsou ihned blokovány (zámky objektu˚ jsou výluˇcné, pokus zamˇcít již zamˇcené je ihned blokován)
odblokování transakcí cˇ ekajících na zámek provede až ukonˇcení ˇ zámku transakce vlastnící zámek, nikoliv uvolnení ˇ samotného zámku bez ukonˇcení stransakce, napˇr. pomocí (tzn. uvolnení ROLLBACK TO SAVEPOINT, nezpusobí ˚ odblokování zablokovaných)
ˇ blokované transakce cˇ ekající na již uvolnené zámky mohou ˇ pˇredbehnout jiné dosud neblokované transakce -- tran1 -- tran2 update student set year=3; savepoint before_mark_lock; update mark set mark=’F’; update mark set mark=’E’; rollback to savepoint -- ^ blocked by tran1 before_mark_lock; -- ^ blocked by tran3 -- ^ not-blocked commit; -- ^ tran1 ends commit; Marek Rychlý
-- tran3
update mark set mark=’F’; -- ^ not-blocked commit; -- ^ tran3 ends
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
22 / 24
Optimalizace plánu˚ vykonávání Materializované pohledy Databázové transakce
ACID a ovládáná databázové transakce Zamykání databázových objektu˚ ˇ dotazu˚ Optimalizace práce s databází bez zmeny
ˇ dotazu˚ Optimalizace práce s databází bez zmeny zamykání a odemykání objektu˚ a (od)blokování je cˇ asoveˇ nároˇcné ˇ napˇr. mnoho úprav jedné tabulky delejte ˇ (zámek využijte maximálne, spoleˇcneˇ v ˇ autocommit a nakonci potvrd’te commitem) jedné transakci, tj. na zaˇcátku vypnete
zpracování dotazu optimalizátorem, tj. volba plánu vykonávání dotazu, pˇred jeho samotným provedením je cˇ asoveˇ nároˇcné ˇ (pro opakované volání dotazu˚ pˇredpˇripravte dotaz/použjte výzané promnenné, takže se optimalizátor vyvolá pouze jednou; v JDBC pomocí PreparedStatement)
sít’ová komunikace s databázovým serverem je cˇ asoveˇ nároˇcná ˇ po vetších ˇ (posílejte dotazy, které nemají odpoved’, skupinách; v JDBC používejte (Prepared)Statement.addBatch() a .executeBatch())
ˇ dotazu)“ (obrázky pˇrevzaty z „David Krch: Rychlejší aplikace i bez zmen ˚ Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
23 / 24
ˇ Shrnutí a záver
ˇ Shrnutí a záver
ˇ ˇ Casto provádené složité dotazy je nutno odladit. (zobrazit vykonávací plán, zavést indexy a mat. pohledy, pˇrípadneˇ použít hints)
Materializovaný pohled obsahuje výsledky dotazu. (nastavením mat. pohledu se urˇcí, jak se výsledky získají a použijí)
Modifikace dat v transakcích vede k zamykání. (a zamykání muže ˚ zpusobit ˚ blokování, nutno odladit)
Je nutno optimalizovat také pˇrístup k db. serveru, nejen dotazy. ˇ (jeden commit, pˇredpˇripravené dotazy s vázanými promennými, odesílání dávek)
Marek Rychlý
Optimalizace dotazu˚ a databázové transakce v Oracle — Demo-cviˇcení pro IDS, 22. dubna 2015
24 / 24