실전DB 튜닝- Oracle편
◆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 구문과 함께
출처: http://uincity.tistory.com/225 [열심남의 일상]