• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle defaultNChar=true SLOW on NCHAR/NVARCHAR2

 
Heinrich Jahre
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I am using a JDBC Prepared Statement with a bunch of parameters using setString(pos, value). The underlying columns on the tables are all NCHAR and NVARCHAR2. I have set the Oracle JDBC driver's "defaultNChar=true" so that Oracle DB would always treat my parameters as national language characters. The driver file is "ojdbc6.jar".

My problem: My parametrized query is extremely slow with "defaultNChar=true". But as soon as I set "defaultNChar=false" the query is ultra fast (3 seconds).

Query usage looks like that:





I have been trying to find the root cause of why my prepared statements executed against an "Oracle Database 11g Release 11.2.0.3.0 - 64bit Production" DB are slow with a JDBC driver "Oracle JDBC driver, 11.2.0.3.0". I could not find any clue!

I even got the DB NLS config hoping to find anything, but I am not sure here either:



Please help!

Thanks,
H.
 
Heinrich Jahre
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UPDATE: It looks like the query is stuck when using "defaultNChar=true" somehow. I am seeing this when using JConsole:



Total blocked: 1 Total waited: 1

Stack trace:
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(Unknown Source)
java.net.SocketInputStream.read(Unknown Source)
oracle.net.ns.Packet.receive(Packet.java:311)
oracle.net.ns.DataPacket.receive(DataPacket.java:103)
oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:312)
oracle.net.ns.NetInputStream.read(NetInputStream.java:257)
oracle.net.ns.NetInputStream.read(NetInputStream.java:182)
oracle.net.ns.NetInputStream.read(NetInputStream.java:99)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:121)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:77)
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1173)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:309)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:238)
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1446)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1757)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4372)
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4539)
- locked oracle.jdbc.driver.T4CConnection@7f2315e5
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:5577)
com.xyz.datahub.test.DriverTest.fireStatement(DriverTest.java:253)
com.xyz.datahub.test.DriverTest.run(DriverTest.java:184)
com.xyz.datahub.test.DriverTest.main(DriverTest.java:427)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic