几乎 80% - 85% 的数据库性能问题是由于应用数据库的设计或者应用程序本身的代码所引起的。因此良好的事务处理能力需要在设计应用程序的时候，在设计数据库的时候就考虑到性能和伸缩性。

 

         在我们使用Java EE开发企业级应用程序的过程中，总会涉及到系统的性能问题，并且都会与数据库进行打交道。当我们碰到数据库性能优化时，最有效的就是直接跟踪SQL 语句的执行情况，对SQL 语句写法进行优化、对索引进行优化，效果往往非常显著。

 

         Hibernate是当前非常流行的数据库DAO组件，在使用Hibernate的时候，有一个参数可以在控制台打印出SQL：

<property name="hibernate.show_sql" value="true"/>

但是即便如此，对于参数化SQL，依然没有办法全部显示出来，下述的情况想必大家都遇到过吧：

Hibernate: 
    select
        * 
    from
        ( select
            dictgroup0_.group_id as group1_0_,
            dictgroup0_.delete_flag as delete2_0_,
            dictgroup0_.group_code as group3_0_,
            dictgroup0_.group_desc as group4_0_,
            dictgroup0_.group_name as group5_0_,
            dictgroup0_.test_mode as test6_0_ 
        from
            dict_table_group dictgroup0_ 
        where
            dictgroup0_.delete_flag=? 
            and dictgroup0_.test_mode=? ) 
    where
        rownum <= ?

         因为“?”处没有具体数值，其实这样的语句对开发，调整SQL语句及程序的帮助是非常有限的，拷贝到类似于PLSQL Developer或者TOAD中无法看到SQL执行的结果。

本文通过在Java IDE工具中集成SQL语句拦截工具和SQL语句显示工具完成监控参数化SQL。

 

1.1       预期读者

1、开发人员：使用Java进行应用系统服务层、数据访问层开发。能够使用Java IDE工具，如Eclipse、IntelliJ IDEA等。

 

2          工具介绍

P6Spy：本文使用P6Spy作为拦截SQL的工具，是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架，相当于一个 SQL 语句的记录器，P6Spy 用 Log4J 来记录 JDBC 调用的日记信息。本文只使用P6Log，不使用P6Outage。该软件工作原理如下：

P6Spy 就是一个代理，它只做了一层对 JDBC 驱动的拦截，然后转发出去，与实际的应用程序没有任何的耦合性，除了在配置中将驱动程序改成 P6Spy 的拦截驱动外，程序其他地方并不需要做任何的改变。这层拦截器可能会给系统带来略微的性能下降，但对程序其他方面没有任何的影响。相对于这一点点的性能下降，相比它所带来的好处，在开发环境中对于开发人员来说是完全可以忽略不计的。

 

 

Sqlprofiler：这个小工具可以实时地显示数据库查询的情况，并能够和P6Spy关联。其它的信息也会进行收集和显示，比如：单个数据库请求的时间、一类请求的时间以及所有请求的时间。此外，该工具能够建立统计分析，并生成索引脚本，不过这个特性不在本文的讨论范畴

 

3          安装与设置

准备工作如下：

1、  下载P6Spy，下载地址：http://sourceforge.net/projects/p6spy/

2、  下载Sqlprofiler，下载地址：http://sourceforge.net/projects/sqlprofiler/

3、  一个能够运行Hibernate程序的Java工程。

 

本文使用的P6Spy版本为1.3，sqlprofiler版本为0.3。

 

安装P6Spy：

1、  打开p6spy-install.jar把p6spy.jar放到/WEB-INF/lib/ 目录下，或IDE能够识别jar文件的位置

2、  把spy.properties放到Java工程的src目录下，或者是IDE编译后能够拷贝到classess/目录下的位置。

3、  将应用系统中的原来数据库驱动名称改为为 P6Spy 的驱动程序名称 com.p6spy.engine.spy.P6SpyDriver ，其它的全部使用默认值。

4、  打开配置文件 spy.properties，找到 realdriver，把它的值改为你的应用系统的真正的数据库驱动名称。

# 提前加载驱动

deregisterdrivers=true


标准控制台输出：

appender=com.p6spy.engine.logging.appender.StdoutLogger
 
 log4j.logger.p6spy=INFO,STDOUT

 

在集成Tomcat环境下Spring框架下可以忽略第3步，按以下范例修改：

<bean id="myDataSourceTarget" class="org.apache.commons.dbcp.BasicDataSource"  destroy-method="close">
       <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>          
       <property name="url" value="jdbc:oracle:thin:@10.128.129.88:1521:invest"></property>
       <property name="username" value="请填写"></property>
       <property name="password" value="请填写"></property>
    </bean>
    
    <bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource" >
       <constructor-arg>
           <ref local="myDataSourceTarget" />
       </constructor-arg>
    </bean>

 

运行你的应用程序或 Web 应用程序，可以在 spy.log 里看到 P6Spy 监测到的 SQL 详细的执行与操作的记录信息了，包含有完整的 SQL 执行参数，只不过所有信息都在一行上，看着比较困难。

 

4          扩展

4.1       减少无用数据的输出

P6Spy将其能够拦截到的SQL数据都拦截下来，并输出到控制台，造成我们获取和程序关联信息的困难。在调试时主要关注，替换绑定变量后真实SQL，来检验业务的正确性，对其他的SQL，并不是太在意。我们通过修改配置文件和提供扩展代码完成只输出有用的信息，当需要展示全部信息的时候，也可以通过修改配置进行调整。

打开配置文件 spy.properties，修改如下信息：

module.log= com.p6spy.engine.logging.P6LogFactory

改为自定义的Factory（在这里体现了P6Spy的在设计上的灵活性），继承自P6LogFactory，并重载：

public ResultSet getResultSet(ResultSet real, P6Statement statement, String preparedQuery, String query) throws SQLException方法

 

返回定制的基于P6LogResultSet的ResultSet。

在定制的ResultSet中重载：

public boolean next() throws SQLException方法，代码从P6LogResultSet拷贝对应代码，修改如下：

if (currRow > -1) {
                            long startTime = System.currentTimeMillis(); 
                            StringBuffer buffer = new StringBuffer();
                            String comma = "";
                            for (Iterator itr = resultMap.keySet().iterator(); itr.hasNext();) {
                                     String index = (String) itr.next();
                                     buffer.append(comma);
                                     buffer.append(index);
                                     buffer.append(" = ");
                                     buffer.append((String) resultMap.get(index));
                                     comma = ", ";
                            }
 
                            P6Connection p6connection = (P6Connection) this.statement
                                               .getConnection();

                            P6LogQuery.logElapsed(p6connection.getId(), startTime, "resultset",
                                               preparedQuery, query);
 
                            resultMap.clear();
                   }
                   currRow++;
                   return passthru.next();

 

这时候打在控制台的就是清理后实际执行的SQL语句了。

 

4.2       输出到Sqlprofiler看格式化结果

Sqlprofiler以监听的模式获取P6Spy的SQL输出，并根据P6Spy的输出格式将SQL拆解显示，一般来说我们并不需要关注P6Spy的SQL格式，只需要按照以下步骤进行配置：

1、  把Log4j配置为Server模式

appender=com.p6spy.engine.logging.appender.Log4jLogger
 
log4j.appender.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender
log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost
log4j.appender.SQLPROFILER_CLIENT.Port=4445
log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true
 
log4j.logger.p6spy=INFO,SQLPROFILER_CLIENT

 

注：请注意Sqlprofiler对应的端口号为4445

 

2、  打开Sqlprofiler，接收SQL脚本

下图为Sqlprofiler正常工作下的截图：

 

SSMS监控锁表的SQL sql语句监控_数据库