tuning problem

I've got one hand tied behind my back on this one and the CBO isn't cooperating with me. I'm running 8.1.6

The reason I'm hampered is that the application doesn't support usage of hints. So we rely completely on CBO and effective statistics gathering to yield optimal query performance.

There are a handful of queries that I have that simply will not do what I want them to do in regards to the execution plans.

We have a dev system that DOES everything properly. I exported that database and loaded it onto a new server (more powerful server) and still can't get the right execution plans on particular queries.

I've tried every form of stats gathering method on the tables, indexes, columns I could think of. tried histograms with various bucket sizes and I still can't get oracle to cooperate.

The two problems are 1. Wrong index selected. 2. Choice to use hash join when it should be using nested loops.

I've struggled with this for an entire day and I'm completely stumped.

The databases are identical on the dev server and the new server. The only difference is that the new server has a bigger sga and shared pool because it's a more powerful machine.

Related question: (this would solve my problem too). Is there anyway to create an integrity constraint that is used only for the purpose of data integrity. This one table that I'm having the problem with has a composite primary key (4 columns). I need this to enforce data integrity, but I really don't want this index used for query plans...and this is the index oracle keeps selecting on this new machine.

I've tried reordering the columns in that integrity constraint, most selective to least selective, vice-versa etc... and I still can't get CBO to work the same on this new machine as it does on the development machine.

If I manually write the queries to use the USE_NL hint along with the index hint to use the appropriate index (and not the composite primary key index)... I get an excellent low cost plan with low sql recursion, low consistent gets etc...

This new machine is about 10 times more powerful than the dev machine but right now, the dev machine is outperforming this new machine on about 5 queries. I'm trying to figure this out before my management realizes this.

I'd appreciate any help.

Thanks,

Sent via Deja.com
http://www.deja.com/ Received on Sat Feb 10 2001 - 16:33:59 CST