7. Oracle

[오라클 튜닝] instance 튜닝2

미니대왕님 2018. 12. 13. 20:06

 

[오라클 튜닝] 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#]';