Home » RDBMS Server » Performance Tuning » big oracle problem please help
big oracle problem please help [message #112099] Tue, 22 March 2005 23:56 Go to next message
dong94
Messages: 2
Registered: March 2005
Junior Member
problem:
when almost 125 user have connected into oracle, then new users can not connect to oracle.
new users just sleeping and no response, some unix commands like ps,top same so.
I can not find any error message in file /var/adm/messages or alert_log.

the truss sqlplus output is:
22104: shmget(537806143, 0, 0) = 12288
22104: shmat(12288, 0x60000000, 0600003) = 0x60000000
22104: close(4) = 0
22103: read(10, 0x082BE65E, 2048) (sleeping...)

vmstat 2 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr f0 s0 s1 s2 in sy cs us sy id
0 0 0 8156272 2513808 941 595 2479 226 226 0 0 0 26 69 10 1460 2834 2917 7 15 78
0 0 0 6902980 1176196 15 190 0 0 0 0 0 0 1 5 0 645 158807 971 18 16 66
0 0 0 6899552 1174636 209 1359 0 0 0 0 0 0 1 0 0 741 156510 652 16 17 67
0 0 0 6898444 1174148 5 0 0 0 0 0 0 0 0 2 0 553 155806 328 16 12 72
0 0 0 6900316 1174816 43 212 0 4 4 0 0 0 1 2 0 567 155880 853 16 12 71


my system environment:
SunOS ho0 5.8 Generic_108529-24 i86pc i386 i86pc

Memory size: 3808 Megabytes

*
* System Configuration
*
swap files
swapfile dev swaplo blocks free
/dev/dsk/c1t0d0s1 29,1 8 4194288 4194288
/swap_file1 - 8 4095992 4095992
/swap_file2 - 8 4095992 4095904
*
* Tunable Parameters
*
8388608 maximum memory allowed in buffer cache (bufhwm)
3210 maximum number of processes (v.v_proc)
99 maximum global priority in sys class (MAXCLSYSPRI)
3205 maximum processes per user id (v.v_maxup)
600 auto update time limit in seconds (NAUTOUP)
25 page stealing low water mark (GPGSLO)
1 fsflush run rate (FSFLUSHR)
25 minimum resident memory for avoiding deadlock (MINARMEM)
25 minimum swapable memory for avoiding deadlock (MINASMEM)
*
* Process Resource Limit Tunables (Current:Maximum)
*
Infinity:Infinity cpu time
Infinity:Infinity file size
Infinity:Infinity heap size
0x0000000000848000:0x0000000007f48000 stack size
Infinity:Infinity core file size
0x0000000000000400:0x0000000000010000 file descriptors
Infinity:Infinity mapped memory
*
* Streams Tunables
*
9 maximum number of pushes allowed (NSTRPUSH)
65536 maximum stream message size (STRMSGSZ)
1024 max size of ctl part of message (STRCTLSZ)
*
* IPC Messages
*
8192 max message size (MSGMAX)
65535 max bytes on queue (MSGMNB)
1024 message queue identifiers (MSGMNI)
1024 system message headers (MSGTQL)
*
* IPC Semaphores
*
1024 semaphore identifiers (SEMMNI)
8192 semaphores in system (SEMMNS)
8192 undo structures in system (SEMMNU)
1024 max semaphores per id (SEMMSL)
100 max operations per semop call (SEMOPM)
256 max undo entries per process (SEMUME)
32767 semaphore maximum value (SEMVMX)
16384 adjust on exit max value (SEMAEM)
*
* IPC Shared Memory
*
4294967295 max shared memory segment size (SHMMAX)
32 min shared memory segment size (SHMMIN)
1024 shared memory identifiers (SHMMNI)
100 max attached shm segments per process (SHMSEG)
*
* Time Sharing Scheduler Tunables
*
60 maximum time sharing user priority (TSMAXUPRI)
SYS system class name (SYS_NAME)

my oracle:
ORACLE RDBMS Version: 7.3.4.3.0.

Fixed Size 34716
Variable Size 335905248
Database Buffers 737280000
Redo Buffers 512000

processes = 450
use_ism = TRUE
pre_page_sga = TRUE

pmem out is:

4449: oracleRT (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Address Kbytes Resident Shared Private Permissions Mapped File
08041000 28 28 - 28 read/write/exec [ stack ]
08050000 7812 5260 5260 - read/exec oracle
08800000 36 36 12 24 read/write/exec oracle
08809000 664 612 - 612 read/write/exec [ heap ]
60000000 328068 234140 234140 - read/write/exec/shared [ shmid=0x1800 ]
74061000 4 4 4 - read/shared [ shmid=0x1800 ]
74062000 500 500 500 - read/write/exec/shared [ shmid=0x1800 ]
740DF000 4 4 4 - read/shared [ shmid=0x1800 ]
740E0000 720000 720000 720000 - read/write/exec/shared [ shmid=0x1800 ]
DF940000 4 4 - 4 read/write/exec [ anon ]
DF950000 12 8 8 - read/exec libmp.so.2
DF963000 4 4 - 4 read/write/exec libmp.so.2
DF970000 532 488 488 - read/exec libc.so.1
DFA05000 24 24 - 24 read/write/exec libc.so.1
DFA0B000 8 8 - 8 read/write/exec libc.so.1
DFA20000 44 36 36 - read/exec libm.so.1
DFA3A000 8 8 - 8 read/write/exec libm.so.1
DFA40000 4 4 - 4 read/write/exec [ anon ]
DFA50000 24 20 20 - read/exec librt.so.1
DFA66000 4 4 - 4 read/write/exec librt.so.1
DFA70000 28 24 24 - read/exec libaio.so.1
DFA87000 4 4 - 4 read/write/exec libaio.so.1
DFA88000 4 4 - 4 read/write/exec libaio.so.1
DFA90000 24 24 24 - read/exec libgen.so.1
DFAA6000 4 4 - 4 read/write/exec libgen.so.1
DFAB0000 104 68 68 - read/exec libelf.so.1
DFADA000 4 4 - 4 read/write/exec libelf.so.1
DFAE0000 4 4 - 4 read/write/exec [ anon ]
DFAF0000 460 292 292 - read/exec libnsl.so.1
DFB73000 24 24 - 24 read/write/exec libnsl.so.1
DFB79000 28 12 - 12 read/write/exec libnsl.so.1
DFB90000 40 40 40 - read/exec libsocket.so.1
DFBAA000 4 4 - 4 read/write/exec libsocket.so.1
DFBB0000 4 4 4 - read/exec libdl.so.1
DFBC0000 116 116 116 - read/exec ld.so.1
DFBED000 8 8 - 8 read/write/exec ld.so.1
DFBEF000 4 4 - 4 read/write/exec ld.so.1
-------- ------ ------ ------ ------
total Kb 1058648 961832 961040 792
Re: big oracle problem please help [message #112243 is a reply to message #112099] Wed, 23 March 2005 19:32 Go to previous messageGo to next message
avdba_22
Messages: 5
Registered: March 2005
Junior Member
are you getting any error message? Do you have any free memory when you do top? How are your cpu's?
Re: big oracle problem please help [message #112248 is a reply to message #112243] Wed, 23 March 2005 20:46 Go to previous message
dong94
Messages: 2
Registered: March 2005
Junior Member
I can not find any error message in system log or oracle log.
I have two physical cup, but when use mpstat command can see four cup.
mpstat
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 159 7 515 1207 108 709 11 11 7 7 1505 8 3 6 84
1 157 7 454 1161 1 782 11 11 6 6 1854 6 34 4 56
2 151 6 389 1465 238 718 11 11 6 6 1787 8 12 5 75
3 136 6 162 1691 246 553 10 11 9 6 1247 5 13 4 77
Previous Topic: PL/SQL code is conusimg alot of shared memory
Next Topic: Server response degrades
Goto Forum:
  


Current Time: Thu Oct 01 02:54:08 CDT 2020