Keep pool로 바꾸는 방법
SQL> show parameter keep
NAME TYPE VALUE
-----------------------------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 0
SQL> alter system set db_keep_cache_size=4M <--- 에러가 날 경우
System altered.
SQL> show parameter keep
NAME TYPE VALUE
--------------------------------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 4M
<에러 해결>
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------------------------
sga_target big integer 272M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 272M
SQL> alter system set db_keep_cache_size=4M scope=both
용량 확인
SQL> show parameter size
NAME TYPE VALUE
--------------------------------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 0
db_keep_cache_size big integer 4M
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
global_context_pool_size String
hash_area_size integer 131072
java_max_sessionspace_size integer 0
java_pool_size big integer 0
large_pool_size big integer 0
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size big integer 0
parallel_execution_message_size integer 2148
sga_max_size big integer 272M
shared_pool_reserved_size big integer 4613734
shared_pool_size big integer 0
sort_area_retained_size integer 0
sort_area_size integer 65536
streams_pool_size big integer 0
workarea_size_policy string AUTO
redolog 버퍼용량 확인
SQL> show parameter log
ASMM Disable 만드는 질의
SQL> alter system set sga_target=0 scope=both
SQL> alter system set java_pool_size=0 scope=both
SQL> alter system set large_pool_size=0 scope=both
SQL> alter system set shared_pool_size=0 scope=both
SQL> alter system set stream_pool_size=0 scope=both
버퍼사이즈 계산
SQL> select pool,sum(bytes)/1024/1024 from v$sgastat group by pool;
POOL SUM(BYTES)/1024/1024
----------------------------------
175.998459 <===기타 keep(4M), sga 와 상관없는거 다빼면 버퍼사이즈
java pool 4
shared pool 84.0056801
large pool 4
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 272M
SQL> select name,value from v$parameter where name like '%size'
NAME VALUE
-------------------------------------------------------------------------------
sga_max_size 285212672
shared_pool_size 92274688
large_pool_size 4194304
java_pool_size 4194304
streams_pool_size 0
shared_pool_reserved_size 4613734
java_max_sessionspace_size 0
db_block_size 8192
db_cache_size 176160768
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 4194304
db_recycle_cache_size 0
db_recovery_file_dest_size 2147483648
global_context_pool_size
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
parallel_execution_message_size 2148
hash_area_size 131072
max_dump_file_size UNLIMITED
object_cache_optimal_size 102400
sort_area_size 65536
sort_area_retained_size 0
olap_page_pool_size 0
9장 자동화된 툴
Dictionary Views
SQL> select table_name, num_rows from dba_tables where owner='SCOTT'
TABLE_NAME NUM_ROWS
--------------------------------------------
DEPT
EMP
BONUS
SALGRADE
EM-Admin-Advisor central
SQL> select view_name from dba_views where view_name like '%ADVICE'
VIEW_NAME
------------------------------
V_$PGA_TARGET_ADVICE
V_$MTTR_TARGET_ADVICE
V_$PX_BUFFER_ADVICE
V_$DB_CACHE_ADVICE
V_$SGA_TARGET_ADVICE
V_$SHARED_POOL_ADVICE
V_$JAVA_POOL_ADVICE
V_$STREAMS_POOL_ADVICE
GV_$PGA_TARGET_ADVICE
GV_$MTTR_TARGET_ADVICE
GV_$PX_BUFFER_ADVICE
GV_$DB_CACHE_ADVICE
GV_$SGA_TARGET_ADVICE
GV_$SHARED_POOL_ADVICE
GV_$JAVA_POOL_ADVICE
GV_$STREAMS_POOL_ADVICE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_STREAMS_POOL_ADVICE
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_MTTR_TARGET_ADVICE
23 rows selected.
Asynchronius와 synchronius 장단점 비교
| Commit | 장점 | 단점 |
| Asynchronius | 속도가 빠름 | 신뢰도 떨어짐(data lose의 가능성), 배치잡할때 |
| synchronius | 안정성이 높음 | Transaction 많을때 느려짐, 일반 사용 |
