oracle 7 performance

[rustychain]

I have two AIX boxes which have same hardware and o/s level and same Oracle - 7.1.4.1.0. They have both been recently built so I'm testing them out before we use them in anger. I have an import (below) which takes 15 secs on box A and 15 mins on box B. If I add a BUFFER=64000 parm on box B it imports in 15 seconds.

imp user/pass ignore=y \
tables=aaa_copy,\
bbb_copy,\
ccc_copy,\
ddd_copy,\
eee_copy,\
fff_copy,\
ggg_copy,\
hhh_copy\
full=n \
file=blah.dmp

When I run one of our batch processes on both boxes it runs for 10 mins on box A and 45 mins on box B.

I have made the init<sid>.ora file the same on both boxes and when I query v$parameter on both I get identical rows.

I've tried to check other settings and all look the same. Only difference I can see is distributed option is switched on on box A but would that make it faster?

I'm at a loss as to what's so different between the boxes and the only clue I have is the import. Can anyone tell me what else I could check?

 

[SantaMufasa]

If I add a BUFFER=64000 parm on box B it imports in 15 seconds.

Regarding exports and imports, the "BUFFER=..." clause is critical. I, personally, run all of my imports/exports with at least "BUFFER=15000000" (that's 15 million), even on a PC.

Besides the difference of the "distributed option", the other thing to consider if you have performance issues on other queries/batch processes, are:[ul][li]Have you confirmed that indexes on Box B match those on Box A?[/li][li]Are statistics current on all tables on Box B?[/li][/ul]Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[rustychain]

Hi Santa (I dunno, Xmas starts earlier every year! )

I can see that adding the BUFFER fixes the import problem but I'm confused as to why the import works fine on box A without it. Does this mean the default for buffer is different on box A to box B? The docs I've seen say the default is 'system dependent' but don't say what that actually relates to i.e. where in AIX is it picking it up from. Do you know where oracle gets the default? I guess because of this I'm assuming there really is something fundamentally different between the boxes either in unix or oracle and I'm just not seeing it.

As for indexes and stats there are the same number of indexes on both boxes for the application and they both use rule based optimisation which I understand means stats are not used so hopefully it's not either of those that's slowing the batch process.