On my 8172 database the db_block_size is 8K.
db_block_buffers is 100000
I was checking the stats of a particular session which seems to be stuck, I noticed the
buffer is not pinned count=265385370
What does this suggest?
Buffer is pinned count
This stat essentially counts the number of times a buffer was free when visited. This does not affect the appication.
Why 100000? How much memory do you have?
4GB of RAM.
Another point is , my shared_pool_size is set to be 539MB, is it appropriate to have such a large size?
It is big. Often DBAs increase the shared pool for some reasons (latch contention, etc). But this can delay the onset of shared pool latch contention and at the end exacerbates it.
Increasing the the shared_pool size is not going to account for SQL that is not shared. This will be causing a poor hit ratio from v$librarycache.
Do you have only one instance running? Note that the maximum size of SGA depends upon the OS limits and the kernel paramters that are set.
Click Here to Expand Forum to Full Width