Касательно структуры статьи:
1. Лучше бы разбили статью на несколько отдельных частей по разным пунктам, т.к. сейчас получится каша с кучей комментариев к разным частям вразброс,
2. Еще лучше было бы разбить и по разным СУБД, т.к. уже вижу кучу неточностей по Oracle.
Далее постараюсь (насколько будет хватать времени и не будет мешать лень) прокомментировать по пунктам:
1. View: Материализация представлений поддерживается в очень частных случаях
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
2. Касательно FAST REFRESH: если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
3. «вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.»
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
4. «абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции» — вы прямо напрашиваетесь на холивар об актуальности «грязных чтений». Не путайте функциональность и целевое назначение view и mview. MView, в первую очередь, это таблицы со всеми сопутствующими свойствами.
5. «один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг.» — Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
6. «View: В параметризованные представления во FROM можно передавать только константы»
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
7. «В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса» — в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
8. «JPPD: Не работает с оконными функциями и рекурсивными CTE»
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
9. «JPPD: Низкая эффективность при работе с денормализованными данными»
Ничего не понятно… Касательно выбранных планов оптимизатора, надо приводить точные данные и, например для Оракла, трассировку 10053. Тут важно даже умение собирать статистику.
10. «Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом» — что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
11. «Разделение логики условий на типы JOIN и WHERE» — кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
12. «Плохая оптимизация при работе с последними значениями»
Оракл может построить хороший план:
SQL> explain plan for
2 SELECT SUM(cc.ls)
3 FROM Product pr
4 LEFT JOIN (SELECT MAX(shipment) AS ls, s.product
5 FROM shipmentDetail s
6 GROUP BY s.product) cc ON cc.product=pr.id
7 WHERE pr.name LIKE 'Product 86%';
Explained.
Plan hash value: 2212025625
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 69 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 78 | | |
| 2 | NESTED LOOPS | | 1 | 78 | 69 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PRODUCT | 1 | 65 | 68 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | SORT AGGREGATE | | 1 | 26 | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| SHIPMENTDETAIL | 1 | 26 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | SHIPMENTDETAIL_PRODUCT_FK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$639F1A6F" "S"@"SEL$2")
INDEX_RS_ASC(@"SEL$639F1A6F" "S"@"SEL$2" ("SHIPMENTDETAIL"."PRODUCT"))
USE_NL(@"SEL$B9D46A48" "CC"@"SEL$1")
LEADING(@"SEL$B9D46A48" "PR"@"SEL$1" "CC"@"SEL$1")
NO_ACCESS(@"SEL$B9D46A48" "CC"@"SEL$1")
FULL(@"SEL$B9D46A48" "PR"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$3")
OUTLINE(@"SEL$E8571221")
MERGE(@"SEL$8812AA4E" >"SEL$E8571221")
OUTLINE(@"SEL$776AA54E")
OUTLINE(@"SEL$2")
OUTER_JOIN_TO_INNER(@"SEL$776AA54E" "CC"@"SEL$1")
OUTLINE_LEAF(@"SEL$B9D46A48")
PUSH_PRED(@"SEL$B9D46A48" "CC"@"SEL$1" 2)
OUTLINE_LEAF(@"SEL$639F1A6F")
ALL_ROWS
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PR"."NAME" LIKE 'Product 86%')
5 - filter(COUNT(*)>0)
8 - access("S"."PRODUCT"="PR"."ID")
Проверьте свои актуальность и свойства статистик по этим таблицам.
Почему нет? Oracle XE — бесплатный, Oracle LiveSQL — бесплатный, Oracle SQL developer — бесплатный, Oracle SQLcl тоже…
EE и его опции, конечно, дорогие, но зато шикарные :)
Помимо самих дневных перепадов температур, еще у них меня бесят их кондиционеры везде: ну какого черта они у них настроены так, что как будто в холодильник заходишь! Или ходишь нормально днем в шортах, приходишь вечером в отель и… одеваешься! Кроме того, в СФ вообще большинство отелей древнючие и в древнючих зданиях, а те что модные новенькие в дни больших конференций стоят по $1500-4000 за ночь
Да, конечно, по идее вы должны откалибровать IO именно в момент высокой нагрузки: I/O Calibration
Однако, это не всегда нужно, т.к. по дефолту оракл выставляет достаточно адекватные статистики, ну и, конечно, проверяйте свою на баги калибровки перед тем как ее запустить, т.к. я помню на старых версиях была баг с занижением в 1000 раз.
Какой статистики? Статистики для оптимизатора (по таблицам, индексам и тд)?
В любом случае — нет. Неправильный сбор статистики, конечно, может усугубить перфоманс, но чаще бывают проблемы с неактуальной статистикой.
Кстати, кто хочет посетить чисто оракловый митап RuOUG (Russian oracle User Group) с Джоэлом могут зарегистрироваться тут: https://anketolog.ru/s/216265/NGT6oXOr
Ну уж если настолько примитивно делать(без проверки форматирования, кавычек, экранирования и тд), то можно просто запросом(query->xml->csv) сделать:
select *
from
xmltable( 'for $r at $i in /ROWSET/ROW[1]
return element r {
element val {string-join($r/*/name(),";")}
},
for $r at $i in /ROWSET/ROW
return element r {
element val {string-join($r/*,";")}
}
'
passing
--dbms_xmlgen.getxmltype(q'[&query ]')
xmltype(cursor(
-- тут сам запрос:
select level a, 2 b, sysdate dt from dual connect by level<=10
))
columns
p_val varchar2(4000) path 'val'
)
/
CURSOR_SHARING = force, да еще и установленный на уровне системы, а не сессии, уже давно вызывает больше боли, чем дает пользы. Его по-моему давно убрали из основных тестов и поэтому кол-во багов с ним очень высоко. Есть у меня один клиент, который до сих пор продолжает упорно жрать кактус, постоянно ловя всякие неожиданные баги из версии в версию…
У меня пара замечаний по сабжу:
1. Для решения этой проблемы есть Adaptive cursor sharing, и он делает именно то, что нужно: на основе гистограмм создает подходящие дочерние курсоры. Если у них это не работало, значит надо было разбираться с причиной почему ACS не работал. Игорь Усольцев делал отличную подробную презентацию в российской юзергруппе оракл (RuOUG). Вообще, советую посещать наши мероприятия.
2. Раз на базе стоит cursor_sharing=force, то можно было просто создать профиль с одним единственным хинтом BIND_AWARE на любом из этих запросов, с указанием параметра force_match=>true.
3. Насчет динамики:
Понятно, что исправление приложения и использование параметров как литералов в запросе – это самый подходящий способ решения проблемы, но он ведет к динамическому SQL с его известными недостатками.
Я еще на 10-ке использовал несколько разных вариантов в зависимости от условий:
3.1 Если был возможен PL/SQL то просто разбивал через IF на пару разных вариантов, грубо говоря: IF cardinality>N then query1 else query2
A cardinality получал одним из быстрых способов.
3.2. Разбиение на union all с доп.подзапросом, например:
select/*+ index(t1 (a,b)) */ *
from t1
where ...
and (select count(*) from t1 where ... and rownum<=X)<X
union all
select/*+ full(t1) или index_ffs(t1) */ *
from t1
where ...
and (select count(*) from t1 where ... and rownum<=X)=X
И если данные уже промаркированы как в статье(отдельная таблица где помечены «большие/средние/маленькие»), то этот вариант был бы намного проще
4. Здесь неверно:
Другой путь – отключение запроса связанных переменных (ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE) или удаление гистограмм (ссылка).
Это решение абсолютно для противоположной цели: отключение _OPTIM_PEEK_USER_BINDS и удаление гистограм делают в случае, если оракл плодит разные планы, а хочется строго одного и того же для любых биндов.
5. Существует и еще один подход: секционирование с учетом data skew. Тут вариантов много, одним из простейших является интервальное секционирование по «перекошенному» столбцу основного ACCESS предиката. А для статьи из примера удобно было бы секционировать как раз по SMALL/MIDDLE/LARGE, то есть три разные секции, каждая со своей статистикой(причем заработает даже без гистограмм, т.к. достаточно (num_rows-num_nulls)/num_distinct статистик по секции), что дает оптимизатору легко понять селективность столбца в данной секции.
ЗЫ. Надо было Виктору ко мне обратиться, вместе бы посмотрели почему ACS у них не работал.
Тем более, что аудитория у такой статьи на английском будет повыше…
зы. к тому же интересно насколько они будут оперативными по сравнению с отечественными компаниями
есть еще timestamp'2001-01-01 00:00:00' или TIMESTAMP '1997-01-31 09:26:56.666 +02:00'
Попробуйте вот этот код с фиксом: gist.github.com/xtender/fe7a1d8c0dff83fe886cb1de95a436bc
1. Лучше бы разбили статью на несколько отдельных частей по разным пунктам, т.к. сейчас получится каша с кучей комментариев к разным частям вразброс,
2. Еще лучше было бы разбить и по разным СУБД, т.к. уже вижу кучу неточностей по Oracle.
Далее постараюсь (насколько будет хватать времени и не будет мешать лень) прокомментировать по пунктам:
1. View: Материализация представлений поддерживается в очень частных случаях
Это очевидная неточность. Материализация представление поддерживается всегда, но не всегда поддерживаются те или иные опциональные возможности, как например FAST REFRESH.
2. Касательно FAST REFRESH: если вы подумаете и сами попробуете проанализировать как можно реализовать инкрементальные обновления, то поймете, что список ограничений абсолютно адекватен текущей сложности SQL.
3. «вас будет ждать еще один неприятный сюрприз: материализованное представление обновляется только в самом конце транзакции.»
Сюрприз?! При создании мвью вы сами выбираете «on commit», так что должны знать, что это происходит при коммите.
4. «абсолютно непонятно, как в принципе получить актуальные данные для материализованного представления внутри транзакции» — вы прямо напрашиваетесь на холивар об актуальности «грязных чтений». Не путайте функциональность и целевое назначение view и mview. MView, в первую очередь, это таблицы со всеми сопутствующими свойствами.
5. «один из достаточно авторитетных экспертов Oracle Donald Burleson в одной из своих книг.» — Что?! На таком серьезном ресурсе как хабр и упоминать бурлесоновщину?!
6. «View: В параметризованные представления во FROM можно передавать только константы»
В Oracle можно использовать контексты, функции, можно создать FGAC и тд и тп.
7. «В MS SQL для решения таких задач есть так называемые table inlined функции, в них можно объявить параметры и использовать их внутри запроса» — в оракле тоже есть и, кроме того, туда спокойно можно передавать параметры из других таблиц
8. «JPPD: Не работает с оконными функциями и рекурсивными CTE»
Работает, просто оконные функции вы не умеете готовить:
вы указываете «row_number() OVER (PARTITION BY shipment ORDER BY id)» — то есть сами инструктируете СУБД сначала посчитать ROW_NUMBER для всего дата сета из таблицы, а фильтруете на другом уровне, после этого подсчета. Если СУБД сначала отфильтровала по вашему предикату, то результат ROW_NUMBER был бы неверным.
9. «JPPD: Низкая эффективность при работе с денормализованными данными»
Ничего не понятно… Касательно выбранных планов оптимизатора, надо приводить точные данные и, например для Оракла, трассировку 10053. Тут важно даже умение собирать статистику.
10. «Так, например, переписанный запрос оконными функциями будет выглядеть следующим образом» — что вы тут хотели и почему запросы не эквивалентны? Вообще во всех указанных СУБД есть LATERALS/CROSS APPLY, которые легко решают проблемы с JPPD
11. «Разделение логики условий на типы JOIN и WHERE» — кстати, советую прочитать про Oracle vs ANSI синтаксис, и пре- и пост- предикаты в отличной книге «The Power of Oracle SQL» habr.com/en/post/461971
12. «Плохая оптимизация при работе с последними значениями»
Оракл может построить хороший план:
Проверьте свои актуальность и свойства статистик по этим таблицам.
… /// to be continued…
Автокомплит отжог… "альбом" и был лишним :)
В прошлом году вышел новый альбом oracle xe 18c, в этом выйдет xe19
EE и его опции, конечно, дорогие, но зато шикарные :)
экспортнуть ее и отправить мне ее на почту? Тогда я смог бы пофиксить это
Однако, это не всегда нужно, т.к. по дефолту оракл выставляет достаточно адекватные статистики, ну и, конечно, проверяйте свою на баги калибровки перед тем как ее запустить, т.к. я помню на старых версиях была баг с занижением в 1000 раз.
В любом случае — нет. Неправильный сбор статистики, конечно, может усугубить перфоманс, но чаще бывают проблемы с неактуальной статистикой.
Кстати, кто хочет посетить чисто оракловый митап RuOUG (Russian oracle User Group) с Джоэлом могут зарегистрироваться тут: https://anketolog.ru/s/216265/NGT6oXOr
1. Для решения этой проблемы есть Adaptive cursor sharing, и он делает именно то, что нужно: на основе гистограмм создает подходящие дочерние курсоры. Если у них это не работало, значит надо было разбираться с причиной почему ACS не работал. Игорь Усольцев делал отличную подробную презентацию в российской юзергруппе оракл (RuOUG). Вообще, советую посещать наши мероприятия.
2. Раз на базе стоит cursor_sharing=force, то можно было просто создать профиль с одним единственным хинтом BIND_AWARE на любом из этих запросов, с указанием параметра force_match=>true.
3. Насчет динамики:
Я еще на 10-ке использовал несколько разных вариантов в зависимости от условий:
3.1 Если был возможен PL/SQL то просто разбивал через IF на пару разных вариантов, грубо говоря: IF cardinality>N then query1 else query2
A cardinality получал одним из быстрых способов.
3.2. Разбиение на union all с доп.подзапросом, например:
И если данные уже промаркированы как в статье(отдельная таблица где помечены «большие/средние/маленькие»), то этот вариант был бы намного проще
4. Здесь неверно:
Это решение абсолютно для противоположной цели: отключение _OPTIM_PEEK_USER_BINDS и удаление гистограм делают в случае, если оракл плодит разные планы, а хочется строго одного и того же для любых биндов.
5. Существует и еще один подход: секционирование с учетом data skew. Тут вариантов много, одним из простейших является интервальное секционирование по «перекошенному» столбцу основного ACCESS предиката. А для статьи из примера удобно было бы секционировать как раз по SMALL/MIDDLE/LARGE, то есть три разные секции, каждая со своей статистикой(причем заработает даже без гистограмм, т.к. достаточно (num_rows-num_nulls)/num_distinct статистик по секции), что дает оптимизатору легко понять селективность столбца в данной секции.
ЗЫ. Надо было Виктору ко мне обратиться, вместе бы посмотрели почему ACS у них не работал.