After my blog on tuning CMP was published as a featured article at TheserverSide.com, I got a few emails from readers asking whether there are any other ways to generate performance statistics on SQL statements used in J2EE applications. The approach outlined in my blog was to use EXPLAIN PLAN with P6SPY and I agree that is not the most efficient and convenient method.
I was a database developer in the early part of my career and I found SQLTrace very useful those days. Oracle database provides diagnostic features such as SQLTrace and TKPROF to gather statistics about SQL being executed in an application. These utilities are really handy to find out bottlenecks in the SQL being used by database applications. These can also be used with J2EE applications that accesses Oracle database. Also these can be used to examine the SQL being generated by the CMP engine of your J2EE container. Using SQLTRACE you can gather more information that using P6SPY and EXPLAIN PLAN at one shot.
J2EE applications normally use connection pooling provided by J2EE containers and J2EE containers do not provide ability to switch on SQLTrace for the connection pool being maintained on the fly.
As an application developer, you have two alternatives to turn on SQLTrace from your J2EE applications. First is to make changes in your J2EE applications to turn on SQLTrace. This requires code changes and probably you want to avoid this approach.
The second option is neat and requires creation of database trigger in the database schema. This does not require code modification in your J2EE application however needs some basic knowledge of SQL and help from your DBA.
Let us assume we are using the following connection pool and we want to turn on SQLTRACE for the application that uses this connection pool.
<connection-pool name="OracleDS Pool" min-connections="1"
max-connections="1">
<connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@localhost:1521:DEBU">
</connection-factory>
</connection-pool>
If you note from the above connection pool is that the database user being used is SCOTT. To turn on SQLTRACE for this application we have to create a database logon trigger to enable SQLTrace for SCOTT user when user is connected to the database.
CREATE OR REPLACE TRIGGER scott_logon_trg
AFTER LOGON ON DATABASE
BEGIN
IF (user = 'SCOTT') THEN
DBMS_SESSION.SET_SQL_TRACE(true);
END IF;
END;
Try to keep the connection pool to absolute minimum so that trace files are generated only for few user sessions. If you look at the connection pool for my test I’ve specified the connection to have only one connection pool.
After you created the database logon trigger, you can restart your J2EE container and then run your applications. The trace files for your user sessions will be generated in the USER_DUMP_DESTINATION for the database and you can take these trace files and use TKPROF to analyze the performance statistics for the SQL generated for your application.
Please remember that making friendship with your DBA always helps.
In OC4J 10.1.3 we are providing an ability to users to switch on SQLTrace by invoking an MBean method for the DataSource from our JMX Console at runtime and you will not have to go through this pain to switch on SQLTrace.
7:13:36 AM 
|
