I still believe CBO is better than RBO, but WTH?

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Thu, 06 Sep 2001 13:34:18 GMT
Message-ID: <3b97785e.21871494@news>

<rant>

This is the SQL:

>cat zot1.sql

explain plan set statement_id = 'z1' for SELECT
L1.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) ,count(1) FROM
zot A
,PSTREESELECT06 L1
, PSTREESELECT10 L
WHERE A.LEDGER='BUDGETS' AND A.FISCAL_YEAR=2002 AND (A.ACCOUNTING_PERIOD=2 OR A.ACCOUNTING_PERIOD=998) AND A.BUSINESS_UNIT='QAS01'
AND L1.SELECTOR_NUM=891
AND A.ACCOUNT=L1.RANGE_FROM_06
AND L1.TREE_NODE_NUM BETWEEN 828402330 AND 1483389475 AND L.SELECTOR_NUM=899

AND A.DEPTID=L.RANGE_FROM_10
AND L.TREE_NODE_NUM BETWEEN 888888889 AND 1333333333
AND A.CURRENCY_CD='AUD' AND A.STATISTICS_CODE=' '

GROUP BY L1.TREE_NODE_NUM
;

Indexes are in the logical places you see above. Ie, the ones "predicated" to constants. Tables and indexes all freshly re-organized.

Now, in SQL*Plus:

Yes Mastah?analyze table zot delete statistics;

Table analyzed.

Yes Mastah?@zot1

Explained.

Yes Mastah?@gplan
Enter statement_id:>z1
Press Return to continue...
QUERY_PLAN

SELECT STATEMENT 4
SORT GROUP BY 4

      NESTED LOOPS 4
        NESTED LOOPS 1
          TABLE ACCESS BY INDEX ROWID ZOT 1
            INDEX RANGE SCAN ZOT_I1 1
          INDEX RANGE SCAN PS_PSTREESELECT10 1
        INDEX RANGE SCAN PS_PSTREESELECT06 409

8 rows selected.

Yes Mastah?set timing on
... edit zot1 to insert "--" in front of the "explain plan" Yes Mastah?@zot1
Press Return to continue...
TREE_NODE_NUM SUM(A.POSTED_TOTAL_AMT) COUNT(1)

------------- ----------------------- ----------

blah,blah,blah......

36 rows selected.

Elapsed: 00:00:01.85
         ^^^^^^^^^^^

Nice eh? Wait, here comes the rub:

Yes Mastah?analyze table zot compute statistics;

Table analyzed.

Elapsed: 00:00:09.99
Yes Mastah?@zot1

Explained.

Elapsed: 00:00:00.03
Yes Mastah?@gplan
Enter statement_id:>z1
Press Return to continue...
QUERY_PLAN

SELECT STATEMENT 1
SORT GROUP BY 1

      NESTED LOOPS 1
        NESTED LOOPS 1
          INDEX RANGE SCAN PS_PSTREESELECT10 1
          TABLE ACCESS BY INDEX ROWID ZOT 151
            INDEX RANGE SCAN ZOT_I1 151
        INDEX RANGE SCAN PS_PSTREESELECT06 409

8 rows selected.

Elapsed: 00:00:00.83
Yes Mastah?@zot1
Press Return to continue...
TREE_NODE_NUM SUM(A.POSTED_TOTAL_AMT) COUNT(1)

------------- ----------------------- ----------

blah,blah,blah.....

36 rows selected.

Elapsed: 00:01:07.34
         ^^^^^^^^^^^

AAAAAAARRRRRRRRRRGGGGGHHHH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Next time someone tells me the 8.0 and 8.1 optimizers are very sophisticated, I'm gonna kill him/her!

Roll-on 9i. Anything is better than this!

</rant>

PS: No load on system, repeated many, many times to make sure buffers are not affecting this, same timezone, same box, same universe, yadda yadda.

How did I fix this? Picked up the index ZOT_I1 and re-created it with the columns in the order of precedence of the data in the predicates above. Shouldn't make the slightest difference, because without stats I was getting good performance with EXACTLY the same index. With the new index, I get the same performance with or without stats.

And they wonder why my hair is going white... <groan>

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Sep 06 2001 - 08:34:18 CDT