DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TKPROF : High Disk Reads ... Ideas wanted

  1. #1
    Join Date
    Feb 2001
    Posts
    163
    Box 1 : ( NT, 2 cpu, 1.5 G Ram )

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 35 0.01 0.01 0 0 0 0
    Execute 5246 0.47 0.51 0 0 0 0
    Fetch 9947 1628.06 1635.03 892 162709051 0 4701
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 15228 1628.54 1635.55 892 162709051 0 4701

    Box 2: ( NT, 2 cpu , 1 G Ram)

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 40 0.03 0.02 0 0 0 0
    Execute 2908 0.32 0.40 0 0 0 0
    Fetch 5641 3502.22 4858.18 1316417 164089855 0 2733
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 8589 3502.57 4858.60 1316417 164089855 0 2733


    Box 1 is an export & import of data from box 2. The above is a TKPROF output from both boxes for similar operations.

    Explain plan looks the same on both boxes and is running in a rule based environment

    Version : Oracle 8.0.5.1.1
    OS : NT / SP 6 ( Box 1 ) , NT / SP 5 (box 2)
    Disk : simple disk layout

    Any ideas.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How large is buffer cache (db_block_size, db_block_buffers) for bothe instances? If execution plan is realy the same then all I can think of would be much smaller buffer cache in box2.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2001
    Posts
    163
    Box 1's buffer cache is actually 20% less than that of box 2

    Box 2 =~ 50 M

    Box 1 = ~ 40M

    uday

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Just an idea :

    tables with cache option enabled on box1 and disabled on box2 ?

    Gert

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hm, one more thing. You say the data in box1 (the one with fewer disk reads) is the exported version of the data on box2. Is it possible that there has been many deletes in tables in box1 and that there are some full table scans occuring on those "sparse" tables? If this is the case then the same full table scan on table on box2 might cause thousands of disk reads, while on box1 it could scan it in only a couple of reads, because the data was "packed together" during import, so the HWM is set very low comparing to that of box2.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2001
    Posts
    163
    jurij,

    Thats one thing I though of before posting here and I am currently importing the data back after exporting, dropping and recreating the user. I will keep you posted

    BTW, the explain plan, does not show any full scans either.

    Any thing else to look for ?

    UDay

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