Java EE 6 Performance - Datasource Configuration and Good Hibernate Properties for Oracle Relational Databases

Hello Everyone...
I am back!

In this post i would like to present some configurations to optimize your web application that uses Hibernate 4/5 and Oracle relational database.

Web Applications can require two types of transaction configuration using EJB concepts, Bean management or JTA/Container management transaction. In both case you need to delivery good results on every request...that's your challenge!

So, try to use these configurations over Hibernate and Datasource for JBoss EAP 6.x Middleware:

1 - Modules with JMS, Data Mining, Quartz Jobs, Multithreads - Executors (persistence.xml) or modules that use batch updates over the same transaction - until 1500 rows per request

        <properties>
            <property name="hibernate.show_sql"   value="false"/>
            <property name="hibernate.format_sql" value="false"/>
              <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
             <property name="hibernate.generate_statistics" value="false" />
            <property name="hibernate.connection.release_mode" value="after_transaction" />            <property name="hibernate.transaction.jta.platform"    value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />
           <property name="hibernate.jdbc.use_streams_for_binary" value="true" />

            <property name="hibernate.order_inserts" value="true"/>
            <property name="hibernate.order_updates" value="true"/>
            <property name="hibernate.jdbc.fetch_size" value="100"/>
            <property name="hibernate.jdbc.batch_size" value="100"/>            <property name="hibernate.cache.use_second_level_cache" value="false"/>


2 - Web Modules only (frontend) or WebServices (persistence.xml)
Transactions with 100 or 500 rows per request

        <properties>
            <property name="hibernate.show_sql"   value="false"/>
            <property name="hibernate.format_sql" value="false"/>
              <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
             <property name="hibernate.generate_statistics" value="false" />
            <property name="hibernate.connection.release_mode" value="on_close" />            <property name="hibernate.transaction.jta.platform"    value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />
             <property name="hibernate.jdbc.use_streams_for_binary" value="true" />

            <property name="hibernate.order_inserts" value="true"/>
            <property name="hibernate.order_updates" value="true"/>
            <property name="hibernate.jdbc.fetch_size" value="50"/>
            <property name="hibernate.cache.use_second_level_cache" value="false"/>


3 - JTA Datasource
(for JMS modules, in most of cases, XA Driver is recommended)

                <datasource jta="true" jndi-name="java:/jdbc/<name>" pool-name="jdbc/<name>" enabled="true" use-ccm="false"
                            statistics-enabled="true">
                        <connection-url>jdbc:oracle:thin:@//<ip>:<port>/<service></connection-url>
                        <driver>oracle</driver>
                        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                        <pool>
                            <!-- min 60% of max - dont forget this --> 
                            <min-pool-size>32</min-pool-size>
                        <max-pool-size>46</max-pool-size>
                        <prefill>true</prefill>
                        <flush-strategy>IdleConnections</flush-strategy>
                        </pool>
                        <security>
                            <user-name>xxxx</user-name>
                            <password>xxxx</password>
                        </security>
            <validation>
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
                <background-validation>true</background-validation>
                <background-validation-millis>120000</background-validation-millis>                 <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
            </validation>
            <timeout>
                <idle-timeout-minutes>1</idle-timeout-minutes>                 </timeout>
            <statement>
                <share-prepared-statements>true</share-prepared-statements>
            </statement>