P6Spy 、 SQL Profiler

时间：2021-10-26 10:29:10

P6Spy

在优化Hibernate性能的时候，很重要的一点就是要看到Hibernate底层执行的SQL

虽然通过打印日志配合Hibernate的show_sql属性能够拼凑出Hibernate底层执行的SQL，但是很不方便，也不直观

下载地址：http://sourceforge.net/projects/p6spy/files/p6spy/

使用起来非常简单（项目只使用Hibernate，没有使用Spring）：

1，将p6spy.jar加入项目工程classpath中

2，将p6spy配置文件spy.properties加入项目工程/src目录下

3，修改Hibernate配置文件hibernate.cfg.xml：

<!--

　　<property name="connection.driver_class">

　　　　com.mysql.jdbc.Driver

　　</property>

-->

<property name="connection.driver_class">

　　com.p6spy.engine.spy.P6SpyDriver

</property>

4，修改p6spy配置文件：

驱动修改为Hibernate所使用的驱动

# oracle driver

# realdriver=oracle.jdbc.driver.OracleDriver

# mysql Connector/J driver

# realdriver=com.mysql.jdbc.Driver

# informix driver

# realdriver=com.informix.jdbc.IfxDriver

# ibm db2 driver

# realdriver=COM.ibm.db2.jdbc.net.DB2Driver

# the mysql open source driver

#realdriver=org.gjt.mm.mysql.Driver

realdriver=com.mysql.jdbc.Driver

deregisterdrivers一定要修改为true，原因注释已经说的很清楚了

#the DriverManager class sequentially tries every driver that is

#it's possible to registered to find the right driver.In some instances,

#load up the realdriver before the p6spy driver, in which case

#your connections will not get wrapped as the realdriver will "steal"

#the connection before p6spy sees it.  Set the following property to "true"

#to cause p6spy to explicitily deregister the realdrivers

deregisterdrivers=true

修改日志记录方式，可以选择控log4j控制日志输出、控制台输出日志、文件记录日志：

#specifies the appender to use for logging

#appender=com.p6spy.engine.logging.appender.Log4jLogger

#appender=com.p6spy.engine.logging.appender.StdoutLogger

appender=com.p6spy.engine.logging.appender.FileLogger

#name of logfile to use, note Windows users should make sure

#to use forward slashes in their pathname (e:/test/spy.log)

#(used for file logger only)

logfile=./log/spy.log

# append to  the p6spy log file.  if this is set to false the

# log file is truncated every time.  (file logger only)

append=true

#The following are for log4j logging only

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender

log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout

log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n

#log4j.appender.CHAINSAW_CLIENT=org.apache.log4j.net.SocketAppender

#log4j.appender.CHAINSAW_CLIENT.RemoteHost=localhost

#log4j.appender.CHAINSAW_CLIENT.Port=4445

#log4j.appender.CHAINSAW_CLIENT.LocationInfo=true

log4j.logger.p6spy=INFO,STDOUT

spy.log：

1399255288210|4|0|statement|select user0_.id as id0_, user0_.name as name0_, user0_.age as age0_ from

	TEST_USER user0_ where user0_.id>2|select user0_.id as id0_, user0_.name as name0_, user0_.age as age0_ from

	TEST_USER user0_ where user0_.id>2

1399255288217|-1||resultset|select user0_.id as id0_, user0_.name as name0_, user0_.age as age0_ from

	TEST_USER user0_ where user0_.id>2|age0_ = 30, id0_ = 3, name0_ = ll

SQL Proflier

P6Spy的日志非常繁琐，很难进行统计

SQL Profiler是一款基于P6Spy的图形化监控工具，不但能够监控SQL执行，统计SQL的执行结果，还能根据SQL的执行效能提供优化建议
下载地址：http://sourceforge.net/projects/sqlprofiler/files/sqlprofiler/

要想在原有的P6Spy的基础上运行SQL Profiler，需要对P6Spy的配置文件进行一些修改：

#specifies the appender to use for logging

appender=com.p6spy.engine.logging.appender.Log4jLogger

#appender=com.p6spy.engine.logging.appender.StdoutLogger

#appender=com.p6spy.engine.logging.appender.FileLogger

#The following are for log4j logging only

#log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender

#log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout

#log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n

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,STDOUT

log4j.logger.p6spy=DEBUG, SQLPROFILER_CLIENT

然后将sqlprofiler.jar放至e盘下，打开cmd运行java -Xms200m -Xmx512m -jar sqlprofiler.jar即可
P6Spy 、 SQL Profiler

更加详细的介绍，参见：http://www.ibm.com/developerworks/cn/java/j-lo-p6spy/

相关文章