Re: Bind Variables ignoring Index on 10.2.0.4

Pat wrote:
> So far as I can tell, it's not an issue with bind variable peeking
> (although thanks for the suggestion; I hadn't thought to look at that
> yet). I'm getting the same results with and w/o peeking enabled.
>
> Overnight, one of the DBA's tweaked a couple of optimizer settings to
> try to reduce the table scans. Seems to have pushed our test query
> from TABLE ACCESS FULL to INDEX FULL SCAN. In this particular case,
> that's actually a bit faster, but it's still a sub-optimal path since
> the query in question should be an INDEX UNIQUE SCAN (it's an equality
> get by primary key).
>
> At this point, I'm focussing on a typing mismatch or something
> similar. I'm seeing some stuff in the plan_table_output that I'm not
> understanding, but it's definately different between my bind variable
> and literal case.
>
> LITERALS (uses an INDEX_UNIQUE_SCAN on SYS_TRIGGER_SYS_ID).
>
> 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 | 87 | 1 (0)|
> 00:00:01 |
> | 1 | DELETE | SYS_TRIGGER | | | | |
> | 2 | TABLE ACCESS BY INDEX ROWID| SYS_TRIGGER | 1 | 87
> | 1 (0)| 00:00:01 |
> |* 3 | INDEX UNIQUE SCAN | SYS_TRIGGER_SYSID | 1 | |
> 1 (0)| 00:00:01 |
> --------------------------------------------------------------------------------------------------
>
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 -
> access(NLSSORT(INTERNAL_FUNCTION("sys_id"),'nls_sort=''BINARY_CI''')=HEXTORAW('6100
> ') )
>
>
> BIND VARIABLES (Uses an INDEX FULL SCAN on SYS_C008583)
>
> SQL> variable id char
> SQL> exec :id := 'a';
>
> PL/SQL procedure successfully completed.
>
> SQL> explain plan for delete from sys_trigger where "sys_id" = :id;
>
> Explained.
>
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 2281741627
>
> --------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> --------------------------------------------------------------------------------
> | 0 | DELETE STATEMENT | | 1 | 87 | 355 (1)|
> 00:00:05 |
> | 1 | DELETE | SYS_TRIGGER | | | | |
> |* 2 | INDEX FULL SCAN| SYS_C008583 | 1 | 87 | 355 (1)|
> 00:00:05 |
> --------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------
> ---------------------------------------------------
>
> 2 -
> filter(NLSSORT("sys_id",'nls_sort=''BINARY_CI''')=NLSSORT(:ID,'nl
> s_sort=''BINARY_CI'''))
>
> 15 rows selected.
>
>
> Note the different filter conditions in each case.
> Note also that in the case of the bind variable, he's using a binary
> index on sys_id (that's what SYS_C008583 is), instead of the
> functional index on sys_id.
>
> What's the filter condition telling me? Are we doing something wrong
> with our NLS queries? Are bind variables defeating the NLS indexes
> somehow?

What is jumping out at me is the following:

                                  rows   bytes    cost    CPU
  INDEX FULL SCAN| SYS_C008583 |     1 |    87 |   355	 (1)|

Good grief.

I have yet to work with 10.2.0.4 so I am wondering what the optimizer level is set to?

select name, value
from gv$parameter
where name = 'plsql_optimize_level';

If not 2 then try setting it to 2.

What happens if you use a hint to force an index range scan?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org