DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query Plan changes with bind variables?

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Oracle 8.1.7.2 on Sun Solaris 2.8

    I have a partitioned table where each partition contains a month worth of data. Each partition contains about 4 million rows and I have 24 months online at any one time. I also have a local partitioned index on date.

    My basic query is:
    SELECT * FROM myTable
    WHERE myDate = some_date
    OR myDate = some_other_date;

    If I use explicit values for some_date and some_other_date, then the query returns in 4 seconds. For example:
    Code:
    SELECT * FROM myTable
    WHERE myDate = to_date('2002/09/01','YYYY/MM/DD')
    OR myDate = to_date('2002/09/15','YYYY/MM/DD');
    The Query plan correctly eliminates partitions in the index.

    If I use bind variables in SQL*Plus for some_date and some_other_date, then the query also returns in 4 seconds. For example:
    Code:
    DECLARE
       sd DATE;
       ed DATE;
    BEGIN
       sd := to_date('2002/09/01','YYYY/MM/DD');
       ed := to_date('2002/09/15','YYYY/MM/DD');
       FOR x IN (
          SELECT * FROM myTable
          WHERE myDate = to_date('2002/09/01','YYYY/MM/DD')
          OR myDate = to_date('2002/09/15','YYYY/MM/DD')) LOOP
          ctr := ctr+1;
       END LOOP;
    END;
    The query also comes back in about 4 seconds and choses the correct plan.

    However, when I use bind variables through an OCI host program (either Perl DBI or Pro*C/C++) the optimizer chooses a full table scan instead of partition elimination.

    We have tried the query with both a hint for the index and without. When we use a hint for the index it does a full scan on the index but still takes a long time.

    When we try the query in an OCI host program with explicit values (no bind variables) the correct plan is chosen.

    This problem occurs when we use either the 9.2.0.1 client or the 8.1.7.0 OCI libraries.

    This table has no statistics (because of a bug we hit with partitioned tables and the CBO) so I know (I think I know, anyway) that the RBO is being used.

    I'm kind of thinking it has something to do with when the statement is parsed oracle makes assumptions about the data that may not be true to figure out the query plan. When the values are presented the plan has already been figured out incorrectly.

    I know there are lots of ways around this. In fact we've already implemented "Plan B" with REF CURSORS and a package. I just want to know if anybody has seen behaviour like this before or if anybody has any ideas about why this might be happening?
    Jeff Hunter

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why don't you try PLAN STABILITY option?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Do the various permutations all result in the same basic statement in the SGA?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Do the various permutations all result in the same basic statement in the SGA?

    - Chris
    Hmmm...Good point. I assumed they were different, but I'll have to check.
    Jeff Hunter

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by tamilselvan
    Why don't you try PLAN STABILITY option?
    Yes, this is an option we have considered.
    Jeff Hunter

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try to discover this problem with ur optimazer:

    1) Event 10053 will put to trace file choices which will made CBO.

    alter sesssion set events '10053 trace name context forever level 1; -- for more detailed report
    or
    alter sesssion set events '10053 trace name context forever level 2; -- for less detailed report

    in trace file check how optimazer use bind variables in both
    (from sqlplus and DBI) cases.
    Oracle will output text sql statments from sql area,
    as i can undestand AS IS.
    Then in trace file will be information about all indexes, that can be use in query and about all tables/partitions,
    but oracle print table names and object #, not index name.
    U have to check index names from dictionary.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width