adjusting SGA size in Oracle Databases

[Steven E. Protter]

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

[bob hollis]

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]

let me try putting that attachment in again

[bob hollis]

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]

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

[Hein van den Heuvel]

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]

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]

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]

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.