Thanks for the question, chirag.
Asked: November 08, 2017 - 10:57 am UTC
Last updated: November 09, 2017 - 1:50 am UTC
Version: 11.2.0.3
Viewed 1000+ times
You Asked
HI Tom,
We have a databases running on 11.2.0.3 with memory parameters set as below:
This is a windows server 2008 R2.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 11104M
sga_target big integer 11008M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
_pga_max_size big integer 300M
pga_aggregate_target big integer 3000M
But the total installed memory on the server is 12 GB only. As per my knowledge, the SGA_target consumes the memory from server during the nomount mode itself.
and in this case the database should have given memory related error at startup and wouldn't have started initially.
I am wondering, how is it still running.
Please correct me if I am wrong.
and Connor said...
SGA yes, PGA no.
So out of the 12G we're going to grab 11G (sga_target).
Obviously the combination of SGA+PGA is being allowed to exceed 12G, so you are potentially inviting a nice big thrashing of paging/swap once you start to reach those limits.
So some adjustment of the parameters in definitely in order. 11G (on a 12G machine) definitely seems excessive.
Is this answer out of date? If it is, please let us know via a Comment