4

I'd like to log all the SQL that my application is issuing, so I know EXACTLY what it's doing. I've setup Java Util Logging for Oracle according to this answer, to try to capture things at the lowest level I can.

My application is using Hibernate (currently 3.2.3.ga).

When I use oracle.jdbc.level=CONFIG or oracle.jdbc.level=FINE in my logging.properties, see a pretty reasonable level of logging, but I don't see ANY of the update statements my application is issuing. I only see select and delete statements, for example:

CONFIG oracle.jdbc: 73FCEAC9 SQL: select <REDACTED FOR STACK OVERFLOW>

When I use oracle.jdbc.level=FINEST in my logging.properties, see update and insert statements, but the logging volume is FAR greater than is practical. It seems like they're getting caught by the code to "Logs calls to internal methods" with the FINEST setting, and a single update statement will show up in the logs dozens of times. Here's an example:

FINEST oracle.jdbc: 212E0078 Enter: "update <REDACTED FOR STACK OVERFLOW>" 
FINEST oracle.jdbc: 212E0078 Enter: "update <REDACTED FOR STACK OVERFLOW>", -1, -1 
FINEST oracle.jdbc: 212E0078 Enter: "update <REDACTED FOR STACK OVERFLOW>", "[ {result_set_type=-1}  {use_long_fetch=false}  {result_set_concurrency=-1} ]" 
FINEST oracle.jdbc: 212E0078 Enter: "update <REDACTED FOR STACK OVERFLOW>", "[ {result_set_type=-1}  {use_long_fetch=false}  {result_set_concurrency=-1} ]" 
FINEST oracle.jdbc: 212E0078 Enter: void 
FINEST oracle.jdbc: 212E0078 Return: void 
FINEST oracle.jdbc: 212E0078 Exit: [0.012976 ms] 
FINEST oracle.jdbc: 2C5FB90F Enter: oracle.jdbc.driver.T4CConnection@212e0078, "update <REDACTED FOR STACK OVERFLOW>", "[ {result_set_type=-1}  {use_long_fetch=false}  {result_set_concurrency=-1} ]" 
FINEST oracle.jdbc:          Enter: oracle.jdbc.driver.T4CConnection@212e0078 
FINEST oracle.jdbc:          Return: void 
FINEST oracle.jdbc:          Exit: [0.012198 ms] 
FINEST oracle.jdbc:          Enter: oracle.jdbc.driver.T4CConnection@212e0078, "[ {result_set_type=-1}  {use_long_fetch=false}  {result_set_concurrency=-1} ]" 
FINEST oracle.jdbc:          Enter: oracle.jdbc.driver.T4CPreparedStatement@9887002 
FINEST oracle.jdbc:          Return: void 
FINEST oracle.jdbc:          Exit: [0.016657 ms]

I would have assumed that the oracle.jdbc.level=FINE setting should catch update statements, because the documentation says it logs "Logs the entry and exit to every public method providing a detailed trace of JDBC operations", but it's logging almost exactly the same stuff as oracle.jdbc.level=CONFIG.

I've tried with the following Oracle drivers (in Gradle dependency format):

compile group: 'com.oracle.database.jdbc.debug', name: 'ojdbc8_g', version: '12.2.0.1'

compile group: 'com.oracle.database.jdbc.debug', name: 'ojdbc8_g', version: '21.3.0.0'

Is there any way to configure the Oracle driver to log ALL select, update, insert, and delete statements, and not much else? I've also tried setting oracle.sql.level=FINE and oracle.level=FINER, but nothing useful about updates appears to be getting logged at those levels or higher.

Update

I setup p6spy and it also failed to log insert and update statements. However this answer solved that problem by enabling logging of batches. Could the Oracle driver logging here be doing the same thing, and not logging batch updates at the CONFIG level?

Kaypro II
  • 3,210
  • 8
  • 30
  • 41
  • So are you saying that you tried all the [log levels](https://docs.oracle.com/javase/9/docs/api/java/util/logging/Level.html)? – Abra Oct 12 '21 at 01:32
  • Yes, I've tried FINEST, FINER, FINE, and CONFIG. I'm surprised that CONFIG is missing inserts and updates, and that so little is being logged at the FINER and FINE levels. I'm assuming I'm missing something, because if this is it then this logging has surprising gaps that seriously compromise its usefulness. – Kaypro II Oct 12 '21 at 04:55

1 Answers1

0

In java.util.logging you have two ways to select the messages shown in the log:

  • category, which was set by the developers to oracle.jdbc
  • log level, which you already examined needs to be at least finest There is nothing else you can do at configuring the logging.

But there are alternatives: If the application is under your control, add logging to all calls for update.

If the application is not under your control, or you want a generic solution go for a wrapper. You define a JDBC driver that can log your requests (maybe finely configurable as to your needs), but then in turn it forwards all calls to the real JDBC driver.

Queeg
  • 7,748
  • 1
  • 16
  • 42