Re: Bind Variables ignoring Index on 10.2.0.4

From: Pat <pat.casey_at_service-now.com>
Date: Mon, 7 Apr 2008 08:45:35 -0700 (PDT)
Message-ID: <40e0b744-69c0-4e21-b35c-8dc465d29cf1@k37g2000hsf.googlegroups.com>

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? Received on Mon Apr 07 2008 - 10:45:35 CDT