Oracle DB — kdyz jeden index zmeni vsechno

Minuly mesic nas kontaktoval klient s problemem, ktery zna kazdy DBA: aplikace je pomala. Konkretne — vyhledavani objednavek v jejich ERP systemu trvalo 45 sekund. Pred rokem to byly 2 sekundy. Databaze: Oracle 11g R2, tabulka objednavek: 12 milionu radku.

Diagnoza: EXPLAIN PLAN¶

Prvni reflex: potrebujeme vic RAM nebo rychlejsi disky. Ale nez zacnete hazet hardware na problem, podivejte se na execution plan. V 90 procentech pripadu je problem v SQL nebo chybejicich indexech. Oracle provedl FULL TABLE SCAN na tabulce ORDERS a nasledny NESTED LOOPS join s tabulkou CUSTOMERS. Zadny index na sloupci ORDER_DATE, podle ktereho se vyhledavalo.

Reseni¶

Composite index na sloupcich z WHERE klauzule. Po vytvoreni indexu a aktualizaci statistik se execution plan dramaticky zmenil. Cost spadl z 47 832 na 234. Dotaz z 45 sekund na 0.3 sekundy.

Histogramy — skryty hrdina¶

Sloupec STATUS mel nerovnomerne rozlozeni hodnot — 95 procent radku bylo ACTIVE. Bez histogramu Oracle odhadoval 50/50, coz vedlo k chybnym execution planum. Reseni: DBMS_STATS.GATHER_TABLE_STATS s histogramem na sloupci STATUS.

Partitioning¶

Pro tabulku s 12 miliony radku jsme doporucili range partitioning podle ORDER_DATE. Mesicni partice = partition pruning = dalsi zrychleni. Bonus: archivace starych dat je trivialni.

AWR monitoring¶

Nastavili jsme tydenni AWR reporty s automatickym alertem, kdyz top SQL zmeni execution plan. Prevence je lepsi nez haseni pozaru.

Pravidla pro SQL optimalizaci¶

1. Vzdy EXPLAIN PLAN — nehadejtee, merte. 2. Composite indexy. 3. Aktualizujte statistiky pravidelne. 4. Histogramy pro nerovnomerne sloupce. 5. Partitioning pro velke tabulky.