Database resource configuration
Posted by satish on Jun 04, 2019; 11:09am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Database-resource-configuration-tp7414.html
Dear Erman,
Need your expertise
We are on oracle version 11.2.0.1.0 supporting tomcat application server
We have only one instance running on server.It has 32 GB ram out of which we have assigned 12GB to SGA and 4 GB to PGA as below.
1)From the free output below,We could see the system has 30G free space and the used is 2G.We could not understand why 5G of swap is used here when we have lots of free space.Is this normal?Will this create performance issues?
We have 6 physical core and 2 sockets,total 12 cores on this server.
2)Idle shows 90% but some processes are there in runqueue.Is this CPU problem?
SQL> sho parameter sga
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 12032M
sga_target big integer 12032M
SQL> sho parameter pga
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
pga_aggregate_target big integer 4000M
SQL>
[oracle@PAPLVERTA~]$ free -m
total used free shared buffers cached
Mem: 32158 22387 9770 0 44 20278
-/+ buffers/cache: 2065 30092
Swap: 63999 5472 58527
[oracle@PAPLVERTA~]$ cat /proc/cpuinfo|grep -i proc|wc -l|grep -v grep
24
NAME TYPE VALUE
------------------------------------ --------------- -----
cpu_count integer 24
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 24
SQL>
[oracle@PAPLVERTA~]$ sar -u 2 5
Linux 2.6.18-194.el5 (SSSTINDCPODB01.SSST.ORG) 06/04/2019
07:33:53 AM CPU %user %nice %system %iowait %steal %idle
07:33:55 AM all 9.83 0.00 2.08 0.04 0.00 88.04
07:33:57 AM all 5.89 0.00 0.19 0.00 0.00 93.92
07:33:59 AM all 7.89 0.00 0.29 0.08 0.00 91.73
07:34:01 AM all 7.39 0.00 1.10 0.00 0.00 91.50
07:34:03 AM all 10.20 0.00 0.75 0.02 0.00 89.03
Average: all 8.24 0.00 0.88 0.03 0.00 90.84
[oracle@PAPLVERTA~]$
SQL> !top
top - 07:33:38 up 8 days, 15:26, 1 user, load average: 5.75, 5.99, 5.96
Tasks: 434 total, 4 running, 430 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.3%us, 1.2%sy, 0.0%ni, 91.9%id, 1.5%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 32930016k total, 22875996k used, 10054020k free, 57400k buffers
Swap: 65535992k total, 5603816k used, 59932176k free, 21100308k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17641 oracle 16 0 12.0g 1.0g 1.0g R 100.9 3.2 0:33.53 oracleCAPROD (LOCAL=NO)
14411 oracle 20 0 12.0g 1.6g 1.6g R 98.9 5.2 41:25.39 oracleCAPROD (LOCAL=NO)
17194 oracle 24 0 12.2g 4.0g 3.8g R 98.9 12.9 8:38.33 oracleCAPROD (LOCAL=NO)
17698 oracle 15 0 13000 1292 740 R 1.9 0.0 0:00.02 top
1 root 15 0 10348 684 576 S 0.0 0.0 0:12.77 init [5]
2 root RT -5 0 0 0 S 0.0 0.0 0:00.16 [migration/0]
3 root 34 19 0 0 0 S 0.0 0.0 0:00.37 [ksoftirqd/0]
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/0]
5 root RT -5 0 0 0 S 0.0 0.0 0:00.15 [migration/1]
6 root 34 19 0 0 0 S 0.0 0.0 0:04.33 [ksoftirqd/1]
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 [watchdog/1]
8 root RT -5 0 0 0 S 0.0 0.0 0:00.02 [migration/2]
[oracle@PAPLVERTA~]$ vmstat 2 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
4 0 5603816 9999456 58344 21102688 0 1 168 426 1 1 5 1 92 1 0
7 0 5603812 9775612 58384 21102732 0 0 1008 168 1273 2908 17 4 79 0 0
7 0 5603812 9475436 58392 21102824 0 0 1000 72 1017 2584 26 3 71 0 0
4 0 5603812 9593280 58448 21102788 0 0 1004 110 1085 2722 18 1 82 0 0
2 0 5603812 9717060 58488 21102812 0 0 1016 146 1168 2803 12 0 87 0 0
[oracle@PAPLVERTA~]$
The recent change is,we have increased SGA from 6gb to 12gb....increased pga size from 1gb tp 4gb.
Thank you,
satish