Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

Oracle training Weblogic Book
Easy Oracle Jumpstart
Oracle training & performance tuning books
Burleson Consulting Remote DB Administration
Reply to this topicStart new topic
> Optimum Block Size, Block Size and access time calculation
post Aug 6 2017, 09:58 PM
Post #1


Group: Members
Posts: 1
Joined: 6-August 17
Member No.: 52,105

Hi guys,
I have given this lecture notes but can't understand it. I am sorry to ask a long question but can anyone please explain how the block reads are estimated?
SELECT ProductID, ProductDescription, Postcode, SUM(Quantity) FROM OrderLine INNER JOIN Order WHERE OrderID= Order.ID INNER JOIN Customer WHERE Customer.ID = CustomerID INNER JOIN Product WHERE Product.ID = ProductID GROUP BY ProductID, ProductDescription, Postcode
Base Sizing Data:
Database block size : 2KB
Access time 20msec
Inter-database connection 256Kb/sec
Records : Customer 200 bytes, Product 100 bytes, Order 25 bytes, OrderLine 35 bytes
Single database performance estimate
Total blocks read: 10000 + 2500 + 200 + 50 = 127500
Elapsed time 127500 * 20 / 1000 = 255 secs Say, 4 minutes
Distributed performance estimate
Total blocks read: 10000 + 2500 + 200 + 50 = 127500
Elapsed time components Block local access 12550 * 20 / 1000 = 251 secs Plus transmission time 200 * 2/ 25 = 16 secs And another 16 secs to allow for 70% utilisation Total >285secs, close to 5 minutes
Go to the top of the page
+Quote Post
post Aug 8 2017, 10:41 AM
Post #2

Advanced Member

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13

Hi Azarnegar,

You ask a huge question here! At best, different block sizes will only benefit queries from 0% to 17%.

Please read:


As to your query, we need to start by getting the query execution plan:


Once we the see the data access methods we can determine if migration away from a 2k block size would result in faster execution. First we would see if legitimate full-table scans are present, and then see if it could be parallelized. For the whole procedure, see my book:



The place to start is by examining to I/O access plans in you SQL!

As to estimated block reads, Oracle uses the metadata from dbms_stats plus block size data:

Please read, the estimated blocks read comes from DBA supplied metadata:


Please post the plan!

Also, your estimate of 20ms per read is wat too long! Most storage arrays will deliver data at twice that speed!

Good luck!

Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


Lo-Fi Version Time is now: 20th October 2017 - 04:27 PM