CBO influences

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. Received on Fri Sep 09 2005 - 11:26:20 CDT