Оптимизация Rails для Oracle - Переменные связывания и совместное использование курсора
ОГЛАВЛЕНИЕ
Переменные связывания и совместное использование курсора
Для создания запросов "на лету" в ActiveRecord используются ее (динамически построенные) знания о моделях и базах данных. В ней таким образом строятся даже многие методы моделей, что позволяет разработчикам писать вещи вроде:
Employee.find_by_first_name_and_last_name('Steven','King') Но чтобы сделать так, ActiveRecord строит SQL-запрос в виде строки и проводит вставку параметров перед посылкой запроса в адаптер для выполнения (не используя переменных связывания). Проблемы SQL-инжекции (SQL injection) и безопасности предотвращаются на уровне адаптера, для чего значения SQL-вставок заключаются в кавычки, но при этом наблюдается снижение производительности и оказывается воздействие на масштабируемость любого приложения Rails, выполняющегося в среде Oracle. Чтобы понимать, как это воздействует на производительность и масштабируемость, позвольте мне объяснить, что происходит при выполнении запроса.
При первом выполнении запроса Oracle выполняет шаг полного разбора, на котором проверяет, является ли запрос синтаксически корректным, и все ли объекты (таблицы, столбцы...), к которым производятся обращения в запросе, действительно существуют. Если проверки проходят успешно, создается новый элемент библиотечного кэша, содержащий результаты синтаксического анализа, которые могут быть многократно использованы для последующего выполнения. Ключом библиотечного кэша является непосредственно текст SQL-запроса, поэтому два запроса, у которых имеются любые литеральные различия, для целей анализа считаются различными.
Когда выполняется новый запрос, в библиотечном кэше производится просмотр с хешированием, чтобы увидеть, проводился ли ранее анализ запроса. Если запрос найден, то используется кэшированный результат разбора и удается избежать шага полного разбора, более того, к библиотечному кэшу не добавляются никакие новые элементы. Способ минимизации числа выполнений полного разбора состоит в том, чтобы всегда использовать параметры связывания.
В контексте приложений Rails версий, предшествующих 1.2, где отсутствовала какая бы то ни было специфическая настройка базы данных, это означает, что каждый раз выполняется другой запрос (даже если он отличается только значением параметра), для оператора механизмом базы данных выполняется полный разбор и в библиотечный кэш добавляется элемент для нового запроса.
Вы можете проконтролировать происходящее, рассмотрев представление v$sql, где в столбце sql_text хранится фактический оператор SQL, используемый для запросов: каждая строка в v$sql соответствует элементу библиотечного кэша. Мы можем использовать следующий запрос, чтобы получить все запросы, сгенерированные нашим приложением (ограничив себя запросами, которые касаются одной из этих 6 таблиц):
select sql_text from v$sql
where ( lower(sql_text) like '%employee%'
or lower(sql_text) like '%countries%'
or lower(sql_text) like '%departments%'
or lower(sql_text) like '%jobs%'
or lower(sql_text) like '%locations%'
or lower(sql_text) like '%regions%'
)
and not lower(sql_text) like '%v$sql%'
order by sql_text
If we do the following:
(1..200).each do /id/
Employee.find(id) rescue nil
end
который пытается получить каждого employee (служащего) с id в диапазоне от 1 до 200. В конечном счете, в v$sql будет занесено следующее:
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * FROM employees WHERE (employees.id = 1)
SELECT * FROM employees WHERE (employees.id = 10)
SELECT * FROM employees WHERE (employees.id = 100)
SELECT * FROM employees WHERE (employees.id = 101)
...
Подобное поведение абсолютно не годится для приложения, которое должно быть масштабируемым, особенно для общих моделей использования, где вы можете ожидать большого количества запросов, использующих первичный ключ (например, обращение к таблице пользователей при входе в систему или для получения данных конфигурирования).
Полным ходом ведется работа, имеющая своей целью включить в Rails надлежащую обработку переменных связывания, или, по крайней мере, позволить адаптерам самой ActiveRecord выполнять связывание параметров в пределах запроса. А пока наилучшим вариантом представляется использование возможностей обработки перезаписи запросов, встроенных в базу данных.
Здесь приходит на выручку определенная конфигурация, предлагаемая Oracle. Параметр базы данных CURSOR_SHARING изменяет способ поведения базы данных при выполнении полных и частичных разборов при наличии запросов, в которых не используются параметры связывания. Параметру может быть назначено одно из значений exact , similar или force (последнее может быть установлено для всей базы данных или для определенного сеанса).
- exact - значение по умолчанию, когда база данных ведет себя так, как было описано выше.
- force - указывает базе данных, что необходимо переписывать все запросы, заменяя литералы в тексте SQL на параметры связывания (например: "SYS_B_0"), что приводит к одному анализируемому входу для каждого запроса.
- similar - переписывает запрос, но при этом оценивает эффект такого переписывания и генерирует различные входы в библиотечном кэше для запросов, которые закончились бы использованием различных планов выполнения - то есть, если замена литерала на параметр связывания приводит к другому плану выполнения, то замена не будет сделана.
До появления Rails 1.2, чтобы изменить установку совместного использования курсора, нужно было изменить ее для всего экземпляра базы данных или внести исправление в адаптер ActiveRecord. Начиная с последнего главного выпуска, был добавлен параметр конфигурирования адаптера, который делает именно это.
Установка cursor_sharing может быть использована в файле database.yml, чтобы выбрать предпочтительное значение, не смешивая его с глобальными параметрами базы данных (и таким образом сделать приложение лучшим "гражданином" в гетерогенной среде, где другим приложениям могут потребоваться другие параметры настройки).
development:
adapter: oracle
host: DEBIAN
username: rails
password: rails
cursor_sharing: similar
Его значение используется прямо в операторе alter session после установления подключения (в OracleConnectionFactory.new_connection):
conn.exec "alter session set cursor_sharing = #{cursor_sharing}" rescue nil Более того, значение по умолчанию было выбрано равным similar , что означает, что даже в том случае, если к параметрам не добавлено cursor_sharing, вы получите разумное поведение системы: запросы будут изменены согласно плану, который может использоваться в зависимости от значений.
Если вы повторите (redo) приведенный выше запрос, то вы увидите это в v$sql:
SELECT * FROM employees WHERE (employees.id = :"SYS_B_0")
всего один вход в библиотечном кэше для любого вызова Employee.find(id).
Чтобы видеть последствия similar в сравнении с force , обновите таблицу, чтобы получить некоторое искажение данных:
Employee.find(:all).each do /emp/
emp.salary = 3000 unless emp.id == 100
emp.save
end
Вы обновляете все записи, чтобы сделать зарплату равной 3 000$ за исключением того служащего, id которого равняется 100. Теперь можно изнутри проекта Rails добавить индекс к таблице служащих, генерируя миграцию:
C:\Progetti\ArticoliROR\Oracle\project>ruby script/generate migration AddIndexes
exists db/migrate
create db/migrate/007_add_indexes.rb
В сгенерированном файле вы должны получить
class AddIndexes < ActiveRecord::Migration
def self.up
add_index :employees, :salary, :name=>:idx_emp_salary
end
def self.down
remove_index :employees, :name=>:idx_emp_salary
end
end
после чего из командной строки мы делаем расчистку (rake) db:migrate. Теперь, если мы сделаем:
Employee.find(:all, :conditions=>["salary = ?", 24000])
Employee.find(:all, :conditions=>["salary = ?", 3000])
то в v$sql находятся два проанализированных (разобранных) входа:
SELECT * FROM employees WHERE (salary = :"SYS_B_0")
SELECT * FROM employees WHERE (salary = :"SYS_B_0")
Это происходит потому, что два сгенерированных запроса используют два различных плана выполнения в зависимости от значения зарплаты. (В первом случае используется индекс, а во втором случае был выбран полный просмотр таблицы, так как именно он является наилучшим планом.) Использование значения force для параметра совместного использования курсоров послужило бы указанием для базы данных, что для обоих запросов следует использовать один и тот же элемент. А это, конечно же, совсем не то, чего мы хотим в этом случае, даже если это означает, что у нас будет иметься только один элемент кэша.
Имея для этого параметра Rails-конфигурацию, давайте настраивать его значение на уровне всего приложение, в зависимости от того, какого вида имеются данные и какой характер использования предвидится для приложения.
Вторым параметром, добавленным в Rails 1.2, который помогает в настройке приложения, является конфигурационный параметр prefetch_rows. Он позволяет устанавливать параметр подключения OCI_ATTR_PREFETCH_ROWS, который указывает количество строк, которые будут предварительно выбраны из базы данных в каждом цикле обращения (round trip). Это дает большое увеличение производительности во всех случаях, когда нужно выбрать много строк.
Типичными моделями являются итерации по набору объектов, например, как в следующем операторе:
Employee.find(:all, :conditions=>"salary < 5000")
В этих случаях предварительная выборка строк означает меньшее число посещений базы данных, хотя это преимущество и достигается путем увеличения размера памяти, используемой адаптером - что, вообще говоря, не проблема, особенно, если желательно обработать все строки, возвращенные запросом.
Значение по умолчанию установлено равным 100. Это значение было идентифицировано организациями, принимавшими участие в разработке адаптера Oracle, как идеальное для диапазона совокупности тестовых данных. Но оно больше, чем должно быть значение числа предварительно выбранных строк для cursor_sharing, определенное путем эталонного тестирования с реальными наборами данных и запросами, используемыми в приложении. Для заказной настройки этот параметр, как обычно, устанавливается в файле database.yml:
development:
adapter: oracle
host: DEBIAN
username: rails
password: rails
prefetch_rows: 100