Re: Performace Degrade after upgrading to 8.1.7.4

hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0306082103.6dc266e0_at_posting.google.com>...
> 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
>

alter system always changes a running database on the fly. You do NOT need a reboot. If doing so, you may even need to set/change the events parameter in init.ora, because your on the fly event will be lost after a reboot.
The combo of PCTUSED and PCTFREE will force much more blocks on the freelist. Doing so, Oracle will search longer before it decides to allocate a new block (if Oracle can't find an appropiate block on the freelist it will allocate a new block). This might be what you want to do it: take care of a more agressive reuse of free space. In setting those percentages however you need to make sure an integer number of records will fit in your block. I would probably revert to the default PCTUSED of 40, and see what happens.
Also if you are using dictionary managed tablespaces, you will probably see many statements referring to sys.uet$ and sys.fet$. As there is only one such set of tables maintaining allocation info for all tablespaces, you may want to make sure you are using dictionary managed tablespaces.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Tue Jun 10 2003 - 09:44:01 CEST