| 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, 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')
)
The explain plan, the best that I could make out from what you posted: HASH [UNIQUE]
NESTED LOOPS [OUTER] Est. CostRows Est. KBytes
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')
)
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.
Received on Tue Sep 18 2007 - 06:21:24 CDT
 |
 |