Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tuning problem
Two things to try - both specific to 8.1.x
> From: gdas_at_my-deja.com
> Organization: Deja.com
> Newsgroups: comp.databases.oracle.server
> Date: Sat, 10 Feb 2001 22:33:59 GMT
> Subject: 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 - 20:11:23 CST