Re: Bind Variables ignoring Index on 10.2.0.4

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 7 Apr 2008 20:13:53 +0200
Message-ID: <47fa6440$0$14345$e4fe514c@news.xs4all.nl>

<pat.casey_at_service-now.com> schreef in bericht news:bead96b8-845f-47cc-845b-f30fbf51371b_at_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?

With the risk of making a stupid remark (I don't know anything about NLS_SORT in create_index):
Is there any reason why you do (NLSSORT("sys_id",'nls_sort=''BINARY_CI''')) (double quotes, case DOES matter here)
and not
 (NLSSORT(SYS_ID,'nls_sort=''BINARY_CI'''));?? (no double quotes, case doesn't matter now)
To my knowledge (but again...) a column name in lower char and double quotes does not resolve to the actual column.

I may be wrong, but your remark about a typo made me wonder....

BTW Same goes for "BINARY_CI": why those double quotes?

Shakespeare Received on Mon Apr 07 2008 - 13:13:53 CDT

Original text of this message