Reduce SGA size

[jamiek]

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?

[sambavan]

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

[julian]

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.

[jamiek]

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,

[sambavan]

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

[julian]

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.

[pando]

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

[jamiek]

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

[jm]

login as user sys then try it

[jamiek]

Could you give me any comments on this output below?

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]