Initializing Table Space - Force default column widths 4

[speial]

I have a table that contains several columns defined between VARCHAR2(100) to VARCHAR@(254). As the table gets updates, ORACLE begins to chain the rows.

My understanding is the chaining occurrs becasue when the table was created, ORACLE initialized the VARCHAR2 columns with 'one' character for a "place holder" so to speak.

When the table is updated, actual data pushes the rows outside the initial block space, forcing ORACLE to begin chaining the rows.

My observation indicates that most of the VARCHAR2 columns defined over 100 characters, will actually be populated with text taking about 50% of the defined space. ( a VARCHAR2(100) will, on the average, contain at least 50 characters).

To avoid chaining (and serious performance hits) I am trying to force ORACLE to create the table space (and setting up the block space) expecting the identified columns to contain, 50% data, rather than using one character.


If ORACLE 8, 8i, 9i, permits this 'forcing', how is this performed ?

What other tuning possibilites should I consider ?

 

[avjoshi]

If you expect more and frequent updates than inserts into your table then you may want to consider storing less number of rows per block. That will leave you enough room for updates.
To do that you will have to reduce the PCTUSED (percent of block space utilized while writing data) storage clause.

Anand

 

[carp]

Actually, I believe you will want to increase PCTFREE. This will reserve more space in each data block to accomodate data expansion.
Also, this is the first time I've heard of Oracle putting a phantom character into a VARCHAR2 field. Where did you hear about this? A NULL VARCHAR2 has NO characters - it contains no value.
Decreasing PCTUSED will potentially leave data blocks off of the free list longer, but will not necessarily result in fewer rows per block.

Elbert, CO
1338 MST

 

[SantaMufasa]

Speial,

I would like to add some additional background regarding your objective to avoid row chaining.

To avoid row chaining, I recommend the following steps:

1) Confirm the size of your database data blocks (usually 8,192 or 16,384).

2) Determine how many FULL-GROWN rows (i.e., rows which you have updated to their typical mature size) can fit within a data block. (For the sake of discussion, let's say that 10 typical, full-grown rows will fit in a [8,192-byte] block.)

3) Approximate the total size of those 10 rows when they are immature, newly inserted rows. (For the sake of discussion, let's say that typically, 10 immature rows occupy approximately 2,000 bytes. This means that you do not want new rows inserted into this block once the block reaches 2,000 bytes because we know that expansion of these rows will occupy the other 6,000 bytes. So, we must tell Oracle to stop INSERTing new rows into this block when the block reaches 2K/8K full, i.e, 25% full. This means we must specify to Oracle to leave 75% of blocks free for expansion.)

4) Specify to Oracle how much free space to reserve for row expansion. In the example we are using, we could implement this directive with this syntax:
"ALTER TABLE my_table STORAGE (PCTFREE 75);"
This specification directs Oracle to stop inserting rows into any block of this table when the block's consumption reaches 25% (leaving 75% free for expansion). Oracle's default for PCTFREE is 10%...which explains why you have so much row chaining going on...Oracle is allowing only 10% growth on your rows, then chaining when it runs out of growth space.

5) You may also specify the point below which your blocks will again accept newly INSERTed rows. If, via row DELETEs or "row-slimming" UPDATEs, space consumption drops, you can specify the point to again allow INSERTs. If we want to again allow INSERTs when block consumption drops below 15%, we could say:
"ALTER TABLE my_table STORAGE (PCTUSED 15);"
Oracle's default for PCTUSED is 40%. In our case, however, if PCTFREE is 75, the maximum size for PCTUSED is 25, since the total of the two values cannot exceed 100.

So, in Anand's fine suggestion to reduce PCTUSED, (to not allow INSERTs to begin again until block space falls below the new PCTUSED), that is only the less-important half of the story. You must first increase your PCTFREE from the default 10% to a much larger value so that Oracle will stop INSERTing rows earlier, thus leaving more growth space in your blocks.

You can eliminate your existing row chaining by first doing the appropriate "ALTER TABLE..." commands to assign more appropriate PCTFREE and PCTUSED values, then issue the following type of command:

"ALTER TABLE my_table MOVE PARALLEL NOLOGGING;"

This command executes remarkably quickly since the table rebuild uses parallel processors (if available) and does not waste time creating unnecessary redo log file entries.

Please post your findings, or if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:45 (21Jan04) GMT, 13:45 (21Jan04) Mountain Time)

 

[BJCooperIT]

Dave, as always you are a fountain of knowledge! Have a star on me.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

Carp,

Sorry, Carp, I didn't see your response since I was composing my earlier response while you were posting yours.

If memory serves, typically every "cell" on a row has, as its first byte (or bytes for cells > 255 characters), a length indicator which specifies the number of bytes of actual data for the ensuing cell. When a cell is null, then Oracle stores a binary zero in the length indicator for that cell. But when cells at the end of a row are null, then Oracle does not even store the one-byte length indicator; it handles the trailing null cells by reducing the "number or columns for this row" indicator in the row header.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:52 (21Jan04) GMT, 13:52 (21Jan04) Mountain Time)

 

[carp]

Dave -
Concur. But the length indicator is not a one-character place-holder, is it? As you say - it represents a number.
Perhaps speial is referring to the length indicator and I misinterpreted what was being said.

 

[sbix]

Dave (Muf) from Oracle 9.2 you can also specify several block sizes, so if speial DB's is in that rev. or superior he could also to create a special tablespace with a large block size in order to reduce block migration or block chaining