[SGA °ü·Ã ÆĶó¹ÌÅÍ]
1. shared_pool
- shared_pool_size (1 granule);µ¿Àû º¯°æ°¡´É
- shared_pool_reserved_size (default shared_pool_size ÀÇ 5%)
2. Data buffer Cache
- db_cache_size (=db_block_buffers --8i ÀÌÀü) (1 granule);µ¿Àû º¯°æ°¡´É
3. Redolog buffer
- log_buffer
4. large pool
- large_pool_size (default 0);µ¿Àû º¯°æ°¡´É
5. JAVA pool
- java_pool_size (default 24)
6. Total SGA Å©±â : sga_max_size --9i ºÎÅÍ µîÀå
[delete °úÁ¤]
1. ¸Þ¸ð¸®·Î ¿Ã¸°´Ù.
2. ·Ñ¹é¼¼±×¸ÕÆ® ÀÌ¿ë°¡´ÉÇÑ Àå¼Ò È®º¸
4. ¶ô
4. redo buffer¿¡ ÀÛ¾÷³»¿ë±â·Ï
5. ¼öÁ¤
[Commit]
1. redo log buffer¿¡ ¸¸ ±â·Ï
--LRU ¸®½ºÆ®¿¡¼ »ç¶óÁú ¶§, DBW0°¡ Dirty_list (=check pont queue)¸¦ º¸°í db file¿¡ ±â·Ï
--Check point °¡ ¹ß»ýÇϸé, DBW0°¡ Dirty_list (=check pont queue)¸¦ º¸°í db file¿¡ ±â·Ï
[Data Buffer Cashe]
1. ÀûÁß·ü °è»ê
v$sysstat : session logical reads : A
physical reads : B
physical reads direct : C
physical reads direct (lob) : D
hitratio = 1-(B-C+D)/A) > 90%
*physical reads directÀº ¾î´À¶§ ¿Ã¶ó°¡´Â°¡?
1)insert into emp2 select * from emp;
2)insert /*+append*/ into emp select * from emp; ->¼¹öÇÁ·Î¼¼½ºÀÇ ¸Þ¸ð¸®¿¡¼ copy ÀÛ¾÷ÀÌ ÀÌ·ç¾îÁü, buffer cache¿¡¼ ÀÛ¾÷ÇÏÁö ¾ÊÀ½. À̶§ ¿Ã¶ó°¨.
2. Hit ratio 90% ÀÌÇÏÀÎ °æ¿ì
a. ´ÙÁß ¹öÆÛ ±¸¼º
¿¹)°¡.20M¸¦ ´ÙÀ½°ú °°ÀÌ ÇÒ´ç
db_cache_size=16M
db_recycle_cache_size=4M
³ª.alter table big_temp storage (buffer_pool recycle); --create ÇÒ ¶§ÇϵçÁö..
- Object Àüü¸¦ ³ÖÀ» ÇÊ¿ä¾ø°í, ÀÛ¾÷¿¡ ÇÊ¿äÇÑ ¸¸Å¸¸ Àâ¾Æ¾ß È¿°ú ¸¸¶¥.
ÀÛ¾÷ blocks µéÀº user_tablesÀÇ blocks Ä÷³ Âü°í (<- analyzeÇØ¾ß Á¤È®ÇÑ Á¤º¸)
´Ù.ÀÚÁÖ »ç¿ëÇÏ´Â blockÀ» À§ÇØ db_keep_cache_size ¿µ¿ªÇÒ´ç
-alter table big_temp storage (buffer_pool keep); --create ÇÒ ¶§ÇϵçÁö..
-keepÇÒ objectµéÀÌ ¸ðµÎ µé¾î°¥ ¼ö ÀÖµµ·Ï Å©Áö Àâ¾Æ¾ß È¿°ú º¼¼ö ÀÖ´Ù.
b. Cache ¿É¼Ç ÀÌ¿ë
¿¹)full table scanÀº MRUÂÊÀ¸·Î À̵¿ÇÏÁö ¾Ê´Â´Ù. ±×·¯³ª ±×·¡µµ MRUÂÊ¿¡ ³²°Ô ÇÏ·Á¸é alter table table_name cache;ÀÌ¿ë
c. buffer cache size Áõ°¡ °í·Á : v$db_cache_advice ÀÌ¿ë
- size_for_estimate
- estd_physical_reads
[buffer Cache ¿¡ ¿Ã¶ó¿Í ÀÖ´Â Á¤º¸º¸±â]
SQL> select owner#, name , count(*)
from v$cache
where owner# >0
group by owner#,name;
[v#sess_io]
*db block gets : ¿øº» block¿¡¼ ÀоúÀ» °æ¿ì -> logical reads
*consistent gets : rollback block¿¡¼ ÀоúÀ» °æ¿ì -> ¿ª½Ã logical reads
[v$buffer_pool_statistics] --v$sysstatÀÌ system Àüü¶ó¸é ÀÌ ºä´Â pool º°·Î Á¶È¸
*Keep = 100%
*Recycle < 50%
*Default > 90%
[v$system_event]
*free buffer waits : db buffer cache¿¡ free buffer°¡ ¾ø¾î ±â´Ù¸± ¶§
=>DBWR_is_slaves ÁöÁ¤ : DBWRÀÇ µ¿ÀÛÀ» µµ¿ÍÁÜ - DBWRÇÁ·Î¼¼½º°¡ db blockÀ» ÀÐ¾î¼ slave 1 ..slaveNÇÁ·Î¼¼½º°¡ disk¿¡ ±â·ÏÇÏ´Â ÀÛ¾÷À» ÇÔ. ´ÜÀÏ CPU¿¡¼ ±ÇÀå.
¶Ç´Â DB_writer_processes : DBWRÇÁ·Î¼¼½º°¡ ÁöÁ¤ÇÑ ¼ö¸¸Å »ý¼ºµÊ. ¸ÖƼ CPU¿¡¼ ±ÇÀå
*buffer busy waits : ¶È °°Àº rollback segmentÀ» ¿©·¯¸íÀÌ µ¿½Ã¿¡ »ç¿ë ½Ã, free list(insert ÀÛ¾÷½Ã)
[Redolog Buffer]
[±â·ÏµÇ´Â ³»¿ë]
1. DDL, DMLµî select Á¦¿ÜÇÑ ¼öÁ¤ ÀÛ¾÷ ¸ðµÎ ±â·Ï
[Å©±â]
1. select value from v$parameter where name='log_buffer'; --ÆÄÆĹÌÅÍ¿¡ ÁöÁ¤µÈ Å©±â
2. select bytes from v$sgastat where name='log_buffer'; --½ÇÁ¦ ÇÒ´çµÈ Å©±â
[Æ©´× ¸ñÇ¥]
:redo buffer¿¡ ±â·ÏÇϱâÀ§ÇØ waitiing ¹ß»ý ÇÏÁö ¾Êµµ·Ï ÇØÁØ´Ù.
[¸®µÎ·Î±× ¹öÆÛ¿¡ ´ëÇÑ ´ë±â°¡ ÀϾ°í ÀÖ´ÂÁö È®ÀÎ]
select max(decode(name, 'redo buffer allocation retries', value)) retry,
max(decode(name, 'redo entries', value)) retry,
from v$syssat
where name in ('redo buffer allocation retries','redo entries');
select sid, event, seconds_in_wait, state
from v$session_wait
where event like 'log buffer space%';
select event, total_waits from v$system_event
where event like ''log file switch%;
[Redolog fileÀÇ ´ë±â ¿©ºÎ È®ÀÎ]
select event, total_waits, time_waited, average_wait
from v$system_event
where event in ('log file sync','log file parallel write');
[LGWRµ¿ÀÛ½ÃÁ¡]
1. commit ½Ã µ¿ÀÛ
2. buffer¿¡ 1/3ÀÌ»ó ³»¿ëÀÌ Ã¤¿öÁö¸é µ¿ÀÛ
3. Checkpoint ¹ß»ý½Ã(¿¹, ¸Å 3Ãʸ¶´Ù)
4. DBWRÇÁ·Î¼¼½º°¡ ¼öÁ¤µÈ ¹öÆÛ¸¦ µð½ºÅ©¿¡ ±â·ÏÇÒ ¶§(ÇÊ¿äÇÑ °æ¿ì)=DBWR µ¿ÀÛ Àü
[v$system_event]
*log buffer space :
*log file switch %
[I/O Æ©´×]
1. v$filestat, dba_data_files¸¦ È°¿ëÇÏ¿© io°¡ ¸¹Àº fileÀ» ã´Â´Ù.
2. ÇØ´ç data fileÀÌ ¼ÓÇØ ÀÖ´Â tbs¸¦ Á¶È¸
3. ÇØ´ç tbs¿¡ ¼ÓÇØ ÀÖ´Â segment_nameÀ» Á¶È¸
4. º¸°í ÀûÀýÈ÷(?) ´Ù¸¥ tbs·Î À̵¿
alter table table_name move tablespace tablespace tbs_name; --dept table¸¦ À̵¿
alter index index_name rebuild tablespace tbs_name; --index´Â rebuild
5. alter table emp allocate extent (datafile 'data_file_name' size 1M);
[Check point ¹ß»ý½ÃÁ¡]
1. Log switch ¹ß»ý½Ã
2. Database Á¾·á½Ã (abort Á¦¿Ü)
3. Alter system checkpoint ¸í·É½Ã
4. alter system switch logfile ¸í·É½Ã
5. Å×ÀÌºí½ºÆäÀ̽º offline½Ã
6. Æ©´×½Ã »ç¿ëµÇ´Â ÃʱâÈ ÆĶó¹ÌÅÍÀÇ Á¶°ÇÀ» ¸¸Á·ÇÒ ¶§
log_checkpoint_interval = (OS block)*N (default 0)
log_checkpoint_timeout = sec. (default 1800))
fast_start_io_target = dirty block number (8i)
fast_start_mttr_target = sec (9i)
[¿À´ÃÀÇ Oracle tips]
1. granule Å©±â È®ÀÎÇϱâ
select component, current_size, granule_size
from v$sga_dynamic_components --sga_max_size°¡ 128M ÀÌ»óÀº 16M, ±× ÀÌÇÏ´Â 4M °íÁ¤
2. v$cache¸¦ ¸¸µé¾î ÁÖ´Â sql¹® SQL>start ?/rdbms/admin/catparr.sql
3. ´Ù·®ÀÇ µ¥ÀÌÅ͸¦ º¹»çÇϱâ
insert /*+append*/ into emp2 nologging as select * from emp;
À߸ÔÀ½
ºñ°í)insert into emp2 nologging as select * from emp;
¹«½ÃµÊ
4. ¾ó¸¶³ª ¸¹À½ Full table scanÀÌ ÀϾ´Â °¡?
select name, value
from v$sysstat
where name like '%table scans%';
5. Full table scan ÀÛ¾÷ÀÇ ÁøÇà·üÀ» ÆľÇÇÏ´Â sql
select sid, serial#, opname, to_char(start_time,'HH24:MI:SS') "start",
(sofar/totalwork)*100 "percent_complete"
from v$session_longops
where totalwork <> 0;