My question is regarding Oracle9i. I have set my SGA size to 300 MB. Now how do I know how much of that memory is actually being used? I have a feeling that 300 MB is just too much for that instance. How do I determine the memory utilization at any given time? (eg: How much of shared_pool_size is used?) Any script will be greatly appreciated. I did look into v$sgastat, but I didn't get much from it.
An SGA of 300MB probably isn't too big. First, I'd query V$SGASTAT as follows:
SELECT * FROM v$sgastat WHERE name='free memory';
If your large pool, Java pool, or shared pool has a large number of free bytes, then adjust LARGE_POOL_SIZE, JAVA_POOL_SIZE, or SHARED_POOL_SIZE accordingly.
The bulk of your SGA should be allocated to the Buffer Cache. If you have set DB_CACHE_ADVICE=ON and you are using Oracle9i's DB_CACHE_SIZE parameter, then you can query V$DB_CACHE_ADVICE to see the effects of raising or lowering your DB_CACHE_SIZE parameter.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.