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

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 Bakker, Senior Oracle DBA