RE: ASSM in 10g RAC doesnt seem work that well

Let me give you a heads up on a rather serious bug we encountered using
ASSM with Oracle 9i RAC (9.2.0.3.0, 9.2.0.5.0).  Oracle documentation
states at least a 30% performance improvement with ASSM over
freelists/freelist groups but I'm certain they didn't run their tests
against tables that get into the "condition" we're experiencing.  I also
see some of the testing on this thread is seeing significant performance
improvements with ASSM...

We started seeing I/O per single row inserts grow to huge numbers- on a
simple table with 3 indexes I/O would be as high as 1500 - 2000 blocks
per insert (vs < 20 under normal circumstances on this table).  Bug
4074953 was created and a patch has been created to fix the issue- The
text for the bug explains what was happening but basically the bitmap
blocks were getting lost and excessive bitmap blocks are getting scanned
when looking for the block to insert the row into.  It's specific to
ASSM and RAC. =20

Something I consider rather serious to be aware of.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Friday, March 25, 2005 8:58 AM
To: oracle-l
Subject: Re: ASSM in 10g RAC doesnt seem work that well




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 -----=20
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 =3D '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

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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# =3D ( select statistic# from v$statname where name =3D =
'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 =3D '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

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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# =3D ( select statistic# from v$statname where name =3D =
'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


--=20

Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: