Tuesday, January 16

Balancing SGA in Oracle 9i

Now with 10g having sga_target for automatically managing your SGA memory size some people get lazy and just forget about SGA. But most of us still have 9i databases to maintain.

Time for a quick overview on how to reduce disk i/o with the help of db_buffers.

I've got a Sun v480 with 4Gb RAM running one instance, it's not under a lot of load but could do with some tuning.
Lets look how our current SGA settings looks:
SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------
sga_max_size big integer 2283246744

SQL> select * from v$sgastat order by pool,bytes;

POOL NAME BYTES
----------- -------------------------- ----------
java pool free memory 67108864
shared pool trigger source 152
shared pool fixed allocation callback 496
shared pool trigger defini 576
shared pool trigger inform 920
shared pool PLS non-lib hp 2088
shared pool joxs heap init 4240
shared pool KQR S SO 5416
shared pool table definiti 18984
shared pool PX subheap 28096
shared pool session heap 29560
shared pool KGK heap 33368
shared pool DG Broker heap 39200
shared pool MTTR advisory 388024
shared pool errors 390856
shared pool sessions 905840
shared pool message pool freequeue 940944
shared pool sim memory hea 1014808
shared pool KSXR receive buffers 1034000
shared pool FileIdentificatonBlock 1791824
shared pool parameters 1827072
shared pool PL/SQL DIANA 1960192
shared pool 1M buffer 2098176
shared pool Checkpoint queue 2622720
shared pool KQR M PO 2885104
shared pool dictionary cache 3229952
shared pool KQR L PO 3372488
shared pool event statistics per sess 3762720
shared pool KQR L SO 5260312
shared pool KGLS heap 5424464
shared pool KQR M SO 6135256
shared pool FileOpenBlock 11813536
shared pool PL/SQL MPCODE 22044808
shared pool miscellaneous 25966760
shared pool library cache 66948552
shared pool sql area 166511368
shared pool free memory 248709688
fixed_sga 734360
log_buffer 787456
buffer_cache 1073741824

40 rows selected.
Ok, we can see that we do have quite some free memory
in the shared pool, what should we do with that?
from looking at iostat and wait statistics I've noticed that the disk
subsystem (a simple Sun 3310 in this case) is getting a bit hammered sometimes.
Lets see how the db cache is doing.
SQL> select size_factor, size_for_estimate, estd_physical_read_factor
2 from v$db_cache_advice order by size_factor;

SIZE_FACTOR SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR
----------- ----------------- -------------------------
.0938 96 4344.0852
.1875 192 163.0333
.2813 288 23.782
.375 384 22.5502
.4688 480 20.6444
.5625 576 10.4806
.6563 672 1.4957
.75 768 1.0948
.8438 864 1.0367
.9375 960 1.0102
1 1024 1
1.0313 1056 .9999
1.125 1152 .813
1.2188 1248 .7185
1.3125 1344 .5829
1.4063 1440 .536 <- Lets aim for this
1.5 1536 .5196
1.5938 1632 .5139
1.6875 1728 .5079
1.7813 1824 .5055
1.875 1920 .5043

21 rows selected.

Right, from looking in the v$db_chace_advice view we can determine that a few hundred Mb more of cache would be a good idea. There is quite a lot of free memory at the OS level, so we can probably increase the sga_max_size with 250Mb as well.
-- Reduce the shared pool with 150Mb.
SQL> alter system set shared_pool=379584512 scope=spfile

System altered.

-- Add max size with 250Mb
SQL> alter system set sga_max_size=2545390744 scope=spfile;

System altered.

-- Add the 250Mb we added + the 150Mb we reduced from the shared pool.
SQL> alter system set db_cache_size=1493172224 scope=spfile;

System altered.

-- Restart Oracle
SQL> shutdown immediate
...
SQL> startup
That should do the trick, lets see what the server thinks about things in a couple a weeks time or so.

Don't forget:
Please have more than one look at the statistics before you do anything, the free memory in the shared pool could be free today but needed all other days. Take a few snapshots over time and analyze the results before doing any changes. And whatever you do, don't over allocate SGA.
The last thing you want is the SGA to be swapped to disk or the system swapping out other things.

1 comment:

Unknown said...

My Server is Dell Xeon 3.06 with 2 GB RAM and oracle and D2K with everday database size 1 GB. I would like to know how to Divide SGA and Shared Pool for Performance.