Re: Oracle Tuning

On Jul 24, 12:30 pm, cptkirkh <kh..._at_scic.com> wrote:
> I analyzed my tables and found numerous tables with a large number of
> chained rows. I would like to repair these and was wonderign how many
> i can repoar at one time or better yet how many should i repair at one
> time. I run Oracle 10g on Windows 2003 server.

Are they chained or migrated rows? The difference is great, and the solution for one (migrated rows) won't fix the other (chained rows). Migrated rows have run out of space to expand, thus the data is 'migrated' to a new data block leaving behind a pointer to the new address at the old location. Rebuilding the table with modified PCTFREE and PCTUSED values, then reloading, fixes this issue. Not so with truly chained rows, a row which will not fit, ever, into a single block sized smaller than the total size of that row. A 4K row won't fit into a 2K or 4K block (since you don't have a full 4K to use due to the small overhead Oracle requires). The solution here is to either:

Rebuild the database with a larger db_block_size.

Build a tablespace with a larger block size; you're allowed to build a tablespace with a block size twice as large as the db_block_size setting (I believe, however, that 32k is the largest block size you can have) and move the table to this new tablespace.

My guess is you're experiencing the pain of migrated rows; a rebuild of your table or tables with modified PCTFREE and PCTUSED settings is in order. The requirement here is to effect these changes with minimal impact to your user community and adherence to the requirements set forth in your SLA.

David Fitzjarrell Received on Wed Jul 25 2007 - 08:10:34 CDT