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

kk2796 (AT) hotmail (DOT) com (Kevin) wrote in message news:<5a1dc659.0407210618.3564b58b (AT) posting (DOT) google.com>...
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