What are
|
Block | Description |
---|---|
segment header | The problem is probably a freelist contention. Use freelists or increase the amount of freelists. Use freelist groups (this may have markable effects even in single instances). |
data block | Increasing the size of the
db_buffer_cache can help to reduce these waits; but this can
also point to freelist contention:Change PCTFREE and/or
PCTUSED : Check, whether there are indices where many
processes insert into the same point. Increase
INITRANS . Define less lines per
block.To find out what tables may need changes to PCT_FREE and/or
PCT_USED , turn the MK_FLC option on in your
config file and then refer to the FreeList
Contention block of the report. |
undo header | If you don't use Undo TableSpaces, you probably have too few rollback segments. In this case, add more rollback segments and/or increase the number of transactions per rollback segment. |
undo block | This may also point to to few rollback segments (see "undo header" above), but as well to their size: you may want to increase the size of the rollback segments. For "Parallel Server", you can also define more PCM locks in <Parameter:GC_ROLLBACK_LOCKS> |
free list | Add more freelists or increase the number of free lists. For "Parallel Server", make sure that each instance has its own freelist group(s). |
Beside tuning the FREELISTS
and FREELIST GROUPS
parameter for each single object (which is a bunch of work and, at least for
FREELIST GROUPS
, requires a complete rebuild of the affected
table), there's something new with Oracle 9i: If you use locally managed
tablespaces, you may benefit from a new feature called ASSM, Automatic
Segment Space Management. This is defined at tablespace level on creation of
the same - which means, you cannot change it for existing tablespaces but
must create a new tablespace and move all objects there:
CREATE TABLESPACE index01
DATAFILE '/disk1/oraData/index01.dbf' SIZE 750M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO |
To ease the move of all objects, there are some scripts available for
download at the IzzySoft
website: look for the DBAHelpers archive there. Create a new tablespace as
described above, then use the tabmove.sh
and/or
idxmove.sh
script from the DBAHelper archive to move all objects
to this tablespace. When those scripts successfully finished their work,
check for objects that may have remained in the original tablespace:
SELECT *
FROM dba_segments WHERE tablespace_name='orig_tablespace_name' |
Of course you have to replace the string "orig_tablespace_name" with the
name of your original tablespace (uppercase!) in the above statement. If no
objects remained, use the DROP TABLESPACE orig_tablespace_name
statement to remove the original tablespace, delete the datafile that
belonged to it, and then create the tablespace anew with ASSM (see above).
If there are any objects left in the original tablespace, check the logs
of tabmove.sh
and/or idxmove.sh
for the reason:
may be you have some objects that are not supported by ASSM - LOBs could be
one example, see next paragraph.
One side effect to consider is, that at least with Oracle 9i LOB objects are not supported with ASSM. So if you have LOB objects and want to use ASSM, you must create a separate tablespace for the LOB objects that does not use ASSM. For index tablespaces this shouldn't be a restriction, since one normally should not have LOB objects there.