메모리 관리 방식: 수동, ASMM, AMM

SHOW PARAMETER memory_target

SHOW PARAMETER sga_target

SELECT name, display_value, isdefault
  FROM v$parameter
 WHERE name IN ('shared_pool_size', 'db_cache_size', 'large_pool_size',
                'java_pool_size', 'streams_pool_size');

SELECT component,
       current_size/1024/1024        MB,
       min_size/1024/1024            min_mb,
       user_specified_size/1024/1024 user_mb
  FROM v$memory_dynamic_components
 WHERE current_size > 0;

SELECT CASE
         WHEN (SELECT TO_NUMBER(value) FROM v$parameter WHERE name='memory_target') > 0
              THEN 'AMM'
         WHEN (SELECT TO_NUMBER(value) FROM v$parameter WHERE name='sga_target') > 0
              THEN 'ASMM'
         ELSE 'MANUAL'
       END AS memory_mgmt
  FROM dual;

SELECT component, ROUND(current_size/1024/1024) cur_mb,
       oper_count, last_oper_type
  FROM v$memory_dynamic_components
 WHERE current_size > 0;

SHOW PARAMETER SGA_TARGET
show parameter sga_max_size
show parameter pga_aggregate_target

ALTER SESSION SET CONTAINER=CDB$ROOT;

COL pdb_name     FOR A15
COL sga_target   FOR A12
COL sga_min_size FOR A12

SELECT p.name AS pdb_name,
       MAX(CASE WHEN s.name='sga_target'
                THEN CASE WHEN REGEXP_LIKE(s.value$,'^\d+$')
                          THEN TO_CHAR(TO_NUMBER(s.value$)/1024/1024)||'M'
                          ELSE s.value$ END END) AS sga_target,
       MAX(CASE WHEN s.name='sga_min_size'
                THEN CASE WHEN REGEXP_LIKE(s.value$,'^\d+$')
                          THEN TO_CHAR(TO_NUMBER(s.value$)/1024/1024)||'M'
                          ELSE s.value$ END END) AS sga_min_size
  FROM pdb_spfile$ s
  JOIN v$pdbs p ON p.con_uid = s.pdb_uid
 WHERE s.name IN ('sga_target','sga_min_size')
 GROUP BY p.name
 ORDER BY p.name;

SELECT sga_size, sga_size_factor, estd_db_time, estd_physical_reads
  FROM v$sga_target_advice ORDER BY sga_size;

SELECT pga_target_for_estimate/1024/1024 pga_mb, pga_target_factor,
       estd_pga_cache_hit_percentage, estd_overalloc_count
  FROM v$pga_target_advice ORDER BY pga_target_for_estimate;

SELECT size_for_estimate mb, size_factor, estd_physical_read_factor, estd_physical_reads
  FROM v$db_cache_advice
 WHERE name = 'DEFAULT' AND advice_status = 'ON';

SELECT shared_pool_size_for_estimate mb, shared_pool_size_factor, estd_lc_time_saved
  FROM v$shared_pool_advice;

SELECT snap_id, begin_interval_time, end_interval_time
  FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 5 ROWS ONLY;

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
@?/rdbms/admin/awrrpt.sql

SELECT low_optimal_size/1024 low_kb,
       optimal_executions, onepass_executions, multipasses_executions
  FROM v$sql_workarea_histogram
 WHERE total_executions > 0;