otak-otak | favorite evening food

January 24, 2009

Insert performance oracle 10.2

Filed under: benchmark — ora62 @ 4:23 pm

Bulk load insert, also application with high insert into a transaction table could lead to some bottleneck issue:
– log file sync issue, this kind of wait event due to frequent commit

– log buffer space issue, due to lack of log_buffer memory setting – free buffer waits, due to lack of db_cache_size memory setting

– buffer busy waits, this issue related to concurrency and transactions at the same table which being inserted, also sometimes too many indexes or indexes which has high clustering factors will lead to this wait event issue.

in fact if we doing direct path inserts, some issues can be avoided, such as free buffer waits, but wait event that related to redo is intact. in 10.2 there is a feature called asynchronous commit, which can alleviate redo wait event issue, but index issue still carryon even we using direct path.

there is trick approach which depends on your application behavior to boost insert performance.

– temporary table

– asynchronous commit (but be aware of recovery and data consistency)

– direct path

Blog at WordPress.com.