Re: Bind Variables ignoring Index on 10.2.0.4

On Apr 7, 7:00 am, pat.ca..._at_service-now.com wrote:
> 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?

What is the status of the index? Received on Mon Apr 07 2008 - 15:02:01 CDT