JDBC Debug to identify/Capture the queries in Weblogic/SOA Logs

Sometimes it is required to identify the list of SQL's executed for a specific operation. To check the list of SQL's got executed in weblogic server, it's easy to enable debug in JDBC level which will capture all the SQL's triggered through JDBC (Datasource - Connection Pool).

Enable Debug to see the SQL's triggered:
Login into WLS Admin console.
Select the SOA Server (need to repeat this for all SOA Servers the debug needs to be collected).
Go to Debug tab
Enable Weblogic -> JDBC -> sql -> DebugJDBCSQL.
Save and activate the changes.

Setting log level for server:

Debug logs getting into server log only when the server logging level is set to debug. Please make sure the server logging level is set to debug for the debug logs to be logged.

Login into WLS Admin console
Select the server (need to repeat this for all servers the logging level to be set)
Go to Logging tab -> Expand Advanced section
Set the Log file Severity level to "Debug".
Save the changes.
Restart the server for the changes to change effect.

Command Line in SetDomainEnv.sh /Debugging SQL's

Alternatively, these debug flags can also be set as system properties during WebLogic Server start:
-Dweblogic.Debug=weblogic.JDBCConn,weblogic.JDBCSQL,weblogic.JTAJDBC


If JDBC debug is enabled, SQL's triggered through the datasource will be logged. Below sample was collected for the queries triggered for cube_instance table through JDBCSQL debug.
 Example:

<Oct 25, 2011 12:33:54 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <orabpel.engine.pool-5.thread-1> <> <BEA1-00B22D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-0000000000000003> <1319567634097> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-35, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(SELECT wi.CIKEY, wi.NODE_ID, wi.SCOPE_ID, wi.COUNT_ID, wi.LABEL, ci.DOMAIN_NAME, ci.COMPONENT_NAME, ci.COMPOSITE_NAME, ci.COMPOSITE_LABEL, ci.COMPOSITE_REVISION, ci.COMPONENTTYPE, ci.PRIORITY, wi.EXP_DATE FROM WORK_ITEM wi, CUBE_INSTANCE ci WHERE wi.CIKEY = ci.CIKEY AND ci.STATE <= 3 AND wi.STATE = 1 AND wi.EXP_DATE is null AND wi.EXP_FLAG = 0 AND wi.EXECUTION_TYPE!= 1 AND wi.MODIFY_DATE< ? AND ci.COMPONENTTYPE = ?)>
<Oct 25, 2011 12:36:38 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <BEA1-04202D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-000000000000039c> <1319567798305> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-212, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(SELECT count(*) FROM CUBE_INSTANCE ci WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPONENT_NAME = ? AND ci.COMPOSITE_REVISION = ?)>
<Oct 25, 2011 12:36:38 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <BEA1-04222D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-000000000000039c> <1319567798317> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-213, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(SELECT count(*) FROM CUBE_INSTANCE ci WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPONENT_NAME = ? AND ci.COMPOSITE_REVISION = ? AND ci.STATE IN (3) )>
<Oct 25, 2011 12:36:38 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <BEA1-04242D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-000000000000039c> <1319567798325> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-214, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(SELECT count(*) FROM CUBE_INSTANCE ci WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPONENT_NAME = ? AND ci.COMPOSITE_REVISION = ? AND ci.STATE IN (6, 10) )>
<Oct 25, 2011 12:36:38 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <BEA1-04262D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-000000000000039c> <1319567798332> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-215, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(SELECT count(*) FROM CUBE_INSTANCE ci WHERE ci.COMPONENTTYPE = ? AND ci.DOMAIN_NAME = ? AND ci.COMPOSITE_NAME = ? AND ci.COMPONENT_NAME = ? AND ci.COMPOSITE_REVISION = ? AND ci.STATE IN (1) )>
<Oct 25, 2011 12:37:11 PM MDT> <Debug> <JDBCSQL> <mutsubra-pc> <AdminServer> <orabpel.invoke.pool-4.thread-1> <> <BEA1-04BD2D118D32C2CE5C8A> <d126c29923b443ab:1a3db08d:1333c594d0b:-8000-0000000000000406> <1319567831486> <BEA-000000> <[[weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection-SOADataSource-256, oracle.jdbc.driver.LogicalConnection@151b9890]] prepareStatement(INSERT INTO CUBE_INSTANCE (CIKEY, PARENT_REF_ID, AG_ROOT_ID, TEST_RUN_ID, COMPOSITE_LABEL, STATE, CONVERSATION_ID, EXT_STRING1, MODIFY_DATE, SCOPE_USIZE, STAGE, AT_COUNT_ID, CREATOR, ECID, PARENT_ID, TITLE, PRIORITY, TEST_SUITE, TEST_CASE, METADATA, SCOPE_CSIZE, COMPOSITE_NAME, STATUS, ROOT_ID, DOMAIN_NAME, CREATE_CLUSTER_NODE_ID, OUTCOME, CMPST_ID, EXT_INT1, AG_MILESTONE_PATH, COMPONENTTYPE, PROCESS_TYPE, MODIFIER, CREATION_DATE, AT_DETAIL_ID, COMPONENT_NAME, SCOPE_REVISION, EXT_STRING2, AT_EVENT_ID, COMPOSITE_REVISION, TEST_RUN_NAME, CACHE_...