Re: Which Tuning Method? System/Resource Tuning?

Vance,

The biggest bang for the buck on Solaris 8 is using directio with Oracle. If you don't mount your filesystems containing datafile using directio, do it now or you are double buffering all the data and wasting memory and CPU cycles.

Example of /etc/vfstab entry for directio /dev/md/dsk/d120 /dev/md/rdsk/d120 /u08 ufs 2 yes forcedirectio

Also, verify that you have all the SCSI cards in your Sun spread out across all the PCI buses. By default Sun stuffs all the cards on the first PCI bus thus the layout is not optimized from the factory. Look in the E450 manual to see what card slot is on which bus.

Another performance increase can be gained my populating the the SIMM banks in pairs with identical chips so that the memory will interleave. I am not exactly sure how much increase this gives throughput-wise but it should make memory access twice as fast.

And once you do all that, the PIII is still going to blow the doors off the E450 for single threaded type activities like import/exports. I can't believe how fast the commodity PC hardware has become. I built a single processor PIII 850 and it was 300-700% faster than a 4 x 300 Mhz E450 depending on the type operation. Also, this was SCSI vs. IDE too.

The thing you have to keep in mind is that the E450 is about 5 years old and the PIII's and PIV's are only a couple years old. Think 386 vs. pentium if you can remember back that far. The PC technology has definately leapfrogged Sun's old stuff, but Sun supposedly has new stuff out, but I haven't tried any of it yet. Sun has got to get moving or they are going to be in even bigger trouble.

Let us know how it works out....

"Vance Wu" <vwu_at_anacomp.com> wrote in message news:c3d4638a.0111061055.36988298_at_posting.google.com...
> 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 - 23:21:36 CST