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.
The OS on machine B is Linux Redhat 6.2. They are both loaded
equally, and the statistic is targeted on a fresh session exucuted
only for that one particular query. Yes, they were run for tables,
indexes, and indexed columns, but they are identical on both machines.
And machine A (Sun Ultra Enterprise 450, OS is Solaris) is supposed to
be faster.
Received on Tue Nov 06 2001 - 16:55:12 CST
