7 Replies Latest reply on Apr 15, 2010 6:30 AM by Aman....

    4K, 8K & 16k Block size results comparison

    666246
      Hi,

      I created 3 tables T4k ( in 4k blocksize tablepace), T8k in 8k block size and T16k in 16k blocksize respectively.

      All the 3 tables are single column based. All the tables are same sized of 760MB(sum(bytes) from dba_segments).

      I tested my results (Elapsed Time) by running select * from table_name two times i.e First time with Char(2000) and second time with Char(600).

      No. of allocated blocks are same in both the cases of char(600) and char(2000). But with different no. of rows.

      Below are the results in case of Char(2000), different no. of rows to equalize the table size of 760MB.
      T4k=  192000 rows=  00:22:43.70 (elapsed time) = 193595 blocks allocated
      T8k=1288800 rows=   00:35:15.11 = 96413 blocks allocated
      T16k=337000 rows=   00:39:52.63 = 48333 blocks allocated
      Below are the results in case of Char(600)
      T4k= 960000 rows =  00:42:25.99 (elapsed time)
      T8k=1055000 rows= 00:47:07.55
      T16k=1155000 rows=  00:53:26.72
      I have 2 very important questions.

      First is that 4k block size has faster result as compared to 8k and 16k block size results as shown above. Expert opinion required please

      Secondly, If we compare char(2000) results with char(600). There is huge elapsed time difference e.g. in case of T4k, query is taking 22 mins approx, and it's taking 42 mins in case of char(600), taking almost double time.

      No. of allocated data blocks are same, table size is same of 760MB, Why double time difference between char(2000) and char(600). Expert opinion required too please.

      I'll highly appreciate the reasons for these cases.

      Thanks alot.

      ---- Please do not think abt the overall results execution time bcoz i'm doing it on my lap top that has 1gb of ram in total, db=11g standard edition. I know it's v/slow but my concern is comparsion

      Best Regards,
      Kam
      Oracle Certified DBA
        • 1. Re: 4K, 8K & 16k Block size results comparison
          Aman....
          Oh no! This has come up again! I would suggest that you don't assume anything being faster/slower/whatever before reading this post from Jonathan and also, read all the links in that post.
          http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/

          HTH
          Aman....
          • 2. Re: 4K, 8K & 16k Block size results comparison
            You are leaving out so much, it is hard to make any comment.
            Probably you enjoy posting mysteries?

            A few items

            - No platform information was included
            - no file system information was included, no info about the disk block size
            - Do you think the size of the table Oracle deals with is the number of blocks in dba_segments? In a full table scan the only thing that counts is the High Water Mark. Do you think all tables are equally big in terms of High Water Mark? Potentially you are comparing apples and pears
            - As you don`t post table definitions, don´t post actual selects and don´t post explain plans: are you aware of the difference between variable length and fixed length semantics?

            You really need to post much more detail, and get away from the fairy tale domain. Previous experiments have shown the net difference is zero.
            Likely your test is biased or you subscribe to the Multiple Block size Myth.

            ----------------
            Sybrand BAkker
            Senior Oracle DBA
            • 3. Re: 4K, 8K & 16k Block size results comparison
              Hemant K Chitale
              running select * from table_name
              very misleading Test. You have not factored PacketSizes and SQLNet RoundTrips. A SELECT * causes Oracle to send all the rows and columns (ok, single column) to the client, with a default/specified ARRAYSIZE being used but subject to fragmentation into multiple packets (even if your sqlplus client process is on the same host as the database server process).


              Hemant K Chitale
              http://hemantoracledba.blogspot.com
              • 4. Re: 4K, 8K & 16k Block size results comparison
                Pavan Kumar
                Hi Aman,

                Really thankful for posting that *"Jonathan links"* - which was very informative. Let me save it into my library
                Thanks sir and How the work is going on ORACLE 11g Release 2. Let me know when you come across to "Bangalore"

                - Pavan Kumar N
                • 5. Re: 4K, 8K & 16k Block size results comparison
                  Aman....
                  11gr2 is good . I shall be at Banglore next week, you are at Banglore?

                  Aman....
                  • 6. Re: 4K, 8K & 16k Block size results comparison
                    Pavan Kumar
                    Hi Aman,

                    Yes sir, send across a mail to my mail id, my mobile number got changed. Next saturday I will leave to hyderabad and back on next coming monday to Bangalore. let me know prior information from your end, so that I can meet you.

                    - Pavan Kumar N
                    • 7. Re: 4K, 8K & 16k Block size results comparison
                      Aman....
                      Oh, so you have joined fianlly Oracle Corp is it? Send me your email over OC.

                      HTH
                      Aman....