Hi
i am amit new dba. Getting poor performance.
Oracle Version : 11gR2
Linux : RH5
Details i gatherd from linux and oracle :
$ cat /proc/cpuinfo | grep proc
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7
$ free -m
total used free shared buffers cached
Mem: 16043 15991 52 0 21 14696
-/+ buffers/cache: 1274 14769
Swap: 16386 738 15647
]$ vmstat 3
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
6 2 756440 107476 21544 14978408 0 0 64 29 0 0 10 1 88 1 0
5 2 756440 50284 21712 15032776 0 0 41723 624 2845 2261 47 9 33 10 0
5 0 756440 51296 21712 15032640 0 0 2844 32 1817 847 43 11 46 1 0
3 0 756440 57100 21728 15028384 0 0 101 27 1767 797 40 11 49 0 0
5 0 756440 51624 21736 15028436 0 0 39 25 1558 604 37 8 54 0 0
3 0 756440 53580 21752 15028688 0 0 63 51 1707 735 39 11 50 0 0
7 3 756440 51380 21796 15030116 0 0 2099 16 2332 1488 30 8 60 2 0
3 1 756440 52004 21652 15030384 0 0 33767 551 3020 2629 33 12 49 7 0
4 0 756440 56652 21604 15022416 0 0 1937 760 2806 2160 54 15 25 6 0
$ iostat
Linux 2.6.18-238.1.1.el5 (Aspire) 08/17/2012
avg-cpu: %user %nice %system %iowait %steal %idle
10.47 0.00 0.98 0.69 0.00 87.86
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 25.91 749.19 232.56 10860385942 3371303876
sda1 0.00 0.00 0.00 2522 4
sda2 23.81 678.14 137.24 9830419009 1989421080
sda3 0.01 0.04 1.40 634744 20298960
sda4 0.00 0.00 0.00 6 0
sda5 0.38 60.19 57.59 872538993 834882896
sda6 0.20 4.14 4.37 59997174 63387120
sda7 0.00 0.00 0.00 660 0
sda8 1.52 6.68 31.96 96792386 463313816
sdb 10.40 279.80 234.40 4056048253 3397875371
sdb1 10.39 279.44 234.40 4050859053 3397872313
sdb2 0.00 0.36 0.00 5188464 3058
SQL> select count(*) from v$process;
COUNT(*)
----------
99
SQL> select count(*),event from v$session_wait group by event;
COUNT(*) EVENT
---------- ----------------------------------------------------------------
64 SQL*Net message from client
1 asynch descriptor resize
1 Streams AQ: emn coordinator idle wait
15 rdbms ipc message
3 db file sequential read
1 pmon timer
1 smon timer
1 Streams AQ: qmn slave idle wait
1 Space Manager: slave idle wait
1 Streams AQ: waiting for time management or cleanup tasks
1 VKTM Logical Idle Wait
COUNT(*) EVENT
---------- ----------------------------------------------------------------
1 Streams AQ: qmn coordinator idle wait
2 DIAG idle wait
5 EMON slave idle wait
SQL> sho sga
Total System Global Area 6413680640 bytes
Fixed Size 2213776 bytes
Variable Size 3690989680 bytes
Database Buffers 2684354560 bytes
Redo Buffers 36122624 bytes
SQL> sho pga
SQL> sho parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1G
SQL> sho parameter db_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 1536M
Please check this and guide me what should i do to inhance my database performance. i have to do some thing in my database level or linux server level.
whenever count(*) from process increase then linux server performance become poor.
and that time i have to restart my jboss server where java application loded.
please help me on this.
one thing that i know and i observed is need to SQL tune... and other please tell what should i do...
If you need any other information please tell me
Thanks
Edited by: 953051 on Aug 17, 2012 3:22 AM
Edited by: 953051 on Aug 20, 2012 11:59 PM