Axapta 3.0 sp3+oracle 10.2.0.3 optimizer_index_cost_adj

[ring]

опытным путём было выявлено что параметр (optimizer_index_cost_adj) в каждой аксаптовской сессии(3-tier) меняется со 100(default) на 1 в связи с этим оптимизатор oracle всегда выбирает чтение по индексу, вместо full table scan что в моём случае нежелательно.Возвратить всё на круги своя можно через

begin
sys.dbms_system.set_int_param_in_session(SID, SERIAL, 'optimizer_index_cost_adj', 100);
end;
/
Но это конечно же не выход из положения=))) т.к. пользователей более 300...

Вопрос: почему акспата меняет deafult значение optimizer_index_cost_adj внутри какдой новой открытой сессии и как заставить её использовать нужное мне значение

[cryomice]

А почему бы не использовать sys.dbms_system в логон триггере?

[ring]

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

[fed]

Можешь попробовать посмотреть в файл axsysru.ktd и axsysen-us.ktd. Там помнится были волшебые слова, которые аксапта посылает на сервер в начале любой открытой сесии с ораклом или сиквелом (причем они конечно разные и для оракла и для сиквела). Не факт, правда, что тебе это поможет. Возможно они ДО index_cost_adj посылаются. Но попробовать стоит

[Logger]

Цитата:

#319
>MSG_SQL_POST_INITXAL
>SKIPTRANS 1-99
>
>This is the SQL PostInitNonSql-trigger, i.e. SQL statements executed
>automatically and unconditionally by the kernel.
>
>The SQL below is divided into database (kernel) specific sections,
>each section starting with an ID enclosed in brackets. The ID consists
>of the string 'SQLID' followed by the internal Microsoft Business Solutions-Axapta SQL database
>identification number (e.g. Oracle is '3', etc).
>
[SQLID3]
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
ALTER SESSION SET NLS_SORT = 'binary'
[SQLID1]

походу вот это имелось в виду.

Только index_cost_adj - там нет.

[fed]

Цитата:

Сообщение от Logger

походу вот это имелось в виду.

Только index_cost_adj - там нет.

Угу. Это в ax32serv.exe прописано. Если его в режиме Unicode открыть и посмотреть - то видно. Просто есть надежда что если в ktd-файле поковыряться - удастся дефолтное поведение перекрыть.

Надо будет, кстати, поискать волшебное ключевое слово в 2009ом ax32serv.exe.

[ring]

Цитата:

Сообщение от fed

Угу. Это в ax32serv.exe прописано. Если его в режиме Unicode открыть и посмотреть - то видно. Просто есть надежда что если в ktd-файле поковыряться - удастся дефолтное поведение перекрыть.

Надо будет, кстати, поискать волшебное ключевое слово в 2009ом ax32serv.exe.

дописал alter session set optimizer_index_cost_adj = 100 в axsysru.ktd в клиенте,зашёл
под 2-tier,значение изменилось=)))

З.ы. под 3-tier эффекта никакого

[fed]

Кстати - небольшой совет. Еще по опыту версии 2.1 я убедился, что если что-то в axsysru.ktd меняешь, то надо бы аналогичную правку и в прочих .ktd файлах сделать. Просто вроде бы системное соединение, которое ax32serv.exe открывает, использует то ли axsysen-us.ktd, то ли axsysen-uk.ktd. В общем - я во времена 2.1 на этом деле однажды кучу проблем поимел и просто стал во всех ktd-файлах не думая править...
P.S. А в трехзвенке надо править файлы на сервере. Я бы подправил и в application\bin и в server\bin

[gl00mie]

Цитата:

Сообщение от fed

Надо будет, кстати, поискать волшебное ключевое слово в 2009ом ax32serv.exe.

Есть оно там - никуда не девалось. Как пошло с первой версии 3-ки (а, может, и раньше) прописанное строковой константой

PHP код:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1 


так и в 2009-й версии сохранилось.

Цитата:

Сообщение от ring

дописал alter session set optimizer_index_cost_adj = 100 в axsysru.ktd в клиенте,зашёл под 2-tier,значение изменилось=)))

Очень интересно. Вообще, «изнутри» это все выглядит примерно так

bool SqlConnectBase::dblogin()
{
    // ...
    if (   SqlConnectBase::loginRun()
        && SqlConnectBase::loginSetupConnection()
        && ...
       )
         return true;
}

здесь все указанные методы SqlConnectBase не являются статическими, но лишь показаны для наглядности в связке с SqlConnectBase. Так вот, во-первых, обработка строк ktd идет в SqlConnectBase::loginRun(), а строка

PHP код:

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1 


отcылается на Oracle в методе SqlConnectBase::loginSetupConnection(), т.е. уже после обработки строк из ktd (хотя в последних версиях ядра что-то могло и поменться, надо проверить).

Цитата:

Сообщение от ring

хотелось бы узнать как это поменять в самой аксапте и вообще возможно ли это

как уже упоминалось, установка optimizer_index_cost_adj == 1 зашита в ядре в виде строкового литерала и в связи с этим значение optimizer_index_cost_adj в Аксапте не настраивается, однако, отправка на СУБД указанного SQL-запроса происходит отнюдь не безусловно, а примерно так (это упрощенный псевдокод, где SqlConnectBaseObj - экземпляр класса SqlConnectBase или производного от него):

if (    SqlSystem::databaseBackendId()    == DatabaseId::Oracle
     && SqlConnectBaseObj.parm().offs0x6A == true
   )
    SqlConnectBaseObj.executeDirectNoTrace("ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1");

но вот дальше докопаться уже сложней. Упоминание о SqlConnectBase удалось найти в патенте Microsoft №20070220155 «Server session management application program interface and schema», но, к сожалению, более детальных описаний упоминаемых там классов я не нашел, поэтому что за объект возвращает метод parm(), и что находится по смещению 0x6A этого объекта, выяснить не удалось. Между тем, видимо, если бы удалось "управлять" данным свойством возвращаемого объекта, то отправку указанного запроса на Oracle можно было бы предотвратить.

[ring]

К сожалению выставить желаемое значение optimizer_index_cost_adj под 3-tier так и не удалось ни с помощью редактирования ktd файлов ни даже с помощью LOGON триггера на оракле (странно но факт) , под 2-tier всё работает....

[Logger]

Выскажу крамольную мысль.
В указанной строке есть как раз пара пробелов. Можно попробовать заменить на нули. Догнать 1 до 100.

Как говорится если нельзя, но очень хочется, то можно

[ring]

Остаёться править ax32serv.exe =)

[Maximin]

В общем, взяли hiew и запатчили ax32serv.exe и axcom.dll - заработало и в 3х-уровневой.
Спасибо MS за оставленные пробелы до и после знака равно в строке "optimizer_index_cost_adj = 1"
Про необходимость правки axcom.dll - не уверен, правили везде "чтобы наверняка".
.ktd файлы можно не трогать - работает и без этого.
Для двухуровневой, соотв. необходим так же патченный клиент.
Upd: Уточню - axcom.dll необходимо править, чтобы сессия Axapta COM Connector тоже была с нужными параметрами.

[Maximin]

Апдейт: чтобы вообще сервер эту строку не отправлял, в файле ax32serv.exe версии 3.0.1951.3730
по адресу 000С1923 заменить байт 74 на EB. В формате .pth:
000С1923: 74 EB.
В остальных версиях нужно искать строку 8A 48 6A 84 C9 74 17 6A 00 6A и менять в ней 74 в середине, опять же, на EB.

С таким сервером всё работает так, как настроено в Oracle. Кстати, там же можно открутить параметр "QUERY_REWRITE_ENABLED".

[Logger]

Цитата:

Сообщение от Maximin

Кстати, там же можно открутить параметр "QUERY_REWRITE_ENABLED".

А это что за зверь ?

[ring]

Цитата:

Сообщение от Logger

А это что за зверь ?

One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

[fed]

Ну я бы не советовал отключать Query Rewrite. Насколько я помню, этот параметр кроме включения поддержки materialized view, еще и включает использование Function Based Indexes для оптимизации запросов. То есть - если у нас есть индекс по NLS_LOWER(CustAccount) и стоит запрос вида select * from salesTable where NLS_LOWER(custAccount)=NLS_LOWER('Рога и копыта'), то с включенным Query Rewrite индекс будет использоваться, а с выключенным - не будет (пойдет fullscan). Насколько я понимаю, если Аксапта строит индекс по комбинации полей типа строка и число (скажем - ItemId+StatusIssue+StatusReceipt), то в результате строится индекс по хитрому строковому выражению, конкатенирующему NLS_LOWER(itemId) и какие-то результаты преобразования StatusIssue и StatusReceipt в строку. Я совсем не уверен (хотя и не проверял), что Оракл будет в состоянии построить разумную статистику по подобной сложной функции, а затем использовать ее в оптимизации запросов. Таким образом, используя функциональные индексы мы лишаемся довольно заметной части оракловского CBO, поскольку связать статистику по функциональному индексу, гистограммы значений полей и конкретные значения параметров запроса довольно не легко. И вполне возможно, что манипуляции с Index_cost_adjustment были придуманы не просто от нечего делать, а для того чтобы условиях фактически неработающего CBO Оракла, скорректировать работу оптимизатора таким образом, чтобы он более охотно использовал функциональные индексы.
Интересно было бы проверить, насколько часто и корректно будет использовать функциональные индексы оракл в вашем случае, после того как вы выключите корректировки index_cost_adj...

Кстати - есть сильное подозрение, что флажек 0x6a, о котором пишет gl00mie, как раз говорит Аксапте, что надо использовать Function Based Indexes, а не старый (оставшийся со времен версии 2.1) механизм, который использовал обычные индексы по строковым полям, но при любой записи в поле, включенное в какой-нибудь индекс, конвертировал строку в нижний регистр.

[gl00mie]

Цитата:

Сообщение от fed

Насколько я понимаю, если Аксапта строит индекс по комбинации полей типа строка и число (скажем - ItemId+StatusIssue+StatusReceipt), то в результате строится индекс по хитрому строковому выражению, конкатенирующему NLS_LOWER(itemId) и какие-то результаты преобразования StatusIssue и StatusReceipt в строку.

По правде сказать, это как-то... неочевидно. Если посмотреть, как выглядит для Оракла, к примеру, \Data Dictionary\Tables\InventTrans\Indexes\StatusItemIdx, то получим следующий DDL-запрос (взято с рабочей базы):

PHP код:

CREATE INDEX "WORK"."I_177STATUSITEMIDX" 
    ON "WORK"."INVENTTRANS"  (SUBSTR(NLS_LOWER("DATAAREAID"),1,3),
    "STATUSRECEIPT", "STATUSISSUE", SUBSTR(NLS_LOWER("ITEMID"),1,20),
    "DATESTATUS") 
    TABLESPACE "WORKIDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 
    STORAGE (INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0) 
    NOLOGGING 


Цитата:

Сообщение от fed

Я совсем не уверен (хотя и не проверял), что Оракл будет в состоянии построить разумную статистику по подобной сложной функции, а затем использовать ее в оптимизации запросов. Таким образом, используя функциональные индексы мы лишаемся довольно заметной части оракловского CBO, поскольку связать статистику по функциональному индексу, гистограммы значений полей и конкретные значения параметров запроса довольно не легко. И вполне возможно, что манипуляции с Index_cost_adjustment были придуманы не просто от нечего делать, а для того чтобы условиях фактически неработающего CBO Оракла, скорректировать работу оптимизатора таким образом, чтобы он более охотно использовал функциональные индексы.

В общем, не подтверждаются пока эти подозрения. Кроме того, в «условиях фактически неработающего CBO Оракла» кому бы был нужен вообще этот Оракл?!

Цитата:

Сообщение от fed

Кстати - есть сильное подозрение, что флажек 0x6a, о котором пишет gl00mie, как раз говорит Аксапте, что надо использовать Function Based Indexes, а не старый (оставшийся со времен версии 2.1) механизм, который использовал обычные индексы по строковым полям, но при любой записи в поле, включенное в какой-нибудь индекс, конвертировал строку в нижний регистр.

Возникает еще один интересный вопрос: а почему Аксапта при работе с Ораклом до сих пор продолжает использовать функциональные индексы? Разве Оракл не поддерживает уже давным давно case-insensitive collation(s)?

[fed]

Отвечу по пунктам:
1. Оказывается мои воспоминания про формат индексов который в FBI индексах используется - неправильные
2. При работе с ораклом, Аксапта по дефолту строит статистику по каждому полю. Статистика по индексу строится в оракле автоматически. Однако же (опять таки - если я ничего не помню), для создания гистограммы необходимо ручками создавать статистику по комбинации полей (и я не уверен right now, что оракл позволяет создавать статистику по комбинации выражений, а не по комбинации полей).
3. Если гистограмм нет - то вероятность генерации некорректного плана запроса существенно возрастает. Хотя с другой стороны - гистограмм нету и для обычного составного индекса (даже не функционального). Так что для любого составного индекса, стоит попробовать ручками создавать статистику, поскольку иначе возрастает вероятность генерации кривого плана запроса.
Ну то есть - я не до конца уверен в своих предположениях по поводу Function Based Indexes, но у меня есть некоторое ощущение, что если не манипулировать с Index_cost_adj, то оптимизатор будет несколько чаще ошибатся и выбирать full scan вместо использования FBI-индекса.

По поводу case-insensitive - мне казалось эта штука поддерживается в оракле начиная с версии 11 ? И мне казалось (хотя я и не уверен), что как раз DAX 2009 должна использовать case-insensitive collation в случае использования именно 11 версии оракла.
Надо будет проверить короче говоря...

[Logger]

Цитата:

Сообщение от fed

По поводу case-insensitive - мне казалось эта штука поддерживается в оракле начиная с версии 11 ? И мне казалось (хотя я и не уверен), что как раз DAX 2009 должна использовать case-insensitive collation в случае использования именно 11 версии оракла.
Надо будет проверить короче говоря...

А вот это очень интересно!
Вы уж посмотрите плиз.

Обидно не использовать все возможности оракла.
Откроенно говоря я подозреваю, что мы никогда не увидим в Аксапте поддержки case-insensitive collation(s)

Невыгодно это Майкрософту. Хотелось бы ошибиться.