Reduce SGA size

I found we allocated to my memory to SGA compared to its physical memory. Total SGA size is about 224M and physical RAM is 256.
Can I reduce size of shard_pool_size and db_block_buffers in the parameter file and Do I need to shut down DB?
And also is there any concerns before I perform this task?

Do a shutdown immediate and change the shared_pool_size and db_block_buffers and then restart the instance ... Once this is done monitor your library cache hit and buffer catch hit ratios

Sam

If you want to understand better the SHARED POOL you need to know better X$KSMSP.

One common DBA mistake is oversizing the Shared Pool.

How can I see X$KSMSP table?
and what is it?
Please explain why I have to know about it and what kind of information the table include...etc.
Thanks,

Code:

---

SQL> select ksmchcom contents, count(*) chunks,
  2  sum(decode(ksmchcls, 'recr', ksmchsiz)) recreatable,
  3  sum(decode(ksmchcls, 'freeabl', ksmchsiz)) freeable,
  4  sum(ksmchsiz) total
  5  from x$ksmsp
  6  where ksmchcls not like 'R%'
  7  group by ksmchcom;

---

Sam

That's it Sam!

Note that each chunk is a bit larger than the containing object for there is a 16 byte header in which some info is stored: type, class and size of the chunck, etc.

Free chuncks: they do not contain valid objects.
Recreatable chunks: they contain objects that may be temporaly removed and then recreated.
Freeable chunks: they contain objects need during the session and then freed. They are not recreatable, hence they cannot be temporaly removed from memory.

Sam did not mention the perminant chunks, they contain the so called persistant objects.

try http://www.ixora.com.au for better understanding of Oracle Internals

'No rows selected' after querying the statement Sam gave me.
What does that mean?

login as user sys then try it

Could you give me any comments on this output below?

Code:

---


CONTENTS        CHUNKS RECREATABLE  FREEABLE    TOTAL
----------------    ---------    -----------      ---------      --------
KGFF heap                  11        1296            5688        6984

KGK contexts                2              *              2376        2376

KGK heap                    3          8772            560        9332

KGL handles          24514      7887248                *        7887248

KQLS heap                145          66140        92944      159084

PL/SQL DIANA            18            4428        26912        31340

PL/SQL MPCODE          53          16156        76212      92368

PLS cca hp desc            1                *            164          164

PLS non-lib hp              1            2096                *          2096

character set m            6                  *          28684      28684

dictionary cach          142                  *        307432    307432

fixed allocatio              18              576                  *            576

free memory            6771                  *                *    676992

kzull                            32                  *            1588      1588

library cache          57143      8178392      14278752  22457144

multiblock rea                1                  *              2064        2064

permanent memor        16                  *                * 17075672

row cache lru              24            1064                *            1064

session param v          24                  *            69216    69216

sql area                  45422      5585220        49729024  55314244

table columns              17          15248                  *        15248

table definiti                  2              620                  *          620

trigger defini                2            1072              1072      2144

23 rows selected.
I took the liberty to edit the thread, Sam

---

[Edited by sambavan on 10-03-2001 at 06:11 PM]

oops!
sorry...how come copy and paste doesn't work here...

I have gone ahead and edited the code for you. In the future if you would like to use the copy and paste option, use the

[  CODE   ]

option...

NOTE: If you feel that I have violated your rights on this forum, by editing your post, please let me know and I'll refrain from doing it.

Sam

[Edited by sambavan on 10-03-2001 at 06:18 PM]

Thanks sam..any comments on this output?

After reducing the SGA via above steps. All you need to do is make sure that memory use is as efficient as possible.
If the application using the db has a lot of packages(procedures,functions) check to see what the number of executions is for each and pin the most used into the SGA.
Why? this will reduce the overhead to reload into memory.

Have Fun

roo

Here are some alternative scripts to check free and freeable memory

Try this script for SGA

PROMPT R-free = Shared pool reserved size
PROMPT R-freea = reserved memory
PROMPT free = This is the amount of contiguous free memory available
PROMPT freeabl = Used memory is freeable
PROMPT perm = Freed memory not yet available
PROMPT recr = Reserved

select sum(ksmchsiz) Bytes, ksmchcls Status
from sys.x$ksmsp
group by ksmchcls;

And this for buffer_cache (requires catparr.sql to have been run)

PROMPT State 0 = Buffers that have NOT been used
PROMPT State 1 = Buffers that have been used

select state, count(*)
from x$bh
group by state;



select
decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK
STATUS", count(*)
from x$bh
group by
decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);