Re: Bind Variables ignoring Index on 10.2.0.4

On Apr 7, 10: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?

I tried this on my test 10.2.0.4/Win32 instance:

CREATE TABLE CI_TEST(X CHAR(32)); CREATE UNIQUE INDEX UQ$CI_TEST ON
CI_TEST(NLSSORT(X,'NLS_SORT=''BINARY_CI'''); Inserted 832 rows into this table with values: 'A' to 32x'A', 'B' to 32x'B', ..., 'Z' to 32x'Z'.

SELECT X FROM CI_TEST WHERE X=:B1; :B1 was initialized to 'x';

SQL_ID a779vscy3axhc, child number 0

| Id  | Operation                   | Name       | Rows  | Bytes |
Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |
2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CI_TEST    |     1 |    34 |
2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | UQ$CI_TEST |     1 |       |

Predicate Information (identified by operation id):

   2 -
access("CI_TEST"."SYS_NC00002$"=NLSSORT(:B1,'nls_sort=''BINARY_CI'''))

Note

• dynamic sampling used for this statement

Note the SYS_NC00002$ in predicate information - this is our caseinsensitive  virtual column. Also note that I did not create a PRIMARY KEY constraint here. This prevents us from creating foreign keys on this table, so let's create the primary key:

ALTER TABLE CI_TEST ADD CONSTRAINT PK$CI_TEST#X PRIMARY KEY (X); Table altered.

CREATE TABLE CI_REF (X CHAR(32), Y NUMBER,  CONSTRAINT FK$CI_REF#X$CI_TEST#X FOREIGN KEY (X) REFERENCES CI_TEST(X)); Table created.

ALTER SYSTEM FLUSH SHARED_POOL; <re-executed the select statement here>

The SQL_ID for the statement didn't change, so did the plan. It still uses the virtual column and the case-insensitive index. However, my COMPATIBLE is set to '10.2.0.3' and my OPTIMIZER_FEATURES_ENABLE is '10.1.0'. Let's see what happens if I set them both to '10.2.0.4' and bounce the instance... Nothing. Same picture. The only thing that's left is gathering stats on the table with histograms and see if that changes anything... Nope. The unique index is properly used again, the only difference is that this time the plan doesn't say anything about dynamic sampling.

So it doesn't seem to be some specific new CBO behavior or bug introduced in 10.2.0.4. However, my experiment may not reproduce your exact setup. A 10053 trace taken on your system might shed some light on why the CBO refuses to use your function-based index when it seems natural choice. You might want to drop and recreate the SYS_TRIGGER_SYSID index (make sure it's UNIQUE,) and see if things return back to normal. If the issue persists, I'd suggest that you open a SR with Oracle Support, they might suggest how to diagnose it properly. This can well be a regression after all...

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Apr 08 2008 - 08:23:15 CDT