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 18.104.22.168.1
OS : NT / SP 6 ( Box 1 ) , NT / SP 5 (box 2)
Disk : simple disk layout
05-29-2001, 03:51 PM
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.
05-29-2001, 03:53 PM
Box 1's buffer cache is actually 20% less than that of box 2
Box 2 =~ 50 M
Box 1 = ~ 40M
05-29-2001, 03:55 PM
Just an idea :
tables with cache option enabled on box1 and disabled on box2 ?
05-29-2001, 03:58 PM
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.
05-29-2001, 04:06 PM
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.