Не че е голяма грешка, но е на подходящото място 🙂
Ирина Арт 5
Продължаваме пластелиновата тема с още 2 динозавъра:
Вчера работи върху по-„масова“ тема:
Тук се виждат три чинии, чаши, лъжици, питка и нож. Според обяснението на Ирина, синята чаша е моята, защото е най-голяма 😉
И едно доста по-различно изкуство – нещо като пеене. Микрофона е пластмасов, но поне сцената е много подходяща за подскачане:
И за падане също:
Пострадали няма 🙂
Думите са на Радой Ралин и много добре пасват на новия устройствен план на София.
Друг цитат, може би с век по-стар, но също подходящ, е „Парица е царица“.
Става дума за това парче от парка, което бе отхапано за „София ленд“. Още преди години хората заподозряха, че ще завърши като бизнес сгради. Няколко кмета и общински съвета по-късно, това вече е факт.
До тук с неподкупността на ГЕРБ и загрижеността им за доброто на гражданите….
Exadata Adventures
Ако някой е харесал моята презентация на последния семинар на БГПО, може да я изтегли от тук.
Премахнал съм разни красиви фонове и дизайни, за да се получи по-малък файл.
Пътят към Трявна (за сбирката на БГПО)
Хензел (или Гретел) хвърлял трошички по пътя, за да успеят да се върнат до къщата. Да, ама птичките им ги изяли и те се загубили.
За това аз моите трошички си ги записвам в електронен формат. Ако някой не знае как да намери хотел „Калина Палас“ в Трявна, и има GPS (за предпочитане на Garmin), може да опита да използва този файл. Това е track, който започва от отклонението на пътя София-Варна, минава през Севлиево и Габрово и стига до хотела в Трявна, в който ще се проведе сбирката на БГПО. Да нямате после оправдания за закъснение за моята лекция 🙂
Discalimer: Не давам никаква гаранция за трака, но подозирам, че е точен. Защото лично съм го записал. Друг е въпроса, че до сега не съм качвал такова нещо публично и не знам дали съм го направил кадърно.
Зелениковски манастир
Многоръкия шива
Не обичам да си губя времето. Не. Мразя да си губя времето. Не понасям да правя излишни неща.
Сега ми стовариха една таблица, която има данни за 2 години и половина, а няма нито индекси, нито партишъни. Има 334 милона реда. И аз трябва да я нацепя и да разходя данните в 30 по-малки таблички, по месеци. Малки, спретнати, чак компресирани.
Направих си аз празните таблички и започнах да прехвърлям. Ето какво се случи:
SQL> insert /*+append*/ into DATA_200703
2 select *
3 from HUDGE_TABLE_WITH_DATA
4 where j.rowtime < to_timestamp('01.04.2007', 'DD.MM.YYYY')
5 and j.rowtime >= to_timestamp('01.03.2007', 'DD.MM.YYYY')
6 order by j.nice_column;
4586739 rows created.
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
. . .
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | LOAD AS SELECT | | 1 | | 1 |00:29:29.90 | 3355K| 3388K| 52040 | 525K| 525K| 525K (0)| |
| 2 | SORT ORDER BY | | 1 | 4161K| 4586K|00:28:32.90 | 3344K| 3388K| 44291 | 1557M| 9652K| 244M (1)| 1400K|
|* 3 | FILTER | | 1 | | 4586K|00:16:10.97 | 3344K| 3343K| 0 | | | | |
|* 4 | TABLE ACCESS FULL| HUDGE_TABLE_WITH_DATA | 1 | 4161K| 4586K|00:16:06.38 | 3344K| 3343K| 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Тук вземам данните за един месец и ги сортирам по една подходяща за случая колонка (за да се сортират по-добре). Както знаете, колкото повече повторения се паднат в един блок, толкова повече може да се компресира той.
Това, което се получава, е мега гадно. За да изчопля данните за един месец, аз трябва да пребъркам цялата огромна таблица. Нямам избор – индекси няма. И това упражнение, което отнема половин час, трябва да го повторя 30 пъти. Не ме кефи, ама хич. Толкова излишни сканирания, такава брутална загуба на време…
Представете си някой, който сортира един чувал с поща. Избира си улица 1. Взема всяко едно писмо и поглежда дали е за там. Ако е за там – го слага в съответния раздел. Ако не – го оставя пак на голяма купчина. После повтаря всичко отначало за улица 2, улица 3 и т.н. И на всичко отгоре, аз дори не махам редовете, които вече съм insert-нал.
Не може ли като посетя веднъж един ред, да го напъхам в неговата си табличка; следващия – в неговата и т.н.?
* * *
Всъщност може. Още от Oracle 9i имаме multitable insert. Който, в случая ми върши работа перфектно.
SQL> insert /*+append*/
2 when
3 (rowtime < to_timestamp('01.05.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.04.2007', 'DD.MM.YYYY')) then into DATA_200704
4 when
5 (rowtime < to_timestamp('01.06.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.05.2007', 'DD.MM.YYYY')) then into DATA_200705
6 when
7 (rowtime < to_timestamp('01.07.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.06.2007', 'DD.MM.YYYY')) then into DATA_200706
8 when
9 (rowtime < to_timestamp('01.08.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.07.2007', 'DD.MM.YYYY')) then into DATA_200707
10 when
11 (rowtime < to_timestamp('01.09.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.08.2007', 'DD.MM.YYYY')) then into DATA_200708
12 when
13 (rowtime < to_timestamp('01.10.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.09.2007', 'DD.MM.YYYY')) then into DATA_200709
14 when
15 (rowtime < to_timestamp('01.11.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.10.2007', 'DD.MM.YYYY')) then into DATA_200710
16 when
17 (rowtime < to_timestamp('01.12.2007', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.11.2007', 'DD.MM.YYYY')) then into DATA_200711
18 when
19 (rowtime < to_timestamp('01.01.2008', 'DD.MM.YYYY') and rowtime >= to_timestamp('01.12.2007', 'DD.MM.YYYY')) then into DATA_200712
20 select *
21 from HUDGE_TABLE_WITH_DATA j
22 where rowtime < to_timestamp('01.01.2008', 'DD.MM.YYYY')
23 and rowtime >= to_timestamp('01.04.2007', 'DD.MM.YYYY')
24 order by j.nice_column;
41983210 rows created.
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
. . .
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | MULTI-TABLE INSERT | | 1 | | 7 |00:03:25.78 | 3428K| 3793K| 498K| | | | |
| 2 | VIEW | | 1 | 44M| 44M|00:58:49.57 | 3358K| 3793K| 450K| | | | |
| 3 | SORT ORDER BY | | 1 | 44M| 44M|00:58:49.57 | 3358K| 3793K| 450K| 15G| 29M| 244M (1)| 13M|
|* 4 | FILTER | | 1 | | 44M|00:23:32.12 | 3344K| 3342K| 0 | | | | |
|* 5 | TABLE ACCESS FULL| HUDGE_TABLE_WITH_DATA | 1 | 44M| 44M|00:22:47.99 | 3344K| 3342K| 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Тук сканирам само един път цялата голяма таблица, а insert-вам в 8 от малките. Ако не беше сортирането, щях да го пусна направо за всичките 29 оставащи таблички, но се притесних да не ми отеснее temp-a. Което се оказа излишно притеснение.
Между другото, в сряда ме питаха кое ми е направило голям впечатление в Oracle и чак съм си казал „а стига бе!„. Е, сортирането на огромно количество данни е такова нещо. Не мога да повярвам как е сортирало около 15-16 GB данни (по мои груби сметки), с 1-pass sort, използвайки 244 MB памет (явно максимума при тоя PGA target и автоматично управление). Не за пръв път ми прави впечатление – сортирането на големи обеми информация в Oracle е невероятно ефективно!
P.S. Поради спецификата на данните и с помощта на сортирането, компресията се получи 1:8. Това е с нормалната компресия, на 10.2, а не с новата хибридна от 11.2 🙂
Трябва ли ти този индекс?
Много хора вярват, че всички проблеми с производителността на БД се решават с правене на индекс (за щастие не и сред колегите ми тук). Това може и да е вярно за 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, и то по повече от веднъж. В този случай индекса е тотално нежелателен (този индекс е полезен за други заявки).
Ирина арт 4
Вчера купих на Ирина пластелин. Така и така обича да твори изкуство, нека да опита нещо по-различно 🙂
В началото и помагах, после тя продължи сама. За първия час натвори следното:
Ето и детайлите:
Започнахме с това цвете. За загрявка. После Ирина започна да „рисува“ къщичка с черти от пластелин. За да и покажа, че идеята всъщност е да се правят триизмерни неща аз сътворих това:
Гъба. Долу-горе мое производство, Ирина сложи само точките отгоре. После започна сама:
Това е къщичка. Изцяло нейно дело. Доста миниатюрна – висока е по-малко от 2 сантиметра.
Таралеж. Не по-голям от гъбата.
Калинка. Започна да я прави по-малка и от таралежа, аз и подсказах да е по-голяма. Така я направи цели 3 сантиметра на дължина.
„Момиче“ (отново 2D). Тук трябва да се отбележат следните изрично направени детайли:
– голо пъпче – в символ на лятото
– поличка – естествено
– обувки. Доста време им отдели
Това беше за първият ден. А днес Ирина направи (отново съвсем самичка) цял динозавър – висок поне 5 сантиметра, с много детайли: