OraFAQ Forum: Server Administration » PGA memory allocation

Home » RDBMS Server » Server Administration » PGA memory allocation (Oracle 10gR2,RHEL6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PGA memory allocation [message #642910]

Tue, 22 September 2015 21:20

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

I have configured the below oracle 10gR2 server with total RAM = 16GB as below:-

1) SHMMAX = 13GB
2) SGA = 8GB
3) PGA = 4800M
4) CTX index memory = 2 GB.

From the above settings, I just want to know whether the PGA allocated from operating system RAM or from SHMMAX ?

Is PGA memory allocated from balance OS RAM (3GB) or from SHMMAX (13GB) ?

Please help me to solve this mystery.

Report message to a moderator

Re: PGA memory allocation [message #642911 is a reply to message #642910]

Tue, 22 September 2015 21:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

PGA comes from OS & can consume ALL existing RAM, such that it can crash the OS.
V10 has no ability to limit PGA size.

Report message to a moderator

Re: PGA memory allocation [message #642917 is a reply to message #642911]

Wed, 23 September 2015 01:05

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Thanks for your speedy reply.

Now that, I have increased the total physical memory from 16GB to 20GB.

May I know what is the new kernel parameters that I need to configure to maximize the 20GB ?

What should be the new values for SHMMAX, SHMALL, SGA, PGA and CTX ?

If I configure the SHMMAX to be 10GB, half of physical memory, what should be the new value for SHMALL ?

Can I configure the CTX index memory to be more than 2GB ?

Please assist me on this configuration problem.

Report message to a moderator

Re: PGA memory allocation [message #642919 is a reply to message #642917]

Wed, 23 September 2015 01:13

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There are no a priori good parameter values, otherwise Oracle would set it without you.
You have to follow the performances of your database and database applications using StatsPack or AWR.
There are also many V$%ADVICE views which will help to configure memory parameters, use them.

[Updated on: Wed, 23 September 2015 01:13]

Report message to a moderator

Re: PGA memory allocation [message #642920 is a reply to message #642919]

Wed, 23 September 2015 01:35

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Thanks

Is the formula for SHMALL = (SHMMAX/pagesize) or (SHMMAX/Hugepagesize) ?

With pagesize = 4096, the new value for SHMALL = SHMMAX/4096 = (10737418240/4096) = 2621440

OR

With Hugepagesize = 2048, the new value for SHMALL = SHMMAX/2048 = (10737418240/2048) = 5242880

Which of the two above methods shall I choose to configure SHMALL ?

[Updated on: Wed, 23 September 2015 01:36]

Report message to a moderator

Re: PGA memory allocation [message #642922 is a reply to message #642920]

Wed, 23 September 2015 01:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The one that suits your need and environment.
Where did you find these formulas?
Always post your reference.

Report message to a moderator

Re: PGA memory allocation [message #642925 is a reply to message #642922]

Wed, 23 September 2015 01:53

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Those comes from google site.

You did mentioned that 'The one that suits your need and environment'

What do you mean by environment here ?

Are you referring to the operating system or oracle environment?

Report message to a moderator

Re: PGA memory allocation [message #642926 is a reply to message #642925]

Wed, 23 September 2015 01:55

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What Google site?
Post the link.

Report message to a moderator

Re: PGA memory allocation [message #642927 is a reply to message #642926]

Wed, 23 September 2015 01:59

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

Sorry, I don't have the link but got the script below from the same link:-

# Checking Oracle user real uid and gid
oracle_uid=$(id -ru oracle 2> /dev/null)
oracle_gid=$(id -rg oracle 2> /dev/null)

# Checking the available memory
memtotal=$(awk '/MemTotal/ { print $2 }' /proc/meminfo)
echo "memtotal : " ${memtotal}

# Checking the memory page size
pagesize=$(getconf PAGE_SIZE)
echo "pagesize : " ${pagesize}

# Checking the huge page size
hugepagesize=$(awk '/Hugepagesize/ { print $2 }' /proc/meminfo)
echo "hugepagesize : " ${hugepagesize}

# Approximation of the total system memory
if [ $memtotal -gt 66060288 ]; then # > 63GB
memtotal=67108864
elif [ $memtotal -gt 32505856 ]; then # > 31GB
memtotal=33554432
elif [ $memtotal -gt 15728640 ]; then # > 15GB
memtotal=16777216
elif [ $memtotal -gt 7340032 ]; then # > 7GB
memtotal=8388608
elif [ $memtotal -gt 3145728 ]; then # > 3GB
memtotal=4194304
elif [ $memtotal -gt 2096852 ]; then # about 2GB
memtotal=2097152
fi

# Shared memory parameters
shmmax=$(($memtotal * 1024 / 2))
echo "shmmax : " ${shmmax}
shmmni=4096
shmall=$(($memtotal * 1024 / $pagesize))
echo "shmall : " ${shmall}

# Semaphore parameters used in the Oracle validated configurations
semmsl=250
semmni=142
semmns=32000
semopm=100

# Allocate huge pages if the system has 4GB+
if [ $memtotal -gt 3145728 ]; then
nr_hugepages=$(($memtotal / 2 / $hugepagesize))
else
nr_hugepages=0
fi
# Set memlock value corresponding to the size of memory allocated for huge pages
if [ $nr_hugepages -gt 0 ]; then
memlock=$(($nr_hugepages * $hugepagesize))
else
memlock=32
fi

#cat >> /etc/sysctl.conf << __EOF__
cat >> sysctl.conf << __EOF__
## Added by oracle10g-platform
fs.aio-max-nr = 3145728
kernel.shmmax = ${shmmax}
kernel.shmmni = ${shmmni}
kernel.shmall = ${shmall}
kernel.sem = ${semmsl} ${semmns} ${semopm} ${semmni}
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
net.ipv4.tcp_rmem = 4096 262144 4194304
net.ipv4.tcp_wmem = 4096 262144 262144
net.ipv4.ip_local_port_range = 1024 65000
#vm.swappiness = 0
#vm.dirty_background_ratio = 3
#vm.dirty_ratio = 15
#vm.dirty_expire_centisecs = 500
#vm.dirty_writeback_centisecs = 100
vm.hugetlb_shm_group = ${oracle_gid:=0}
vm.nr_hugepages = ${nr_hugepages}
## End of additions from oracle10g-platform
__EOF__

Report message to a moderator

Re: PGA memory allocation [message #642929 is a reply to message #642927]

Wed, 23 September 2015 02:02

devan0165
Messages: 28
Registered: September 2015
Location: Malaysia

Junior Member

This is another script I got from another site:-

# obtain the total memory from the system:
mem=$(free|grep Mem|awk '{print $2}')

#Convert the value of $mem to bytes:
totmem=$(echo "$mem*1024"|bc)

#Get the Hugepagesize from /proc/meminfo:
huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')

#Calculate what 75% of the total memory on the system for SHMMAX:
max=$(echo "$totmem*75/100"|bc)

#Divide the SHMMAX value by the Hugepagesize to get SHMALL:
all=$(echo "$max/$huge"|bc)

[Updated on: Wed, 23 September 2015 02:04]

Report message to a moderator

Previous Topic:	Need XMLTYPE & LOB segment explanation
Next Topic:	Error ORA-47306 causing error for Checkpoint

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 10:26:22 CST 2025