Oracle Tuning Memory

Controlli

SELECT * FROM v$sgainfo;

set pagesize 200 linesize 200
SELECT * FROM v$sga_target_advice;

SHOW PARAMETER pga_aggregate_target;
SHOW PARAMETER pga_aggregate_limit;

AMM

 SHOW PARAMETER memory_target;

ASSM

SHOW PARAMETER sga_target;
SHOW PARAMETER sga_max_size;

Incrementare o decrementare shared_pool_size dinamicamente:

select
  component,
  oper_type,
  oper_mode,
  initial_size/(1024*1024) "Initial",
  TARGET_SIZE/(1024*1024)  "Target",
  FINAL_SIZE/(1024*1024)   "Final",
  status
from
  v$sga_resize_ops;

select
  component,
  current_size/(1024*1024) "CURRENT_SIZE",
  min_size/(1024*1024) "MIN_SIZE",
  user_specified_size/(1024*1024) "USER_SPECIFIED_SIZE",
  last_oper_type "TYPE"
from
  v$sga_dynamic_components;

show parameter shared_pool_size
show sga
alter system set shared_pool_size=400M scope=memory;
show sga
alter system set shared_pool_size=0 scope=memory;
show sga