Re: Performace Degrade after upgrading to 8.1.7.4

hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0306042008.5d927466_at_posting.google.com>...
> Hi ,
>
>
> We had Oracle 8.1.7.0 running on HP-UX 11.0 connected to FC60 Disk
> Array which has 256 MB controller cache. On that system , we were
> experiencing I/O issues (CPU spending more time in WaitI/o).So we
> decided to shift to EMC2 having 1GB controller cache & faster disks.
> We connected another machine of same configuration (except lesser
> memory . New system has 3 GB RAM instead of 3.5 GB ).
> We installed 8.1.7.0 on new machine.
> Applied patch for 8.1.7.4
> Created tablespaces as LMT's with Uniform Extent Size.Previously we
> had Dictionary Managed Tablespaces.
> Created users & tables with PCTFREE=10 PCTUSED=85
> Exported old instance & imported it to new one with "ignore=y". We did
> it per user basis.
> WIth this , we were successful in removing fragmentation for tables.
>
> All this was done on May 25. Since then we are experiencing
> performance degradation. This occurs especially for Write operations.
> We are even getting slower response than previous system which is
> never expected. When we looked at SAR o/p , Wait I/o was almost
> eliminated. But CPU tends to be busy during peak times with more than
> 30% of time in SYS activities as opposed to 20% on old server. Also
> Load on Machine has dramatically increased to 7-8 against 3-4 (as seen
> in TOP o/p). This is Production Database.
> Users are getting mad over this & so are we.
>
> I saw Waitss on 'db file scattered & sequential read' in
> v$seesion_wait constituting almost 90%
>
> Kindly provide suggestions as early as possible .....
>
> Regards,
> Mahesh Hardikar

the following statement will have all your sessions dump wait info in trace files
alter system set events '10046 trace name context forever, level 8' to stop it us
alter system set events '10046 trace name context off' It will show you exactly on which file and block the wait occurs. My guess though is the pctfree and pctused combo result in excessive free list management.

Regards
Sybrand Bakker
Senior Oracle DBA Received on Thu Jun 05 2003 - 13:13:51 CEST