As always, this kind of information depends on many factors. Your having
a not null chained fetch ratio may mean that you have a lot of 'long'
(not in the datatype acceptance - VARCHAR2(4000) belongs to the
category) columns, especially if you have settled for a medium to small
data block size (Oracle defaults are smaller than they should be). If
the average row size is bigger than what you can reasonably squeeze into
a datablock, there is not much you can do short of rebuilding your
database with a bigger block size, which is not worth the trouble if
only Toad complains ans if users are satisfied with performance. Now,
you may indeed have 'truly chained' tables - this may occur if you
insert rows with most columns set to NULL, and if the columns are filled
up later. Basically, you should adjust PCTFREE so that the number of
rows you insert is not bigger than the number which fits in the block
once all the updates have been performed and the rows have reached their
final size. It is not that easy to fix, because if you alter your table
to set PCTFREE to a higher value, this will work for future inserts
only. The best thing to do would probably be to let PCTFREE unchanged or
possibly lower it, export your table(s), reimport - this will fix
everything for 'old' data - THEN change PCTFREE to the value computed as
adequate (something higher than the default 10) - and it should answer
the question of 'future' data.
Parse/execute ratio is another problem, which may come from having a lot
of 'hard-coded' statemenst - since you are on 8.1.6 you should activate
this new feature which replaces hard-coded values with bind variables on
the fly - or your shared pool is too small. Check the relevant V$ views
(typically V$LIBRARYCACHE and V$ROWCACHE) to see whether you have a lot
of reloads. Pinning statements/packages etc. in memory (the
DBMS_SHARED_POOL package) may also be a good idea.
--
HTH,
Stephane Faroult
email: sfaroult_at_oriolecorp.com
Oriole Corporation
Voice: +44 (0) 7050-696-269
Fax: +44 (0) 7050-696-449
Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------
>
> I get the following information from Toad.
>
> Chained fetch ratio: 0.0464 (PCTFREE TOO LOW for a table)
> parse/execute ratio 77.8308 (high parse to execute ratio)
>
>
> to increase the performance and to avoid the warning : PCTFREE TOO LOW for a table &high parse to execute ratio
> What should be the optimum values ?
> What are parameters related to these values ?
>
> Environment:
> ==========
> ORACLE 8.1.6
Received on Mon Sep 18 2000 - 05:25:27 CDT
