1 2 3 Previous Next 63 Replies Latest reply: Jan 22, 2008 9:52 AM by 314771 RSS

    DB Block Size

    611433
      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
          I forgot to mention that it is a RAC, Oracle 10g R2, OS Solaris 10.
          • 2. Re: DB Block Size
            Maran Viswarayar
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Any suggestions?
                            • 11. Re: DB Block Size
                              155651
                              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
                                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
                                  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
                                    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 Previous Next