| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Doing a query with string vs bind variables.
On Sep 17, 5:18 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Sep 17, 3:42 pm, Luch <DarthL..._at_gmail.com> wrote:
>
>
>
> > We have a PowerBuilder application where we started noticing the
> > performance of a query is worse if you do it with bind variables then
> > if you do the same query as a string. We made a small example and sent
> > to Sybase (makers of PB). We noticed that the bind variables query
> > will not use the same index as the string query, even though the two
> > queries are exactly the same.
>
> > Example
>
> > String query:
> > SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
> > "ORDER_HEADER"."ORDER_NUMBER" ,
> > "RELATION_MASTER"."NAME" ,
> > "ORDER_HEADER"."RELATION_CODE" ,
> > "ORDER_HEADER"."WO" ,
> > "ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
> > "ORDER_HEADER"."AUTHORIZATION" ,
> > "ORDER_HEADER"."ACCESS_CONTROL" ,
> > "ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
> > "ORDER_HEADER"."TOTAL_COST" ,
> > "ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
> > FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE
> > ( "ORDER_HEADER"."RELATION_CODE" =
> > "RELATION_MASTER"."RELATION_CODE" (+)) and
> > ( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
> > ("ORDER_HEADER"."ACCESS_CONTROL" = 'ADM' or
> > ( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
> > ( "ORDER_HEADER"."ORDER_TYPE" = 'RO' ) )
>
> > Bind variable query:
> > SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
> > "ORDER_HEADER"."ORDER_NUMBER" ,
> > "RELATION_MASTER"."NAME" ,
> > "ORDER_HEADER"."RELATION_CODE" ,
> > "ORDER_HEADER"."WO" ,
> > "ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
> > "ORDER_HEADER"."AUTHORIZATION" ,
> > "ORDER_HEADER"."ACCESS_CONTROL" ,
> > "ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
> > "ORDER_HEADER"."TOTAL_COST" ,
> > "ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
> > FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE
> > ( "ORDER_HEADER"."RELATION_CODE" =
> > "RELATION_MASTER"."RELATION_CODE" (+)) and
> > ( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
> > ("ORDER_HEADER"."ACCESS_CONTROL" = :s_user or
> > ( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
> > ( "ORDER_HEADER"."ORDER_TYPE" = :s_order_type ) )
>
> > In the Bind-variable query, we pass the same parameters as we are hard-
> > coding in the equivalent string version.
>
> > Why would the bind variable query use a different index, and why it
> > would it be a "worse" index then the one the string-query uses?
>
> > The puzzling part is that if I take the Bind-variable query and run it
> > in Oracle SQL Developer (it will prompt me for the values when run) it
> > will also perform well... It is only in running through Sybase
> > PowerBuilder that the bind-variable query runs slow. Sybase stated
> > they have no control over which index the database will use.
>
> > Another note... the above example is only one case... this same
> > situation is happening with many queries throughout our application.
>
> > any thoughts?
>
> Which version of Oracle are you using? It's possible (if I remember
> correctly from an explanation of a similar situation by Jonathan
> Lewis) that the string is evaluated as a constant and can correctly
> use the 'proper' index, where the bind variables are unknowns and
> introduce a 5% selectivity into the CBO calculations. I'm certain
> Jonathan can explain it better.
>
> Post the version of Oracle (all four numbers); it's difficult to
> assist you without this information. And post the explain plans if
> you have them available.
>
> David Fitzjarrell
The version of Oracle is: 10.2.0.1.0
The explain plan of each is posted below. The string (the good/fast one) uses an index called I_ORDER_HEADER_1, which makes perfect sense, it is perfect for the where condition. The bind variable (bad/slow one) query uses I_ORDER_HEADER_CUSTOMER_PO, which is not as good.
String/Good one:
SELECT STATEMENT
7 HASH [UNIQUE] 6 NESTED LOOPS [OUTER] 3 INLIST ITERATOR 2 ODB_PB.ORDER_HEADER TABLE ACCESS [BY INDEX ROWID] 1 ODB_PB.I_ORDER_HEADER_1 INDEX [RANGE SCAN] 5 ODB_PB.RELATION_MASTER TABLE ACCESS [BY INDEX ROWID] 4 ODB_PB.I_RELATION_MASTER_CODE INDEX [RANGE SCAN] Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned 1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index I_ORDER_HEADER_1. 4 1 -- 2 This plan step retrieves rows from table ORDER_HEADER through ROWID(s) returned by an index. 5 1 0.048 3 This plan step loops through the query's IN list predicate, executing its child step for each value found. 4 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index I_RELATION_MASTER_CODE. 1 1 -- 5 This plan step retrieves rows from table RELATION_MASTER through ROWID(s) returned by an index. 2 1 0.026 6 This plan step joins two sets of rows by iterating over theouter, or driving, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause; in addition to the rows satisfying that condition, Oracle also returns all rows from the row set not containing the outer join operator (+) which failed to satisfy
that condition. 7 1 0.074 7 This plan step has no supplementary description information. 8 This plan step designates this statement as a SELECT statement.
Bind-Variable (Bad one):
SELECT STATEMENT
6 HASH [UNIQUE] 5 NESTED LOOPS [OUTER] 2 ODB_PB.ORDER_HEADER TABLE ACCESS [BY INDEX ROWID] 1 ODB_PB.I_ORDER_HEADER_CUSTOMER_PO INDEX [RANGE SCAN] 4 ODB_PB.RELATION_MASTER TABLE ACCESS [BY INDEX ROWID] 3 ODB_PB.I_RELATION_MASTER_CODE INDEX [RANGE SCAN] Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned 1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index I_ORDER_HEADER_CUSTOMER_PO. 3 1 -- 2 This plan step retrieves rows from table ORDER_HEADER through ROWID(s) returned by an index. 4 1 0.048 3 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index I_RELATION_MASTER_CODE. 1 1 -- 4 This plan step retrieves rows from table RELATION_MASTER through ROWID(s) returned by an index. 2 1 0.026 5 This plan step joins two sets of rows by iterating over theouter, or driving, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause; in addition to the rows satisfying that condition, Oracle also returns all rows from the row set not containing the outer join operator (+) which failed to satisfy
that condition. 6 1 0.074 6 This plan step has no supplementary description information. 7 This plan step designates this statement as a SELECT statement.Received on Mon Sep 17 2007 - 16:39:53 CDT
 |
 |