query performance mystery

It's either gremlins or I'm completely stumped.

Running Oracle 9i release 2 on Win2k and have identical setups running on three separate machines.

machine 1
- dual 900mhz cpus 4 gigs ram

• cpu maxes at 40%
• query runs in 7 seconds

machine 2
- single 900mhz cpus 2 gigs ram

• cpu maxes at 40%
• query runs in 10 seconds

machine 3
- dual 900mhz cpus 3 gigs ram

• cpu maxes at 8%
• query runs in 70 seconds!!!

The profiles are set with no CPU, memory, or other restrictions for the user on all instances across machines. I've even gone so far as to transfer all the instance related os level files from machine 1 to machine 3 (DBFs, Control files, etc) and gotten the same results. Results are the same after running stats. Same OS services are running with the same memory utilization. We never exceed 1 GIG of memory usage for the ENTIRE system. VM thrashing is not occurring.

Now somebody explain this to me like I'm a four year old. We're not I/O bound so why does it seem like the CPU utilization is being throttled? Shouldn't we be using the same % of CPU on the 3rd box as the other two? If there's no cpu usage limitation for the user on this database instance, is there a global setting that limits cpu utilization for Oracle on the box itself? If there is I can't seem to find it.

My forehead is getting FLATTENED from slamming my head against the wall on this one.

Any clues???? Received on Sat Jan 11 2003 - 05:05:51 CST