title: Oracle defaultNChar=true SLOW on NCHAR/NVARCHAR2
body: <p>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".</p>
  
  <p>My problem: My parametrized query is <strong>extremely slow with "defaultNChar=true"</strong>. But as soon as I set <strong>"defaultNChar=false" the query is ultra fast</strong> (3 seconds).</p>
  
  <p>Query usage looks like that:</p>
  
  <pre><code>String sql = "INSERT INTO MYTABLE_ERROR(MY_NAME,MY_FLAG,MY_VALUE) "
                          + "SELECT ? AS MY_NAME,"
                          + "? AS MY_FLAG,v.MY_VALUE"
                          + " FROM OTHER_TABLE v"
                          + " JOIN ( SELECT * FROM ... iv ... WHERE iv.MY_NAME = ? ) rule1 "
                          + " ON v.\"MY_NAME\"=rule1.\"MY_NAME\" AND v.\"MY_VALUE\"=rule1.\"MY_VALUE\""
                          + " WHERE rule1.\"MY_NAME\" = ? AND v.\"MY_VALUE\" = ?";
  
  preStatement = conn.prepareStatement (sql);
  int count = 1;
  for (String p : params)
  {
          // SLOW
          //preStatement.setNString (count++, p);
          // SLOW
          //preStatement.setObject (count++, p, Types.NVARCHAR);
          // SLOW
          preStatement.setString (count++, p);
  }
  </code></pre>
  
  <p>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!</p>
  
  <p>I even got the DB NLS config hoping to find anything, but I am not sure here either:</p>
  
  <pre><code>NLS_LANGUAGE    AMERICAN
  NLS_TERRITORY   AMERICA
  NLS_CURRENCY    $
  NLS_ISO_CURRENCY    AMERICA
  NLS_NUMERIC_CHARACTERS  .,
  NLS_CALENDAR    GREGORIAN
  NLS_DATE_FORMAT DD-MON-RR
  NLS_DATE_LANGUAGE   AMERICAN
  NLS_CHARACTERSET    AL32UTF8
  NLS_SORT    BINARY
  NLS_TIME_FORMAT HH.MI.SSXFF AM
  NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
  NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
  NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
  NLS_DUAL_CURRENCY   $
  NLS_NCHAR_CHARACTERSET  AL16UTF16
  NLS_COMP    BINARY
  </code></pre>
  
  <p>Please help!</p>
  
  <p>Thanks,
  G.</p>
  
  <p>UPDATE: It looks like the query is stuck when using "defaultNChar=true" somehow. I am seeing this when using JConsole:</p>
  
  <pre><code>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.mycompany.test.DriverTest.fireStatement(DriverTest.java:253)
  </code></pre>