Re: Performace Degrade after upgrading to 8.1.7.4

hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0306102220.58515a79_at_posting.google.com>...
> Hello ,
>
> But when I do a import , it will always make Data Block completely
> filled to 90% based on PCTFREE setting. We have very less deletions
> happening in the database. So in such case can PCTUSED give a problem
> in Free List management ? I ran utlbstat & utlestat. Is there any
> indication for the above Wait Event ?
> Correct me if I am wrong.
>
> We have seen no of connections hitting database on a higher side than
> previous one. So CPU is almost busy during load times & 90% processes
> in Run Mode (TOP O/p). sar -q also shows run queue occupied for almost
> 100% Can it be the issue ? Will adding a CPU benefit ?
>
> Regards,
> Mahesh Hardikar

Based on your observations you *must* have excessive freelist management (which will take place in memory, so makes the server CPU-bound), as only very few deletions will cause the block to fall below 85 percent, and have the block placed on the freelist. It usually shows up in utlestat as 'buffer busy waits' and there is also a break down for which type of Oracle block the system is waiting. A CPU constantly on 100 percent is BAD, no discussion about that. I would however rather fight the root cause (AND change ALL your pctused settings to 40), instead of taking the usual panacea measure, which is adding a cpu. It won't work. If the application is really the culprit (which is likely) the extra cpu will be immediately saturated also.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Wed Jun 11 2003 - 13:06:48 CEST