К тому же, я, кажется, уже научился читать по лицам моих начальников, и они одобряют этот пост.

основные выводы

• никогда не писать select * from, поскольку уже при парсинге sql-запроса (а не при его исполнении) выделяется память под кэши в Prepared Statement для каждой возвращаемой запросом колонки из расчета максимального размера данных в колонке, умноженного на fetchSize (по-умолчанию равен 10 и означает количество строк из запроса, что транспортный слой драйвера должен получить из БД). Никогда не выгружать из базы те столбцы, что не собираетесь использовать (вот, кстати, один из ответов на вопрос - "а почему вы не используете Hibernate?")
• если используется старый Oracle JDBC-драйвер, то минимизировать использование памяти под кэши можно только изменением fetchSize, других механизмов нет. То есть рекомендуется использовать максимально свежую версию драйвера даже для коннекта со старыми релизами базы данных
• никогда не кэшировать Prepared Statement средствами стороннего пула (например, не стоит использовать такую опцию из стандартного DBCP) - они и так кешируются в Oracle JDBC-дайвере, причем отключить это кэширование достаточно сложно (невозможно до версии 11.2, как я помню). Иначе совершенно неправильно будет работает "внутренняя" логика в Prepared Statement по созданию и управлению буфферами под кэш значений, что приводит к очень сильному потреблению памяти под кэши
• выделять "побольше памяти", вернее, смириться с тем, что приложению необходимо больше памяти, чем, к примеру, в случае использования MySQL
• следует использовать драйвер 11.2.x.x и тогда можно будет задавать размеры буфферов в ручную
• Особенностью oracle драйвера является то, что он не может работать без кэша. Так что просто необходимо управлять его параметрами, поскольку по-умолчанию они настроены странно

Настройка

<bean id="daoPropertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="placeholderPrefix"><value>$jdbc{</value></property>
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>

    <bean id="oracle_prop" lazy-init="false"
          class="org.springframework.util.StringUtils"
          factory-method="collectionToDelimitedString">
        <constructor-arg index="0">
            <list>
                <value>v$session.program=MY-jdbc-pool</value>
                <value>useFetchSizeWithLongColumn=true</value>
                <value>defaultRowPrefetch=10</value>
                <value>disableDefineColumnType=true</value>
                <value>oracle.jdbc.useThreadLocalBufferCache=true</value>
                <value>oracle.jdbc.implicitStatementCacheSize=50</value>
                <value>oracle.jdbc.maxCachedBufferSize=18</value>
            </list>
        </constructor-arg>
        <constructor-arg value=";" index="1" type="java.lang.String"/>
    </bean>

    <bean id="my_oracle_datasource" 
          class="org.apache.commons.dbcp.BasicDataSource" 
          destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="$jdbc{jdbc.url}"/>
        <property name="username" value="$jdbc{jdbc.username}"/>
        <property name="password" value="$jdbc{jdbc.password}"/>
        <property name="initialSize" value="2"/>
        <property name="maxIdle" value="5"/>
        <property name="maxActive" value="10"/>
        <property name="maxWait" value="10"/>
        <property name="defaultAutoCommit" value="false"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <property name="validationQuery" value="select 1 from dual"/>
        <property name="connectionProperties" ref="oracle_prop"/>
        <property name="poolPreparedStatements" value="false"/>
        <property name="maxOpenPreparedStatements" value="0"/>
    </bean>

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

Oracle Database Release 10.2.0.4

• oracle.jdbc.freeMemoryOnEnterImplicitCache=true - полностью очищать буфер данных для Prepared Statement, когда он достается из пула (но буфер праметров при этом не трогается, кстати). Уже из определения свойства видно, что его использование вряд-ли как-то серьезно может помочь в управлении потребления памяти. Во-первых, очистка буфера и уменьшение его размера - это не одно и то же :), а во-вторых лично я вижу хоть какую-то пользу от этой операции если бы она выполнялась когда Statement возвращается в кэш, а не берется из него.

Oracle Database Release 11.1.0.7.0

• oracle.jdbc.maxCachedBufferSize - по-умолчанию равен Integer.MAX_VALUE. Максимальный размер буфера, который будет сохранен во внутреннем кэше
• oracle.jdbc.useThreadLocalBufferCache=true - оптимизация выделения буферов по потокам, а не по количеству коннекшенов (не выделяются буффера под неактивные коннекшены)

Oracle Database Release 11.2.x.x

• oracle.jdbc.maxCachedBufferSize - с версии драйвера 11.2 инициализация параметра maxCachedBufferSize может увеличить производительность очень больших систем с большим кэшем Prepared Statement и SQL-запросами, требующими совершенно разный настроек буферов кэша. В 11.2 значение maxCachedBufferSize интерпретируется как логарифм по основании 2 от максимального размера буфера. К примеру, если maxCachedBufferSize имеет значение 20, то максимальный буфер в кэше равен 1048576 (но в документе не указана единица измерения! Судя из экспериментов - это килобайты). Для обратной совместимости, значения более 30 интерпретируются как полное значение, а не как log2, но использование степени двойки предпочтительнее.
• oracle.jdbc.useThreadLocalBufferCache=true поддерживается как и ранее
• oracle.jdbc.implicitStatementCacheSize - Начальный размер кэша Prepared Statement. Задав этому параметру положительное значение вы включите кэширование Implicit Statement Cache.

А также

• не забудьте про размер fetchSize - он задается при помощи свойства defaultRowPrefetch - не самом деле этот параметр должен поддерживать не только Oracle, но и прочие jdbc-драйвера
• судя из документации, в версии 11.2.x.x должны корректно поддерживаться разные значения fetchSize, заданные для разных PreparedStatement#setFetchSize(int), а не только в общих настройках драйвера (помните, что именно этот параметр играет одно из решающих значение на потребление памяти драйвером)
• полезно также ознакомится с Connection Properties Recognized by Oracle JDBC Drivers и Oracle Extended Data Source Properties

Стратегия настройки

• Анализируете сколько памяти Вам не жалко и прописываете это в параметр oracle.jdbc.maxCachedBufferSize - не забудте что указанное значение будет интерпретироваться драйвером как "степень двойки"
• Если получаете exeption типа OutOfMemory "ноги" которого ростут из соответстующего места Оracle-драйвера - то начинайте уменьшать значение defaultRowPrefetch. Если вы опустились до "1" (достаточно бредовое значение, но я, например, больше чем "5" поставить не могу - и так Oracle гиг сжирает, т.е. maxCachedBufferSize = 20) но exception все-равно происходит - ну нет вариантов, покупайте память и увеличивайте oracle.jdbc.maxCachedBufferSize
• Кстати, величина этого параметра очень сильно влияет на скорость, с которой oracle начинает реально отдавать данные с сервера в приложение независимо от числа передаваемых записей, если в запросе очень большое количество полей. То есть, если вы столкнулись с тем что все в принципе работает, но первый запрос дико "тупит" - увеличивайте oracle.jdbc.maxCachedBufferSize

Результаты

Кстати, недавно мы переводили на Oracle еще одно аналогичное приложение - огромное потребление памяти самим JDBC-драйвером приводило к тому, что процессоры были загружены на 100%, но работал исключительно "сборщик мусора" (GC), занимаясь исключительно очисткой кэшей JDBC-драйвера. Так что примите мой совет - всегда проводите настройку кэшей в Oracle JDBC Driver. (Похоже, Oracle так увлекся идеей "каждой инсталляции Oracle - по сертифицированному админу", что даже их JDBC-драйвера требуют администрирования).

THE END