TKPROF : High Disk Reads ... Ideas wanted

[uday]

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.

[jmodic]

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.

[uday]

Box 1's buffer cache is actually 20% less than that of box 2

Box 2 =~ 50 M

Box 1 = ~ 40M

uday

[denevge]

Just an idea :

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

Gert

[jmodic]

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.

[uday]

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