-
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.
-
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?
-
Box 1's buffer cache is actually 20% less than that of box 2
Box 2 =~ 50 M
Box 1 = ~ 40M
uday
-
Just an idea :
tables with cache option enabled on box1 and disabled on box2 ?
Gert
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|