Re: [Q] Increase SGA, increase performance ???

From: Paul Osborn <posborn_at_menlosoftware.com>
Date: 1996/06/07
Message-ID: <4p9ooe$gce_at_nntp1.best.com>#1/1


porterf_at_primenet.com (Mark E. Porterfield) wrote:

>In article <4p6vgn$3am_at_news2.cais.com>, MCC <duffy_at_cais.cais.com> wrote:
>>We have SUN SParc 1000E (SOlaris 2.5) with Oracle 7.2.3 installed. This
>>SUN UNIX computer have 4 CPU and 128 MB RAM (Total). The SGA currently
> Are you running parallel server?
 

>>setup size "8386600" and the database size is 1.5GB (total datafiles). I
> How is your cache hit ratios for buffers, dictionary and library?

Excellent point. If your buffers are currently handling 90%+ of application requests for data without physical i/o, then increasing the size of the SGA will not help.

Speaking off the cuff, if your server has 128M, the SGA is 8M and your db is 1.5G, increasing the size of the SGA should help if: 1) The OS doesn't start paging part of it out. 2) Your application is anywhere near dynamic.

Generally, to experiment with SGA sizing: 1) Create some benchmark queries from end user/application SQL (ideally the ones that cause the most physical i/o and run most oftern).

2)  Run the benchmarks and record the results.
3)  Increase the size of the SGA by 4M or 8M.
4)  Proportionately increase the size of the db_block and shared_pool
buffers.
5) Rerun the benchmarks and compare the results. 6) Performance impoved "significantly"
        ifTrue:[ Repeat steps 2-6 ]
        ifFalse:[ Reset SGA size to prior setting ]

[warning: self promotion on]

You can find queries to help size the SGA on my web site at: www.menlosoftware.com

[self promotion off]

>>don't know the SGA size is big enough or not? If I increase SGA size,
>>will it increase performance? How big SGA size is good value? Do I need
>>put more RAM or not? Thank you for help.
 

> It is difficult to say what will help you. What you need to do is
> - Check for disk swapping
> - Check Cache Hit Ratios
> - Check Physical Disk IO
> - Check what type of optimization (Rule based/Cost Based)
 

>Oracle has A LOT of things that can effect perfomance including Chained Rows, Free Chunks, extents,
>not enough "Parking slots" in data blocks etc. Give us some more information on what you have
>already done and what you have.
>--
>-------------------------------------------------------------------------------
>Mark E. Porterfield
>porterf_at_primenet.com
>The Deloitte & Touche Consulting Group

Critical to being able to give a more definitave answer to your tuning question is knowing what kind of application(s) you're running on the server. Oracle needs to be tuned differently for data entry, OLTP, or data warehousing applications.

Regards,
Paul

--
posborn_at_menlosoftware.com      Menlo Software
http://www.menlosoftware.com   744 College Ave.
V:415.324.1286                 Menlo Park, CA 94025-5204 USA
Received on Fri Jun 07 1996 - 00:00:00 CEST

Original text of this message