Re: Oracle on Sun Solrais Box is DEAD SLOW!!!!!!!!

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 4 Sep 2003 06:59:07 -0700
Message-ID: <2687bb95.0309040559.6b3a5f62_at_posting.google.com>

spraveen2001_at_yahoo.com (Praveen) wrote in message news:<98d8ec76.0309040247.14124473_at_posting.google.com>...
> Hi All,
>
> I'm using 8.1.7 on Sun Solaris machine. I have 2 databases in this
> machine. around 20 peope will access the db daily for development
> purpose. But, day by day the system getting dead slow and unable to
> connect also. I got the following report after checking in the
> machine.
>
> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
> 7445 oracle 191M 155M sleep 59 0 0:00:01 0.8% oracle/11
> 7451 oracle 189M 155M sleep 60 0 0:00:00 0.4% oracle/1
> 7425 kiran 5456K 224K cpu0 59 0 0:00:00 0.2% prstat/1
> 350 oracle 10M 160K sleep 59 0 0:00:43 0.1% tnslsnr/1
> 317 oracle 298M 261M sleep 59 0 0:00:06 0.1% oracle/1
> 285 oracle 190M 154M sleep 59 0 0:00:11 0.1% oracle/1
> 323 oracle 298M 261M sleep 59 0 0:00:22 0.1% oracle/11
> 291 oracle 191M 154M sleep 59 0 0:00:21 0.1% oracle/12
> 7449 oracle 189M 155M sleep 59 0 0:00:00 0.0% oracle/1
> 321 oracle 299M 261M sleep 59 0 0:00:06 0.0% oracle/11
> 395 root 4320K 120K sleep 59 0 0:00:09 0.0% sendmail/1
> 403 root 2376K 400K sleep 59 0 0:00:12 0.0% mibiisa/7
> 6693 oracle 189M 155M sleep 59 0 0:00:04 0.0% oracle/1
> 299 oracle 189M 155M sleep 59 0 0:00:05 0.0% oracle/1
> 216 root 2896K 168K sleep 59 0 0:00:06 0.0% nscd/20
> 7439 oracle 189M 155M sleep 59 0 0:00:00 0.0% oracle/1
> 331 oracle 297M 262M sleep 59 0 0:00:06 0.0% oracle/1
> 297 oracle 189M 155M sleep 59 0 0:00:06 0.0% oracle/1
> 7393 oracle 189M 155M sleep 59 0 0:00:00 0.0% oracle/1
> 390 root 1784K 80K sleep 59 0 0:00:00 0.0% ttymon/1
> 387 root 1784K 0K sleep 59 0 0:00:00 0.0% ttymon/1
> 348 root 4960K 0K sleep 59 0 0:00:00 0.0% dtlogin/1
> 7387 oracle 189M 155M sleep 59 0 0:00:00 0.0% oracle/1
> 394 smmsp 4280K 112K sleep 59 0 0:00:00 0.0% sendmail/1
> 335 oracle 297M 262M sleep 59 0 0:00:07 0.0% oracle/1
> 333 oracle 297M 262M sleep 59 0 0:00:05 0.0% oracle/1
> 329 oracle 297M 262M sleep 59 0 0:00:07 0.0% oracle/1
> 327 oracle 297M 262M sleep 59 0 0:00:00 0.0% oracle/1
> 325 oracle 297M 262M sleep 59 0 0:00:02 0.0% oracle/1
> 319 oracle 299M 261M sleep 59 0 0:00:06 0.0% oracle/14
> 307 oracle 189M 154M sleep 59 0 0:00:01 0.0% oracle/1
> 305 oracle 189M 154M sleep 59 0 0:00:01 0.0% oracle/1
> 303 oracle 189M 155M sleep 59 0 0:00:05 0.0% oracle/1
 >>snip<<
> 178 root 2184K 0K sleep 59 0 0:00:00 0.0% lockd/2
> 224 root 1408K 0K sleep 59 0 0:00:00 0.0% powerd/3
> 197 root 3312K 120K sleep 59 0 0:00:00 0.0% syslogd/13
> 162 root 2456K 128K sleep 59 0 0:00:00 0.0% inetd/1
> 139 root 2144K 128K sleep 59 0 0:00:00 0.0% rpcbind/1
> 186 root 3680K 96K sleep 59 0 0:00:01 0.0% automountd/2
> 176 daemon 2464K 0K sleep 59 0 0:00:00 0.0% statd/1
>
> If we observer, the oracle is consuming so much memory. Can anyone
> please tell what is happening?
>
> Thanks in Advance,
> Praveen

Praveen, UNIX utilities generally count the shared memory more than once making the figures you posted less than useful.

Some useful information might be

1- How long has the databases in question been in use on this machine?
2- How often are they bounced?  How long since the last bounce?
3- Page and swap information from SAR
4- IO information from VMSTAT
5- Did you look at TOP to see what processes where consuming most

resources?
6- Are the alert logs clean (no error or problem messages) when the problem occurs. Did you check for core dumps or trace files being produced at the time of the problem start? 7- Does the problem appear to come and go? This could indicate a specific job running at the time is consuming too many resources and needs tuning.
8- What is the otimizer mode? And when where statistics last updated? 9- Were any new applications added to the system about the time the problem first started to occur? This could point to reaching a machine load limit, resouce contention between the new application and existing applications, or a need to adjust the db parameters to better reflect the new load, etc...

HTH -- Mark D Powell -- Received on Thu Sep 04 2003 - 15:59:07 CEST