This discussion is archived
1 2 3 5 Previous Next 63 Replies Latest reply: Jan 22, 2008 7:52 AM by 314771 RSS

DB Block Size

611433 Newbie
Currently Being Moderated
Hi all, my current db block size is 8k, I want to increase it to 16k. Do I have to install the whole Database again? I work in a live envioerment, can some one tell me how this can be done?
  • 1. Re: DB Block Size
    611433 Newbie
    Currently Being Moderated
    I forgot to mention that it is a RAC, Oracle 10g R2, OS Solaris 10.
  • 2. Re: DB Block Size
    Maran Viswarayar Pro
    Currently Being Moderated
    MAy i know..Whats the reason to upgrade to 16K.

    There is no concept like Resinatlll the Database...


    IF you want to change the size of block size create a DB with 16K

    1.Create a DB with 16K
    2. Export /Import

    No other way to change the block size..

    you can use datatransfer to move data from 8k database to 16 K database but still you need to Create a Fresh DB
  • 3. Re: DB Block Size
    153119 Pro
    Currently Being Moderated
    Why do you want to increase your db block size?
    Because 'bigger is better'?
    This is likely going to have detrimental effects (as you will have more records in a block, you will have potentially have more sessions modifying 1 block, resulting in more 'buffer busy waits').
    Also increasing the blocksize might have a detrimental effect on Cache Fusion, a feature of RAC, as you need to ship bigger blocks to other nodes in the cluster.

    And yes, you do need to recreate the whole database.
    Exp the database, recreate the database, import.

    I would strongly recommend you set up a test environment before even premeditating this change, and I would also not listen to self-proclaimed gurus in this forum, who advocate 'bigger is always better'.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: DB Block Size
    611433 Newbie
    Currently Being Moderated
    I ran statspack and found out that I have high update activity with 4,070.0 db block changes per second. So I am thinking about changing it.

    Any suggestions?

    Thanks for your reply though.
  • 5. Re: DB Block Size
    153119 Pro
    Currently Being Moderated
    The update activity is a function of your application, not of the block size.
    You will have the same number of changes in less blocks.
    You consider to change job then?

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 6. Re: DB Block Size
    611433 Newbie
    Currently Being Moderated
    Does this mean that the Application is responsible for the db changes?
    Do I have to ask the application user to sort this problem?
  • 7. Re: DB Block Size
    Maran Viswarayar Pro
    Currently Being Moderated
    I ran statspack and found out that I have high update activity with 4,070.0 db block changes per second. So I am thinking about changing it.
    Absolute madness.....With just a single parameter you may end up in awful lo tof problems....
  • 8. Re: DB Block Size
    611433 Newbie
    Currently Being Moderated
    If you go throught this report you will notice that its not just one issue.


    Elapsed: 130.65 (min) 7,839 (sec)
    DB Time: 10,084.54 (min) 605,072.56 (sec)
    Cache: 9,152 MB
    Block Size: 8,192 bytes
    Transactions: 55.57 per second


    Performance Summary
    Physical Reads: 358/sec MB per second: 2.8 MB/sec
    Physical Writes: 573/sec MB per second: 4.48 MB/sec
    Single-block Reads: 325.89/sec Avg wait: 75.08 ms
    Multi-block Reads: 3.03/sec Avg wait: 95.95 ms
    Tablespace Reads: 5,114/sec Writes: 6,333/sec


    Top 5 Events
    Event Percentage of Total Timed Events
    db file sequential read 31.7 %
    gc buffer busy 16.1 %
    log file sync 11.0 %
    db file parallel write 6.9 %
    gc current block lost 6.8 %




    Tablespace I/O Stats
    Tablespace Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
    FMS_PROFILE_CFD_DATA 2,917 61.8 1.5 2,605 53% 48.22%
    MNE_E_COMBINED_INDEX 803 57.5 1 2,451 25% 28.42%
    MNE_E_COMBINED_DATA 312 89.5 5.4 332 48% 5.62%
    FMS_CUSTOMER_DATA 576 73.4 1.4 1 100% 5.04%
    MNE_E_RATED_INDEX 145 49.9 1.4 279 34% 3.71%
    UNDOTBS1 9 97.5 1 249 4% 2.26%
    FMS_PROFILE_CFD_INDEX 52 74.4 1.1 205 20% 2.25%
    FMS_CUSTOMER_INDEX 139 74.4 1 41 77% 1.57%
    MNE_E_RATED_DATA 51 58.1 2.9 103 33% 1.35%


    Tablespace I/O Stats
    Tablespace Wait (s) Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO
    FMS_PROFILE_CFD_DATA 2,674,748 2,917 61.8 1.5 2,605 53% 48.22%
    MNE_E_COMBINED_INDEX 1,466,869 803 57.5 1 2,451 25% 28.42%
    MNE_E_COMBINED_DATA 451,485 312 89.5 5.4 332 48% 5.62%
    FMS_CUSTOMER_DATA 332,127 576 73.4 1.4 1 100% 5.04%
    MNE_E_RATED_INDEX 165,982 145 49.9 1.4 279 34% 3.71%
    UNDOTBS1 197,541 9 97.5 1 249 4% 2.26%
    FMS_PROFILE_CFD_INDEX 150,431 52 74.4 1.1 205 20% 2.25%
    FMS_CUSTOMER_INDEX 104,978 139 74.4 1 41 77% 1.57%
    MNE_E_RATED_DATA 70,205 51 58.1 2.9 103 33% 1.35%


    Load Profile
    Logical reads: 19,856/s Parses: 449/s
    Physical reads: 358/s Hard parses: 0.39/s
    Physical writes: 573/s Transactions: 55.57/s
    Rollback per transaction: 1.82% Buffer Nowait: 99.86%


    Instance Efficiency
    Buffer Hit: 98.15% In-memory Sort: 100%
    Library Hit: 99.87% Latch Hit: 99.47%
    Memory Usage: 70.57% Memory for SQL: 64.21%



    Wait Events
    Event Waits Wait Time (s) Avg Wait (ms) Waits/txn
    db file sequential read 2,554,688 191,808 75 5.9
    gc buffer busy 219,294 97,344 444 0.5
    log file sync 449,261 66,743 149 1.0
    db file parallel write 2,450,434 41,785 17 5.6
    gc current block lost 35,604 40,915 1149 0.1
    gc current block busy 72,032 20,077 279 0.2
    log buffer space 49,317 16,296 330 0.1
    gc cr block busy 41,708 13,824 331 0.1
    gcs log flush sync 3,005,491 12,466 4 6.9
    gc current block 2-way 1,854,681 11,906 6 4.3



    Instance Activity Stats
    Statistic Total per Second per Trans
    SQL*Net roundtrips to/from client 9,673,622 1,234.0 22.2
    consistent gets 108,421,117 13,831.0 248.9
    consistent gets - examination 26,308,389 3,356.1 60.4
    db block changes 31,904,605 4,070.0 73.2
    execute count 6,941,063 885.5 15.9
    parse count (hard) 3,065 0.4 0.0
    parse count (total) 3,519,704 449.0 8.1
    physical reads 2,811,918 358.7 6.5
    physical reads direct 615,701 78.5 1.4
    physical writes 4,493,264 573.2 10.3
    physical writes direct 3,040 0.4 0.0
    redo writes 245,807 31.4 0.6
    session cursor cache hits 3,476,421 443.5 8.0
    sorts (disk) 0 0.0 0.0
    sorts (memory) 81,330 10.4 0.2
    table fetch continued row 142,219 18.1 0.3
    table scans (long tables) 11,494 1.5 0.0
    table scans (short tables) 83,594 10.7 0.2
    workarea executions - onepass 0 0.0 0.0



    Latch Activity
    Latch Get Requests % Get Miss % NoWait Miss Wait Time (s)
    KCL gc element parent la 43,810,664 0.4 1.5 139
    cache buffers chains 392,816,346 0.9 0.0 4
    cache buffers lru chain 12,226,774 0.2 0.3 1
    ges resource hash list 11,590,555 0.2 0.1 1
    parallel query alloc buf 69,238 9.9 2
    session allocation 1,529,592 1.3 1



    Buffer Pool Advisory
    Current: 66,369,000 disk reads
    Optimized: 50,077,000 disk reads
    Improvement: 24.55% fewer
    The Oracle buffer cache advisory utility indicates 66,369,000 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 50,077,000, a 24.55% decrease.


    PGA Memory Advisory
    Current: 76% cache hit
    Optimized: 100% cache hit
    Improvement: 24% more
    The PGA advisory utility indicates 76% PGA cache hit ratio during the sample interval. Oracle estimates that doubling the PGA size (by increasing pga_aggregate_target) will increase the PGA cache hit ratio to 100%, a 24% increase.


    Init.ora Parameters
    Parameter Value
    db_block_size 8,192
    db_file_multiblock_read_count 16
    pga_aggregate_target 5.59GB
    optimizercost_model cpu
    session_cached_cursors 50
    cursor_sharing exact
  • 9. Re: DB Block Size
    Maran Viswarayar Pro
    Currently Being Moderated
    Elapsed: 130.65 (min) 7,839 (sec) 
    DB Time: 10,084.54 (min) 605,072.56 (sec) 
    Cache: 9,152 MB 
    Block Size: 8,192 bytes 
    Transactions: 55.57 per second 
    
    
    Performance Summary 
    Physical Reads: 358/sec MB per second: 2.8 MB/sec 
    Physical Writes: 573/sec MB per second: 4.48 MB/sec 
    Single-block Reads: 325.89/sec Avg wait: 75.08 ms 
    Multi-block Reads: 3.03/sec Avg wait: 95.95 ms 
    Tablespace Reads: 5,114/sec Writes: 6,333/sec 
    
    
    Top 5 Events 
    Event Percentage of Total Timed Events 
    db file sequential read 31.7 % 
    gc buffer busy 16.1 % 
    log file sync 11.0 % 
    db file parallel write 6.9 % 
    gc current block lost 6.8 % 
    
    
    
    
    Tablespace I/O Stats 
    Tablespace Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO 
    FMS_PROFILE_CFD_DATA 2,917 61.8 1.5 2,605 53% 48.22% 
    MNE_E_COMBINED_INDEX 803 57.5 1 2,451 25% 28.42% 
    MNE_E_COMBINED_DATA 312 89.5 5.4 332 48% 5.62% 
    FMS_CUSTOMER_DATA 576 73.4 1.4 1 100% 5.04% 
    MNE_E_RATED_INDEX 145 49.9 1.4 279 34% 3.71% 
    UNDOTBS1 9 97.5 1 249 4% 2.26% 
    FMS_PROFILE_CFD_INDEX 52 74.4 1.1 205 20% 2.25% 
    FMS_CUSTOMER_INDEX 139 74.4 1 41 77% 1.57% 
    MNE_E_RATED_DATA 51 58.1 2.9 103 33% 1.35% 
    
    
    Tablespace I/O Stats 
    Tablespace Wait (s) Read/s Av Rd(ms) Blks/Rd Writes/s Read% % Total IO 
    FMS_PROFILE_CFD_DATA 2,674,748 2,917 61.8 1.5 2,605 53% 48.22% 
    MNE_E_COMBINED_INDEX 1,466,869 803 57.5 1 2,451 25% 28.42% 
    MNE_E_COMBINED_DATA 451,485 312 89.5 5.4 332 48% 5.62% 
    FMS_CUSTOMER_DATA 332,127 576 73.4 1.4 1 100% 5.04% 
    MNE_E_RATED_INDEX 165,982 145 49.9 1.4 279 34% 3.71% 
    UNDOTBS1 197,541 9 97.5 1 249 4% 2.26% 
    FMS_PROFILE_CFD_INDEX 150,431 52 74.4 1.1 205 20% 2.25% 
    FMS_CUSTOMER_INDEX 104,978 139 74.4 1 41 77% 1.57% 
    MNE_E_RATED_DATA 70,205 51 58.1 2.9 103 33% 1.35% 
    
    
    Load Profile 
    Logical reads: 19,856/s Parses: 449/s 
    Physical reads: 358/s Hard parses: 0.39/s 
    Physical writes: 573/s Transactions: 55.57/s 
    Rollback per transaction: 1.82% Buffer Nowait: 99.86% 
    
    
    Instance Efficiency 
    Buffer Hit: 98.15% In-memory Sort: 100% 
    Library Hit: 99.87% Latch Hit: 99.47% 
    Memory Usage: 70.57% Memory for SQL: 64.21% 
    
    
    
    Wait Events 
    Event Waits Wait Time (s) Avg Wait (ms) Waits/txn 
    db file sequential read 2,554,688 191,808 75 5.9 
    gc buffer busy 219,294 97,344 444 0.5 
    log file sync 449,261 66,743 149 1.0 
    db file parallel write 2,450,434 41,785 17 5.6 
    gc current block lost 35,604 40,915 1149 0.1 
    gc current block busy 72,032 20,077 279 0.2 
    log buffer space 49,317 16,296 330 0.1 
    gc cr block busy 41,708 13,824 331 0.1 
    gcs log flush sync 3,005,491 12,466 4 6.9 
    gc current block 2-way 1,854,681 11,906 6 4.3 
    
    
    
    Instance Activity Stats 
    Statistic Total per Second per Trans 
    SQL*Net roundtrips to/from client 9,673,622 1,234.0 22.2 
    consistent gets 108,421,117 13,831.0 248.9 
    consistent gets - examination 26,308,389 3,356.1 60.4 
    db block changes 31,904,605 4,070.0 73.2 
    execute count 6,941,063 885.5 15.9 
    parse count (hard) 3,065 0.4 0.0 
    parse count (total) 3,519,704 449.0 8.1 
    physical reads 2,811,918 358.7 6.5 
    physical reads direct 615,701 78.5 1.4 
    physical writes 4,493,264 573.2 10.3 
    physical writes direct 3,040 0.4 0.0 
    redo writes 245,807 31.4 0.6 
    session cursor cache hits 3,476,421 443.5 8.0 
    sorts (disk) 0 0.0 0.0 
    sorts (memory) 81,330 10.4 0.2 
    table fetch continued row 142,219 18.1 0.3 
    table scans (long tables) 11,494 1.5 0.0 
    table scans (short tables) 83,594 10.7 0.2 
    workarea executions - onepass 0 0.0 0.0 
    
    
    
    Latch Activity 
    Latch Get Requests % Get Miss % NoWait Miss Wait Time (s) 
    KCL gc element parent la 43,810,664 0.4 1.5 139 
    cache buffers chains 392,816,346 0.9 0.0 4 
    cache buffers lru chain 12,226,774 0.2 0.3 1 
    ges resource hash list 11,590,555 0.2 0.1 1 
    parallel query alloc buf 69,238 9.9 2 
    session allocation 1,529,592 1.3 1 
    
    
    
    Buffer Pool Advisory 
    Current: 66,369,000 disk reads 
    Optimized: 50,077,000 disk reads 
    Improvement: 24.55% fewer 
    The Oracle buffer cache advisory utility indicates 66,369,000 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 50,077,000, a 24.55% decrease. 
    
    
    PGA Memory Advisory 
    Current: 76% cache hit 
    Optimized: 100% cache hit 
    Improvement: 24% more 
    The PGA advisory utility indicates 76% PGA cache hit ratio during the sample interval. Oracle estimates that doubling the PGA size (by increasing pga_aggregate_target) will increase the PGA cache hit ratio to 100%, a 24% increase. 
    
    
    Init.ora Parameters 
    Parameter Value 
    db_block_size 8,192 
    db_file_multiblock_read_count 16 
    pga_aggregate_target 5.59GB 
    _optimizer_cost_model cpu 
    session_cached_cursors 50 
    cursor_sharing exact 
  • 10. Re: DB Block Size
    611433 Newbie
    Currently Being Moderated
    Any suggestions?
  • 11. Re: DB Block Size
    155651 Newbie
    Currently Being Moderated
    In 9i and above it is possible to create tablespaces with multiple blocksizes. Create tablespaces with 2K and 16K block sizes. Move small tables with random DML opeartions or single inserts to 2K tablespaces. Tables with lots of reads and less writes can be placed in 16K tablespace. If the default block size is 16K then all system tables and statistics information which has constant writes will also be in large block size and this can increase write IO.

    See this link for more information
    http://www.myoracleguide.com/MultipleBlocksizes.htm
  • 12. Re: DB Block Size
    sgalaxy Journeyer
    Currently Being Moderated
    Since your first wait event is db_file_sequential_read... and you think of changing the block size.... you may find the following useful....
    Re: Db file sequential read

    Greetings...
    Sim
  • 13. Re: DB Block Size
    153119 Pro
    Currently Being Moderated
    As has been discussed many times, Oracle didn't implement this to facilitate performance, but to allow tablespaces with different blocksizes to be plugged in in one database.
    Using multiple blocksizes will disable autotuning the cache, and likely not goin to help at all.
    There are few people who believe this helps, regrettably they spam their ideas with fervor.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 14. Re: DB Block Size
    chris_c Journeyer
    Currently Being Moderated
    from the waits section you top waits are.
    Event                    Waits          Wait Time (s)     Avg Wait (ms)     Waits/txn
    db file sequential read      2,554,688     191,808          75          5.9
    gc buffer busy                219,294          97,344          444          0.5
    log file sync                449,261          66,743          149          1
    db file parallel write           2,450,434     41,785          17          5.6
    gc current block lost           35,604          40,915          1149          0.1
    gc current block busy           72,032          20,077          279          0.2
    log buffer space           49,317          16,296          330          0.1
    gc cr block busy           41,708          13,824          331          0.1
    gcs log flush sync           3,005,491     12,466          4          6.9
    gc current block 2-way           1,854,681     11,906          6          4.3
    before you think about messing about with blocksizes, take a look at the SQL sections of the report and figure out what sql is most expensive in terms of IO and buffer gets.
    also there are a number of waits related to the global cache, does the application update tables with a small number of rows frequently? if you don't understand how the application is using the database then changing the blocksize could make things worse/better/ not change anything at all...
1 2 3 5 Previous Next