OAS/OC4J and OpenSource JDBC profiling using Log4J, p6spy and SQLProfiler

* Add the contents of the provided applib directory to your oc4j applib to the applib of your oc4j

oasStartup.jar
log4j-1.2.8.jar
p6spy.properties
p6spy.jar


* Add log4j jars and log4j startup class to server.xml

<init-library path="../applib/oasStartup.jar" />
<init-library path="../applib/log4j-1.2.8.jar" />


* Add log4j startup-class to the startup-classes in your server.xml

<startup-classes>
<startup-class classname="util.OASLog4JStartup" failure-is-fatal="true" >
<execution-order>0</execution-order>
<init-param>
<param-name>LOGGING_CONFIG_FILE_NAME</param-name>
<param-value>config/log4j.xml</param-value>
</init-param>
<init-param>
<param-name>CONFIG_RELOAD_INTERVAL</param-name>
<param-value>1500</param-value>
</init-param>
</startup-class>
</startup-classes>


* Add the following lines to you config/log4j.xml or use the provided config/log4j.xml

<appender name="SQLPROFILER_CLIENT" class="org.apache.log4j.net.SocketAppender">
<param name="RemoteHost" value="localhost"/>
<param name="Port" value="4445"/>
<param name="LocationInfo" value="true"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%p | %m %c%n"/>
</layout>
</appender>
<appender name="p6spyfileappender" class="org.apache.log4j.DailyRollingFileAppender">
<param name="DatePattern" value="'.'dd-MMM-yyyy"/>
<param name="File" value="./log/p6spy.log"/>
<param name="Append" value="false"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%p | %m %c%n"/>
</layout>
</appender>
<appender name="ConsoleAppender" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%p | %m %c%n"/>
</layout>
</appender>

<logger name="p6spy">
<level value="DEBUG"/>
<!-- send log statements to sqlprofiler -->
<appender-ref ref="SQLPROFILER_CLIENT"/>
<!-- write out the log statements -->
<appender-ref ref="p6spyfileappender"/>
</logger>
<root>
<level value="INFO"/>
<appender-ref ref="ConsoleAppender"/>
</root>


* Edit your data-sources.xml to use the p6spy driver and make sure you use the
com.evermind.sql.DriverManagerDataSource driver

class="com.evermind.sql.DriverManagerDataSource"
connection-driver="com.p6spy.engine.spy.P6SpyDriver"


* profiling your sql
start sqlprofiler using<br/>
java –jar sqlprofiler<br/>
<br/>

''''start oc4j and it will start logging sql queries to the sqlprofiler as well as to the

p6spyfileappender''''