There is a lot of myths about big and small database blocks for indexes.
From 9i there is a possibility to create tablespaces with different block size. A lot of people
think that there a solution for speed up a indexes. It was my assumption too, but I decided to
make some research on that. You can find my results and some remarks below.
Test nr 1.
Table definition
CREATE TABLE TEST1
(
A VARCHAR2(18 CHAR),
B VARCHAR2(18 CHAR),
B1 VARCHAR2(15 CHAR),
B2 VARCHAR2(16 CHAR),
B3 VARCHAR2(8 CHAR),
C1 DATE,
C2 DATE,
DUR VARCHAR2(6 CHAR),
E VARCHAR2(3 CHAR),
I VARCHAR2(40 CHAR)
)
There are 89999997 rows in this table,
and column A has 914174 different values.
I've wrote a piece of PL/SQL script which was looking
for random A value and then fetch all rows with this value
in loop. Time of looking for value and fetching all rows for it was
measured by timestamp variable in PL/SQL and then reported
to standard output. This script was executed in 'dry run' mode
2 times after database shutdown and then it was run in measured mode.
This same scenario was performed 12 times - 6 times for 8 kB block and
6 times for 16 kB.
declare
i int;
c int;
v_begin timestamp(9);
v_end timestamp(9);
v_interval INTERVAL DAY TO SECOND;
begin
i:=0;
for w in 1..1000 loop
v_begin := SYSTIMESTAMP;
c:=0;
for r in (select A, B, DUR from test where A = (select lpad(trunc(dbms_random.value(0,1000000)),18,'0') from dual)) loop
i:=r.duration;
c:=c+1;
end loop;
v_end := SYSTIMESTAMP;
v_interval := v_end - v_begin;
if (c<>0) then
dbms_output.put_line(to_char(v_interval) || ' ' || c);
end if;
end loop;
end;
/Here are results:
So as you can see response time is almost the same and none of block size
is a winner.
When I made some more depth investigation, I found out that main SQL statement
is using index range scan to find rowid of block in table. In raw trace files,
I have found, as expected "db file sequential read" so Oracle was using single block
read to access a index and also single block read was used to access the table.
If database block size if below max IO request size it will be always executed in one step.
Here is example result from tkprof:
8 kB database block
16 kB database block
As you can see both execution of SQL has this same execution plan. But in case of 8 kB block SQL had better response time, then in case of 16 kB. There are one difference in executing both scenario - in 16 kB block there was several "db file scattered read" events which means that there was multi blocks operations. In raw trace file I have found that each multi blocks operations were only 2 or 3 blocks read.
Another side effects, related to block size change are changes in SQL cost.
CBO is calculating index cost using leaf block number and clustering factor.
In case of 16 kB index tablespace, where will be less leaf blocks but clustering factor could be bigger then in 8 kB index tablespace.
Here are results from my example:
8 kB - cost 106
Leaf Blocks - 411768
Custering Factor - 86883030
16 kB
Leaf Blocks - 211117
Custering Factor - 90356662
There are all my findings. I'm waiting for any comments and remarks.
Update - 11.05.2008
With 4 kB blocks performance is even worse. I will update all the pictures soon.
