1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Block size calculation

Discussion in 'General' started by Armita, Aug 6, 2017.

  1. Armita

    Armita Starter

    Likes Received:
    Trophy Points:
    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
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Likes Received:
    Trophy Points:
    Russian Federation
    What you want to calculate ?
    Are you check the value of I/o in the query plan?
  3. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    I'm not sure how these 'estimates' were computed but it appears that the person who did this over-estimated the available space in a 2K block. Basically there will be around 150 bytes of block overhead (block header, etc) leaving 1898 bytes for data. Usually PCTFREE is 10% which would provide 1708 bytes for inserts. There is absolutely no information on how MANY rows are in each table, but at 1708 bytes and 200 bytes per row each block could contain 8 rows (it's actually 8.54 but we can't have a 'partial' row without chaining). This means your 100000 blocks from the Customer table (that's the presumption anyway) contain 800000 rows, all tightly packed with no embedded free space. The computations would be similar for the remaining tables. Of course Oracle keeps track of all of this data and it's available in Statspack reports (which need NO license) and in AWR reports (that do require a license). Obviously your notes are missing some important details which is why you can't fully understand them and, as a result, neither can we.

    This is one where you need to bring this up to your instructor; we really can't help you here.