Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
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
azarnegar
post Aug 6 2017, 09:58 PM
Post #1


Newbie
*

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?
Query:
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
Thanks
Go to the top of the page
 
+Quote Post
burleson
post Aug 8 2017, 10:41 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,492
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:

http://www.dba-oracle.com/s_oracle_block_size.htm

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

http://www.dba-oracle.com/t_explain_plan.htm

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:


http://www.rampant-books.com/book_1001_oracle_sql_tuning.htm

http://www.rampant-books.com/book_0214_ora...ence_3rd_ed.htm

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:

http://www.dba-oracle.com/art_builder_cpu_io.htm

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: 22nd August 2017 - 02:17 AM