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