-
SAFE, extent sizes and I/O
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
Last edited by efrijters; 12-11-2003 at 10:01 AM.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
Stop worrying. Use system allocated lmt's.
Jeff Hunter
-
What do you mean by "system allocated"? Automatic sizing of extents?
Uniform sizing is better (best?) when you read SAFE?
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
Originally posted by marist89
Stop worrying. Use system allocated lmt's.
Which will again raise Fragmentation Issues !!
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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...
Last edited by efrijters; 12-11-2003 at 11:41 AM.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
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:
Code:
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
Last edited by efrijters; 12-11-2003 at 12:11 PM.
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
-
i agree that 256k makes more sense -- this also imples that dbfile_multi_block_read_count ought to be 256k/(db default block size).
-
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
-
Re: SAFE, extent sizes and I/O
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.
Last edited by jmodic; 12-11-2003 at 02:00 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|