|
If you're not using ASSM, Automatic Segment Space Management, with
locally managed tablespaces, I'd recommend that you do so. ASSM
automates space management and eliminates the need to specify PCTUSED,
FREELISTS and FREELIST GROUPS. You can still specify PCTFREE and
INITRANS.
To help determine the correct value for PCTFREE, check to see if you
have extensive row chaining--if you do, you should increase the
parameter. Even if disk space isn't an issue, you don't want to
increate PCTFREE so much that you end up with a lot of empty space in
blocks; this will cause some operations to have to access more blocks
than they would otherwise; that will increase your logical I/O count
and therefore your latching, which is already a problem.
As for INITRANS, that is set per table, and should reflect the number
of sessions that might be performing DML concurrently in the same
block. Therefore, the number of concurrent sessions, the database
block size, and the number of blocks in a given table would be the
starting points for determining the appropriate value; fortunately,
though, you can just query V$SEGMENT_STATISTICS view for "ITL waits":
SELECT OWNER, OBJECT_NAME, VALUE
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'ITL wait'
AND VALUE > 0
If they're significant, increase INITRANS for that table.
Finally, you mention ASM, which is Automatic Storage Management,
Oracle's new built-in logical volume manager. I doubt that using ASM
will address your database's current performance.
|
