Oracle Block size Vs OS block size - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: Oracle Block size Vs OS block size

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Hm, I wouldn't say it is irrelevant. It would be (more or less) irrelevant if you had OS block size of 4K and DB_BLOCK_SIZE of 8K, but not the other way arround as the original poster asked.

    If you have 8K OS block size and 4K database block size, then for every single block I/O operation the database requests, the OS will read an extra 4K of data the database hasn't asked for.
    Jurij :

    Ofcourse OS will fetch the extra 4K for single block I/O request, but I intended to say "Irrelevent" with respect to Performance Issue, because OS always fetechs in multilples of it Block Size limited my Max I/O per fetch.

    So have DB Block_Size 8K or 4K, you will get 8K blocks per request (considering 8K block size as said by poster).
    So, the Q of performance is Irrelevent, coz in any case you will get non requested data as well.

    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"

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Could it be that your extent sizes are limiting the size of the reads?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #13
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    UNIFORM Initial/Next sizes for both tablespace, and therefore segments, are 4MB.

    PCTFREE 0

    32K (128 Blocks per extent)
    8K (512 Blocks per extent)

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.3.0 - Production

    alter session set db_file_multiblock_read_count=64;

    Session altered.

    set timi on
    Alter session set events '10046 trace name context forever, level 12';

    Session altered.

    8K Default Tablespace

    SQL> select count(*) from test
    where snapshot_id_c = 'test';


    Elapsed: 00:00:29.86

    WAIT #1: nam='db file scattered read' ela= 14379 p1=18 p2=200913 p3=64
    WAIT #1: nam='db file scattered read' ela= 14212 p1=18 p2=200977 p3=64
    WAIT #1: nam='db file scattered read' ela= 17312 p1=18 p2=201041 p3=64

    32K non-Default Tablespace

    SQL> select count(*) from test
    where snapshot_id_c = 'test';

    Elapsed: 00:00:30.01

    WAIT #1: nam='db file scattered read' ela= 11546 p1=11 p2=87701 p3=16
    WAIT #1: nam='db file scattered read' ela= 11580 p1=11 p2=87717 p3=16
    WAIT #1: nam='db file scattered read' ela= 11550 p1=11 p2=87733 p3=16
    Last edited by grjohnson; 08-12-2003 at 09:25 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    To find out OS block size, login as sys and run the following query:
    select max(lebsz) from sys.x$kccle;

    If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
    When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

    Tamil

  5. #15
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hmmm.

    Do the "elapsed" times not suggest that you are spending less time waiting on the 32K reads than on the 8k reads? How does that correlate to the longer elapsed time for the 32k read?

    Confused.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #16
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    To find out OS block size, login as sys and run the following query:
    select max(lebsz) from sys.x$kccle;

    If Veritas is used and block size is 8k, then it is advisable to set your DB_BLOCK_SIZE to 8K. The reason is simple: no extra IO.
    When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

    Tamil
    I'd like to see some supporting evidence -- i don't believe that any overhead that might be associated with this block reconstruction would be enough to offset the loss in i/o performance from larger block sizes.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #17
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by tamilselvan
    When they are NOT equal then OS has to do reconstruct the data block in the memory. This will impair performance.

    Tamil
    Reconstruct?? Why and How?

    BTW, where do we get info of the X$ tables?


    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"

  8. #18
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by slimdave
    Hmmm.

    Confused.
    Retested... on a quieter DB

    SQL> Alter session set events '10046 trace name context forever, level 12';

    Session altered.

    SQL> select count(*) from test
    where snapshot_id_c = 'test';

    Elapsed: 00:00:31.40

    WAIT #1: nam='db file scattered read' ela= 50510 p1=9 p2=379403 p3=128
    WAIT #1: nam='db file scattered read' ela= 50602 p1=9 p2=379531 p3=128
    WAIT #1: nam='db file scattered read' ela= 37283 p1=9 p2=379659 p3=128
    WAIT #1: nam='db file scattered read' ela= 37624 p1=9 p2=379787 p3=126
    WAIT #1: nam='db file scattered read' ela= 56332 p1=10 p2=418315 p3=128
    WAIT #1: nam='db file scattered read' ela= 30759 p1=10 p2=418443 p3=128
    WAIT #1: nam='db file scattered read' ela= 37364 p1=10 p2=418571 p3=128
    WAIT #1: nam='db file scattered read' ela= 30100 p1=10 p2=418699 p3=126
    WAIT #1: nam='db file scattered read' ela= 68661 p1=9 p2=379915 p3=128
    WAIT #1: nam='db file scattered read' ela= 41933 p1=9 p2=380043 p3=128
    WAIT #1: nam='db file scattered read' ela= 42122 p1=9 p2=380171 p3=128
    WAIT #1: nam='db file scattered read' ela= 39265 p1=9 p2=380299 p3=126
    WAIT #1: nam='db file scattered read' ela= 56861 p1=10 p2=418827 p3=128
    WAIT #1: nam='db file scattered read' ela= 31159 p1=10 p2=418955 p3=128
    WAIT #1: nam='db file scattered read' ela= 36711 p1=10 p2=419083 p3=128
    WAIT #1: nam='db file scattered read' ela= 32579 p1=10 p2=419211 p3=126
    .
    .
    .

    STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401)

    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 5.34 30.65 82527 82547 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 5.34 30.65 82527 82547 0 1

    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 58 (FRED)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 SORT AGGREGATE (cr=82547 r=82527 w=0 time=30658418 us)
    0 TABLE ACCESS FULL OBJ#(30464) (cr=82547 r=82527 w=0 time=30658401 us)


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: FIRST_ROWS
    1 SORT (AGGREGATE)
    0 TABLE ACCESS (FULL) OF 'TEST'


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    db file scattered read 650 0.07 28.21
    SQL*Net message from client 2 52.87 52.87

    SQL> exit

    SQL> Alter session set events '10046 trace name context forever, level 12';

    Session altered.


    SQL> select count(*) from test
    where snapshot_id_c = 'test';

    Elapsed: 00:00:26.72

    WAIT #1: nam='db file scattered read' ela= 34521 p1=12 p2=2949 p3=32
    WAIT #1: nam='db file scattered read' ela= 31547 p1=12 p2=2981 p3=32
    WAIT #1: nam='db file scattered read' ela= 22113 p1=12 p2=3013 p3=32
    WAIT #1: nam='db file scattered read' ela= 23875 p1=12 p2=3045 p3=32
    WAIT #1: nam='db file scattered read' ela= 52675 p1=11 p2=2566 p3=32
    WAIT #1: nam='db file scattered read' ela= 38911 p1=11 p2=2598 p3=32
    WAIT #1: nam='db file scattered read' ela= 35585 p1=11 p2=2630 p3=32
    WAIT #1: nam='db file scattered read' ela= 36678 p1=11 p2=2662 p3=31
    WAIT #1: nam='db file scattered read' ela= 37421 p1=11 p2=2693 p3=32
    WAIT #1: nam='db file scattered read' ela= 36737 p1=11 p2=2725 p3=32
    WAIT #1: nam='db file scattered read' ela= 40435 p1=11 p2=2757 p3=32
    WAIT #1: nam='db file scattered read' ela= 33564 p1=11 p2=2789 p3=32
    WAIT #1: nam='db file scattered read' ela= 47267 p1=11 p2=2822 p3=32
    WAIT #1: nam='db file scattered read' ela= 39201 p1=11 p2=2854 p3=32
    WAIT #1: nam='db file scattered read' ela= 34944 p1=11 p2=2886 p3=32
    .
    .
    .
    STAT #1 id=2 cnt=0 pid=1 pos=1 obj=30464 op='TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640)

    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 4.05 26.08 20078 20097 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 4.05 26.08 20078 20097 0 1

    Misses in library cache during parse: 1
    Optimizer goal: FIRST_ROWS
    Parsing user id: 58 (FRED)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 SORT AGGREGATE (cr=20097 r=20078 w=0 time=26080652 us)
    0 TABLE ACCESS FULL OBJ#(30464) (cr=20097 r=20078 w=0 time=26080640 us)


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: FIRST_ROWS
    1 SORT (AGGREGATE)
    0 TABLE ACCESS (FULL) OF 'TEST


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    db file scattered read 631 0.06 24.22
    SQL*Net message from client 2 47.37 47.37

    32K block size performed slightly quicker. But DMBC still as expected.

    Incidently, for default Buffer_cache we can look at v$bh table for objects in the cache, how can we see what's in the non default cache?
    Last edited by grjohnson; 08-12-2003 at 11:26 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #19
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    it looks like you might get something from upping the DMBC there. What value were you using? Might like to try raising it by a factor of 4 and see if it eliminates some of those waits.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #20
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'm giving all it's got captain... (in my worst scotty impersonation)

    DB_FILE_MULTIBLOCK_READ_COUNT = 128 (is the maximum my os can handle)

    Incidently, I just re-ran the test's again and they return in the following:

    8K - Elapsed: 00:00:31.17
    32K - Elapsed: 00:00:40.37


    The DB is pretty quiet, but the server get's a bit of a hammering from time to time from other DB's. So, I'm still not convinced of a better read performance on larger non-default blocks sizes.

    I was also testing indexes and tables in larger non-default DB's this morning... which was also eyebrow raising, with the blocks of an index scan going into the default db_cache... not the db_32k_cache as I'd have expected.... more testing to go on that one.
    Last edited by grjohnson; 08-12-2003 at 12:01 PM.
    OCP 8i, 9i DBA
    Brisbane Australia

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