Re: Bind Variables ignoring Index on 10.2.0.4

On Apr 7, 10:08 am, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
> On Apr 7, 3:42 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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-Hidequoted 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 --
>
> it seems that now bind variable peeking is a problem for a lot of
> people. I do not agree.
> Bind variable peeking is a means of the optimizer to get more
> information about statement.
> In some situations it can result in a not-optimal execution plan, but
> without bind peeking
> there are less chances that optimizer could elaborate an optimal
> execution plan because it
> has less informations about the statement.
>
> The real problem is CBO with bind variables and bind peeking could
> help and be good for a lot
> of situations.
>
> Regards,
> Cristian Cudiziohttp://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com- Hide quoted text -
>
> - Show quoted text -

When were the statistics last updated? You said there are about 40K rows in the table so how many does dba_tables.num_rows show?

• Mark D Powell --