Re: CBO bug?

kk2796_at_hotmail.com (Kevin) wrote in message news:<5a1dc659.0407210618.3564b58b_at_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

Run the query with event 10053 set, and you'll get a trace file outlining how CBO calculated the cost in this specific case. You're going to need it anyway when submitting a TAR. alter session set events '10053 trace name context forever, level 1' as also discussed in Jonathan Lewis' book.

Sybrand Bakker
Senior Oracle DBA Received on Thu Jul 22 2004 - 09:58:17 CEST