Hello fellow Oracle DBAs & experts,
I am sure this is a very basic question but I am somehow unsure of it and hence would like to seek your opinion (basically when people ask this question to me, I am somehow unable to provide a justifiable answer):
1. Is there a way to find how much of the allocated SGA is used? I understand that memory is allocated to different pools like the shared pool, streams pool, java pool, log buffer, buffer cache, streams pool, etc. and that is something that can be known from v$sga/sgastat. What I want to understand is how much of the buffer cache is used or how much of the shared pool is used and how do I justify an increase in the SGA?
2. At all times, there is some amount of memory from the sga that is shown as free. That misleads to a belief that free memory is available. How can it be explained that inspite of free memory there can be many scenarios when sga needs to be increased (primarily because of more memory for the buffer cache)?
Many Thanks