RE: ASSM and high volume concurrent inserts
- From: FMHabash <fmhabash@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>, <fmhabash@xxxxxxxxx>, <haroon_a_qureshi@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 22 Dec 2009 14:28:30 -0500
Clarification accepted. I tested it and it works as you described.
Thanks
-----Original Message-----
From: Mark W. Farnham <mwf@xxxxxxxx>
Sent: Thursday, December 17, 2009 7:20 PM
To: fmhabash@xxxxxxxxx; haroon_a_qureshi@xxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
Subject: RE: ASSM and high volume concurrent inserts
By DMT do you mean dictionary managed? That is certainly not required to use
"manual" aka freelists segment space management. A locally managed
tablespace can be ASSM or freelists in all versions I'm aware of.
extent management dictionary
extent management local autoallocate
extent management local uniform [SIZE <size_clause>]
are the extent management possibilities
and
segment space management auto
segment space management manual
are the segment space management possibilities. If you specify extent
management dictionary, you get segment space management manual. (And does
anyone think dictionary managed tablespaces are ever a good choice any more?
IF you do, please let me know when and why - and avoiding a full export
import to get rid of dictionary management of an existing SYSTEM tablespace
I don't really count as a "choice.")
You're probably confused by the statement "The segment_management_clause is
relevant only for permanent, locally managed tablespaces" in the manual.
That is true, but only because ASSM is not available for dictionary managed
tablespaces.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of FMHabash
Sent: Thursday, December 17, 2009 6:15 PM
To: haroon_a_qureshi@xxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
Subject: RE: ASSM and high volume concurrent inserts
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 <haroon_a_qureshi@xxxxxxxxx>
Sent: Thursday, December 17, 2009 12:46 PM
To: Oracle-L@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
