Re: CBO influences

"EdStevens" <quetico_man_at_yahoo.com> wrote in message news:1126283179.991124.138480_at_z14g2000cwz.googlegroups.com...
> Platform: 8.1.7 and 9.2.0.1 on various Win2k or Win2003
>
> Last week a developer brought me a query that was running
> satisfactorily in prod (8.1.7), but took 45 minutes in test (9.2.0).
>
> As it happens, we are in the middle of transitioning a group of
> databases from 8i to 9i, and so have several copies of this database
> running under both versions, on a variety of hardware, ranging from
> honking big 8-cpu blade servers to rusty old 2-cpu doorstops with mice
> nesting on the motherboard.
>
> Pulling an extended trace showed that the test box was spending 99+
> percent of its time on 'hs message to agent' events. (The query
> includes a join to a DB2 table, via the Oracle Transparent Gateway.)
> At first I suspected 8i vs. 9i gateway issues, but as we have tested on
> various platforms, we have eliminated that.
>
> As we tried to narrow down the variables, we discovered that the poor
> execution (high HS message events) only occured on one 9.2 database,
> running on a server that was in the middle of the range of available
> hardware. We found that on every other system, the optimizer was
> generating an access plan that featured several hash joins, where the
> one poor performing system generates nested loops. Even when we
> export/import the entire schema from the 'bad' 9.2 box to a 'good' 9.2
> box. Initialization parms idendtical, data structure and volume
> identical. And yet access plans are different, with disasterous
> results.
>
> So ... at this point I'm grasping for what other factors could
> influence the CBO. Where do I look for a reasonably detailed
> discussion of such? The qeury is rather lengthy, and explain plans
> never format well here. And I wouldn't expect anyone to pore over
> them. Just looking for some pointers on the kinds of things I should
> be looking at. At this point I'm out of ideas.
>

Just giving you a possibility to shoot at:

You say the data structure etc.are identical and have eliminated the possibility of a data restructure causing changes in data packing, cluster factors etc.

Have you checked to see if the bad 9i
database has enabled system statistics
(CPU costing). It is almost a guaranteed side effect of CPU costing that the optimizer gets biased (though not necessarily strongly) towards indexed access paths and away from table-scan access paths (which tends to mean more nested loops and fewer hash joins and sort/merge joins)

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005