Заметки ораклового молаписта

Наконец, выдалась свободная минутка написать.

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

Итак, сегодня хотелось бы поговорить о возникшей на днях проблеме. Выглядела она следующим образом:

ORA-12801: error signaled in parallel query server P007
ORA-08103: object no longer exists

И возникала в процессе исполнения MERGE куска данных в таблицу. Около 20 млз в 160 млз. На таблице был построен набор битмап индексов. Табличное пространство ASSM.

В нашей практике данная ошибка встречается довольно часто. Oracle имеет ряд багов на эту тему.

Обычно это лечится пересозданием битмап индексов. Думаю, что как вариант можно сделать им rebuild online.

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

1.      В каком-то обновляемом блоке были проблемы, однако при переносе в другое ТП, они были исправлены.

2.      В исходном ТП было недостаточно места. Похоже, что истинной причиной ошибки была как раз проблема со свободным пространством. Неясно, почему ошибка выглядела именно таким образом.

Вот так.

- Данные для merge(update): t_update

T(M_iu)=t_jx(N_i+N_u+N_s)+t_rx(N_i+N_u+N_s)+t_uxN_u+t_ixN_i

T(M_u)=t_jx(N_u+N_s)+t_rx(N_u+N_s)+t_uxN_u

T(I)= t_rxN_i+t_ixN_i

Таким образом

T(M_iu)-(T(M_u)+ T(I)) =

=t_jx(N_i+N_u+N_s)+ t_rx(N_i+N_u+N_s)+t_uxN_u+t_ixN_i – (t_jx(N_u+N_s)+t_rx(N_u+N_s)+t_uxN_u+t_rxN_i+t_ixN_i)=

=t_jx(N_i+N_u+N_s) – t_jx(N_u+N_s) = t_jxN_i

Где:

T(M_iu) – время операции merge(i/u)

T(M_u) – время операции merge(u)

T(I) – время операции insert

t_j – усредненное время джойна для одной записи

t_r – усредненное время чтения одной записи

t_u – усредненное время update одной записи

t_i – усредненное время insert одной записи

N_i – количество записей для insert

N_u – количество записей для update

N_s – количество записей в обновляемой таблице

То есть, merge(i/u) должен быть медленнее на время джойна количества записей для insert.

Это в теории. На практике совершенно иначе. Даже если количество записей для insert равно 0 (Ni=0), то время merge(i/u) значительно превышает время merge(u). Следовательно, гипотеза неверна.

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

Объяснение этого можно найти, если сделать 10104 (hash join statistics) трассировку выражений merge(i/u) и merge(u). Пример этих трассировок можно найти в приложении. Также это можно увидеть в представлении v$session_longops, вырезка из которой приведена в таблице 1. Ситуация следующая: при указании в операторе merge условий и insert, и update, при выполнении оператора, производится два джойна подряд. Также интересно, что оба джойна являются OUTER JOIN. При этом, каждый их этих джойнов проходит значительно быстрее джойна в merge(update). По результатам наших экспериментов, время двух джойнов в выражении merge(i/u) почти равно времени джойна в merge(u). Но необходимо не забывать, что в merge(i/u) все таблицы, участвующие в выражении читаются два раза.

Таким образом:

T(M_iu)= t_jx(N_i+N_u+N_s)+2xt_rx(N_i+N_u+N_s)+t_uxN_u+t_ixN_i

T(M_iu)-( T(M_u)+ T(I))= t_jxN_i+ t_rx(N_i+N_u+N_s)

Это выражение уже гораздо ближе к реальности.

Безусловно, измерение в секундах, имеет огромное количество недостатков. Гораздо важнее показатели CPU, IO и т.п. Но, повторюсь, сейчас ставлю перед собой задачу исключительно исследовать тенденцию. Для любителей же поанализировать статистику, приведу частичный пример для i,u(0,1000000) сравнение операций merge(u) и merge(i/u) в таблице 3.

Также стоит отметить, что была попытка запуска алгоритма при Ni = 0 и Nu = 100.000.000. В этом случае merge(u) выполнялся около 3-х часов, а merge(i/u) около 7-и часов. Т.е. в случае отказа от использования merge(u/i), на нашем оборудовании можно было бы выиграть около 4-х часов в случае обновления порядка 100 млн. записей.

Еще свел список некоторых исправленных багов на металинке в выражении MERGE в таблице 2.

Ниже приведены графики времени выполнения соответствующих опреаций, а также график преимущества операций I+Mu над Miu.

Пакет при помощи которого производился эксперимент и прочие файлы вышлю по запросу на почту egor.osipov[цобачко]billing.ru

Вырезка из v$session_longops Таблица 1

Opname	Target	Target_desc	TotalWork	Units	Elapsed_seconds
Information		Merge completed	0	info	0
Hash Join			469575	Blocks	786
Table Scan	T_ium		371476	Blocks	479
Table Scan	T_merge		32688	Blocks	35
Hash Join			469575	Blocks	628
Table Scan	T_ium		371476	Blocks	365
Table Scan	T_merge		32688	Blocks	39
Information		Merge started	0	info	0
Information		Update Completed	0	info	0
Hash Join			469575	Blocks	1194
Table Scan	T_ium		371476	Blocks	262
Table Scan	T_merge		32688	Blocks	31
Information		Update started	0	info	0

INSERT
Сумма по полю Sec	Ins
Upd	0	1000	10000	100000	1000000	10000000
10000000	0,003	0,07	0,176	0,122	1,094	9,59
1000000	0,002	0,017	0,021	0,115	1,048	9,749
100000	0,002	0,014	0,019	0,116	1,039	9,595
10000	0,002	0,024	0,032	0,108	1,072	9,926
1000	0,002	0,033	0,036	0,144	0,967	10,051
0	0,002	0,041	0,024	0,139	1,068	9,842

UPDATE
Сумма по полю Sec	Ins
Upd	0	1000	10000	100000	1000000	10000000
10000000	1409,773	1345,477	1527,403	1369,143	1515,165	1624,855
1000000	132,465	105,988	105,018	103,534	99,019	200,827
100000	42,853	50,684	42,962	48,343	39,869	79,14
10000	32,491	29,843	39,321	42,173	29,327	40,483
1000	26,531	26,341	27,136	26,617	27,257	46,443
0	0,005	0,003	0,003	0,004	0,003	0,003

MERGE
Сумма по полю Sec	Ins
Upd	0	1000	10000	100000	1000000	10000000
10000000	2245,225	2377,532	2375,714	2256,942	2307,594	2794,332
1000000	180,662	179,926	182,774	214,046	447,038	1060,22
100000	83,182	111,373	108,911	120,021	121,979	1168,561
10000	70,292	59,273	61,198	86,044	114,056	419,1
1000	54,088	52,992	55,005	74,97	106,443	412,714
0	0,009	55,482	55,265	72,643	106,133	383,344

M-(U+I)
Сумма по полю Sec	Ins
Upd	0	1000	10000	100000	1000000	10000000
10000000	2245,22	2377,391	2375,362	2256,698	2305,407	2775,152
1000000	180,657	179,892	182,732	213,816	444,942	1040,721
100000	83,178	111,346	108,873	119,788	119,902	1149,37
10000	70,288	59,224	61,134	85,829	111,912	399,248
1000	54,084	52,926	54,932	74,681	104,508	392,612
0	0,004	55,399	55,217	72,364	103,998	363,66

И еще несколько удобных скриптов. Стоит заметить, что все скрипты, требующие вызова CONTROL CENTER’а выполняются достаточно долго. Одним из методов их ускорения может являться (в случае деплоя или андеплоя группы маппингов)  групповая процедура. Для этого необходимо при создании плана указать сразу все необходимые маппинги.

Скрипт деплоя маппинга. Запускается при нахождении в модуле. В этом скрипте интересным моментом является то, что при ошибке в деплоее, не генерируется исключения. Т.е. нельзя использовать стандартный механизм if { [catch {….}  erm ] > 0 } {.…}. Решение проблемы видно в скрипте.

Скрипт андеплоя маппинга. Запускается при нахождении в модуле. Применяется для удаления пакета из БД.

Ну и просто удаление маппинга. Запускается при нахождении в модуле.

Мне периодически приходят благодарности за публикацию этих скриптов.  Самое забавное, что недавно пришло письмо от человека, который не знает русского, поэтому не понял что написано в блоге, однако сами скрипты ему очень пригодились. Значит, потребность в них есть. Поэтому сегодня решил опубликовать еще пару скриптов OMB+. Не уверен, что многие будут их использовать именно в этом  виде, однако уверен, что эти скрипты будут полезны в качестве наглядного пособия по изучению предмета.

При выгрузке модели из OWBмы установили ряд требований к этим проектам. Требования диктовались внутренней спецификой, однако, вот они:

1.   Business name всегда должно соответствовать physicalname.

Причина этого заключается в том, что часто маппинги создаются методом копирования из другого маппинга и некоторой модификацией его. Результатом такой практики становится то, что в итоге появляются объекты с businessname соответствующим physical name уже существующего объекта. Эта проблема возникает, когда необходимо сделать обновление нескольких маппингов на конечной базе. В любом случае, стоит быть уверенным, что вы контролируете названия своих объектов.

2.   Target Load Ordering на маппингах должен быть выключен.

Так как эта опция входит только в EEверсию, мы от нее отказались. И соответственно, использовать ее мы не имеем права. Таким образом, есть необходимость проверять отсутствие ее на маппингах.

3.   В модели должны отсутствовать индексы и партиции в таблицах.

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

Итак, задача стоит написать процедуру, на вход которой передается название проекта, а она проверяет соответствующие характеристики и выдает предупреждение, если что-то не соответствует нашим требованиям. Также процедура в случае необходимости должна исправлять найденные ошибки.  Процедура запускается перед экспортом, и при необходимости в нее можно добавить необходимые проверки. Стоит обратить внимание, что везде для вывода сообщение используется процедура log. Её можно безболезненно заменить процедурой puts, если вывод должен осуществляться только на экран. Однако, гораздо интереснее, использовать процедуру, которая будет вскоре описана здесь.

Таким образом, можно написать следующую процедуру:

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

А вот пример их использования:

Всем, добрый день! К моему сожалению, обстоятельства не позволяли мне обновлять блог все это время. Сегодня хочу предоставить Вашему вниманию очередную статью. В этот раз она посвящена выражению MODEL, так незаслуженно обделенному вниманием многих разработчиков. Я постараюсь это исправить. К сожалению, статья не является законченной, но т.к. она лежит у меня на диске уже больше года, я все же решил опубликовать то, что есть.

MODEL – выражение позволяющее представлять результат запроса в виде многомерной модели и позволяющее производить с ней операции характерные для многомерных сред. Суть его очень проста – выражение MODEL позволяет нам представить результат запроса в виде многомерного куба и задавать выражения для расчета его произвольных ячеек. Выражение SQL MODEL очень сильно напоминает по смыслу объект MODEL в Oracle Olap – также задающего правила расчета ячеек куба. Поскольку, я расскажу лишь об основах этого выражения, за всеми подробностями обращаться прямо в документацию (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm)

Выражение MODEL особенно удобно применять в случае построения отчетов. Причина этого в том, что из-за многомерной структуры его, оно будет очень чувствительно к большому количеству данных. Тем не менее, при помощи него очень удобно обрабатывать уже агрегированные результаты отчетов. Отличным примером будет необходимость дополнительно вывести в помесячном отчете о продажах за год строчку о сумме продаж за несколько произвольных месяцев.

Начнем с простых вещей. Для начала определим многомерную модель.

Многомерная модель определяется следующим образом: указываем, какие колонки запроса рассматривать как измерения(dimensions), а какие как показатели(measures).

При необходимости, куб может быть партиционирован(partitioned) по набору колонок:

Стоить заметить, что возможность партиционирования особенно полезна при параллельной обработке.

В этих трех полях должны быть перечислены все колонки, участвующие в SELECT. Интересной особенностью здесь является возможность определения исходно несуществующих измерений. Это означает, что возможна такая ситуация:

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

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

Теперь перейдем к правилам(rules).

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

Теперь рассмотрим выбор ячеек. Каждая ячейка уникально определяется уникальным набором меток – элементов измерения и задается следующим выражением:

 Т.е. для указания одной ячейки нужно по каждому измерению выбрать один элемент. Порядок указания ссылок на элементы измерений определяется порядком измерений в выражении DIMENSION BY. Существует два типа меток  – позиционные и символические.

1. Позиционные(positional) метки. Позиционная ссылка – это константа, уникально определяющая элемент измерения. Например, meas[‘Элемент1’,…]. В данном случае ‘Элемент1’ – это позиционная метка. При использовании этого типа меток выбираются ячейки у которых соответствующее измерение = значению ссылки.

2. Символические(symbolic) метки. Символическая метка – это выражение, имеющее логический тип. При использовании этого типа меток будут выбраны только те ячейки, для которых результат выражения равен true. Например, meas[dim1 > 10,…]. В данном случае будут выбраны те ячейки, у которых значение элемента измерения > 10. В общем случае, не обязательно указывать выражение относительно именно соответствующего измерения. Это может быть абсолютно любое логическое выражение. Например, допустимо следующее: meas[15 > 10,…].

Набор меток и показатель – является ссылкой. Ссылки делятся на одиночные и множественные.

1. Одиночные(single) ссылки – это такие ссылки, которые уникально определяют ячейку. Например, ссылки, измерения которых определяются исключительно позиционными метками – всегда одиночные. Также ссылка остается одиночной, если кроме позиционных меток, используются уникальные символические метки. Например: meas[10 , dim2=’Элемент2’, …].

2. Множественные(multi-cell) ссылки – это такие ссылки, которые определяют набор ячеек куба. Ссылка может быть позиционной, если применяются символические метки, определяющие более одного элемента измерения. Например: meas[dim1 > 10,  dim2=’Элемент2’, …].

 Рассмотрим пример простейшего правила:

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

Т.е. продажи определены только для Shop1. Тем не менее, мы знаем, что в магазине Shop2 каждого товара было продано в два раза больше. Используя выражение MODEL эту задачу можно решить следующим образом:

Результат будет следующим:

Все хорошо пока товаров 2. Если их 100 – писать подобные правила быстро надоест. В таком случае на помощь приходит функция CV() – current value - текущее значение. Эта функция используется в правой части правила и возвращает текущее значение элемента измерения в левой части. Без параметров она возвращает текущее значение того измерения, в позиции которого она указана. Пример: meas[CV(), …]. Здесь CV() вернет текущее значение первого измерения. В качестве параметра функции CV() можно задавать название измерения, текущее значение которого необходимо вернуть. Здесь может быть не совсем понятно, что означает текущее измерение. Давайте рассмотрим на примере.

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

При этом известно, что SecondGood  во всех магазинах кроме 1-го продано в 4 раза больше чем FirstGood. В перовом же магазине – в 5 раз больше.

Сначала, разберем первое правило. Как видно – в левой части стоит множественная ссылка, которая указывает на набор ячеек, у которых товар = ‘SecGood’, а номер магазина > 1. В таком случае (если в левой части выбирается насколько ячеек), каждой из выбранных ячеек по очереди присваивается значение, вычисляемое в правой части. Т.е. это выражение можно представить в итерационной форме следующим образом:

Таким образом, функция CV() – возвращает текущее значение выбранного поля на данной итерации. Соответственно в результате таблица будет выглядеть слеудющим образом:

Также, CV() может принимать в качестве аргумента – название измерения. Тогда возвращать она будет значение не текущего измерения, а переданного в качестве параметра.

Если же необходимо указать однородное по измерению правило,  т.е. такое правило, где для элементов со всеми значениями измерения, необходимо указывать значения, стоит использовать выражение ANY. Также допустимо выражение вида dim1 IS ANY.  Стоит только помнить, что ANY является символической ссылкой, так как фактически интерпретируется как dim IS NULL or dim IS NOT NULL, даже, если оно указывается позиционно.

Если переформулировать предыдущую задачу в следующий вид:

При этом известно, что SecondGood  во всех магазинах кроме 1-го продано в 4 раза больше чем FirstGood.

То правило будет выглядеть следующим образом:

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

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

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

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

Если бы в исходных данных уже содержались бы данные по TotalYears,  и было бы необходимо их затереть, то правило выглядело бы следующим образом:

Таким образом, на выходе мы получим:

Вообще, что касается правой части, то тут могут стоять практически любые выражения. Например, NVL, CASE, DECODE  и т.д.

Первый скрипт IOC просто копирует все входящие аттрибуты оператора из одной группы в другую. Это удобно для построения выражений.

Скрипт CPEXPR позволяет скопировать выражение из одного маппинга в другой.

Скрипт UPDNAME просто позволяет произвести замену по рег.выражению названий аттрибутов.

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

Параметры таблицы в маппинге

Параметры представления в маппинге

Параметры аггрегатора в маппинге

Параметры ПРЕ- и ПОСТ- маппинг процедур.

Параметры локейшнов

Параметры аттрибутов

Параметры маппингов

Параметры модулей

Скрипт UPDMAPCONF позволяет в выбранном проекте и модуле для всех маппингов устанавливать необходимые свойства.
В данном скрипте устанавливаются следующие параметры:
GENERATION_LANGUAGE = PLSQL
GENERATION_MODE = SET_BASED
ENABLE_PARALLEL_DML = true
ANSI_SQL_SYNTAX = false
*В качестве названия проекта или модуля допускается ALL. В этом случае рассматриваются все проекты или модули соответственно.