Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

adjusting SGA size in Oracle Databases

bob hollis
Frequent Advisor

adjusting SGA size in Oracle Databases

 
15 REPLIES
Steven E. Protter
Exalted Contributor

Re: adjusting SGA size in Oracle Databases

You probably want to monitor performance during and after this process.

You can use glance if you have a license, or this series of sr scripts that collect information in the background.

See attachment.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

ok - here are the results of your script
I put it in a tar file and compressed it
Can you help me make sense of it?
Thanks!
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

let me try putting that attachment in again
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

I have tried to get the attachments to see if they are ok - I can't get either one?
Do the files have to be ascii?
what about tar or compressed

in the meantime - here is the current sga for one database
SQL> show sga

Total System Global Area 814946792 bytes
Fixed Size 104936 bytes
Variable Size 240345088 bytes
Database Buffers 573440000 bytes
Redo Buffers 1056768 bytes

They are all the same right now - there are 6 databases running on the one machine with 4GB memory
Jean-Luc Oudart
Honored Contributor

Re: adjusting SGA size in Oracle Databases

If you don't use Java in your application, you can reduce your Java pool size.

Also tune your shared pool size. Check attachment

Rgds,
Jean-Luc
fiat lux
Hein van den Heuvel
Honored Contributor

Re: adjusting SGA size in Oracle Databases

While I generally would agree with Steven's recommendation to gather and compare before and after generic performance data I'm afraid it does not apply to your case. It's a good idea, but it will not help you with your immediate problem: how to (re)size the SGA (both buffers and sql area).

I'd strongly encourage you to run Oracle's STATSPACK. Start now, before the split. Statspack can use heuristics to estimate the effect of SGA changes: If you double then you'd save x% IO. If you'd halve then it would likely cost you x% IO. See an example below where the SGA was overconfigured with 8GB buffers and statspack suggest that 1GB would be plenty, only increasing misses a little (this was later confirmed).

Running that report now will a) get you aquainted with its output b) will give you perhpas a better starting point that you currently have. Maybe 800MB was small, maybe it was oversized. c) will give you a system wide reference to calibrate the cost/benefit of that split.

My hunch would be to start with 1/3 for the app, and 2/3 for the data, but obviously this is just a WAG and you milage will vary.

Btw... Why are they considering that split? Surely this was well thought through with plusses and minusses nively lined up in a report probably based on a representative experiment? Surely from that investigation it becomes immediatly clear where to start the new initial sizes! Surely this split is not just done 'for yuks' and well thought through right?! ( ;^).

fwiw,
Hein.
----- sample statspack for oversized db-buffer -----
Buffer Pool Advisory for DB: XXX Instance: xxx End Snap: 6
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate

Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 800 .1 99,250 1.66 851,175
D 1,600 .2 198,500 1.01 519,230
D 2,400 .3 297,750 1.01 517,651
D 3,200 .4 397,000 1.01 517,458
:
D 8,000 1.0 992,500 1.00 513,720

bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

LOL
No - not doing it for yucks!

Actually we do a lot of development adding new plants to our system and we need to "refresh" our test systems with a copy of production data on a regular basis - simply purging the data and then doing an import of production data can take 18 or more hours - days if we have problems! We can't just "clone" the entire production system either, since the application system tables are quite different in development and production - if fact we have 3 different "flavors" of our development database - each can require a refresh. With the data in its own database, we will be able to "clone" just the data using our symmetrix and BCVs in under an hour - much better!
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

How do you use STATSPACK to get estimates of shared pool? I collect statistics on our production system - but I haven't seen that particular feature.
Thanks
Hein van den Heuvel
Honored Contributor

Re: adjusting SGA size in Oracle Databases


The contents of the statspack report depends on the LEVEL used for the snaps. We have been using level 7 lately as that gives activity reports by object. At that level the pool estimate sections are also reported as per partial example in my earlier reply. I don't recall setting anything else (in init.ora?).

We use a trivial script to help with snap + comment:

echo "execute statspack.snap(7,0,'\"$1\"');\n" | sqlplus -s perfstat/perfstat

fwiw,
Hein.
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

It looks like 8.1.7 only lets you do levels 0,5, or 10 - with the default being 5

I'm trying it with 10

execute statspack.snap(i_snap_level=>10);
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

I'm looking through a statspack level 10 report - I don't see anything that looks like a recommendation for changes to the SGA or parts of it. What am I missing?
Hein van den Heuvel
Honored Contributor

Re: adjusting SGA size in Oracle Databases

Well, I switched to 9i about 2 years ago and never looked back (I did have to look forward and wait for patches at times! :-).

The 8i statspack / db engine might not have
this. Sorry, dunno and no time to figure that out just now (I would 'disassemble' spreport from 9i to figure out what tables are used for the pool estimates).

On 8i you may be stuck with the old back of the enveloppe, looking at hit rates, read rates, dirty buffer list sizes, checkpoint volume, and apply some voodoo guesses. Sorry.
Yogeeraj_1
Honored Contributor

Re: adjusting SGA size in Oracle Databases

hi,

with 8.1.7.4, i prefer to run 15-30 minutes max on a statspack report without any parameters.

statspack.snap
...
wait for 15 minutes
...
statspack.snap

analyze.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Alexander M. Ermes
Honored Contributor

Re: adjusting SGA size in Oracle Databases

Hi there.
What about using Oracle Enterprise Manager Diagnostic Pack ?
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
bob hollis
Frequent Advisor

Re: adjusting SGA size in Oracle Databases

I don't have OEM. 8-(

Does anyone know of a white paper that can tell me how to take an 8.1.7 statpack report and calculate what my SGA SHOULD be - or at least give me a starting point. Or a script - even better 8-)

I've been all over metalink and haven't found anything, but I could be missing something.

Thanks!