How will my performance benefit from:
1. increasing PCTFREE--like 50? How then to set up PCTUSED--40-default or less?
2. increasing FREELISTS to 60 and/or FREELIST GROUPS to #CPUs?
3. increasing INITRANS--60 to #of users?
4. specifying Parallel or not?
5. What other parameters could I change?
The space and memory are not a problem. 8 CPUs, 16G RAM, Ora10g--10.1.0.4--the patch doesn't seem to improve performance.
It'll be very helpfull to me if you give an example of some reasonable numbers to start testing with. Also, Oracle recommended me to use ASM. Do you think my performance will benefit from that? I could not find too many materials from people who currently use it.
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 > 0If 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.
This was first published in July 2005