Re: Performance issue can't identify

Connie Milliken wrote:

>Although I fear repercussions from Mladen :), I must ask for help because I
>am at my wit's end with this and really need some help......
>
>

OK. I never allow my name to be mentioned in vain, so I'll try responding. I am not yet good
with burning shrubbery, so you'll have to accept this form of answer.

>I inherited a SunOS 5.8 Solaris 64-bit that has 3 Oracle 9.2.0.5 databases
>running on it with a total of 1700MB being used for SGAs. The box has 4GB
>RAM memory. Without any OS configuration changes being made (other than
>kernel parms), the databases were originally installed on the server as it
>came “out of the box”. (Even the simplest things, such as copying large
>files can be slow so I am not convinced the problem is necessarily Oracle).
> However, does anyone see anything here that I don’t see either related to
>Oracle or Unix or can recommend anything else to look into? With very
>little running on any of the databases, this is what it looks like:
>
>oracle:/etc > vmstat 5 4
>procs memory page disk faults cpu
>r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy
>id
>0 0 3 4071688 356568 3 399 335 44 45 0 1 2 0 7 8 227 25 276 6 7
>87
>0 0 4 5319016 1707200 0 2 9 0 0 0 0 2 0 2 2 174 269 151 0 1
>99
>0 0 4 5319008 1707200 0 0 0 0 0 0 0 17 0 2 2 273 276 181 0 1
>99
>0 0 4 5319008 1707200 0 0 0 0 0 0 0 11 0 2 2 233 283 171 0 1
>99
>
># swap -l
>swapfile dev swaplo blocks free
>/dev/dsk/c0t0d0s1 32,1 16 8395184 8058320
>
>* Oracle tunables
>set shmsys:shminfo_shmmax=4294967295
>set shmsys:shminfo_shmmin=1
>set shmsys:shminfo_shmmni=100
>set shmsys:shminfo_shmseg=32
>set semsys:seminfo_semmni=300
>set semsys:seminfo_semmsl=4096
>set semsys:seminfo_semmns=4096
>set semsys:seminfo_semopm=100
>set semsys:seminfo_semvmx=32767
>* end Oracle tunables
>
>

Impressive box, with big and empty memory. As they say, the size doesn't matter, it's the magic
in the wand. So, you've gotta ask yourself one question: what is slow? CPU? Controller? Disk?
One should never "tune an instance", what needs tuning is always an application. The only application
you mentioned is copying, so the first thing to do is to examine "cp" command with truss and
see if there are any errors. Is one of the files being copied on an NFS or samba-mounted FS?
If that doesn't reveal anything, the next step is to create a little program which would copy files
from the place A to the place B, compile it with "-p" (profiling) and examine the execution trace
with "prof". That will tell you where the time is lost. One thing that you didn't mention is I/O subsystem. What kind of disks you have, how many controllers,
what is the speed of the backplane? Do you have anything like I2O? This usually has more to do with copying
then the amount of memory and swapping/paging statistics. People are, for reason unknown to me fascinated
with CPU and memory, completely forgetting about the I/O subsystem. Now we come to the "magic in the wand"
part of the story. The only thing that bosses are asking about the storage is the capacity: how many gigabytes? There are some differences between IDE peripherals with 200GB capacity and FC/AL (Fibre Channel with
Arbitrated Loop) connected disk farms with local, battery backed storage. There are some differences between I2O controllers attached to memory and interrupt driven cards attached to some kind of an intermediate backplane. On many SUN machines, "the intermediate backplane" is called PCI. That type of boxes is the reason why SUN
named its operating system "Slowaris". It's a very appropriate name. My advice is to do an I/O benchmark. You can get yourself IOZone and see how fast can you do I/O. If you need
a minute per disk read, then you can have one million CPU farm with one terahertz each, your system will still be slow. Inadequate I/O subsystem is what killed Sequent which was, at one time, my favorite box. It's all over now, Big Blue devoured Sequent.

OK. You've got your answer. Now, go in peace and fix that @#$%! box.

-- 
Mladen Gogala
Oracle DBA
Ext. 121


--
http://www.freelists.org/webpage/oracle-l