Re: Tuning utility?

Hello Judith,

There are two scripts on my web site that might help you.

The first, called row_migration.sql, reports the degree of row migration as a percentage of the number of rows, for each table with migrated rows. It ignores tables with LONGs or possible row lengths greater than the block size, because it is not easy to distinguish row migration from chaining for such tables. A suggested PCTFREE is calculated as the free space left by one less than the number of average rows that can fit in a block. A new PCTUSED is suggested to allow for a little more than one row between PCTFREE and PCTUSED.

The second, called sparse_tables.sql, reports the data density for sparse tables as a percentage of the number of rows that could fit below the high-water mark. A new PCTFREE of 1 is recommended, on the assumption that there is no risk of row migration for such tables. A new PCTUSED is suggested as before.

Warnings: Both these scripts use the statistics recorded in the data dictionary, so you need to have analyzed everything recently if the results are to be trusted. Also, they are based on the assumption of a pseudo-random pattern of insert/update/delete. There are some tables that need more generous values for PCTFREE and PCTUSED, because they have an unusual DML pattern. Nevertheless, you can certainly take this as a better starting point than the defaults of 10 and 40!

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.net.au/

On Fri, 18 Feb 2000 14:04:32 +0100, Judith Fehrenbacher <fehrenbacher_at_i-core.de> wrote:

>We're using Oracle 8.0.5 on HP UX 11.0.
>I' m looking for a tuning utility which helps me detecting blockchaining
>and fragmentation, assisting in setting the correct initialization
>parameters, suggesting values concerning PCTFREE/PCTUSED values and so
>on.
>I'm not interested in analyzing and tuning SQL statements.
>Is it worth investing quite a lot to buy the Performance Pack?
>What about Toad?
>Anybody evaluated Reorg from Trivadis?
>
>
>Thanks,
>
>Judith Fehrenbacher
>
Received on Fri Feb 18 2000 - 21:55:34 CST