Problem solve Get help with specific problems with your technologies, process and projects.

Determining memory utilization in 9i

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.