CBO bug?

From: Kevin <kk2796_at_hotmail.com>
Date: 21 Jul 2004 07:18:12 -0700
Message-ID: <5a1dc659.0407210618.3564b58b@posting.google.com>

Very strange behavior - I ran into a scenario where the CBO (Oracle 8.1.7) actually computed a negative value for a cost of a query (and in turn, gave a horrendous execution plan involving unnecessary cartesian products of multi-million row tables).

I've stripped the real-world query down to pinpoint the problem, and developed a test case which consistently gives negative valued costs.

Here is the test case:

DROP TABLE TAB1
/
CREATE TABLE TAB1 (COL1 VARCHAR2(1))
/
CREATE INDEX TAB1_N1 ON TAB1(COL1)
/

• FOR :NUM_POPULATED_ROWS, USE ANY
• VALUE LARGER THAN 614

INSERT INTO TAB1
    SELECT 'X'
    FROM ALL_OBJECTS
    WHERE ROWNUM <= :NUM_POPULATED_ROWS /

• FOR NUM_NULL_ROWS, USE ANY VALUE
• LARGER THEN 2.5 TIMES :NUM_POPULATED_ROWS.
• SO, IF NUM_POPULATE_ROWS WAS 800,
• ANY VALUE LARGER THAN 2000 WILL DO

INSERT INTO TAB1
    SELECT NULL
    FROM ALL_OBJECTS
    WHERE ROWNUM <= :NUM_NULL_ROWS
/
COMMIT
/

• REPLACE 'APPS' WITH CORRECT OWNERNAME,
• IF NECESSARY BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'APPS', TABNAME => 'TAB1', CASCADE => TRUE); END;

/

---

• AFTER SCRIPT IS COMPLETE, DO AN EXPLAIN ON THIS QUERY:

  ---

 SELECT * FROM DUAL,
    (SELECT COL1

     FROM    TAB1,DUAL 
     WHERE   DUMMY = COL1

    )
 WHERE UPPER(DUMMY) = COL1(+); Obviously, with the test-case, there are other ways to write the query. In my real-world case, this is not the case, and I wound up having to use a comprehensive set of hints to guide the query's entire execution path (once the negative cost gets introduced, the CBO just goes crazy).

Can anyone confirm this bug on 8.1.7? Could someone with a 9i database give it a shot? Thanks!

-Kevin
Received on Wed Jul 21 2004 - 09:18:12 CDT