I Recently created a 19c Enterprise DB on a Windows server with 54GB of memory. I allocated 48GB to the DB. Today I increased the physical memory to 116GB. I would like to increase the memory utilization to 106GB.
When investigating the memory parameters I found the SGA_LIMIT and MEMORY_MAX_TARGET set to 0. pga_aggregate_target was set to 12205M. Following guidance from Oracle documentation i made the following changes;
alter system set memory_max_target = 104G scope=spfile;
restart the DB
alter system set SGA_TARGET = 0 scope=both;
alter system set PGA_AGGREGATE_TARGET = 0 scope=both;
Task manager showed the memory utilization at 41GB. So I ran a few queries with large sorts like;
select * from table_with_over_30M_rows order by 1,2,3,4;
select * from table_with_over_30M_rows t1 inner join other_table_with_45M_rows t2 on t2.id=t1.id;
This caused the Task Manager memory utilization to increase to 48GB. I have allowed users back on and the memory utilization has not increased above 48GB.
I believe there something limiting the memory. I have checked all the parameters I can think of and nothing sticks out. My questions;
What should I look for regarding parameters?
How can I try to force additional memory to be consumed?
What other troubleshooting steps can I take?