Oracle DB — když jeden index změní všechno

Minulý mesic nas kontaktoval klient s problémem, který zná kazdy DBA: aplikace je pomalá. Konkrétně — vyhledávání objednávek v jejich ERP systemu trvalo 45 sekund. Pred rokem to byly 2 sekundy. Databaze: Oracle 11g R2, tabulka objednávek: 12 milionu radku.

Diagnóza: EXPLAIN PLAN¶

Prvni reflex: potřebujeme víc RAM nebo rychlejsi disky. Ale nez zacnete hazet hardware na problem, podívejte se na execution plan. V 90 procentech případů je problem v SQL nebo chybějících indexech. Oracle provedl FULL TABLE SCAN na tabulce ORDERS a následný NESTED LOOPS join s tabulkou CUSTOMERS. Žádný index na sloupci ORDER_DATE, podle kterého se vyhledavalo.

Řešení¶

Composite index na sloupcich z WHERE klauzule. Po vytvoření indexu a aktualizaci statistik se execution plan dramaticky změnil. Cost spadl z 47 832 na 234. Dotaz z 45 sekund na 0.3 sekundy.

Histogramy — skryty hrdina¶

Sloupec STATUS měl nerovnoměrné rozložení hodnot — 95 procent radku bylo ACTIVE. Bez histogramu Oracle odhadoval 50/50, což vedlo k chybnym execution planum. Řešení: DBMS_STATS.GATHER_TABLE_STATS s histogramem na sloupci STATUS.

Partitioning¶

Pro tabulku s 12 miliony radku jsme doporucili range partitioning podle ORDER_DATE. Měsíční partice = partition pruning = další zrychlení. Bonus: archivace starých dat je triviální.

AWR monitoring¶

Nastavili jsme týdenní AWR reporty s automatickým alertem, kdyz top SQL zmeni execution plan. Prevence je lepsi nez hasení požárů.

Pravidla pro SQL optimalizaci¶

1. Vždy EXPLAIN PLAN — nehadejtee, měřte. 2. Composite indexy. 3. Aktualizujte statistiky pravidelne. 4. Histogramy pro nerovnoměrné sloupce. 5. Partitioning pro velke tabulky.