Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: CBO influences

Re: CBO influences

From: Jonathan Lewis <>
Date: Fri, 9 Sep 2005 16:56:22 +0000 (UTC)
Message-ID: <dfserm$nhb$>

"EdStevens" <> wrote in message
> Platform: 8.1.7 and 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)


Jonathan Lewis
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated 4th Sept 2005
Received on Fri Sep 09 2005 - 11:56:22 CDT

Original text of this message