Oracle Database pfile parameter value discrepancy

Question

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.

ibre5041 · Accepted Answer · 2024-04-10 07:12:14Z

1

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

edited Apr 10, 2024 at 7:12

answered Apr 10, 2024 at 7:03

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Database pfile parameter value discrepancy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related