반응형

*I/O 튜닝의 핵심 원리

 하나, Random 액세스 발생량을 줄인다.

 둘, Sequential 액세스에 의한 선택 비중을 높인다.

 

1. 테이블 Random 액세스 최소화 튜닝 - SQL전문가 가이드 p.628~p.630

(하나, Random 액세스 발생량을 줄인다.)

1) 인덱스 칼럼 추가
2) Covered Index
3) Include Index
4) IOT, 클러스터형 인덱스, 클러스터 테이블 활용
5) 수동으로 클러스터링 팩터 높이기

 

2. 인덱스 스캔범위 최소화 - SQL전문가 가이드 p.631~p.637

(둘, Sequential 액세스에 의한 선택 비중을 높인다.)

1) 인덱스 선행 칼럼이 범위조건일 때의 비효율

    - 인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를

스캔하는 단계에서 비효율이 발생한다.


2) 범위조건을 In-List로 전환

   - 인덱스 선두 칼럼의 between 조건을 IN-List 조건으로 바꿀 때 IN-List 개수가 적어야 한다. IN-List 개수가 많을 때는, between 조건 때문에 리프 블록을 추가로 스캔하는 비효율보다 IN-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 때 특히 그렇다.

   - Oracle이라면 범위조건에서 Index Skip Scan이 유용할 수 있다. Index Skip Scan은 인덱스 선두 칼럼이 누락됐을 때뿐만 아니라 부등호, between, like 같은 범위검색 조건일 때도 사용될 수 있다. 

 

 (가) 1차 단계 - BETWEEN

 SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

 FROM 매물아파트매매

 WHERE 아파트시세코드 = 'A0100'

 AND 평형 = '59'

 AND 평형타입 = 'A'

 AND 인터넷매물 BETWEEN '1' AND '2'

 ORDER BY 입력일 DESC

 ;

 

 (나) 2차 단계 - IN

SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드

FROM 매물아파트매매

WHERE 아파트시세코드 = 'A0100'

AND 평형 = '59'

AND 평형타입 = 'A'

AND 인터넷매물 IN ('1',  '2')

ORDER BY 입력일 DESC

;


 3) 조건을 2개 이상 사용할 때의 비효율
 - 인덱스 구성 [회사+지역+상품명]일 때, 아래와 같이 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.

 

(가) 1차 단계

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE 회사 = :com

AND 지역 LIKE :reg || '%'

AND 상품명 LIKE :prod || '%'

;

 

(나) 2차 단계

  A) CASE 1 - 만약 지역 칼럼에 대한 검색조건이 입력되지 않을 수도 있어 LIKE 연산자를 사용할 것이라면 SQL을 아래와 같이 만들어 사용하는 것이 좋다.

    <SQL1>

    SELECT 고객ID, 상품명, 지역, ...

    FROM 가입상품

    WHERE 회사 = :com

    AND 상품명 LIKE :prod || '%'

    ;

   <SQL2>

   SELECT 고객ID, 상품명, 지역, ...

   FROM 가입상품

   WHERE 회사 = :com

   AND 지역 = :reg

   AND 상품명 LIKE :prod || '%'

   ;

 

 B) CASE 2 - UNION ALL을 사용하는 방법도 있다.

 - 기존의 인덱스 구성 하에서, UNION ALL 상단 쿼리는 기존과 동일한 비효율을 안은 채 수행되겠지만 하단 쿼리만큼은 최상으로 수행될 수 있다. 만약 UNION ALL 상단 쿼리까지 최적화하려면 [회사+상품명] 순으로 구성된 인덱스를 하나 더 추가해야 한다.

 - 인덱스를 새로 추가하는데 부담이 있으면 기존 인덱스 순서를 [회사+상품명+지역] 순으로 변경하는 것을 고려할 수 있는데, 그럴 경우(UNION ALL) 하단 쿼리를 처리할 때 불리해진다. 따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.                   

 

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE :reg IS NULL 

AND 회사 = :com

AND 상품명 LIKE :prod || '%'

UNION ALL

SELECT 고객ID, 상품명, 지역, ...

FROM 가입상품

WHERE :reg IS NOT NULL

AND 회사 = :com

AND 지역 = :reg

AND 상품명 LIKE :prod || '%'

;

반응형
반응형

 

1. 비트맵 인덱스는 Lock에 의한 DML 부하가 심하다.

   - 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 Lock이 걸린다.

   - OLTP성 환경에 비트맵 인덱스를 쓸수 없다.

   - 읽기 위주의 대용량 DW(특히, OLAP) 환경에 아주 적합하다.

  

2. Oracle B*Tree 인덱스와 달리 비트맵 인덱스는 NULL도 저장

  

       SELECT * FROM 상품

WHERE 색상 IS NULL

;

 

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

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

 

 칼럼의 Distinct Value 개수가 적을 때 비트맵 인덱스를 사용하면 저장효율이 매우 좋다. B*Tree 인덱스보다 훨씬 적은 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용하다. 다양한 분석관점(Dimension)을 가진 팩트성 테이블이 주로 여기에 속한다. 반대로 Distinct Value가 아주 많은 칼럼이면 오히려 B*Tree 인덱스보다

많은 공간을 차지한다.

 

 Distinct Value 개수가 적은 칼럼일 때 저장효율이 좋지만 테이블 Random 액세스 발생 측면에서는 B*Tree 인덱스와 똑같기 때문에 그런 칼럼을 비트맵 인덱스로 검색하면 그다지 좋은 성능을 기대하기 어렵다. 스캔할 인덱스 블록이 줄어도는 정도의 성능 이점만 얻을 수 있고, 따라서 하나의 비트맵 인덱스 단독으로는 쓰임새가 별로 없다. 그 대신, 여러 비트맵 인덱스를 동시에 사용할 수 있는 특징 때문에 대용량 데이터 검색 성능을 향상시키는 데에 효과가 있다.

 

 비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점 때문에 다양한 조건이 사용되는, 특히 정형화되지 않은 임의 질의(ad-hoc query)가 많은 환경에 적합하다.

 

SELECT 지역, SUM(판매량), SUM(판매금액)

FROM 연도별지역별상품매출

WHERE (크기 = 'SMALL' OR 크기 IS NULL)

AND 색상 = 'GREEN'

AND 출시연도 = '2012'

GROUP BY 지역

;

 

 

반응형
반응형

*인덱스를 재생성하는 데 걸리는 시간과 부하를 무시할 수 없다. 따라서 인덱스의 주기적인 재생성 작업은 아래와 같이 예상효과가 확실할 때만 시행하는 것이 바람직하다.

 

 1. 인덱스 분할에 의한 경합이 현저히 높을 때

 2. 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL Join에서 반복 액세스되는 인덱스 높이(height)가 증가했을 때

 3. 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

 4. 총 레코드 수가 일정한데도 인덱스가 계속 커질 때

 

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

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

 

 Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나빠지면 인덱스를 재생성하거나 DBMS가 제공하는 명령어를 이용해 빈 공간을 제거하는 것이 유용할 수 있다. 하지만 일반적으로 인덱스 블록에는 어느 정도 공간을 남겨두는 것이 좋다. 왜냐하면, 빈 공간을 제거해 인덱스 구조를 슬림(slim)화하면 저장 효율이나 스캔효율에 좋겠지만 인덱스 분할이 자주 발생해 DML 성능이 나빠질 수 있기 때문이다.

 

 인덱스 분할에 의한 경합을 줄일 목적으로, 초기부터 빈 공간을 남기도록 옵션을 주고 인덱스를 재생성할 수도 있다. 하지만 효과는 일시적이다. 언젠가 빈 공간이 다시 채워지기 때문이며, 결국 적당한 시점마다 재생성 작업을 반복하지 않는 한 근본적인 해결책이 되지는 못 한다.

 

 

반응형
반응형

*OR조건은 Full Table Scan된다.

 

1차 단계- OR조건은 Full Table Scan된다.

 

 SELECT * FROM EMP

  WHERE JOB = 'CLERK' OR DEPTNO = 20;

 

2차 단계- JOB과 DEPTNO에 각각 생성된 인덱스를 사용하고 싶다면 UNION ALL 사용

             

 SELECT * FROM EMP

  WHERE JOB = 'CLERK'

  UNION ALL

 SELECT * FROM EMP

  WHERE DEPTNO = 20

  AND LNNVL(JOB = 'CLERK')

 ;

 

JOB과 DEPTNO 칼럼을 선두로 갖는 두 인덱스가 각각 사용되었고, UNION ALL 위쪽 브랜치는 JOB = 'CLERK'인 집합을 읽고 아래쪽 브랜치는 DEPTNO = 20 인 집합만을 읽는다.

 

 분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, EMP 테이블 액세스가 두 번 일어난다. 따라서 중복 액세스되는 영역(DEPTNO = 20 이면서 JOB = 'CLERK')의 데이터 비중이 작을 수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다.

 

 중복 액세스되더라도 결과집합에는 중복이 없게 하려고 UNION ALL 아래쪽에 ORACLE이 내부적으로 LNNVL함수를 사용한 것을 알 수 있다. JOB <> 'CLERK' 이거나 JOB IS NULL인 집합만을 읽으려는 것이며, 이 함수는 조건식이 FALSE이거나 알 수 없는(UNKNOWN) 값일 때 TRUE를 리턴한다.

 

 *ORACLE에서 OR-Expansion 제어(힌트 사용)

 SELECT /*+ USE_CONCAT */ FROM EMP  -- OR Expansion 기능 유도

WHERE JOB = 'CLERK' OR DEPTNO = 20;

 

 SELECT /*+ NO_EXPAND */ FROM EMP  -- OR Expansion 기능 방지

WHERE JOB = 'CLERK' OR DEPTNO = 20;

 

 

P.S. 사용자 쿼리를 직접 바꿔주지 않아도 옵티마이저가 2차 단계를 대신해 주는 경우가 있는데 이를 'OR-Expansion'이라고 한다.

반응형
반응형

*조인 제거(Join Elimination) 또는 테이블 제거(Table Elimination)

 - 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 '조인 제거(Join Elimination)' 또는 '테이블 제거(Table Elimination)'라고 한다.  

 

 - 조인 제거 기능이 작동하려면 PK와 FK 제약이 설정돼 있어야만 한다. 이는 옵티마이저가 쿼리 변환을 수행하기 위한 지극히 당연한 조건이다. 만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.

 

- FK가 설정돼 있더라도 emp의 deptno 칼럼이 Null 허용 칼럼이면 결과가 틀리게 될 수 있다. 조인 칼럼 값이 Null인 레코드는 조인에 실패해야 정상인데, 옵티마이저가 조인문을 함부로 제거하면 그 레코드들이 결과 집합에 포함되기 때문이다. 이런 오류를 방지하기 위해 옵티마이저가 내부적으로 E.DEPTNO IS NOT NULL 조건을 추가해 준다. Oracle 10g까지는 조인 제거가 일어나지 않았지만 11g부터 Inner쪽 테이블 제거 기능이 구현되었다.

 

 SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL, E.HIREDATE

 FROM DEPT D, EMP E

 WHERE D.DEPTNO = E.DEPTNO;

 

 ALTER TABLE DEPT ADD

  CONSTRAINT DEPTNO_PK PRIMARY KEY(DEPTNO);

 

 ALTER TABLE EMP ADD

  CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)

  REFERENCES DEPT(DEPTNO);

 

 SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE

 FROM EMP E, DEPT D

 WHERE D.DEPTNO(+) = E.DEPTNO --- Outer 조인

반응형
반응형

'조인조건(Join Predicate) Pushdown'은 말 그대로 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서 NL Join 수행 중에 드라이빙 테이블에서 읽은 조인 칼럼 값을 Inner 쪽(=right side) 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.

 

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

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

 

1차 단계

 SELECT D.DEPTNO, D.DNAME, E.AVG_SAL

 FROM DEPT D

, (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) E

 WHERE E.DEPTNO(+) = D.DEPTNO;

 

2차 단계

 SELECT D.DEPTNO, D.DNAME

, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)

 FROM DEPT D;

 

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

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

 

1차 단계 - 집계함수가 여러 개이면 EMP에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.

 SELECT D.DEPTNO, D.DNAME

, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =  D.DEPTNO) AVG_SAL

, (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL

, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL

 FROM DEPT D;

 

2차 단계 - 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 SUBSTR함수로 분리하는 방법이 유용할 수 있다.

SELECT DEPTNO, DNAME

, TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL

, TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL

, TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL

FROM (

SELECT /*+ no_merge */ D.DEPTNO, D.DNAME

,(SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7) || MAX(SAL)

FROM EMP WHERE DEPTNO = D.DEPTNO) SAL

FROM DEPT D

);

반응형
반응형

참고 해야 할 여러 DB 가이드들입니다.

*SQL 가이드
http://www.dbguide.net/db.db?cmd=view&boardUid=148404&boardConfigUid=9&categoryUid=216&boardIdx=132&boardStep=1

1. 데이터 모델링의 이해
2. SQL 기본 및 활용
3. SQL 고급 활용 및 튜닝


*Oracle 가이드
http://www.dbguide.net/db.db?cmd=view&boardUid=12896&boardConfigUid=9&boardIdx=47&boardStep=1

1. 설치가이드
2. 11g, DBA를 위한 신기능
3. 백업, 복구 가이드
4. 솔루션 백서 가이드
5. 20가지 주요기능
6. 10g, DBA를 위한 신기

*MS-SQL 가이드
http://www.dbguide.net/db.db?cmd=view&boardUid=13785&boardConfigUid=9&boardIdx=67&boardStep=1

1. 개발자를 위한 SQL Server 2005
2. MS-SQL 2005 미러링 가이드
3. MS-SQL 2005 데이터 웨어 하이징 가이드
4. MS-SQL 2005 인덱싱된 뷰를 통한 성능 향상
5. MS-SQL 2005 업그레이드 가이드
6. MS-SQL 2005 개발자 가이드
7. MS-SQL 2005 관리자 가이드
8. 초보 DBA MS-SQL 2005 관리 가이드
9. MS-SQL 2005 성능문제 해결
10. MS-SQL 2005 데이터 통합 가이드
11. 튜닝 가이드(관리자를 위한 튜닝 가이드)
12. DBA 가이드
13. 튜닝 가이드(관리자를 위한 튜닝 가이드)
14. 트러블슈팅 가이드
15. MS-SQL 2000 운영가이드

반응형
반응형

1. JOIN QUERY - EXAMPLE

-- Data Requirements:
-- Data Base : All Client until 31 December 2008
-- Cash + Portfolio Value as of July 9th (yesterday)
-- Last Transaction Date based on last Done Transaction date

SELECT
 A10.ACNT_NO
 , A10.ACNT_NM
 , A10.ESTBL_DATE AS JOINT_DATE
 , A16.INV_DEP_CASH AS CASH_VALUE
 , A16.LAST_TR_DATE AS LAST_TR_DATE
 , T5.TOTAL AS STOCK_VALUE
 , A20.CNTC_NO AS CONTACT
 , A10.INV_ACC_NO
 ,CASE A10.INV_BANK_CODE
 WHEN '88' THEN 'SHINHAN'
 WHEN '99' THEN 'KB'
 ELSE ''
 END AS BANK_NAME
 , A10.SID
 ,A10.KSEI_ACNT_NO
FROM A100M A10, A106H A16, A200M A20, (SELECT ACNT_NO, SUM(BLQTY*CLPR) AS TOTAL FROM T500H WHERE TR_DATE= TO_CHAR(SYSDATE-1, 'YYYYMMDD') GROUP BY ACNT_NO) T5
WHERE A10.INV_ACC_NO IS NOT NULL --BANK
AND A10.INV_STA_YN ='Y' -- BANK
AND A10.ESTBL_DATE <= '20081231'
AND A16.TR_DATE= TO_CHAR(SYSDATE-1, 'YYYYMMDD')
AND A10.ACNT_NO = A16.ACNT_NO
AND A16.ACNT_NO = T5.ACNT_NO (+)
AND A16.ACNT_NO = A20.ACNT_NO (+)
ORDER BY A10.ACNT_NO;

2. 날짜 관련(TO_CHAR(SYSDATE,'YYYYMMDD')) - 아래 1)과 2) 쿼리는 동일하다.

1) SELECT * FROM EMP WHERE HIREDATE=TO_CHAR(SYSDATE,'YYYYMMDD');

2) SELECT * FROM EMP WHERE HIREDATE = '20120716';

 

반응형
반응형

 

1. [Oracle오라클 DB OCP 9i ] grant 권한 조금

http://blog.yahoo.com/_MF32LG6KKZWY6WR6ZOR4HELUPI/articles/602250

2. NULL 관련해서 명확히 동작원리를 이해
 
조인의 문법
 
함수는 책에 나오는것만 정확히
 
(특히 ceil / round / trunc 처럼 비슷하지만 조금씩 다른 함수들은..정확히 알지 못하면 단답형으로 나오면 쥐약입니다.)

3. 오라클 통계쿼리 시간별, 일별, 월별, 년도별

http://blog.naver.com/PostView.nhn?blogId=sjrmwlq1&logNo=130046501373

반응형
반응형

 1. 특정 테이블스페이스(TBS1)의 데이터파일 확인
  select file_name, bytes/1024/1024
  from dba_data_files
  where tablespace_name='TBS1';

select autoextensible
  from dba_data_files
  where tablespace_name='UNDOTBS1';

select autoextensible
  from dba_data_files;

2. TBS1에 3GB(3072MB) 크기로 /oradata/oradata/TBS1_02.dbf 데이터파일을 생성하여 추가
  alter tablespace TS_ETRD1
  add datafile '/oradata/oradata/TBS1_02.dbf'
  size 3072m;

반응형