[오라클 튜닝] instance 튜닝2

 

[오라클 튜닝] instance 튜닝2

 

 

1. 디스크 I/O 상태 분석 -> I/O 횟수가 한 쪽으로 심하게 몰릴 경우 데이터파일 재구성 검토

 

vi disk_io.sql -------------------------------------------------------------------------------- set pagesize 66 set linesize 140 set echo off set feedback off set verify off ttitle 'Disk I/O per Disk Files' col tablespace_name heading 'Tablespace Name' format a10 col file_name heading 'File Name' format a50 col phyrds heading 'Physical|Reads' format 99999990 col phywrts heading 'Physical|Writes' format 99999990 spool disk_io.lst select tablespace_name, file_name, phyrds, phywrts from dba_data_files df, v$filestat fs where df.file_id = fs.file# / spool off set feedback on set verify on --------------------------------------------------------------------------------

2. Sort 확인

- Disk 사용률 확인 => 5% 이상이면 튜닝 검토 select disk.value disk, mem.value mem, round(disk.value/mem.value, 2) "DISK RATIO" from v$sysstat disk, v$sysstat mem where disk.name = 'sorts (disk)' and mem.name = 'sorts (memory)'; - Temp Tablespace 사용 현황 확인 col tablespace_name format a10  select tablespace_name, current_users, total_extents, used_extents, extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment; - 진행중 정보 확인(SQL문이 완료되기 전 확인) col username format a10 col user format a10 select username, user, contents, segtype, extents, blocks from v$sort_usage;

3. Undo 세그먼트 경합 확인 => 5% 이상이면 튜닝 검토

select sum(waits) waits, sum(gets) gets, round(sum(waits)/sum(gets), 2) ratio from v$rollstat;

4. 특정 Undo 세그먼트 할당(대용량 처리시)

- Undo 세그먼트 생성 grant alter rollback segment to scott; create rollback segment big_roll storage (initial 5m next 5m); - Undo 세그먼트 사용 alter rollback segment big_roll online; set transaction use rollback segment big_roll; ---------------------------- update ... set ...; commit; ---------------------------- alter rollback segment big_roll offline;

5. Blocking Session 확인

- 조회 select s.sid, s.serial#, t.start_time, t.xidusn, s.username from v$session s, v$transaction t, v$rollstat r where s.saddr = t.ses_addr and t.xidusn = r.usn and ((r.curext = t.start_uext-1) or ((r.curext = r.extents-1) and t.start_uext = 0)); - Kill alter system kill session '[sid],[serial#]';

6. Lock 확인

- 조회 select xidusn, object_id, session_id, oracle_username, os_user_name, locked_mode from v$locked_object where xidusn = 0; select sid, serial#, username from v$session where sid = [session_id]; - Kill alter system kill session '[sid],[serial#]';