실전DB 튜닝- Oracle편

지난 4일동안 교육받은 내용을 간단히 정리~!

 

혹 Oracle DB튜닝 사용자시라면 참조만 하세요.

 

 

---

◆1일차
1. 성능이슈
- 응답시간 = service time + wait time
- 성능이슈의 대부분은 wait time
- wait time 의 대부분은 I/O

2. 게시판 설계시 고려점
- HEADER와 본문을 물리적으로 1개의 테이블로 생성시 내용이 긴경우(LOB,LONG) 1개의 ROW가 1개이상의 BLOCK을 차지 할수 있음
- 본문을 분리설계함으로써. 게시판의 목록(HEADER)은 빨리 나오게 하고 필요시 내용을 보게 하는 방식으로 성능향상을 꾀할수 있음

3.특정 컬럼의 NULL 에 대한 고민 필요
- SQL상에 NVL함수 사용한다고 하면 해당 NVL사용으로 INDEX사용이 불가능하게 됨
- NVL을 사용한다고 하면 설계시점부터 NULL 허용할지, NULL대신 디폴트값을 설정할지에 대한 고민이 필요함

4. 부분범위 VS 전체 범위 => ONLINE VS BATCH
5. 조인 : 정규화 이후 엔터티들간의 관계를 통해 처리
6. 실행계획을 확인한다는것은 ?
⇒ INDEX를 사용하는지만  확인하는것이 아닌 적절한 INDEX를 사용하는지 내가 의도하는대로 실행이되는지를 확인하는것이다.
7. 적절한 INDEX 선택은 조건에 따른 다르다. 조건에 따라서 INDEX를 유도하거나, OPTIMIZER에게 맡기거나 해야함
8. NVL(MAX(SEQ),0) +1  ⇒ NULL값이 나올경우를 고려

9. NL 조인 : 소량의 RANDOM ACCESS 에 유리
- Oute Table/ Inner Table
- Driving Table / Lookup Table
- Driving Table 범위가 적을것
- 3개이상의 NL조인시 첫번째 조인 결과 건수가 적을수로 유리
- Join 연결조건(Lookup Table)의 Index의 유무가 중요
⇒ 양쪽에 연결조건에 Index가 없으면 Sort Merge Join이나 Hash Join으로 처리
- 데이터량이 작은 Table에 Index가 없고 Join연결이 있다면 Index를 생성해야 함.

10. Sort Merge Join : 전체 범위 처리, Sort의 대상 양이 중요함

11. Hash Join : Build Input(T)을 처리해서 HASH함수로 파티셔닝하여 HASH AREA에 LOADING  ⇒  Proved Input(T)을 읽어 HASH함수를 메모리에서 확인해서 처리
⇒ FULL SACN => Multi Blok I/O



2일차
1. not in  VS not exist 의 차이점
- not in 은 null제외됨 not exist는 null포함값도 처리

2.sort 가 발생하는 경우
distinct
union
minus
intersect
subquery(제공자)  <ㅡ> 확인자 서브쿼리 col in(select col from tab where ….)
                                                                = exist (select col from tab where …)


3. 제공자 subquery ⇒ 확인자 서브쿼리가 될수 있게 전환 unnest 힌트 혹은 exist -> in

4. Index 사용이 최선은 아니다.
- index는 single blok I/O, full scan은 multi block I/O임, index사용시 범위가 많은경우 full scan이 더 좋음

5. 합계를 위한 Grouping : 특정 부서의 실적과 부서 전체 실적을 구할때  ⇒ P.115
- copy_t, 혹은 ROLLUP 함수 이용

6. Group by 절의 함수사용은 자제한다. ⇒ p120
⇒ Select절에서 외부에서 함수사용을 한번만 할수 있게처리 한다.

7. Literal Sql ⇒ Static Sql  Hard Parsing이 많은 경우 시스템 전체 성능에 영향을 미침
http://blog.naver.com/kang_sok/60152188327









3일차

1.DBMS_XPLAN 실습 (P.181)

CREATE TABLE STORER2 AS SELECT LPAD(LEVEL,7,'0') CUSTID      ,LPAD(LEVEL,7,'0') CUSTNM      ,'ABC'||LPAD(MOD(LEVEL,100),3,'0') CUST      ,DECODE(MOD(LEVEL,30),0,'CJGLS',              LPAD(MOD(LEVEL,100),5,'0')) STOREKEY      ,CHR(MOD(LEVEL,10)+65) CUSTTYPE  FROM DUAL  CONNECT BY LEVEL <= 1000000; ALTER TABLE STORER2 ADD CONSTRAINT CUSTID_PK PRIMARY KEY (CUSTID); DROP INDEX STORE2_IDX1; CREATE INDEX STORE2_IDX1 ON STORER2(CUST,STOREKEY,CUSTTYPE); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'STORER2'); EXEC DBMS_STATS.GATHER_INDEX_STATS(USER,'STORE2_IDX1'); alter system flush buffer_cache; alter system flush shared_pool; SELECT /*+ GATHER_PLAN_STATISTICS */       COUNT(DISTINCT CUSTNM)  FROM STORER2 A WHERE STOREKEY IN ('CJGLS','00001')   AND CUST = 'ABC001'   AND CUSTTYPE IN ('H','B','X')   AND ROWNUM < 2000; select * from table(dbms_xplan.display_cursor(null,null,'allstats last outline'));

=========================실행 결과----------------------- Row# Plan_table_output 1 SQL_ID  7p7xg3t3nbfms, child number 0 2 ------------------------------------- 3 SELECT /*+ GATHER_PLAN_STATISTICS */        COUNT(DISTINCT CUSTNM)   FROM STORER2 A  WHERE STOREKEY IN ('CJGLS','00001')    AND 4 CUST = 'ABC001'    AND CUSTTYPE IN ('H','B','X')    AND ROWNUM < 2000 5 6 Plan hash value: 667456922 7 8 -------------------------------------------------------------------------------------------------------------------------------------------- 9 | Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | 10 -------------------------------------------------------------------------------------------------------------------------------------------- 11 |   1 |  SORT GROUP BY                 |             |      1 |      1 |      1 |00:00:00.18 |    1010 |   1088 | 73728 | 73728 |          | 12 |*  2 |   COUNT STOPKEY                |             |      1 |        |   1999 |00:00:00.21 |    1010 |   1088 |       |       |          | 13 |   3 |    INLIST ITERATOR             |             |      1 |        |   1999 |00:00:00.20 |    1010 |   1088 |       |       |          | 14 |   4 |     TABLE ACCESS BY INDEX ROWID| STORER2     |      1 |     39 |   1999 |00:00:00.20 |    1010 |   1088 |       |       |          | 15 |*  5 |      INDEX RANGE SCAN          | STORE2_IDX1 |      1 |     39 |   1999 |00:00:00.05 |      10 |     64 |       |       |          | 16 -------------------------------------------------------------------------------------------------------------------------------------------- 17 18 Outline Data 19 ------------- 20 21  /*+ 22      BEGIN_OUTLINE_DATA 23      IGNORE_OPTIM_EMBEDDED_HINTS 24      OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 25      ALL_ROWS 26      OUTLINE_LEAF(@"SEL$1") 27      INDEX(@"SEL$1" "A"@"SEL$1" ("STORER2"."CUST" "STORER2"."STOREKEY" "STORER2"."CUSTTYPE")) 28      END_OUTLINE_DATA 29  */ 30 31 Predicate Information (identified by operation id): 32 --------------------------------------------------- 33 34   2 - filter(ROWNUM<2000) 35   5 - access("CUST"='ABC001' AND (("STOREKEY"='00001' OR "STOREKEY"='CJGLS')) AND (("CUSTTYPE"='B' OR "CUSTTYPE"='H' OR 36              "CUSTTYPE"='X')))

2. 실습

EXPLAIN PLAN SET STATEMENT_ID='P1' FOR  

SELECT /*+ GATHER_PLAN_STATISTICS */

      COUNT(DISTINCT CUSTNM)

 FROM STORER2 A

WHERE STOREKEY IN ('CJGLS','00001')

  AND CUST = 'ABC001'

  AND CUSTTYPE IN ('H','B','X')

  AND ROWNUM < 2000;  

  

SELECT * FROM TABLE(dbms_xplan.display(null,'P1','advanced'));

3. 게시판 페이지 처리 기법 ⇒ 새로쓴 대용량 데이터베이스 솔루션 중간쯤에 해당 해법이 있음

4. V$SEGMENTS_STATISTICS : 해당 VIEW를 통해 I/O등의 세부내역을 확인가능하다.
- ITL waits : update transaction이 많은 경우 PCT FREE공간이 부족해질수 있다.
 ⇒ INIT TRANS값을 조정하여 늘릴수 있으나, 이경우 DATA저장 공간이 부족해져서 TRADE OFF가 있음

5. v$sqlarea 등 v$sql  동적 view활용하여 과부하 쿼리 추출 p.187
6. v$sql_monitor  ⇒ 11g 부터 지원하는 Real-Time SQL Monitor

4일차
1. 인덱스 최적화전략
- 사용하고 있는 sql추출 ⇒  Access유형 분석 ⇒인덱스 생성⇒ 옵티마이저 실행계획 확인

2. Trace 분석을 통한 성능개선
- where조건의 subquery가 공급자 인가? 확인자 인가? 에 따라 Join순서가 변경됨
- 적절한 Access범위가 되도록(공급자 쿼리형태가 되도록) 힌트, 인덱스 생성등을 해줘야 함

3. 배치 처리 프로그램 개선
- Loop형 로직 ⇒ sum(decode 등을 이용 IF … ELSE조건을 한번에 처리
- UPDATE와 INSERT를 동시 처리하는 경우 MERGE INTO 활용
- Fetch한 값을 조건에 따라 반복하는경우 Fetch시점에 not exist 를 이용
- 반복구문에 의한 union 집합은 WITH 절을 활용
- INSERT ALL : 조건이 없는경우 , Insert First : 조건이 있는경우
 하나의 Insert문장으로 multi row를 하나의 table혹은 multi table에  insert하는 경우에 사용
 arrey processing (type)으로 처리시 FORALL 구문과 함께