Bind Variables ignoring Index on 10.2.0.4
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