This discussion is archived
8 Replies Latest reply: Oct 11, 2006 12:33 PM by RPuttagunta RSS

Explain Plan and Execution Plan in 10gR2.

RPuttagunta Journeyer
Currently Being Moderated
Hi,

Version 10.2.0.1.0.

I have two questions:

1) If the explain plan differs from the execution path in this version, then, is it safe to assume that the statistics are stale (or not gathered at all) on the underlying tables?

2) Can you in any way make a query use RBO instead of CBO? (I know it doesn't make any sense since CBO is lot smarter, but, for purely academic reasons).

Thank you,
Rahul.
  • 1. Re: Explain Plan and Execution Plan in 10gR2.
    530897 Explorer
    Currently Being Moderated
    When using bind variables the explain can differ from the actual execution plan because there is no bind variable peeking taking place for the explain. So a difference in explain and execution does not necessarily mean statistics are stale. If no stats are gathered and you are using cost based optimization, dynamic sampling should occur for both the explain and execution.

    You can use the rule hint to force a query to use RBO or set the optimizer_mode at the session level to RULE.
  • 2. Re: Explain Plan and Execution Plan in 10gR2.
    464759 Newbie
    Currently Being Moderated
    2) Can you in any way make a query use RBO instead of CBO?
    There maybe a way to do that with outlines: see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#i12916.
  • 3. Re: Explain Plan and Execution Plan in 10gR2.
    18622 Newbie
    Currently Being Moderated
    The rule-based optimizer is not in 10.2 anymore. Plus I don't see the RULE hint anymore in the 10.2 Performance tuning manual. One way to get practically the same behavior (in terms of access paths) would be to make sure you have no histogram. Then the optimizer would probably use any index, no matter how harmful it is, as it was doing with the rule-based optimizer.

    Daniel
  • 4. Re: Explain Plan and Execution Plan in 10gR2.
    530897 Explorer
    Currently Being Moderated
    The rule based optimizer is most definitely present in 10gR2. It might not be in the documentation, but it is still there.

    C:\sql>sqlplus test/test

    SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 10 15:43:34 2006

    Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options

    test@SVTEST> set autotrace traceonly
    test@SVTEST> alter session set optimizer_mode=rule;

    Session altered.

    Elapsed: 00:00:00.01
    test@SVTEST> select * from dual;

    Elapsed: 00:00:00.03

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 272002086

    ----------------------------------
    | Id | Operation | Name |
    ----------------------------------
    | 0 | SELECT STATEMENT | |
    | 1 | TABLE ACCESS FULL| DUAL |
    ----------------------------------

    Note
    -----
    - rule based optimizer used (consider using cbo)


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    3 consistent gets
    2 physical reads
    0 redo size
    407 bytes sent via SQL*Net to client
    381 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    test@SVTEST>
  • 5. Re: Explain Plan and Execution Plan in 10gR2.
    530897 Explorer
    Currently Being Moderated
    Also, if perform 10046 trace on SQL statements that generate recursive SQL you will see Oracle still uses the RULE hint on some of the queries.

    It is not supported, but it is still there.
  • 6. Re: Explain Plan and Execution Plan in 10gR2.
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Thank you, thank you very much for this info. I didn't know about this and I assumed that it was depricated (there isn't a "hidden parameter setting" for this anymore).

    The only way I know, was to mimic the rule behavior (via parameter settings, outline etc). Haven't found info about yet on the internet but it seems to be out there (so it can be found)
  • 7. Re: Explain Plan and Execution Plan in 10gR2.
    RPuttagunta Journeyer
    Currently Being Moderated
    Eric,
    Also, if perform 10046 trace on SQL statements that generate recursive SQL you will see Oracle still uses the RULE hint
    I did a trace and YES, i noticed that it in fact uses RULE.

    Would you know why that is? Since we know that we should be using CBO instead(assuming the underlying data dictionary also follows the same rules). Is this to make Oracle server run faster? Or was it because that those sql's written long ago and they never got to revisit some of the code?

    Thank you,
    Rahul.
  • 8. Re: Explain Plan and Execution Plan in 10gR2.
    530897 Explorer
    Currently Being Moderated
    I do not know for sure why some of the recursive sql still has the rule hints. Reasons such a speed/stability and just plain old code that has not yet been updated could be valid reasons.