Re: Help please: Row chaining issues

Sorry I don't know of any white papers and can only speak from first hand knowledge.

Errrgh!. Why are you doing anything to tables in SYS? If they are Oracle tables DON'T TOUCH!!!!. If they are not Oracle tables get them out of SYS.

Now as to solving the problem. Chained rows are created either when the size of the row exceeds the capacity of a block or when changes are made to existing rows in tables which result in the size of the row being increased.

Look up the rules for estimating sizes, PCT_FREE, and PCT_USED in the manual. (If I remember correctly out of a 4K block you get just under 3,800 bytes available for data and/or index rows.)

The second problem results from varchar2 and, if I remember correctly, number variables being empty at time the row is created and being expanded later. At the time the row is created only one is allocated (it contains the length -- which is zero) for each empty varchar2 and number. When the row is updated and data inserted the row is expanded in place and if the row will no longer fit in the block it is split, part of it moved to a new block, and a link between the blocks created. This can be avoided through the use of one of two techniques, one of which is IMHO stupid and the other frowned upon by Oracle. The stupid solution is to plug a dummy value in each column at the time it is created. The frowned upon solution is to use CHAR instead of VARCHAR2 and make sure that all columns are defaulted to consist of as many spaces as they have characters.

regards

Jerry Gitomer

Rob Ellis wrote in message <3731A750.902C607E_at_cognos.com>...
>Hi,
>
>We have been told by Oracle that we are suffering in performance in our
>7.3.4 databases because our ratio of table fetch by rowid to table fetch
>continued row is consistently below 1000. Does anyone know of any
>'white papers' on row chaining? We have been doing export/imports of
>tables where there exist chained rows, however, we are finding that
>unless we reload every table with chained rows we still have fairly poor
>ratio numbers. Some of the tables affected are in sys so I guess we
>cannot attack those without doing a full database reload.
>
>I suspect that 'chained' rows includes both rows that have been migrated
>and rows that are chained simply because some rows are too big to fit
>into our 4k database block.
>
>Any advice on row chaining would be helpful.
>
>Thanks,
>Rob Ellis
>rob.ellis_at_cognos.com
>
Received on Thu May 06 1999 - 11:52:44 CDT