Problem solve Get help with specific problems with your technologies, process and projects.

Changing parameters for better performance

I have a very intensive application--4,500 transactions per second. I would like to change the table/indexes storage in order to get better performance.

I have a very intensive application--4,500 transactions per second--huge amount of inserts and updates based on these inserts, t.e. multiple selects too. I was researching every option I have to tune up the performance, and I would like to change the table/indexes storage in order to get better performance. I have chain buffer latches, high CPU, lots of sequential reads in my TOP5 in statPack report. All SQL is very optimized, all bind variables, narrow tables but lots of records, good indexes and fresh statistics. My flow in the same tables usually is 10 inserts (in each table), 20 selects (from all of them), 10 updates (depends on the selects).

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-- 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.

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":

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.

Dig Deeper on Oracle database performance problems and tuning