Rychlejší aplikace i bez změn dotazů - 2.díl - vliv vázaných proměnných

V minulém díle jsme si na vzorovém příkladu vkládání 100.000 záznamů ukázali jak velkou zátěž může pro databázový server znamenat zbytečně časté commitování. Dobu zpracování této operace jsme snížili ze 167 na 105 sekund, tedy o třetinu. Ke slibovanému osmdesátinásobnému zrychlení nám chybí ještě dva kroky. V závěru předchozího dílu jsme zjistili, že parsování (rozbor a optimalizace) dotazu zabralo serveru celých 74 ze zmiňovaných 105 sekund. Svou pozornost dnes zaměříme právě na minimalizaci času parsování.

3. Úspora při použití vázaných proměnných

V dalším kroku se budeme snažit uspořit práci kterou musí vykonat databázový optimalizátor. Cesta vede přes používání dotazů s vázanými proměnnými - databázový server tak dostává stále stejný příkaz (text SQL) a použije již dříve vytvořený exekuční plán uložený v SGA paměti.Vedle vlastního SQL však dostane ještě sadu parametrů se kterými má dotaz provést. Dálší čas lze ušetřit opakovaným použitím stejného objektu PreparedStatement.

void vazaneProm(Connection conn, String dalsidata) throws SQLException {
PreparedStatement stmt;
String popis;
stmt=conn.prepareStatement(
"INSERT INTO mojeTab (datum,popis,dalsidata) VALUES(SYSDATE,?,?)");
for(int recNo=0;recNo<100000;recNo++) {
popis="Muj popis c." +recNo;
stmt.setString(1,popis);
stmt.setString(2,dalsidata);
stmt.execute();
}
stmt.close();
conn.commit();
}

Doba běhu klesla na 19 sekund, došlo tedy zhruba k pěti násobnému zrychlení. Tím, že použijete stejný objekt PreparedStatement šetříte nejen zdroje na úrovni vaší aplikace, ale též databázového serveru. Aplikace tímto postupem explicitně databázovému serveru přikazuje použít dříve vytvořený cursor pro který byl exekuční plán již sestaven. Server tak s vyjímkou prvního volání nemusí provádět optimalizaci dotazu.
Výhody opakovaného použití PreparedStatement s vázanými proměnnými v rámci jednoho spojení jsou známé. Může ale tato technika mít nějaký význam i v případech, kdy dotaz kladou různí klienti a objekt PreparedStatement proto nelze sdílet?
To si můžete vyzkoušet, pokud v předchozím kódu přesunete volání stmt=conn.prepareStatement(...) a stmt.close() dovnitř smyčky. Tuto variantu budu dále označovat „3b". Doba běhu sice vzroste z 19 na 27 sekund, ale pořád je zhruba tříkrát kratší, než ve variantě bez vázaných proměnných. Jak je to možné? Vždyť aplikace využívá pokaždé jiný cursor?

Oracle cachuje již dříve vytvořené exekuční plány v části paměti SGA zvané Shared Pool. Pokud aplikace položí SQL dotaz, jehož text přesně odpovídá již dříve optimalizovanému dotazu, neprovádí se plná optimalizace (tzv. Hard Parse), ale vyhledá a použije se již dříve nacachovaný exekuční plán (v operaci nazývané Soft Parse). To je samozřejmě výrazně méně náročné. Je přitom jedno, zda byl dotaz položen v rámci stejného spojení, nebo v různých spojeních.
Důležité je, aby se celý text dotazu nezměnil. Jakmile dojde ke změně textu, i když například jen nahrazením jednoho literálu druhým tak jak jsme to dělali v předchozím díle, optimalizátor existující exekuční plán nenajde a musí provést kompletní optimalizaci. Použití vázaných proměnných má tedy v Oracle význam nejen tam, kde spouštíte opakovaně stejný dotaz v rámci jednoho spojení, ale i tam, kde stejný dotaz spouští opakovaně různí uživatelé.


Statistika2.Commit 1x3a. Vázané proměnné, sdílený PreparedStmt.3b. Vázané proměnné, bez sdílení PreparedStmt.
HodnotaÚsporaHodnotaÚspora
Čas a CPU
Dosažený čas [s]1051982%2675%
CPU used by this session (Spotřeba CPU) [s]86593%1088%
Optimalizace dotazů
Parse time elapsed (Doba parsování dotazů) [s]740100%0,899%
Opened cursors cumulative (Otevřené kurzory) [počet]100 08992100%100 0900%
Parse count (hard) (Úplná optimalizace dotazů) [počet]100 0001100%1100%
Parse count (total) (Optimalizace dotazů celkem) [počet]100 08992100%100 0900%
Čtení a zápis dat
Session logical reads (Logické čtení) [počet operací]426 928130 27469%427 0260%

V obou případech - jak při sdílení objektu PreparedStatement (3a), tak i při jeho opakovaném vytváření (3b) pokleslo významně zatížení procesoru (CPU used by this session). Při sdílení objektu PreparedStatement (3a) server optimalizuje dotaz jen jedenkrát (parse count (hard)) a délka trvání této operace (parse time elapsed) poklesla pod setinu sekundy a nebyl jsem ho tak již schopen změřit.
Ve variantě kdy vytváříme objekt uvnitř smyčky (3b), dochází k parsování pro každý záznam, jak ukazuje statistika parse count (total). Z rozdílu mezi statistikami parse count (total) a parse count (hard) je však vidět, že se jedná pouze o méně nákladný Soft Parse - vyhledání nacachovaného exekučního plánu.
Cca 90 operací Soft Parse o které tato statistika ve všech případech překračuje očekávanou hodnotu jde z velké části na interní operace které musí server provést v rámci zpracování našeho dotazu. Cca 10 operací z toho pak představují dotazy které mi sloužily k uložení výkonnostních statistik během testu.
Ukázali jsme si vliv použití vázaných proměnných na výkon vaší aplikace. Jejich použití však hraje důležitou roli také v zabezpečení vaší aplikace. Na rozdíl od spojování SQL v textu totiž používáním vázaných proměnných nevystavujete svou aplikaci nebezpečí SQLInjection.
Ze slibovaného osmdesátinásobného zrychlení jsme se zatím dostali na zhruba osmi až devítinásobné zrychlení. Máme-li splnit svůj cíl, musíme naši operaci zrychlit ještě desetkrát. V závěrečném dílu toho dosáhneme využitím hromadného zpracování dat.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Česky o všem co se točí kolem Oracle Database.

Autoři:

Patrik Plachý
Technology Sales Consultant

David Krch
Principal Consultant
Oracle Expert Services

Oracle Czech

Search

Archives
« květen 2015
PoÚtStČtSoNe
    
1
2
3
4
5
6
7
8
9
10
11
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
       
Today