'Database/Oracle' 카테고리의 글 목록

Oracle 참고 : http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDHCBA
https://docs.oracle.com/cd/B12037_01/network.101/b10776/tnsnames.htm

오라클 SID, Service Name 차이 | Oracle개념용어정리

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

지금껏 오라클을 사용하면서도 SID와 Service Name은 거의 구분해서 사용하지 않았다.

덕분에 최근까지는 SID와 Service의 차이를 인식하지 못하고 사용해 왔다.

사실 일반적인 테스트 환경이나 소규모의 경우 한개의 DB서버에 한개의 인스턴스만 사용한다.

이런 환경에서는 SID와 Service Name을 구분할 필요가 없었던것.

단순히 구분짓자면 이렇게 말할수 있다.

SID = DB 하나의 인스턴스

Service Name = 여러개의 인스턴스를 모아 하나의 서버 혹은 시스템을 구성한것

쉽게 예를 들어보자.

서버 한대에 인스턴스를 여러개 생성하여 orcl1, orcl2 로 각각 생성했다고 하자.

각각의 인스턴스는 orcl1, orcl2 라는 SID를 갖게 된다.

해당 서버에서 두개의 인스턴스를 묶어 사용할경우, orcl 이라는 Service Name을 갖을수 있다.

이외에도 서버 두대에 설치하여 각각 미러링 처리하여 동일한 서버인것 처럼 활용할경우

각각의 서버는 서로다른 SID를 갖게 되지만 Service Name을 동일하게 하여 같은 서버 처럼 활용할수 있다

[출처] 오라클 SID와 Service Name의 차이|작성자 도토리

DBMS 서버를 기동하기 위해서는 DB서버가 기동하는 서버의 IP 그리고

DB서버가 접속을 받아들이기 위한 프로토콜에 대한 정의가 필요합니다.

오라클의 경우 인스턴스가 서버 역할을 하는 DBMS프로세스인데,

인스턴스가 기동할때 SID를 필요로 합니다.

즉 SID는 인스턴스의 이름인 셈이지요.

SID가 필요한 이유는 한 서버(H/W)에 여러개의 인스턴스가 기동될 수 있으므로

구별하는 태그가 필요하겠지요. 따라서 SID는 DB서버에서 필요한 정보입니다.

SID정보는 환경변수와, LISTENER.ORA라는 파일에서 정의 됩니다.

DB에 접속하는 클라이언트 프로그램의 경우 접속하고자 하는 오라클 인스턴스 정보를

필요로 합니다. 클라이언트 프로그램이 접속하는데 필요한 정보는 서버IP, 오라클SID, 접속프로토콜
같은 정보가 필요하지요. 이러한 정보를 묶어서 서비스명으로 대표하고,
이 서비스명으로 클라이언트 프로그램이 서버에 접속하는데 사용합니다.

이 정보는 클라이언트쪽의 TNSNAMES.ORA라는 파일에 정의 되어있습니다.

출처 : 네이버 지식 검색 : 정확히는 모름(?)

instance, instantiate ; 인스턴스, 인스턴스화

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

인스턴스는 추상화 개념 또는 클래스 객체, 컴퓨터 프로세스 등과 같은 템플릿이 실제 구현된 것이다.
인스턴스화는 클래스 내의 객체에 대해 특정한 변형을 정의하고, 이름을 붙인 다음, 그것을 물리적인
어떤 장소에 위치시키는 등의 작업을 통해, 인스턴스를 만드는 것을 의미한다.

1. 몇몇 필자들은, 객체지향 프로그래밍에서 클래스를 인스턴스화 한다는 것이, 클래스의 구체적인 인스턴스,
즉 객체를 만드는 것이라고 말한다. 그 객체는 컴퓨터 내에서 실행시킬 수 있는 실행 파일이다.

2. 객체지향 프로그램 언어인 자바에서는, 클래스로부터 인스턴스화된 객체를, 객체라는 말 대신에
역시 클래스라고 부름으로써 많은 사용자들을 혼란스럽게 한다. 즉 자바에서는, 특정한 클래스를
만들기 위해 클래스를 인스턴스화하며, 그것 역시 컴퓨터 내에서 동작하는 실행 파일이다.

3. 객체지향 프로그래밍 개념이 나오기 이전의 데이터 모델링이나 프로그래밍에서는, 인스턴스화라는 것이
관계형 데이터베이스 테이블 내에 새로운 엔트리를 만듦으로써 추상화된 객체로부터 실재(데이터가 들어있는)
객체를 만드는 것도, 한 가지 용례였다.

[출처] 오라클 SID, Service Name 차이 | Oracle개념용어정리 |작성자 용쓰

======================================================================

오라클(Oracle) SID 및 DB_NAME 확인 방법

출처 : http://pangate.com/665

jdbc 에서 thin 드라이버로 오라클에 접속할 때는 SID를 알아야 한다.
최근에는 SID로 직접 기술하여 접근하는 것보다는 service name 이라는 것을 tnsname.ora 파일에 지정해 놓고 이것을 사용한다. 아무래도 SID가 공개되는 것이 문제가 될 수 있을 것이다.

<tnsname.ora 의 작성 예>

PRODDB =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 152.25.24.15)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
)

PRODDB =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 152.25.24.15)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
)

서비스명과 인스턴스명과 데이타베이스명과 SID는 서로 비슷한 듯 하면서 약간 다르다.

1. 오라클 데이타베이스명을 확인하는 방법

SELECT NAME, DB_UNIQUE_NAME FROM v$database;

2. 오라클 SID를 확인하는 방법

SELECT instance FROM v$thread;

일반적인 경우 데이타베이스가 하나만으로 구성 되어 있다면 데이타베이스명이 SID가 된다. 하지만 RAC 로 구성하여 데이타베이스 두개가 동시 가동되는 경우라면 이 SID 가 서로 다를 수 있기 때문에 중복 확인해야 한다.

JDBC 로 접속할 때 url 정보 작성 방법 :
url=jdbc:oracle:thin:@ip주소:포트:SID (url=jdbc:oracle:thin:@192.168.20.1:1521:ORCL)

아래의 예제에서 보면 RAC로 묶여 있는 경우 DATABASE NAME 과 실제 INSTANCE NAME 은 서로 다를 수 있다. 데이타베이스명은 ORCL 이지만 인스턴스명은 ORCL1 과 ORCL2 로 이름이 다름. thin 드라이브 URL 에서는 이 인스턴스명을 사용해야 한다.

사용자명 입력: system/manager@PRODDB

다음에 접속됨:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters option

SQL> SELECT NAME, DB_UNIQUE_NAME FROM V$DATABASE;

NAME DB_UNIQUE_NAME
------------------ ------------------------------------------------------------
ORCL ORCL

SQL> SELECT INSTANCE FROM V$THREAD;

INSTANCE
--------------------------------------------------------------------------------
ORCL1
ORCL2

SQL>

======================================================================

SID
jdbc:oracle:thin:@서버IP:서버Port:SID
jdbc:oracle:thin:@//hostname:port:sid

Service Name
jdbc:oracle:thin:@서버IP:서버Port:ServiceName
jdbc:oracle:thin:@//hostname:port/serviceName

Thin 사용
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=서버IP)(PORT=서버Port)))(CONNECT_DATA=(SERVICE_NAME=ServiceName)))

출처 : http://gent.tistory.com/39

PL/SQL(Procedure, Package)을 사용하다 보면 동적으로 쿼리(Query)를 생성하거나 텍스트(text) 쿼리를 입력 받아서 실행해야하는 경우가 있다. 다음 두가지 방법을 적절히 사용하면 좋은 결과를 얻을수 있다.

EXECUTE IMMEDIATE : Inset, Update, Delete 구문을 실행하거나 Select 구문을 실행 시 INTO를 사용하여 단일 값을 리턴 받을 때 사용

OPEN-FOR : Select 구문을 실행 시 Cursor를 리턴 받을 때 사용

주의 : 바인드 변수(:) 사용 시 쿼리 내부에서 변수명은 의미가 없고 변수 순서, 개수가 USING의 변수 순서, 개수와 일치해야 한다. 바인드 변수가 없다면 USING는 생략가능.

1. EXECUTE IMMEDIATE (INSERT, UPDATE, DELETE 등 구문 실행)

CREATE OR REPLACE PROCEDURE

PC_SET_HOLIDAY ( in_hldy_dte in date

, in_hldy_nm in varchar2

, in_use_yn in varchar2)

IS

v_query varchar(1000);

d_sysdate date;

BEGIN

-- 단일 값을 리턴받을때

EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL' INTO d_sysdate;

END;

v_query := v_query || 'INSERT INTO HOLIDAY';

v_query := v_query || ' VALUES(:1,:2,:3,:4)';

BEGIN

-- INSERT, UPDATE, DELETE 구문 실행

EXECUTE IMMEDIATE v_query

USING in_hldy_dte, in_hldy_nm, in_use_yn, d_sysdate;

END;

2. OPEN-FOR (CURSOR를 리턴 받을 때)

CREATE OR REPLACE PROCEDURE

PC_GET_HOLIDAY ( in_fromdate in varchar2

, in_todate in varchar2

, out_cursor out SYS_REFCURSOR)

IS

v_query varchar(1000);

BEGIN

v_query := v_query || 'SELECT HLDY_DTE, HLDY_NM';

v_query := v_query || ' FROM HOLIDAY';

v_query := v_query || ' WHERE HLDY_DTE BETWEEN :in_fromdate';

v_query := v_query || ' AND :in_todate';

BEGIN

-- CURSOR를 리턴 받을때

OPEN out_cursor FOR v_query

USING in_fromdate, in_todate;

END;

-- SELECT 1건 샘플

DECLARE
TABLE_NAME USER_TABLES.TABLE_NAME%TYPE;

BEGIN
EXECUTE IMMEDIATE 'SELECT TABLE_NAME FROM USER_TABLES WHERE ROWNUM = 1' INTO TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || TABLE_NAME);

END;
/

-- SELECT CURSOR 샘플

DECLARE
V_QUERY VARCHAR2(4000);
R USER_TABLES%ROWTYPE;
C1 SYS_REFCURSOR;

BEGIN
V_QUERY := V_QUERY || 'SELECT TABLE_NAME';
V_QUERY := V_QUERY || ' , TABLESPACE_NAME';
V_QUERY := V_QUERY || ' FROM USER_TABLES';
V_QUERY := V_QUERY || ' WHERE ROWNUM < :ROW_CNT';

OPEN C1 FOR V_QUERY USING 10;

LOOP
FETCH C1 INTO R.TABLE_NAME, R.TABLESPACE_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
END LOOP;

CLOSE C1;

END;
/

참고 : http://blog.kjslab.com/20

1. 커서의 내용을 미리 정의 해 놓고 사용하는 방법

DECLARE

CURSOR C1 IS
SELECT TABLE_NAME
, TABLESPACE_NAME
, STATUS
, PCT_FREE
FROM USER_TABLES;

BEGIN

FOR R IN C1 LOOP
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
END LOOP;END;

/

-- ROW 단위로 변수 정의

DECLARE
R USER_TABLES%ROWTYPE;
C1 SYS_REFCURSOR;

BEGIN
OPEN C1 FOR
SELECT *
FROM USER_TABLES;

LOOP
FETCH C1 INTO R;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME);
END LOOP;

CLOSE C1;

END;
/

-- ROW 단위로 변수 정의 후 COLUMN 단위로 사용

DECLARE
R USER_TABLES%ROWTYPE;
C1 SYS_REFCURSOR;

BEGIN
OPEN C1 FOR
SELECT TABLE_NAME
, TABLESPACE_NAME
FROM USER_TABLES;

LOOP
FETCH C1 INTO R.TABLE_NAME, R.TABLESPACE_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
END LOOP;

CLOSE C1;

END;
/

-- COLUMN 단위로 변수 정의

DECLARE
TABLE_NAME USER_TABLES.TABLE_NAME%TYPE;
C1 SYS_REFCURSOR;

BEGIN
OPEN C1 FOR
SELECT TABLE_NAME
FROM USER_TABLES;

LOOP
FETCH C1 INTO TABLE_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || TABLE_NAME);
END LOOP;

CLOSE C1;

END;
/

-- TYPE 정의

DECLARE
TYPE V_TYPE IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE, TABLESPACE_NAME USER_TABLES.TABLESPACE_NAME%TYPE);
R V_TYPE;
C1 SYS_REFCURSOR;

BEGIN
OPEN C1 FOR
SELECT TABLE_NAME
, TABLESPACE_NAME
FROM USER_TABLES;

LOOP
FETCH C1 INTO R;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
END LOOP;

CLOSE C1;

END;
/

2. 커서 변수를 미리 만들어 놓고 불러서 사용하는 방법

DECLARE
R USER_TABLES%ROWTYPE;
C1 SYS_REFCURSOR;

BEGIN
OPEN C1 FOR
SELECT *
FROM USER_TABLES;

LOOP
FETCH C1 INTO R;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || R.TABLE_NAME || ', TABLESPACE NAME : ' || R.TABLESPACE_NAME);
END LOOP;

CLOSE C1;
END;
/

3. 동적으로 커서를 생성해서 사용하는 방법

BEGIN
FOR C1 IN (
SELECT TABLE_NAME
, TABLESPACE_NAME
FROM USER_TABLES
)
LOOP
DBMS_OUTPUT.PUT_LINE('TABLE NAME : ' || C1.TABLE_NAME || ', TABLESPACE NAME : ' || C1.TABLESPACE_NAME);
END LOOP;
END;
/

오라클 쿼리 실행 시간

출처 : http://mainia.tistory.com/769

1. SET TIMING ON 사용하기

간혹 Stored Procedure 실행시 쿼리의 구간 별 시간을 알고

싶을 때가 있습니다. 전체 SP 수행 시간은 Object 테이블을 뒤져서

보면 되는데 각 단계별로 out print 를 찍으면서 보고 싶을 때

SET TIMING ON 을 사용하면 됩니다.

Set timing on; 후 timing start 로 시작하고 timing stop 로

마무리를 하면 됩니다. 그럼 그 시간이 측정되어 로그에

찍히게 됩니다.

Execute as Script (F5) 로 실행

SET TIMING ON;

TIMING START;
SELECT * FROM DUAL;
TIMING STOP;

TIMING START;
SELECT * FROM DUAL;
TIMING STOP;

Toad Menu > View > Query Viewer 를 사용해도 된다.
Execute Statement (F9) 로 실행해야 보임

2. DBMS_UTILITY.GET_TIME 사용하기

다른 방법은 DBMS_UTILITY.GET_TIME 을 사용하는 방법입니다.

현재시간을 리턴하게 되므로 쿼리 실행후 그 차이 값을 계산하는

방법입니다. 이것이 더 불편할거 같아요.

변수를 선언하고 그 변수에 현재 시간을 저장한 후 마지막에

값을 연산해서 초로 계산하게 되는 것이다. GET_TIME 은

100분의 1초를 리턴하게 됩니다.

DELARE
START_TIME NUMBER;
END_TIME NUMBER;
BEGIN

START_TIME := DBMS_UTILITY.GET_TIME;
SELECT *
FROM DUAL;
END_TIME := DBMS_UTILITY.GET_TIME;

SELECT (:END_TIME - :START_TIME)/100 ELAPSE_TIME FROM DUAL;
END;
/

======================================================================

ORACLE서버에서 수행시간이 긴 쿼리 찾기 쿼리

SELECT ROWNUM NO,
PARSING_SCHEMA_NAME,
to_char(ELAPSED_TIME/(1000000 * decode(executions,null,1,0,1,executions)),999999.9999 ) 평균실행시간,
executions 실행횟수,
SQL_TEXT 쿼리 ,
SQL_FULLTEXT
FROM V$SQL
WHERE LAST_ACTIVE_TIME > SYSDATE-(1/24*2)
-- AND LAST_ACTIVE_TIME BETWEEN to_Date('20111226163000','YYYYMMDDHH24MISS') AND to_Date('20111226170000','YYYYMMDDHH24MISS')
-- AND ELAPSED_TIME >= 1 * 1000000 * decode(executions,null,1,0,1,executions)
and PARSING_SCHEMA_NAME = 'ZIPCODE'
ORDER BY 평균실행시간 DESC, 실행횟수 DESC;

SELECT TO_CHAR (SID) sid, serial# serialNumber,
SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
b.sql_text sqlText
FROM v$session a, v$sqltext b
WHERE username NOT IN ('SYSTEM', 'SYS')
AND a.TYPE != 'BACKGROUND'
AND a.status = 'ACTIVE'
AND a.sql_address = b.address(+)
AND a.sql_hash_value = b.hash_value(+)
ORDER BY a.last_call_et DESC, a.SID, a.serial#, b.address, b.hash_value, b.piece

현재 실행되고 있는 쿼리 와 실행 시간

SELECT TO_CHAR (SID) sid, serial# serialNumber,
   SUBSTR (TO_CHAR (last_call_et), 1, 6) executeSeconds, userName, machine,
   b.sql_text sqlText
  FROM v$session a, v$sqltext b
  WHERE username NOT IN ('SYSTEM', 'SYS')
   AND a.TYPE != 'BACKGROUND'
   AND a.status = 'ACTIVE'
   AND a.sql_address = b.address(+)
   AND a.sql_hash_value = b.hash_value(+)
  ORDER BY a.last_call_et DESC,
   a.SID,
   a.serial#,
   b.address,
   b.hash_value,
   b.piece

출처 : http://ukja.tistory.com/320

갑자기 퀴즈가 하고 싶어졌습니다. 지난 번 퀴즈가 너무 쉬웠다는 비난이 쏟아져서... @_@

1. 오라클 버전은 11.1.0.6입니다.

TPACK@ukja1106> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

2. 파티션이 아닌 테이블 T1과 해시 파티션인 테이블 T2가 있습니다.

TPACK@ukja1106> create table t1(c1 number);

Table created.

Elapsed: 00:00:00.03
TPACK@ukja1106> create table t2(c1 number)
  2  partition by hash(c1) partitions 4;

Table created.

Elapsed: 00:00:00.01

3. 파티션이 아닌 테이블 T1에 대해 40만건을 Insert하는데는 1초가 조금 안걸립니다.

TPACK@ukja1106> insert into t1 select level from dual connect by level <= 400000;

400000 rows created.

Elapsed: 00:00:00.56

4. 해시 파티션인 테이블 T2에 대해 40만건을 Insert하는데는 무려! 6.3초가 걸립니다.

TPACK@ukja1106> insert into t2 select level from dual connect by level <= 400000;

400000 rows created.

Elapsed: 00:00:06.32

5. 자, 여기서 문제 나갑니다. 왜 이렇게 큰 성능 차이가 발생할까요?

======================================================================

출처 : http://ukja.tistory.com/321

티팩 (TPACK)

홈페이지 : https://sites.google.com/site/otpack/
설치 : https://sites.google.com/site/otpack/guide/install_guide

이전 포스트 [퀴즈] Partitioned Table과 Insert 성능에서 제가 생각한 답변이 나왔습니다. 이렇게 적어주셨습니다.

테이블이 파티셔닝에 되어 있어,
insert 할 때 insert 대상 블럭(버퍼)가 계속 바뀌게 되고,
그로 인해 logical reads가 증가하기 때문인 것 같습니다.

음... 마치 미리 답을 알고 있었다는 듯한 간단 명료한 답변입니다. ^^

이 퀴즈에서 예를 들었던 해시 파티션의 경우 매번 Insert가 이루어질 때마다 매번 다른 블록으로 들어갈 활륙이 높습니다(하지만 항상 그런 것은 아닙니다. 해시 파티션의 경우 해시값에 따라 들어갈 파티션을 결정하게 되는데 같은 해시값이 중복해서 나올 수 있기 때문입니다). 같은 원리로 레인지 파티션이나 리스트 파티션에서도 이런 문제가 발생할 수 있습니다.

가령 100개의 로우가 한번에 하나의 블록안에 들어가게 되면 Batch Processing이 이루어집니다. (또는 Array Insert라고도 합니다) Array Insert 효과에 의해서 Logical Reads도 줄어들고 Redo 데이터도 줄어듭니다.

퀴즈에서와 같이 아래와 같이 테이블을 만듭니다.

create table t1(c1 number);
create table t2(c1 number) partition by hash(c1) partitions 4;

Case 1. 가장 성능이 좋은 경우입니다. 가능한 많은 수의 로우가 한번에 하나의 블록안에 들어가므로 Array Insert의 효과가 극대화됩니다.

-- 가장 성능이 좋음. 
insert into t1 select level from dual connect by level <= 500000;

Case 2. 해시 파티션인 경우 각 로우가 매번 다른 블록으로 들어갈 확률이 높습니다. 따라서 Array Insert의 효과가 크게 감소합니다.

-- 성능이 안좋아짐
insert into t2 select level from dual connect by level <= 500000;

Case 3. 가장 성능이 안좋은 경우는 다음과 같이 건건이 Insert하는 경우입니다. Array Insert가 아예 발생하지 않기 때문입니다.

-- 성능이 가장 안좋음
begin
	for idx in 1 .. 500000 loop
		insert into t1 values(idx);
	end loop;
	commit;
end;
/

Case 4. 해시 파티션에 데이터가 추가되는 원리를 이해한다면, 아래와 같은 방법을 사용하면 Case 1과 같이 가장 성능이 좋게 만들 수 있습니다. ORA_HASH 함수를 이용해서 가능한 같은 파티션으로 정렬된 형태로 Insert하는 트릭입니다. 파티션키에 정렬된 형태로 Insert를 하는 것이 핵심입니다. 레인지 파티션이나 리스트 파티션에서도 동일한 방법을 사용할 수 있습니다.

-- 가장 성능이 좋음
insert into t2 select level from dual connect by level <= 500000 order by ora_hash(level, 3);

하지만 중요한 것은 답을 맞추고 안맞추고 아니라, 데이터를 통해서 객관적으로 검증할 수 있는가입니다. 이전에 이런 퀴즈가 있었죠.

1. 오라클에서 특정 세션(혹은 시스템)의 현재 상태를 바로 알 수 있는 가장 좋은 방법은 무엇일까요?

2. 오라클에서 특정 세션(혹은 시스템)을 하는 일을 추적할 수 있는 가장 좋은 방법은 무엇일까요?

첫번째 질문에 대한 제 대답은 "V$SESSTAT 뷰에 대해 Snapshot을 만들고, 각 Snapshot간의 Delta값을 비교한다"입니다. 즉 세션의 성능 문제를 분석하는데 있어서 가장 중요한 Snapshot 데이터는 V$SESSTAT 뷰이며, 이 데이터를 비교라는 방법을 이용해서 분석하는 것이 가장 기본적인 방법입니다. 이것이 티팩의 핵심적인 아이디어라는 것도 이미 말씀드렸습니다.

거창하게 말씀드렸는데, 더 간결 솔직하게 말하면 V$SESSTAT 뷰가 제공하는 데이터를 분석해보면 어느 정도 의미있는 분석을 할 수 있다. 요런 이야기입니다.

티팩의 Session Snapshot Report를 이용해서 위의 내용을 분석해보겠습니다.

create table t1(c1 number);
create table t2(c1 number) partition by hash(c1) partitions 4;

-- Session Snapshot 시작
exec tpack.begin_session_snapshot;

-- Case 1
insert into t1 select level from dual connect by level <= 500000;

exec tpack.add_session_snapshot;

-- Case 2
insert into t2 select level from dual connect by level <= 500000;

exec tpack.add_session_snapshot;

-- Case 3
begin
	for idx in 1 .. 500000 loop
		insert into t1 values(idx);
	end loop;
	commit;
end;
/
	
exec tpack.add_session_snapshot;

-- Case 4
insert into t2 select level from dual connect by level <= 500000 order by ora_hash(level, 3);

exec tpack.add_session_snapshot;

-- Session Snapshot Report 보기
set pages 10000
set lines 200
col item format a30
col deltas format a50
select * from table(tpack.session_snapshot_report);

결과는 다음과 같습니다.(내용이 길어셔 편집했음을 알려드립니다) DELTAS 값을 잘 분석해보시기 바랍니다.

- TYPE: STAT, WAIT, TIME, LATCH, ...
- ITEM: TYPE에 따른 항목(가령 STAT일 경우 session logical reads 등)
- START_VAL: 최초 Snapshot의 값
- END_VAL: 마지막 Snapshot의 값
- TOTAL_DELTA: END_VAL - START_VAL
- DELTAS: 각 Snapshot간의 Delta값의 흐름

TYPE       ITEM                            START_VAL    END_VAL TOTAL_DELTA DELTAS
---------- ------------------------------ ---------- ---------- ----------- --------------------------------------------------
STAT       redo size                      1343966008 1567241868   223275860 8486656->87223020->119117508->8448676
STAT       undo change vector size         423505332  485865780    62360448 1695312->26462580->32510584->1691972
STAT       session logical reads            14843144   16914592     2071448 15863->1521337->518902->15346
STAT       db block changes                 10655679   12453768     1798089 12623->762448->1010680->12338
STAT       db block gets                    11368998   13058307     1689309 13946->1144031->517777->13555
STAT       db block gets from cache         11368998   13058307     1689309 13946->1144031->517777->13555
STAT       redo entries                      5338577    6240096      901519 7792->381384->504754->7589
STAT       calls to kcmgrs                   5831580    6730131      898551 6973->380346->504279->6953
STAT       HSC Heap Segment Block Changes    4933463    5814688      881225 2827->375425->500125->2848
...
STAT       Heap Segment Array Inserts        2682231    3063258      381027 2785->375297->113->2832
...
STAT       DB time                             26491      31554        5063 162->1280->3406->215
...

위의 결과에서 HSC Heap Segment Block Changes와 Heap Segment Array Inserts 두 개의 지표(불행히도 이 두 개의 유용한 지표는 11g에서 추가된 것입니다)를 잘 해석하시면 Array Insert가 미친 영향을 완벽하게 해석할 수 있습니다. 단, 티팩 자체가 발생시키는 Array Insert가 100여회 정도된다는 것을 고려해서 해석해야 합니다.

티팩이 해주는게 고작 이것이냐고 비난하지 마시기 바랍니다. 티팩이 하고자 하는 것은 성능 트러블슈팅을 위해 필요한 기본적인 데이터를 자동으로 수집하고 적절히 리포트해주는 것일 뿐, 결국 최종 해석은 사람의 몫입니다.

중요한 것은 데이터에 기반한 과학적인 분석을 하느냐 아니면 이거 아니면 저거 다 찔러보는 방식의 분석을 하느냐일 것입니다.

- TPACK 권한 부여

CREATE PUBLIC SYNONYM TPACK FOR TPACK.TPACK;
CREATE PUBLIC SYNONYM TPACK_SERVER FOR TPACK.TPACK_SERVER;
GRANT EXECUTE ON TPACK TO PUBLIC;
GRANT EXECUTE ON TPACK_SERVER TO PUBLIC;

오라클 Table/Index Analyze 통계 확인 및 실행방법

출처 : http://jangpd007.tistory.com/248

DBMS_STATS.GATHER_TABLE_STATS
참고 : http://blog.naver.com/PostView.nhn?blogId=tyboss&logNo=70153024309
http://egloos.zum.com/bosoa/v/1402860
http://m.blog.daum.net/ryaniya/277

ANALYZE 란?

ANALYZE는 인덱스, 테이블, 클러스터의 통계정보를 생성 한다.

ANALYZE가 생성한 통계정보들은 비용기준(Cost-based)의 옵티마이저가 가장 효율적인 실행계획을 수립하기 위해 최소비용을 계산할 때 사용 된다.

각 오브젝트의 구조를 확인하는 것과 체인(Chain) 생성 여부를 확인할 수 있으므로 시스템의 저장공간 관리를 도와준다.

ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 한다.

- 테이블 : 총 로우의수, 총 블럭의 수, 비어있는 블럭에 쓰여질 수 있는 빈 공간의 평군, 체인이 발생된 로우의 수, 로우의 평균 길이
- 인덱스 : 인덱스의 깊이(Depth), Leaf block의 개수, Distinct Key의 수, Leaf Blocks/Key의 평균, Data blocks/key의 평균, Clustering Factor, 가장 큰 key 값, 가장 작은 key 값
- 컬럼 : Distinct한 값의 수, 히스토그램 정보
- 클러스터 : Cluster Key당 길이의 평균

문법

object-clause : TABLE, INDEX, CLUSTER중에서 해당하는 오브젝트를 기술하고 처리할 오브젝트 명을 기술 한다.

operation : operation 옵션에는 다음 3가지중 한가지 기능을 선택할 수 있다.

- COMPUTE : 각각의 값들을 정확하게 계산 한다. 가장 정확한 통계를 얻을 수 있지만 처리 속도가 가장 느리다.
(테이블에 저장되어 있는 모든 행을 대상으로 통계정보를 수집한다.)
- ESTIMATE : 자료사전의 값과 데이터 견본을 가지고 검사해서 통계를 예상 한다. COMPUTE보다 덜 정확 하지만 처리속도가 훨씬 빠르다
(오라클 서버의 자동화 알고리즘에 의해 데이터를 추출하여 통계정보를 수집한다.)
- DELETE : 테이블의 모든 통계 정보를 삭제 한다
(수집되어 있는 통계정보를 삭제한다.)

정보수집

주기적인 ANALYZE 작업을 수행 시켜 주어야 한다.

테이블을 재생성 하거나, 새로 클러스터링을 한 경우, 인덱스를 추가하거나 재생성한 경우, 다량의 데이터를 SQL이나 배치 애플리케이션을 통해 작업한 경우 ANALYZE를 수행 시켜 주는 것이 좋다.

사용자는 USER_TABLES, USER_COLUMNS, USER_INDEXS, USER_CLUSTER 등의 자료사전 뷰를 통해 정보를 확인할 수 있다

테이블을 ANALYZE 시킨다면 거기에 따르는 인덱스들도 같이 실시하는 것이 좋다.

오 라클에서는 20,000건 이하의 행수를 가진 데이터에 대해서는 COMPUTE STATISTICS절의 사용을 권장하며 20,000건 이상되는 경우에는 ESTIMATE STATISTICS절의 사용을 권장하고 있다. 또한, 통계정보의 분석은비 일과시간에 수행하는게 원칙이며 일과 시간에 수행해야 하는 경우라면 ESTIMATE STATISTICS절의 사용을 권장한다.

테이블 정보수집 예제
SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

새로운 정보를 구하기 전에 기존 정보를 삭제
SQL> ANALYZE TABLE emp DELETE STATISTICS;

특정 column에 대한 data 분포 수집
SQL> ANALYZE TABLE emp COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

통계 정보의 확인 예제

 SQL> SELECT NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
     , SAMPLE_SIZE
     , LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'CMS_CATEGORY';
 
 SQL> SELECT NUM_DISTINCT
     , DENSITY
     , LOW_VALUE
     , HIGH_VALUE
     , LAST_ANALYZED
     , COLUMN_NAME
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'CMS_CATEGORY';

오라클을 통해 작업할 시

insert시점에서 정상적으로 인덱싱이 되지 않는 경우가 발생한다.

특히 결합인덱스를 많이 사용하고 있는 경우 발생될 확률이 높다.

이런경우 오라클의 Analyzed를 통해서 해결이 가능하고

어느정도의 실행속도를 향상 시킬 수있다.

(실제 오라클사에서도 3개월에 한번씩은 Analyze를 실행하라 권고하고 있다.)

[Analyzed 확인 방법]

1-1. 테이블 통계정보
SELECT TABLE_NAME
     , BLOCKS  -- 해당 데이터가 저장되어 있는 블록 수.
     , NUM_ROWS  -- 데이터 행 수.
     , AVG_ROW_LEN  -- 하나의 행의 평균 길이.
     , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_TABLES
[WHERE TABLE_NAME = '테이블명']

1-2. 테이블 통계정보
SELECT TABLE_NAME
          , COLUMN_NAME  -- 컬럼명
          , LOW_VALUE       -- 해당 컬럼에 저장되어 있는 최소값.
          , HIGH_VALUE      -- 해당 컬럼에 저장되어 있는 최대값.
          , NUM_DISTINCT   -- 유일한 값의 수. (히스토그램 기준)
FROM USER_TAB_COLUMNS
[WHERE TABLE_NAME = '테이블명']

2. 인덱스 통계정보
SELECT INDEX_NAME
     , BLEVEL                        -- 인덱스의 깊미(Depth)
     , LEAF_BLOCKS              -- 리프 블록의 수.
     , DISTINCT_KEYS            -- 인덱스 컬럼의 유일한 값의 수.
     , CLUSTERING_FACTOR  -- 조건을 만족하는 데이터를 검색할 때 인덱스 키 값이 각 블록에 얼마나 잘 분산 저장되어 있는지를 나타내는 정도.
     , NUM_ROWS                 -- 전체 행수.
     , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_INDEXES

ex) SELECT TABLE_NAME
                , NUM_ROWS
    , TO_CHAR( LAST_ANALYZED, 'YYYYMMDD' )
FROM USER_TABLES;
TABLE_NAME NUM_ROWS TO_CHAR(
------------------------------ ---------- --------
ABS_TYPE 38 20040101
ANNIVERS    183 20040101
APPRFLDRHISTORY 570 20040101
APPRFOLDER    16885 20040101
APPRFOLDER_ERR 3670 20040101
APPRFORM    359 20040101
.
.
.
USR_INFO_ADMIN    0 20040101
VAR_DEPT_INFO 0 20040101
VIEW_TYPE 0 20040101
WASTEBOX    0 20040101
ZIP_CODE    44195 20040101
252 rows selected.

※ 참고 : desc user_tables 에서 보통 num_rows 로도 확인 가능

[특정 Table만 Analyze 하는 방법]

ANALYZE TABLE DOCUMENT COMPUTE STATISTICS
ex) DOCUMENT Table 만 Analyze

ANALYZE INDEX XPKDOCBOX COMPUTE STATISTICS
ex) XPKDOCBOX Index 만 Analyze

[전체 Table Analyze 하는 간단한 방법]

1. vi analyze_all.sql
  SELECT 'analyze table || table_name || estimate statistics;' FROM USER_TABLES

2. @analyze_all.sql

3. set heading off
   set echo off
   set feedback off
   set pagesize 300 (line 이 300 미만일 경우)
   spool analyze_table.sql
   /
   spool off

4. vi analyze_table.sql
필요없는 Line 제거 및 정리

5. @analyze_table.sql

[전체 Index Analyze 하는 간단한 방법]

1. vi analyze_all.sql
  SELECT 'analyze index || index_name || estimate statistics;' FROM USER_INDEXES

2. @analyze_all.sql

3. set heading off
   set echo off
   set feedback off
   set pagesize 300 (line 이 300 미만일 경우)
   spool analyze_index.sql
   /
   spool off

4. vi analyze_index.sql
필요없는 Line 제거 및 정리

5. @analyze_index.sql

===========================================================================================================================
===========================================================================================================================

출처 : http://blog.naver.com/vxxv122?Redirect=Log&logNo=130128144052

정의

→ 비용기반 옵티마이저에서 통계정보를 모아주기 위한 튜닝 도구.

테이블과 인덱스, 클러스터, 컬럼에 대한 통계정보를 수집.

권한

→ Analyze Any 시스템 권한

통계 수집

1 . 테이블

→ Analyze Table 테이블명 Compute Statistics;

* Row수, 사용된 Block수, 사용안된 Block수, 사용가능한 평균공간, 변경된 Row수, 컬럼당 distinct 값수

컬럼당 두번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값

(질문) 왜 두번째로 가장 작은값과 큰값을 ?

2 . 인덱스

→ Analyze Index 인덱스명 Estimate Statistics;

* 인덱스 레벨, 레벨 Block수, distinct Key수, Key당 Leaf와 Data Block수 평균, Clustering Factor, 최소 Key 값

최대 Key값

3 . 클러스터

→ Analyze Cluster 클러스터명 Delete Statistics;

* Cluster Key당 길이의 평균

4 . 컬럼

→ Analyze Table 테이블명 Compute Statistics For Table;

→ Analyze Table 테이블명 Compute Statistics For Columns 컬럼명 Size 75;

→ Analyze Table 테이블명 Compute Statistics For All Indexed Columns Size 75;

* 디볼트 버켓 수는 75개

Distinct 한 값의 수, 히스토그램 정보

유효성 검사

→ Analyze Table 테이블명 Validate Structure [Cascade];

* 검사하려는 테이블과 관련된 모든 테이블을 검사하려면 Cascade 옵션 사용.

옵션

1 . Compute

→ 테이블에 저장되어 있는 모든 행을 대상으로 통계정보 수집

2 . Estimate

→ 오라클 서버의 자동화 알고리즘에 의해 데이터를 추출하여 통계정보를 수집

3 . Delete

→ 수집되어 있는 통계정보를 삭제

* 20,000건 이하의 Row는 Compute 권장, 이상은 Estimate 권장.

확인

1 . User_Tables

2 . User_Tab_Columns

3 . User_Indexes, Index_Stats, Index_histogram

4 . User_Cluster

5 . DBA_Analyze_Objects

===========================================================================================================================
===========================================================================================================================

출처 : http://www.oracleclub.com/article/23928

1. 개요
- TABLE, COLUMN, 그리고 INDEX 에 대한 통계 정보를 수집 하게 하는 PROCEDURE

2. SYNTAX
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME          VARCHAR2,
                               TABNAME          VARCHAR2,
                               PARTNAME         VARCHAR2 DEFAULT NULL,
                               ESTIMATE_PERCENT NUMBER   DEFAULT TO_ESTIMATE_PERCENT_TYPE
                                                                            (GET_PARAM('ESTIMATE_PERCENT')),
                               BLOCK_SAMPLE     BOOLEAN DEFAULT FALSE,
                               METHOD_OPT       VARCHAR2 DEFAULT GET_PARAM('METHOD_OPT'),
                               DEGREE           NUMBER   DEFAULT TO_DEGREE_TYPE(GET_PARAM('DEGREE')),
                               GRANULARITY      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
                               CASCADE          BOOLEAN DEFAULT TO_CASCADE_TYPE(GET_PARAM('CASCADE')),
                               STATTAB          VARCHAR2 DEFAULT NULL,
                               STATID           VARCHAR2 DEFAULT NULL,
                               STATOWN          VARCHAR2 DEFAULT NULL,
                               NO_INVALIDATE    BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE (
                                                                 GET_PARAM('NO_INVALIDATE')),
                               FORCE            BOOLEAN DEFAULT FALSE);

3. PARAMETER 설명
- DBMS_STATS.SET_PARAM 에 의해서 디폴트 파라미터 설정 변경이 가능하다.
- 수동 통계정보 생성 시에 저정을 하지 않았을 때 적용되는 DEFAULT 값에 영향을 미치는 값
    => CASCADE
    => DEGREE
    => ESTIMATE_PERCENT
    => METHOD_OPT
    => NO_INVALIDATE
    => GRANULARITY
- 자동 통계정보(GATHER_STATS_JOB) 시에만 영향을 미친다.
    => AUTOSTATS_TARGET [ AUTO   : ORACLE이 자동으로 대상 OBJECT 결정,
                          ALL    : 대상 시스템의 모든 OBJECTS
                          ORACLE : SYS/SYSTEM OBJECT 만 ]

1) DEFAULT 값 확인
SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;

    DBMS_STATS.GET_PARAM('METHOD_OPT')
    ----------------------------------
    FOR ALL COLUMNS SIZE AUTO

2) DEFAULT 값 변경
    EXECUTE DBMS_STATS.SET_PARAM('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1') ;

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

3) 변경된 DEFAULT 값 확인

SELECT DBMS_STATS.GET_PARAM('METHOD_OPT') FROM DUAL ;

    DBMS_STATS.GET_PARAM('METHOD_OPT')
    ----------------------------------
    FOR ALL COLUMNS SIZE 1

Parameter	Description
OWNNAME	분석할 테이블 소유자
TABNAME	테이블 이름
PARTNAME	파티션 이름, 지정 하지 않으면 NULL 값
ESTIMATE_PERCENT	- 분석할 Row의 Percentage, NULL 이면 Compute(Row 전체) - 유효값은 1/1000000 ~ 100 - 디폴트로, DBMS_STATS.AUTO_SAMPLE_SIZE 에 의해서 최적의 값을 결정
BLOCK_SAMPLE	- random block sampling or random row sampling 결정 - random block sampling 이 좀더 효과적이다. - 데이터의 블록 별 분포도가 안좋을 시에는 부적절한 정보 생성 - 디폴트 값이 False로, random row sampling 을 수행한다.
METHOD_OPT	- Histogram 생성시 사용하는 옵션 * FOR ALL [ INDEXED \| HIDDEN ] COLUMNS [ size_clause ] * FOR COLUMN [ size clause ] column \| attribute [size clause] [, column\|attribute [ size clause ]…] - Size_clause := SIZE { integer \| REPEAT \| AUTO \| SKEWONLY } => Integer : Histogram Bucket 수, Max 는 1,254 => REPEAT : 이미 Histogram 이 있는 칼럼에 대해서만 생성 => AUTO : 데이터 분산도와 칼럼 부하 정도에 따라서 생성 결정 => SKEWONLY : 데이터 분산도에 따라서 생성 결정 - 디폴트 값은 FOR ALL COLUMNS SIZE AUTO 이다. - 즉, Histogram 의 생성여부를 Oracle 이 알아서 판단하게 된다. - 이 경우 EX) method_opt => FOR ALL COLUMNS SIZE 1 => 모든 컬럼에 대해서 Histogram bucket 의 수를 1로 한다. 즉, 컬럼 내에 존재하는 여러 가지 값들의 cardinality 는 모두 동일한 값으로 간주한다. ( histogram 을 사용하지 않는다.) 이것은 where 조건에 들어오는 특정 컬럼에 대한 값의 변화에 따라서 PLAN 이 변경될 가능성을 없애고자 함이다. - FOR ALL COLUMNS SIZE 1 로 설정하여 Histogram 정보가 생성되지 않도록 조치 한다. - Histogram 설명 : http://wiki.gurubee.net/display/CORE/Gathering+Histogram
DEGREE	- 병렬처리 정도 - 디폴트 값은 NULL 이고, CREATE TABLE, ALTER TABLE 시 설정된 DEGREE 값에 의해 정해진다. - AUTO_DEGREE 값은 병렬처리 정도를 자동으로 결정한다. - 이것은 1 or DEFAULT_DEGREE [ Object Size 와 CPU Count 에 의해 결정 ]
GRANULARITY	- Parition table 에 대한 분석시 사용 => ‘ALL’ : Global, Partition, Subpartition 통계정보 수집 – Parition Table 대상 => ‘AUTO’ : 디폴트 값으로 ,Partition Type 에 따라서 결정 – 일반 Table 대상 => ‘DEFAULT’ : Global, Partition 통계정보 수집, Old Version 과 호환을 위해 사용 => ‘GLOBAL’ : Global 통계정보 수집 => ‘GLOBAL AND PARTITION’ : SubPartition 에 대한 통계정보는 수집되지 않는다. => ‘PARTITION’ : Partition 통계정보 수집 => ‘SUBPARTITION’ : SubPartition 통계정보 수집
CASCADE	- 대상 테이블의 인덱스에 대한 통계수집 여부 - 인덱스 통계정보는 병렬처리가 불가능하다. - TRUE : 대상 테이블에 관련된 index 에 대해서 통계정보 수집
STATTAB	- 통계수집을 통한 기존 통계정보 Update 전에, 기존에 존재하는 통계정보를 저장할 User Stat Table 을 지정
STATID	- Stattab 와 연관된 구분자 값
STATOWN	- Stattab 에 지정한 User Stat Table 의 소유자가 다를 경우 지정
NO_VALIDATE	- 의존적인 Cursor를 Invalidate 할지 , 안할지 결정 => True : 관련된 Cursor 를 invalidate 하지 않는다. => False : 디폴트 값으로, 관련된 Cursor 를 Invalidate 한다. - Default 로 DBMS_STATS.AUTO_INVALIDATE 값이고, 의미는 DBMS 가 의존적 Cursor를 언제 invalidate 할지 자동으로 결정 - 이때 작용하는 Parameter는 _OPTIMIZER_INVALIDATION_PERIOD 이고, Default 롤 18000 초(5시간) 이다. - 즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에 해당 Cursor가 실행될 때 invalidation이 발생한다. - 이것을 Auto Invalidation이라고 부른다. - 일정 시간에 걸쳐 랜덤하게 Cursor를 Invalidation함으로써 특정 시점에 Hard Parse가 한꺼번에 몰리는 현상을 피할 수 있다.
FORCE	- Lock 걸린 Table 에 대해서도 강제로 통계정보 생성

4. Test
1) Mission
    CASCADE => TRUE
        E 인덱스에 대한 통계정보도수집하라.

    CASCADE => FALSE
        E 인덱스에 대한 통계정보도수집하라.

    METHOD_OPT =>'FOR ALL COLUMNS SIZE 1'
        E 칼럼(HIGH AND LOW COLUMN VALUE)에 대한 통계정보도 수집하라.

    METHOD_OPT =>'FOR COLUMNS'
        E컬럼에 대한통계정보를 수집하지 마라

2) Test
2-1) 일반 테이블
SHOW USER

USER IS"SYS"

① SCOTT의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블, 칼럼(HIGHAND LOW COLUMN VALUE),
연관 인덱스의 통계정보를 생성한다.( COMPUTE STATISTICS )

       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                         TABNAME    => 'BIG_TABLE',
                                         CASCADE    => TRUE,
                                         METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');

    ② SCOTT의 BIG_TABLE의 15% ROW를 가지고, 테이블, 칼럼, 연관인덱스의 통계정보를 생성한다.( SAMPLE 15 PERCENT )
       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'SCOTT',
                                         TABNAME          => 'BIG_TABLE',
                                         CASCADE          => TRUE,
                                         ESTIMATE_PERCENT => 15);

    ③ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블의 통계정보를 수집하라.
       인덱스와 칼럼에 대한 통계정보는 제외
       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                         TABNAME    => 'BIG_TABLE',
                                         CASCADE    => FALSE,
                                         METHOD_OPT => 'FOR COLUMNS');

    ④ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과인덱스에 대한 통계정보를 수집하라.
       칼럼에 대한 통계정보는 제외
       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                         TABNAME    => 'BIG_TABLE',
                                         CASCADE    => TRUE,
                                         METHOD_OPT => 'FOR COLUMNS');

⑤ SCOTT의 BIG_TABLE의 전체 테이블과 모드 인덱스를 가지고, 테이블과 컬럼(NO HISTOGRAM),

그리고 인덱스에 대한 통계정보를 수집하라.
잠시 후에 인덱스 칼럼들의 HISTOGRAM 통계정보를 수집하라.

       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
                                         TABNAME => 'BIG_TABLE',
                                         CASCADE => TRUE);

잠시 후에..

       EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    =>'SCOTT',
                                          TABNAME    => 'BIG_TABLE',
                                          CASCADE    => TRUE,
                                          METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');

⑥ SCOTT 의 BIG_TABLE 의 전체 테이블과 모드 인덱스를 가지고, 테이블과 인덱스칼럼(ONLY HIGH AND LOW )에

       대한 통계정보를 수집하라. 인덱스에 대한 통계정보는수집하지 마라.
        EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                          TABNAME    => 'BIG_TABLE',
                                          CASCADE    => FALSE,
                                          METHOD_OPT => 'FOR ALL INDEXED COLUMNSSIZE 1');

2-2) PARTITIONTABLE 의 경우
    ① 추가적으로 GRANULARITY 정보를 'ALL', 'AUTO', 'PARITION', 'GLOBAL AND PARTITION', 'GLOBAL', 'SUBPARTITION'을

       통해서 통계수집 대상 TABLE SEGMENT를 선정 가능하다.

    ② 참고
        - LOCK VS DBMS_STATS.GATHER_TABLE_STATS
            : DML 이 LOCK 이 발생 하여도 GATHER_TABLE_STATS 는 정상적으로 진행된다.

            BEGIN
                FOR I IN 1001 .. 5000 LOOP
                    INSERT INTO CHECK_LOCK VALUES ( I , I , 'LOCK');
                END LOOP ;
            END ;
            /

        - @CHECK_USER_LOCK.SQL
            ENTER VALUE FOR USER_NAME: SCOTT10
            OLD 46: AND B.USERNAME =UPPER('&USER_NAME')
            NEW 46: AND B.USERNAME =UPPER('SCOTT10')

            USERNAME    SID LOCK_TYPE       MODE_HELD   MODE_REQUE LOCK_ID1 LOCK_ID2
            ---------- ---- --------------- ----------- ---------- -------- --------
            SCOTT10     151 DML             ROW-X (SX) NONE       51782    0
            SCOTT10     151 TRANSACTION     EXCLUSIVE   NONE       131077   307

        - EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCOTT10',TABNAME => 'CHECK_LOCK');

        ==> DML LOCK 과는 무관하게 진행 된다.

========================================================================

ANALYZE 와 DBMS_STATS 의 수집정보 차이

참고 : http://jedah.tistory.com/entry/ANALYZE-%EC%99%80-DBMSSTATS-%EC%9D%98-%EC%88%98%EC%A7%91%EC%A0%95%EB%B3%B4-%EC%B0%A8%EC%9D%B4

=================================================================================

index rebuild 작업

출처 : http://aozjffl.tistory.com/285

질문 :

index에 관해서 공부를 하다가 궁금한 점이 있어 이렇게 질문을 등록합니다.

테이블에 데이터가 자주 들락날락해서 delete된 데이터의 index가 그대로 남아있어, 필요이상으로 많은 공간을 차지할 때 한다는 것을 알고 있습니다.

그런데, 이렇게 필요이상으로 index가 공간을 많이 차지한다는 것을 어떻게 알 수 있을까요?

답변1 :

아래에 있는 두 방법의 결과로 판단합니다.

첫번째 방법 :

우선 분석하고자 하는 인덱스에 대한 통계정보를 생성합니다.

SQL> analyze index 인덱스명 compute statistics;
-- 수백만건 이상의 row를 지닌 테이블에 대한 인덱스인 경우 compute statistics 대신에 estimate 옵션을 사용할 것.

아래 쿼리 결과의 blevel값이 4가 넘으면 index rebuild를 하는 것이 좋습니다.
이 blevel(Branch level)이 의미하는 것은 오라클이 index access를 할 때 몇 단계를 거쳐서 블럭의 위치를 찾아가는 가와 관계가 있습니다.

SQL> select index_name, blevel
from user_indexes
where index_name = '인덱스명';

두번째 방법 :

아래의 쿼리를 이용하여 index_stats테이블에 추가적인 인덱스 정보를 생성합니다.

SQL> analyze index 인덱스명 validate structure;

아래 쿼리 결과의 pct_deleted가 20%이상으로 나타나면 rebuild 대상입니다.

그리고 distinctiveness는 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는지를 보여주는 값입니다.

예를 들면, 만일 1만건의 row와 9000건의 서로 다른 값을 가진 테이블이 있을 때 distinctiveness값은 다음과 같이 계산됩니다.

(10000-9000)*100/10000 = 10 => 컬럼의 값이 잘 분산되어 있음

또 다른 예를 들면, 1만건의 row가 있지만 2가지 값으로만 중복되어 있다면 distintiveness값은

(10000-2)*100/10000 = 99.98 => rebuild 대상이 아니라 bitmap index로 만들 대상(99%이상이면 bitmap index 대상)

SQL> select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) pct_deleted,
            (lf_rows-distinct_keys)*100/decode(lf_rows,0,1,lf_rows) distinctiveness
     from index_stats
     where name = '&index_name';

답변2 :

답변2도 답변 1과 같은 내용이지만 스크립트를 사용함으로써, 한번에 여러개의 rebuild 대상 index를 찾을 수 있다는 장점이 있다.

결과 정보를 휘발성으로 저장하는데, 그 결과를 통해서 rebuild 대상을 체크할 수 있음.
주의할 점은 아래의 쿼리 실행시 Lock이 발생하므로, 업무시간 이외에 할 것을 강추함.

SQL> analyze index index_name validate structure ;

rebuild_indx.sql

REM =============================================================
REM
REM rebuild_indx.sql
REM
REM Copyright (c) Oracle Software, 1998 - 2000
REM
REM Author : Jurgen Schelfhout
REM
REM The sample program in this article is provided for educational
REM purposes only and is NOT supported by Oracle Support Services.
REM It has been tested internally, however, and works as documented.
REM We do not guarantee that it will work for you, so be sure to test
REM it in your environment before relying on it.
REM
REM This script will analyze all the indexes for a given schema
REM or for a subset of schema's. After this the dynamic view
REM index_stats is consulted to see if an index is a good
REM candidate for a rebuild or for a bitmap index.
REM
REM Database Version : 7.3.X and above.
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt - deleted entries represent 20% or more of the current entries
prompt - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt - when distinctiveness is more than 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
c_name INTEGER;
ignore INTEGER;
height index_stats.height%TYPE := 0;
lf_rows index_stats.lf_rows%TYPE := 0;
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
distinct_keys index_stats.distinct_keys%TYPE := 0;
cursor c_indx is
select owner, table_name, index_name
from dba_indexes
where owner like upper('&schema')
and owner not in ('SYS','SYSTEM');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner Index Name % Deleted Entries Blevel Distinctiveness');
dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');

c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
ignore := DBMS_SQL.EXECUTE(c_name);

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;
--
-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current entries
-- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
--
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;

end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

spool off
set verify on
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

샘플 결과

Sample Output
-------------

Rebuild the index when:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Possible candidate for bitmap index:

- when distinctiveness is more than 99%

Owner Index Name % Deleted Entries Blevel Distinctivenes
--------------- ------------------------- ----------------- ------ -------------
SMS PLATMAP_I1 31.504 1 32.927
SMS PLATMAP_I2 27.682 1 29.399
SMS PLATMAP_I3 31.237 1 31.237
SMS PRODMAP_I4 8.765 5 99.9
SMS SB_CR_BOM_ITEMS_I1 34.496 2 97.356
SMS SB_CR_OS_VERSIONS_I1 51.942 1 68.063
SMS SB_CR_RELEASES_I1 34.584 1 18.426
SMS TAR_HEAD_I2 21.728 5 22.344

PL/SQL procedure successfully completed.

참조 : http://cafe.naver.com/prodba/16759
http://oracleclub.com/article/46676

10진수를 2진수로 변환

방법 1.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (SIGN (BITAND (DECI, POWER (2, (TRUNC (LOG (2, DECI) + POWER (10, -20)) + 1 - LEVEL)))), ',')), ',') BIT
FROM (SELECT 6 DECI FROM DUAL)
CONNECT BY POWER (2, LEVEL - 1) <= DECI

방법 2.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (BIT, ',')), ',') BIN
      FROM (    SELECT COUNT (*) OVER () - LEVEL + 1 LVL
                     , DECODE (TRUNC (DECI / POWER (2, LEVEL)), ROUND (DECI / POWER (2, LEVEL)), 0, 1) BIT
                  FROM (SELECT 7 DECI FROM DUAL)
            CONNECT BY POWER (2, LEVEL - 1) <= DECI)
START WITH LVL = 1
CONNECT BY PRIOR LVL = LVL - 1

2진수를 10진수로 변환

SELECT SUM (SUBSTR (BIN, LEVEL, 1) * POWER (2, LENGTH (BIN) - LEVEL)) DECI
FROM (SELECT 11101 BIN FROM DUAL)
CONNECT BY LEVEL <= LENGTH (BIN)

======================================================================================================
======================================================================================================

10진수로 저장한 컬럼 1개의 값으로 체크박스 checked 설정

붉은색 : 컬럼값
파란색 : 체크박스 개수

첫번째 체크박스만 체크 : 1
두번째 체크박스만 체크 : 2
첫번째/두번째 체크박스만 체크 : 3
세번째 체크박스만 체크 : 4
첫번째/세번째 체크박스만 체크 : 5
두번째/세번째 체크박스만 체크 : 6
모든 체크박스 체크 : 7

SELECT REVERSE(LPAD(REPLACE (MAX (SYS_CONNECT_BY_PATH (SIGN (BITAND (DECI, POWER (2, (TRUNC (LOG (2, DECI) + POWER (10, -20)) + 1 - LEVEL)))), ',')), ','), 3, '0')) BIT
FROM (SELECT 6 DECI FROM DUAL)
CONNECT BY POWER (2, LEVEL - 1) <= DECI

======================================================================================================
======================================================================================================

Java에서 진수 변환

10진수를 2진수로 변환
Integer.toBinaryString(7)

10진수를 2진수로 변환시 공백 문자열로 채워서 (붉은색이 채워질 문자, 파란색이 자리수)
String.format("%010d", Integer.parseInt(Integer.toBinaryString(7)))

2진수를 10진수로 변환
Integer.valueOf("100", 2)

SYS_CONNHECT_BY_PATH 에 ESC (ASCII 27) 문자를 구분자로 처리할 경우

ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH function

이런 에러가 발생한다.

특수문자를 사용할 경우 아래처럼 처리할 수 있다.

Sample

SELECT CHR(27) FROM DUAL -- ASCII 코드로 문자 확인

SELECT RAWTOHEX ('') FROM DUAL -- HEX 코드 확인

SELECT UNISTR('\001B') FROM DUAL -- HEX 코드를 유니코드로 변환

WITH TB1 AS (
SELECT 1 COL1, NULL COL2 FROM DUAL
UNION ALL
SELECT 2, 1 FROM DUAL
UNION ALL
SELECT 3, 1 FROM DUAL
UNION ALL
SELECT 4, 2 FROM DUAL
UNION ALL
SELECT 5, 2 FROM DUAL
UNION ALL
SELECT 6, 3 FROM DUAL
)
SELECT PATH1
, TRANSLATE(PATH1, '_', '_') PATH2
, TRANSLATE(PATH1, '_' || CHR(27), '_') PATH3
, REPLACE(PATH2, CHR(27), ' > ') PATH4
, PATH3 PATH5
, REPLACE(PATH3, CHR(10), ' > ') PATH6
, REGEXP_SUBSTR(PATH1, '[^]+', 1, 1) CODE1
, REGEXP_SUBSTR(PATH1, '[^'||CHR(27)||']+', 1, 2) CODE2
, REGEXP_SUBSTR(PATH1, '[^'||CHR(27)||']+', 1, 3) CODE3
FROM (
SELECT SYS_CONNECT_BY_PATH(COL1, '') PATH1
, SUBSTR(SYS_CONNECT_BY_PATH(COL1, ''), 2) PATH2
, SUBSTR(SYS_CONNECT_BY_PATH(COL1, '
'), 2) PATH3
FROM TB1
START WITH COL2 IS NULL
CONNECT BY PRIOR COL1 = COL2
ORDER SIBLINGS BY COL1)

XML 에서 ESC 문자 출력

참고 : http://www.dpawson.co.uk/xsl/sect2/N3353.html
HTML ISO-8859-1 참고 : http://blog.naver.com/tyboss/70152845514

Java 에서 ESC 문자 출력

char esc = '\u001B';
char esc2 = 27;

1. Cursor 사용하기

DECLARE
    V_TBL CSM_WORK_ORG_DTL_TBL%ROWTYPE;
    CURSOR CUR1 IS SELECT CASINO_BUSN_DATE, SFT_NO FROM CSM_WORK_ORG_DTL_TBL;
BEGIN
    OPEN CUR1;
    LOOP
        FETCH CUR1 INTO V_TBL.CASINO_BUSN_DATE, V_TBL.SFT_NO;
        EXIT WHEN CUR1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_TBL.CASINO_BUSN_DATE || V_TBL.SFT_NO);
    END LOOP;
    CLOSE CUR1;
END;
/

2. Cursor 사용하지 않고 Loop

BEGIN
    FOR CUR1 IN (SELECT CASINO_BUSN_DATE, SFT_NO FROM CSM_WORK_ORG_DTL_TBL) LOOP
        DBMS_OUTPUT.PUT_LINE(CUR1.CASINO_BUSN_DATE || CUR1.SFT_NO);
    END LOOP;
END;
/

출처 : http://scidb.tistory.com/30

기획팀에서 이대리가 전산실에 와서 부탁을 한다.

기획실 이대리:"월별 부서별로 2002년도 실적을 좀 뽑아주실수 있나요?"

전산실 김대리:"네 오늘저녁 6시쯤 오시면 실적 보고서를 드릴수 있습니다."

기획실 이대리:"실적을 만드실때 주의사항이 있습니다.
   월별 부서별로 실적을 뽑을때 만약 20번 부서에서 5월, 7월에
   실적이 없다고 하더라고 5월,7월 실적을 0 으로 표시해주세요."

전산실 김대리:"네 알겠습니다. 그것은 별로 어렵지 않습니다."

년월만 들어있는 테이블과 월별부서별실적 테이블의 구조는 아래와 같다.

월별 부서별 실적테이블의 2002년 실적은 다음 그림과 같다.
모든 월에 실적이 있는것은 아니다.(예를 들면 10번 부서는 2002년도에 1,3,6,7,8,10,11 월에 실적이 없다. )

30번 부서부터는 지면관계상 그림에서 생략하였다.

기획실 이대리의 요구사항은 아래그림과 같다.
(실적이 없는달은 실적을 0 으로 표시함)

30번 부서의 실적부터는 지면관계상 그림에서 생략하였다.

다행히 월별, 부서별 실적 테이블이 존재하기 때문에 김대리는 묵묵히 월별 실적 SQL을 아래처럼 작성하였다.
아래처럼 작성한 이유는 부서가 20개(10번부터 200번까지) 있기 때문에 부서별로 무조건 12건(1월~12월)을 만들기 위해서 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 10 --> 10번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 20 --> 20번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 30 --> 30번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'
Union all
...........................................................................................중간생략
Union all
SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m , dept_sale_history e
WHERE m.yymm = e.yymm(+)
AND e.DEPTNO(+) = 200 --> 200번부서에 대해서 1월~12월 실적을 만듬.
AND m.yymm like '2002%'

우연히 김대리의 작업을 지켜보던 전산실 박과장이 한마디 한다.
전산실 박과장 :"김대리 그작업할때 200번이나 노가다(Union All) 할생각이냐?
"SQL 공부좀해라"

김대리에게 호통을 친 박과장은 자신감 있게 아래의 SQL을 1분만에 만들었다.

SELECT dept_month.deptno, dept_month.yymm, NVL(e.sale_amt,0)
   FROM (SELECT d.deptno, m.yymm
   FROM ( SELECT c.deptno
   FROM DEPT c
WHERE EXISTS (SELECT 1
   FROM dept_sale_history d
   WHERE d.deptno = c.deptno
      AND d.yymm like '2002%')) d,
   ( SELECT m.yymm
      FROM year_month m
      WHERE m.yymm like '2002%' ) m
   ) dept_month,    --> 월별 부서별 집합을 먼저 만든다.
dept_sale_history e
WHERE dept_month.deptno(+) = e.deptno
AND dept_month.yymm(+) = e.yymm

위의 SQL 의 핵심은 모든 부서에 대하여 1월~12월 까지 와꾸?(틀)를 만들어 놓고
부서별 월별실적 테이블과 아우터 조인을 하기위해서 이다.
위의 SQL 에서 EXISTS 를 사용한 이유는 2002 년도에 실적이 있는 부서만 뽑기 위해서다.
하지만 위의 SQL 도 비효율이 있다.
부서별 월별 실적테이블을 2번이나 ACCESS 하였다.

박과장의 작업을 옆에서 지켜보던 신입사원이 고개를 기우뚱 하며 박과장에게 말을 건낸다.
전산실 신입사원:"dept_sale_history" 테이블을 2번 사용하지 않고도 같은 실적을 뽑을수 있습니다."
전산실 박과장 :"그래? 그럼 한번해봐"

신입사원을 지켜보던 박과장은 경악을 금치 못한다.
신입사원이 20초만에 SQL 을 작성하고도 성능은 신입사원의 SQL이 우수했기 때문이다.
단 4줄의 SQL 로 기획팀 이대리의 요구사항을 해결하였다.
박과장은 SQL 을 사용한지 10년이 넘는 배테랑 개발자 이지만 10g 신기능은 써보지 못한 상태였다.
아래의 SQL이 신입사원의 SQL 이다.

SELECT e.deptno, m.yymm, NVL(e.sale_amt,0)
FROM year_month m LEFT OUTER JOIN dept_sale_history e
PARTITION BY (e.deptno) ON (m.yymm = e.yymm )
WHERE m.yymm like '2002%';

신입사원이 위의 SQL 을 사용할수 있었던건 처음 배운 SQL 문법이 Oracle 10g 기준이었고
박과장은 Oracle 8 버젼의 SQL을 공부 해었기 때문이다.

위의 Partition Outer Join 은 10g 의 새기능이다.
Partition Outer Join 의 기능을 요약하면 부서별로 중간중에 빠진 월의 실적을 생성해주는 기능이다.

결론 :
Partition Outer Join 은 10g 의 신기능중 일부에 불과하다.
버전별로 New Features의 중요성을 다시한번 강조하지만 위의 경우와 같이
신기능을 모르면 작업량이 늘어날수 밖에 없고 대부분 성능도 느리다.
또한 Oracle 8.0 시절에 최적화된 SQL 이 항상 Oracle 10g 에서 최적화된 SQL 이라고 볼수 없다.
Oracle 9i 가 나온지는 10년이 됬으며 Oralce 10g 가 나온지도 6년이 지났고 2년전에 Oracle 11g 가 나왔다.
신버젼이 나올때 마다 알라딘의 요술램프처럼 주인님이 사용해주기를 기다리는 마술 같은 여러가지 신기능이 숨어있다는 점을 기억하자.

===========================================================================================
===========================================================================================

출처 : http://ukja.tistory.com/361

데이터 모델링을 하다보면 비정규화의 유혹에 빠지는 경우가 있습니다. 이유는 여러가지가 있겠지만, 대표적인 한가지 이유는 우리의 데이터 인식법과 관계형 데이터베이스의 표현 양식에서 차이가 있다는 것입니다. 가령 특정 테이블에 있는 인덱스별로 컬럼 목록을 보고 싶다면, 우리 눈에는 이렇게 보는 것이 가장 편합니다.

T1_N1  C1, C2, C3
T1_N2  C3
T1_N3  C2, C1

하지만 관계형 데이터베이스는 정규화 법칙에 의해 다음과 같이 데이터를 저장하게 됩니다. 보기가 어렵죠.

T1_N1  C1
T1_N1  C2
T1_N1  C3
T1_N2  C3
T1_N3  C2
T1_N3  C1

정규화된 형태에서 데이터를 추출하면 우리가 데이터를 인식하는 방법과 달라서 대단히 불편하게 느끼게 됩니다. 이런 이유 때문에 비정규화의 유혹이 생길 수 있습니다. (엄격하게 말하면 위의 예는 제 1 정규형에 해당합니다)

즉 비정규화란 "우리가 데이터를 인식하는 방식 그대로" 표현하고자 하는 욕구를 의미한다고 할 수 있습니다. 비정규화를 하면 쿼리가 간단해지는 것도 이런 이유 때문입니다. 더 정확하게 말하면 우리 눈에 간단해보이는거죠. 물론 성능을 위한 적절한 비정규화는 삶의 양념같은 역할을 하기도 합니다. 너무 빡빡한 삶은 재미가 없잖아요? ^^;

다행스러운 것은 오라클이 제공하는 (비교적 최신의) 기능들을 잘 이용하면 관계형으로 저장된 데이터를 비교적 간단하게 우리가 원하는 비정규화된 형태로 추출할 수 있다는 것입니다.

간단한 예제를 볼텐데요, 우선 다음과 같이 테이블 T1과 총 6개의 인덱스(Primary Key 포함)을 만듭니다.

create table t1(
c1 number primary key,
c2 number,
c3 number,
c4 number
);

create index t1_n1 on t1(c1, c2);
create index t1_n2 on t1(c2, c3);
create index t1_n3 on t1(c3);
create index t1_n4 on t1(c3, c1, c2);
create index t1_n5 on t1(c1, c2, c4);

우리의 목적은 다음과 같은 표 형태로 인덱스별, 컬럼별 순서와 Unique 여부를 보는 것입니다. U가 붙으면 Unique이고, T1_N4의 경우에는 (C3, C1, C2)로 이루어져 있다는 의미입니다.

Index_name    C1   C2   C3   C4
-----------          --   --   --   --
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

위의 같은 표현 양식에는 일반적으로 표현하기 힘든 두 가지 쿼리 패턴이 숨어 있습니다.

Missing Value 채우기: 인덱스 T1_N1의 경우를 보면 컬럼 (C1, C2)로 구성되어 있습니다. 하지만 전체 컬럼은 (C1, C2, C3, C4)로 이루어져 있죠. 이때 C3, C4가 Missing Value가 됩니다. Missing Value까지 다 포함해야 위와 같은 형태의 결과를 얻을 수 있습니다.
Row 값을 Column으로 변환하기: 인덱스 T1_N1의 경우를 보면 (C1(1), C2(2), C3(X), C4(X)) 로 구성됩니다. Missing Value 채우기를 통해 데이터를 만들었다고 하더라도 이 데이터들을 Row로 되어 있기 때문에 컬럼 형태로 변환해야 합니다.

위의 두 가지 요구 사항은 매우 보편적으로 발생합니다. 최신 버전의 오라클에서는 Partition Outer Join과 Listagg 함수를 이용하면 비교적 간단하게 구현할 수 있습니다. 그렇지 않다면 좀 더 수고스러운 과정을 거쳐야할 것이구요.

우선 Missing Value를 채우는 가장 보편적인 방법은 값을 중복해서 복사하는 것입니다. 가령 다음과 같이 인덱스별, 컬럼별로 모든 조합이 다 나오도록 중복된 데이터 셋을 만들면 됩니다.

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
SYS_C0016792         C1
SYS_C0016792         C2
SYS_C0016792         C3
SYS_C0016792         C4
T1_N1                C1
T1_N1                C2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2
T1_N2                C3
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3
T1_N3                C4
T1_N4                C1
T1_N4                C2
T1_N4                C3
T1_N4                C4
T1_N5                C1
T1_N5                C2
T1_N5                C3
T1_N5                C4

위와 같은 형태의 데이터를 만드는 가장 보편적인 방법은 복사를 위한 피봇(Pivot) 테이블을 이용하는 것입니다. 아래와 같이 DBA_TAB_COLUMNS 뷰를 피봇 테이블로 활용할 수 있습니다. DBA_TAB_COLUMNS와 DBA_INDEXES 뷰는 정상적인 조인이 불가능합니다. DBA_INDEXES 뷰에는 컬럼 정보가 없기 때문입니다. 따라서 Cartesian Join이 발생하게 되고 그로 인해 인덱스별로 컬럼 수만큼 복사가 발생하게 됩니다.

-- pivot table
select
i.index_name,
i.uniqueness,
tc.column_id,
tc.column_name
from
dba_tab_columns tc,
dba_indexes i
where
tc.owner = 'TPACK'
and tc.table_name = 'T1'
and tc.owner = i.owner
and tc.table_name = i.table_name
-- 컬럼에 대한 조인 필터 없음 ==> Cartesian Join
order by
i.index_name, tc.column_id
;

-- 그 덕분에 다음과 같이 복사가 이루어짐
INDEX_NAME           UNIQUENES COLUMN_ID COLUMN_NAME
-------------------- --------- ---------- --------------------
SYS_C0016792         UNIQUE             1 C1
SYS_C0016792         UNIQUE             2 C2
SYS_C0016792         UNIQUE             3 C3
SYS_C0016792         UNIQUE             4 C4
T1_N1                NONUNIQUE          1 C1
T1_N1                NONUNIQUE          2 C2
T1_N1                NONUNIQUE          3 C3
T1_N1                NONUNIQUE          4 C4
T1_N2                NONUNIQUE          1 C1
T1_N2                NONUNIQUE          2 C2
T1_N2                NONUNIQUE          3 C3
T1_N2                NONUNIQUE          4 C4
T1_N3                NONUNIQUE          1 C1
T1_N3                NONUNIQUE          2 C2
T1_N3                NONUNIQUE          3 C3
T1_N3                NONUNIQUE          4 C4
T1_N4                NONUNIQUE          1 C1
T1_N4                NONUNIQUE          2 C2
T1_N4                NONUNIQUE          3 C3
T1_N4                NONUNIQUE          4 C4
T1_N5                NONUNIQUE          1 C1
T1_N5                NONUNIQUE          2 C2
T1_N5                NONUNIQUE          3 C3
T1_N5                NONUNIQUE          4 C4

Missing Value를 채운 후, 그 데이터를 DBA_IND_COLUMNS 뷰와 조인하면 우리가 원하는 완전한 형태의 정보를 얻을 수 있습니다.

select
c.index_name,
decode(ic.column_position,null,'',
  decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
c.column_name,
c.column_id,
ic.column_position
from
(
select
  i.table_owner,
  i.table_name,
  i.index_name,
  i.uniqueness,
  tc.column_id,
  tc.column_name
from
  dba_tab_columns tc,
  dba_indexes i
where
  tc.owner = 'TPACK'
  and tc.table_name = 'T1'
  and tc.owner = i.owner
  and tc.table_name = i.table_name
order by
  i.index_name, tc.column_id
) c,
dba_ind_columns ic
where
c.table_owner = ic.table_owner(+)
and c.table_name = ic.table_name(+)
and c.index_name = ic.index_name(+)
and c.column_name = ic.column_name(+)
order by
c.index_name, c.column_id
;

INDEX_NAME           I COLUMN_NAME           COLUMN_ID COLUMN_POSITION
-------------------- - -------------------- ---------- ---------------
SYS_C0016792         U C1                            1               1
SYS_C0016792           C2                            2
SYS_C0016792           C3                            3
SYS_C0016792           C4                            4
T1_N1                  C1                            1               1
T1_N1                  C2                            2               2
T1_N1                  C3                            3
T1_N1                  C4                            4
T1_N2                  C1                            1
T1_N2                  C2                            2               1
T1_N2                  C3                            3               2
T1_N2                  C4                            4
T1_N3                  C1                            1
T1_N3                  C2                            2
T1_N3                  C3                            3               1
T1_N3                  C4                            4
T1_N4                  C1                            1               2
T1_N4                  C2                            2               3
T1_N4                  C3                            3               1
T1_N4                  C4                            4
T1_N5                  C1                            1               1
T1_N5                  C2                            2               2
T1_N5                  C3                            3
T1_N5                  C4                            4               3

이제 남은 일은 Row로 존재하는 데이터를 Column 형태로 표현하는 것입니다. 아래와 같이 DECODE 함수와 MAX 함수를 조합하면 완전한 형태의 피보팅(Pivoting)이 이루어집니다.

-- pivoting and decode
col col_list format a50
with x as (
select
  c.index_name,
  decode(ic.column_position,null,'',
   decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
  c.column_name,
  c.column_id,
  ic.column_position
from
  (
  select
   i.table_owner,
   i.table_name,
   i.index_name,
   i.uniqueness,
   tc.column_id,
   tc.column_name
  from
   dba_tab_columns tc,
   dba_indexes i
  where
   tc.owner = 'TPACK'
   and tc.table_name = 'T1'
   and tc.owner = i.owner
   and tc.table_name = i.table_name
  order by
   i.index_name, tc.column_id
  ) c,
  dba_ind_columns ic
where
  c.table_owner = ic.table_owner(+)
  and c.table_name = ic.table_name(+)
  and c.index_name = ic.index_name(+)
  and c.column_name = ic.column_name(+)
order by
  c.index_name, c.column_id
)
select
x.index_name,
rpad(max(decode(x.column_id,1,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,2,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,3,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
rpad(max(decode(x.column_id,4,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')
  as col_list
from
x
group by
index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

Oracle 11gR2에서는 LISTAGG 함수를 이용하면 훨씬 간단한 방법으로 구현할 수 있습니다. 그 이전 버전이라면 XMLAGG 함수를 사용할 수 있을 것입니다.

-- pivoting and listagg
col col_list format a50
with x as (
select
  c.index_name,
  decode(ic.column_position,null,'',
   decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
  c.column_name,
  c.column_id,
  ic.column_position
from
  (
  select
   i.table_owner,
   i.table_name,
   i.index_name,
   i.uniqueness,
   tc.column_id,
   tc.column_name
  from
   dba_tab_columns tc,
   dba_indexes i
  where
   tc.owner = 'TPACK'
   and tc.table_name = 'T1'
   and tc.owner = i.owner
   and tc.table_name = i.table_name
  order by
   i.index_name, tc.column_id
  ) c,
  dba_ind_columns ic
where
  c.table_owner = ic.table_owner(+)
  and c.table_name = ic.table_name(+)
  and c.index_name = ic.index_name(+)
  and c.column_name = ic.column_name(+)
order by
  c.index_name, c.column_id
)
select
x.index_name,
listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '')
  within group (order by x.column_id)
  as col_list
from
x
group by
x.index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

Partition Outer Join을 사용하면 Cartesian Join을 사용할 필요없이 훨씬 효율적으로 Missing Value를 채울 수 있습니다.

아래 결과를 보면 DBA_TAB_COLUMNS 뷰와 DBA_IND_COLUMNS 뷰를 Outer Join하는 것만으로 Missing Value를 채울 수가 없다는 것을 알 수 있습니다.

-- what is partition outer join?
select
ic.index_name,
tc.column_name,
ic.column_position
from
(select
  column_id,
  column_name
from
  dba_tab_columns
where
  owner = 'TPACK'
  and table_name = 'T1'
) tc
left outer join
(select
  index_name,
  column_position,
  column_name
from
  dba_ind_columns
where
  table_owner = 'TPACK'
  and table_name = 'T1'
) ic
on (tc.column_name = ic.column_name)
;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N3                C3                                 1
T1_N4                C3                                 1
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C4                                 3

하지만 다음과 같이 Partition Outer Join을 사용하면 DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 Outer Join을 수행합니다. 따라서 Missing Value를 완벽하게 채울 수 있습니다.

select
ic.index_name,
tc.column_name,
ic.column_position
from
(select
  column_id,
  column_name
from
  dba_tab_columns
where
  owner = 'TPACK'
  and table_name = 'T1'
) tc
left outer join
(select
  index_name,
  column_position,
  column_name
from
  dba_ind_columns
where
  table_owner = 'TPACK'
  and table_name = 'T1'
) ic
partition by (ic.index_name)   -- DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 아우터 조인
on (tc.column_name = ic.column_name)
;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
SYS_C0016793         C2
SYS_C0016793         C3
SYS_C0016793         C4
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3                                 1
T1_N3                C4
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N4                C3                                 1
T1_N4                C4
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C3
T1_N5                C4                                 3

위에서 얻은 결과를 LISTAGG 함수를 이용하면 원하던 포맷의 결과를 얻을 수 있습니다.

-- partition outer join & listagg
col col_list format a50
with x as (
select
  i.index_name,
  tc.column_id,
  ic.column_position,
  tc.column_name,
  decode(ic.column_position,null,'',
   decode(i.uniqueness,'UNIQUE','U','')) as is_unique
from
  (
   (
   select
    column_id,
    column_name
   from
    dba_tab_columns
   where
    owner = 'TPACK'
    and table_name = 'T1'
   ) tc
   left outer join
   (select
    index_name,
    column_position,
    column_name
   from
    dba_ind_columns
   where
    table_owner = 'TPACK'
    and table_name = 'T1'
   ) ic
   partition by (ic.index_name)
   on (tc.column_name = ic.column_name)
   left outer join
   (select
    index_name,
    uniqueness
   from
    dba_indexes
   where
    table_owner = 'TPACK'
    and table_name = 'T1'
   ) i
   on (i.index_name = ic.index_name)
  )
order by
  ic.index_name, tc.column_id
)
select
x.index_name,
listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '')
  within group (order by x.column_id)
  as col_list
from
x
group by
x.index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3

적다보니 너무 길어졌네요. 오라클이 제공하는 기능을 잘 이용하면 그다지 어렵지 않게(?) 정규화된 관계 모델로부터 우리가 인식하는 형식의 데이터로 표현할 수 있다는 정도의 의도로 봐주시면 되겠습니다.

===========================================================================================
===========================================================================================

출처 : http://blog.naver.com/savemyface/130020495511

Oracle 10g SQL의 개선 사항 중, 보고서 작성에 유용한 기능을 한가지 알아봅니다.

Partitioned Outer Join은 기존의 Outer Join을 개선한 것으로서, OLAP 환경의 Dimension 테이블과 Fact 테이블을 조인하는 경우, 유용하게 사용 할 수 있습니다.

구체적인 설명 보다는 간단한 예를 들어 살펴보겠습니다.

times 테이블에는 2006년도 1월~12월의 시계열 데이터가 포함되어 있습니다.

SQL> create table times
2 (year number,
3 month number);

테이블이 생성되었습니다.

SQL>
SQL> insert into times(year, month)
2 select 2006, rownum from all_objects where rownum <=12;

12 개의 행이 만들어졌습니다.

SQL>
SQL> select * from times;

      YEAR      MONTH
---------- ----------
      2006          1
      2006          2
      2006          3
      2006          4
      2006          5
      2006          6
      2006          7
      2006          8
      2006          9
      2006         10
      2006         11
      2006         12

12 개의 행이 선택되었습니다.

sales 테이블에는 년도 및 월별 제품 판매량 데이터가 포함되어 있습니다.

SQL> create table sales
2 (year number,
3   month number,
4   prod varchar2(10),
5   quantity number);

테이블이 생성되었습니다.

SQL> insert into sales values(2006, 1, 'PS3', 25);

SQL> insert into sales values(2006, 5, 'PS3', 32);

SQL> insert into sales values(2006, 5, 'X-box', 54);

SQL> insert into sales values(2006, 6, 'PS3', 21);

SQL> insert into sales values(2006, 6, 'X-box', 67);

SQL> insert into sales values(2006, 7, 'PS3', 21);

SQL> insert into sales values(2006, 7, 'X-box', 26);

SQL> insert into sales values(2006, 8, 'X-box', 15);

SQL> insert into sales values(2006, 11, 'PS3', 27);

SQL> insert into sales values(2006, 12, 'PS3', 25);

SQL> insert into sales values(2006, 12, 'X-box', 15);

SQL> select * from sales
2 order by prod, year, month, quantity;

      YEAR      MONTH PROD         QUANTITY
---------- ---------- ---------- ----------
      2006          1          PS3                25
      2006          5          PS3                32
      2006          6          PS3                21
      2006          7          PS3                21
      2006         11          PS3                27
      2006         12          PS3                25
      2006          5         X-box              54
      2006          6         X-box              67
      2006          7         X-box              26
      2006          8         X-box            15
      2006         12         X-box              15

11 개의 행이 선택되었습니다.

작성하고자하는 결과 형식은 다음과 같습니다.

즉, 빨간색으로 표시된 행은 해당 년도 및 월에 해당 제품의 판매량이 존재하지 않음을 의미합니다.

예를 들어, PS3는 2006년도 2월에 판매량이 전혀 없음을 나타냅니다.

제품명           년도          월           수량
---------- ---------- ---------- ----------
PS3               2006          1             25
PS3               2006          2
PS3               2006          3
PS3               2006          4
PS3               2006          5             32
PS3               2006          6             21
PS3               2006          7             21
PS3               2006          8
PS3               2006          9
PS3               2006         10
PS3               2006         11            27
PS3               2006         12            25
X-box            2006          1
X-box            2006          2
X-box            2006          3
X-box            2006          4
X-box            2006          5             54
X-box            2006          6             67
X-box            2006          7             26
X-box            2006          8             15
X-box            2006          9
X-box            2006         10
X-box            2006         11
X-box            2006         12            15

이런 결과를 만들기 위해서는 sales 테이블을 prod 컬럼의 종류에 따라 파티션하고,

각 파티션을 times 테이블과 outer join을 수행해야합니다.

이를 위해서, Oracle 10g 부터는 손쉽게 사용 할 수 있는 Partitioned Outer Join을 소개하였습니다.

SQL> select s.prod, t.year, t.month, s.quantity
2 from sales s partition by (s.prod)
3 right outer join times t
4 on s.year=t.year and s.month=t.month;

PROD           YEAR      MONTH    QUANTITY
---------- ---------- ---------- ----------
PS3               2006          1             25
PS3               2006          2
PS3               2006          3
PS3               2006          4
PS3               2006          5             32
PS3               2006          6             21
PS3               2006          7             21
PS3               2006          8
PS3               2006          9
PS3               2006         10
PS3               2006         11            27
PS3               2006         12            25
X-box            2006          1
X-box            2006          2
X-box            2006          3
X-box            2006          4
X-box            2006          5             54
X-box            2006          6             67
X-box            2006          7             26
X-box            2006          8             15
X-box            2006          9
X-box            2006         10
X-box            2006         11
X-box            2006         12            15

24 개의 행이 선택되었습니다.

Thë 굽은ㄴr무™

Database/Oracle

[Oracle] SID, Service Name (thin) 용어 정리

[Oracle] 오라클 동적 쿼리 (Dynamic Query) 사용법

[Oracle] 오라클 커서 (cursor) 사용 방법

[Oracle] 오라클 쿼리 시간

[Oracle] 파티션 테이블 (Partition table) insert 성능

[Oracle] 오라클 Table/Index Analyze (DBMS_STATS.GATHER_TABLE_STATS, Index rebuild) 통계정보 확인 및 실행방법

ANALYZE 란?

ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 한다.

문법

정보수집

테이블 정보수집 예제
SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

새로운 정보를 구하기 전에 기존 정보를 삭제
SQL> ANALYZE TABLE emp DELETE STATISTICS;

통계 정보의 확인 예제

[Oracle] 10진수 - 2진수 변환

[Oracle] SYS_CONNECT_BY_PATH 구분자(delimiter) 특수문자(ESC) 사용하기

[Oracle] 오라클 PLSQL 사용하기

[Oracle] PARTITION OUTER JOIN

+ Recent posts

티스토리툴바

Database/Oracle

ANALYZE 란?

ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 한다.

문법

정보수집

테이블 정보수집 예제SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

새로운 정보를 구하기 전에 기존 정보를 삭제SQL> ANALYZE TABLE emp DELETE STATISTICS;

통계 정보의 확인 예제

티스토리툴바

테이블 정보수집 예제
SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

새로운 정보를 구하기 전에 기존 정보를 삭제
SQL> ANALYZE TABLE emp DELETE STATISTICS;