231219 Linux_block 관리

■ 오라클 블록 사이즈 : 2k, 4k, 8k(기본값), 16k, 32k

select * from  dba_tablespaces;

 

select * from v$sgastat;
select * from v$sgastat where name = 'free memory';

# sga 메모리 free 확인

select round(sum(bytes)/1024/1024,2) free_mb from v$sgastat where name = 'free memory';

select * from v$memory_dynamic_components;

SQL> show parameter db_4k_cache_size

SQL> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
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_cache_advice                      string      ON
db_cache_size                        big integer 0
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
result_cache_max_result              integer     5
result_cache_max_size                big integer 1760K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
session_cached_cursors               integer     50

# 기본 블록 크기는 데이터베이스 생성 시 결정한다.
- dbca(초기파라이터, spfile)를 통해서 데이터베이스 생성 시 할 경우는 8k 결정이 된다.
- 다른 block 크기를 기본 블록으로 설정하려면 수동으로 데이터베이스 생성하면 된다. (pfile 생성후 사용, create pfile from spfile;)
(수동으로 하지 않는다면, 고정이다(8k).)

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

# non standard block, 수동관리 
+ row_cache_size
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_cahce_size <- db_block_size 로 설정한 값의 data buffer cache 공간
(db_block_size로 이미 설정했으니 설정하면 안된다.)

ⓐ db_4k_cache_size 4k 생성
ⓑ 별도의 OLT_tbs tablespace 생성

SQL> show parameter db_4k_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size                     big integer 0

[putty 창]
SQL> alter system set db_4k_cache_size = 12m;

SQL> show parameter db_4k_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size                     big integer 12M : 4배수 단위 설정된다.

# 생성
1. 메모리 확보
2. tablespace 생성

CREATE TABLESPACE oltp_tbs
DATAFILE '/home/oracle1/userdata/oltp_tbs01.dbf' SIZE 5M AUTOEXTEND ON
BLOCKSIZE 4K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLE hr.oltp_emp 
TABLESPACE oltp_tbs 
AS SELECT * FROM hr.employees;

 

SELECT * 
FROM dba_segments
WHERE OWNER = 'HR'
AND segment_name IN ('OLTP_EMP', 'EMPLOYEES');

 

SELECT * 
FROM dba_extents 
WHERE owner = 'HR'
AND segment_name in ('OLTP_EMP', 'EMPLOYEES');

SQL> select * from hr.oltp_emp where department_id = 20;
1. parse (문법, 객체관리 등, 실행계획)
ex) 색인페이지(oracle index) /  책:table / 책페이지:block / 책 문장:row
- 단어 찾기 : i/o 
- 여러 단어 찾기:  non uniqe scan
- 단어찾는 목록이 없다면, full table scan (시간이 오래걸릴 것이다)
- row_id scan 
- join 방법 및 순서
2. bind(변수 확인) : 실행계획을 공유하기 위해 변수 이용
3. execute(물리적/논리적) : buffer cache 존재 유무 확인 -> cursor 담기
4. fetch : cursor 내용 user에게 전달

# sys session
ⓐ# undo 생성

CREATE UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' SIZE 10M
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL;

# 생성 확인
[sql developer]

SELECT segment_id, segment_name, owner, tablespace_name, status 
FROM dba_rollback_segs;

SYSTEM UNDO -> DML 작업시 사용

# ONLINE 되어 있는 부분만 볼것

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

 

- xacts: 트랜잭션 확인

ⓑ# 새로운 hr session 

update hr.employees
set salary = salary * 1.1
where employee_id = 200;

# 트랜잭션 활동하는 session 확인.

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

# sys session 

[putty]

alter system set undo_tablespace = undotbs;

SQL> alter system set undo_tablespace = undotbs;

SQL> show parameter undo

[sql developer]

SELECT segment_id, segment_name, owner, tablespace_name, status 
FROM dba_rollback_segs;

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

 

# 기존 hr session
-- 새로운 undo segment 를 할당 받지 않고 썼던 undo segment 을 사용
delete from hr.employees where employee_id = 202;
SQL> delete from hr.employees where employee_id = 202;

# sys session 

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

# 새로운 hr session 
[sql developer]

update hr.employees
set salary = salary * 1.1
where employee_id = 100;

# sys session 

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

# 기존 hr session 
[putty]
SQL> rollback;

Rollback complete.

# sys session 

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

PENDING OFFLINE : undo retention때문에 PENDING OFFLINE로 확인

# hr session 
[putty]
SQL> delete from hr.employees where employee_id = 202;

1 row deleted.

# sys session 확인

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

새로운 undo 할당되며, PENDING OFFLINE 은 할당받지 않는다.

SQL> drop tablespace undo1 including contents and datafiles;

Tablespace dropped.

[sql developer]

SELECT segment_id, segment_name, owner, tablespace_name, status 
FROM dba_rollback_segs;

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

# 어떤 session 접속해서 transaction 작업을 얼마나 했는지 확인 가능

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

username : 계정
xidusn : segment 번호
ubafile :데이터파일번호
ubablk : 블록번호 
used_ublk : 사용중인 undo 블록의 수, 모니터링 확인

# 10분 단위 undo 발생량 정보 확인

SELECT * FROM v$undostat;

undoblks : begin_time(시작시간) ~ end_time(끝난 시간) 사이에 transaction 한 횟수

SELECT to_char(begin_time, 'yyyy-mm-dd hh24:mi:ss') begin_time, to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') end_time, undoblks 
FROM v$undostat;

- 하루일자 확인 -> sum(), group by begin_time 으로 확인하기
# 전날 모니터링

SELECT to_char(begin_time, 'yyyy-mm-dd'), SUM(undoblks)
FROM v$undostat
GROUP BY to_char(begin_time, 'yyyy-mm-dd');