Re: Oracle question from a Sys. admin, re: Solaris performance

On Fri, 04 Mar 2005 00:34:53 +0100, Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

>On 3 Mar 2005 14:58:58 -0800, tonij67_at_hotmail.com wrote:
>
>>My questions about monitoring performance is from an Oracle standpoint,
>>not Unix. I am not a DBA so I don't know how these instances are
>>running, if they are crawling or screaming I have no idea.
>>
>>I know how to use unix perf. mon tools; sar, mpstat, vmstat, etc...and
>>there is no paging going on. Yet. With 50 instances we often have
>>high CPU utilization but that is not accompanied by I/O wait and/or
>>blocked processes, so so far things are ok. These are 4 proc. V880s
>>with 32 GB of ram using EMC Clarion striped LUNs.
>>
>>I just don't know how far we can push it, there seems to be no end in
>>site as sales is on a rampage to get everybody and their brother to use
>>this product so for all I know we could be doubling ot tripling the
>>amount of instances before we see anything noticable.
>>
>>For example, one system has 53 instances and averages 33% CPU
>>utilization. Disk activity is heavy but there is no I/O wait or
>>paging. Is it a problem now? Not as far as I am concerned, speaking as
>>a system admin. Compare that to an older E450 with 2gb of ram that
>>spends all day at 100% with the run que in double digits and high I/O
>>wait...so far it seems the V880s are up to the task but I have no idea
>>where the breaking point is, and our DBAs cannot tell me either.
>
>
>Get acquainted with Statspack. Take two snapshots on level 5 within 15
>minutes apart.
>Run $ORACLE_HOME/rdbms/admin/spreport
>specifying the 2 last snapshot id's
>
>upload the file to Anjo Kolks site http://www.oraperf.com
>
>The report will exactly tell you what the average transaction doing:
>using CPU, waiting for I/O or whatever.
>
>As soon as it is more waiting than doing anything useful (I have a
>situation where 99.88 percent of the time of a transaction is spent in
>waiting for disk), you have a problem.
>You simply must be I/O bound, or your databases have no end-users at
>all.
>If Mr. Garry's comment is adequate (and they usually are), and you are
>using 8.0 default parameters (which were way too small), your system
>is I/O bound *because* of the SGA being too small. If you are going to
>crank up all of them (which is usually the only thing many admins and
>DBA's 'tuning' a system do), you will have paging and swapping soon
>enough.

Sybrand,

Not that this really answers the OP's question, I'm beginning to wonder if they aren't creating a separate instance for each user .... it was this statement that leads me to that:  

"get everybody and their brother to use this product so for all I know we could be doubling ot tripling the amount of instances before"

Thanks.

• Ed Stevens