среда, 8 февраля 2017 г.

Oracle XMLTABLE example

Подробный пример использования XMLTABLE я нашел в IBM Developerworks :-)
Меня конкретно интересовала обработка множественных значений для одной ячейки (column path)

суббота, 21 января 2017 г.

Быстрый старт Oracle GG IExtract or IReplicat

Для быстрого старта Integrated Replicat или Extract в Oracle GG применяли такую оптимизацию internal SQL с помощью SQLPatch

DECLARE
    sql_text      clob := 'SELECT c.capture_name,      xo.queue_owner || ''.'' || xo.queue_name 
FROM all_xstream_outbound xo,all_capture c  WHERE xo.capture_name =
c.capture_name AND        server_name = SUBSTR(UPPER(''OGG$EXIEKS''),1,
30)'
;
    hints         VARCHAR2(1000) := 'PARALLEL(4)';
    description   VARCHAR2(100) := 'GG sql patch for IE';
    name          VARCHAR2(100) := 'GG_IE_SQL_PATCH';
    output        VARCHAR2(100);
    sqlpro_attr   sys.sqlprof_attr;
BEGIN
    sqlpro_attr := sys.sqlprof_attr(
        hints
    );
    output := sys.dbms_sqltune_internal.i_create_sql_profile(
        sql_text      => sql_text,
        profile_xml   => dbms_smb_internal.varr_to_hints_xml(
            sqlpro_attr
        ),
        name          => name,
        description   => description,
        category      => 'DEFAULT',
        creator       => 'SYS',
        validate      => true,
        type          => 'PATCH',
        force_match   => true,
        is_patch      => true
    );

    dbms_output.put_line(
        output
    );
END;
/

В итоге все запрос теперь выполняются с применением патча.


SELECT c.capture_name,       xo.queue_owner || '.' || xo.queue_name   FROM all_xstream_outbound xo, all_capture c  WHERE xo.capture_name =  c.capture_name AND        server_name = SUBSTR(UPPER('OGG$EXIEKS123'), 1,  30)
2017-01-15/11:43:21
GG_IE_SQL_PATCH
SELECT c.capture_name,       xo.queue_owner || '.' || xo.queue_name   FROM all_xstream_outbound xo, all_capture c  WHERE xo.capture_name =  c.capture_name AND        server_name = SUBSTR(UPPER('OGG$EXIEKS124'), 1,  30)
2017-01-15/11:43:52
GG_IE_SQL_PATCH
SELECT c.capture_name,       xo.queue_owner || '.' || xo.queue_name   FROM all_xstream_outbound xo, all_capture c  WHERE xo.capture_name =  c.capture_name AND        server_name = SUBSTR(UPPER('OGG$EXIEKS125'), 1,  30)
2017-01-15/11:43:58
GG_IE_SQL_PATCH


 Старт IExtract вместо 30 минут выполняется за 2 минуты после создания SQLPatch. 

воскресенье, 15 января 2017 г.

PlantUML


Сегодня через плагин к идее открыл для себя PluntUML. 
Крутота!
Я и не предполагал, что можно настолько удобно "изготавливать" или проектировать (термин "рисовать" совсем не подходит) в любом ТЕКСТОВОМ редакторе.

понедельник, 9 января 2017 г.

Проблемы при обслуживании секционированных таблиц

Проблемы при обслуживании секционированных таблиц


В проекте управления архивными данными была использована опция Oracle Partition. PSU - 11.2.0.4.7 Платформа - Oracle M6. SunOS 5.11

Столкнулись со следующими проблемами:

1. Unusable index segment not removed when index become unusable after partition operations
При выполнении операции alter table truncate partition/subpartition без опции update global indexes глобальные индексы должны были быть переведены в состояние UNUSABLE и удалены сегменты этих индексов. В состояние UNUSABLE индексы переведены, но сегменты не удалены. 
После этого перестроение индекса требует дополнительного пространства в TBS, т.к. при REBUILDе "старый" сегмент удаляется только после создания "нового". 
Проблема не очень большая, если размер индексов не велик. Но у нас размер одного глобального индекса доходит до 400G. И на для таких размеров поиск дополнительного свободного места в TBS - это уже проблема. 
Можно, конечно, придумать 1000 и 1 способ перестроения с выделением и дальнейшим освобождением использованного для перестроения места, но это все - дополнительные подпрыгивания, которых можно избежать, если перед  alter table truncate partition/subpartition  сделать alter index unusable.
Добавлю, что я не смог найти способа удалить существующий сегмент у unusable-индекса. Повторное изменение состояния на unusable не приводит к удалению существующего сегмента.

PS. Такое поведение выявлено для операции alter table truncate partition у субсекционированной таблицы. Возможно будет наблюдаться и в других вариантах операций обслуживания партиций.  

2. Alter table exchange partition stopped with error ORA-01407: cannot update ("SYS"."HIST_HEAD$"."COL#") to NULL
Исключение архивных данных из оперативной базы в проекте выполняется с помощью операции обмена секциями партиции с пустой таблицей. Делается это подобной командой:
ALTER /*+ MONITOR */ TABLE IBS.Z#DOCUM_RC EXCHANGE PARTITION (R_2015_10) WITH TABLE IBS.TT_J70NXQA2GAPKVTPNMJJA INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES  PARALLEL 128
Как видите, в exchange участвуют партиции локальных индексов.
В случае, если выполняются следующие условия:
- у секционированной таблицы присутствует функциональный индекс, 
- системные имена колонок функциональных индексов секционированной таблицы и таблицы, использующейся в exchange, отличаются (можно проверить в dba_ind_columns),
- по функциональному индексу собрана статистика,
то выполнение exchange завершится с ошибкой
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01407: cannot update ("SYS"."HIST_HEAD$"."COL#") to NULL

Очевидный баг работы со словарем, но в саппорте найти патча для этой операции не удалось.

Лечится удалением статистики с колонок функционального индекса перед операцией exchange. В моем случае пилюлька выглядит так:
exec                                       dbms_stats.delete_column_stats(ownname=>'IBS',tabname=>'Z#DOCUM_RC',colname=>'SYS_NC00169$');
exec                                       dbms_stats.delete_column_stats(ownname=>'IBS',tabname=>'Z#DOCUM_RC',colname=>'SYS_NC00168$');
exec                                       dbms_stats.delete_column_stats(ownname=>'IBS',tabname=>'TT_J70NXQA2GAPKVTPNMJJA',colname=>'SYS_NC00169$');
exec                                       dbms_stats.delete_column_stats(ownname=>'IBS',tabname=>'TT_J70NXQA2GAPKVTPNMJJA',colname=>'SYS_NC00170$');

3. Alter table exchange partition stopped with error ORA-19728: data object number conflict
Еще одна проблема с exchange. Возникает в случае одинаковых идентификаторов data_object_id у обменивающихся объектов.
В нас в проекте проблема возникла следующим образом:
- Была база данных с секционированным объектом.
- Создали копию этой базы и назвали ее архивом.
- Прошло какое-то время и теперь надо через transportable tbs перенести данные из оперативной БД в архив.
- В оперативной БД создаем "транспортную" таблицу и делаем exchange архивируемой партиции с этой таблицей. При exchange мы же просто делаем обмен сегментами, И в этом случае data_object_id у объекта не меняется. Т.о. мы получили транспортную таблицу с тем же самым data_object_id, что и соответствующая партиция в архивной БД.
- "Транспортную" таблицу мы не двигаем в другое TBS. (Заранее спроектировали хранение так, чтобы все лежало отдельно и не надо было заморачиваться с отделением архивных данных для переноса).
- Делаем экспорт TTBS в оперативной БД и импорт TTBS в архивной БД.
- Пытаемся "подключить" партицию в архивной БД в соответствующую таблицу с помощью exchange и получаем
ALTER /*+ MONITOR */ TABLE IBS.Z#MBK_ACQ EXCHANGE PARTITION (R_2016_04) WITH TABLE IBS.TT_8ZINLFCFCLIF2L1RFJNG INCLUDING INDEXES WITHOUT VALIDATION;
ERROR at line 1:
ORA-19728: data object number conflict between table TT_8ZINLFCFCLIF2L1RFJNG and partition R_2016_04_C_000 in table Z#MBK_ACQ 

Способов решения проблемы много. Все они сводятся к изменению data_object_id либо у партиции, либо у "транспортной" таблицы. 
Можно сделать move.
Но я выбрал truncate partition в архивной таблице, т.к. эти данные мне уже не нужны. Они будут замещены данными из транспортной таблицы. И к тому же в партиция архивной таблицы большая и Move будет выполняться долго.
Но для truncate пришлось на время отключить FK на таблицу, в которой делал truncate partition.
Кроме этого, если делаем truncate пустой партиции, то data_object_id не меняется. Парни в Oracle тоже не хотят делать лишние движения. Так что для "надежности" после truncate я еще и move таки делаю.
То что после truncate и move становятся unusable глобальные индексы, меня не волнует.  Я так и так собираюсь их перестраивать после exchange. 

4. TX-index contention or index unformated blocks after delete operation
У операции exchange, выполняемой с опцией update global indexes, нашлась еще одна БОЛЬШАЯ проблема. И выражается эта проблема либо в возникновении TX-index contention после выполнения exchange, либо в лавинообразном росте сегмента глобального индекса таблицы с высококонкурентной вставкой.
Но это слишком сложная тема для первого рабочего дня в новом году. Опишу чуть позже.  

вторник, 28 июля 2015 г.

DBMS_SQLPA. Число запусков SQL Statement

В последнее время мне очень много приходится работать над вопросами оптимизации работы Oracle Database и оценивать эффект от предлагаемых рекомендаций.
Существует множество инструментов для подобного рода оценок. Это и родные инструменты Oracle, собранные в пакет Oracle Real Application Testing. И сторонние инструменты проведения нагрузочного тестирования -HP LoadRunner и т.п.
Хочу поделиться одним наблюдением, сделанным во время тестов с использованием SQL Performance Analyzer.
С использованием пакета DBMS_SQLPA можно сформировать тестовый набор sql-выражений и запускать их в различных тестовых базах данных, сравнивая результаты запусков и оценивая эффект от изменений окружения. Так можно тестировать переход на новые версии Database, операционных систем, оценивать эффект от изменения параметров Oracle DB или параметров хранения объектов.
Я с помощью SPA оценивал эффект от партиционирования объектов. И при выполнении запросов обратил внимание, что реальное время выполнения запроса сильно отличается от статистических данных, которые появляются в dba_advisor_sqlstats.
Например запрос выполнялся 20 мин, а во вьюхе появляется значение elapsed_time - 98 сек.
Никак не мог понять, как такое возможно.
Оказалось, что в версии 11.2 при выполнении тестового набора каждый запрос выполняется ни один, а несколько раз. Это поведение регулируется параметром DISABLE_MULTI_EXEC
В версии 11.2 по умолчанию значение этого параметра равно FALSE.
В зависимости от времени выполнения запроса, он (запрос) может выполняться от 2 раз (для long running SQL statement) до 10 раз (соответственно для FAST Running SQL statement). При этом первое выполнение используется для разогрева buffer cache и не учитывается в статистике выполнения запроса.
Данные о числе запусков каждого запроса можно посмотреть в представлении DBA_ADVISOR_SQLSTATS.
TESTEXEC_TOTAL_EXECSNUMBERTotal number of executions during test execute
 

вторник, 8 апреля 2014 г.

Установка Oracle RAC 11.2.0.3 на Oracle Linux x64 6.4 (VirtualBox)

1. Заморачивался и развертывал DNS
2. При создании ASM DiskGroup есть проблема. Мне никак не установить совместимость для ADVM. Без него я не могу создать ASFS.
Оказывается - это баг. Его надо фиксить... Фикс бага буду делать после установки базы, чтобы проверить процесс накатки патчей.
Create ASM diskgroup using asmca
--> ADVM Compatibility is greyed and not selectable
Check OS version and kernel release
# uname -r
2.6.39-300.17.2.el6uek.x86_64
# cat /etc/oracle-release 
Oracle Linux Server release 6.3
# lsmod | grep ora
oracleasm              53352  1
See: Bug 12983005  Linux: ADVM/ACFS is not supported on OS version '2.6.39-100.7.1.el6uek.x86_64'
--> Missing driver for oracleacfs, oracleadvm , oracleoks
Fix : Install clusterware patch 11.2.0.3.3 or higher
For a detailed 11.2.0.3.4 patch install please read the following link.

3. При выполнении cluvfy постоянно получал проблемы с DNS. 
runcluvfy failed with PRVF-5636
 Решилось с помощью настройки в DNS
The warning is for not resolvable queries so the proper test is to run
time nslookup idontexist

if that takes more than 15 secs then:

If you are using your own bind DNS server try to edit /etc/named.conf

and change

zone "." in {
type hint;
file "root.hint";
};

to

zone "." in {
type hint;
file "/dev/null";
};

суббота, 8 июня 2013 г.

Компания "Балтрос"

Сегодня - мой последний рабочий день в компании "Балтрос", в которой я проработал более 13 лет.
Я  хочу сказать СПАСИБО всем своим коллегам, которые все это время были со мной рядом. Я очень благодарен Вам за работу, которая была проделана вместе с Вами, и за то время, когда мы вместе отдыхали.
Я не собираюсь терять с Вами связь, но мне будет не хватать ежедневного общения с Вами.
 Еще раз - БОЛЬШОЕ ВАМ СПАСИБО!