Tuning Init.ora file

[ddiamond]

Version 8.0.5.0
Operating System: Windows 2003 Server.

We just moved our oracle database to a new server that has significantly more resources - much faster CPU, more ram, much faster hard drives, much faster network. And as expected, oracle does run a little faster on the new server, but we would have expected it to run a lot faster.

We observed that wereas on the old server, it was thrashing the hard drive, using most of available memory, using most of available CPU, and hammering the network, on the new server it is using hardly any of the CPU, disk, or network. Basically, the new server is bored. It looks like hardware is no longer a bottle neck. Is there anything I can tune to make Oracle take better advantage of its new resources. Specifically, are there any settings in init.ora that I should modify?

 

[SantaMufasa]

DDiamond,

You should accrue significant performance improvements by cranking up these memory-structure settings:[ul][li]shared_pool_size[/li][li]shared_pool_reserved_size (to about 10-15% of shared_pool_size)[/li][li]db_block_buffers (at least equal, in bytes, to the new shared_pool_size: "db_block_size" X "db_block_buffers" = bytes consumed by buffer cache.)[/li][/ul]Let us know your performance findings once you have bumped up these values.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

And maybe consider also these:

db_file_multiblock_read_count
sort_area_size
sort_area_retained_size
sort_multiblock_read_count






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

Thanks guys. I will look into it.

 

[KenCunningham]

Don't forget to keep a copy of your old init.ora just in case you need to revert. Sometimes 'bored' servers are preferable to 'interesting' ones!

I want to be good, is that not enough?

 

[ddiamond]

I increased the share_pool_size from 320M to 560M. That did have a noticable impact on performance. A report that had taken 15 minutes to run now runs in 10 minutes.

I also attempted to increase the db_block_buffers 93696 (732M 8k blocks) to 163968 (1281M 8k blocks). The database would not start with this new setting. Any idea why? I have another oracle database that runs just fine with that setting.

And Ken, your advice is duly noted. I am definately keeping a copy of our original init.ora.

 

[SantaMufasa]

The database would not start with this new setting. Any idea why?

The instance's alert<SID>.log should indicate the specific problem.


I'll bet you are running Oracle on a 32-bit server, correct? If so, there is a hard maximum of 1.75GB for the SGA that you hit with a 32-bit processor. (The total of 560M [shared pool] + 1.34GB [163,968 x 8,192 block bufferes] = 1.9GB.)

With a 64-bit server, you are limited only by physical memory.

So, if I am right about your using a 32-bit server, just back off your values to fit within 1.75GB and see what happens.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Error message:
ORA-00600: internal error code, arguments: [SKGMINVALID]

 

[ddiamond]

Through experimentation, I discovered that the largest value I can use for db_block_buffers is 100000, but 100000 * 8K + 560M = 1.3GB, so I should be able to go higher.

 

[SantaMufasa]

I'm just on the way to the hospital to take care of a daughter that was just in an auto accident (not seriously injured, and not her fault, luckily). So I can't look up the cause of the error message you encountered, above.

But did you determine that you are running on a 32-bit server? If so, I'll bet that is the cause of your error. Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Yes, we are running on a 32-bit server. Hope your daughter is OK.

 

[SantaMufasa]

Thanks, DDiamond, for the kind wishes. She is home now with a minor concussion.

Knowing that your server is 32-bit, did you re-try your settings so that the total falls below the 1.75GB limit? If so, what were the results of that exercise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Yes. I initially changed my db_block_buffers so that my total was equal to 1.7GB, but I still got the same error. I then kept lowering it until it worked. See my post above.

Through experimentation, I discovered that the largest value I can use for db_block_buffers is 100000, but 100000 * 8K + 560M = 1.3GB, so I should be able to go higher.

Am I calculating that correctly? BTW, if I set the db_block_buffers to 100001 I get the error, so 100000 is truly the limit with a share_pool_size = 560M. But based on a 1.75GB limit, 100000 seems too low.

 

[SantaMufasa]

But based on a 1.75GB limit, 100000 seems too low.

But we must also consider the additional Sytem Global Area (SGA) consumption of the Program Global Area (PGA) and User Global Area (UGA) memory structures...That would account for the calculated difference between 1.75GB and the totals you encountered.


So, the moral to the story is, if you want to go higher in SGA consumption, obtain a 64-bit environment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”