Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 cost-based optimizers  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
Как-то в поледнее время гложит мысль, что cost-based оптимизатор - не обязательно есть польза. Просто у нас тут баальшая база, ну и куча запросов естественно. Мого времени тратится на переписывание уже давно написанных запросов. При этом цикл примерно сделующий:
  • пишется запрос,
  • оптимизируется
  • работает.
  • на сервере естественно меняется старистика в связи с изменением данных
  • приходит время серверу переоптимизировать запрос (например, в связи с перегрузкой сервера).
  • в новых условиях запрос оптимизируется по-другому и уже не работает нормально (данные большие, шаг влево, шаг вправо - запрос практ. виснет навсегда)
  • goto п. 1 (оптимизация).

    Хорошо еще , если из этого цикла есть выход по условию, являющийся естественно полной фиксацией плана запроса в виде хинтов , forceplan ("прибивает гвоздями" порядок обработки таблиц в запросе, если кто не знает) и прочей лабуды (если она вообще есть у сервера, вон говорят в DB2 вообще нет хинтов).

    Вот и задумываюсь я. Вроде бы как cost-based оптимизатор - стандарт для Enterprise-систем. И вижу я впереди полный беспросвет в виде вышеприведенного бесконечного цикла. Выхода нету. Все.

    Кто что скажет ?
  • 4 апр 05, 21:30    [1440113]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34709
    Пардон, goto должен быть на п.2. Хотя иногда , естественно, и на п.1 приходится.
    4 апр 05, 21:31    [1440115]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    alex_k
    Member

    Откуда: krasnoyarsk
    Сообщений: 6694
    жизнь вообще есть бессмысленная бесконечность перерождений, вырваться из которой можно только в нирвану :-)
    4 апр 05, 23:07    [1440175]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    AAron
    Member

    Откуда: Москва
    Сообщений: 4324
    а что, rule-based - лучше?


    кстати, жесткое прибивание плана как раз есть не лучший вариант. при изменении статистики точно начнутся танцы с бубном. Что собственно и наблюдается.
    4 апр 05, 23:29    [1440183]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    Yo!!
    Guest
    вообще-то именно в этом и фича cost-based - он умеет менять план в зависимости от статистики. у вас он просто какой-то неправильный ;)

    rule-based vs cost-based имхо хорошо отслеживается на оракле - зайдите даже на этом форуме и посмотрите с какими проблемами столкнулись те кто по каким-то причинам юзал rule. к стате из современных живых субд помоему только интербэйс осталься с rule-based.
    4 апр 05, 23:43    [1440187]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    ASCRUS
    Member

    Откуда: МО Электросталь
    Сообщений: 5994
    MasterZiv
    Как-то в поледнее время гложит мысль, что cost-based оптимизатор - не обязательно есть польза. Просто у нас тут баальшая база, ну и куча запросов естественно. Мого времени тратится на переписывание уже давно написанных запросов. При этом цикл примерно сделующий:
  • пишется запрос,
  • оптимизируется
  • работает.
  • на сервере естественно меняется старистика в связи с изменением данных
  • приходит время серверу переоптимизировать запрос (например, в связи с перегрузкой сервера).
  • в новых условиях запрос оптимизируется по-другому и уже не работает нормально (данные большие, шаг влево, шаг вправо - запрос практ. виснет навсегда)
  • goto п. 1 (оптимизация).

  • У каждой СУБД свой cost-based оптимизатор, каждый по своему хорош/плох, не надо пожалуйста обобщать, лично я наоборот доверяю и радуюсь тому же оптимизатору и эвристическому анализатору ASA, у которых в тесном симбиозе хватает мозгов на очень очень многое, чего я честно говоря не наблюдал в том же MSSQL 2000 (хотя там тоже оптимизатор достаточно неплох с моей личной точки зрения).
    5 апр 05, 00:26    [1440209]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    michael_
    Member

    Откуда: Москва
    Сообщений: 600
    Оптимизатор может пойти другим путем, не только от того, что пересобрали статистику, но и может менять правила игры от билда к билду, (и в Sybase ASA с этим не все просто). Мне кажется, неплохой путь - разбиение (сегментирование) больших таблиц либо вручную либо средствами сервера, как например в Oracle.

    Вручную приходится делать несколько таблиц одной структуры, например по кварталам, месяцам или "архив - рабочие данные".

    А сказки, что сервер ИМЯРЕК классно справляется с таблицами в сотни, десятки или просто миллионы записей на практике выглядят как перкомпилеж процедур, написания хинтов и прочие пляски. А как сказать заказчику после перезапуска сервера: "Потерпите, сервер кеш накопит и опять будет все быстро"? Именно в этот момент после вынужденного простоя он хочет все получить быстро.
    5 апр 05, 10:19    [1440687]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    softwarer
    Member

    Откуда: 127.0.0.1
    Сообщений: 67463
    Блог
    MasterZiv
    Как-то в поледнее время гложит мысль, что cost-based оптимизатор - не обязательно есть польза.

    ..... если используется в том стиле, в котором привыкли использовать rule-based.

    Между ними есть одна ключевая разница: rule-based держит план запроса "раз и навсегда" (если не меняется структура базы). Таким образом, его оптимизация сводится к "отпинали ногами, заставив работать по нужной схеме". И до тех пор, пока схема остается нужной - все неплохо. Cost-based, наоборот, адаптирует план к мелким вроде бы изменениям, и оптимизация сводится к тому, чтобы дать ему возможность для правильных маневров, убирая в то же время возможность для неправильных. "Отпинать ногами" с ним не работает.

    Если CBO неудачен (в конкретном сервере, для конкретной задачи итп) - лучше пользоваться RBO, возможно, подключая CBO для отдельных случаев, где он даст выигрыш. Если же он хорош - надо учиться давать ему возможность принять правильное решение.
    5 апр 05, 10:21    [1440701]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    В DB2 c этим делом гораздо проще. Там оптимизатор срабатывает только в том случае если используется динамический SQL или пакет связан с базой данных при помощи опции REOPT. Т.е. как правило нет гемороя с изменением планов запросов по волатильным табицам (которые часто меняют свой объем и статистику) Достаточно один раз написать запрос и он будет таким всегда. Т.е. то что в Оракле называется "стабилизацией плана запроса", или написание абстрактых планов запросов как в Sybase ASE в DB2 вообще не нужно.
    5 апр 05, 10:43    [1440832]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    softwarer
    Member

    Откуда: 127.0.0.1
    Сообщений: 67463
    Блог
    gardenman
    Т.е. как правило нет гемороя с изменением планов запросов по волатильным табицам (которые часто меняют свой объем и статистику) Достаточно один раз написать запрос и он будет таким всегда. Т.е. то что в Оракле называется "стабилизацией плана запроса", или написание абстрактых планов запросов как в Sybase ASE в DB2 вообще не нужно.

    Честно говоря, я пока что не сталкивался с необходимостью как раз "стабилизировать план запроса" - и у меня есть ощущение, что это сделано в основном ради тех, кто никак не перейдет с седьмой версии оракла :)

    Безусловно, иногда бывают временные таблицы, которые могут наполняться миллионами записей - на это есть DYNAMIC_SAMPLING, есть ручная установка статистики - справиться можно.

    А, да. Если "стабилизация планов запросов" - мера, которая поможет на серверах с, назовем так, непредсказуемым администрированием. Там, где администратор относится к категории "молодых экспериментаторов", это может помочь :)
    5 апр 05, 10:58    [1440922]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    2 softwarer
    Я очень хочу разобраться в одном вопросе. Как часто срабатывает оптимизатор? И имеет ли смысл засталять его срабатывать на каждый чих?
    Я, например, в этом вопросе полностью поддерживаю IBM. Пакет скомпилен, план по нему построен, и, как правило в 99,99% случаев этот план останется тем же самым, несмотря на то, что параметры запроса могут поменяться. Т.е. работа оптимизатора просто не нужна. (это операторы типа:
    select * from table where id=:host_variable, - доступ чисто по индексу, или join 2-3 таблиц). Проще просто хранить уже известный план запроса.
    А для оставшегося 0,01% и для хранилищ данных, где хранение плана не имеет смысла - пользовать REOPT или динамический SQL.
    5 апр 05, 11:12    [1440979]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    softwarer
    Member

    Откуда: 127.0.0.1
    Сообщений: 67463
    Блог
    gardenman
    Я очень хочу разобраться в одном вопросе. Как часто срабатывает оптимизатор?[/q]
    Это зависит от задачи. Например, в любой задаче, ориентированной на ad-hoc queries, оптимизатор может и должен срабатывать на каждый чих. С другой стороны, в обычной OLTP - когда я как-то взглянул на статистику production-а, нашел запросы с execution count в сорок-пятьдесят миллионов раз (при единственном срабатывании оптимизатора).

    [quot gardenman]И имеет ли смысл засталять его срабатывать на каждый чих?

    Это вопрос, на который надо давать свой ответ для каждого запроса.

    gardenman
    Я, например, в этом вопросе полностью поддерживаю IBM. Пакет скомпилен, план по нему построен, и, как правило в 99,99% случаев этот план останется тем же самым, несмотря на то, что параметры запроса могут поменяться. Т.е. работа оптимизатора просто не нужна. (это операторы типа:

    Давайте я отмечу два недостатка, которые вижу в этом случае по сравнению с оракловым подходом:

    - план фиксируется в тот момент, когда статистика данных очень далека от реальности (той, которая будет после продолжительной эксплуатации). Если не делать профилактических перекомпиляций - на этом многое потеряется именно из-за невозможности предусмотреть развитие системы.

    Напротив, в Oracle план редко, но перестраивается; раз в несколько недель, но подвергается пересмотру. Полагаю, раз в несколько недель переоптимизировать - копеечные затраты вообще и нулевые - по сравнению недобором скорости на каждом запросе, выполняющемся по плану нескольколетней давности.

    - иногда случаются проблемы. Например, индекс может пострадать в результате сбоя на диске. При "хранимом раз и навсегда" плане это приведет к ошибке при выполнении запроса; гибкий оптимизатор просто перестроит план, исключив поврежденный индекс. Разумеется, новый план может оказаться и очень плохим - но имеет все шансы оказаться "приемлимым", и система будет работать, пока чинят индекс.

    gardenman
    select * from table where id=:host_variable, - доступ чисто по индексу, или join 2-3 таблиц). Проще просто хранить уже известный план запроса.

    Oracle, собственно, так и делает. Причем - в чем прелесть - я могу управлять этим процессом; если я напишу

    execute immediate 'select * from table where f1 = :f1 and f2 = ' || f2 ....
    

    Этот запрос будет строить план для каждого нового значения f2 (использовать гистограммы), но в то же время повторно использовать план для любого значения f1.

    При этом - обратите внимание, SQL динамический. Если сделать аккуратно - один и тот же план будет использоваться и для запроса, выполняемого таким образом, и для того же запроса, написанного в коде хранимки, и для того же запроса, пришедшего с клиента.


    А для оставшегося 0,01% и для хранилищ данных, где хранение плана не имеет смысла - пользовать REOPT или динамический SQL.

    А вообще-то я вчера присутствовал на презентации DB2. Надо признать, очень грамотно рассказывали, захотелось повозиться.
    5 апр 05, 11:34    [1441064]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    softwarer
    Member

    Откуда: 127.0.0.1
    Сообщений: 67463
    Блог
    К вопросу "зачем нужно редактирование сообщений" :) Еще раз, в более читаемом формате.

    gardenman
    Я очень хочу разобраться в одном вопросе. Как часто срабатывает оптимизатор?

    Это зависит от задачи. Например, в любой задаче, ориентированной на ad-hoc queries, оптимизатор может и должен срабатывать на каждый чих. С другой стороны, в обычной OLTP - когда я как-то взглянул на статистику production-а, нашел запросы с execution count в сорок-пятьдесят миллионов раз (при единственном срабатывании оптимизатора).

    gardenman
    И имеет ли смысл засталять его срабатывать на каждый чих?

    Это вопрос, на который надо давать свой ответ для каждого запроса.

    gardenman
    Я, например, в этом вопросе полностью поддерживаю IBM. Пакет скомпилен, план по нему построен, и, как правило в 99,99% случаев этот план останется тем же самым, несмотря на то, что параметры запроса могут поменяться. Т.е. работа оптимизатора просто не нужна. (это операторы типа:

    Давайте я отмечу два недостатка, которые вижу в этом случае по сравнению с оракловым подходом:

    - план фиксируется в тот момент, когда статистика данных очень далека от реальности (той, которая будет после продолжительной эксплуатации). Если не делать профилактических перекомпиляций - на этом многое потеряется именно из-за невозможности предусмотреть развитие системы.

    Напротив, в Oracle план редко, но перестраивается; раз в несколько недель, но подвергается пересмотру. Полагаю, раз в несколько недель переоптимизировать - копеечные затраты вообще и нулевые - по сравнению недобором скорости на каждом запросе, выполняющемся по плану нескольколетней давности.

    - иногда случаются проблемы. Например, индекс может пострадать в результате сбоя на диске. При "хранимом раз и навсегда" плане это приведет к ошибке при выполнении запроса; гибкий оптимизатор просто перестроит план, исключив поврежденный индекс. Разумеется, новый план может оказаться и очень плохим - но имеет все шансы оказаться "приемлимым", и система будет работать, пока чинят индекс.

    gardenman
    select * from table where id=:host_variable, - доступ чисто по индексу, или join 2-3 таблиц). Проще просто хранить уже известный план запроса.

    Oracle, собственно, так и делает. Причем - в чем прелесть - я могу управлять этим процессом; если я напишу

    execute immediate 'select * from table where f1 = :f1 and f2 = ' || f2 ....
    

    Этот запрос будет строить план для каждого нового значения f2 (использовать гистограммы), но в то же время повторно использовать план для любого значения f1.

    При этом - обратите внимание, SQL динамический. Если сделать аккуратно - один и тот же план будет использоваться и для запроса, выполняемого таким образом, и для того же запроса, написанного в коде хранимки, и для того же запроса, пришедшего с клиента.


    А для оставшегося 0,01% и для хранилищ данных, где хранение плана не имеет смысла - пользовать REOPT или динамический SQL.

    А вообще-то я вчера присутствовал на презентации DB2. Надо признать, очень грамотно рассказывали, захотелось повозиться.[/quot]
    5 апр 05, 11:39    [1441089]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    2 softwarer
    Спасибо, очень грамотно рассказали как это происходит в Оракле.
    5 апр 05, 11:54    [1441156]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    2 MasterZiv
    на предмет отсутствия хинтов в DB2 гляньте сюда, правда не могу сказать что это (хинт или не хинт, и как это вообще называть), но план запроса можно изменить весьма конкретно
    5 апр 05, 12:01    [1441186]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34709
    AAron

    кстати, жесткое прибивание плана как раз есть не лучший вариант. при изменении статистики точно начнутся танцы с бубном. Что собственно и наблюдается.


    Господа, мы как бы в курсе всех тех страшилок, которые сулятся всем тем, кто жестко фиксирует план запроса. Также мы в курсе как пользоваться статистическими оптимизаторами. Мы умеем им пользоваться.
    Проблема не в этом, а в том, что такой оптимизатор всегда действует стохастически, т.е. по вероятностным принципам. А надо ли это ?
    Не всегда.

    Я хотел бы попросить выступивших здесь рассказать немного о том, как (в какие моменты и при каких условиях) происходит оптимизация запросов в DB2, и как устроена стабилизация плана запроса в Oracle.
    5 апр 05, 12:57    [1441441]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    Yo!!
    Guest
    автор
    Я хотел бы попросить выступивших здесь рассказать немного о том, как (в какие моменты и при каких условиях) происходит оптимизация запросов в DB2, и как устроена стабилизация плана запроса в Oracle.


    все у дб2 примерно также, просто у оракла есть крайний вариант, когда уже ничто не спасает хинты.

    https://www.sql.ru/forum/actualthread.aspx?bid=5&tid=94928
    5 апр 05, 13:02    [1441476]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    Если рассмотреть классический подход к разработке приложений в DB2, то все выглядит примерно так:

    Пишем исходник на C/C++, FORTRAN,COBOL, Java с вложенным SQL.
    Будем рассматривать С++. такой файл должен иметь расширение *.sqx (lWindows) или *.sqC (*NIX)
    Обрабатываем препроцессором DB2.

    db2 prep test.sqx bindfile test.bnd [куева хуча опций, такаих как уровень изоляции уровень оптимизации и пр..]

    В результате получаем еще два файла с расширениями *.cxx и *.bnd

    Файл *.cxx - собственно сайл с кодом С++ который компилим и линкуем любым доступным с++ компилером. а файл *.bnd - содержит SQL, во внутренем предствалении DB2.
    Затем выполняем связываение DB2 и файлов *.bnd проекта, типа:
    db2 bind test.bnd [куча опций] - как раз на этом этапе и формируется план запроса. в системном каталоге DB2 имеется таблицы SYSIBM.SYSPLAN, SYSIBM.SYSPLANAUTH, SYSPLAN.SYSPLANDEP из наименований которых вполне понятно за что каждая из них отвечает. План запроса хранится в SYSIBM.SYSPLAN в двоичном виде, и посмотреть его можно при помощи утилит db2exlpn или db2exfmt (выдает слишко много инфы).
    Динамический SQL делается примерно также, (но я им как правило не пользуюсь - это слишком медленно для ОЛТП) создается временный пакет с соответствующим ему планом выполнения.

    В вамом же файле *.cxx полученном после обработки препроцессором ВООБЩЕ не содержится никакого SQL, а только вызов нужного оператора ко его идентификатору в пакете.
    В результате достигается
    1)не надо на каждый чих писать ХП (в понимании MSSQL и Sybase) . В сущности связанный пакет и есть как-бы ХП. Поэтому ХП в ДБ2 используются не с такой интенсивностью как в других базах, потому как и так неплохо работает.
    2)Код компилируется, и на этапе компиляции проверяются ошибки - если есть ошибки то просто ничего не откомпилится. Возрастает надежность.
    3) План выполнения уже известен до запуска оператора - увеличивается скорость выполнения.
    4) Не надо согласовывать код написанный в приложении с кодом на сервере (ведь все в одном месте)

    Есть еще и такое понятие как версионность пакетов. Но об этом я уже не буду.

    Еще будут вопросы?
    5 апр 05, 14:39    [1441975]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    Yo!!
    Guest
    допустим написан проэкт, скомпилирован, запущен в продакшен. туда полились данные, пора менять план. кто отслеживает вот это "пора" ? дба в ручную или какой-то процесс cубд ? это просто SYSIBM.SYSPLAN меняется или что-то более суровое ?

    автор

    2)Код компилируется, и на этапе компиляции проверяются ошибки - если есть ошибки то просто ничего не откомпилится. Возрастает надежность.


    а если ошибки появились после компиляции ? удалили/изменили табличку, сгорел диск и кусок дб недоступен и т.п. есть ли понятие valid/invalid для процедуры ?
    5 апр 05, 15:38    [1442302]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    nkulikov
    Guest
    2Yo!
    Есть valid/invalid не только для процедуры.

    Есть такое понятие как autonomics. Который говорит как, когда, какую статистику собирать. Никто не мешает этим не пользоваться ибо все autonomics возможности берут примерно 6-7% производительности и делать все админстратор
    5 апр 05, 16:33    [1442600]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    gardenman
    Member

    Откуда: С-Петербург
    Сообщений: 2347
    2 Yo!
    Обратите внимание на табличу SYSPLAN.SYSPLANDEP , имя которой я бы расшифровал как Sys Plan Dependiences (Зависимости)
    5 апр 05, 16:41    [1442648]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    Yo!!
    Guest
    т.е. как и в оракле хранятся все зависимости между объектами и если один объект исчезает то зависимые помечаются как invalid, так ? иначе получается что при неком событии какой-то процесс прочесать все файлики *.bnd и просмотреть не фигурирует ли там измененый объект.

    а по дефолту autonomics включены ? админ конечно кекс конечно башковитый, но следить ежедневно за изменениями в тысячах объектах субд думаю трудновато.
    5 апр 05, 16:53    [1442708]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    softwarer
    Member

    Откуда: 127.0.0.1
    Сообщений: 67463
    Блог
    nkulikov
    Есть такое понятие как autonomics. Который говорит как, когда, какую статистику собирать.

    Насколько я понимаю, это тот самый Query Patroller, о котором ты вчера рассказывал? ;-)
    5 апр 05, 16:56    [1442723]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    nkulikov
    Guest
    Нет. Это совсем другое. :)

    Query Patroller - это инструмент управления нагрузкой (workload)

    Автономные возможности - это когда DB2 сама определяет и запускает (или уведомляет о необходимости) backup, runstats (по каким таблицам и по каким параметрам), rebalance, reorg.
    5 апр 05, 18:40    [1443096]     Ответить | Цитировать Сообщить модератору
     Re: cost-based optimizers  [new]
    MasterZiv
    Member

    Откуда: Питер
    Сообщений: 34709
    Спасибо, gardenman, все в общем понятно.
    Т.е. я так понял, если "переводить на язык" других серверов, то можно сказать, что в DB2 "хранимые процедуры" (на самом деле их аналоги) оптимизируются только один раз - при их создании. Правда при этом не понятно, какие значения параметров запроса используются для получения статистики. Как с этим ?

    Однако хотелось бы также услышать как это происходит в динамическом SQL.
    Пока я так понял, что происходит то же самое, только создается временный "пакет" а затем он уничтожается. Т.е. запрос оптимизируется каждый раз.

    Вот например, я из Exel-я буду по ODBC запрос посылать ... как будет ?

    Вообще, я уже понял, что DB2 - странный сервер.
    5 апр 05, 18:47    [1443114]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Сравнение СУБД Ответить