Home » RDBMS Server » Server Administration » PGA memory allocation (Oracle 10gR2,RHEL6)
PGA memory allocation [message #642910] Tue, 22 September 2015 21:20 Go to next message
devan0165
Messages: 27
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.
Re: PGA memory allocation [message #642911 is a reply to message #642910] Tue, 22 September 2015 21:26 Go to previous messageGo to next message
BlackSwan
Messages: 25848
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.
Re: PGA memory allocation [message #642917 is a reply to message #642911] Wed, 23 September 2015 01:05 Go to previous messageGo to next message
devan0165
Messages: 27
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.
Re: PGA memory allocation [message #642919 is a reply to message #642917] Wed, 23 September 2015 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
devan0165
Messages: 27
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The one that suits your need and environment.
Where did you find these formulas?
Always post your reference.
Re: PGA memory allocation [message #642925 is a reply to message #642922] Wed, 23 September 2015 01:53 Go to previous messageGo to next message
devan0165
Messages: 27
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?
Re: PGA memory allocation [message #642926 is a reply to message #642925] Wed, 23 September 2015 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What Google site?
Post the link.

Re: PGA memory allocation [message #642927 is a reply to message #642926] Wed, 23 September 2015 01:59 Go to previous messageGo to next message
devan0165
Messages: 27
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__
Re: PGA memory allocation [message #642929 is a reply to message #642927] Wed, 23 September 2015 02:02 Go to previous message
devan0165
Messages: 27
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:
  


Current Time: Sun Feb 18 01:59:08 CST 2018

Total time taken to generate the page: 1.59598 seconds