Oracle Lob segment 용량 확인.

2014.02.13 17:06
 
 



select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;

select dbms_lob.getlength(b)
from lob_test
;

truncate table lob_test;
insert into lob_test values(1, empty_clob());
declare
    v1 clob;
    begin
    select b into v1 from lob_test;
    for i in 1..6 loop
    dbms_lob.append(v1, rpad('x',1000,'x'));
    end loop;
    end;
/
commit;

select dbms_lob.getlength(b)
from lob_test
;

insert into lob_test select a, b from lob_test connect by level <= 10000;
commit;

select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;

SQL>
SQL>
SQL>
SQL> truncate table lob_test;
insert into lob_test values(1, empty_clob());
declare
    v1 clob;
    begin
    select b into v1 from lob_test;
    for i in 1..6 loop
    dbms_lob.append(v1, rpad('x',1000,'x'));

Table truncated.

SQL>
1 row created.

SQL>   2    3    4    5    6    7      end loop;
  8      end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> select dbms_lob.getlength(b)
  2  from lob_test
  3  ;

DBMS_LOB.GETLENGTH(B)
---------------------
                 6000

SQL>
SQL>
SQL>
SQL>
SQL> insert into lob_test select a, b from lob_test connect by level <= 10000;
commit;

select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;
10000 rows created.

SQL>
Commit complete.

SQL> SQL>   2    3 

SUM(DBMS_LOB.GETLENGTH(B)/1024/1024)
------------------------------------
                           57.226181

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select segment_name,segment_type,sum(bytes/1024/1024)
  2  from dba_segments
  3  where owner='SCOTT'
  4  and segment_name = 'SYS_LOB0000100237C00002$$'
  5  group by segment_name,segment_type
  6  order by 3 desc
  7  ;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE                                           SUM(BYTES/1024/1024)
------------------------------------------------------ --------------------
SYS_LOB0000100237C00002$$
LOBSEGMENT                                                               80


SQL>



신고

슈플 Oracle/Tests

Oracle PCTFREE PCTUSED 관련 내용

2014.02.13 16:51
 
 

Oracle PCTFREE 와 PCTUSED에 대한 개념 자료 이다. 

PCTFREE 등의 속성 설정은 테이블 및 트랜잭션 특성에 따라 다르게 적용이 되므로 

오라클의 성능이 점점 올라가면서 전통적 관리 기법에 대해서도 다시 한번 고민해볼 시기인 것 같긴하다.


아래는 어디서 퍼온 내용인데 참고 정도로만 확인하자.


PCTFREE
  블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정 합니다.

  예로 "PCTFREE 20" 으로 설정을 하면, 데이터 블록의 20%를 사용 가능한 빈 영역으로 유지하여 각 블록에 있는 행을 갱신하는데 사용한다는 의미입니다.

  PCTFREE의 기본값은 10% 입니다.

  PCTFREE와 PCTUSED의 합이 100을 초과하지 않는 범위 내에서 0에서 99까지의 값을 PCTFREE 값으로 사용할 수 있습니다.

  INDEX값은 수정이 자주 발생하지 않으므로 PCTFREE는 5%정도가 적당합니다.

PCTFREE가 적을 경우
  - 기존 테이블 행 갱신에 의한 확장을 위해 적은 공간을 확보 합니다.

  - 많은 로우가 한 블럭에 입력 가능합니다.

  - 수정이 적은 세그먼트에 적합 합니다.

PCTFREE가 클 경우
  - 블럭당 적은 row가 입력됩니다. 즉 같은 row를 입력하기 위해서 많은 블럭이 소요 됩니다.

  - 행 조각을 자주 체인화할 필요가 없으므로 수정 수행 속도가 증가 합니다.

  - 자주 수정되는 세그먼트에 적합 합니다.

PCTUSED
  오라클 서버가 테이블의 각 데이터 블록에 대해 유지하려는 사용 공간의 최소 백분율로써 데이터 세그먼트에 대해 지정합니다

  예로 "PCTUSED 40" 으로 설정하면, 데이터 블록의 사용영역이 39%보다 적어지지 않으면 새로운 행을 삽입할 수 없음을 의미 합니다.

  PCTUSED의 기본값은 40% 입니다.

PCTUSED값이 적을 경우
  - 블록이 재사용되는 일이 적어질 수 있으므로 처리 비용이 감소 합니다.

  - 데이터베이스에서 사용되지 않은 공간이 증가 합니다.

PCTUSED값이 클 경우
  - 블록이 재사용되는 일이 많아질 수 있으므로 처리 비용이 증가 합니다.

  - 공간 사용도를 향상 시킵니다.

PCTFREE와 PCTUSED값을 선택하는 예
① UPDATE시 ROW의 크기가 증가 될 때
- PCTFREE = 20
- PCTUSED = 40
② Insert, Delete가 자주 발생하지만 Update시 ROW의 크기가 증가 되지 않을 때
- PCTFREE = 5
- PCTUSED = 60
③ 테이블이 매우 크지만 대부분 Read Only Transaction 일 때
- PCTFREE = 5

- PCTUSED = 90 


신고

슈플 Oracle/Administration

Oracle 예상 테이블 용량 산정 방법

2014.02.13 16:36
 
 

① 블록 헤더 크기 계산

(Block header) = (fixed header) + (variable transaction header)
		+ (table directory) + (row directory)

(fixed header) = 57
(variable transaction header) = 23 * initrans
(table header) = 4 * n,	n은 테이블 개수, clustered table이 아닌 경우 1
(row directory) = 2 * x, x는 블록당 row 수

② 블록당 가용 데이터 공간 크기 계산

(Available data space) = ( (Block size) - (Block header) ) * (1 - pctfeee/100)
	= ( (Block size) - ① ) * (1 - pctfeee/100)

③ 평균 row 크기 계산

(Average row size) = (row header) + F + 3*V + D

(row header) = clustered table이 아닌 경우 3
F = 크기가 250byte 미만인 컬럼 수
V = 크기가 250byte 이상인 컬럼 수
D = combined column length

④ 블록당 평균 row 수 계산

(Average number of rows per block) = x = TRUNC( ② / ③ )

이 식에서 x를 구함.

⑤ 테이블당 필요 블록 수 계산

(Number of blocks for table) = (total number of rows) / ④

⑥ 테이블 용량 계산

(Table size) = ⑤ * (Block size)


신고

슈플 Oracle/Administration

단편화된 Table 정보 찾기

2014.02.13 16:28
 
 
How to get a list of most fragmented tables in Oracle Database 
  • Table 단편화 정보 조회 쿼리 
select table_name,round((blocks*8),2) "size (kb)" , 
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc

결과는 아래와 같은 형태이다. 

TABLE NAME     SIZE     ACTUAL DATA     WASTER SPACE
TREE     0     0     0
GC_S     3744     4651.9     -907.9
TRAIL     104     113.04     -9.04
ASSOCIATION_RULES     272     353     -81
ATTRIBUTES     1728     2528.12     -800.12
AUDITACTION     128     208.48     -80.48
DV     18608     36266.47     -17658.47
S134     728     903.08     -175.08
A178     344     518.75     -174.75
S129     728     896.48     -168.48
AGS_NODES     2864     4510.33     -1646.33
S149     472     633.79     -161.79
S127     728     871.62     -143.62
tu     2232     3619.76     -1387.76
PCd_DATA     3112     4371.75     -1259.75


단편화된(Fragmented) 테이블에 대해서는 내용을 파악하여 reorg 등 적절한 조치를 취하도록 한다. 


참조 :  https://community.oracle.com/thread/1106205?start=0&tstart=0


신고

슈플 Oracle/Administration

[Oracle] Bulk Collect

2009.12.10 17:07
 
 



SELECT 문에서 한번에 대량의 레코드들 취득 하는 경우, BULK COLLECT구문을 사용하면

한번에 여러개의 레코드를 취득할수 있으므로 퍼포먼스 향상

 

Patten 1

-------------------------------------------------------------------------------

DECLARE
  TYPE empno_tbl_type IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
  empno_tbl  empno_tbl_type;
BEGIN
  SELECT EMPNO BULK COLLECT INTO empno_tbl FROM EMP;
  IF empno_tbl.COUNT > 0 THEN
    FOR i IN empno_tbl.FIRST..empno_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = empno_tbl( i );
    END LOOP;
  END IF;
END;
/

 

Patten 2

-------------------------------------------------------------------------------

DECLARE
  TYPE emp_tbl_type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tbl  emp_tbl_type;
BEGIN
  SELECT * BULK COLLECT INTO emp_tbl FROM EMP;
  IF emp_tbl.COUNT > 0 THEN
    FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
    END LOOP;
  END IF;
END;
/

 

Patten 3 커서 이용

-------------------------------------------------------------------------------

DECLARE
  CURSOR emp_cur IS
    SELECT * FROM EMP;
  TYPE emp_tbl_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tbl  emp_tbl_type;
BEGIN
  OPEN emp_cur;
  FETCH emp_cur BULK COLLECT INTO emp_tbl;
  CLOSE emp_cur;
  IF emp_tbl.COUNT > 0 THEN
    FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
      UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
    END LOOP;
  END IF;
END;
/

 


pattern 4.

 

create table bc (
  a number, 
  b varchar2(10)
);
 
insert into bc values (11,'elf');
insert into bc values (12,'zwoelf');
insert into bc values (13,'dreizehn');
insert into bc values (14,'vierzehn');

commit;

 

declare
  type t_bc_a is table of bc.a%type;
  type t_bc_b is table of bc.b%type;
  v_str_query_se_1 varchar2(1000);
  l_bc_a t_bc_a;
  l_bc_b t_bc_b;
begin

  v_str_query_se_1:='select a, b from bc';

  EXECUTE IMMEDIATE v_str_query_se_1
  BULK COLLECT INTO l_bc_a, l_bc_b;
end;
/



,커서를 이용할시 취득할 데이터 수가 많을듯하면 Limit 사용하여 일정 레코드 단위로

Fetch하는 것이 성능면에서 좋다.

bulk collect 오라클 시스템에서

PL/SQL
처리하면서 사용하는 메모리를

많이 늘려 사용한다고 한다.


신고

슈플 Oracle/Administration Bulk, collect, oracle, 오라클