Which Tuning Method? System/Resource Tuning?

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. Received on Tue Nov 06 2001 - 12:55:14 CST