Re: ASSM and high volume concurrent inserts

Responding to the question "does anyone think dictionary managed tablespaces 
are ever a good choice anymore"...

The one scenario where I've found DMT to be preferable to LMT is where the 
underlying storage is migrateable or otherwise has some initialization latency 
for infrequent I/O requests. Mainframes have long used hierarchical 
file-systems, where a seldom-accessed file is transparently moved or "migrated" 
to offline storage, requiring a "recall" to primary storage when the 
left-behind file-header is accessed. the problem that LMT tablespaces have is 
that every query on the DBA_EXTENTS and DBA_FREE_SPACE views requires access to 
the bitmapped extent-map blocks residing right in the datafile. So, in an 
environment that is possibly likely to grow onto such storage, now or in 
future, it is worth considering retaining the ability to create DMTs by 
creating the database with the SYSTEM tablespace DMT. Use LMT tablespaces in 
most situations, but convert to DMT (using 
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL) prior to moving a tablespace to 
migrateable storage.



-----Original Message-----
From: Powell, Mark [mailto:mark.powell2@xxxxxx]
Sent: Friday, December 18, 2009 09:06 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: RE: ASSM and high volume concurrent inserts

>> does anyone think dictionary managed tablespaces are ever a good >> choice 
>> any more? If you do, please let me know when and why << Yes, in test where 
>> we have limited space because you cannot limit the table/index size via 
>> maxextents under a locally managed tablespace. I still think that a DBA 
>> should be able to assign a maximum size to an object because for most of our 
>> objects there is a size where it the objects extends beyound this value 
>> something is wrong. That and the fact I have seen an infinite insert loop 
>> make it to production where the key was sequence generated. All you need is 
>> an unlimited sized table assigned to a tablespace with extendable files to 
>> fill the disk and ruin you weekend that you now have to spend trying to 
>> release storage. I have seen several posts where sites failed to use maximum 
>> sizes on their data files that would have usually prevented this issue, but 
>> being able to limit object sizes also has benefits. -----Original 
>> Message----- From: oracle-l-bounce@xxxxxxxxxxxxx 
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham Sent: 
>> Thursday, December 17, 2009 7:21 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 ] 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 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 -- http://www.freelists.org/webpage/oracle-l -- 
>> http://www.freelists.org/webpage/oracle-l -- 
>> http://www.freelists.org/webpage/oracle-l -- 
>> http://www.freelists.org/webpage/oracle-l

Other related posts: