Re: Which Tuning Method? System/Resource Tuning?

Vance

Have you SQL traced the "one particular query" on both machines to see if the optimizer is coming up with the same plan? As your two databases are not almost identical, you have a version difference in your Oracle dbms, if your plans are different then an optimizer bug in 8.1.6 could be the cause.

Kind Regards

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"Vance Wu" <vwu_at_anacomp.com> wrote in message news:c3d4638a.0111061455.7f414d6c_at_posting.google.com...
> Ron Reidy <rereidy_at_indra.com> wrote in message
news:<3BE83B6B.21136668_at_indra.com>...
> > Vance Wu wrote:
> > >
> > > Hi Friends,
> > >
> > > I need your help, I have two machines A and B:
> > >
> > > Machine A: SUN Ultra Enterprise 450, 2GM memory single processor,
> > > running
> > > Oracle 8.1.6
> > >
> > > Machine B: Intel Pentium III, 550Mhz, 512MB memory, running Oracle
> > > 8.1.7
> > >
> > > Machine A is supposed to be a lot faster than machine B. Two machines
> > > are
> > > loaded identical databases for development purpose including same
> > > amount of
> > > data, same set of tables, indexes, stored procedures ..
> > >
> > > I have performance problem on machine A, I believe I need
> > > system/resource tuning
> > > instead of SQLstatement/Application tuning because two databases are
> > > almost identical except may be some of the system buffers are slightly
> > > different.
> > >
> > > When I execute one particular query on machine A it took 15 seconds,
> > > but on
> > > machine B, it took less than 2 seconds.
> > >
> > > The following is statistics information from v$sesstat that I captured
> > > on two
> > > machines, when I started with a new session and executed only that one
> > > particular query. The comparison listed contains only different
> > > statistics numbers, the remaining statistic number are the same.
> > >
> > > NAME Machine A Machine B
> > > ----------------------------------- --------- ---------
> > > recursive cpu usage 1160 4
> > > session logical reads 52592 306
> > > CPU used when call started 1163 10
> > > CPU used by this session 1163 10
> > > session uga memory 212764 203800
> > > session uga memory max 221036 212072
> > > messages sent 2 1
> > > session pga memory 577636 389408
> > > session pga memory max 577636 389408
> > > total file opens 2 0
> > > db block gets 213 215
> > > consistent gets 52379 91
> > > physical reads 47939 0
> > > change write time 0 1
> > > redo synch time 6 2
> > > free buffer requested 47941 1
> > > dirty buffers inspected 1 0
> > > free buffer inspected 1 0
> > > prefetched blocks 22 0
> > > redo entries 5 6
> > > redo size 1568 1572
> > > no work - consistent read gets 52346 58
> > > table scan blocks gotten 123 51
> > > table fetch by rowid 1120840 12
> > > bytes sent via SQL*Net to client 2898 3600
> > > bytes received via SQL*Net from client 11403 12506
> > > cursor authentications 0 1
> > > buffer is pinned count 2192418 2
> > > buffer is not pinned count 49388 76
> > >
> > > The big differences between two machines are the "recursive cpu
> > > usage",
> > > "session logical reads", "table fetch by rowid", "buffer is pinned
> > > count",
> > > "buffer is not pinned count", "physical reads" and "consistent gets".
> > > Can anybody guide me to the right tuning method on system A.
> > >
> > > Thank you in advance,
> > > Vance Wu.
> > What OS and patch levels on machine B? Are they both loaded equally?
> > When was the last time statistics were run? Were they run for tables,
> > indexes, and indexed columns?
> >
> > For tuning...
> > Install STATSPACK. Read the doc in $ORACLE_HOME/rdbms/admin about how
> > to install and use this. Bounce the instance and run your app. After a
> > sufficient amount of time (~ 30 mins.), run the report. There are
> > atciles on www.oramag.com that describe how to interpret the report.
> >
> > If you don't want ot run STATSPACK, run utlbstat and utlestat and do the
> > same thing.

>
>