Loni jsme převzali správu Oracle databáze pro jednoho z našich klientů — velkou českou finanční instituci. Systém zpracovával přes milion transakcí denně a pomalu umíral. Response time se zhoršovaly každý měsíc, batch joby nestíhaly doběhnout přes noc. Tady je příběh, jak jsme to dali dohromady.
Diagnóza: AWR reporty nelžou¶
První věc, kterou uděláte, když Oracle databáze nefunguje jak má, je podívat se na AWR (Automatic Workload Repository) reporty. V našem případě to bylo jako otevřít lékařskou zprávu — jasný obraz problému.
Top wait events ukazovaly masivní db file sequential read — klasický příznak špatných execution planů a chybějících indexů. Buffer cache hit ratio bylo na 87 %, což pro OLTP systém s 64 GB SGA je tristní. A shared pool free memory kolísala kolem 2 %, což znamenalo neustálé hard parsingy.
Krok 1: Stabilizace execution planů¶
Oracle optimizer je chytrý, ale někdy až moc. Na produkci se nám stávalo, že po gather stats se execution plány radikálně změnily a dotazy, které běžely sekundu, najednou trvaly minuty.
Řešení: SQL Plan Baselines. Zachytili jsme dobré execution plány a pomocí DBMS_SPM je uzamkli. Nové plány se musí nejdřív ověřit, než je optimizer smí použít. Trochu konzervativní přístup, ale v bankovním systému nechcete surprises.
Druhý krok bylo nastavení pending statistics. Statistiky se nejdřív publikují do pending stavu, otestují na staging prostředí a teprve potom aktivují na produkci. Ano, je to víc práce. Ano, stojí to za to.
Krok 2: Partitioning — základ pro velké tabulky¶
Hlavní transakční tabulka měla 800 milionů řádků a rostla. Bez partitioningu to bylo jako hledat jehlu v kupce sena — i s indexem. Partitioning je sice Enterprise Edition feature (a tím pádem drahý), ale pro tabulky této velikosti je to nutnost.
Zvolili jsme range partitioning po měsících na sloupci TRANSACTION_DATE. Starší partitions (>2 roky) komprimujeme pomocí COMPRESS FOR OLTP. Výsledek: partition pruning snížil I/O na většinu dotazů o 90 %, protože typický dotaz se ptá na data za poslední měsíc.
Archivace je teď triviální — ALTER TABLE DROP PARTITION místo DELETE s miliony řádků. Batch joby, které dříve běžely 6 hodin, teď trvají 40 minut.
Krok 3: Index strategie¶
Klasická chyba: tabulka měla 23 indexů, z toho 8 nepoužívaných. Každý INSERT musel aktualizovat 23 indexů — a při milionech insertů denně to stálo výkon.
Prošli jsme V$SQL_PLAN a identifikovali reálně používané indexy. Osm jsme dropli, tři zbytečně wide composite indexy jsme nahradili cílenějšími. Přidali jsme dva function-based indexy pro časté dotazy s TRUNC(transaction_date).
Výsledek: INSERT throughput se zvedl o 35 %. A paradoxně — i SELECT dotazy byly rychlejší, protože optimizer měl méně indexů na zvažování a nevybíral ty špatné.
Krok 4: PGA a temp management¶
Batch joby generovaly masivní sorty a hash joiny, které přetékaly na disk (temp tablespace). PGA_AGGREGATE_TARGET byl nastavený na 2 GB, což pro server s 128 GB RAM bylo směšné.
Zvýšili jsme PGA na 16 GB a přidali jsme temp tablespace na rychlých SSD discích (místo starých SAS disků). Batch joby, které předtím temp zaplnily a spadly, teď běží čistě v paměti.
Krok 5: Connection pooling a application-side změny¶
Na aplikační straně (Java EE, GlassFish) jsme zjistili, že connection pool byl nastavený na max 200 spojení, ale průměrně se jich používalo 15. Zbylá spojení jen zabírala session memory na Oracle straně.
Snížili jsme pool na 50, přidali statement caching (PreparedStatement cache v JDBC driveru) a zapnuli implicit statement caching na Oracle straně. Počet hard parsů se snížil z 500/s na 20/s.
Výsledky po třech měsících¶
- Průměrný response time: z 2.3s na 0.4s
- Buffer cache hit ratio: z 87 % na 99.2 %
- Batch job completion: z 6h na 40min
- Hard parses: z 500/s na 20/s
- Temp tablespace usage: z 98 % na 15 %
Žádný hardware upgrade. Žádná nová licence. Jen správná konfigurace a pochopení toho, co Oracle vlastně dělá pod kapotou.
Ponaučení¶
Oracle Database je mocný nástroj, ale vyžaduje péči. AWR reporty čtěte pravidelně — ne až když hoří. Investujte čas do partitioningu u velkých tabulek. A hlavně: než koupíte nový hardware, podívejte se, jestli ten stávající využíváte správně.