Skip to Main Content

Can more memory be allocated to SGA than what is set to SGA_TARGET?

sabyakgpAug 3 2021

Hi
I know Oracle can allocate more memory to PGA than what is specified in PGA_AGGREGATE_TARGET but is this also true for SGA (using ASMM SGA_TARGET)?
I ran a test in my local machine (Oracle 19c) whose SGA_TARGET is 1744M and SGA_MAX_SIZE is 3504M. I ran the below code to expand shared pool (courtesy : EODA, 3rd Edition by Tom Kyte and Darl Kuhn)

declare
k varchar2(30);
ss varchar2(2000);
begin
for i in 1 .. 100000 loop
ss := 'create or replace procedure SP' || i || ' is
a number;
begin
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
a := 123456789012345678901234567890;
end;';
execute immediate ss;
k := 'SP' || i;
sys.dbms_shared_pool.keep(k);
end loop;
end;
/

As the code was running I observed Shared pool started growing and database buffer cache shrinking (in v$memory_resize_ops). After running for couple of hours the above code PL/SQL block failed with ORA-04031: unable to allocate 56 bytes of shared memory. when SGA size reached almost 1744M (1743.99902 MB) precisely.
So it was clear Oracle was not able to extend the SGA up to SGA_MAX_SIZE. Does this mean SGA_TARGET is a hard limit to how much memory Oracle can allocate to SGA?
Thanks, Sabya

Comments
Post Details
Added on Aug 3 2021
0 comments
13 views