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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: