Solved

Different execution plans for a query with RULE hint

Posted on 2000-05-10
10
921 Views
Last Modified: 2011-09-20
I have a query with RULE hint and optimizer cretes different execution plans depending on if statistics are collected for the tables in the query.
My understanding is that the optimizer has to ignore statistincs in case of rule based optimization.
Oracle version: 8.0.5.2.1
Any comments?
0
Comment
Question by:davdonin
10 Comments
 
LVL 2

Expert Comment

by:AndrewRodionov
Comment Utility
Hi Denis,

Post your query to analyze it. I've tried to repeat your result but it was not successful ;-)

Andrew
0
 
LVL 3

Expert Comment

by:rkogelhe
Comment Utility
This sounds like an Oracle bug to me.
0
 
LVL 2

Author Comment

by:davdonin
Comment Utility
Okay. Please find below a script for creating sample tables.

=== cut here ===
DROP TABLE table2;
DROP TABLE table3;
DROP TABLE table4;
DROP TABLE table1;

CREATE TABLE table3 (
   id                 NUMBER(10)   PRIMARY KEY,
   description        VARCHAR2(30) NULL,
   gl_code            NUMBER(10)   NULL
);

CREATE TABLE table4 (
   id                 NUMBER(10)   PRIMARY KEY,
   description        VARCHAR2(30) NULL,
   gl_code            NUMBER(10)   NULL
);

CREATE TABLE table1 (
   id                 NUMBER(10)   PRIMARY KEY,
   account_id         NUMBER(10)   NOT NULL,
   code               NUMBER(10)   NULL,
   action_date_time   DATE         NOT NULL,
   duration           NUMBER(10)   NOT NULL,
   stream_id          NUMBER(10)   NULL,
   partition_value    NUMBER(10)   NOT NULL
)
PARTITION BY RANGE (partition_value) (
   PARTITION par00_29 VALUES LESS THAN (30),
   PARTITION par30_59 VALUES LESS THAN (60),
   PARTITION par60_99 VALUES LESS THAN (100)
);

CREATE INDEX table1_ind1 ON table1 (account_id, duration, action_date_time);
CREATE INDEX table1_ind2 ON table1 (stream_id, code);

CREATE TABLE table2 (
   id                 NUMBER(10)   PRIMARY KEY,
   table1_id          NUMBER(10)   NOT NULL,
   stream_id          NUMBER(10)   NULL,
   amount1            NUMBER(18,5) NOT NULL,
   amount2            NUMBER(18,5) NOT NULL,
   table3_id          NUMBER(10)   NOT NULL,
   table4_id          NUMBER(10)   NOT NULL,
   --
   CONSTRAINT table2_table1_fk FOREIGN KEY (table1_id) REFERENCES table1 (id),
   CONSTRAINT table2_table3_fk FOREIGN KEY (table3_id) REFERENCES table3 (id),
   CONSTRAINT table2_table4_fk FOREIGN KEY (table4_id) REFERENCES table4 (id)
);

CREATE INDEX table2_ind1 ON table2 (stream_id, table1_id);
CREATE INDEX table2_ind2 ON table2 (table1_id, table3_id, table4_id, stream_id, amount1);
=== cut here ===

Here is the query.
=== cut here ===
SELECT /*+ RULE*/
       NVL (t4.gl_code, t3.gl_code),
       SUM (t2.amount2)
  FROM table1 t1,
       table3 t3,
       table2 t2,
       table4 t4
 WHERE t1.code = p_code
   AND t1.stream_id = p_stream_id
   AND t1.account_id = p_account_id
   AND t1.partition_value = MOD (p_account_id, 100)
   AND t1.id = t2.table1_id
   AND t2.table3_id = t3.id
   AND t2.table4_id = t4.id
 GROUP BY NVL (t4.gl_code, t3.gl_code)
=== cut here ===

Now, if I execute this query with NO statistics collected I get the following execute plan:
=== cut here ===
SELECT STATEMENT   Cost = 6
  SORT GROUP BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS BY GLOBAL INDEX ROWID TABLE1
            INDEX RANGE SCAN TABLE1_IND1
          TABLE ACCESS FULL TABLE2
        TABLE ACCESS BY INDEX ROWID TABLE3
          INDEX UNIQUE SCAN SYS_C0020398
      TABLE ACCESS BY INDEX ROWID TABLE4
        INDEX UNIQUE SCAN SYS_C0020399
=== cut here ===

Then I collect statistics for TABLE1:
ANALYZE TABLE table1 ESTIMATE STATISTICS;
and the plan looks like:
=== cut here ===
SELECT STATEMENT   Cost = 5
  SORT GROUP BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS FULL TABLE2
          TABLE ACCESS BY GLOBAL INDEX ROWID TABLE1
            INDEX UNIQUE SCAN SYS_C0020404
        TABLE ACCESS BY INDEX ROWID TABLE3
          INDEX UNIQUE SCAN SYS_C0020398
      TABLE ACCESS BY INDEX ROWID TABLE4
        INDEX UNIQUE SCAN SYS_C0020399
=== cut here ===

Strange, isn't it?

NB: No records were inserted into the tables.
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
Try changing the init.ora parameter optimizer_mode to RULE and see if statistics change anything.

You can also change this parameter for the session only by:

ALTER SESSION SET OPTIMIZER_MODE = RULE;

I'd like to know if the hint works any different than setting the parameter.
0
 
LVL 1

Expert Comment

by:nitinpai
Comment Utility
It appears that ur query is not using the 'RULE' optimizer mode. Bcos if optimizer is using 'RULE'  then we won't have 'Cost' in the plan.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Accepted Solution

by:
sbenyo earned 100 total points
Comment Utility
It's because you're using Partitions!
Look at this from oracle's docs:

                   Which Optimizer is Used ?
                   -------------------------

Whenever a query is parsed a series of steps is followed to determine
whether the rule-based or cost-based optimiser should be used. The flow
chart below shows how this decisition is made.


                            QUERY IN

                                |
                                |
                               \ /
                 -----------------------------------
                | Is there a hint for table access, |
                | an ORDERED, FIRST_ROWS or         |
                | ALL_ROWS hint ?                   |
                |                                   |  yes
                | 7.3+ Does any table have PARALLEL |-------> COST
                |      set ?                        |
                |                                   |
                | 8.0+ Are any index-only or        |
                |      partition tables involved ?  |
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------   yes
                | Is there a RULE hint ?            |-------> RULE
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Is RULE set at SYSTEM or SESSION  |  yes
                | level and no CHOOSE hint ?        |-------> RULE
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Is ALL_ROWS or FIRST_ROWS set at  |  yes
                | SYSTEM or SESSION level and no    |-------> COST
                | CHOOSE hint ?                     |
                 -----------------------------------
                                |
                                | no
                                |
                 -----------------------------------
                | Are there statistics for any of   |  yes
                | the tables ?                      |-------> COST
                 -----------------------------------
                                |
                                | no
                                |

                               RULE



Notes :
-------

(1) Hints in view definitions or in the SELECT clause of an INSERT or
    CREATE TABLE are included for the flowchart.

(2) Recursive SQL choose the goal base on the instance setting.
    This includes SQL called from PL/SQL. Of course hints
    can be used in any SQL called by PL/SQL to override this default.

        Instance        Recursive
        --------        ---------
        RULE            RULE

        FIRST_ROWS      CHOOSE
        ALL_ROWS        CHOOSE
        CHOOSE          CHOOSE

 (3) If any table in a query has a parallel degreee greater than one
     (including the default degree), Oracle uses the cost-based optimizer
     for that query--even if OPTIMIZER_MODE = RULE, or if there is a RULE
     hint in the query itself

0
 
LVL 2

Author Comment

by:davdonin
Comment Utility
sbenyo, could you provide a reference to the documentation? thank you.
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
Hi,
Look it metalink.oracle.com for doc:

Doc ID:  Note:66484.1
Subject:  Which Optimizer is Used ?

 
0
 
LVL 2

Author Comment

by:davdonin
Comment Utility
thanks a lot. i wonder if it's mentioned anywhere in the standard oracle8 documentation?...
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
Hi,
Look in Oracle8 Server Concepts:
Chapter 19: The optimizer
At page 19-10 under When to Use the Cost-Based Approach
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now