8 Replies Latest reply: Oct 11, 2006 2:33 PM by RPuttagunta RSS

    Explain Plan and Execution Plan in 10gR2.

    RPuttagunta
      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
          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
            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
              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
                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
                  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.
                    Marco Gralike
                    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
                      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
                        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.