"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
news:3b97785e.21871494_at_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
Just FYI and amusement: the word 'zot' in Dutch means 'mad' in English.
So that's why your rant alone is very funny.
Regards,
Sybrand Bakker, Senior Oracle DBA
Received on Thu Sep 06 2001 - 09:36:22 CDT
