Много хора вярват, че всички проблеми с производителността на БД се решават с правене на индекс (за щастие не и сред колегите ми тук). Това може и да е вярно за Access или dBase, но в съвременните условия нещата са много по-сложни. Днес имам много приятен пример за това, който ще се опитам да споделя.
В петък колегите ме помолиха да помогна с една тлъста заявка, която работи много бавно. Целта беше да минава за под 3 часа, а тя минава за много повече (никой не е имал нервите да я чака повече от 4 часа).
Заявката работи със следните таблици (естествено, че съм променил имената):
– SG – има малко над 100 милиона реда за изследвания период, с около 32 partitions за периода
– SG_DET – аналогична като размери. Двете таблици се join-ват почти 1:1
– TYPES – номенклатурна таблица с няколко хиляди реда
– SMN – около 3 милиона реда, реферира се на 2 места в първоначалната заявка
– TP – номенклатура за SMN, има десетина реда
– SPK – номенклатура, има-няма 4000 реда
Заявката е много къдрава, с много вложени заявки, много предикати, за това ще представя само execution plan-а. В началото беше следния:
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 98K| | 8477M (1)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 | 42 | | | | |
|* 2 | HASH JOIN | | 1 | 42 | | 9 (12)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | SMN | 1 | 37 | | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_SMN_PID | 1 | | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS STORAGE FULL | TP | 11 | 55 | | 4 (0)| 00:00:01 |
| 6 | HASH GROUP BY | | 1229 | 98K| 6867G| 8477M (1)|999:59:59 |
|* 7 | HASH JOIN | | 70G| 5359G| | 1812K (58)| 06:02:31 |
| 8 | TABLE ACCESS STORAGE FULL | TYPES | 19944 | 506K| | 99 (2)| 00:00:02 |
| 9 | SORT AGGREGATE | | 1 | 9 | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | SMN | 1 | 9 | | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_SMN_PID | 1 | | | 3 (0)| 00:00:01 |
| 12 | VIEW | | 108G| 5642G| | 1303K (41)| 04:20:48 |
|* 13 | HASH JOIN | | 108G| 10T| | 1303K (41)| 04:20:48 |
| 14 | TABLE ACCESS STORAGE FULL | SPK | 3957 | 39570 | | 5 (0)| 00:00:01 |
| 15 | PARTITION RANGE ALL | | 2734M| 254G| | 795K (3)| 02:39:06 |
|* 16 | HASH JOIN | | 2734M| 254G| | 795K (3)| 02:39:06 |
| 17 | TABLE ACCESS STORAGE FULL| SG | 106M| 5099M| | 533K (1)| 01:46:40 |
| 18 | TABLE ACCESS STORAGE FULL| SG_DET | 106M| 5100M| | 248K (1)| 00:49:43 |
--------------------------------------------------------------------------------------------------------------------
Предвид сложността на join условията (има таблици, които се закачат през 5 колони; други дори се закачат със substr от една колона), оптимизатора няма как да изчисли от кой join колко редове ще излязат. За това колонката Rows e относително безполезна. От нея се губи достоверността и на другите колони след нея. Така че трябваше да мисля как е добре да се достъпят данните.
Както всички знаят, execution plan се чете от долу нагоре. Започваме от това, че има join на 2 таблици с по над 100 милиона реда. Нормално е това да отнеме време. От друга страна машината, на която се случва, не е много слаба (много ясно, виж какви заявки и се пускат 😉 ). В името на пробата направих една таблица с create table … as select, в която да сложа резултата само от join-а на тия големи таблици. Останах приятно изненадан от скоростта – десетина минути. Значи проблема е на друго място.
На ред 13 виждаме предположението, че от join-а на двете големи таблици ще се получат 2,7 милиарда записа. Това е изстрел в тъмното от страна на оптимизатора – няма как да знае колко си съвпадат данните. Но аз знам, че те са почти 1:1 – т.е. очакват се на повече от 100-110 милиона. От тук на горе по този клон на дървото данните за cost са очевидно грешни. Това е причината на ред 7 да имаме резултат от HASH JOIN с размер 5 терабайта – пълна измислица – и после да го сортираме за 999 минути (това е еквивалента на „не мога да го сметна, ама ще е много“ в колонката Time).
Оставих заявката да отлежи в главата ми една нощ. Всъщност повече, защото беше уикенд. Пуснах и част от заявката да се изпълнява, като използва вече създаденият резултат от join-а на двете най-големи таблици (с ограничение, което връща около 3% от даните при join-a: около 3.8 милиона реда). Резултата беше доста изненадващ (показвам само интересната част):
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | SORT AGGREGATE | | 3761K| 1 | 3761K|21:53:55.47 | 40M| 0 | 0 | | | |
|* 3 | HASH JOIN | | 3761K| 1 | 154K|21:52:50.68 | 40M| 0 | 0 | 840K| 840K| 67M (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | SMN | 3761K| 1 | 3840K|00:00:40.29 | 15M| 0 | 0 | | | |
|* 5 | INDEX RANGE SCAN | IDX_SMN_PID | 3761K| 1 | 3840K|00:00:28.42 | 11M| 0 | 0 | | | |
| 6 | TABLE ACCESS STORAGE FULL | TP | 3686K| 11 | 40M|00:01:15.78 | 25M| 0 | 0 | | | |
Wtf? Първо, цялото време (цели 22 часа!) се губи в hash join между две не-толкова-големи таблици. SMN има малко под 3 милиона реда, TP има десетина реда! От къде идват тези 40 000 000 реда, извлечени от TP???
За да разбуля мистерията ми помогна осъзнаването на същинските данни (освен събраните статистики). Очаквах при join-а с големия resultset, от данните с SMN да влязат в употреба около 40-50% от редовете (някои – по няколко пъти). Ако се бях сетил за това в петък, веднага щях да намеря бъга. За какъв блян ми е достъп през индекс, когато ще използвам половината таблица?
Колонката Starts обяснява защо се получава така. Този hash join всъщност се е случил 3761K пъти – 3 761 000 пъти сме правили hash join между 1-2 реда от SMN и около 11 от TP. Колонката A-rows (actual rows) показва истинската, тъжна картинка. От таблицата SMN за извлечени 3840K реда. Но са бродирани един по един, през индекс. След това, за всяко посещение на SMN, е преровена миниатюрната TP.
Знаете ли за колко време се
– прочита 1-2 реда от таблица SMN през не-уникален индекс IDX_SMN_PID (минимум 3 IO в индекса + 1 в таблицата, повечето LIO)
– прочитат се 11 реда от TP (1 LIO – всичките данни са в един блок)
– намира се съответствието с относително сложен join condition (нещо от рода на TP.col1 = substr(SMN.col3, 1, instr(SMN.col3, ‘-‘)-1)
Много бързо. За около 0.02 секунди.
Но когато направим това „много бързо“ около 3.8 милиона пъти… събират се 22 часа.
Сметките на оптимизатора не излизат, защото той по някаква причина очаква да използва само 1 ред от SMN (проверих, не е заради статистики). Това си личи по колонката E-rows (estimated rows). Обаче се получава така, че е използвал 3 840 000 реда (вероятно с повторения). От там и 11-те реда от TP стават (3.8 милиона посещения ) * (около 11 реда) = около 40 милиона реда.
След като идентифицирах проблема, има няколко решения. Очевидното е да изнасиля оптимизатора да направи Full table scan на SMN, join с TP и резултата да използва за hash join с другите данни. Твърде много хинтове, предвид, че има и друг вариант.
Пренаписах query-то така, че оптимизатора да се сети сам за това. Всъщност дори спестих едно от посещенията в SMN (тя се използва на 2 места в оригиналната, къдрава заявка). Ето резултата:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 1 |00:22:17.15 | 2939K| 2739K| 73717 | | | |
| 1 | LOAD AS SELECT | | 1 | | 1 |00:22:17.15 | 2939K| 2739K| 73717 | 265K| 265K| 265K (0)|
| 2 | HASH GROUP BY | | 1 | 1229 | 219 |00:22:17.15 | 2939K| 2739K| 73715 | 806K| 806K| 199K (0)|
|* 3 | HASH JOIN | | 1 | 4766M| 99M|00:15:31.66 | 2939K| 2739K| 73715 | 1011K| 1011K| 73M (0)|
| 4 | TABLE ACCESS STORAGE FULL | SPK | 1 | 3957 | 3957 |00:00:00.01 | 12 | 0 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 240M| 198M|00:20:29.36 | 2939K| 2739K| 73715 | 101M| 12M| 197M (0)|
| 6 | VIEW | | 1 | 2827K| 2840K|00:00:36.66 | 88560 | 73715 | 73715 | | | |
| 7 | SORT GROUP BY | | 1 | 2827K| 2840K|00:00:36.66 | 88560 | 73715 | 73715 | 82M| 3174K|74752 (16|
|* 8 | HASH JOIN RIGHT OUTER | | 1 | 2899K| 2899K|00:00:00.06 | 80851 | 0 | 0 | 1348K| 1348K| 71M (0)|
| 9 | TABLE ACCESS STORAGE FULL| TP | 1 | 11 | 11 |00:00:00.01 | 7 | 0 | 0 | | | |
| 10 | TABLE ACCESS STORAGE FULL| SMN | 1 | 2899K| 2899K|00:00:00.01 | 80844 | 0 | 0 | | | |
|* 11 | HASH JOIN | | 1 | 963M| 419M|00:28:03.01 | 2851K| 2665K| 0 | 1143K| 1143K| 74M (0)|
|* 12 | TABLE ACCESS STORAGE FULL | TYPES | 1 | 7719 | 7720 |00:00:00.01 | 344 | 0 | 0 | | | |
| 13 | PARTITION RANGE ALL | | 1 | 2734M| 106M|00:05:24.40 | 2850K| 2665K| 0 | | | |
|* 14 | HASH JOIN | | 32 | 2734M| 106M|00:12:25.34 | 2850K| 2665K| 0 | 2047M| 69M| 429M (0)|
| 15 | TABLE ACCESS STORAGE FULL| SG | 32 | 106M| 106M|00:03:34.60 | 1947K| 1795K| 0 | | | |
| 16 | TABLE ACCESS STORAGE FULL| SG_DET | 31 | 106M| 106M|00:03:34.34 | 903K| 870K| 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
22 минути – доста под зададения target от 3 часа 🙂 . Както се вижда, тук няма качествена разлика между очакванията на оптимизатора (e-rows) и реално прочетените редове (a-rows), освен при големия join.
Какъв, всъщност, беше проблема? Проблема беше в използването на индекс при достъп до много данни. Индексите са полезни, когато ще се посети само малка част от таблицата. В случая се посещават 40-50% от редовете, във, вероятно, 80-90% от блоковете в SMN, и то по повече от веднъж. В този случай индекса е тотално нежелателен (този индекс е полезен за други заявки).