Recently i ran into lot of Database Performance issues so i though of writing it here so that anyone who faces slowness in Solaris can be benefitted.
Solaris : 10
Oracle:11.2.03.
16 Core, 2 socket and 128 vCPU
Oracle T4-2 Rac One Database:
120GB RAM
Size 800GB and growing at 5GB per day.
We were running the DB with 20GB Memory without any issues for the almost 4 months and suddenly the ORA-04031 started appearing on shared Pool...after some testing we decided to increase the memory as we had 120GB Memory. THe database was configured with AMM(Memory_Max and Memory_Max_Target) parameter. We increase the Value to 30GB and set a default value of 10GB for shared pool. Here is where everything happened. The database startup took 15 minutes to start the instance and was opened. By the time the DB was started the server begin to Crawl. The DB server was absolutely slow and we had to revert to 20GB again and bounced the DB(basically downtime and pressure). The DB started fine and it was running...
Here is where all troubleshooting started..After checking lot of documents in metalink and contacting Oracle Support , The database was running on DISM(Dynamic Intimate shared Memory) which gives the felixibility to the application to lock the memory rather than OS and in this DB will lock and release memory. DISM also lock the equivalent amount of RAM on the disk swap as well. THis is where all went wrong becuase we had 30 GB of diskswap. For eg. if you want to allocate 40GB of memory to DB you need to have 40GB of disk swap allocated anything less than the Memory assingned will lead to server hanging or slow to respond as it has exhausted the disk swap.
DISM:
Oracle 40GB Memory=40GM Memory +40GB Disk swap=Vitrual swap
so the recommendation was to change the DB behavior from DISM to ISM
Unset the Memory_Max and Memory_Max_Target.
set the SGA_MAX_SIZE and SGA_TARGET to a same value. When we set this value Shared memory is allocated and locks the Memory during the startup and it doesnt care about the disk swap and the DB starts in few seconds....
If you have DISM enable make sure you have disk swap equivalent to memory allocation
ps -ef | grep dism
will display if the DISM process running..
ps -ef | grep oracle
Get the process id and check if the process is using dism or ism
pmap -xs
will display lot of things and look for
ism shmid.......
In solaris the virtual disk usage is really hard to interpret
If using DISM
swap -l will give physically allocated size
vmstat 5 10 will display the disk space usage especially the swap column.....
in ISM
VMSTAT 5 10
now the swap columns means disk swap free + memory free...
use echo ::memstat | mdb -k
to find the accurate usage of the Memory by oracle... which will be displayed as ANON.
Solaris : 10
Oracle:11.2.03.
16 Core, 2 socket and 128 vCPU
Oracle T4-2 Rac One Database:
120GB RAM
Size 800GB and growing at 5GB per day.
We were running the DB with 20GB Memory without any issues for the almost 4 months and suddenly the ORA-04031 started appearing on shared Pool...after some testing we decided to increase the memory as we had 120GB Memory. THe database was configured with AMM(Memory_Max and Memory_Max_Target) parameter. We increase the Value to 30GB and set a default value of 10GB for shared pool. Here is where everything happened. The database startup took 15 minutes to start the instance and was opened. By the time the DB was started the server begin to Crawl. The DB server was absolutely slow and we had to revert to 20GB again and bounced the DB(basically downtime and pressure). The DB started fine and it was running...
Here is where all troubleshooting started..After checking lot of documents in metalink and contacting Oracle Support , The database was running on DISM(Dynamic Intimate shared Memory) which gives the felixibility to the application to lock the memory rather than OS and in this DB will lock and release memory. DISM also lock the equivalent amount of RAM on the disk swap as well. THis is where all went wrong becuase we had 30 GB of diskswap. For eg. if you want to allocate 40GB of memory to DB you need to have 40GB of disk swap allocated anything less than the Memory assingned will lead to server hanging or slow to respond as it has exhausted the disk swap.
DISM:
Oracle 40GB Memory=40GM Memory +40GB Disk swap=Vitrual swap
so the recommendation was to change the DB behavior from DISM to ISM
Unset the Memory_Max and Memory_Max_Target.
set the SGA_MAX_SIZE and SGA_TARGET to a same value. When we set this value Shared memory is allocated and locks the Memory during the startup and it doesnt care about the disk swap and the DB starts in few seconds....
If you have DISM enable make sure you have disk swap equivalent to memory allocation
ps -ef | grep dism
will display if the DISM process running..
ps -ef | grep oracle
Get the process id and check if the process is using dism or ism
pmap -xs
will display lot of things and look for
ism shmid.......
In solaris the virtual disk usage is really hard to interpret
If using DISM
swap -l will give physically allocated size
vmstat 5 10 will display the disk space usage especially the swap column.....
in ISM
VMSTAT 5 10
now the swap columns means disk swap free + memory free...
use echo ::memstat | mdb -k
to find the accurate usage of the Memory by oracle... which will be displayed as ANON.
No comments:
Post a Comment