RE: ASSM and high volume concurrent inserts
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: haroon_a_qureshi@xxxxxxxxx, Oracle-L@xxxxxxxxxxxxx, fmhabash@xxxxxxxxx
- Date: Thu, 17 Dec 2009 15:52:54 -0800 (PST)
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
DECODE
(GROUPING(a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
sum(case when
a.statistic_name = 'ITL waits'
then
a.value else null end) "ITL Waits",
sum(case when
a.statistic_name = 'buffer busy waits'
then
a.value else null end) "Buffer Busy Waits",
sum(case when
a.statistic_name = 'row lock waits'
then
a.value else null end) "Row Lock Waits",
sum(case when
a.statistic_name = 'physical reads'
then
a.value else null end) "Physical Reads",
sum(case when
a.statistic_name = 'logical reads'
then
a.value else null end) "Logical Reads"
from
v$segment_statistics a
--where
-- 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.
http://www.linkedin.com/in/kellynpedersen
"Go away before I replace you with a very small and efficient shell script..."
--- On Thu, 12/17/09, FMHabash <fmhabash@xxxxxxxxx> wrote:
From: FMHabash <fmhabash@xxxxxxxxx>
Subject: RE: ASSM and high volume concurrent inserts
To: haroon_a_qureshi@xxxxxxxxx, Oracle-L@xxxxxxxxxxxxx
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 <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
Other related posts:
