Optimalizace dotazů a databázové transakce v Oracle

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ý


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ý


4 / 24



Zapojení optimalizátoru vykonávání dotazu˚

(diagram pˇrevzat z „Database SQL Tuning Guide, Oracle Database Online Documentation 12cR1)“ Marek Rychlý


5 / 24



Komponenty optimalizátoru vykonávání dotazu˚



6 / 24



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ý


7 / 24



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ý


8 / 24



ˇ 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



9 / 24



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ý


10 / 24



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ý


11 / 24



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ý


12 / 24



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ý


13 / 24



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ý


14 / 24


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ý


16 / 24



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ý


17 / 24



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ý


18 / 24


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ý


20 / 24



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ý


21 / 24



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


22 / 24



ˇ 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ý


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ý


24 / 24

Optimalizace dotazů a databázové transakce v Oracle

Recommend Documents