Re: Oracle speed issue between 8i and 9i

From: VC <boston103_at_hotmail.com>
Date: Thu, 23 Oct 2003 22:34:15 GMT
Message-ID: <HHYlb.11445$Fm2.7109@attbi_s04>

Hello Jonathan,

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bn9at0$kr4$2$8302bc10_at_news.demon.co.uk...
>
>
> In practise, of course, the extra CPU cost is highly
> unreasonable. (You could use _old_connect_by_enabled=true
> to work around this), there is also a hint which I can't remember
> that relates to controlling the filtering that may help - there's
> a note about it on metalink somewhere.
>

"_old_connect_by_enabled" when set to 'true' from init.ora causes "Bug 2561037: CANNOT EXPORT FULL DATABASE WITH A PARAMETER , _OLD_CONNECT_BY_ENABLED=TRUE." We've also experienced an ora-03113 with this setting whilst executing a long-running query.
If you mean the 'no_filtering' hint, then using it makes the query performance even worse.
The only hint that helps is 'FULL'. Then the plan becomes:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=6) 1 0 SORT (AGGREGATE)

   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         INDEX (RANGE SCAN) OF 'A1_IDX' (NON-UNIQUE) (Cost=1
Card=48 Bytes=144)
   5    3         TABLE ACCESS (BY USER ROWID) OF 'A1'
   6    2       HASH JOIN
   7    6         CONNECT BY PUMP
   8    6         TABLE ACCESS (FULL) OF 'A1' (Cost=10 Card=30243

Bytes=181458)

... and the query runs 2-3 times faster but still slower than in 8i without hinting. However, with the hint being helpful during the whole tree traversal, if one wants to traverse a subtree, a non-hinted query is more performant. What's most frustrating, there appears to be not many knobs, one can use to 'improve' the query performance (except the FUUL hint).

Rgds. Received on Thu Oct 23 2003 - 17:34:15 CDT