Simple but beautiful test case on automatic memory management(AMM) in Oracle 11g

Oracle 11g proudly introduced the whole automatic memory management functionality. This automatic management includes not only SGA but also PGA.

Yes, believe it or not, all you have to do is just to tell Oracle how much memory it should use. Sounds fanatstic, but there would be many pitfalls you should be worried about.

See following simple but beautiful test case.

• Memory target and memory max target is set as 200M bytes.
• Mixed workloads of heavy logical reads and heavy hard parse to simulate the fight between db cache and shared pool.
• During this war, I would check if the change of PGA aggregate target would happen.

*.memory_target=200m
*.memory_max_target=200m

declare
  pat1     varchar2(1000);
  pat2     varchar2(1000);
  va       number;
begin
  select ksppstvl into pat1
    from sys.xm$ksppi i, sys.xm$ksppcv v   -- synonym of x$ table
    where i.indx = v.indx
    and i.ksppinm = '__pga_aggregate_target';
  
  for idx in 1 .. 1000000 loop
    
    -- Mixed workloads of heavy logical reads and hard parse
    execute immediate 
       'select count(*) from t3 where 10 = mod('||idx||',10)+1' into va;
      
    if mod(idx, 100) = 0 then
      sys.dbms_system.ksdwrt(2, idx || 'th execution');
    
      for p in (select ksppinm, ksppstvl
          from sys.xm$ksppi i, sys.xm$ksppcv v
          where i.indx = v.indx
          and i.ksppinm in
           ('__shared_pool_size', '__db_cache_size', '__pga_aggregate_target')) loop
          sys.dbms_system.ksdwrt(2, p.ksppinm || ' = ' || p.ksppstvl);
      end loop;
        
      select ksppstvl into pat2
      from sys.xm$ksppi i, sys.xm$ksppcv v
      where i.indx = v.indx
      and i.ksppinm = '__pga_aggregate_target';
      
      -- Do I have PGA aggregate target change?
      if pat1 <>  pat2 then
        sys.dbms_system.ksdwrt(2, 'yep, I got it! pat1=' || pat1 ||', pat2='||pat2);
        exit;
      end if;
    end if;
  end loop;
end;
/

The result is as following(printed in the alert log file):

100th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
200th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
300th execution
__shared_pool_size = 88080384
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
400th execution
__shared_pool_size = 92274688
__db_cache_size = 16777216
__pga_aggregate_target = 83886080
500th execution
__shared_pool_size = 88080384
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
600th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
700th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
800th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
900th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
1000th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080
1100th execution
__shared_pool_size = 92274688
__db_cache_size = 20971520
__pga_aggregate_target = 83886080

1200th execution
__shared_pool_size = 92274688
__db_cache_size = 37748736
__pga_aggregate_target = 58720256
yep, I got it! pat1=83886080, pat2=58720256

Oracle continued to be bounced between db cache size and shared pool size – which is very reasonable and expected.

But about 1200th execution, Oracle suddenly stole memory from the area of PGA aggregate target! This is also a typical and expected behavior with the AMM ability.

But the danger would not be negligible under some boundary situations. Changed PGA aggregate target means the change of COST, which implies the danger of sudden execution plan change.

Yes, this test case looks somewhat extreme. But once happened with logical holes and bugs, the result would be a catastrophe.

I would like to be reported with more real-life cases on this problem in the near future when Oracle 11g gets into the mainstream.