Re: Performace Degrade after upgrading to 8.1.7.4

Hello ,

Thanks for the suggestions.

Now we have reverted to old machine . But we disconnected it from FC60 & connected to EMC2. This way now we r sure , OS & related patches are at the same level. We reinstalled Oracle but did not apply the patch for 8.1.7.4. Imported the schemas. PCTFREE & PCTUSED are 10 & 85 resp. So now Oracle is also at the same level. Only hardware is changed & storage from Oracle.

I will try to set those traces. Does it need any reboot? Is the combination of PCTFREE & PCTUSED causing this ? How is it affecting FREELIST ? Can you please elaborate ? If this is so , what is the alternative to reduce this ?

Thanks & Regards,
Mahesh Hardikar

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0306050313.5343f6e6_at_posting.google.com>...
> 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 Mon Jun 09 2003 - 07:03:52 CEST