Re: Automatic Segment Space Management
Date: Fri, 5 Mar 2010 04:19:16 -0800 (PST)
Message-ID: <88174435-635d-46b8-8452-bab6e63f4e0e_at_v20g2000prb.googlegroups.com>
On PCTFREE parameter: I think it is better to avoid chained rows and not only for performance reasons:
- There are some bugs related to chained rows/row migration. For
example 9.2.0.8 has a bug that may cause UPDATE statement to fail if this UPDATE leads to row migration.
- Once a block becomes full you may also have problem when several
transactions attempt to update this block simultaneously as by default INITRANS = 1. I suggest analyzing all large tables looking whether significant UPDATE activity leading to row expansion is possible. For example many historic tables would contain following columns: START_DATE
COMPLETION_DATE
COMPLETION_CODE
ERROR_DESCRIPTION Only START_DATE gets populated during INSERT, other columns are populated through UPDATE. This leads to row expansion and chained rows.
The most extreme example I've seen is following: a table contained only two columns, USER_ID and PASSWORD_HISTORY. PASSWORD_HISTORY column was VARCHAR(4000) and contained last 20 or so passwords in encrypted format (string 30 characters long) separated by dot. When the system went live each user had only one password in the PASSWORD_HISTORY, as passwords expired 30 days after creation they were recorder in the PASSWORD_HISTORY column. So rows expanded and expanded again and it reached a point where UPDATE based on unique index would run for very long time - longer than Apps Server timeout. So users couldn't reset their passwords and couldn't login to the system. We rebuilt the table with plenty of free space per block and it has been stable since then.
On ASSM: I know one reason to use ASSM and one reason not to use it:
- Use ASSM as it allows shrinking of segments in 10g. This is very
useful feature as shrinking is done online and no additional space is
required when it is running (not like index rebuild/table move).
However shringing is approx 10 times slower than ALTER TABLE MOVE/
ALTER INDEX REBUILD.
- Don't use ASSM: If you have large uncomitted DELETE in ASSM
tablespace then single-row inserts are very slow. Typical scenario:
something goes wrong during purge, for example Apps Support may decide
to change retention period from 90 days to 30 days, this results in
enormous DELETE, UNDO runs out of space, DELETE fails and being rolled
back. While this is happening the application is almost dead as singlerow
inserts run for several seconds instead of milliseconds. We have
many systems that keep data somewhere between 2 and 100 days in nonpartitioned
tables, the data gets purged nightly, this purge is often
single biggest source of instability.
Received on Fri Mar 05 2010 - 06:19:16 CST