0

We are trying to capture queries with sub-optimal unexpected column length from code hitting the database where columns defined are of limited length. How to capture such full queries with column length in Oracle. example: Java application with hibernate had a string column defined in hbm without length, this is hitting DB table where that column is defined varchar2(50). What we were complained is this application is throwing query with varchar2(2000) against the DB, how to capture this full query with column length coming from application ?

Env: Oracle 11g, 12c Java 1.7 with Hibernate

Mahi Dbdwh
  • 1
  • Are you asking how to make the Java application log Hibernate generated SQL statements to its' log files, or how to make OracleDB log statements to Oracles' log files? – Selaron Jan 17 '19 at 08:49
  • I am fine with both ways, we tried hibernate option of sql_mode to capture SQL, but it doesn't show column length thrown to DB. Same way tried to identify the query from v$sql, trace file and awr reports to check the same ie column length in the fired query. Either way if I can find that column length is throwing as varchar2(2000) because of missing length property in HBM file, some way to capture actual SQL with column length. – Mahi Dbdwh Jan 17 '19 at 08:53
  • Isn't there an Exception witha stack trace in some Java application log file which tells some more details? – Selaron Jan 17 '19 at 08:56
  • There was no exception and there is no reason to have exception, because this is observed during performance issue that particular query is causing huge IO even though it has index. And index is PK, explain plan shows index being used. – Mahi Dbdwh Jan 17 '19 at 09:01
  • You can have Hibernate log all queries with parameters to the application log: https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate#1713464 Would this help? – Selaron Jan 17 '19 at 09:35
  • Seems it would be useful and I will try this too. But not sure if it captures the column length in select statement formed by application, that's what my main intention to find. – Mahi Dbdwh Jan 17 '19 at 10:05
  • I'm not sure what 'column length' is in context of a perpared JDBC query like 'select * from user where name = :n' and then set the named parameter 'n' to an arbitrary String (e.g. "Bruce Lee"). There is no column length specified on java application level, but I don't know how Oracle JDBC driver handles this internally - maybe it internally assigns data type varchar2(2000) if it gets a long value for parameter 'n' in this query? Hibernet would Just add the SQL query and "Bruce Lee" to the log in this example. – Selaron Jan 17 '19 at 10:16
  • Yes, that's what I too assume and felt. But in case if in hbm file if we have 2 columns, 1st one with column name, type as string and length and 2nd column with just column name and type String (ignoring length). In this case how query gets for this. select col1, col2 from table; col1: with defined length in hbm col2: default length decided by hibernate for that type: string. If we can capture this, we'll save unwanted performance leakage from application layer. I am really thankful to you for engaging in this help :) – Mahi Dbdwh Jan 17 '19 at 10:56
  • I think the length defined in hbm mapping file ist ignored for queries. Hibernet just invokes the `PreparedStatement.setString(int, String)` method on the query where hbm mapping column length is not relevant. The length is relevant when Hibernate is asced to create the Table Schema from mapping files or when Hibernate validation framework is used to validate Entity instances befor persisting/updating them. This is just general speaking, it's still opossible that CASTs or function calls are added to SQL queries which return `varchar2(2000)` typed values. – Selaron Jan 17 '19 at 11:17
  • API doc for `PreparedStatement.setString(int, String)`: https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String) – Selaron Jan 17 '19 at 11:17

0 Answers0