Consider using the APT script enough_rollback_segs.sql to check how many rollback segments you should have. A good rule of thumb for intensive concurrent DML is that you should have as many rollback segments as concurrent transactions. However, the peak number shown by that script may not relate to the period during which your loads were running. You might have had higher concurrency during the day.
Because the physical location of index inserts is constrained by the key value, increasing the number of freelists will not help. In general, there is no point in having multiple freelists on an index. The degree of the problem does not seem to be cause for much concern, however, if you want to do something about it then I would recommend partitioning.
I was looking through your book attempting to determine where some things might benefit from tuning. These output below is from daily loads into our data warehouse. Essentially these processes are running in the middle of the night and there are not other users logged in performing queries. I am also aware that many of the scripts will use parallel processes. Obviously there are some issues with data block accessibility, in particular the rollback segments. I figure that is a matter of adding more. The other tablespaces listed, in particular STAGING_INDEX hold indexes for staging tables which are a temporary holding area before the data is extracted, aggregated and then inserted into fact and dimension tables. The block size for the warehouse is 16k and I am aware that most of the staging tables are truncated before being reloaded. In a shotgun approach, I bumped up the freelist values for most of these indexes from 2 to 4. The pctfree is the default value of 10 for all of them. It seems that pctfree would have more of an impact on the data segments rather than the index, however, since these tables are reloaded each night, perhaps setting the pctfree on the indexes to be lower can buy back some of the contention that I am seeing? Considering we are not really updating an existing data on these tables. I am also wondering about increasing initrans as well.
Okay, that is it for now, I just found that there is one table that has eleven indexes on it! If it is not already obvious, I have inherited this system and do not have a wealth of experience dealing with parallel processes. If there is something simple that I am overlooking I would welcome you pointing me in the right direction.
REPORT DATE: 09-06-2000 BLOCK_CLASS TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- data block 12667 0 segment header 14 0 undo header 2911 0 undo block 25 0 TABLESPACE_NAME TOTAL_WAITS TIME_WAITED ------------------------------ ----------- ----------- INDEX_DM_LARGE 1 0 RBS 2936 0 STAGING 8 0 STAGING_INDEX 12659 0