[ORACLE] table과 index 파악하기 공부

DB를 사용하다보면 점점 늘어나는 데이터를 관리하기 위해서 데이터의 양이 얼마나 되는지 파악할 필요가 생깁니다.
하지만 oracle의 온갖 숨겨져있는 view들을 잘 파악하지 못하면 정확하게 알아내기가 쉽지 않습니다.

oracle은 analyze라는 명령어를 사용하여 table, index, cluster의 상태를 분석할 수 있도록 합니다.
이 analyze도 여러가지 옵션을 주어서 다양하게 분석할 수 있지만 이번에는 그냥 기본적인 분석방법만 소개하도록 하고.

이와 더불어서 우리가 꼭 알아야하는 view가 user_tablesuser_indexes입니다.
user_tables는 table에 대한 꽤 자세한 정보가 들어가 있구요.
user_indexes는 index에 대한 자세한 정보가 들어가 있습니다.
하지만 저는 이번에는 이 user_indexes view를 인덱스 이름 알아내는데만 쓰도록;;

자...이제 본격적으로!

오라클에서 pl/sql을 써서 모든 테이블의 크기, 그리고 모든 인덱스의 크기 등을 알아보도록 하겠습니다.

우선 sqlplus에서 pl/sql의 값이 화면에 표시되도록!

SET SERVEROUTPUT ON SIZE 1000000

set pagesize 10000
set linesize 200

-- table
BEGIN
FOR rec IN (SELECT table_name FROM user_tables) LOOP
DBMS_DDL.ANALYZE_OBJECT ('TABLE', NULL, rec.table_name, 'COMPUTE', NULL, NULL, NULL);
DBMS_OUTPUT.PUT_LINE (rec.table_name || ' is analyzed!');
END LOOP;
END;
/

BEGIN
DBMS_OUTPUT.PUT_LINE ('TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS');
DBMS_OUTPUT.PUT_LINE ('----------------------- --------- ------------- ---------');
FOR rec IN (SELECT table_name,blocks,empty_blocks,num_rows FROM user_tables ORDER BY table_name) LOOP
DBMS_OUTPUT.PUT_LINE (RPAD (rec.table_name, 23) || LPAD (rec.blocks, 10) || LPAD (rec.empty_blocks, 14) || LPAD (rec.num_rows, 10));
END LOOP;
END;
/


set 부분은 그냥 보기 좋게 하기 위해서 넣은거니까 넘어가시고...
먼저 for loop를 돌려서 테이블들을 분석하도록 합시다.
DBMS_DDL.ANALYZE_OBJECT 를 사용해서 테이블을 분석합니다.
첫번째 파라메터는 table, index, cluster등을 넣을 수 있습니다.
두번째는 owner, 세번째는 table이름입니다.
for loop에서 쿼리해놓은 user_tables의 테이블 이름을 가지고 와서 넣어주도록 합니다.
저렇게 하면 "analyze table table_name compute statistics" 라는 명령어를 사용한 것과 동일한 효과를 가져옵니다.
일단 저렇게 다 분석을 해놓고 user_tables에 들어있는 데이터를 가져옵니다.
blocks는 그 table이 차지하고 있는 블럭의 수를, empty_blocks는 차지하고 있는 블럭중에 실제로 사용하지 않는 블럭의 수, num_rows는 테이블의 자료(row)수를 나타냅니다.
block 하나의 크기는 oracle setting시에 사용자가 정할 수 있으니 곱하기 해서 나오는게 실제로 MB,KB의 크기가 되겠죠.

이제 인덱스를 알아보겠습니다.

DECLARE
sql_cmd NUMBER;
ignore NUMBER;
CURSOR icur IS
SELECT name, blocks, btree_space, used_space, pct_used, lf_rows, del_lf_rows
FROM index_stats;
idx icur%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE ('INDEX_NAME BLOCKS BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS');
DBMS_OUTPUT.PUT_LINE ('---------------------------- --------- ------------ ---------- --------- -------- -----------');
FOR rec IN (SELECT index_name FROM user_indexes ORDER BY index_name) LOOP
sql_cmd := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(sql_cmd, 'ANALYZE INDEX ' || rec.index_name || ' VALIDATE STRUCTURE', DBMS_SQL.v7);
ignore := DBMS_SQL.EXECUTE(sql_cmd);
DBMS_SQL.CLOSE_CURSOR(sql_cmd);
OPEN icur;
FETCH icur INTO idx;
CLOSE icur;
DBMS_OUTPUT.PUT_LINE (RPAD (idx.name, 28) || LPAD (idx.blocks, 10) || LPAD (idx.btree_space, 13) || LPAD (idx.used_space, 11) || LPAD(idx.pct_used, 10) || LPAD(idx.lf_rows, 9) || LPAD(idx.del_lf_rows, 12));
END LOOP;
END;
/

인덱스는 좀 특이합니다.
인덱스를 분석하는 쿼리는 "analyze index index_name validate structure" 입니다.
이건 그런데 index_stats라는 뷰에 분석 결과를 저장하는데, index_stats가 데이터가 하나만 들어가는 뷰입니다.
그래서 인덱스를 한번에 다~ 분석해놓고 볼수가 없습니다. 하나 분석할때마다 결과를 확인해야합니다.
그래서 코드도 저모냥입니다. (이것저것 짜깁기하다보니 저런 엄청난 코드가;;)
우선 분석을 해야겠죠?
DBMS_SQL.PARSE(sql_cmd, 'ANALYZE INDEX ' || rec.index_name || ' VALIDATE STRUCTURE', DBMS_SQL.v7);

이건 analyze 쿼리를 실행할 수 있도록 합니다.pl/sql에서는 analyze는 실행할 수 없기 때문에 이런 편법을 사용해야하죠.
이렇게 실행이 되면 분석결과는 index_stats라는 view에 들어갑니다.
그러면 커서에서 그 결과를 select 쿼리해서 내용을 뿌려줍니다.
blocks는 그 인덱스가 차지하고 있는 블럭수, btree_space는 그 블럭안에서 btree가 차지하고 있는 공간, used_space는 그 인덱스가 실제로 사용한 공간, pct_used는...까먹었습니다. (pct 관련해서 좀 더 알아봐야되는데...귀찮아-_-)
lf_rows는 leaf node로 등록된 row의 수, del_lf_rows는 삭제된 leaf node의 수를 말합니다.

일전에 올렸던 '바보같은 인덱스'에서 알 수 있듯이
table에서 데이터가 삭제되면 그 데이터와 매핑되어있던 인덱스도 삭제되어야 합니다.
인덱스의 크기는 줄어들지 않고, 트리구조상 leaf node를 삭제해야겠죠.
그러면 del_lf_rows가 늘어나는 겁니다. 이건 필요없는거죠. rebuild를 통해서 없애야 하는겁니다.

이렇게 pl/sql로 만들어놓으면 DB 한가할 때 가끔씩 돌려서 DB의 데이터 상태를 파악할 수 있습니다.
어떤 놈이 문제가 되는지 한눈에 들어오니 좋군요.

핑백

  • 그쟈잉 : [ORACLE] table과 index 파악하기 2009-03-06 19:47:28 #

    ... /sql로 만들어놓으면 DB 한가할 때 가끔씩 돌려서 DB의 데이터 상태를 파악할 수 있습니다.어떤 놈이 문제가 되는지 한눈에 들어오니 좋군요.[출처] http://antonio91.egloos.com/3774823 ... more

덧글

  • 그쟈잉 2009/03/06 20:05 # 답글

    감사합니다. 좋은정보 퍼갑니다.
댓글 입력 영역



Google Analytics