RE: ASSM and high volume concurrent inserts

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 17 Dec 2009 13:11:25 -0500
Message-ID: <DF9723DA1FB3454B85E3074BF9536E25_at_rsiz.com>

ASSM is actually designed to help with high volume concurrent inserts.

First things first: Are the buffer busy waits on the table or on associated indexes, or both, and how much of each?

If NOT on the table, then focus on the indexes (and especially whether some can be dispensed with for the duration of the load.)

How many parallel jobs are doing the inserts? If you cut it in half does the rate of insert rise or fall? Can you find a sweet spot below the current number of running jobs that shoves rows in at the maximum rate but the minimum amount of conflict?

Do you have partitioning available? If you have it and it is not already being used for a useful partition strategy, you can insert directly into separate partitions from multiple insert jobs to split out the load.

If you can't partition and you can't reduce the indexes, see whether you can group the data being slammed in is friendly to the index growth.

Of course if you can use some sort of direct loading method, that is best.

How often do you load 200 million plus rows? Can you simply load them direct into a fresh paritition and swap it in? (See Gorman, scaling to infinity for a clear explanation.)

And do check that there are not bugs relevant to your release and version and patches, etc.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Haroon A. Qureshi
Sent: Thursday, December 17, 2009 12:47 PM To: Oracle-L_at_freelists.org
Subject: ASSM and high volume concurrent inserts

Hello,

A client is running an application that does high volume concurrent inserts
(> 200million rows). Performance degrades with high buffer busy waits. The
tablespace is ASSM managed, so we can't change the freelists to tune it. I want to try moving to a manually managed tablespace and tune the freelists. But not sure if the client will go for that change, given the effort and timelines. Has anyone come across performance issues with ASSM and high volume inserts? Anyway of tuning it with ASSM?

Thanks in advance,
Haroon

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 17 2009 - 12:11:25 CST