что такое партиция в оракле
Секционирование. Автоматическое добавление секций
Решение для равномерно увеличивающегося ключа секционирования без пропусков
Данный триггер с использованием автономных транзакций автоматически создает новую секцию с именем ‘P’+номер секции размером в 10000, когда ID — наш ключ секционирования — остается 4000 значений до границы секции(10000-4000 = 6000, т.е. и тд.), но сначала проверяется не существует ли уже данная секция(такое может произойти, например, при повторном добавлении 6000-й записи, или ручном добавлении секции). Параметры секционирования — 10000 и 4000, вы должны подбирать исходя из вашей конкретной ситуации, но следует учесть, что граница(4000 в примере) должна быть больше максимального количества одномоментно добавляемых записей, т.к. иначе на момент транзакции вставки данных, транзакция не будет «знать» о новой секции, т.к. на начало транзакции ее не существовало, поэтому данные вставлены не будут с жалобой об отсутствии сопоставления секции данному ключу. Этого бы можно было избежать с использованием alter table split default_partition, который я рассмотрю далее, но это скажется на времени выполнения.
Проверим наш триггер, заполнив секцию:
insert into xtender.test_part
select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED
from all_objects o
where rownum
Кроме того, в случае использования сиквенсов, которые из-за кэширования «шагают» не последовательно можно изменить триггер, чтобы он выполнялся для набора значений с 4000 до 3900 записи с конца секции:
заменим условие
на
Решение для прочих случаев
В случаях случаях, когда мы указываем секцию по умолчанию, мы можем разделять ее тогда, когда туда уже попали записи, вопрос в том как это автоматически отслеживать.
В Data dictionary мы можем получить информацию о всех секциях секционированных таблиц, сделав выборку из dba_tab_partitions, в которой partition_position указывает порядок секции в таблице, а high_value — параметры секции. Следовательно, мы можем получить имя последней секции в таблице и сделать выборку из нее для получения количества записей в ней.
Как управлять секциями в БД Oracle и не сойти с ума
Мы уже рассказывали о том, почему секционирование баз данных очень важно для производительности DLP-системы и как мы реализовывали его в PostgreSQL. В этой статье речь пойдет об Oracle.
Специфика использования СУБД в DLP-решениях состоит в том, что объем данных прирастает очень быстро. Их невозможно держать в оперативном архиве, и долговременное хранение – это необходимость в компании численностью свыше хотя бы 50 человек. При этом оперативный архив наполняется так быстро, что отдавать информацию в долгосрочный архив приходится раз в 2 недели или чаще. Использование только встроенных средств СУБД требует знаний и опыта. Это главная сложность, и она, в общем-то, очевидна «на берегу».
Кроме того, возникают проблемы, не очевидные сразу. Как вернуть из долгосрочного архива партицию с данными более старой версии приложения и прицепить к более свежей? Что делать, если у них разных формат хранения данных? Что делать, если подключение секции было прервано, и она «зависла» между долговременным и оперативным архивом?
В целом, решение этих вопросов сводится к двум основным техническим задачам: автоматизация управления секциями в СУБД Oracle (отключение и подключение) и система «отката» секций в случае, если при подключении что-то пошло не так.
Что не так со встроенными механизмами Oracle DB
Убирать данные на ленту и возвращать их из долговременного архива помогает опция Partitioning, с ее помощью можно разделить таблицу на части по какому-то принципу, например, по диапазону дат. Кроме управляемости и доступности, такое разделение еще позволяет повысить производительность. Каждый период хранится в отдельном табличном пространстве, что позволяет с помощью технологии transportable tablespace, достаточно быстро перемещать табличные пространства между различными отчетными и архивными БД с различными версиями и платформами. Но проблема в том, что стандартных механизмов не всегда достаточно: они позволяют только создавать базовые структуры без учета специфики приложения. А дальше администратор вынужден создавать вокруг них кучу инструментов управления. Да и сам процесс отключения-подключения-переноса требует навыков администрирования БД. Поэтому задача минимум – автоматизировать этот процесс, сделать его доступным для администраторов приложений.
Мы разработали набор скриптов, с помощью которых можно управлять секционированными таблицами, получать любую информацию о них и т.д. Не нужно знание команд, опыта работы с СУБД. Администратор приложения просто запускает скрипт или выбирает в интерфейсе нужное действие, указывает нужную партицию, и все происходит само собой.
(Не)совместимость версий
Итак, мы автоматизировали отключение секций и отправку их в долгосрочный архив. Но с долгосрочным архивом есть проблема: иногда его нужно вернуть.
Допустим, администратор перенес в него несколько секций в старой версии. Через год вышла новая версия, в которой добавились новые поля в таблицы, новые индексы, и в долгосрочный архив ушло еще некоторое количество секций. А потом безопасник расследует некий инцидент, и ему необходимо поднять данные двухлетней давности, т.е. поднять секцию несколько версий назад и каким-то образом подключить ее к БД.
Структура таблиц новой версии иногда отличается от исторической. Необходим ряд проверок и изменений для архивной секции. Проверка всегда начинается со сравнения текущей версии Solar Dozor и версии СУБД, и подключаемой партиции. Если есть различия, запускаются процедуры, корректирующие метаданные, добавляются необходимые поля, индексы, ключи, проверяется консистентность подключаемых данных, и пр., удаляется лишнее.
Дополнительные сложности приносит и использование для поиска в Solar Dozor текстовых индексов. Есть некоторые баги, связанные с EXCHANGE PARTITION для текстовых индексов, созданных в разных версиях СУБД или при использовании transportable tablespace (до 12 версии index metadata corruption). Патчи не всегда есть для нужной версии или платформы. Пересоздавать индексы при подключении – не быстрая и достаточно ресурсоемкая процедура. Пришлось «впилить» workaround-ы в процедуры подключения партиции. Структура DR$ таблиц текстовых индексов подключаемой партиции «выравнивается» с текущей, апдейтится поле таблицы ctxsys.dr$index.
Есть и защита от разных ошибок администраторов. Например, на уровне приложения запрещены любые действия с партицией, в которую в данный момент заливаются данные и имеющую статус «current».
«Хьюстон, у нас проблема»
В ходе реализации этих механизмов мы столкнулись с еще одной проблемой, неожиданно часто возникающей у заказчиков. В процессе отключения что-то может пойти не так, вплоть до банального отключения электричества, так что подключение секции может в любой момент прерваться. В результате получаем базу, которая находится в «промежуточном» состоянии.
В СУБД Oracle есть DDL и DML. В DML реализован механизм для обеспечения транзакционной целостности, который откатывает назад результаты, если транзакция не прошла. В DDL такого механизма нет, и любые действия с секцией – это путь в один конец.
Мы разработали механизм, который проверяет выполнение всех шагов по отключению-подключению партиции и корректирует возникающие проблемы. В случае возникновения проблем механизм перезапускает операции с партицией с того момента, когда что-то пошло не так. Ошибки при отключении-подключении логируются, и это позволяет в любой момент узнать, какие проблемы и когда возникали.
Скахин Алексей / pihel
Личный блог. Заметки о программировании и не только
Страницы
пятница, 19 июня 2015 г.
ORACLE: Оптимизация работы секционированных таблиц
Начиная с Oracle 11 размер любой партиции, представленной на диске, по умолчанию равен 8МБ, даже если она не имеет в себе никаких данных (До 11 версии размер по умолчанию был 65КБ).
Отсюда важность указания STORAGE INITIAL при создании партиционированной таблицы.
Отсюда не сложно высчитать минимальный размер такой таблицы 18*100*8МБ= 140ГБ.
Что явный перебор при исходном размере таблицы без секций = 2ГБ.
Размер партиции можно взять с небольшим запасом (1МБ):
Минимальный размер такой таблицы вырастет в сравнении с плоской незначительно на (1-0,68/1)*100% = 32%
+ Уменьшение размера таблицы сопоставимо изменению параметров PCTFREE/PCTUSED
— При update строки с увеличением его размера, в случае нехватки свободного места в блоке (PCTFREE) строка целиком будет перенесена из текущего блока в новый. В старом блоке будет проставлена ссылка на новое расположение. Т.е. при чтении данных из таблицы нужно будет выполнить дополнительное рекурсивное чтение, что значительно увеличит стоимость запроса.
— При большом числе параллельных сессии к одному блоку сильно разрастается информация о заинтересованных сессиях к строкам блока (ITL). Если место в блоке закончится, то невозможно будет расширить ITL, что приведет к ошибке обновления блока.
3. Быстрое обновление таблиц через PARTITION EXCANGE.
Для этого нужна вспомогательная таблица, куда будет класться очередная порция для обновления.
У таблиц должна совпадать полностью структура, включая размерности и последовательность столбцов.
После этого можно перекинуть из этой таблицу в целевую партиционированную одной командой.
Пример перекидки из промежуточной таблицы в RANGE-INTERVAL партиционированную таблицу.
Этой командой произойдет обмен данными между таблицами: партиция заполнится данными таблицы T_TBL_PT, а таблица данными пустой партиции T_TBL.
Аналогично можно делать обмены с субпартициями
4. Узнать имя партиции по произвольному фильтру
Такое может понадобится, допустим, для сбора статистики только по нужной секции, т.к. в dbms_stat нужно указать физическое имя.
Если известно значение в колонке секций, то нужно воспользоваться конструкцией:
5. System партицирование
Нет указания колонки при создании, из-за этого нужно указывать конкретную партицию при вставке или выборке
6. Reference партицирование
Возможность создания детализированной таблицы с наследованием партицирования от родительской: В строках нет даты, она автоматом подтягивается по фк из заголовка и партицируется по ней.
Цена этому увеличение нагрузки в 10 раз: https://jonathanlewis.wordpress.com/2018/03/19/reference-costs/
7. Изменение параметров партиции на основании статистики использования
На основании статистики использования партиций (DBA_HEAT_MAP_SEG_HISTOGRAM) можно включить компрессию
Фрагментация, секционирование – partition. О фрагментации таблиц. Часть первая
Фрагментация (секционирование) это
С помощью фрагментации появляется возможность управления фрагментами(секциями) в больших таблицах, то есть часть не нужных нам данных в текущий момент можно перенести на сторонний носитель.
Создадим три независимых табличных пространства, они нам понадобятся для демонстрационных примеров
Совмещенный тип фрагментации
Или тип фрагментации совмещающий в себе фрагментацию с использованием хэш функции и фрагментации по диапазону значений
Синтаксис
Создания и фрагментации таблиц используется дополнительная синтаксическая конструкция в команде CREATE ТABLE – PATITION BY
Обычный синтаксис для создания ферментированной таблицы выглядит следующим образом
Специфика использования оператора SELECT для выбора данных из фрагментированных таблиц
С помощью оператора SELECT есть возможность выбирать как все данные из фрагментированной таблицы, так и
использовать SELECT для выбора данных из заданного фрагмента таблицы.
данный запрос выведет данные из фрагмента таблицы pt_3
Фрагментация по диапазону значений
Создадим таблицу проводок с фрагментацией по диапазону значений
Заполним таблицу проводок значениями
Выберем данные из таблицы
Следующий пример демонстрирует разбиение на фрагменты таблицу в зависимости года к которой принадлежит проводка
Следующий пример иллюстрирует использование фрагментации таблицы в зависимости от числового значения
Фрагментация с использованием списка значений
Создание таблицы
Фрагментация с использованием хэш-функции
Создадим таблицу проводок с фрагментацией по хэш функции
Заполним ее данными
Выберем данные из таблицы
А так же выполним оператор select для каждого из фрагментов(секций) таблицы
Смешанный тип фрагментации
Смешанный тип фрагментации предусматривает как ферментацию по диапазону значений так и дополнительную фрагментацию по хэш функции или фрагментацию по списку значений
Заполним таблицу pro_range_hash
Управление данными во фрагментах таблицы
Сервер выведет ошибку
Ora-144000. Вставленный ключ секции не соответствует ни одной секции.
Изменим данные таким образом чтобы изменилась принадлежность записей к фрагменту
Получим ошибку обновление ключа секции прведет к ее изменению.
Как же сделать возможным перенос строк?
Для этого необходимо включить для выбранной таблицы опцию row movemen t
Выполним скрипт
выполним наш update повторно
на этот раз ошибок не было. Обновление строк в таблице прошло успешно.
Выполним запрос по таблице pro_list
Получим системную ошибку о том, что сегмент не может быть прочитан
Скорректируем запрос так как нам нужны только документы RR
Корпоративные хранилища данных. Интеграция систем. Проектная документация.
Oracle Partitioning: Оперативное перемещение и восстановление исторических данных
1. Введение
При решении задачи хранения и обеспечения доступа к историческим данным очень часто возникает задача выгрузки архивных данных на резервный носитель (например, на магнитную ленту) с возможностью оперативного восстановления этой информации и обеспечения доступа к ней пользователей. Эта проблема наиболее актуальна для хранилищ данных, хотя может применяться и для обработки архивных данных OLTP-систем.
В данной статье описывается способ решения этой проблемы с помощью опции Partitioning базы данных Oracle Database.
Ниже представлена иллюстрация данного подхода, который включает в себя: идентификацию исторических данных, их перемещение во временную таблицу, экспорт и копирование на резервный носитель.
Иллюстрация подхода перемещения исторических данных
Первым шагом является определение секций, содержащих исторические данные. Исторические данные – это данные за прошлые периоды, над которыми в будущем не будут проводиться операции изменения. Затем секции, содержащие исторические данные, перемещаются в заранее подготовленную временную таблицу. Следующим шагом производится экспорт метаданных для Transport Table Space (TTS). В заключении производится перенос файла с метаданными и файла табличного пространства на резервный носитель.
Далее будет детально рассматриваться процесс экспорта и импорта табличного пространства для одного раздела секционированной таблицы CALLS (информация о телефонных звонках клиентов) схемы DWH.
Описанный подход был принят как основной для задач перемещение и восстановление исторических данных хранилища корпоративной информации компании “ОАО Ростелеком”.
2. Определение исторических данных
Для выявления исторических данных, то есть тех данных которые не будут больше изменяться, администратор должен ежемесячно проводить мониторинг их появления. Перечень данных, которые следует признавать историческими, определяют бизнес-требования. Часто правило определения исторических данных сводится к такому условию: историческими признаются те данные, срок хранения которых превышает определенный лимит, например, 5 лет от текущего момента.
Для автоматизации выявления исторических данных в конкретной таблице фактов, возможно выполнение следующего запроса (обращение к словарю Oracle Database):
Данный запрос вернет перечень разделов (см. поле PARTITION_NAME) по таблицам, данные в которых являются историческими (срок хранения превышает 5 лет). Эти данные необходимо архивировать и перенести на резервный носитель.
3. Перемещение исторических данных
Для перемещения раздела таблицы с историческими данными будет использована технология перемещаемых табличных пространств (Transportable Tablespace). Для перемещения табличных пространств необходимо провести следующие действия:
Ниже приведена последовательность действий по перемещению исторических данных из раздела P_0106 таблицы CALLS.
Данные раздела P_0106 хранятся в табличном пространстве TBS_CALLS_0106_1, которое в свою очередь, состоит из двух файлов: TBS_CALLS_0106_1_001.dbf и TBS_CALLS_0106_1_002.dbf.
Ниже все скрипты будут выполняться из-под пользователя system.
4. Создание временной таблицы
Создадим временную таблицу, в которую в последствии переместим раздел с историческими данными.
5. Перемещение данных во временную таблицу
Выполняем команду смены раздела (exchange paertition) P_0106 (раздел с историческими данными) между таблицей CALLS и временной таблицей CALLS$EXP$P_0106.
6. Удаление связей
Сделать экспорт метаданных табличного пространства можно только тогда, когда оно не связано с другими объектами базы данных.
Для проверки наличия связей необходимо выполнить следующие процедуру и запрос (их необходимо выполнять из-под пользователя SYS):
Если запрос к представлению TRANSPORT_SET_VIOLATIONS возвращает записи, то это значит, что взаимосвязи раздела с другими объектами базы данных существуют. Необходимо, чтобы запрос к данному представлению НЕ возвращал строк. Для этого необходимо изменить табличные пространства для раздела P_0106 таблицы CALLS – переместить раздел в табличное пространство TBS_CALLS_0106_HIST и переместить метаданные о таблице CALLS$EXP$P_0106 в табличное пространство TBS_CALLS_0106_1:
Выполним проверку наличия взаимосвязей повторно.
В представлении TRANSPORT_SET_VIOLATIONS записи отсутствуют – взаимосвязей нет.
7. Атрибут «только для чтения»
Сделать экспорт метаданных табличного пространства можно только тогда, когда оно находится в режиме «только для чтения». Сделать табличное пространство доступным только для чтения можно, выполнив следующую команду:
8. Экспорт табличного пространства
Произведем экспорт метаданных табличного пространства. Для этого будет использована технология DataPump и, соответственно, утилита expdp.
В командной строке необходимы выполнить команду экспорта (см. скрипт – export.sh) в директорию определенною в переменной DATA_PUMP_DIR базы данных.
Перейдем в директорию, которую определяет переменная DATA_PUMP_DIR.
Просмотрим ее содержимое.
9. Копирование файлов
Скопируем файл с метаданными TBS_CALLS_0106_1.DMP и файлы данных БД TBS_CALLS_0106_1_001.dbf, TBS_CALLS_0106_1_002.dbf в директорию /backup/DWH/TBS_CALLS_0106_1_HIST, предназначенную для временного хранения архивов, перед переносом на резервный носитель. Предварительно директорию TBS_CALLS_0106_1_HIST необходимо создать в /backup/DWH/.
Рекомендуется создать текстовый файл /backup/DWH/TBS_CALLS_0106_1.txt, в котором описать месторасположение файлов с данными экспортируемого табличного пространства. И затем включить данный текстовый файл в архив.
Для создания файла с описанием можно выполнить следующие действия (в операционной системе Unix):
10. Создание архива
Создадим архив с содержимым директории TBS_CDR_0306_1_HIST, используя утилиту tar. Этот архив, впоследствии, и будет перемещен на резервный носитель.
Архив создан. Теперь можно удалить исторические данные из таблицы БД.
11. Удаление табличного пространства
Удалим табличное пространство TBS_CALLS_0106_1
Вместе с табличным TBS_CALLS_0106_1 пространством удалится и временная таблица CALLS$EXP$P_0106.
Для облегчения в дальнейшем процесса восстановления в таблице с данными (в нашем примере это таблица CALLS) раздел, в котором были исторические данные, лучше оставить.
12. Восстановление исторических данных
Для восстановления исторических данных из архива необходимо провести следующие действия:
13. Копирование и распаковка архива
Скопируем архив с историческими данными с резервного носителя в директорию для восстановления. В нашем примере это будет директория /backup/Restore. Обычно эту функцию выполняет администратор системы резервного копирования.
Подключимся к серверу, на котором работает наша СУБД, под пользователем операционной системы oracle, используя командную строку.
Извлечём файлы из архива.
14. Копирование файлов
Скопирем файл с метаданными TBS_CALLS_0106_1.DMP в директорию /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/,
файл данных TBS_CALLS_0106_1_001.dbf в директорию /wh/oracle/disk1/DWH/;
файл данных TBS_CALLS_0106_1_002.dbf в директорию /wh/oracle/disk0/DWH/.
15. Импорт исторических данных
Выполним команду экспорта метаданных табличного пространства (см. скрипт – import.sh) в директорию, определенную в переменной DATA_PUMP_DIR базы данных.
После окончания импорта метаданных табличного пространства в схеме DWH появится таблица CALLS$EXP$P_0106.
16. Смена табличных пространств
Осуществим смену (partitio6 exchange) между таблицей CALLS$EXP$P_0106 и таблице CALLS.
17. Заключение
База данных Oracle Database предоставляет гибкий механизм управления табличными пространствами секционированных таблиц, что позволяет достаточно просто организовать управление архивными данными, как в OLTP-системах, так и в хранилищах данных.
Полный архив скриптов можно загрузить по данной ссылке.