Performance problem

From: Gocha Mchedlishvili <gocham_at_cwix.com>
Date: Mon, 19 Jul 1999 03:01:33 GMT
Message-ID: <hywk3.237$Rl2.15242@news.cwix.com>

Hi,

We have 2 databases in-house both 7.3.4, one on NT 4.0 SP4(Dual P-Pro 200Mhz) and another one on Solaris 2.6(Ultra Sparc 300 Mhz). We use dedicated connections on both DBs. We also have optimizer_mode set to RULE. All parameters in init.ora file are the same, except SGA sizes.

I received complain from one of our developers that one query is taking too long to run on NT( about 1min 40s), but it's running fine on Solaris(2s). I found that if 'distinct' clause is removed from the query, it takes about 2s to run on both DBs.
I checked sorting, it's all done in memory. SGA fits in memory and no spooling occurs.
Also I found that if I include /* +CHOOSE */ hint in the query it runs fast.

My question is :
How can I get time of execution the query on NT get close to time on Solaris without changing optimizer mode and without using hints?

Thanks

Gocha Received on Sun Jul 18 1999 - 22:01:33 CDT