Re: query performance mystery

Suggest you run an explain one each system to see if the plans are the same. Also look at v$sessionwait to see what the session is waiting on.

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'

"Lou Fiorino" <lfiorino_at_louserium.com> wrote in message news:ecc5e35e.0301110305.57a0ae0c_at_posting.google.com...
> 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:29:01 CST