Re: Bind Variables ignoring Index on 10.2.0.4

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Mon, 7 Apr 2008 00:19:53 -0700 (PDT)
Message-ID: <43371d4b-8029-40fb-98ef-1f022b6f207f@r9g2000prd.googlegroups.com>


On 4월7일, 오후3시00분, 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?

I suspect if you have histogram on sys_id column. Can you check it?
If histgoram is existent, can you test and compare the result of "with" histogram and "without" histogram? Received on Mon Apr 07 2008 - 02:19:53 CDT

Original text of this message