471,108 Members | 1,279 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

CBO bug?

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
Jul 19 '05 #1
1 2985
kk****@hotmail.com (Kevin) wrote in message news:<5a**************************@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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.