| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 24 2005 - 15:00:24 CST
 |
 |