Re: Performance problems oracle 8i

legedoos_at_gmail.com wrote:
> Hi there!
>
> We have a problem. We are converting data of a database to an oracle
> environment. Therefore we have a production server (win 2000 4
> processors, a bulk of memory) and a test server (win 2000, only one
> processor, less memory). The production server should be faster than
> the test server.

Yes, you have a problem. Several, actually.

1. You're on Windows
2. W2K does not support a bulk of memory, 3GB would be the maximum.
3. No word about hardware. Your test machine has 1 PATA disk, production used a (HP/Compaq) raid controller. And RAID5...
4. Why would 4 processors be faster than 1?
5. Why would more memory means it's being used?
6. Why would more memory being used mean things go faster?

>
> We wrote lots of scripts on the test environment and this all was ok.
> But now we're trying the scripts on the production server and it all
> works very slow.

That will teach you to test on something else than a copy of production! Next time, test on a Linux blade.

>
> For testing we copied the production db to the test db, so the table
> spaces, tables, indexes where exactly the same. We use Spotlight to
> monitor the servers. What I can see is that the production server uses
> lots more Physical Reads/s for the same query than the test server. The
> test server finishes the script in 5 minutes, and the production server
> took all night and wasn't finished this morning.

And... what was it waiting for?

>
> Settings like SGA, buffer size and archive logging are the same on both
> servers.

Oops! Why?!? It's completely different hardware!

> Anyone an idea about what can be the problem?

Yes. Ignorance.
Read the concepts manual. Get trained. Read the concepts manual. (in that order). Read Tom Kytes books. And again. Read the tuning manual. Train, practice. Repeat for 2 years.

Post explain plans for same queries on both machines, and spot the difference. Drop Spotlight, ask for a refund, and start using statspack. It comes with the RDBMS, you know.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...