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

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Fri, 25 Mar 2005 03:01:34 -0800 (PST)
Message-ID: <20050325110134.87166.qmail@web52605.mail.yahoo.com>

Hi Christo,

I tried your test in my environment:
Solaris 9, 10.1.0.3, ASM

I just reduced the number of inserts to 100000 because I could not wait that long :)

I have got totally different results.
ASSM was a way faster on my configuration. Double faster.

But that does not prove anything here.

I collected all possible statistics in these tests.

The biggest puzzle for me was that I got almost double pure CPU time for non-ASSM table???????????

I probably did not read properly about ASSM in so many articles on the net, but:

HOW IS POSSIBLE THAT BIG DIFFERENCE IN CPU TIME FOR ASSM and non-ASSM table?
I have got (in seconds):

recursive cpu usage                  29,37            
         50,68
CPU used when call started           33,23            
         54,51
CPU used by this session             33,23            
         54,51
DB time                              34,54            
         55,17

>From statistics I could not find any big statistics
difference that can lead you to higher CPU time. It is looking to me as totally different algorithm inside insert. No big redo size, undo difference, ...

Anybody to comment this.

Wait statistics are complex thing to compare because it depends on your RAC/HW/network configuration, so I am leaving your wait statistics to be answered by somebody else.

In my environment it was all better in ASSM environemnt.

Regards,
Zoran

• Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:

> 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
>

=== message truncated ===

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Fri Mar 25 2005 - 06:05:12 CST