Index Fragmentation – more thoughts.

Looking at my last post I’m somewhat unsatisfied about the script to create the “fragmented” index so I thought I’d have another go, modifying it to be like this:

drop table TEST_FRAG2;
create table TEST_FRAG2 (id number not null, value varchar2(10));
insert into TEST_FRAG2 select id,’stuff’ from
(select rownum as id from dual connect by level <=400000)
where mod(trunc(id/400),2)=0;
create unique index TEST_FRAG2_IDX on TEST_FRAG2(id);
insert into TEST_FRAG2 select id,’stuff’ from
(select rownum as id from dual connect by level <=400000)
where mod(trunc(id/400),2)=1;
commit;

begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘CHRIS’,tabname => ‘TEST_FRAG2′,
estimate_percent => 100,method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
end;

This creates an index which has a similar type of physical fragmentation as the pervious example but a better clustering factor, running the first test again. (I’m seeing a faster response from disk today, however I wouldn’t read too muchinto that as my test database is 11g , Oracle E-Linux 5.2 on Oracle VM on the cheapest dell server I could get with one disk and a bunch of third party ram).

alter system flush buffer_cache;
alter session set events ’10046 trace name context forever, level 8′;
select count(id) from test_frag2 where id >=1000 and id <=20000;
alter session set events ’10046 trace name context off’;

Taking a look at the trace file shows the following (this is a small sample) type of activity we are reading blocks that are not sequentially placed on disk

WAIT #11: nam=’db file sequential read’ ela= 42 file#=4 block#=25070 blocks=1 obj#=68308 tim=1236636891337307
WAIT #11: nam=’db file sequential read’ ela= 27 file#=4 block#=25956 blocks=1 obj#=68308 tim=1236636891337422
WAIT #11: nam=’db file sequential read’ ela= 27 file#=4 block#=25960 blocks=1 obj#=68308 tim=1236636891337539
WAIT #11: nam=’db file sequential read’ ela= 26 file#=4 block#=25071 blocks=1 obj#=68308 tim=1236636891337667
WAIT #11: nam=’db file sequential read’ ela= 29 file#=4 block#=25968 blocks=1 obj#=68308 tim=1236636891337783
WAIT #11: nam=’db file sequential read’ ela= 26 file#=4 block#=25964 blocks=1 obj#=68308 tim=1236636891337911
WAIT #11: nam=’db file sequential read’ ela= 26 file#=4 block#=25072 blocks=1 obj#=68308 tim=1236636891338034
WAIT #11: nam=’db file sequential read’ ela= 28 file#=4 block#=25976 blocks=1 obj#=68308 tim=1236636891338151

then runing the second test

alter system flush buffer_cache;
alter session set events ’10046 trace name context forever, level 8′;
select count(value) from test_frag2 where id >=1000 and id <=20000;
select count(value) from test_unfrag where id >=1000 and id <=20000;
alter session set events ’10046 trace name context off’;

now uses the same execution plan as before, looking at the tkproffed output shows the query on test_frag2 doing more work.

select count(value)
from
test_frag2 where id >=1000 and id <=20000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01        108        155          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.01        108        155          0           1

select count(value)
from
test_unfrag where id >=1000 and id <=20000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01         83         83          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.01         83         83          0           1

A coalesce on the index TEST_FRAG2_IDX improves things but still we are doing slightly more work, as shown below, a rebuild gives exactly the same result.

select count(value)
from
test_frag2 where id >=1000 and id <=20000

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02         85        132          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02         85        132          0           1

I’ll need to take a look at whats going on here but I suspect the extra work is because the table data is scattered accross more blocks rather than anything to do with the index structure

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: