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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: