Calling all Oracle DBA gurus!

[Thor86]

Can someone please help me in setting up the INIT.ORA file for optimal performance for the following test hardware using Oracle 8i?

Win2k OS
Dual P3-500's
1 Gig of RAM

Thanks.

 

[Cuular]

Hmm you left out, the size of the database, whether it will be used in parallel access mode, what kind of application is going to access it. Some applications require job_queue's some don't. How many disks you have to spread the the database over, are they striped in a raid configuration or not...

Just the bare system specs is NEVER enough info to determine what initialization parameters you need. But using the gui to create the database will give you a good starting init file that can be adjusted later on.

 

[Thor86]

Cuular,

Thanks, for the response! I will look further into those metrics your described and see what I can find, and edit my post accordingly.

 

[Cuular]

A couple of other things to look for:

How many physical disks does the computer have? For best performance you want your data, index, and temporary space on different physical disks if you can.

If you don't have raided disks, what size is the disks ondisk cache/buffer size? You can squeeze out some extra performance by optimizing your db_blocksize in relation to your disks buffer size.

But the start of it is to determine how big the database is, how big each tablespace (data, index, temp, rollback) are going to be, and how many physical disks you have to lay it out across.

If you have 2 but not 3 disks then it is best to put data and index on one and rollback and temp on the other. Using some logic to dictate that. Just about everything you do creates rollback entries and temp space entries. So to speed up reads and writes you seperate those two from the data that you are reading and writing. So you have a seperate disk bogged down with the rollback and temp than you do reading and writing the data.

And when it is all done and over with, the index's don't change that much, so having them on the same disk as your data, after you are done importing or populating the database will not cause that much of a problem.

Hope that helps.

 

[Thor86]

Thanks again for your insight to this matter. The actual problem we are having is that every so often, and this only occurs whenever a certain app (I think it is written in Java) causes this fatal error on our Oracle DB server:

"ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")"

I will get back to you on the other metrics, but I have to do some digging, since I didn't setup the actual server so I do not have a readily available audit sheet to look at the specs. Also haven't setup the dbs so I will have to check on that myself and get all the information of db size, etc.

 

[Cuular]

<< "ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")"
>>



Now we are getting somewhere. That piece of information should have been in the original post. The cure for that is to increase the shared_pool_size definition in the init.ora and restart the database.

The amount to up it is usually about 4-5 times the amount it can't allocate if it is above 10k bytes, or about 30-40,000 if the amount is below 10k, that it can't allocate.

As more and more users, and applications start to use the database, you will have to up this parameter on a semi routine basis.

For a comparison here on a database on a unix server, the shared_pool_size started out at 10,000, and has grown from that up to 75,000,000 for it's current value. It changes about 20-30,000 at a time as our users and applications changed requiring more resources.