Bind Variables ignoring Index on 10.2.0.4

From: <pat.casey_at_service-now.com>
Date: Sun, 6 Apr 2008 23:00:26 -0700 (PDT)
Message-ID: <bead96b8-845f-47cc-845b-f30fbf51371b@c19g2000prf.googlegroups.com>


My company sells and app that's been running atop Oracle for about 18 months now. We've run on 10.1.*, 10.2.0.1, 10.2.0.2, 10.2.0.3, all without any unexpected behavior. Recently, we had a customer install us atop 10.2.0.4 and we've seen some very unexpected behavior with regard to our queries. To whit, anything with bind variables seems to have stopped using what seem like perfectly reasonable indexes, indexes that got used quite naturally on 10.2.0.3.

We're a java app connecting to Oracle via Oracle's THIN JDBC driver (v 10.2.0.3) over TCP.
All session run in case insensative mode and all connections are initiated with:

ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI I have a table sys_trigger with about 40k rows in it. Primary key is sys_id, a char(32) field with unique values.

We have this index atop the table:

create index sys_trigger_sys_id on sys_trigger (NLSSORT("sys_id",'nls_sort=''BINARY_CI'''));

I have current stats on the table.

I'm issuing this query:

DELETE from SYS_TRIGGER where "sys_id" = :1

 SQL> explain plan for delete from sys_trigger where "sys_id" = :1;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT



Plan hash value: 2365230323

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|   0 | DELETE STATEMENT   |		 |     1 |    86 |  1109   (1)| 00:00:14
|
|   1 |  DELETE 	   | SYS_TRIGGER |	 |	 |	      | 	 |
|*  2 |   TABLE ACCESS FULL| SYS_TRIGGER |     1 |    86 |  1109
(1)| 00:00:14 |

SQL> explain plan for delete from sys_trigger where "sys_id" = 'a';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT



Plan hash value: 1594121083

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | DELETE STATEMENT | | 1 | 86 | 3 (0)| 00:00:01 |
|   1 |  DELETE 		     | SYS_TRIGGER	 |	 |	 |	      | 	 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER	 |     1 |    86
|     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN	     | SYS_TRIGGER_SYSID |     1 |	 |
2 (0)| 00:00:01 |

Can anyone offer me any insight into what's going on here? These queries ran fine under 10.2.0.3; they used the index quite happily.

If it's any help, we did run into one other peculiarity with 10.2.0.4 which might point to a lack of understanding of something fundamental on our part.

The PK of sys_trigger is a char(32).
A very small number of our records have PK values with < 32 characters in them e.g. "a" or "b" instead of 32 characters of hex.

Historically, we'd bind them into the query with a piece of code like this:

String key = "a";
String sql = "DELETE from SYS_TRIGGER where sys_id = ?"; PreparedStatement ps = getConnection().prepareStatement(sql);

ps.setObject(1, a);

With 10.2.0.4 we found this didn't work and instead we had to pad the key with 31 spaces:

String key = "a                                ";
String sql = "DELETE from SYS_TRIGGER where sys_id = ?"; PreparedStatement ps = getConnection().prepareStatement(sql);

ps.setObject(1, a);

I have this nagging suspicion that I'm fundamentally missing something basic, important, and obvious here.

Can anyone help? Received on Mon Apr 07 2008 - 01:00:26 CDT

Original text of this message