Том Кайт: Oracle для профессионалов

Третье издание книги Тома Кайта: Oracle для профессионалов, включающее особенности Oracle 12.

1-9. 1-9 главы - внутренняя работа субд oracle
10. Глава 10 - таблицы
11. Глава 11 - индексы
12. Глава 12 - типы данных
13. Глава 13 - секционирование
14. Глава 14 - параллельное выполнение
15. Глава 15 - загрузка и выгрузка данных

1-9 главы - внутренняя работа субд oracle

Про целостность и многоверсионность лучше прочесть книгу Джонота Льюиса: Ядро Oracle

Остановлюсь на некоторых вещах, которые мне показались интересными:

Режимы доступа к бд:
* выделенный сервер - под каждое подключение создается свой серверный процесс
 + нет конкуренции за подключения - очередей или блокировки (хорошо для dwh/olap)
 - в oltp системе большое число сеансов скушают все ресурсы ос
* разделяемый сервер - ограниченный пул подключений (разделяемых серверов)
 + в олтп системе не будет доп. нагрузки на ОС (хорошо для ЯП в которых самим не сделать пул (типа пхп))
  нужно тестировать максимальную степень параллелизма, на которой начинается просадка (зависит от числа ядер, видимо)
 - долгие запросы могут заблокировать работу других сеансов (т.к. пул исчерпан)
 - не стоит применять, если у вас уже есть собственный пул подключения (это приведет к снижению производительности)
* новый режим в 12 оракле - контейнерный, когда имеется один инстанс с общим набором системных процессов и множество бд.

Блокировки:
* пессимистическая - строка блокируется для других сессий до конца операции (nowait, dbms_lock)
* оптимистичная блокировка - вводится доп.столбец с датой документа, он берется до вставки и добавляется в where при update.
Если изменилось 0 строк, это значит, что ктото поменял строку до вас. Нужно выдать ошибку и перечитать данные. (опт. в реалиях веб приложений предпочтительней)
Так же можно сделать через хэш функцию и dbms_lock, если вставляют одно и тоже, то попытка захватить лок с темже хэшем свалится с ошибкой.
** Еще вариант: вести историю изменений и передать всю ответственность на пользователей.

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

Многоверсионность:
Заинтересованные транзакции: Каждый блок хранит список транзакций с блокировками (init_trnans), если строк в блоке много и сессий много, то мы свалимся с конкуренцией, т.к. места под строку блокировки в самом блоке не хватит (решить можно увеличив init_trans или pct_free, тем самым зарезервировать больше места под блокировки блока)

Выявление изменений: Нельзя забирать в хранилища данные по дате изменения, т.к. дата изменения в долгой транзакции может проставиться с задержкой и мы потеряем это обновление. Правильно делать: по номеру scn или по минимальному времени старта всех транзакций в системе на текущий момент (v$transactions)

Перезапуск транзакции: В случае если 2 Update одновременно обновляют одни столбцы (включая которые в where), то 2 запрос встает в очередь и рестартует при фиксации 1 транзакции, при этом используются данные из 1 транзакции, хоть 2 и была запущена до фиксации 1. При рестарте на 2 транзакцию накладывается блокировка, чтобы избежать повторного рестарта. Это все значит, что before Триггер при рестарте будет выполнен дважды!
Дополнительный вывод: after trigger эффективней, т.к. не требуется повторных операций для получения согласованной копии или перезапуска, т.к. согласованная копия уже была получена при update.

Откат транзакции при ошибке: ошибка атомарной операции не вызывает rollback всей транзакции, а откатывает только саму операцию и все связанные операции порожденные этой операцией (аналог savepoint перед оператором)
* insert с триггером откатит insert и все связанные операции в trigger
* ошибка в процедуре откатит все операции внутри процедуры и триггерах на инсертах
 ** если есть обработка when others , тогда откат связанных операций не производится (rollback savepoint не вызывается)

асинхронный коммит: commit write nowait, не гарантирует целостность при падении бд. По-умолчанию именно такой commit используется в plsql и синхронный в самом конце процедуры. Так сделано потому что результат операции процедуры неизвестен до конца ее выполнения.
commit сбрасывает логи undo/redo на диск из буферного кэша. Объем сброса уменьшается за счет того, что кэш и так сам сбрасывается на диск каждые 3с процессом lgwr.

Проверка constraint в самом конце dml операции (не во время ее выполнения), т.к. update/insert при построчном обновлении может нарушать его, но вконце все будет ок.
отложенные/defereable ограничения целостности на pk дадут неуникальный индекс, а на not null не позволят использовать index ffs, т.к. oracle допускает наличие null в таблице, тогда как в индексе их нет.

Производительность записи логов: события log_file_switch/log_buffer_space/checkpoint_incomplete говорит о том, что процесс записи на диск изменения приостановлен до момента освобождения места под новые логи. Старые должны быть скинуты на диск. ЧТобы этого избежать: нужно увеличить число dbwr (процессов записи на диск), увеличить размер логов, увеличить число файлов логов, чаще сохранять логи на диск.
log_file_sync - значит или часто делается commit Или плохо настроены файлы логов( медленные диски, на томже диске где сама бд, файлы не распределены)

Отложенная очистка: кол-во блоков которые сразу очищаются от блокировок в ITIL не превышает 10% от буферного кэша. Если число модифицированных блоков превышает это значение, то применяется отложенная очистка.

Глава 10 - таблицы

1. Временные таблицы
Использование пространства:
сохраняют данные в temp tbs конкретного пользователя (указанный при создании), который ее заполняет.

redo:
до 12 оракла:
* генерирует в 100 раз меньше redo на insert, в 3 раза меньше на update и столько же на delete. UNDO генерится столькоже.
в 12 оракле
* включаем temp_undo_enable=true и временные таблицы перестанут генерировать redo

статистика:
до 12 оракла:
* не собирается, нужно использовать +dynamic_sampling(t 2) (вроде как она поумолчанию делается для временных таблиц), собирать самому или делать set_table_stats вручную (распространяется на все сеансы). Собрать статистику на очищаемых при commit не получится, т.к. сбор статистики делает неявный commit.
в 12 оракл:
* автоматом собирается (или можно самому) и статистика привязана к конкретному сеансу. gather stat больше не делает неявный commit, так что можно собирать статистику на таблицах очищаемых при commit.

2. Обычные таблицы
append (и другая директ вставка) пишет данные выше HWM, delete их не возвращает (только truncate Или дефрагментация), из-за чего таблица распухает и даже на мелкой таблице чтение будет проходить очень долго, т.к. придется читать все блоки до hwm.

freelist N - число списков для свободных блоков. Увеличение их числа уменьшает конкуренцию за поиском свободного места (bufer busy waits), но увеличивает размер таблицы, т.к. один процесс может исопльзовать только свой список свободных блоков. Он не будет переключаться на другой, а создаст под себя новый. Также это сильно увеличивает фактор кластеризации.
этим параметром можно пользоваться при параллельной директ вставке, а потом его отключать, если место критично!

перемещенные строки: если данные обновляются и растут в размере после Update, нужно резервировать большой % pct_free иначе строка будет перемещаться в другой блок , а в старом будет ссылка на новое место. Но индексы и просто обращения будут смотреть старое место, а потом уже идти по ссылке, что усложняет ввод-вывод.

индекс организованные таблицы (IOT):
* сжатые столбцы ( как у обычного индекса) - подходит для малоселективных начальных столбцов
* overflow с указанием threshold N - все строки занимающие больше N% блока будут вынесены в отдельную структуру, а в индексе будет ссылка на них
* overflow с including COL1 - все столбцы кроме первичного ключа и COL1 будут вынесены в отдельную структуру, а в индексе будет ссылка на них
если данные индексной таблицы часто обновляются то эти часто обновляемые данные лучше вынести в overflow. Тогда индекс не будет перестраиваться при их обновлении. (чем это лучше обычной таблицы? :)

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

кластерные таблицы на основе индекса/хэша:
* создается описание кластера
 size - размер ключа под ид кластера в байтах, чем больше данных, тем больше он должен быть, иначе данные не будут в одном блоке.
* создается индекс на кластере (или хэш массив) для позиционирования на нужные части кластера (индекс указывает на блок, не на строку, как обынчо)
* создаются таблицы/таблица с указанием кластера
в итоге получаем таблицы/таблицу у которых строки одного кластера лежат в одном блоке.
+ уменьшается физическое чтение, т.к. данные не читаются повторно
(важно rowid уникален только в рамках таблицы - т.к. он указывает на блок, в котором могут быть разные таблицы)
кластеры не подходят для: часто модифицированных таблиц, нельзя секционировать, делать truncate, полное сканирование посути сканирование нескольких таблиц.
при хэш кластере нельзя сделать фильтрацию по диапазону, т.к. для этого диапазона нельзя подсчитать хэши заранее (по индексу можно) и нужно примерно знать размер будущей таблицы. подходят: для совместно читаемых таблиц.
+хэш кластера - нет чтения индекса (ввод-вывод и конкуренция за него), но увеличивает cpu изза хэширования.
+также можно сделать отсортированный хэш кластер - хэш по 1 столбцу и сортировка по 2. ТОгда при запросе с фильтром по 1столбцу и сортировке по 2 можно будет просто читать кластер.

nested table:
- при обновлении дочерней таблицы блокируется строка из родительской (что не происходит при обычной модели), что существенно сериализует обновление
- накладные расходы на хранение

Глава 11 - индексы

Уже достаточно подробно описано у меня в блоге.
Небольшие дополнения:

bulk collect снижает кол-во логических вводов выводов, т.к. уменьшает число повторных чтений листовых блоков индекса (или блоков таблицы).
желательно устанавливать число bulk collect близким к число строк в листе индекса или блока таблицы, чтобы не читать их повторно
Null поля: В индекс не попадают строки у которых все столбцы = null. Кроме того если все столбцы = Null, то и ограничение уникальности также не проверяется.
Функцональный индекс на части таблицы: Если столбец сильно перекошен, то в индексе можно исключить часто используемое значение (функциональный индекс через детерменестическую функцию), оставить только редкие. Так мы получим все преимущества индекса + он будет мало весить.
Удаление записи из индекса: При удалении строки из индекса, она не удаляется, чтобы не делать перестройку индекса, а лишь помечается удаленной и будет повторно использована при вставке . Сжатие индекса: если доступ к индексу всегда идет по нескольким столбцам, то лучше вначале располагать малоселективный столбцы и сжимать индекс.

Глава 12 - типы данных

varchar2 ограничен 4000 байт, т.е. даже если написать varchar(4000 char), то сюда влезет только половина русских букв, т.к. они по 2 байта
в 12 оракле можно влкючить расширенный varchar (но обратно не выключить!), в это случае vchar будет до 32768 байт, и столбцы более 4000 внутренее будут clob, но с ними можно будет работать как с обычным текстом (dblink, сравнение, intersect)

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

add_month для конца месяца дает также конец месяца, даже если в месяцах разное число дней.

lob поле представляет из себя локатор (указатель) на lob Индекс (если disable storage in row), который в свою очередь, ссылается на части блоба (chunk) в разных места tbs. Что дает быстрый произвольный доступ к разным частям блоба. (лобы до 4000 байт могут хранится прямо в строке - enable storage in row)
Маленький chunk уменьшает размер блоба, но увеличивает время извлечения из-за того что разрастается lob index (кол-во строк * число chunk).
Согласованность чтения обеспечивается на уровне lob index, сами сегменты блоба не сохраняются в undo, но дублируются на месте для многоверсионности.
Retention - время хранения удаленных сегментов блобов для многоверсионности (так что не удивляйтесь, если место под блобы не уменьшается даже после операции delete, они еще будут хранится retention дней)
bfile - указатель на несогласованный файл на диске (можно только читать). Записывать другими способами: отдельная программа или utl_file

Глава 13 - секционирование

Частично было описано ранее в блоге, но по большей части ничего особенного, лучше смотреть документацию.
секционирование в олтп может даже помешать:
* fts почти нет
* секционирование индекса - это просмотр пары блоков ветвления, тут или не будет выигрыша или если просматривается несколько секций (а это просмотр нескольких физических индексов) наоборот все будет медленней
* в олтп также слабо помогает параллельная обработка, т.к. просматривается мелкий объем данных
* ++ уменьшение конкуренции на вставку
* в олтп лучше секционировать хэшем, а не по датам, чтобы уменьшить конкуренцию на вставку, а данные доставать по индексу.
* степень двойки при хэш секционировании обеспечивает более равномерное распределение данных (иначе будет перекос)
секционирование по ссылке (ref), удобно, когда нет возможности денормализировать таблицу. Например заказы - секц.по месяцу, а строки заказов по ссылке (т.е. они тоже получаются секц. по месяцу заказа). Столбец секц. по ссылке должен быть not null
?? локальные префиксные индексы включают в себя столбец партиционирования таблицы??? - какие преимущества?
* глобальные индексы - секционированы (или нет) самостоятельно и могут указывать на различные секции партиционированной таблицы.
* update global index - при операциях с секциями (удаление, добавление, обмен и т.д.) обновляет и глобальный индекс (иначе он становится невалидным)
* в 12 оракле можно обменивать дочернюю/родительскую таблицу каскадно. В 11 только по 1 таблице, что могло привести к несогласованному виду хранилище на короткое время.

Глава 14 - параллельное выполнение

Про оптимизацию параллельных запросов уже было пару подробных статей в блоге.
exadata:
+ распараллеливание выборки по множеству флэш дисков
+ фильтрация происходит в источнике данных (флэш дисках) и строки уже приходят отфильтрованные в rdms. (В классической системе происходит fts и фильтрация данных внутри rdms)
+ из источника запрашиваются только нужные столбцы данных из-за поколоночного хранения данных

Ограничения параллельных dml:
* триггеры
* fk
* битовые индексы и lob
* кластерные таблицы и дблинки
* нельзя прочитать до commit/rollback

pipeline функции:
принимающие внутрь sysrefcursor позволяет распараллеливать запрос самим oracle на N параллельных потоков. Внутрь функции передается только 1/N часть данных, которые обрабатываются последовательно, потом координатор принимает эти куски от параллельных функций.
+ можно параллелить, что обычно не параллелится - plsql код к примеру
+ можно использовать замен lateral запросов в 11g, где их еще нет
тоже самое можно сделать самостоятельно в процедурном стиле через dbms_parallel_execute

Глава 15 - загрузка и выгрузка данных

* если есть доступ к серверу оракла, то загрузка через внешние таблицы предпочтительней, т.к. они дают полный функционал обычных таблиц (кроме обновления). Тогда как sqlloader позволяет только загружать и немного фильтровать (только and/or)
* код sqlloader можно преобразовать в код exttable через параметр
* внешние таблицы можно использовать для вызова sh/bat файлов ОС, что позволит читать вывот пакетных файлов, как таблицы. Вызов такой таблицы надо или загружать во временную таблицу или делать в with+materialize, чтобы он выполнялся единожды
* type oracle_datapump позволяет делать обратную операцию - выгрузку обычной таблицы во внешнюю на диск.
* blob можно загрузить через dbms_lob и insert+returning (указатель на lob)