New mean demo machine

My new Notebook is there! Will spend a couple of hours to do the setup. The specs are quite promising:

CPU:
Intel Core i7-6700 | 4 Cores | 8 Threads | 3,4 – 4,0GHz
Memory:
32GB  SO-DIMM DDR4 RAM 2400MHz Crucial Ballistix Sport LT
6 TB SSD Storage:
1TB m.2 Crucial MX300
1TB m.2 Crucial MX300
2TB Seagate FireCuda | 5400U/Min | 7mm
2TB Seagate FireCuda | 5400U/Min | 7mm

Okay it did cost me a fortune. See the new one on the left:

nc

But that baby should allow for two 2-Node Clusters running with Virtualbox VMs plus a Cloud Control VM. Want to have my own Maximum Availability lab – and it’s still less expensive and easier to transport than an Oracle Database Appliance😉

5 Comments

#ukoug_tech16 Review with Tweets

UKOUG Tech16 is in its final hours, time for a special review: Tweets about it

It was in Birmingham again – very nice location!

 

//platform.twitter.com/widgets.js

Standard Edition attracts not only my attention more and more

//platform.twitter.com/widgets.js

We have had a well visited Round Table about Standard Edition

//platform.twitter.com/widgets.js

and one about Data Guard as well

//platform.twitter.com/widgets.js

Many top-notch speakers again

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

(Sorry, forgot to take a pic here)

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

//platform.twitter.com/widgets.js

I did my part and delivered two talks also: One about what changes with Multitenant for the DBA like I did at the DOAG conference and one about what can be done with Partitioning since 8i. Both talks were well received by a friendly crowd, thank you for that🙂 As always, it was a nice experience and I’d like to express my appreciation to the organizers of this great event!

Leave a comment

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

fight_contention_2

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

Contention with a heap table

Contention with a heap table

The last extent becomes a hot spot; all inserts go there and only a limited number of blocks is available. Therefore we will see Buffer Busy Waits. The playground:

SQL> create table t (id number, sometext varchar2(50));

Table created.

create sequence id_seq;

Sequence created.

create or replace procedure manyinserts as
begin
 for i in 1..10000 loop
  insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?');
 end loop;
 commit;
end;
/

Procedure created.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..100 LOOP
 dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

Procedure created.

The procedure manysessions is the way how I simulate OLTP end user activity on my demo system. Calling it leads to 100 job sessions. Each does 10.000 inserts:

SQL> exec manysessions

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T			    2985

So we got thousands of Buffer Busy Waits that way. Now the remedy:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number, sometext varchar2(50))
     partition by hash (id) partitions 32;

Table created.

 
SQL> alter procedure manyinserts compile;

Procedure altered.

SQL> alter procedure manysessions compile;

Procedure altered.

SQL> exec manysessions 

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
   1000000

SQL> select object_name,subobject_name,value from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';  

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
T	   SYS_P249	       0
T	   SYS_P250	       1
T	   SYS_P251	       0
T	   SYS_P252	       0
T	   SYS_P253	       0
T	   SYS_P254	       0
T	   SYS_P255	       0
T	   SYS_P256	       1
T	   SYS_P257	       0
T	   SYS_P258	       0
T	   SYS_P259	       1
T	   SYS_P260	       0
T	   SYS_P261	       0
T	   SYS_P262	       0
T	   SYS_P263	       0
T	   SYS_P264	       1
T	   SYS_P265	       1
T	   SYS_P266	       0
T	   SYS_P267	       0
T	   SYS_P268	       0
T	   SYS_P269	       0
T	   SYS_P270	       0
T	   SYS_P271	       1
T	   SYS_P272	       0
T	   SYS_P273	       0
T	   SYS_P274	       0
T	   SYS_P275	       1
T	   SYS_P276	       0
T	   SYS_P277	       0
T	   SYS_P278	       0
T	   SYS_P279	       2
T	   SYS_P280	       0

32 rows selected.

SQL> select sum(value) from v$segment_statistics 
     where owner='ADAM' 
     and statistic_name='buffer busy waits'
     and object_name = 'T';

SUM(VALUE)
----------
	 9

SQL> select 2985-9 as waits_gone from dual;

WAITS_GONE
----------
      2976

The hot spot is gone:

hash_part_table

This emphasizes again that Partitioning is not only for the Data Warehouse. Hash Partitioning in particular can be used to fight contention in OLTP environments.

,

1 Comment

%d bloggers like this: