I generated a pfile from the spfile of my Oracle database and noticed that some parameters appear twice in it with different values.
For example:
MYDBNAME.__shared_pool_size=16642998272 #which means 15872M
*.shared_pool_size=7247757312 #which means 6912M
Query results:
show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 6912M
So does shared_pool_size have 6912M or 15872M? Why do these values for the same parameter differ?
If it actually has 6912M and I wanted to expand it to let's say 10G, would it be enough to set the same value 10737418240 for these two parameters (MYDBNAME.__shared_pool_size and *.shared_pool_size)?
In addition, the alter system command only changes the *.shared_pool_size parameter, while MYDBNAME.__shared_pool_size remains unchanged.
But from what I've noticed, over the course of a few days, the value of the MYDBNAME.__shared_pool_size parameter can dynamically change on its own.
You do use sga_target. So sizes of memory regions within SGA are changed dynamically, depending the load.
In such a case:
*.shared_pool_size=7247757312 #which means 6912M
This is just a minimal size of region set by you
MYDBNAME.__shared_pool_size=16642998272 #which means 15872M
This is auto-tuned size, determined by database.
Database just stores size of auto-tuned value in spfile to be preserved till next db start