DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: SAFE, extent sizes and I/O

  1. #11
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks sofar guys!

    What is the best way to test this?

    If you can give me some pointers, then I'll test it for myself.

    Is this the best method?:
    1. create tablespaces with 40kb, 256kb, 4MB, 128MB, db_file_multi_block_read_count=32, create some tables, do some SQL, examine explain plan

    2. do the same as 1 but with 256kb, 4MB, 128MB tablespaces

    3. compare explain plans for 1 & 2

    I don't have much experience with examining FTS and index lookups, so I've got some extra homework to do.

    Any extra advice?
    Are there more parameter involved besides db_file_multi_block_read_count and db_block_size?
    Last edited by efrijters; 12-12-2003 at 04:36 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #12
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    What has Plan Got to do with Extent Sizing??
    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"

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    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....
    Where did you get this information from? Because it's the stranges thing I've heard about FTS, and it certanly shows differently on my systems. I've never encountered description of this behavior anywhere in the literature either. Can you show your testcase where it is evident that FTS is actualy performing single block read followed by multiblock reads?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by abhaysk
    What has Plan Got to do with Extent Sizing??
    I was under the impression that wrong extent sizing would lead to more FTS and less index lookups.

    I wanted to see if there was a difference in FTS/index lookups when executing a 'select'-statements in a test environment. (two tablepaces with different extent sizing, but the same tables in each of the ts's)
    I thought that the correct extent size would give more index lookups instead of FTS's.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #15
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Matching the block size and DMBRC to the o/s i/o size is important in order to avoid i/o inefficiency.

    If your small TS extent size was 64kb, then a 1Mb table (having 16 extents) would require 16 i/o operations to FTS.

    If your extent size matches the o/s i/o size of 256kb, then FTS requires 4 i/o operations.

    If your extent size was 1Mb, then you would still need 4 i/o operations.

    If your extent size was 384kb, then you would need 8 i/o operations (because each extent would require an i/o op of 256 + another of 128).


    On the subject of a 40kb extent size, workout exactly how much space you would save over a 256kb extent size. For 100 tiny objects, you would save 21Mb.

    On the subject of FTS's, i believe that Oracle only scans up to the HWM -- if you only have 1 block with data in it, then the whole 256kb wouldn't be scanned anyway. Is that right? Anyone? Bueller?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    On the subject of FTS's, i believe that Oracle only scans up to the HWM -- if you only have 1 block with data in it, then the whole 256kb wouldn't be scanned anyway. Is that right? Anyone? Bueller?
    You Said it...so for small tables, where in the few rows can fit in a block or 2, why do u need to waste an extra 200K+??

    Jurij : I am not sure for what i said in my earlier post but as per some discussion with my colleauges i think once some body has said it...
    Will do a test on it and confirm.

    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"

  7. #17
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    On the subject of a 40kb extent size, workout exactly how much space you would save over a 256kb extent size. For 100 tiny objects, you would save 21Mb.

    On the subject of FTS's, i believe that Oracle only scans up to the HWM -- if you only have 1 block with data in it, then the whole 256kb wouldn't be scanned anyway. Is that right? Anyone? Bueller?
    I did some "what if" trying to see if the standard SAFE could be adapted to make it more efficient in space allocation for small tables, and came to the conclusion that anything I saved with the tiny ones would be lost in the noise of the +/- estimates for the big ones.

    Thats my understanding about HWM too. [blush] I once had an empty table, that had been full, occupying 20Mb that the CBO read with FTS right up to the HWM ! [/blush]

  8. #18
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    You Said it...so for small tables, where in the few rows can fit in a block or 2, why do u need to waste an extra 200K+??
    Is that a joke? Who cares about 200kb? Personally I don't give a crap about it -- I can lose 200kb down the back of a sofa everyday without worrying.

    You just have to do the maths to workout how much extra disk space is being wasted on your schema, and see if it's worth bothering about. I don't think it is.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #19
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Oke, thanks guys...

    One last question about SAFE: at the end of the document are some SELECT's listed that checks if your temp-ts follows safe or not:
    Code:
    To check for UNDO and TEMP tablespaces that do not obey the 
    extent size rules you can run the following query:
    select tablespace_name, initial_extent, next_extent, 
    pct_increase, min_extlen
    from dba_tablespaces t,
    (select tablespace_name tbspc,
    sum(bytes) tbspc_sz,
    count(*) num_files,
    sum(bytes)/sum(blocks) blk_sz
    from dba_data_files
    group by tablespace_name) f
    where
    ( initial_extent < (tbspc_sz - blk_sz * num_files) / 4096
    or initial_extent > (tbspc_sz - blk_sz * num_files) / 1024
    or next_extent != initial_extent
    or pct_increase != 0
    or min_extlen != initial_extent)
    and tablespace_name = tbspc
    and tablespace_name != 'SYSTEM'
    So, this means the TEMP-ts is to be expected to follow SAFE-rules. My SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE are 256KB. I have about 120 concurrent user max. I have spare memory and I was wondering if I could improve sorting by increasing those settings, but when I look at v$sysstat it looks good:
    Code:
    select substr(name,1,15) name,value from v$sysstat
    where lower(name) like '%sorts%'
    
    NAME                 VALUE
    --------------- ----------
    sorts (memory)    32029729
    sorts (disk)         47709
    sorts (rows)    4646383592
    The disk to memory ratio is small. Do you think I could benefit from a larger SORT_AREA_SIZE?
    Last edited by efrijters; 12-14-2003 at 11:05 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  10. #20
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Never mind the last reply. The disk/memory ratio is below 5%, so the SORT_AREA_SIZE doesn't need resizing!!

    Thanks guys!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width