[오라클]오라클의 SGA 메모리 영역
C:\>sqlplus "/as sysdba"
SQL> -- Current parameter settings
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 584M
sga_target big integer 584M
SQL>
SQL> -- SGA Dynamic Component Size Information
SQL> desc v$sga_dynamic_components;
이름 널? 유형
----------------------------------------- -------- ----------------------------
COMPONENT VARCHAR2(64)
CURRENT_SIZE NUMBER
MIN_SIZE NUMBER
MAX_SIZE NUMBER
USER_SPECIFIED_SIZE NUMBER
OPER_COUNT NUMBER
LAST_OPER_TYPE VARCHAR2(13)
LAST_OPER_MODE VARCHAR2(9)
LAST_OPER_TIME DATE
GRANULE_SIZE NUMBER
SQL> select component, current_size, min_size from v$sga_dynamic_components where component in ('shared pool', 'large pool', 'java pool', 'DEFAULT buffer cache');
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------------------------------------------------- ------------ ----------
shared pool 176160768 171966464
large pool 4194304 4194304
java pool 16777216 16777216
DEFAULT buffer cache 406847488 406847488
SQL>
SQL> -- SGA Dynamic Component Size Information
SQL> col component format a22
SQL> col current_size format a15
SQL> col min_size format a15
SQL> select component, current_size/1048576||'M' current_size,min_size/1048576||'M' min_size from v$sga_dynamic_components where component in ('shared pool', 'large pool', 'java pool', 'DEFAULT buffer cache');
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------- --------------- ---------------
shared pool 168M 164M
large pool 4M 4M
java pool 16M 16M
DEFAULT buffer cache 388M 388M
SQL>
SQL> -- Current parameter settings in V$PARAMETER
SQL> col name format a20
SQL> col value format a20
SQL> SELECT name, value, isdefault FROM v$parameter WHERE name IN ('shared_pool_size','large_pool_size','java_pool_size','db_cache_size');
NAME VALUE ISDEFAULT
-------------------- -------------------- ---------
shared_pool_size 0 TRUE
large_pool_size 0 TRUE
java_pool_size 0 TRUE
db_cache_size 0 TRUE
SQL>
SQL> -- java_pool_size를 작게 설정해두게 되면 자바컴파일시에 에러가 발생하게 된다.
SQL> -- 오라클10g에서는 자동으로 설정하게 되며 자바를 실행시키면 자동으로 해당 java_pool_size 가 변경이 되어서 설정이 되게 된다.
SQL> select * from V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
584 1 238 1 9006
292 .5 238 1 9006
438 .75 238 1 9006
1168 2 238 1 9006
876 1.5 238 1 9006
1022 1.75 238 1 9006
730 1.25 238 1 9006
7 개의 행이 선택되었습니다.
SQL>
SQL> show parameter sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 584M
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 584M
sga_target big integer 584M
SQL>
