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?
10-01-2001, 08:41 PM
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
10-02-2001, 07:04 AM
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.
10-02-2001, 12:24 PM
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.
10-02-2001, 08:18 PM
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;
10-03-2001, 03:20 AM
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.