Swapping problem

greenspun.com : LUSENET : DBAzine : One Thread

Hi I am handling a production oracle8.1.6 database on HP Unix..which is pretty small.(1.2 G). No major application changes were done.Initially The RAM was 512 MB and application was working fine, but with little less slow responce. Then We changed some memory parametere( Shared pool incrteased by 30 MB from 20 - 50 MB and Sort_area_Size increaed from 4 MB to 10 MB). after 2 weeks I noticed excessive swapping on the server. Then The Ram was upgaraded to 1.5 GB , and number of concurrent user connected to database is around 100. Still it is showing excessive swapping..specially when the number of concurrent sessions goes up to around 150 (Sometime) Can any one please advice me about the memory requirement per session. and I guess , sort_area_size (10 MB)..that is for each user session..is creating big problem ..by increasing PGA Requirment. Can anyone confirm this. Early reply will be higly appreciated. Thanks and Regards Satya Prakash Sharma satya_tcs@hotmail.com

-- Satya Prakash Sharma (satya_tcs@hotmail.com), March 22, 2002


hi Satya Prakash Sharma

as a matter of fact, the parameter "Sort_area_Size" is for each session. The given size of 10MB is a good deal, i would recommend you to reduce this parameter, subsequently until you get a reasonable value. Additionally, put the parameter "sort_area_retained_size = 65536" into your initora-file

In order to checkout the amount of disk-sorts by comparsion to memory- sorts, use the following statement :

select name,value from sys.v_$sysstat where name in ('sorts (memory)','sorts (disk)') order by 1 desc;

-- khanh Toan Hoang (khanh.toan.hoang@seb.de), April 12, 2002.

Moderation questions? read the FAQ