Thanks for the question, Tautvydas.

Asked: October 02, 2015 - 12:13 pm UTC

Last updated: September 26, 2018 - 12:40 am UTC

Version: 12.1.0.1 SE

Viewed 10K+ times! This question is

You Asked

Hi,

we have Oracle 12c (12.1.0.1 SE) RAC on 2-node Windows 2012 R2 OS.
I am testing simple performance test using jmeter which starts 500 concurrent users ant each of then inserts a one row in table 1000 times. Jmeter makes a commit after each insert. Randomly all hangs, servers cpu drops down (90% -> 0%), interconnect network bandwich goes up (5Mb -> 100 Mb) and all sessions stuck for about 60 seconds. And this happens few times during the test.
When i check database i see, that all inserts are waiting on "GC buffer busy acquire" or "GC buffer busy release".

Table used for test:
create table xx_some_table
(
log_sequence NUMBER not null,
request_id NUMBER not null,
message_text VARCHAR2(4000) not null,
session_id NUMBER,
timestamp DATE not null
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create indexxx_some_table_N1 on xx_some_table (REQUEST_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 10M
next 1M
minextents 1
maxextents unlimited
);
create index xx_some_table_N2 on xx_some_table (SESSION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 10M
next 1M
minextents 1
maxextents unlimited
);
create unique index xx_some_table_U1 on xx_some_table (LOG_SEQUENCE)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 10M
next 1M
minextents 1
maxextents unlimited
);

Sequence used for test:
create sequence xx_some_table_sq
start with 1
increment by 1;

Insert statement issued by users:

insert into XX_CDF_REQ_OUTPUT_MESSAGES_TL values(
XX_CDF_REQ_OUTPUT_MESSAGES_sq.Nextval,
trunc(dbms_random.value(1,10000)),
dbms_random.string('A', 400),
trunc(dbms_random.value(1,100)),
sysdate)

What can couse such delays in that simple insert statment? have tested same procedure in single instances - no problem at all.

and Connor said...

RAC is not the same as single instance :-)

THis is a link to an old paper on the OTN, but the principles are unchanged

http://www.oracle.com/technetwork/products/clustering/overview/s298716-oow2008-perf-130776.pdf

Take a look at page 16.

But try repeating the tests with

1) sequences set to cache size (say) 50000
2) make sure your relevant tablespaces as ASSM
3) hash partition your indexes which are monotonically increasing

but also, ask yourself - is your test a true reflection of what you plan to happen on your system. 500 connections all going flat out with no sleep time (you didnt specify if you have one) would be equivalent to a true user population of potentially tens of thousands...

Similarly, would you really expect a session to randomly pick an instance - would it not be the norm that once (say) session_id = 'x' is using (say) instance 1, then it will probably stay on instance 1 for its life. 'dbms_random' might not be such an accurate reflection of reality.

All systems have limits eventually.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sequence cash size i not that important s

Alex S, September 21, 2018 - 5:45 am UTC

Try this test to find the optimal sequence cache size.
I would not expect 50000 fits all hats.

create or replace
procedure test_sequence_cache(
p_cache_size integer := 0
, p_init_ind integer := 10000000
, p_ins_rows integer := 10000
, p_repeat integer := 10
)
is
l_begin_ts timestamp(6);
l_run_time varchar2(30);
l_nextval integer;

begin
execute immediate 'truncate table t reuse storage';
execute immediate 'alter sequence t_seq cycle minvalue 100000 '
|| case when p_cache_size = 0 then 'nocache' else 'cache '||to_char(p_cache_size) end;

l_begin_ts := current_timestamp;
for x in 1 .. p_repeat loop
for r in (
select 1 from b_posting
where id between p_init_ind + 1 and p_init_ind + p_ins_rows
) loop
select t_seq.nextval into l_nextval from dual;
end loop;
end loop;
l_run_time := to_char(lib.ts_dif(l_begin_ts, current_timestamp)/p_repeat,'999.9999');

dbms_output.put_line('Cache size: '||to_char(p_cache_size, '999990')||' -> Avg run time: '|| l_run_time);

end;
/

begin
test_sequence_cache(0, p_repeat => 20);
test_sequence_cache(20, p_repeat => 20);
test_sequence_cache(50, p_repeat => 20);
test_sequence_cache(100, p_repeat => 20);
test_sequence_cache(200, p_repeat => 20);
test_sequence_cache(250, p_repeat => 20);
test_sequence_cache(500, p_repeat => 20);
test_sequence_cache(1000, p_repeat => 20);
test_sequence_cache(2000, p_repeat => 20);
test_sequence_cache(5000, p_repeat => 20);
test_sequence_cache(10000, p_repeat => 20);
end;
/

begin
test_sequence_cache(0, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(20, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(50, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(100, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(200, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(250, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(500, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(1000, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(2000, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(5000, p_ins_rows => 100000, p_repeat => 10);
test_sequence_cache(10000, p_ins_rows => 100000, p_repeat => 10);
end;
/

September 26, 2018 - 12:40 am UTC

nice input

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

sequence cash size i not that important s

More to Explore

PL/SQL demos

PL/SQL docs

gc buffer busy acquire ion RAC on stress load

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

sequence cash size i not that important s

More to Explore

PL/SQL demos

PL/SQL docs