Re: ASSM in 10g RAC doesnt seem work that well
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Mar 2005 14:57:36 -0000
Christo,
Here's a guess you may be able to check on.
I am assuming that you've called the tablespace LARGE_ONE
because it is a tablespace with a large extent size. Is the tablespace
called AUTO using extent management auto as well as segment
space management auto. Some of the difference could have appeared
because of some of the inefficiencies that occur when ASSM is mixed
with small extents. (Number of blocks per Level 1 bitmap block is low).
Bute even if the AUTO tablespace was locally managed with large
extents, you may see some collision problems on level 2 bitmaps.
Level 1 bitmap blocks become associated with a specific instance,
but I believe the same is not true of level 2 bitmaps.
As the blocks for level 1 bitmaps are filled, the level 2 bitmap
has to be updated to show that it's supply of level one bitmaps
is dropping.
Possibly you have been filling blocks so fast that your level 2
bitmap (and I think you would probably only have had one)
has been bouncing back and forth between instances.
Did you check v$waitstat to see if that gave you any clues
about the blocks that were subject to waits - my GUESS
is that you will see "2nd level bmb".
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005
----- Original Message -----
From: "Christo Kutrovsky" <kutrovsky.oracle@xxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 24, 2005 7:56 PM
Subject: ASSM in 10g RAC doesnt seem work that well
Hello All,
I've been testing a 10g RAC database (on linux & RAW) and one very
specific task our application is doing is concurent inserts into the
same table.
I understand there are concurency issues with Indexes, however I am
doing a very simple test on a single table with concuren inserts.
My testing shows that if the table is in a non-assm tablespace with
freelist groups it works much better, compared to an ASSM tablespace.
About 30 seconds (50% more) is lost in each session waiting on the
global cache events.
So it appears that ASSM is not a good solution for 10g RAC, yet it is
supposed to be the solution for RAC.
Am I doing something wrong ? Is my test flawed ? Did I miss something?
This is a test system, with no-one else but me on the system.
Here's the test case:
/* NON ASSM */
SQL>
drop table k_ins2;
Table dropped
Executed in 0.231 seconds
select tablespace_name, segment_space_management from dba_tablespaces
where tablespace_name = 'LARGE_ONE';
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
------------------------------ ------------------------
LARGE_ONE MANUAL
Executed in 0.24 seconds
create table k_ins2 (id number, type varchar2(30), dt date, inst
number(1)) tablespace LARGE_ONE storage(freelist groups 2);
Table created
Executed in 0.18 seconds
exec dbms_application_info.set_module('ASSM TEST',null);
PL/SQL procedure successfully completed
Executed in 0.15 seconds
declare
i number;
begin
for i in 1..1000000 loop
insert into k_ins2 values (i, 'FIXED', sysdate +i/10, null);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed
Executed in 63.712 seconds
session 2 ->
Executed in 66.48 seconds
===========================================================================================
select inst_id as i, sid, event, sum(total_waits) as cnt_w,
sum(total_timeouts) cnt_tout,
sum(time_waited/100) as waited, round(avg(average_wait/100),3) as
avg_wait, sum(max_wait/100) as mx
from (
select inst_id, sid, event, total_waits, total_timeouts, time_waited,
average_wait, max_wait
from gv$session_event where (inst_id,sid)
in (select inst_id,sid from gv$session where module like 'ASSM TEST%')
and event not in ('SQL*Net message from client') and time_waited > 10
union all
select inst_id, sid, 'CPU Time', null, null, stats.value as
time_waited, null, null
from gv$sesstat stats where (stats.INST_ID, stats.sid)
in (select inst_id,sid from gv$session where module like 'ASSM TEST%')
and statistic# = ( select statistic# from v$statname where name = 'CPU
used by this session')
) group by rollup((inst_id,sid),event) order by
grouping_id(inst_id,sid,event) desc, waited desc;
I SID EVENT CNT_W CNT_TOUT WAITED
AVG_WAIT MX
- --- ------------------------------- ----- ---------- ----------
---------- ----------
4559 0 129.81
0.01 0.2
1 148 2411 0 65.72
0.01 0.08
2 137 2148 0 64.09
0.01 0.12
1 148 CPU Time 64.07
2 137 CPU Time 59.08
2 137 gc current block busy 131 0 4.5
0.03 0.08
1 148 gc current block busy 34 0 0.98
0.03 0.04
2 137 enq: HW - contention 1438 0 0.31
0 0.04
1 148 gc current block 2-way 779 0 0.28
0 0
1 148 gc current multi block request 1587 0 0.28
0 0
2 137 gc current block 2-way 579 0 0.2
0 0
1 148 enq: HW - contention 11 0 0.11
0.01 0.04
12 rows selected
Executed in 0.52 seconds
SQL>
/* ASSM */
SQL>
drop table k_ins2;
Table dropped
Executed in 0.19 seconds
select tablespace_name, segment_space_management from dba_tablespaces
where tablespace_name = 'USERS';
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
------------------------------ ------------------------
USERS AUTO
Executed in 0.241 seconds
create table k_ins2 (id number, type varchar2(30), dt date, inst
number(1)) tablespace USERS;
Table created
Executed in 0.17 seconds
exec dbms_application_info.set_module('ASSM TEST',null);
PL/SQL procedure successfully completed
Executed in 0.16 seconds
declare
i number;
begin
for i in 1..1000000 loop
insert into k_ins2 values (i, 'FIXED', sysdate +i/10, null);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed
Executed in 99.834 seconds
Session 2 ->
Executed in 100.494 seconds
========================================================================================================================
SQL>
select inst_id as i, sid, event, sum(total_waits) as cnt_w,
sum(total_timeouts) cnt_tout,
sum(time_waited/100) as waited, round(avg(average_wait/100),3) as
avg_wait, sum(max_wait/100) as mx
from (
select inst_id, sid, event, total_waits, total_timeouts, time_waited,
average_wait, max_wait
from gv$session_event where (inst_id,sid)
in (select inst_id,sid from gv$session where module like 'ASSM TEST%')
and event not in ('SQL*Net message from client') and time_waited > 10
union all
select inst_id, sid, 'CPU Time', null, null, stats.value as
time_waited, null, null
from gv$sesstat stats where (stats.INST_ID, stats.sid)
in (select inst_id,sid from gv$session where module like 'ASSM TEST%')
and statistic# = ( select statistic# from v$statname where name = 'CPU
used by this session')
) group by rollup((inst_id,sid),event) order by
grouping_id(inst_id,sid,event) desc, waited desc;
I SID EVENT CNT_W CNT_TOUT WAITED
AVG_WAIT MX
-- --- -------------------------------- ----- ---------- ----------
---------- ----------
6321 15 196.87
0.038 3.68
1 146 2115 15 98.5
0.047 2.08
2 137 4206 0 98.37
0.026 1.6
1 146 CPU Time 65.73
2 137 CPU Time 57.67
1 146 gc buffer busy 208 15 20.21
0.1 1.01
2 137 gc current block 2-way 3426 0 16.74
0 1.05
2 137 gc current block busy 183 0 16.09
0.09 0.27
1 146 gc current block busy 151 0 7.02
0.05 0.27
2 137 gc buffer busy 227 0 5.86
0.03 0.04
1 146 gc current block 2-way 288 0 3.32
0.01 0.27
2 137 gc current grant busy 262 0 1.76
0.01 0.23
1 146 gc current retry 24 0 1.71
0.07 0.23
2 137 gc cr block busy 108 0 0.25
0 0.01
1 146 gc current multi block request 882 0 0.2
0 0.11
1 146 enq: HW - contention 2 0 0.19
0.1 0.19
1 146 DFS lock handle 560 0 0.12
0 0
17 rows selected
Executed in 0.471 seconds
--
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
