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

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....

Unknown

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

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

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

Aman....

11gr2 is good . I shall be at Banglore next week, you are at Banglore?

Aman....

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

Aman....

Oh, so you have joined fianlly Oracle Corp is it? Send me your email over OC.

HTH
Aman....