Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DATABASE TUNING


From: Stephane Faroult <>
Date: Mon, 18 Sep 2000 12:25:27 +0200
Message-Id: <>

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.


  Stephane Faroult
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------, 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

Original text of this message