| 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 18, 7:21 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Sep 17, 4: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?
>
> Let's take a look at what you posted (slightly reformatted):
> The fast 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' )
> )
>
> The explain plan, the best that I could make out from what you posted:
> HASH [UNIQUE]
> NESTED LOOPS [OUTER] Est. Cost
> Rows Est. KBytes
> INLIST ITERATOR
> ODB_PB.ORDER_HEADER TABLE ACCESS [BY INDEX ROWID] 5
> 1 0.048
> ODB_PB.I_ORDER_HEADER_1 INDEX [RANGE SCAN] 4
> 1 --
> ODB_PB.RELATION_MASTER TABLE ACCESS [BY INDEX ROWID] 2
> 1 0.026
> ODB_PB.I_RELATION_MASTER_CODE INDEX [RANGE SCAN] 1
> 1 --
>
> Note the INLIST ITERATOR, which is likely the result of Oracle
> transforming (ORDER_HEADER"."ACCESS_CONTROL" = 'ADM' or
> "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') into
> ORDER_HEADER.ACCESS_CONTROL IN ('ADM','ALL'). It is hard to tell
> without a DBMS_XPLAN output, but this step is keeping those rows that
> do no have ACCESS_CONTROL set to 'ADM' or 'ALL' from hitting the
> NESTED LOOPS operation, while in the SQL statement with the bind
> variables, the INLIST ITERATOR is not present, which may mean that
> those rows are filtered after the NESTED LOOPS operation. From a
> performance standpoint, it is best to eliminate as many rows as
> possible as early as possible.
>
> The slow 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 )
> )
>
> HASH [UNIQUE]
> NESTED LOOPS [OUTER] Est. Cost Rows
> Est. KBytes
> ODB_PB.ORDER_HEADER TABLE ACCESS [BY INDEX ROWID] 4
> 1 0.048
> ODB_PB.I_ORDER_HEADER_CUSTOMER_PO INDEX [RANGE SCAN] 3
> 1 --
> ODB_PB.RELATION_MASTER TABLE ACCESS [BY INDEX ROWID] 2
> 1 0.026
> ODB_PB.I_RELATION_MASTER_CODE INDEX [RANGE SCAN] 1
> 1 --
>
> Note that the INLIST ITERATOR is not present in the above - Oracle did
> not transform the query. Try changing the WHERE clause as follows:
> WHERE
> "ORDER_HEADER"."RELATION_CODE" =
> "RELATION_MASTER"."RELATION_CODE" (+)
> AND "ORDER_HEADER"."STATUS" = 'OPEN'
> AND "ORDER_HEADER"."ACCESS_CONTROL" IN ( :S_USER , 'ALL')
> AND "ORDER_HEADER"."ORDER_TYPE" = :S_ORDER_TYPE
>
> Also, verify that table and index statistics are up to date and try to
> obtain a DBMS_XPLAN that shows the actual values and predicates,
> rather than just the estimates (note that the estimates are nearly
> identical in the above). See:
> http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Hi Charles,
I changed the queries exactly as you suggested. However, there is no change to each of their explain plans. The "bind variables" query still does not use the INLIST iterator, nor does it use what I think is the better index (the one used by the string query).
Would you possibly be able to assist me more if I post the results of the DBMS_XPLAN that you suggested?
Another tidbit... I'm running 10.2.0.1.0 on Windows... The Sybase support staff member runs 10.2.0.1.0 on Solaris... He runs the same queries as me, that I gave him... he reports that the bind variablesquery is fast, and that it does use the same index as the string-query version.
 |
 |