Oracle JDBC driver - выделение буферной области под запрос

Довелось мне тут на днях (ну конечно, подумаешь, месяц прошел, не полгода же...) две любопытных ссылочки изучить.

Официальная документация и написанный по ней отзыв.

Документ описывает, например, почему мы вообще "переезжаем" с одной версии Oracle JDBC driver на другую.

The 7.3.4, 8i, and 9i Oracle JDBC drivers used more or less the minimal amount of

memory possible. Unfortunately this resulted in an unacceptable performance penalty.

The drivers were rearchitected in 10g to improve performance. One of the most important

changes in that rearchitecture was how the drivers used memory. The development team

made a specific decision to trade memory footprint for performance. As a result the 10g

drivers were on average about 30% faster than the 9i drivers.

В версиях 7.3.4, 8i и 9i драйвер поглощал больше или меньше минимального числа доступной памяти. Что приводило к плачевным результатам производительности. Однако! В 10 версии разработчики сделали одно очень важное изменение - рефакторинг способо испорльзования памяти драйвером.  Благодаря этому драйвер версии 10g работает на 30% быстрее, чем 9 драйвер! Вы все еще на 9 Oracle JDBC driver? Тогда мы идем к вам!

Что это дает с точки зрения тестирования? Ну да, мало что... Можно, конечно, узнав, что у Заказчика стоит 7-9 версия, попрыгать вокруг с бубном, проверив производительность... Но меня больше заинтересовала другая часть документации.

Как происходит буферизация области для запроса к БД?

10g драйвер имеет более большую и сложную структуру, нежели предыдущие версии. Объекты содержат в себе больше информации и, соответственно, занимают больше помяти. Это увеличивает размер используемой памяти, но не это является главной проблемой. Главной проблемой является буфер, используемый для хранения результата запроса.

Each Statement (including PreparedStatement and CallableStatement) holds two

buffers, one byte[] and one char[]. The char[] stores all the row data of character type: CHAR,VARCHAR2, NCHAR, etc. The byte[] stores all the rest. These buffers are allocated when theSQL string is parsed, generally the first time the Statement is executed. The Statement holdsthese two buffers until it is closed

Каждый  Statement захватывает 2 буфера, один byte[], один char[].

• В буфере char[] хранятся все символьные типы - CHAR,  VARCHAR2, NCHAR и прочие.
• В буфере byte[] содержатся все остальные типы.

Далее барабанная дробь - так как буферная область выделяется, когда SQL запрос парсится, то размер буферной области не зависит от реального размера данных, возвращаемых запросом. Он зависит от максимально возможного значения буферной области. То есть, распарсив запрос, мы уже знаем типы всех колонок таблицы, к которой будем обращаться - и драйвер суммирует значения, которые ему надо выделить.

И даже если вы хотите заинсертить всего-лишь одно единственное значение в таблицу с 600-стами колонками, увы... Но по порядку...

The driver also has the fetchSize, the number of rows to retrieve on each fetch. With the size of each

column and the number of rows, the driver can compute the absolute maximum size of the data

returned in a single fetch. That is the size of the buffers.

Some types such as LONG and LONG RAW are too large to store in line in the buffers and are

handled differently. If the query result contains a LONG or LONG RAW, the fetch size is set to

1, which addresses most memory problems as will become clear further on. These types will not

be discussed further.

У драйвера также есть параметр fetchSize, число строк, которые он будет фетчить. И размер буферной области, которую он будет выделять на попытку обратить к БД - это перемножение fetchSize (число строк) на значения выделямой под каждую колонку памяти.

Некоторые типы, такие как LONG - слишком большие для хранения в буффере. Если в запросе есть такой тип, значение fetchSize устанавливается равным 1, но все равно приводит к большим проблемам с памятью. Поэтому такие типы далее не рассматриваются.

И, конечно же, с примерами и цифрами:

Character data is stored in the char[] buffer. Java chars take up two bytes per character. A VARCHAR2(10) column will contain a maximum of ten characters, or ten Java chars or 20 bytes per row. A VARCHAR2(4000) column will take 8K bytes per row. What matters is the defined size of the column, not the size of the actual data. A VARCHAR2(4000) column that contains all NULLs will still take 8K bytes per row. The buffers are allocated before the driver sees the query results so the driver must allocate enough memory for the largest possible result. A column defined as VARCHAR2(4000) can contain up to 4000 characters. The buffer must be allocated large enough to hold 4000 chars, even if the actual result data is not so large.

BFILE, BLOB, and CLOB values are stored as locators. The locators can be up to 4K bytes, so for each BFILE, BLOB, and CLOB column, the byte[] must have at least 4K bytes per row. RAW columns can contain up to 4K bytes. Other types take substantially less. A reasonable approximation is to assume 22 bytes per column per row for all other types.

Example:

CREATE TABLE TAB (ID NUMBER(10), NAME VARCHAR2(40), DOB DATE)

ResultSet r = stmt.executeQuery(“SELECT * FROM TAB”);

When the driver executes the executeQuery method, the database will parse the SQL. The database will report that the result will have three columns: a NUMBER(10), a VARCHAR2(40), and a DATE. The first column needs (approximately) 22 bytes per row. The second column needs 40 chars per row. The third column needs (approximately) 22 bytes per row. So one row needs 22 + (40 * 2) + 22 = 124 bytes per row. Remember that each character needs two bytes.

The default fetchSize is 10 rows, so the driver will allocate a char[] of 10 * 40 = 400 chars (800 bytes) and a byte[] of 10 * (22 + 22) = 440 bytes, total of 1240 bytes. 1240 bytes is not going to cause a memory problem. Some query results are bigger.

In the worst case, consider a query that returns 255 VARCHAR2(4000) columns. Each column takes 8k bytes per row. Times 255 columns is 2040K bytes or 2MB per row. If the fetchSize is set to 1000 rows, then the driver will try to allocate a 2GB char[]. This would be bad.

Вольный перевод:

Символьные значения хранятся в буферной области типа char[]. Колонка типа VARCHAR2(10) может содержать максимум 10 символов, или 10 Java символов, или 20 байт для каждой (!) строчки. Строчек, напомню, ровно столько, сколько указано в настройке fetchSize драйвера. Колонка типа VARCHAR2(4000) занимает уже 8 Кб на каждую строчку!

Ну и самое главное - нас волнует только размер колонки, а размер актуальных данных вообще не колышет. Колонка типа VARCHAR2(4000) может содержать все значения, равные NULL - неважно! Все равно выделяем ей 8 Кб. Буферная область выделяет ДО того, как драйвер увидит результаты запроса. Именно поэтому он должен выделить количество памяти, достаточное для выполнения самого большого запроса.

Это, имхо, самая основная мысль данной статьи. Но едем дальше. Колонка типа VARCHAR2(4000) может содержать до 4000 символов - а значит, буферная область ей выделяется под 4000 символов, даже если реальные данные намного меньше.

BFILE, BLOD, CLOB значения хранятся как локаторы. Локаторы занимают 4 Кб, соответственно, под каждую колонку такого типа выделяет по 4 Кб буферной области на каждую строку. Тип RAW может содержать также до 4 Кб. Другим типам требуется значительно меньше памяти, Разумное приближение - принимать по 22 байта за колонку (в каждой строке) для всех остальных типов.

Пример!

CREATE TABLE TAB (ID NUMBER(10), NAME VARCHAR2(40), DOB DATE)

ResultSet r = stmt.executeQuery(“SELECT * FROM TAB”);

Когда драйвер выполняет executeQuery метод, БД парсит SQL. БД сообщает, что результато запроса являются 3 колонки: NUMBER(10), VARCHAR2(40) и DATE. Для первой колонки необходимо (приблизительно) 22 байта на каждую строчку. Второй колонке необходимо 40 символов на каждую строку (по 2 байта на символ). Третьей колонке необходимо (приблизительно) 22 байта на строчку.

Таким образом, на одну строчку нам необходимо 22 + (40 * 2) + 22 = 124 байта.

Помните, что каждый символ занимает 2 байта! Стандартное значение fetchSize = 10 строк, таким образом, драйвер выделяет для символьной колонки char[] 10 * 40 = 400 символов (800 байт) и для byte[] колонок, которых две - 10 * (22 + 22) = 440 байт, всего 1240 байт.

1240 байт не приводит к проблемам с памятью, правда? Но некоторые результаты запросов побольше.

В худшем случае, представим, что запрос возвращает 255 колонок типа VARCHAR2(4000). Каждая колонка забирает по 8 Кб на строку. На 255 колонок необходимо уже 2040 Кб или же 2 Мб на строку! Если fetchSize = 1000 строк, то драйвер будет пытаться выделить 2 Гб char[]. И ему станет плохо

Что же мы выносим из данной статьи? А то, что следить за размерами БД все-таки стоит. Это тестовые примерчики, на которых мы учимся писать запросы - все такие милые и простые. И БД тестовые у нас маааленькие. А реальные побольше будут.

И когда Заказчик хочет, чтобы у него колонка была рассчитана на 4000 символов по принципу, "ну а на всякий случай!", то, прочитав эту статью, поневоле задумаешься... А так ли уже это просто? А, фигня вопрос - пошел изменил скрипт создания БД и добавил скрипт миграции. Но правда ли все так просто? Может, все-таки стоит выяснить причину, почему Заказчику надо так много места и даже, возможно, убедить его, что и сотни символов будет достаточно?

А еще мне очень нравится вывод, сделанный из вышесказанного:

There are several things a user can do to manage the size of these buffers.

1 Define tables carefully

2 Code queries carefully

3 Set the fetchSize carefully

Чтобы разумно управлять размеров буферной области:

1. Определяйте размеры колонок аккуратно.
2. Кодируйте запросы к БД аккуратно.
3. Устанавливайте fetchSize значение аккуратно.

И будет Вам счастье

Ну а про всякие страшные настройки драйвера аля:

• oracle.jdbc.freeMemoryOnEnterImplicitCache
• oracle.jdbc.maxCachedBufferSize
• oracle.jdbc.useThreadLocalBufferCache
• oracle.jdbc.implicitStatementCacheSize

Советую почитать в блоге Ромы, если не хочется читать в оригинале. В конце концов, это мнение разработчика, что бывает очень и очень полезно узнать