Re: Doing a query with string vs bind variables.

On Sep 18, 12:40 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

Once I ran the queries you gave, it made me realize I didn't have updated statistics after all. I must've been gathering them on the wrong schema. My mistake, I feel a little stupid about it.

So, I then gathered the statistics, and now the bind-variable query performs just as good now. And, I can see it using the 'correct" same index that the string-query uses. So, I suppose everything is good.

The only thing that puzzles is that why, when using the bind-variable query from both PowerBuilder and Oracle SQL Developer... It only performed poorly from PowerBuilder. Using the bind-variable query from Oracle SQL Developer (or SQL Plus) performed the same (and used same index) as the string version, even with the outdated statistics. Received on Wed Sep 19 2007 - 15:31:18 CDT