Re: Doing a query with string vs bind variables.

On Sep 18, 12:20 pm, Luch <DarthL..._at_gmail.com> wrote:
> 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 variables
> query is fast, and that it does use the same index as the string-query
> version.
> Unfortunately I don't have a non-Windows environment to try it myself.

I am sure that there are several people in the group that would be happy to help. One thing that you need to be careful with - make certain that the bind variable declarations match the data types OF the columns in the database tables. Such a problem should be clear in a DBMS_XPLAN. Also, post the output of the following: SELECT
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_NAME
FROM
  DBA_IND_COLUMNS
WHERE
  INDEX_NAME IN ('I_ORDER_HEADER_1','I_RELATION_MASTER_CODE ','I_ORDER_HEADER_CUSTOMER_PO')
ORDER BY
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_POSITION; SELECT
  TABLE_NAME,
  COLUMN_NAME,
  ENDPOINT_NUMBER,
  ENDPOINT_VALUE
FROM
  DBA_HISTOGRAMS
WHERE
  TABLE_NAME IN ('ORDER_HEADER','RELATION_MASTER') ORDER BY
  TABLE_NAME,
  COLUMN_NAME; SELECT
  TABLE_NAME,
  LAST_ANALYZED
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('ORDER_HEADER','RELATION_MASTER'); SELECT
  TABLE_NAME,
  INDEX_NAME,
  LAST_ANALYZED
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME IN ('ORDER_HEADER','RELATION_MASTER') ORDER BY
  TABLE_NAME; The DBMS_XPLAN that compares the estimated statistics with the actual statistics should prove to be very helpful.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Sep 18 2007 - 11:40:59 CDT