반응형

[오라클]오라클의 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>



 

반응형
Posted by 공간사랑
,