Re: Bind Variables ignoring Index on 10.2.0.4

pat.casey_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?

I've read the other responses and feel like we are shooting in the dark without having enough information to identify the target. Can you run the following and report back the results?

1. Run an Explain Plan (DBMS_XPLAN output) as is.
2. Run again with dynamic sampling disabled.
3. Run again using hints to force the 10.2.0.3 plan.
4. Verify that the explain plans matche what is in gv$sqlplan. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org