Oracle RAC performance tuning issues

[hongying dong]

Tim,
can't stop asking questions.

We are testing on importing about 10tb data to an Oracle RAC 2 nodes database. we have big RAMs and more than 10 CPUs on each node. when import this 10tb file, I noticed some thing as following:
1. we have about the same number of sessions on each node which is good, loadbalance works, right?
2. one node have less read and write, it is about 60% of the another node. I think somehow the Ethernet card or network cable or say hardware make this difference, not CRS, am I correct?

3. the initial setting sets all memory_target, memory_max_target, sga_target, sga_max_target to 0, I mean all of them set to 0. I personal strongly again it. on linux, I did 'free', I found OS have 125gb ram but only used 55 gb ram during the import, there are still 70gb free. so I assumed database should setup a real number(not 0) and higher number on sga_target or memory_target to get more RAMs from OS for database to use.

4. we may need to modify the kernal.shmmax which is set to 1/3 of toal OS RAM. how you think?

4. I understand there are some parameters can be tuned for ASM, I found list of it, but dont know it is a good practice to change ASM instance parameters

Sorry, too many questions. when I search online, I always find http://www.oracle-base.com, I am glad I have account with this web site.
Please help, thank you,

Robin

[Tim...]

Hi.

1) Yes. The sessions should be spread approximately evenly.

2) Remember, load balancing is done based on session count, not session activity/load. If you happen to get some of the less busy sessions on one node, this node will appear to be doing less work.

3) You say all parameters are zero, but something must be non-zero for you you have any memory allocated. Do you mean all the automatix settings are zero and you are actually using manual settings (shared_pool_size, db_cache_size...)?

On Linux, I would avoid automatic memory management (AMM) (memory_target) because it does not support hugepages. Instead I would use Automatic Shared Memory Management (ASMM) (sga_target, pga_aggregate_target), which is backed by hugepages. Of course, you also need to configure hugepages.

http://www.oracle-base.com/articles/lin ... nux-64.php

4) A rough guide specified by Oracle is 1/2 physical RAM. Read MOS Note 567506.1.

https://support.oracle.com/epmos/faces/ ... q5v21oo_74

5) I think you should avoid messing with ASM parameters unless you know specifically why you are doing it. In large databases make the allocation unit size bigger may help:

http://www.oracle-base.com/articles/11g ... hancements

There are some bugs associated with the default setting for MEMORY_TARGET (275M) on the ASM instance. If you have any specific issues with it, the recommendation is to increase it to 1G.

Cheers

Tim...

[hongying dong]

Wa, great info. I am so happy I got all once.

these sga_max_target, sga_target, memory_target, memory_max_target all set to 0, yes all of them are set to 0.
after I checked on OS, there were only 10% of RAM are used. this means these parameters dont do any automatic memory management. so we changed sga_target to big number, then 70% RAM are used with some swap(I think that is because the kernel.shmmax still in a lower number), but performance improved in a big deal.

I will not do any thing to ASM as you suggested.

Thanks again, your help is greatly appreciated.

Robin

[Tim...]

Hi.

The kernel.shmmax is not going to cause swap. Swap is caused when you've allocated too much memory to the SGA and PGA. Remember, you have to leave some memory for the the OS and processes.

If a single instance is running on a server, a reasonable starting point is to set sga_target + pga_aggregate_target to be about 70% of the physical memory. You them monitor the system and increase to reduce this based on how much free memory there is. The aim is to never swap.

Cheers

Tim...

[hongying dong]

Tim, thanks for your respond.

OK, I will keep my 11g memory which is sga+pga to 70% of the OS RAM. that is understandable.
But I like to know how kernel.shmmax related to OS total RAM? should be 70% or higher or 100% as OS RAM?
If we dont set to kernel.shmmax to 100% of OS RAM, what is rest of RAM going to do?

Thanks,
Robin

[Tim...]

Hi.

It sounds to me like you've not read the MOS note I linked to! It explains what the setting should be and why! Here is what it says,

"Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of "1/2 of physical RAM".

The maximum size of a shared memory segment is limited by the size of the available user address space. On 64-bit systems, this is a theoretical 2^64bytes. So the "theoretical limit" for SHMMAX is the amount of physical RAM that you have. However, to actually attempt to use such a value could potentially lead to a situation where no system memory is available for anything else. Therefore a more realistic "physical limit" for SHMMAX would probably be "physical RAM - 2Gb".

In an Oracle RDBMS application, this "physical limit" still leaves inadequate system memory for other necessary functions. Therefore, the common "Oracle maximum" for SHMMAX that you will often see is "1/2 of physical RAM". Many Oracle customers chose a higher fraction, at their discretion.

Occasionally, Customers may erroneously think that setting the SHMMAX as recommended in this NOTE limits the total SGA. That is not true. Setting the SHMMAX as recommended only causes a few more "shared memory segments" to be used for whatever total SGA that you subsequently configure in Oracle. For additional detail, please see Document 15566.1, "SGA, SHMMAX, Semaphores and Shared Memory Explained"

So the first sentence of the last paragraph explains exactly your misunderstanding of the parameter. It is not the maximum total size of shared memory, but the maximum size of a single segment. Multiple segments can be used to make up your SGA, so it's no problem if it is set smaller. Hence Oracle's recommendation of 1/2 physical memory!

If I give you a link to explain something, you should really read it. If not, you are wasting both our time...

Cheers

Tim...

[hongying dong]

Thank you Tim. I will read every link you show me.
This cleared so many of my questions this time.

thanks again,
Robin

[Tim...]