SAFE, extent sizes and I/O

[efrijters]

Hello,

I'm trying to use the SAFe rules while creating my new tablespaces. I have a question about the extent sizes mentioned in the SAFE-document.

The document says: use 160K, 5120K and 160M for extent sizes.

Okay, everything is clear, but...

I use Oracle 8.1.7 on a MS Window 2K Server. I prooved to myself that the largest possible read I/O on this system is 256KB.
(I changed some parameters so that read/sort-actions will use a multiple of 256KB.)

My questions:
1. When I compare this with the SAFE-document, isn't it wise to use a minimal/multiple size of 256KB instead of 160KB?

2. I also have a lot of very small tables in my db (30-40% is either empty or have a small amount of rows). Those tables are now in 40KB extents. I want to create a "SMALL"-ts for those tables.
Which uniform size is best for this LMT-TS?:
- 40KB? (very small, but maybe a lot of overhead, when there are multiple extents)
- 160KB? (as mentioned in SAFE)
- 256KB? (if I'm right in question number 1)

I hope you can share your knowledge & experience on this subject...

Erik

[marist89]

Stop worrying. Use system allocated lmt's.

[efrijters]

What do you mean by "system allocated"? Automatic sizing of extents?
Uniform sizing is better (best?) when you read SAFE?

[abhaysk]

Originally posted by marist89
Stop worrying. Use system allocated lmt's.

Which will again raise Fragmentation Issues !!

[efrijters]

Please, let's not start that 'fragmentation'-discussion again...

Abhaysk : what about my initial question? I'm getting the feeling that the difference between 256kb and 160KB is small. In the case of small tables I just waste space. But I worry about the buffer cache though...

[efrijters]

I looked at the amount of data of my db. In total we have 1.3GB data (It ain't much, I know...)

Data spread:
about 40% of the tables is almost empty;
about 50% of the table is about 5MB - 99MB
about 10% of the tables are bigger: 100-270MB

What about the following setup:

Size             Extent   TS
of table         Size     Name
--------------------------------
     <= 2.5MB     40KB    TINY
2.5MB - 64MB     256KB    SMALL
64MB  - 1G         4MB    MEDIUM
      >1G        128MB    LARGE

I know this doesn't follow SAFE concerning the rule of only creating 3 sizes of extents, but I made an extra extent size (40KB - TINY) for all those tiny tables.

I just want to know if this is wrong or bad for performance.

Your help is very appreciated (you may also donate money )

Erik

[slimdave]

i agree that 256k makes more sense -- this also imples that dbfile_multi_block_read_count ought to be 256k/(db default block size).

[tamilselvan]

I would suggest 16K uniform size for small-tiny TS where you can store MASTER (LOOKUP) tables and set db_file_multiblock_read_count to 8. Why should you waste space by allocating 40KB for the initial size when you have couple of rows in the small tiny tables.

When you set block size 8KB, IO size 256KB, and db_file_multiblock_read_count 32 ( 256/8), the optimizer may choose full table scan instead of index lookup.

In general, Thoery is great; but test is always accurate and perfect.

Tamil

[jmodic]

Originally posted by efrijters
The document says: use 160K, 5120K and 160M for extent sizes.

If you look again in that document, you'll find that those extend sizes are recomended for databases prior to 8.0 - that's because of the rounding algorithm Oracle have used in earlier releases (minimum 5 blocks in an extent or something like this). For Oracle8 and later that document (I hope we both are refering to the same whitepaper!) it explicitely states more "logical" recomended sizes are 128K, 4M and 128M respectively.

[abhaysk]

Originally posted by slimdave
i agree that 256k makes more sense -- this also imples that dbfile_multi_block_read_count ought to be 256k/(db default block size).

Why would it??

If you really drill down and know how Oracle will do an FTS, then having 256K just for few rows which can fit in a block seems useless.

Coz Oracle will first do a single Block I/O even for an FTS and then start the DB_MULTI_BLOCK_READ_COUNT blocks per I/O....so with having extent size 256, it will still do an 2 I/O operations...

And with extent size 16K (if all rows can fit in here), then also it will have to do just 2 I/O..

So why 256K??

Abhay.