Posts Tagged partitioning

How to move Partitions ONLINE and make them READ ONLY in #Oracle 12c

readonly

You’ll see two New Features about Partitions covered here:  MOVE ONLINE (12cR1) and READ ONLY (12cR2)

[oracle@uhesse ~]$ sqlplus adam/adam@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 08:53:59 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 05 2017 08:41:13 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1					  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   40
Q2		   47
Q3		   40
Q4		   46

The next command has 12cR1 enhancements: row store compress basic is new syntax for compress basic, having the same effect than the 11g syntax. The online clause is the major improvement here, since it allows that movement while other sessions work with DML statements on that partition:

SQL> alter table sales_range move partition q1 row store compress basic online;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1	   BASIC			  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   16
Q2		   47
Q3		   40
Q4		   46

The following command is a 12cR2 New Feature – Partitions can be made READ ONLY now, preventing DML and DDL statements against them:

SQL> alter table sales_range modify partition q1 read only;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1	   BASIC			  YES
Q2					  NO
Q3					  NO
Q4					  NO

SQL> delete from sales_range where time_id=to_date('01.01.2016','dd.mm.yyyy');
delete from sales_range where time_id=to_date('01.01.2016','dd.mm.yyyy')
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


SQL> alter table sales_range drop partition q1;
alter table sales_range drop partition q1
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.

To undo the above:

SQL> alter table sales_range modify partition q1 read write;

Table altered.

SQL> alter table sales_range move partition q1 nocompress online;

Table altered.

SQL> select partition_name,compress_for,read_only from user_tab_partitions;

PARTITION_ COMPRESS_FOR 		  READ
---------- ------------------------------ ----
Q1					  NO
Q2					  NO
Q3					  NO
Q4					  NO

SQL> select partition_name,bytes/1024/1024 as mb from user_segments where partition_name like 'Q%';

PARTITION_	   MB
---------- ----------
Q1		   40
Q2		   47
Q3		   40
Q4		   46

Instead of the shown basic compression, OLTP compression and Hybrid Columnar Compression (HCC) can also be done online. Could be a topic of another article to cover that new syntax 🙂

Watch me on YouTube demonstrating the above:

,

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

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

fight_contention

Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:

Index Leaf Block Contention

Index Leaf Block Contention

For the demo below, I’m using 100 jobs running at the same time to simulate 100 end user session that do inserts into table t with an ordinary index i that is not yet partitioned:

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

Table created.

SQL> create index i on t(id);

Index created.

SQL> create sequence id_seq;

Sequence created.

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

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

SQL> exec manysessions

PL/SQL procedure successfully completed.

After a couple of minutes the jobs are done and the table is populated:

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 = 'I';

OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
I			  167363

There have been Buffer Busy Waits on the table t as well of course, but let’s focus on the index here. Now the same load but with a Hash Partitioned index instead:

SQL> drop index i;

Index dropped.

SQL> truncate table t;

Table truncated.

SQL> create index i on t(id) global
     partition by hash(id) partitions 32;
 
Index created.

Notice that you have to say GLOBAL even though the table is not partitioned itself, so LOCAL is impossible. How about the effect?

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 = 'I';


OBJECT_NAM SUBOBJECT_	   VALUE
---------- ---------- ----------
I	   SYS_P249	     138
I	   SYS_P250	     122
I	   SYS_P251	     138
I	   SYS_P252	     120
I	   SYS_P253	     134
I	   SYS_P254	     116
I	   SYS_P255	     132
I	   SYS_P256	     129
I	   SYS_P257	     126
I	   SYS_P258	     140
I	   SYS_P259	     126
I	   SYS_P260	     129
I	   SYS_P261	     142
I	   SYS_P262	     142
I	   SYS_P263	     156
I	   SYS_P264	     155
I	   SYS_P265	     165
I	   SYS_P266	     121
I	   SYS_P267	     142
I	   SYS_P268	     148
I	   SYS_P269	     120
I	   SYS_P270	     112
I	   SYS_P271	     168
I	   SYS_P272	     130
I	   SYS_P273	     129
I	   SYS_P274	     137
I	   SYS_P275	     147
I	   SYS_P276	     131
I	   SYS_P277	     132
I	   SYS_P278	     136
I	   SYS_P279	     124
I	   SYS_P280	     138

32 rows selected.

Instead of having just one hot part, we now have as many ‘warm parts’ as there are partitions, like the picture below tries to show:

Reduced contention with hash partitioned index

Reduced contention with hash partitioned index

Precisely this was achieved by the solution:

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

SUM(VALUE)
----------
      4325

SQL> select 167363-4325 as waits_gone from dual;

WAITS_GONE
----------
    163038

Give me an Oracle Database and I don’t need a calculator 🙂

Watch me explaining the above on YouTube:

,

3 Comments

%d bloggers like this: