what every dba needs to know about jdbc connection pools

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

What every DBA needs to know about JDBC connection pools Bridging the language barrier between DBA and Middleware Administrators

Jacco H. Landlust Platform Architect Director Oracle Consulting NL, Core Technology October, 2014

Presented at


Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3


Program Agenda

Terminology

Connection Pool configuration

Perhaps some other helpful topics

Q&A

4


Terminology Ensure you are talking the same language as your middleware friends

5


WebLogic Terminology

6

Domain

Node Manager Node Manager

Machine A Machine B AdminServer

Cluster

Managed Server Managed Server

Managed Server


Resources and Services within a Domain

• Can be target at One or Multiple Managed Servers

• Examples:

– application components, such as EJBs

– security providers

– resource adapters

– diagnostics and monitoring services

– JDBC data sources

– JMS servers or other messaging resources

– persistent store

7


Data source

• A Data Source object is the representation of a data source in the Java programming language. In basic terms, a data source is a facility for storing data

• An object that implements the Data Source interface will typically be registered with a naming service based on the Java Naming and Directory Interface (JNDI) API

• The Data Source interface is implemented by a driver vendor (in this presentation examples are based on Oracle’s driver)

8


Data source; 3 types of implementations

• Basic implementation — produces a standard Connection object

• Connection pooling implementation — produces a Connection object that will automatically participate in connection pooling. This implementation works with a middle-tier connection pooling manager

• Distributed transaction implementation — produces a Connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pooling manager.

9


Distributed transactions (XA)

• “eXtended Architecture”, An X/Open group standard

• For executing a "global transaction" that accesses more than one back-end data-store, thus XA is a type of transaction coordination

• Two-phase commit (2PC)

10


Be alarmed if your developer calls every transaction distributed

11


Java Transaction API (JTA)

• Enables distributed transactions to be done across multiple XA resources

• Can even be cross domain (warning: complicated setup!)

• Configuration settings for JTA are applicable at the domain level. This means that configuration attribute settings (e.g. timeout) apply to all servers within a domain

• Monitoring and logging tasks for JTA are performed at the server level

• Configuration settings for participating resources (such as JDBC data sources) are per configured object

12


Data sources in Oracle WebLogic Server

• Generic

• Multi-Data source

• Gridlink

13


Generic Data source

• Provide connection management processes that help keep your system running efficiently

• Connects to a specific database (service)

• Contains a pool of database connections that are created when the data source is created and at server startup

14

RDBMS

Generic DS

WLS


RAC

Multi-Data source

• Abstraction around a group of generic data sources

• The multi data source determines which data source to use to satisfy the request depending on the algorithm selected in the multi data source configuration: load balancing or failover

15

INST1

Generic DS

WLS

INST2

Generic DS

Multi DS


RAC

Gridlink Data source

• Event-based data source that adaptively responds to state changes in an Oracle RAC instance

• Key foundation for providing deeper integration with Oracle RAC

• Responds to FAN events to provide Fast Connection Failover (FCF), Runtime Connection Load-Balancing (RCLB), and RAC instance graceful shutdown

• XA affinity is supported at the global transaction ID level

16

INST1

WLS

INST2

Gridlink DS


A GridLink data source uses Fast Connection Failover to:

• Provides rapid failure detection

• Abort and remove invalid connections from the connection pool

• Perform graceful shutdown for planned and unplanned Oracle RAC node outages

• Adapt to changes in topology, such as adding or removing a node

• Distribute runtime work requests to all active Oracle RAC instances, including those rejoining a cluster

17


Runtime Connection Load Balancing allows WebLogic Server to:

• Adjust the distribution of work based on back end node capacities such as CPU, availability, and current workload

• React to changes in Oracle RAC topology

• Manage pooled connections for high performance and scalability

18


XA affinity

• Performance feature that ensures that all database operations performed on a RAC cluster within the context of a global transaction are directed to the same RAC instance.

• Do not confuse this with DTP services!

• Will be established based on the global transaction id, instead of by individual data source, to ensure that connections obtained from different data sources that are configured for the same RAC cluster are all associated with the same RAC instance..

19


Use Gridlink whenever using RAC*

20


Connection pools Cache of database connections maintained so connections can be reused when future requests to the database are required

21


Connection properties: JDBC URL

jdbc:oracle:<drivertype>:@<connection data>

22

thin|oci

@host:port/service

@host:port:instance

@ldap://host:port/entry,cn=OracleContext,dc=com


Connection properties: JDBC URL & SCAN usage

• Requires 11.2.0.2 JDBC Driver

• Included in WebLogic version 10.3.4 and newer

23


Connection Pool: Capacity

• Settings are per managed server

• Available settings: Initial, Min, Max

• Direct link to processes parameter configured per database instance: PROCESSES = ( #ManagedServers * MAX) + 30*

• Test what happens if your application runs out of connections!

24


Consider using a value of 5000 operating system processes to be connected to Oracle concurrently

Fusion Middleware Performance and Tuning Guide 11g Release 1

25


Connection Pool: Capacity Management

Set Shrink frequency to manage aggressive capacity management

(default 900 seconds) Set Inactive Connection Timeout to

return unused connections (default 0 = disabled)

Set Login Delay to prevent logon storm (default 0)

26


Connection Pool: Pinned to Thread

• Minimize the time it takes for an application to reserve a database connection from a data source

• Eliminates contention between threads for a database connection

• Do not use in combination with Multi Data Sources

• Maximum Capacity is ignored

• Shrinking does not apply

• When you Reset a connection pool, the reset connections from the connection pool are marked as Test Needed.

27


Connection Pool: Row Prefetching

• Every time an application asks the driver to retrieve a row from the database, several rows are prefetched with it and stored in client-side memory. In this way, several subsequent requests do not have to execute database calls to fetch data

• Default disabled at data source level

• Some memory structures are pre-initialise to hold the full prefetch size.

28


Configure prefetch size at per-query basis

29


Connection Pool: Statement Cache

• Size of cache is 1:1 related to OPEN_CURSORS database parameter

• Type [LRU|Fixed]:

– LRU: After the Statement Cache Size is met, the Least Recently Used statement is removed when a new statement is used

– Fixed: The first Statement Cache Size number of statements is stored and stay fixed in the cache. No new statements are cached unless the cache is manually cleared or the cache size is increased.

30


Connection Pool: Test Connections on Reserve

• Test each connection before giving to a client

• Adds slight delay to the request

• Seconds to Trust an Idle Pool Connection setting to minimize delay

• Only use dual for test SQL table to minimize unnecessary LIO

31


Connection Pool: Statement Timeout

• The time after which a statement currently being executed will time out.

• A value of -1 disables this feature

• A value of 0 means that statements will not time out

• Statement Timeout <= JTA

• This will not kill the database session/transaction! So load on database server will remain.

32


Connection Pool: XA transaction timeout

• Whenever the timeout has been reached, an error will be thrown at the client starting the transaction. The actual transaction will be rolled back. The actual error is:

ORA-02049 timeout: distributed transaction waiting for lock

• XA Transaction Timeout <= DISTRIBUTED_LOCK_TIMEOUT

• Extra privileges required for monitoring:

– dba_2pc_pending, dba_2pc_neighbors

– dba_pending_transactions

– v$global_transactions

33


Other helpful topics Some semi-random topics that might be helpful when working with WebLogic and JDBC

34


Use jnettrace.jar to Debug Connectivity issues

• java -jar jnettrace.jar <Database Server Host> <Database Server Port> <JavaNet_Port>

• http://www.oracle.com/technetwork/database/enterprise-edition/jnettrace.jar

35


JDBC persistent stores

You can configure a persistency store to use JDBC for TLOGs and JMS, which provides the following benefits:

• Leverages replication and HA characteristics of the underlying database.

• Simplifies disaster recovery by allowing the easy synchronization of the state of the database and JMS/TLOGs.

• Improved Transaction Recovery service migration as the JMS mssages / Transaction Logs to do not need to be migrated (copied) to a new location.

36


Hints when configuring JDBC persistent stores

• Default store used long raw data type, have your MW admin configure “Create Tables from DLL file” to oracle_blob_securefile.ddl

• Configure WorkerCount (a.k.a. I/O multithreading)

• Setup reverse index on primary key of persistent store

• Setup hash partition on persistent store table

• Persistent stores for JMS tables should use MDS instead of Gridlink.

37


WebLogic 10.3.6 and newer in HA scenario’s: use JDBC based persistent stores

38


Rolling Upgrades at Database level

• Data source must run against service

• Failover service before shutdown of instance

• Let connection dry out (= wait)

• Stop instance, patch and start instance

• Repeat for other instances

39


Wrap data types

If something like this shows up in the log file of WebLogic:

java.lang.ClassCastException: WebLogic.jdbc.wrapper.Blob_oracle_sql_BLOB

at

oracle.ifs.server.S_LibrarySession.getTokenCredentialBlob(S_LibrarySess

ion.java:23888)

You do not have a database issue, you have a WebLogic issue: Disable wrap data types

40


WLS provides the ability to disable wrapping which provides the following benefits

• Although WLS generates a dynamic proxy for vendor methods that implement an interface to show through the wrapper, some data types do not implement an interface. For example, Oracle data types Array, Blob, Clob, NClob, Ref, SQLXML, and Struct are classes that do not implement interfaces. Disabling wrapping allows applications to use native driver objects directly.

• Eliminating wrapping overhead can provide a significant performance improvement.

41


Ignore in use connections upon shutdown

42

• Default enabled

• Can kill in-flight transactions, and therefore jeopardize RPO=0 theories

• When disabled shutdown can take (very) long time.


Currently, Oracle Fusion Middleware does not support configuring Oracle Active Data Guard for the database repositories that are a part of the Fusion Middleware topology.

From: Disaster Recovery for Oracle Exalogic Elastic Cloud

43


Oracle Fusion Middleware SOA does not support Oracle Active Data Guard because the SOA components execute and update information regarding SOA composite instances in the database as soon as they are started. From: Best Practices for Oracle Fusion Middleware SOA 11g Multi

Data Center Active-Active Deployment

44


Exadata / Exalogic

• Pass startup options to Managed Server: -Djava.net.preferIPv4Stack=true -Doracle.net.SDP=true

• Change protocol from TCP to SDP n JDBC_URL

• No SCAN on SDP

• Monitoring SDP Sockets Using sdpnetstat on Oracle Linux

45


Questions? and hopefully also some answers

46

what every dba needs to know about jdbc connection pools

Documents