All entries for Monday 16 March 2009

March 16, 2009

National characters and indexes

Follow-up to JDBC driver and poor SQL execution plan from Oracle/Java/Others

We need to upgrade to the Oracle driver version 10.2.0.4 to fix a multi-bytes character display problem in out web app.

But with this driver, some SQLs run very slow.

With a lower version driver, the application works fine.

After investigation, I found out the root cause of the problem:

Jboss data source file has a property

<connection-property name="defaultNChar">true</connection-property>

This tell Jboss to send java.lang.String as a nvarchar2 type instead of a normal varchar2 type to database.

Since the column datatype in database are defined as varchar2, Oracle optimizer will have to do an internal data conversion, converting the data in the varchar2 column to nvarchar2 data. This will invalidate the index we created on the table.

The oracle trace file show the following:

1 - filter(SYS_OP_C2C("THIS_"."OWNERID")=:1)

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXII