Re: Bind Variables ignoring Index on 10.2.0.4
Date: Mon, 7 Apr 2008 10:34:44 -0700 (PDT)
Message-ID: <74be35b4-cf49-4ac5-92d1-6b7333c07add@b1g2000hsg.googlegroups.com>
On Apr 7, 10:18 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
Optimizer level is definately on on 2 at the moment:
SQL> set linesize 132;
SQL> select name, value from gv$parameter where name =
'plsql_optimize_level';
plsql_optimize_level
2
The index full scan might be an artifact of these optimizer settings
though:
optimizer_index_caching = 90
optimizer_index_cost_adjust = 10
Both of those were set last night in an effort to mitigate the table scans. So they likely explain why we were seeing TABLE ACCESS FULL last night and INDEX FULL SCAN this morning.
Seems like a query hint isn't helping either. Sure looks like for some reason in the bind variable case he thinks he cannot use the NLS index. Just don't know why :(.
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI; Session altered.
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> set linesize 132;
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.
SQL> explain plan for delete /*+ INDEX(SYS_TRIGGER_SYSID) */ 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. Received on Mon Apr 07 2008 - 12:34:44 CDT