Playing with Oracle data block size

In this example I have experiment of oracle data block size with 8k and 16K. I performed DML operation against both 8k and 16k data block size. I created two tables table_8k under 8k tablespace tbs_8k and table_16k under 16k tablespace tbs_16k. The summary of the experiment is bigger data block cause more time while update but less time while insert operation.

A)I used OMF file system. So I set db_create_file_dest.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /oradata2

B)Create 8k and 16K blocksize tablespace
SQL> create tablespace tbs_8k blocksize 8k;
Tablespace created.

SQL> alter system set db_16k_cache_size=20M;
System altered.

SQL> create tablespace tbs_16k blocksize 16k;
Tablespace created.

C)create table_8k under 8k blocksize tablespace and table_16k under 16k blocksize tablespace and insert random data into them.
SQL> create table table_8k (n number ,k varchar2(15)) tablespace tbs_8K;
Table created.

SQL> begin
for i in 1 .. 100000
loop
insert into table_8k values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.51

SQL> create table table_16K (n number ,k varchar2(15)) tablespace tbs_16k;
Table created.

SQL> begin
for i in 1 .. 100000
loop
insert into table_16k values(i,'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:09.42

D)I created one script named flush will will flush data from buffer cache and shared pool so that we get actual result to complete operation.
SQL> !vi flush.sql
alter system flush buffer_cache;
alter system flush shared_pool;

SQL> @flush
System altered.
Elapsed: 00:00:00.03

System altered.
Elapsed: 00:00:00.03

SQL> select * from table_8k where k like '%888%';
176 rows selected.
Elapsed: 00:00:00.14

SQL> select * from table_16k where k like '%888%';
195 rows selected.
Elapsed: 00:00:00.04


So we see for 16k select is faster.
E) Now test for update operation.
SQL> @flush
SQL> update table_16k set k='Testing';
100000 rows updated.

Elapsed: 00:00:06.88

SQL> @flush
System altered.
Elapsed: 00:00:00.24
System altered.
Elapsed: 00:00:00.02

SQL> update table_8k set k='Testing';
100000 rows updated.
Elapsed: 00:00:01.91

We see update is fairly faster in 8k tablespace.
SQL> delete from table_8k;
100000 rows deleted.
Elapsed: 00:00:09.53

SQL> delete from table_16K;
100000 rows deleted.
Elapsed: 00:00:09.27

Related Documents:
-------------------------
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle
Advantage and Disadvantage of small and bigger data block
Data Block Format in Oracle