Forum Stats

  • 3,781,158 Users
  • 2,254,484 Discussions
  • 7,879,596 Comments

Discussions

Help CBO make the right choice

Dmitrii Dunaev
Dmitrii Dunaev Member Posts: 76
edited Apr 24, 2015 8:23AM in SQL & PL/SQL

Hello everyone. I have a performance related issue. I would like to hear your opinion on that. Here is a simple query against transaction table:

SELECT
     *
FROM
     transaction t
WHERE
         t.transaction_method_type_code = 'CREDIT'
     AND t.process_date BETWEEN TO_DATE ('11-MAR-2015') AND TO_DATE ('12-MAR-2015');

1.bmp

Oracle chooses an index based on process_date column. Which I think is right. When we increase the interval oracle follows a different route:

SELECT
     *
FROM
     transaction t
WHERE
         t.transaction_method_type_code = 'CREDIT'
     AND process_date BETWEEN TO_DATE ('11-MAR-2015') AND TO_DATE ('29-MAR-2015');

2.bmp

Which is understandable, because in this case transaction_method_type_code is more selective. But in real live we do not hardcode those dates, we keep them in a table.

SELECT
     *
FROM
     transaction t JOIN cntrl_process_date ON process_date BETWEEN previous_process_date AND current_process_date
WHERE
     t.transaction_method_type_code = 'CREDIT';

3.bmp

Oracle no longer chooses the right index, even though the difference between dates in cntrl_process_date is always one day. Which i still can understand because CBO has no idea what the actual data is. Bind picking concept is simply not applicable here.

I tried to help CBO by adding a constraint on a cntrl_process_date but It did not help:

ALTER TABLE cntrl_process_date ADD CONSTRAINT cbo_please CHECK (current_process_date - previous_process_date = 1);

Is there any way to help CBO with the right choice beside adding a hint?

Thank you everyone. I appreciate your time.

Tagged:

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,845 Gold Crown
    edited Apr 1, 2015 2:36PM Accepted Answer

    No version, so hard to give the best advice; and I think you must be getting the Note "plan_table is old version" since we're not seeing any sign of the predicate section.

    A possible strategy - since your control table has one row:

    Add a primary key to the table - even if it's through a meaningless key column (holding, say, the value 1), then change your query to:

    select * from transaction t

    where process_date between (select previous_process_date from cntrl_process_date where primary_key = 1)

                                       and (select current _process_date from cntrl_process_date where primary_key = 1)

    ;

    Where the subqueries are single row subqueries based on known unique (e.g. primary key) the optimizer will evaluate the subquery then optimize the main query - but only in the most recent versions of Oracle

    Regards

    Jonathan Lewis

    UPDATE:  Just seen your version number - somehow it became visible only after I wrote my reply.  11.1 is too old.

    Dmitrii Dunaev

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Apr 1, 2015 12:59PM

    Just one comment, I know it's not the answer for your question... use a format mask in your to_date please, otherwise, some client setting or someone changing the NLS date format will mess you up someday.

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Apr 1, 2015 1:10PM

    which version are you using? In 12c Join Cardinality Dynamic Sampling could be useful - in earlier releases there is the precompute subquery hint (though that's perhaps not a solution I would choose): Randolf Geist mentioned both approaches in (though the problem there was a little bit different).

  • Dmitrii Dunaev
    Dmitrii Dunaev Member Posts: 76
    edited Apr 1, 2015 1:17PM

    Absolutely, John.

  • Dmitrii Dunaev
    Dmitrii Dunaev Member Posts: 76
    edited Apr 1, 2015 1:20PM

    Martin, we use 11.1

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,845 Gold Crown
    edited Apr 1, 2015 2:36PM Accepted Answer

    No version, so hard to give the best advice; and I think you must be getting the Note "plan_table is old version" since we're not seeing any sign of the predicate section.

    A possible strategy - since your control table has one row:

    Add a primary key to the table - even if it's through a meaningless key column (holding, say, the value 1), then change your query to:

    select * from transaction t

    where process_date between (select previous_process_date from cntrl_process_date where primary_key = 1)

                                       and (select current _process_date from cntrl_process_date where primary_key = 1)

    ;

    Where the subqueries are single row subqueries based on known unique (e.g. primary key) the optimizer will evaluate the subquery then optimize the main query - but only in the most recent versions of Oracle

    Regards

    Jonathan Lewis

    UPDATE:  Just seen your version number - somehow it became visible only after I wrote my reply.  11.1 is too old.

    Dmitrii Dunaev
  • Dmitrii Dunaev
    Dmitrii Dunaev Member Posts: 76
    edited Apr 1, 2015 3:59PM

    Jonathan, seems like your solution works, maybe 11.1 is not that old for doing that

    SELECT
         *
    FROM
         transaction t
    WHERE
     t.transaction_method_type_code = 'CREDIT'
         AND 
          process_date between (select previous_process_date from cntrl_process_date where CNTRL_APP_CMPNT_TYPE_CODE = 'RPTDWF')
                                       and (select current_process_date from cntrl_process_date where CNTRL_APP_CMPNT_TYPE_CODE = 'RPTDWF')
    

    4.bmp

    Thank you Jonathan. Can you please reference an Oracle document or an article on sub-queries that are evaluated prior the main query is optimized?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,845 Gold Crown
    edited Apr 1, 2015 4:19PM

    The "pre-compute" thing would have given you predicates of the form:  process_date >= to_date('25-Mar-2015', 'dd-mon-yyyy') and process_date <= to_date('26-Mar-2015','dd-mon-yyyy').

    What's actually happened is that Oracle has effectively decided that it can use the Star Transformation approach to accessing the data as efficiently as possible.

    Notice how large the cost is, and how long the predicted execution time ?  How long did the query actually take to run ? It may be a case of "you're lucky right now, but watch out in 6 months' time".

    Here's one note about the early evaluation: https://jonathanlewis.wordpress.com/2014/02/06/12c-fixed-subquery/ It has some links in the comments to a couple of others, including Tanel's article on precompute_subquery, and this one of mine that justifies my original suggestion to you: https://jonathanlewis.wordpress.com/2014/09/29/12c-fixed-subquery-2/


    Regards

    Jonathan Lewis

This discussion has been closed.