Re: Bind Variables ignoring Index on 10.2.0.4

On Apr 7, 6:44 am, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
> On Apr 7, 9:19 am, Dion Cho <ukja.d..._at_gmail.com> wrote:
>
>
>
>
>
> > 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?
>
> It is very probable that he has histograms on that column, but because
> that column is primary key
> having an histogram on that column, unless this being a GREAT bug,
> coud only make things work well.
> I've made a little test on a test environment where 'ive recently
> installed 10.2.0.4 patchset and i do not
> see the same problem: on my db Oracle use range scan on the index.
> Remember that (unless changes on 10.2.0.4) explain plan does not make
> bind peeking so you
> have to execute really the query to test the execution plan.
> May be that in your program it is better you use setString and not
> setObject. To verify all you have
> to see on v$sql and V$sql_plan after executing your program.
> It seems a problem of implicit type conversion that prevents index
> usage.
>
> Regards,
> Cristian Cudiziohttp://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com- Hide quoted text -
>
> - Show quoted text -

If there are histograms on the table then bind variable peeking could be a problem.

Another issue: should the table be defined with a varchar2(32) column or a character column(32) column and how is defined at the problem site?
Your problem description of having to pad the bind variable would seem to indicate that the definition is character where it should be variable character.

HTH -- Mark D Powell -- Received on Mon Apr 07 2008 - 08:42:37 CDT