Find Out The Logical Read Per Row Using An Index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Find Out The Logical Read Per Row Using An Index

  1. #1
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78

    Find Out The Logical Read Per Row Using An Index

    Oracle version is 9.2.0.3.0
    O/s is Sun 64 bit ver 9

    My aim is to find out the logical read per row using a particular index. In schema everything is analyzed. So is there any view or process to find out it ?

    Say for example, If an index has blevel is 2, avg_data_blocks_per_key is 335 and avg_leaf_blocks_per_key 662 and distinct key is 20, then can I say that LRPR (logical read per row) is 34 which is cost.I am calculating this figure in the following way

    Cost is (2*662)/20 i.e 67 for traversing each key for that index.
    Cost is 337 since 337 blocks need to be scanned for each key value.

    The no. of records for that key is say 10.

    cost for 1 row of that key for index traversing is 67/10 = 7

    cost for one row of that key from table (337/10) = 38

    Approximately the ultimate cost is 38 + 7 = 45 for each row of that key value using this index.

    I assume that I am not collecting any system stats.So it will not affect the behaviour of optimizer.

    Please help me in this regard. If I am wrong tell me the process.

    Regards

    Deba

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from v$segment_statistics

  3. #3
    Join Date
    Oct 2001
    Location
    Calcutta , India
    Posts
    78
    Hi Pando,

    What u r trying to say is not clear. Will u explain it ?

    Thanks

    Deba

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I dont understand why you are calculating something meaningless such as cost!

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by pando
    I dont understand why you are calculating something meaningless such as cost!
    Hell, I don't understand why you would want to find out the logical read per row using a particular index in the first place. Sounds like working harder not .....
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width