alter system set db_cache_size - reduce SGA

[khussain]

I executed the following command on our Oracle 9.2.0 RACed database on Compaq TRU64 Unix:

Reduced db_cache_size from 1008M to 608M:

alter system set db_cache_size = 629145600 scope=both sid='dbx2';

The db_cache_size did get reduced, but I did not see any memory freed to the o/s - does thit not happen instantaneously ??

[khussain]

Anyone with this new 9i feature knowledge ?

[stecal]

Don't you still have sga_max_size to contend with? Try forcing that down (or up) and you will see a difference.

[khussain]

What does the max_sga_size have to do with reducing the allocated memory. Plus, if I can do this dynamically, why do I have to mess with max_sga_size, which is static ?

Here is another example:
SQL>show sga

Total System Global Area 3157237096 bytes
Fixed Size 736616 bytes
Variable Size 2097152000 bytes
Database Buffers 1056964608 bytes
Redo Buffers 2383872 bytes

SQL>alter system set shared_pool_size=268435456 scope=both sid='dbx1';

System altered.

SQL>show sga

Total System Global Area 3157237096 bytes
Fixed Size 736616 bytes
Variable Size 2097152000 bytes
Database Buffers 1056964608 bytes
Redo Buffers 2383872 bytes

No change, why ???

[stecal]

I had shared_pool sizing problems with 9i when using what used to be a good value for sga_max_size on an 8i database. I did all kinds of alter system this and that for sga components (different pool parameters). show sga didn't reflect any changes. Looking at v$sgastat should show your change.