Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
Добрый день!

Последние несколько дней углубленно читал эту книжу издательства ОРейли.
Прочел примерно половину, не углубляясь в разборо особо извращенных случаев запросов и архитекутр БД.

Есть много мыслей и вопросов.

Кто-нибудь из вас использовал диаграммную технику и коэффициенты фильтрации для определения оптимальной последовательности соединения таблиц? Выглядит все красиво, но на деле пока проверить не могу.

Для оптимизации селектов я часто использовал Tuning Expert в Toad\SQLNavigator, но он редко стоящие советы дает.

Обычно дело обходится указанием в подсказке имени индекса, который нужно использовать в первую очередь, указанием ORDERED для последовательости во FROM или в более сложных случаях NL.

Мне показались довольно притянутым за уши пример, разобранный для Оракла в приложении Б - там , где для изменния плана нужно убрать неявное преобразование типа.

Рельных же жизненных примеров Тоу не показывает, пару раз ссылаясь на конфиденциальность таких примеров, которых он оптимизировал в разных конторах.

Еще непонятно, можно ли оптимизировать запросы , в которых есть подзапросы с IN (...). Такие примеры он обходит.

Я подумал, что в принципе несложно написать скриптик ии программку, которые на входе будут получать запрос (или его части FROM и WHERE) и строить на его основе такие диаграммы. Или это уже где-нибудь есть?

Второй вопрос, который меня волнует - как мы реально можем влиять на план запроса (не только в Оракле, но и в MSSQL), кроме использования хинтов?

В главе 4 (управление планами выполнения) приводятся хинты для обеих СУБД и несколько способов выключать нежелательные индексы, в основном путем насильного преобразования типа поля в условии отбора. Но никаких других способов, похоже, нет. Или есть?

В конце концов, если нельзя (или ломает) писать оптимизированный селект, того же результата можно достигнуть через PLSQL, в явном виде написав все вложенные циклы, обходящие таблицы по индексам через простые селекты\курсоры.
12 сен 06, 19:36    [3126542]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
makondo

В главе 4 (управление планами выполнения) приводятся хинты для обеих СУБД и несколько способов выключать нежелательные индексы, в основном путем насильного преобразования типа поля в условии отбора. Но никаких других способов, похоже, нет. Или есть?



Преобразование типа - не модно, да и нет гарантии, что оракл когда нибудь не догадается преобразовать тип обратно. Хинты, outline, статистики.
12 сен 06, 19:40    [3126554]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
makondo
Кто-нибудь из вас использовал диаграммную технику и коэффициенты фильтрации для определения оптимальной последовательности соединения таблиц? Выглядит все красиво, но на деле пока проверить не могу.


Автор описывает идеальный случай, кроме того он не предлагает завершённую технику, описание больше смахивает на отдельные зарисовки, которые как хотите, так и компонуйте.

Я применяю похожую логику оптимизации, но прибегать к построению диаграмм не приходилось. Возможно, диаграммы позволяют наглядно объяснить студентам, читателям принципы оптимизации, но на практике проще оперировать планами в уме.

Вообще практическая польза от метода вызывает сомнение. Оракловый оптимизатор строит и оценивает тысячи вариантов плана. Найти наилучшее решение задачи вручную очень сложно. Скорее всего следует научиться пользоваться функциями CBO самого Оракл.
12 сен 06, 19:48    [3126572]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
makondo

Еще непонятно, можно ли оптимизировать запросы , в которых есть подзапросы с IN (...). Такие примеры он обходит.


Можно. Книга не претендует на полный охват темы. Читай оракловую доку, там есть и про IN.
12 сен 06, 19:50    [3126576]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
makondo
В конце концов, если нельзя (или ломает) писать оптимизированный селект, того же результата можно достигнуть через PLSQL, в явном виде написав все вложенные циклы, обходящие таблицы по индексам через простые селекты\курсоры.


Можно и так, но как правило SQL решает эту задачу значительно быстрее. Более того, при изменении данных, оптимизатор может оптимизировать план запроса, тогда как переписывать PL/SQL процедуру придётся руками. SQL значительно лаконичнее.
12 сен 06, 19:54    [3126584]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
makondo
Я подумал, что в принципе несложно написать скриптик ии программку, которые на входе будут получать запрос (или его части FROM и WHERE) и строить на его основе такие диаграммы. Или это уже где-нибудь есть?


Флаг тебе в руки, дарагой таварищь. Оракл писал свой оптимизатор десятки лет и до сих пор пишет. Чтобы не изобретать велосипед, почитай про explain plan.
12 сен 06, 19:57    [3126590]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
mcureenab
makondo

В главе 4 (управление планами выполнения) приводятся хинты для обеих СУБД и несколько способов выключать нежелательные индексы, в основном путем насильного преобразования типа поля в условии отбора. Но никаких других способов, похоже, нет. Или есть?


Преобразование типа - не модно, да и нет гарантии, что оракл когда нибудь не догадается преобразовать тип обратно. Хинты, outline, статистики.


Оракле просекает про UPPER(), а MSSQL - про преобразование строки в число, а так, имхо - хрен догадается..
12 сен 06, 20:31    [3126652]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
mcureenab
makondo
Кто-нибудь из вас использовал диаграммную технику и коэффициенты фильтрации для определения оптимальной последовательности соединения таблиц? Выглядит все красиво, но на деле пока проверить не могу.


Автор описывает идеальный случай, кроме того он не предлагает завершённую технику, описание больше смахивает на отдельные зарисовки, которые как хотите, так и компонуйте.


Он и не претендует на всеохват - мол, жизнь - штука сложная, все дао не выразить словами. Так прям и пишет - полагайтесь на интуицию.


mcureenab


Я применяю похожую логику оптимизации, но прибегать к построению диаграмм не приходилось. Возможно, диаграммы позволяют наглядно объяснить студентам, читателям принципы оптимизации, но на практике проще оперировать планами в уме.


Может, напишешь свою книжку? :) Или хотя бы поделишься секретами мастерства...




mcureenab

Вообще практическая польза от метода вызывает сомнение. Оракловый оптимизатор строит и оценивает тысячи вариантов плана. Найти наилучшее решение задачи вручную очень сложно. Скорее всего следует научиться пользоваться функциями CBO самого Оракл.


Всякие техники имеет смысл применять, когда перебрные алгоритмы CBO\RBO дают слабину и не обеспечивают оптимальный план. Тогда и нужно изворачиваться.

Один раз я долго тормозил, почему запрос с несколькими подзапросами и ORDER BY умирает, пока случайно не заметил, что одно из полей с псевдонимом в сортировке не из той таблицы, которое в FROM.Хоть таблица та же была, но псевдоним другой..
12 сен 06, 20:39    [3126662]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
mcureenab
makondo
В конце концов, если нельзя (или ломает) писать оптимизированный селект, того же результата можно достигнуть через PLSQL, в явном виде написав все вложенные циклы, обходящие таблицы по индексам через простые селекты\курсоры.

Можно и так, но как правило SQL решает эту задачу значительно быстрее. Более того, при изменении данных, оптимизатор может оптимизировать план запроса, тогда как переписывать PL/SQL процедуру придётся руками. SQL значительно лаконичнее.


Это да.
12 сен 06, 20:42    [3126668]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
mcureenab
makondo
Я подумал, что в принципе несложно написать скриптик ии программку, которые на входе будут получать запрос (или его части FROM и WHERE) и строить на его основе такие диаграммы. Или это уже где-нибудь есть?

Флаг тебе в руки, дарагой таварищь. Оракл писал свой оптимизатор десятки лет и до сих пор пишет. Чтобы не изобретать велосипед, почитай про explain plan.


Несмотря на десятки и сотни человеко-лет он иногда дает сбои. Ну, конечно, может я неправильно строю для него свои запросы :) Вместо EXPLAIN PLAN мне намного приятнее в TOAD рассматривать план, еще и с рекомендациями.
Есть еще EXECUTE IMMEDIATE... Там часто заранее трудно сказать про план.

Я не хочу переть своим умищем против оракловского плана в 90% случаев. :) Нужно менять не когда все хорошо, а когда все плохо
12 сен 06, 20:47    [3126674]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
А Вас какая точка зрения интересует - математическая, предметноориентированная или личная?
14 сен 06, 08:51    [3133614]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
makondo
Member

Откуда:
Сообщений: 428
Splain
А Вас какая точка зрения интересует - математическая, предметноориентированная или личная?


Все интересуют, конечно же, но в первую очередь - 3 и 2 :)
14 сен 06, 09:58    [3133963]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Sergey_Evdokimov
Member

Откуда: Moscow
Сообщений: 362
Я считаю методику очень полезной при отсутствии CBO. Лично мне начальство запретило собирать статистику. Потому стараюсь пользовать именно приемы из этой книги. Результаты меня устраивают. А "Советчик" от Quest еще ни разу ничего дельного мне не предложил.
makondo
Нужно менять не когда все хорошо, а когда все плохо
Согласен
14 сен 06, 10:28    [3134164]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Sergey_Evdokimov
Member

Откуда: Moscow
Сообщений: 362
mcureenab
Я применяю похожую логику оптимизации, но прибегать к построению диаграмм не приходилось. Возможно, диаграммы позволяют наглядно объяснить студентам, читателям принципы оптимизации, но на практике проще оперировать планами в уме.
Дэн постоянно говорит, что общая цель - отбросить как можно больше "лишних" строк как можно раньше в плане запроса. В сложных запросах сделать это без диаграммы невозможно.

ЗЫ Насколько я понимаю метод не очень популярный в местных кругах, мне тоже хотелось бы послушать его критику :)
14 сен 06, 10:31    [3134194]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
sam11
Member

Откуда: Москва
Сообщений: 30
ИМХО, неплохая книга. Лично для меня она стала материализацией общей идеи оптимизации сложных SQL-запросов. Сейчас постоянно опираюсь на его методику, правда диаграммы не рисую. Хотя, после настройки пары десятков запросов, изменяется последовательность: сначала пишешь оптимизированный запрос, а потом можно нарисовать диаграмму для наглядности :). Ну а коэффициенты, на мой взгляд, перебор. Обычно, среднее отношение кол-ва записей в таблицах можно прикинуть на глаз.
Еще хочу поделится случаем в нашей фирме: очень сложный SQL-запрос, написанный с учетом индексов и т.д. и т.п., выполнялся за недопустимо большое время. Как оказалось из-за того, что есть ограничение на кол-во планов (или вложенность точно не помню как этот ограничитель называется). Поэтому, я на всякий случай во всех запросах ставлю хинт ORDERED (Oracle).
14 сен 06, 10:40    [3134259]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
contr
Member

Откуда:
Сообщений: 1909
sam11
ограничение на кол-во планов (или вложенность точно не помню как этот ограничитель называется). Поэтому, я на всякий случай во всех запросах ставлю хинт ORDERED (Oracle).

select * from v$parameter where name='optimizer_max_permutations'
14 сен 06, 10:43    [3134279]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
contr
Member

Откуда:
Сообщений: 1909
Sergey_Evdokimov
Дэн постоянно говорит, что общая цель - отбросить как можно больше "лишних" строк как можно раньше в плане запроса.

Гыыы... А что говорит Дэн в случае, когда надо посоединять 5-6 млн из первой таблички, 2-3 млн из второй и 25-30 млн. из третьей?
Для первой это ~95% записей, для второй - около 70%, для третьей - 40% от одной-двух секции?
14 сен 06, 10:48    [3134308]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
sam11
Member

Откуда: Москва
Сообщений: 30
makondo
Я не хочу переть своим умищем против оракловского плана в 90% случаев. :) Нужно менять не когда все хорошо, а когда все плохо


Я думаю, нужно стараться, чтобы оракловский план совпал с твоим представлением об оптимальности (после освоения методики Дэна Тоу). Наверное, это и будет максимально оптимальное решение :)
14 сен 06, 10:50    [3134324]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Sergey_Evdokimov
Member

Откуда: Moscow
Сообщений: 362
contr
Гыыы... А что говорит Дэн в случае, когда надо посоединять 5-6 млн из первой таблички, 2-3 млн из второй и 25-30 млн. из третьей?
Для первой это ~95% записей, для второй - около 70%, для третьей - 40% от одной-двух секции?
Он вводит некое понятие "надежного" плана, время выполнения запроса по которому пропорционально количеству возвращаемых строк.... ну а клиенту их миллионами и сразу точно не надо :)
14 сен 06, 10:52    [3134342]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Могу сказать то же, что уже говорил.
https://www.sql.ru/forum/actualthread.aspx?bid=3&tid=233557&hl=%e4%fd%ed+%f2%ee%f3#2055955

sam11
ИМХО, неплохая книга. Лично для меня она стала материализацией общей идеи оптимизации сложных SQL-запросов.
Лучше учебник почитайте какой.

sam11
Поэтому, я на всякий случай во всех запросах ставлю хинт ORDERED (Oracle).

А может лучше на всякий случай думать головой и ставить хинт только когда это реально может быть необходимо?
14 сен 06, 10:56    [3134369]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
sam11
Member

Откуда: Москва
Сообщений: 30
contr
Sergey_Evdokimov
Дэн постоянно говорит, что общая цель - отбросить как можно больше "лишних" строк как можно раньше в плане запроса.

Гыыы... А что говорит Дэн в случае, когда надо посоединять 5-6 млн из первой таблички, 2-3 млн из второй и 25-30 млн. из третьей?
Для первой это ~95% записей, для второй - около 70%, для третьей - 40% от одной-двух секции?


2-я -> 1-я -> 3-я. Интересно, а зачем столько миллионов на выходе?
14 сен 06, 10:57    [3134376]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
sam11
Member

Откуда: Москва
Сообщений: 30
Я и ёжик
Лучше учебник почитайте какой.

С удовольствием! Что порекомендуете?

Я и ёжик
А может лучше на всякий случай думать головой и ставить хинт только когда это реально может быть необходимо?

Головой думать никогда не вредно - именно поэтому я думаю головой, а чтобы Оракл понял ход моих мыслей и говорю в каком порядке (ordered) я думал :)
14 сен 06, 11:01    [3134408]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Sergey_Evdokimov
Member

Откуда: Moscow
Сообщений: 362
contr
40% от одной-двух секции?
ИМХО: эта книга - "общие" методики для разных БД. Секции, партиции и кто там еще (я не админ) я не помню чтобы он там обсуждал. Думаю книга полезна - она позволяет сформировать подход к оптимизации, а углубленные знания именно ораклового оптимизатора даст родная литература по ORACLE TUNNING (etc) и без вариантов...

А вот тут уже интересно:
Я и ёжик
Могу сказать то же, что уже говорил.
https://www.sql.ru/forum/actualthread.aspx?bid=3&tid=233557&hl=%e4%fd%ed+%f2%ee%f3#2055955
Да и факер там учавствует... То что надо - почитаем :)
14 сен 06, 11:05    [3134437]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Splain
Member

Откуда: Череповец
Сообщений: 924
1. С математической точки зрения описываемые в книге события относятся к задаче построения минимального остова во взвешенном неориентированном графе алгоритмом Прима (жадный алгоритм). Задача же построения оптимального плана выполнения даже в минимальной постановке (задача об оптимальном способе перемножения матриц) принадлежит классу NP и решается методами динамического программирования. Так что все последующие извращения в книге после описания алгоритма Прима имеют целью "заточить" алгоритм и успехом увенчаться не могут по определению - задача этим методом не решается принципиально.
При этом первичное объяснение бессистемно и растянуто на 200 страниц. Фразы про математическое представление результата я комментировать не буду. Скажу только, что результат не является математической моделью и потому кроме как для поиска минимального остова никуда применен быть не может.

2. С предметноориентированной точки зрения метод устраивает соревнование с оптимизатором СУБД - кто лучше выберет план. Однако в современном мире СУБД от программиста не требуется решать задачи оптимизации.
Я поясню о чем речь - здесь имеет место некоторая путаница в понятиях. Употребляемое по историческим причинам выражение "оптимизация запросов" не является точно отражающим выполняемые действия. Оптимизацией выполнения запросов занимается СУБД на основании следующих знаний: что нужно получить (SQL - запрос), ограничения целостности СУБД, и информация о связях между данными, которая не может быть описана ограничениями целостности (помещается в SQL - запрос). При этом описание связей между данными может быть недостаточно точным (полным). Достаточно, чтобы выбираемый оптимизатором план запроса выполнялся в заданное время. С ростом количества данных количество удовлетворяющих заданному времени планов запросов уменьшается, и может потребоваться уточнение описания информационных зависимостей. Этот процесс уточнения называют "оптимизацией" запроса. Однако со смысловой точки зрения процесс уточнения является настройкой оптимизатора СУБД с целью получения оптимального плана.
Определившись с понятиями, пойдем дальше. Во время настройки запроса программист последовательно решает задачи идентификации (где проблема), классификации (в чем состоит проблема) и дедуктивного вывода (как ее решить). Метод диаграммного изображения запросов решает все задачи сам и сразу дает решение - как должно быть. При этом все соображения оптимизатора СУБД, который решает задачу точнее и знания о предметной области программиста оказываются не нужны. Так что я могу предположить, что метод будет работать хорошо только в том случае, если оптимизатор СУБД не умеет оптимизировать или программист не знает предметной области. Поэтому обитающие на форуме специалисты этот метод не используют - он не позволяет пользоваться имеющимися знаниями, во-первых. Во-вторых, никак не помогает решить задачу настройки запроса - кто даст гарантии, что получаемый план выполнения будет выполняться за заданное время?

3. Лично я очень хорошо отношусь к методу, практически влюблен в него. Вот не было бы его - что бы я тогда в диссертации ругать стал?
14 сен 06, 11:09    [3134467]     Ответить | Цитировать Сообщить модератору
 Re: Обсуждение методики оптимизации SQL из одноименной книги Дэна Тоу  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
sam11
С удовольствием! Что порекомендуете?
:)
Любой, который рассматривает основы построения оптимизаторов.
Например Г. Гарсия-Молина, Дж. Ульман, Дж. Уидом "Системы баз данных. полный курс".
sam11

Головой думать никогда не вредно - именно поэтому я думаю головой, а чтобы Оракл понял ход моих мыслей и говорю в каком порядке (ordered) я думал :)

Хинтом Ordered вы лишаете оптимизатор возможности реагировать на изменения в распределении данных. Хинт Ordered приминим если в запросе много таблиц и на построение плана уходит значительное время или эффективный план не попадает в рассматривое множество планов (при текущей реализации оптимизатора), но тут в большинстве случаев будет достаточно более мягкого хинта leading или мер по более точному определению кардинальности выборок таблиц ( гистограммы, dynamic sampling, sql_profile в 10g ).
Ляпать всегда какоето решение , только потому, что оно однажды где то помогло, несколько эээ.... неблагорозумно.
14 сен 06, 11:17    [3134525]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Oracle Ответить