Re: CBO influences

"EdStevens" <quetico_man@xxxxxxxxx> wrote in message
news:1126283179.991124.138480@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Database Design
    ... duty vehicles, rental equipment, parts used for maintenance. ... For instance one employee may assigned to a vehicle during their shift. ... When you get to convert these entities into tables you can create a Physical Data Structure ERD. ... Each of those entities is likely to become a table in your database and everything you have on your yellow pad is likely to become a field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Process to modify multiple queries simultaneously
    ... Access is a relational database product. ... Disclaimer: This author may have received products and services mentioned ... you lost me on data structure. ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.queries)
  • Re: Design issues
    ... Thanks for your suggestions on naming the fields. ... I wanted some help and advise on my existing database table structure and ... normalize your data structure, I'd question how a ProjectNo or a PONo ...
    (microsoft.public.access.tablesdbdesign)
  • Re: ID field as logical address
    ... and it's caused by failure to fully normalise the data structure. ... What would the data structure look like if it were fully normalized? ... Adding an ID field does at least allow the database to record the fact that two different people with the same name and phone number exist. ... Adding an ID field is only useful if you can reliably link the ID to the real person. ...
    (comp.databases.theory)
  • CBO influences
    ... Last week a developer brought me a query that was running ... databases from 8i to 9i, and so have several copies of this database ... generating an access plan that featured several hash joins, ... one poor performing system generates nested loops. ...
    (comp.databases.oracle.server)
Loading