What are the reasons that lead to terrible high Paging faults.
The following is the topas information and background about the Environment.
OS: AIX 6.1 Softwares: Oracle 11gR2 Paging Space:30720 MB , %Used 0, %Free 100 Memory Real,MB 31616 ; %Comp 58; %Noncomp 33; %Client 33; Network KBPS I-Pack O-Pack KB-In KB-Out Total 592.0 1837.0 1595.0 289.5 302.5 Disk Busy% KBPS TPS KB-Read KB-Write Total 23.0 684.0 122.0 0.0 684.0
CPU is always below 50% loading.The problem occurs where the PAGING Faults goes up to 32362K
Steals PgspIn PgspOut PageIn PageOut Sios 0 0 0 0 141 139 EVENTS/QUEUES Cswitch 4094 Syscall 47816 Reads 4585 Writes 1732 Forks 16 Execs 9 Runqueue 2.5 Waitqueue 0.0
The application has a heavy writing and updating job for some table A. Some records on the table A will be deleted on a scheduler. Delete operations will be executed on each hour abour 5~10 times. Insert operations happen almost 70~100 times/second.
The number of records on table is near 13 million, which have 3 indicies.(almost ~2G) Updating usually happens 5~10 times /min.
VMM settings follows the AIX 6.0 default setting.Use 4k and 64K page size.It seems that use no record swap out to the paging space.
VMM memory load control whose level is the 188.8.131.52.
memory_max_target big integer 25152M . Oracle database memory use automatical management but the Parse time CPU to parse time elapsed = 0.07921 the db block changes has high value.
Can anyone can give some suggestions / hints to help me?
I need some experienced DBA to help me.
In addition, the application uses the connection-pool to keep near 350 connections. (proxool & c3p0 )