解决生产环境接口应用包jboss数据库连接池满的问题

以前只排查过DHCP连接池泄露的问题，思路是通过btrace脚本收集日志排查问题，jboss用的是自带的连接池，不熟悉，经过查看官方文档终于找到jboss连接池的核心类，如下：

（1）Jboss连接池核心类：InternalManagedConnectionPool ：包含2个核心方法：returnConnection （），getConnection（）方法。

（2） 当应用需要进行业务处理时，首先会执行一个getConnection的操作，用于从连接池中获取连接，当业务处理完成后，需要把连接放回到连接池中，执行一个returnConnection的操作。

思路1：通过btrace脚本收集日志排查问题


btrace脚本：

监控DHCP连接池：

package test4tool;

import static com.sun.btrace.BTraceUtils.println;
import static com.sun.btrace.BTraceUtils.timeMillis;

import com.sun.btrace.BTraceUtils.Threads;
import com.sun.btrace.annotations.BTrace;
import com.sun.btrace.annotations.Export;
import com.sun.btrace.annotations.Kind;
import com.sun.btrace.annotations.Location;
import com.sun.btrace.annotations.OnMethod;
import com.sun.btrace.annotations.OnTimer;
import com.sun.btrace.annotations.Return;
import com.sun.btrace.annotations.Self;
import com.sun.btrace.annotations.TLS;

@BTrace 
public class BTraceConnection{
	@TLS
	private static long startTime = 0;

	@Export public static long openedCount;  
    @Export public static long closedCount;
    @Export public static long i;

    @OnMethod(
    clazz="/.*PoolingDataSource/",
    method="getConnection", 
    location=@Location(Kind.RETURN)
    )
    public static void m(@Return Object obj) {
    				startTime = timeMillis();
            openedCount++;  
            println("++++++++++++++++++++++++++++++++One connection is opened!++++++++++++++++++++++++++");  
            println(obj);  
            Threads.jstack();  
    }  

     @OnMethod(clazz="/.*PoolableConnection/", method="close")
    public static void d(@Self Object obj) {
            closedCount++;  
            println("======================One connection is closed!========================");  
            println(obj);  
            Threads.jstack();  
    } 

    @OnTimer(10000)  
   	public static void ontime() {
    	 println("Total opened connection:");  
         println(openedCount);  
         println("Total closed connection:");  
         println(closedCount); 
   } 
}

监控jboss连接池：

package test4tool;

import static com.sun.btrace.BTraceUtils.println;
import static com.sun.btrace.BTraceUtils.timeMillis;

import com.sun.btrace.BTraceUtils.Threads;
import com.sun.btrace.annotations.BTrace;
import com.sun.btrace.annotations.Export;
import com.sun.btrace.annotations.Kind;
import com.sun.btrace.annotations.Location;
import com.sun.btrace.annotations.OnMethod;
import com.sun.btrace.annotations.OnTimer;
import com.sun.btrace.annotations.Return;
import com.sun.btrace.annotations.Self;
import com.sun.btrace.annotations.TLS;

@BTrace 
public class BTraceConnection{
	@TLS
	private static long startTime = 0;

	@Export public static long openedCount;  
    @Export public static long closedCount;
    @Export public static long i;

    @OnMethod(
    clazz="/.*InternalManagedConnectionPool/",
    method="getConnection", 
    location=@Location(Kind.RETURN)
    )
    public static void m(@Return Object obj) {
    				startTime = timeMillis();
            openedCount++;  
            println("++++++++++++++++++++++++++++++++One connection is opened!++++++++++++++++++++++++++");  
            println(obj);  
            Threads.jstack();  
    }  

     @OnMethod(clazz="/.*InternalManagedConnectionPool/", method="returnConnection")
    public static void d(@Self Object obj) {
            closedCount++;  
            println("======================One connection is closed!========================");  
            println(obj);  
            Threads.jstack();  
    } 

    @OnTimer(10000)  
   	public static void ontime() {
    	   println("Total opened connection:");  
         println(openedCount);  
         println("Total closed connection:");  
         println(closedCount); 
   } 
}

思路2：抓取oracle数据库耗费资源的sql语句和 查询Oracle每天执行慢的SQL排查问题

抓取oracle数据库耗费资源的sql语句:
1.http://wukui127.blog.51cto.com/2866802/963370/

Oracle 查询每天执行慢的SQL:
http://blog.itpub.net/28602568/viewspace-1364844/

http://kamiff.iteye.com/blog/725343


参考：

http://www.dbafree.net/?p=378
http://docs.jboss.org/jbossas/javadoc/4.0.2/org/jboss/resource/connectionmanager/InternalManagedConnectionPool.java.html