RE: ASSM and high volume concurrent inserts

From: Kellyn Pedersen <>
Date: Thu, 17 Dec 2009 15:52:54 -0800 (PST)
Message-ID: <>

Nope, you can do this in 10g and I have done this for specific reasons, the exact ones you mention, but ensure that it's freelists that will solve your problem and not something else that's causing it.

ASSM has been tested and documented in some instances to not manage as well as manual freelists/freelists group management will.

To do this, create a tablespace with manual space segment management:

CREATE TABLESPACE <tablespace name> datafile <datatfile> size (you know the drill) extent management local
segment space management manual;

Once created, move the object that you wish to manage the freelists manually to this tablespace and then alter the table specify what you want on the freelists storage.

ALTER TABLE <table_name> move tablespace <new tablespace_name>;

(Rebuild any indexes that have become unusable, but if you can't take a maintenance outage, consider a table redef instead...)

Make the changes to the storage for freelists/freelist groups, ensure you match the indexes appropriately.

I would still recommend verifying this is the problem with this table, (I'd attach the script, but Yahoo mail has *issues* today, so I pasted it below...) Thanks and good luck!

select * from
( select
      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)
   AS "Object",

sum(case when
   a.statistic_name = 'ITL waits'

   a.value else null end) "ITL Waits",

sum(case when
   a.statistic_name = 'buffer busy waits'
   a.value else null end) "Buffer Busy Waits",
sum(case when
   a.statistic_name = 'row lock waits'

   a.value else null end) "Row Lock Waits",
sum(case when
   a.statistic_name = 'physical reads'

   a.value else null end) "Physical Reads",
sum(case when
   a.statistic_name = 'logical reads'

   a.value else null end) "Logical Reads"
   v$segment_statistics a

  -- a.owner like upper('&owner')

group by
   rollup(a.object_name)) b

where (b."ITL Waits">0 or b."Buffer Busy Waits">0) /

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.

"Go away before I replace you with a very small and efficient shell script..."

  • On Thu, 12/17/09, FMHabash <> wrote:

From: FMHabash <>
Subject: RE: ASSM and high volume concurrent inserts To:, Date: Thursday, December 17, 2009, 4:14 PM

Not sure what you mean by 'manually managed'. To manage FLists yourself, you need a DMT TS, which I believe can't do in 10g (for read/write). We had a similar problem on a high frequency LOB insert, which we resolved by eliminating a composite unique constraint. About 90% of BBW's that I've seen resulted from poor application design or run-time anomalies. Focus on application design and run-time behavior before you start thinking tablespace and segment attributes.

-----Original Message-----
From: Haroon A. Qureshi <> Sent: Thursday, December 17, 2009 12:46 PM To:
Subject: ASSM and high volume concurrent inserts


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,



Received on Thu Dec 17 2009 - 17:52:54 CST

Original text of this message