Re: Caching Problem in OPS

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Wed, 10 Mar 1999 09:48:25 -0800
Message-ID: <36E6B068.FDC5286B@us.oracle.com>

OK, let's start off by ensuring I understand this. Machines A and C are clustered with OPS running. Machine B doesn't have OPS. If you take the clustering and OPS out of the picture (i.e. the database is started exclusive on Machine A and a different database is started exclusive on machine B), then we're comparing apples with apples.

So let's ignore the OPS side for the moment, since you stated that you're getting the problem when each database is mounted exclusive. OPS is therefore not involved. It sounds like you've run explain plan, and we end up with the same execution path. That would appear to mean that the same objects are on each database (indexes etc.). Couple of things to look at:

Are the init.ora files EXACTLY the same? Probably not if the boxes are of different sizes. But what is different?
What optimizer are you using?
Are there stats gathered in both cases if you're using CBO?

HTH. Pete

Dicky wrote:

> Sorry for my bad description first. Actually, I've three RS/6000 machines.
> Two of them (A & C) is formed a
> cluster. The following table describes some information about each
> machine:
>
> Machine A Machine B Machine C
> OS AIX 4.1.5 AIX 4.1.5 AIX 4.1.5
> Physical memory 1G 256M 1G
> Paging file 640M 256M 640
> 7.3.4.3 Oracle server Yes Yes Yes
> OPS option Yes No Yes
> SGA size 300M 27M 300M
> DB block size 8K 4K 8K
> Data file format RAW FS RAW
>
> Generally speaking, everything on Machine A is identical to Machine C.
> Parallel server on both machines can startup successfully at the same time.
> And run properly except the problem I described before. So OPS and HACMP
> parts should be OK.
>
> At the first time, Machine A & C was used the same memory initial
> parameters as Machine B used. After the problem discovered, I've changed
> these parameters about 10 times more than the originals, but still no use.
> Then I stopped the OPS instance on Machine C, but again ... My further
> experiment is to remove the OPS instances and database on both machines,
> and reinstall only on Machine A. Afterwards, start the instance on Machine
> A without "Parallel" option and try ... Again still no help and no
> different. So I try to compare the execution plan on Machine A with one on
> Machine B. They are same. What can I do now???
>
> Please help!
>
> Peter Sharman <psharman_at_us.oracle.com> wrote in article
> <36E5615B.ED21090C_at_us.oracle.com>...
> > I'm confused by what you ar trying to achieve with the Parallel Server
> part.
> > If you've installed it on one machine but not the other, you can't use
> OPS.
> > Are the two machines clustered? Parallel Server is only used in a
> clustered
> > configuration to access a single database on shared disk from two or more
> nodes
> > of the cluster. If that's not what you're trying to do, I'd remove OPS
> from
> > the equation first. You will be taking up unnecessary resources with the
> DLM
> > if nothing else.
> >
> > The problem, however, is probably not related to that. We need more
> > information. Is anything else happening on Machine A that would flush
> the data
> > buffer cache? Are there other users doing things? Are the two machines
> using
> > different values for things like db_buffer_cache? How are the disks laid
> out?
> > etc. etc.
> >
> > Dicky wrote:
> >
> > > Hi everybody,
> > >
> > > I have two RS6000 machines.
> > >
> > > Machine A Machine B
> > > --------- ---------
> > > OS AIX 4.1.5 AIX 4.1.5
> > > Phyical memory 1G 256M
> > > Paging file 640M 256M
> > > 7.3.4.3 Oracle server Yes Yes
> > > OPS option Yes No
> > > SGA size 300M 27M
> > >
> > > After restarting the Oracle server on both machines to clear all cache,
> I
> > > run an UNION SQL statment by SQLPLUS program. Due to advanced hardware
> on
> > > Machine A, it runs faster than Machine B (3mins vs. 9mins) in the first
> > > run. However, the result seems to be abnormal when I re-run the same
> > > statment in the same SQLPLUS session. Machine B only takes 1mins to
> finish
> > > in second run, but run time for Machine A remains as it is. Same
> outcome
> > > as re-running more.
> > >
> > > It seems that Machine A cannot take the caching advantage during
> re-run.
> > > I've tried to "startup parallel", "startup exclusive", "startup" Oracle
> > > server with OPS option on Machine A, but still produces same result.
> No
> > > help!
> > >
> > > I would like to know why Oracle server with OPS option is so slow in
> > > re-run. Is it MUST for OPS??
> > >
> > > Thanks in advance!
> >
> > --
> >
> >
> > Regards
> >
> > Pete
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Peter Sharman Email: psharman_at_us.oracle.com
> > WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> > Worldwide Internal Services Education (650)607 0109 (local)
> > San Francisco
> >
> > SQL> select standard_disclaimer, witty_remark
> > 2 from company_requirements;
> >
> > Opinions are mine and do not necessarily reflect those of Oracle
> > Corporation
> >
> > "Controlling application developers is like herding cats."
> > Kevin Loney, ORACLE DBA Handbook
> > "Oh no it's not! It's much harder than that!"
> > Bruce Pihlamae, long term ORACLE DBA
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >

Regards

Pete

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Wed Mar 10 1999 - 11:48:25 CST