Home > Oracle > extent Size and performance

extent Size and performance


What is the importance of creating objects with right extent sizes? Application folks very often places the segments in small uniform size tablespaces or small extent sizes ( in dictionary managed tablespaces, though an old story!) , causing the number of extents very high.  I have seen applications using objects more thousands of extents. 

I did a small test, which shows how bad a ‘db file scattered read’   with large number of small extents.

I got a table TEST with 1269376 records,

SQL> select bytes/1024/1024,owner from dba_segments where segment_name=’TEST’;

BYTES/1024/1024      OWNER
—————                     ——————————
            126                          THOMAS

SQL> select * from test;

1269376 rows selected.

And my db_file_multiblock_read_count   is 128

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_file_multiblock_read_count        integer     128

Now I have created two different tablespaces with 64KB and 5M uniform sizes respectively and moved the TEST table to the first 64KB extent size.

SQL> create tablespace tbs01 datafile ‘/oradata/ctgr/tbs01.dbf’  size 200m
extent management local uniform size 64k
segment space management auto;

SQL> create tablespace tbs02 datafile ‘/oradata/ctgr/tbs02.dbf’  size 200m
extent management local uniform size 5m
segment space management auto;

SQL> alter table test move tablespace tbs01;

Table altered.

 SQL> select segment_name,bytes/1024/1024,extents  from dba_segments where tablespace_name=’TBS01′;

SEGMENT_NAME         BYTES/1024/1024    EXTENTS
——————–        —————                          ———-
TEST                                  126.625                                     2026

TEST table got 2026 extents in 64KB tablespace.   I tried a FTS on this TEST table and got completed in 35.85 seconds.

SQL> set timing on

SQL> set autotrace traceonly exp stat

SQL> ALTER SESSION SET events ‘10046 trace name context forever, level 12’;

SQL> select * from test;

1269376 rows selected.

Elapsed: 00:00:35.83

Moved the  TEST table to tbs02, 5M extent sized tablespace and repeated the test

SQL> alter table test move tablespace tbs02;

Table altered.

SQL> set autotrace traceonly exp stat
SQL> set timing on
SQL> select * from test;

1269376 rows selected.

Elapsed: 00:00:33.51

Though the time wise the difference was small, but there was a big difference in over all operation.  The trace files shows the difference.

Though the db_file_multiblock_read_count was set to 128, the TEST table in 64K tablespace was never been able to use it.  The maximum single read  was 8 blocks (Extent size / Block size=64/8=8).  Alert file shows – part of trace file

WAIT #1: nam=’db file scattered read’ ela= 122 file#=16 block#=15881 blocks=8 obj#=14046 tim=3773862080763
WAIT #1: nam=’db file scattered read’ ela= 116 file#=16 block#=15889 blocks=8 obj#=14046 tim=3773862098643
WAIT #1: nam=’db file scattered read’ ela= 121 file#=16 block#=15897 blocks=8 obj#=14046 tim=3773862129902
WAIT #1: nam=’db file scattered read’ ela= 118 file#=16 block#=15905 blocks=8 obj#=14046 tim=3773862149758
WAIT #1: nam=’db file scattered read’ ela= 119 file#=16 block#=15913 blocks=8 obj#=14046 tim=3773862175090
WAIT #1: nam=’db file scattered read’ ela= 119 file#=16 block#=15921 blocks=8 obj#=14046 tim=3773862199789
WAIT #1: nam=’db file scattered read’ ela= 111 file#=16 block#=15929 blocks=8 obj#=14046 tim=3773862219234
WAIT #1: nam=’db file scattered read’ ela= 114 file#=16 block#=15937 blocks=8 obj#=14046 tim=3773862238023
WAIT #1: nam=’db file scattered read’ ela= 141 file#=16 block#=15945 blocks=8 obj#=14046 tim=3773862260365
WAIT #1: nam=’db file scattered read’ ela= 129 file#=16 block#=15953 blocks=8 obj#=14046 tim=3773862285777
WAIT #1: nam=’db file scattered read’ ela= 110 file#=16 block#=15961 blocks=8 obj#=14046 tim=3773862306669
WAIT #1: nam=’db file scattered read’ ela= 108 file#=16 block#=15969 blocks=8 obj#=14046 tim=3773862325566
WAIT #1: nam=’db file scattered read’ ela= 115 file#=16 block#=15977 blocks=8 obj#=14046 tim=3773862350108
WAIT #1: nam=’db file scattered read’ ela= 131 file#=16 block#=15985 blocks=8 obj#=14046 tim=3773862369584
WAIT #1: nam=’db file scattered read’ ela= 100 file#=16 block#=15994 blocks=7 obj#=14046 tim=3773862389234
WAIT #1: nam=’db file scattered read’ ela= 135 file#=16 block#=16001 blocks=8 obj#=14046 tim=3773862404840
WAIT #1: nam=’db file scattered read’ ela= 115 file#=16 block#=16009 blocks=8 obj#=14046 tim=3773862423293
WAIT #1: nam=’db file scattered read’ ela= 114 file#=16 block#=16017 blocks=8 obj#=14046 tim=3773862465765
WAIT #1: nam=’db file scattered read’ ela= 108 file#=16 block#=16025 blocks=8 obj#=14046 tim=3773862484276
WAIT #1: nam=’db file scattered read’ ela= 115 file#=16 block#=16033 blocks=8 obj#=14046 tim=3773862502827
WAIT #1: nam=’db file scattered read’ ela= 132 file#=16 block#=16041 blocks=8 obj#=14046 tim=3773862521875
WAIT #1: nam=’db file scattered read’ ela= 231 file#=16 block#=16049 blocks=8 obj#=14046 tim=3773862540777
WAIT #1: nam=’db file scattered read’ ela= 114 file#=16 block#=16057 blocks=8 obj#=14046 tim=3773862558424
WAIT #1: nam=’db file scattered read’ ela= 129 file#=16 block#=16065 blocks=8 obj#=14046 tim=3773862576394
WAIT #1: nam=’db file scattered read’ ela= 105 file#=16 block#=16073 blocks=8 obj#=14046 tim=3773862595067
WAIT #1: nam=’db file scattered read’ ela= 108 file#=16 block#=16081 blocks=8 obj#=14046 tim=3773862614336
WAIT #1: nam=’db file scattered read’ ela= 130 file#=16 block#=16089 blocks=8 obj#=14046 tim=3773862632114
WAIT #1: nam=’db file scattered read’ ela= 112 file#=16 block#=16097 blocks=8 obj#=14046 tim=3773862648406
WAIT #1: nam=’db file scattered read’ ela= 127 file#=16 block#=16105 blocks=8 obj#=14046 tim=3773862667488
WAIT #1: nam=’db file scattered read’ ela= 136 file#=16 block#=16113 blocks=8 obj#=14046 tim=3773862686854
WAIT #1: nam=’db file scattered read’ ela= 107 file#=16 block#=16122 blocks=7 obj#=14046 tim=3773862720380
WAIT #1: nam=’db file scattered read’ ela= 103 file#=16 block#=16129 blocks=8 obj#=14046 tim=3773862737961
WAIT #1: nam=’db file scattered read’ ela= 122 file#=16 block#=16137 blocks=8 obj#=14046 tim=3773862755803
WAIT #1: nam=’db file scattered read’ ela= 146 file#=16 block#=16145 blocks=8 obj#=14046 tim=3773862777661

And there were 2000+ reads in the trace file.

$ grep -i read ctgr_ora_9802.trc |wc -l
    2039

But in the 5M uniform sized tablespace, db_file_multiblock_read_count=128 worked as expected.  The trace file shows,

WAIT #1: nam=’db file scattered read’ ela= 24828 file#=17 block#=2195 blocks=128 obj#=14046 tim=3775107883725
WAIT #1: nam=’db file scattered read’ ela= 14884 file#=17 block#=2323 blocks=128 obj#=14046 tim=3775108228691
WAIT #1: nam=’db file scattered read’ ela= 16576 file#=17 block#=2451 blocks=118 obj#=14046 tim=3775108557498
WAIT #1: nam=’db file scattered read’ ela= 4285 file#=17 block#=2579 blocks=105 obj#=14046 tim=3775108873915
WAIT #1: nam=’db file scattered read’ ela= 9488 file#=17 block#=2812 blocks=92 obj#=14046 tim=3775109495316
WAIT #1: nam=’db file scattered read’ ela= 2049 file#=17 block#=3032 blocks=48 obj#=14046 tim=3775110046174
WAIT #1: nam=’db file scattered read’ ela= 8767 file#=17 block#=3081 blocks=128 obj#=14046 tim=3775110173334
WAIT #1: nam=’db file scattered read’ ela= 19404 file#=17 block#=3219 blocks=128 obj#=14046 tim=3775110517803
WAIT #1: nam=’db file scattered read’ ela= 7646 file#=17 block#=3347 blocks=128 obj#=14046 tim=3775110839458
WAIT #1: nam=’db file scattered read’ ela= 14465 file#=17 block#=3475 blocks=58 obj#=14046 tim=3775111196121
WAIT #1: nam=’db file scattered read’ ela= 9897 file#=17 block#=3661 blocks=128 obj#=14046 tim=3775111709529
WAIT #1: nam=’db file scattered read’ ela= 6316 file#=17 block#=3789 blocks=60 obj#=14046 tim=3775112065127
WAIT #1: nam=’db file scattered read’ ela= 1634 file#=17 block#=3859 blocks=35 obj#=14046 tim=3775112255366
WAIT #1: nam=’db file scattered read’ ela= 9911 file#=17 block#=4022 blocks=128 obj#=14046 tim=3775112728929
WAIT #1: nam=’db file scattered read’ ela= 5203 file#=17 block#=4150 blocks=128 obj#=14046 tim=3775113092200
WAIT #1: nam=’db file scattered read’ ela= 10393 file#=17 block#=4278 blocks=128 obj#=14046 tim=3775113465566
WAIT #1: nam=’db file scattered read’ ela= 244 file#=17 block#=4406 blocks=20 obj#=14046 tim=3775113777441
WAIT #1: nam=’db file scattered read’ ela= 7794 file#=17 block#=4427 blocks=62 obj#=14046 tim=3775113833660
WAIT #1: nam=’db file scattered read’ ela= 5194 file#=17 block#=4499 blocks=128 obj#=14046 tim=3775114012895
WAIT #1: nam=’db file scattered read’ ela= 11177 file#=17 block#=4627 blocks=128 obj#=14046 tim=3775114372341
WAIT #1: nam=’db file scattered read’ ela= 3353 file#=17 block#=4755 blocks=46 obj#=14046 tim=3775114716369
WAIT #1: nam=’db file scattered read’ ela= 11691 file#=17 block#=4929 blocks=128 obj#=14046 tim=3775115171796
WAIT #1: nam=’db file scattered read’ ela= 5183 file#=17 block#=5057 blocks=72 obj#=14046 tim=3775115497354
WAIT #1: nam=’db file scattered read’ ela= 5869 file#=17 block#=5139 blocks=52 obj#=14046 tim=3775115684285
WAIT #1: nam=’db file scattered read’ ela= 11054 file#=17 block#=5319 blocks=99 obj#=14046 tim=3775116126484
WAIT #1: nam=’db file scattered read’ ela= 7332 file#=17 block#=5546 blocks=105 obj#=14046 tim=3775116679073
WAIT #1: nam=’db file scattered read’ ela= 16861 file#=17 block#=5652 blocks=117 obj#=14046 tim=3775116973543
WAIT #1: nam=’db file scattered read’ ela= 12160 file#=17 block#=5779 blocks=128 obj#=14046 tim=3775117275216

$ grep -i read ctgr_ora_9890.trc |wc -l
     212

There were only 212 reads in 5M uniform sized tablespace.  So, a small uniform sized tablespace can add strain on I/O when it is doing  ‘db file scattered read’ on large tables.  It is not just fragmentation.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: