The Search For Intelligent Life in the Cost-Based Optimizer
by Tim Gorman

Два типа операций ввода/вывода

Oracle использует два типа операций ввода/вывода при чтении файлов данных:

• Одноблочные операции чтения со случайной природой доступа, обычно свойственные индексному доступу (во время такого запроса ввода/вывода генерируется wait-event db file sequential read)
• Многоблочные операции чтения с последовательной природой доступа, обычно принадлежат операциям полного табличного сканирования (во время такого запроса ввода/вывода генерируется wait-event db file scattered read), но могут также иметь отношение к сортировке и параллельным запросам (генерируется wait-event direct path read)

Теперь, как говорят, давайте начнем с самого начала...

Правила для обоих типов операций ввода/вывода могут быть достаточно простыми. Когда процессу нужен блок данных, процесс, прежде всего, проверяет Буферный Кэш. Если блок находится в Буферном Кэше, то процесс использует блок и "перемещает" его в MRU-конец связного LRU-списка. Если блок отсутствует в Буферном Кэше, тогда он считывается с диска и помещается в буфер Буферного Кэша, "размещенный" в MRU-конце LRU-списка.

Но при таком подходе существует серьезная проблема. Чтобы произошло бы в соответствие с этим алгоритмом, если бы кто-то произвел сканирование каждой строки в каждом блоке данных таблицы, размер которой превышает Буферный Кэш? Другими словами, выполнит полное табличное сканирование (FTS) большой таблицы?

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

В Oracle7 версии 7.0 для предотвращения эффекта очистки кэша (cache flushing effect) был введен ряд тонких, но очень важных изменений. На самом деле, описываемые изменения были впервые встроены в Oracle версии 6.0, но для удобства описания давайте начнем с Oracle7 версии 7.0...

Прежде всего, последовательный многоблочный ввод/вывод (отличительная особенность FTS) стал обрабатываться иначе по отношению к алгоритму LRU. Когда при полном табличном сканировании блоки считывались в Буферный Кэш, они размещались в LRU-конце (не в MRU) связного LRU-списка. Поведение одноблочных операций чтения, используемых при индексном сканировании, не изменилось, считываемые блоки по-прежнему размещались в MRU-конце цепочки LRU. Однако, блоки, считанные при FTS, практически немедленно перезаписывались, если только к ним немедленно не обращались вновь (тем самым, перемещая их в MRU-конец списка).

А почему бы и нет? Если Вы задумаетесь, во время полного табличного сканирования операции логического чтения только изредка находят нужные им блоки в Буферном Кэше просто из-за большого объема считываемых данных. Поэтому операции полного табличного сканирования всегда характеризуются большим числом операций физического чтения. Но насколько часто блоки, считанные с помощью операций FTS, используются вновь? Простой ответ: в большинстве случаев редко. Взвесив все за и против, архитекторы Oracle7 признали этот факт, позволив блокам, считанным с помощью операций FTS, практически немедленно перезаписываться другими, тем самым, сохраняя эффективность Буферного Кэша как реального кэша данных.

Однако архитекторы Oracle7 осознавали, что существует несколько ситуаций, когда выгоднее удерживать блоки при операциях FTS, например, при чтении очень маленьких таблиц, данные которых извлекаются очень часто. Другая особенность очень маленьких таблиц заключается в том, что они никогда не смогли бы очистить содержимое Буферного Кэша! Таким образом, блоки, читаемые во время операций полного табличного сканирования очень маленьких таблиц (very small tables), помещались в MRU-конец связного LRU-списка в качестве исключения. Чтобы определить значение термина "очень маленькая таблица", был создан параметр инициализации SMALL_TABLE_THRESHOLD со значением по умолчанию, равным 2% от DB_BLOCK_BUFFERS. Параметр SMALL_TABLE_THRESHOLD определялся как число блоков, любая таблица с меньшим числом блоков рассматривалась как "очень маленькая".

ПРИМЕЧАНИЕ: Этот алгоритм также характерен для Oracle релиз 6.0, где SMALL_TABLE_THRESHOLD был жестко зашит в ядре (т.е. не был вынесен в качестве параметра) со значением, равным 4 блокам. Он стал "видимым" параметром только в Oracle7.

Таким образом, они учли все, не так ли? Какие умные люди! Тем не менее, умные архитекторы Oracle7 пренебрегли человеческим фактором...

Разработчики приложений и администраторы баз данных всегда были очарованы идеей закрепления (pinning) целых таблиц в Буферном Кэше. Мнение о том, что это хороший подход, очень популярно. Хотя, если Вы задумаетесь, это просто невозможно в большинстве случаев. Все-таки кэш по определению есть относительно небольшое подмножество гораздо большей по размеру базы данных. Даже с учетом "километров" RAM, доступных серверам в наши дни, размер ее все еще не превышает нескольких гигабайт. В большинстве приложений баз данных таблицы с большим размером встречаются также часто как червяки в саду. Но, люди - это класс оптимистов, поэтому, насвистывая модную мелодию, мы делаем все возможное, чтобы закрепить такие таблицы в памяти, полагая, что это как-нибудь улучшит производительность.

Усвоив новые правила в Oracle7 релиз 7.0, администраторы баз данных немедленно переустанавливали параметр SMALL_TABLE_THRESHOLD в абсурдно высокое значение, пытаясь разом добиться труднодостижимого эффекта закрепленной в кэше таблицы (pinned table) и избежать дискового ввода/вывода. Тем самым, они нивелировали всю тонкую работу, проделанную архитекторами Oracle7, и мы получали ту же ситуацию (сброс кэша), которой пытались избежать. Операции полного табличного сканирования постоянно "размывали" Буферный Кэш без какой-либо пользы.