Transcript PowerPoint 프레젠테이션

성능관리
성능관리
2005. 8.
김무영
1
성능관리
목 차
- 성능관리에 대하여
----------- 3
- 개발측면에서의 성능관리 단계 ------- 5
- 성능확보를 위한 설계 가이드 라인 ---- 14
- SQL 튜닝
------------------- 36
- 데이타베이스 튜닝 --------------- 70
- 시스템에서 주로 발생하는 문제들 ----- 97
- 성능관리 예제 1, 2 -------------- 103
2
성능관리
성능관리란?
퍼포먼스
무장애
성능보장
최적의
자원사용
O 성능관리자의 역할은?
O 성능관리의 책임은 누구한테 있는가?
3
성능관리
성능상의 주요요소
DB Tuning
DBA
APP’
(SQL)
Performance
Management
프로그래머
모델러
DB Modeling
etc : OS, Web서버, WAS, H/W…
4
성능관리
성능관리 단계
5
성능관리
누가 성능관리를 하는가
•
•
•
•
응용 설계자(Application Designer)
응용 개발자(Application Developer)
DBA(Database Administrator)
Hardware/Software 관리자
* 성능관리의 책임자는???
6
성능관리
언제 성능관리를 하는가 - 계획단계
계획
분석/
설계

전문성능관리 인력 및 비용 확보

하드웨어 및 소프트웨어 구성 선택

시스템 확장을 감안한 용량계획 수립

시스템 분석 및 설계표준 정립

시스템별 성능기대치 결정

성능목표 설정과 우선순위 결정은
개발
검수
분명하고 일관성 있게
운영
7
성능관리
언제 성능관리를 하는가 - 분석/설계단계

계획
성능을 감안한 데이터모델링 및
데이터베이스 설계
분석/
설계

Table간의 자동화 규칙(제한조건)의
적절한 설계

개발
성능을 감안한 프로그램 설계 및 Table
구조 변경, 추가

검수
데이터요구에 적합한 인덱스 정책수립
및 정의

데이터 모델과 User Interface 간의
적합성 검증
운영
8
성능관리
언제 성능관리를 하는가 - 개발단계

계획
옵티마이저에 대한 이해와 실행계획
조정
분석/
설계

고급SQL 사용 및 최적화

적용업무 프로그램에 적합한 Locking
범위 결정
개발

재사용 Library의 정립 및 적극적
활용
검수

운영
9
프로그램 코딩 표준의 엄격한 준수
성능관리
언제 성능관리를 하는가 – 검수(시험평가)단계
계획
분석/
설계

실제 운영상황에 견줄만한 테스트용
데이터량의 확보

개발
실제 운영환경과 동일한 시스템
구성(Configuration)

전문 튜너에 의한 성능시험 및 보증

성능관리 전문툴 활용

테스트 및 품질보증을 통과한
검수
운영
프로그램만을 현업에 적용
10
성능관리
언제 성능관리를 하는가 - 운영단계
계획
분석/
설계
개발

시스템성능에 대한 지속적인 감시

시스템 및 RDBMS 에서 제공하는 모든
성능감시도구 활용
검수
운영

O/S 와 RDBMS 튜닝간의 적합성 검증

LIVE SYSTEM과 TEST용 SYSTEM의 별도
사용
11
성능관리
언제 성능관리를 하는가 – DB 구축과정
단계
상세
절차
산
출
물
주요
내용
분석 단계
비즈니스
분석
모델링 단계
데이터
분석
논리
모델
개발 및 테스트 단계
물리
모델
DB
구현
시험
사용자 요구사
항분석서
시스템 평가서
현행 시스템 모델
정의서,
논리모델 설계서
(ERD)
물리모델 설계서
스키마 생성용
스크립트,
운영관리지침서
시험계획서,
시험결과서
• 대상조직
업무파악
• 비즈니스
프로세스
분석
• 운영환경
분석
• 사용자요구
사항분석
• 엔터티 주제
영역별 분류
• 주식별자 및
속성 식별
• 데이터 접근
권한 분석
• 데이터정규화
• 기존 시스템
데이터 모델
분석
• DB기본설계
• 데이터관계를
ERD로 표현
• 정규화 및
비정규화
• 데이터 물리
구조 및 분산
설계
• 사용 제품별
특성에 맞도록
설계 수정
• 인덱스 정책
• 무결성,접근
경로별 설계
검토
• 개발용DB구축
• 물리설계시 요
구 사항 충족
여부 검토
• DB오류 발생
여부 검토
• 개발자기술지원
• DB성능관리툴
을 이용한 튜닝
수행
• 요구사항 만
족정도 시험
• 설계상 오류
사항 발생 여
부 시험
• 검증을 통한
논리/물리 모
델 수정 반복
• 퍼포먼스 시험
12
성능관리
언제 성능관리를 하는가 - 최적의 시점
• 따라서 성능관리의 가장 효과적인 시점은 디자인 단계
=> 최소의 비용으로 최대의 효과 기대
13
성능관리
SYSTEM TUNNIG 비율
•
•
OLTP DB의 경우 : A
DW DB의 경우 : B
B.
A.
DB Modeling
Database
20%
17.5%
Program(SQL)
60%
Database Program(SQL)
15%
15%
System
System
2.5%
DB Modeling
65%
14
5%
성능관리
성능확보를 위한 설계 가이드라인
15
성능관리
설계 가이드 라인
•
DB 튜닝측면
: 옵티마이저 정책 수립 실천, 자원소모 최소화 및 병목현상 제거, 적절한 DB 환경조절
•
SQL 사용측면
: 코딩표준, Sql사용형태, 실행계획 조절, 최적성/처리횟수 고려
•
DB 모델링 측면
- 최적의 논리 모델링 수행, 인덱스 정책 수립 실천
- DB 벤더 Master에 의한 물리설계
- 적절한 DB Constraints의 사용으로 SQL수량 제한
•
운영 및 향후 개선을 염두에 둔 설계
•
시스템 전체 측면에서의 과부하 제거
- 불필요한 네트워크 트래픽 제거
- 서버, OS측면에서의 부하제거
•
예상되는 문제점 제거
16
성능관리
데이터 모델 측면(간략히)
• 사전 준비사항
– 데이터모델의 세부 다이아그램
– 테이블의 평균 로우 수, 한 로우의 평균크기등
– 데이터 증가율, 평균 사용자수 등
– 최대집중시(Peak Time) 초당 수행되는 트랜잭션 개수 및 크기등
– 기타 데이터에 대한 문서
• 튜닝되지 않은 데이터모델
– 일대일 관계, 다대다 관계, 원형함정모델등
– 온라인 트랜잭션 처리에 적절하지 않은 모델
– 과도한 조인 및 Full Table Scan 을 유발하는 데이터모델
– 단순 명료 하지않고 복잡한 형태의 모델
17
성능관리
ㅇ DB_BLOCK_SIZE & 모델링
- 평균레코드 길이가 적당
Oracle block
김무영
78030**
서홍교
79030**
임성열
68030**
조광원
88030**
윤송이
88030**
…
…
…
…
…
- 평균레코드 길이가 과도(과도한 디노말제이션)
SGA
Database
Buffer Cache
Shared
Pool
SGA
Oracle block
Redo Log
Buffer
Database
Buffer Cache
김무영 7803** ……..
…
…
DB block IO
(2k ~ 64k)
Disk IO
(2k ~ 4k)
18
Shared
Pool
Redo Log
Buffer
성능관리
인덱스정책 측면
SELECT 사원번호,성명,직위
FROM EMP
WHERE 사원번호 BETWEEN ‘2843’
AND ‘2856’
ORDER BY 1;
EMP Table
EMP_idx1
iNDEX
VALUE
ROWID/Address
사원번호
성명
2812
0000089D.0000.0001
2854
김준억
부장
2840
0000089D.0001.0001
2858
허성욱
부장
2843
0000089D.0002.0001
2812
진종건
사원
2854
0000089D.0003.0001
2855
김종훈
차장
2855
0000089D.0004.0001
2863
이정호
차장
2856
0000089D.000A.0001
2840
김무용
사원
2863
0000089D.0003B.0001
2843
이재민
사원
직위
Sort 된 결과
ㅇ 인덱스란?
ㅇ 인덱스의 종류는 어떤것이 있으며 본 개발사업에서 사용할 인덱스종류는?
B*Tree, Bitmap, F.B.I, Hash, Cluster, Domain, spatial, IOT, Functional, Reverse, compressed …
19
성능관리
1. 인덱스 선정기준
• 인덱스 대상 테이블 선정
– 테이블의 크기가 5~6블럭 이상
– 무작위접근(Random Access)이 빈번한 경우
– 특정범위/순서의 데이터조회가 필요한 경우
– Join의 연결고리가 되는 칼럼
– 참조무결성을 지정한 경우 연결고리 되는 칼럼
– Nested Join이 일어나는 테이블
• 인덱스 대상 칼럼 선정
– 분포도와 손익분기점 : 분포도는 10~15%를 넘지 않아야 한다.
– 인덱스 머지(Index Merge)
– 결합인덱스(Composite Index) : 결합되는 순서에 따라 영향이 크므로 주의
* F.B.I : Oracle 8.0 DB이상
* Index skip 알고리즘 : 9i DB이상
칼럼 1
칼럼 2
칼럼 3
칼럼 4
참고자료 : 대용량DB솔류션
20
성능관리
2. 인덱스 선정절차
• 해당 테이블의 가능한 모든 접근형태 수집
• 대상칼럼 선정 및 분포도 조사
• 반복 수행(Looping)되는 Critical Access Path의 해결
• 클러스터링 검토
• 인덱스 칼럼의 조합 및 순서의 결정
• 시험생성 및 테스트
• 수정이 필요한 Application 조사 및 수정
• 일괄적용
21
성능관리
3. 인덱스의 효율적 관리
•
•
•
•
인덱스와 데이터 테이블은 각기 다른 디스크에 배치
인덱스에 대한 적절한 공간 할당
테이블 접근없이 인덱스만으로 데이터 요구 해결
인덱스 생성 및 삭제 시점의 효율적 운용 검토
– 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하
를 줄이기 위해 인덱스를 삭제, 입력 및 로드가 끝난 이후 재생성
• 인덱스의 명명은 생성목적과 의미를 내포
Ex) Sys_30002(?), IX_dept2
• 인덱스 관리측면 감안
- ANALYZE, REBUILD, 생성 및 삭제
• 인덱스의 종류 및 특징을 감안하여 사용
: B*Tree, Bitmap, F.B.I, Hash, Cluster, Domain, spatial,
Functional, Reverse, compressed …
22
성능관리
4. 인덱스의 활용
“다음의 경우에는 인덱스가 사용되지 않는다.”
• 복합 인덱스에서 Leading Column이 제한 조건에 사용되지 못한 경우
• 인덱스칼럼의 변형
Select *
• 부정형 비교
From emp
Where 주민번호 like ’68%’
• 널을 사용한 비교
and 성별 != ‘남자’
• LIKE문의 ‘%’가 맨 처음에 위치한 경우
and 이전주소 is null
and substr(주민번호,3,2) != ’03’
• Optimizer에 의한 취사선택
Pk : 주민번호
* Where조건에서 인덱스를 사용하는 드라이빙 칼럼은
반드시 위의 조건을 지켜야하며 나머지 체크 칼럼은
부정형으로 사용하여도 무방하다.
23
성능관리
5. 인덱스가 사용되지 않을경우
인덱스 칼럼 변형
SELECT *
FROM DEPT
WHERE SUBSTR(DNAME,1,3) = 'ABC'
부정형 비교
SELECT *
FROM EMP
WHERE JOB <> 'SALES'
NULL, NOT NULL
SELECT *
FROM EMP
WHERE ENAME IS NOT NULL
Optimizer 의 취사선택
SELECT *
FROM EMP
WHERE JOB LIKE 'AB%'
AND EMPNO = '7890'
24
참고자료 : 대용량DB솔류션
성능관리
6. 인덱스칼럼의 변형(external)
SELECT *
FROM EMP
WHERE SUBSTR(DNAME,1,3) = 'ABC'
SELECT *
FROM EMP
WHERE DNAME LIKE 'ABC%'
SELECT *
FROM EMP
WHERE SAL * 12 = 12000000
SELECT *
FROM EMP
WHERE SAL = 12000000 / 12
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYMMDD') =
'940101'
SELECT *
FROM EMP
WHERE HIREDATE =
TO_DATE('940101','YYMMDD')
25
성능관리
7. 인덱스 칼럼의 변형(external)
SELECT
FROM
WHERE
AND
*
EMP
EMPNO BETWEEN 100 AND 200
NVL(JOB,'X') = 'CLERK'
SELECT *
FROM EMP
WHERE EMPNO BETWEEN 100 AND 200
AND JOB = 'CLERK'
SELECT *
FROM EMP
WHERE DEPTNO = '10'
AND JOB
= 'SALSMAN'
SELECT *
FROM EMP
WHERE DEPTNO || JOB = '10SALESMAN'
의도적인 SUPPRESSING
SELECT *
FROM EMP
WHERE JOB = 'MANAGER'
SELECT *
FROM EMP
WHERE RTRIM(JOB) = 'MANAGER'
SELECT *
FROM EMP
WHERE EMPNO = 8978
SELECT *
FROM EMP
WHERE RTRIM(EMPNO) = 8978
26
성능관리
8. 인덱스 칼럼의 변형(internal)
CREATE TABLE SAMPLET
( CHR CHAR(10),
NUM NUMBER (12,3),
VAR VARCHAR2(20),
DAT DATE)
SELECT * FROM SAMPLET
WHERE CHA = 10
SELECT * FROM SAMPLET
WHERE TO_NUMBER(CHA) = 10
SELECT * FROM SAMPLET
WHERE NUM LIKE '9410%'
SELECT * FROM SAMPLET WHERE
TO_CHAR(NUM) LIKE '9410%'
27
성능관리
INDEX COLUMN의 변형 (internal) 예제
SQL> SELECT SUM(UNCOST)
1 row,
28.5 sec
FROM CHULGOT
WHERE STATUS = 90
SQL> SELECT SUM(UNCOST)
1 row,
0.15 sec
FROM CHULGOT
WHERE STATUS = '90'
TABLE ACCESS FULL CHULGOT
SORT AGGREGATE
TABLE ACCESS BY ROWID CHULGOT
INDEX RANGE SCAN CH_STATUS
SQL> SELECT CHULNO, CUSTNO, UNCOST
FROM CHULGOT
rows,
sec
WHERE CFMDEPT LIKE '71%'
TABLE ACCESS FULL CHULGOT
NUMBER type
SQL> SELECT ORDNO, CHULNO, STATUS
NESTED LOOPS
FROM ORDER1T X, CHULGOT Y
rows,
71 sec
WHERE X.CUSTNO = Y.CUSTNO
AND X.ORDDEPT = Y.CFMDEPT
AND y.CHULDATE LIKE '9711%'
28
TABLE ACCESS FULL ORDER1T
TABLE ACCESS BY ROWID CHULGOT
INDEX RANGE SCAN CH_CFMDEPT
성능관리
9. NULL, NOT NULL
SELECT *
FROM EMP
WHERE ENAME IS NOT NULL
SELECT *
FROM EMP
WHERE ENAME > ' '
SELECT *
FROM EMP
WHERE COMM IS NOT NULL
SELECT *
FROM EMP
WHERE COMM > 0
SELECT *
FROM EMP
WHERE COMM IS NULL
29
성능관리
NOT Operator
SELECT 'Not fornd !' INTO :COL1
FROM EMP
WHERE EMPNO <> '1234'
SELECT *
FROM EMP
WHERE ENAME LIKE '김%'
AND JOB <> 'SALES'
SELECT 'OK' INTO :COL1
FROM DUAL
WHERE NOT EXISTS ( SELECT '' FROM EMP
WHERE EMPNO = '1234')
SELECT *
FROM EMP a
WHERE a.ENAME LIKE '김%'
AND NOT EXISTS ( SELECT '' FROM EMP b
WHERE a.ENAME = b.ENAME
AND b.JOB = 'SALES')
SELECT *
FROM EMP
WHERE ENAME LIKE '김%'
MINUS
SELECT *
FROM EMP b
WHERE b.JOB = 'SALES'
30
성능관리
참고자료
Modified Column Values Prevent Use of Index
ㅇ where sal * 1.1 > 50
=> where sal > 50 / 1.1
ㅇ where to_char(hiredate, ‘YYMMDD’) = ’040223’
=> where hiredate = to_date(‘040223’,’YYMMDD’)
ㅇ where substr(job,1,4) = ‘DATA’ => where job like ‘DATA%’
ㅇ where col1||col2 = :FLD
=> where col1 = substr(:FLD,1,3) and col2 = substr(:FLD,4,2)
ㅇ where ename is not null
=> where ename >= 0
ㅇ where deptno != 30
=> where deptno > 30 or deptno < 30
ㅇ number와 char의 비교
: where empno = ‘5224’
Index(X)
ㅇ number와 varchar2의 비교 : where empno = ‘5225’
Index(X)
ㅇ char와 varchar2의 비교
: where a.dept = b.dept
Index(X)
ㅇ Composite Indexes (prjno, empno)
- where prjno = 100 and empno = 100
Index(O)
- where prjno = 100
Index(O)
- where empno = 100
Index(X)
31
성능관리
OPTIMIZER
ㅇ Optimizer : 모든 관계형 DBMS에서 SQL문장을 처리하는 질의 최적화기로 30년 이상 축적된 기술을 포함
하고 있는, 인간의 지능이 가장 많이 녹아 있는 복잡한 소프트웨어이다
ㅇ 구성요소 : 질의 변환(Query Rewriter) + 실행 계획 생성(Plan Generator) + 비용 산정(Estimator)모듈
ㅇ종
류 : RBO(Rule based optimizer), CBO(Cost based optimizer)
ㅇ 오라클 Optimizer_mode
- CHOOSE : Analyze 수행시 All_Rows로 동작하고, 미수행시 Rule로 동작된다.
- RULE : 테이블과 인덱스의 통계존재 여부에 상관없이 RBO를 호출함. 규칙위주의 실행계획을 만듬.
- FIRST_ROWS(CBO) : 하나의 ROW를 가장 빨리 가져올수 있도록 최적화됨. 주로 NESTED LOOP
방식을 선호함.
- ALL_ROWS (CBO) : 전체 ROW를 가장 빨리 가져올수 있도록 최적화함.
* 힌트 사용시 RULE Hint를 제외하고 나머지는 CBO를 호출한다.
select /*+ ordered use_nl(d) */ ename, sal
from emp e, dept d
where e.deptno = d.deptno and d.loc = 'SEOUL'
ㅇ ANALYZE수행여부
ㅇ Optimizer 정책은?
32
성능관리
1. RDB, 왜 쉽고도 어려운가?
OPTIMIZER
select col1,
col2*10, . .
from account x,
custommer y,
transection z
where x.acct = z.acct
and y.cust = z.cust
and jdate = ‘970609’;
SQL
 사용자는 요구만 하고 OPTIMIZER가
실행계획 수립
 수립된 실행계획에 따라 엄청난
수행속도 차이 발생
 실행계획 제어가 어렵다.
 OPTIMIZER가 좋은 실행계획을 수립할
수 있도록 종합적이고 전략적인
FACTOR를 부여
실행
계획
작성
SQL
해석
참
조
참
조
COL$
실행
추
출
OBJ$
transaction
customer
IND$
TAB$
 비절차형, 집합적으로 접근해야 함
VIEW$
account
 SQL이 어떤 역할을 담당하도록 구현할
것인가?
 시스템에 과부하 발생량을 최소화하는
SQL구사
DATA Dictionary
33
DATA
참고자료 : 대용량DB솔류션
성능관리
2. OPTIMIZER 종류
• 규칙기반 최적화(Rule-Based Optimizer)
• 비용기반 최적화(Cost-Based Optimizer)
34
성능관리
3. OPTIMIZER의 역활
OPTIMIZER
실행계획
작성
Row
SOURSE
생성
select * from tab1x, tab2 y, tab3 z
where x.key = y.key
and z.key = x.key
and x.col1 = ‘10’
and x.col2 <> 111
and x.col3 like ‘ABC%’
and y.col4 between ‘10’ and ‘50’
참
조

처리방법
결정
Driving
무자격 배제
Nested Loops ?
 Sort Merge ?
 Hash ?
 ....... ?
처리순서
결정
결정
OBJ$
COL$
사용 가능한 액세스형태 선별
IND$
TAB$
경선 (Rule, Cost)
?

VIEW$
DATA Dictionary

Driving 선택
(나머지는 야당)
35
tab1 - tab2 - tab3
tab2 - tab1 - tab3
?
 tab3 - tab2 - tab1
?
 ....... ?
성능관리
4. Optimizer에 영향을 미치는 요소
Index,
Clustering
SQL 형태
JOIN
OPERATOR
SCALAR
SUB QUERY
FUNCTION
연산자 형태
=, LIKE, …
A = ‘상수’,
A = :변수…
A B
CB
Analytic
information
로우 수
분포도
최대값 ...
OPTIMIZER
SQL Hints
Optimizer Mode
SELECT
/*+ Use_nl(a b) */ …
FROM ...
DBMS, Version, H/W,
DB Parameter
Distributed DB
Oracle
Sort, hash
V7, 8i, 9i...
DB LINK
36
RULE
ALL_rows
First_rows
* 개발뿐만 아니라 운영시
에도 지속적인 정책 필요
참고자료 : 대용량DB솔류션
성능관리
5. RULE Based Optimizer
Rank
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Access Path
Single row by ROWID
Single row by cluster join
Single row by hash cluster with unique or primary key
Single row by unique or primary key
Cluster join
Hash cluster key
Indexed cluster key
Composite key
Single column indexes
Bounded range search on indexed columns
Unbounded range search on indexed columns
Sort merge join
MAX or MIN of indexed columns
ORDER BY on indexed columns
Full table scan
37
성능관리
6. Cost Based Optimizer
• 비용 기반 접근 방식에서의 접근 경로의 선택
– 문장에 대해 사용 가능한 접근 경로
– 각 접근 경로와 그 접근 경로들간의 상호관계에 따른 실행에 필요한
비용
• 접근 경로의 선택을 위하여 고려하는 것 : Selectivity
• Selectivity를 위한 정보
– WHERE 절에 사용되는 연산자
– WHERE 절에 사용되는 UNIQUE나 PRIMARY KEY를 가지는 칼럼들
– 그 테이블에 대한 통계치
– 각종 설정값 : Sort, hash영역 등
38
성능관리
7. OPTIMIZER의 취사 선택
RANKING 의 차이
INDEX Merge 회피
EMPNO Index만 사용
SELECT
FROM
WHERE
AND
*
EMP
ENAME LIKE 'AB%'
EMPNO = '7890'
SELECT
FROM
WHERE
AND
*
EMP
ENAME LIKE 'AB%'
JOB
LIKE 'SA%'
ENAME or
JOB index중
하나만 사용 ,
혹은 Full scan
FULL Table Scan
Low COST 의 선택
SELECT *
FROM EMP
WHERE EMPNO > '10'
JOB Index만 사용
HINT 에 의한 선택
SELECT /*+ INDEX(EMP JOB_IDX) */ *
FROM EMP
WHERE ENAME LIKE 'AB%'
AND JOB
LIKE 'SA%'
39
성능관리
8. COST_based vs RULE_based
Rule
based
Cost
based
SELECT * FROM EMP
WHERE JOB
= 'SALESMAN'
AND EMPNO = '7890'
INDEX merge
SELECT * FROM EMP
WHERE ENAME LIKE 'AB%'
AND EMPNO = '7890'
항상 EMPNO
SELECT * FROM EMP
WHERE ENAME BETWEEN '1101' AND '1210'
AND JOB
LIKE 'SA%'
항상 나중에
생성된
Index만 사용
SELECT * FROM EMP
WHERE JOB
= 'SALESMAN'
AND EMPNO = '7890'
INDEX merge
(and_equel),
SELECT * FROM EMP
WHERE ENAME LIKE 'AB%'
AND EMPNO = '7890'
분포도에 따라
ENAME index
도 사용
SELECT * FROM EMP
WHERE ENAME LIKE 'AB%'
AND JOB
LIKE 'SA%'
분포도에 따라
index 사용,
혹은 full scan
(and_equel)
Index만 사용
특정 iNDEX
40
성능관리
SQL TUNNIG
41
성능관리
SQL 튜닝 개요
• SQL튜닝이란?
• 유연한 언어
– 서로 다른 여러 개의 SQL문이 똑같은 결과를 가져올 수 있다.
• 최선의 선택
– 그러나, 주어진 환경이 똑같다면 오직 하나의 SQL만이 최상의 성능을
낼 수 있다.
– 데이터량, WHERE절, 인덱스사용, 최적화 등을 통해 SQL문의 성능을
판단하는 작업이 SQL 튜닝이다.
42
성능관리
SQL의 공유(표준화)
ㅇ 이미 수립된 실행 계획을 공유해서 재 사용
- 구문 분석 비용 감소(Hard Parse)
- 메모리 사용 개선(Literal SQL에 의한 단편화현상)
ㅇ 동일한 SQL 문장 이란?
– 텍스트 동일(대소문자 구분, 빈 칸, 주석 포함)
– 사용자 및 참조 객체 동일
– 바인드 변수의 데이터 형식 동일
ㅇ SQL 형식 표준을 정하여 코딩
– 대소문자, 빈 칸, 주석 사용 규칙
아예 SQL에 주석 사용 금지 등
– SQL 문장을 동일한 위치에 따로 관리
– 자주 쓰는 것은 PL/SQL로 대치
43
성능관리
SQL 튜닝의 3가지 접근방법
ㅇ 부하의 감소
– 동일한 부하를 보다 효율적인 방법으로 수행
– 일반적인 SQL Tuning(DB모델링 & SQL형태 조정,
실행계획 조정, 자원소모 및 경합감소등)
ㅇ 부하의 조정
– 부하 정도에 따라 업무 조정
- 배치, 리포트 업무 등과 일반 업무(OLTP)를 분리
ㅇ 응용 프로그램별, 시간대별 부하의 병렬 수행
– 병렬로 수행하여 응답 시간을 크게 단축
– 과도한 사용은 일반 사용자에게 악 영향 가능성
– 주로 배치 업무에 많이 사용됨
44
성능관리
Hints
•
Optimization Hints
– /*+ ALL_ROWS */
– /*+ FIRST_ROWS */
– /*+ AND_EQUAL */
– /*+ CLUSTER */
– /*+ COST */
– /*+ FULL */
– /*+ USE_HASH */
– /*+ HASH_AJ */
– /*+ INDEX(TAB) */
– /*+ INDEX_ASC */
– /*+ INDEX_DESC */
•
가장 적합한 Hint를 선정하여 해당 SQL문에
기술
– /*+ CHOOSE */
– /*+ RULE */
– /*+ STAR */
– /*+ PARALLEL */
– /*+ USE_HASH */
– /*+ USE_CONCAT */
– /*+ USE_MERGE */
– /*+ USE_NL */
– /*+ ORDERED */
– /*+ ROWID */
* Instance 레벨 : Optimizer_Mode = All_Rows
* Session 레벨 : Alter Session Set Optimizer_Mode = Rule
* Sql 레벨 : Hints 사용
45
성능관리
SQL TECH’
데이터 연결방법
ㅇ JOIN
- Nested Loop
- Sort Merge
- Hash
- Cartesian
- Star …..
- Out
- Etc(natural, self join)
ㅇ OPERATOR
- Union All…..Group by
- Union…..Group by
- Intersect
- Minus
ㅇ SCALAR QUERY
ㅇ SUB QUERY
ㅇ FUNCTION
Select /*+ Ordered use_hash(a b) */
a.주민번호, a.이름, (select ….from.. Where…) 계급,
b.주소, fun_idc(xxx), sum(xx)
From 인원 a, (Select /*+use_nl(x y)*/ …. -- In Line View
From … Where…) b
Where a.주민번호 = b.주민번호
and a.이름 in (Select …. From… Where….)
Group by ….
Union all (Minus, Intersect….)
Select /*+ rule */
c.주민번호, c.이름, (Select ….From… Where….) 계급,
d.주소, fun_idc(xxx), sum(xx)
From 인원 c, (Select /*+all_rows*/ …. From … Where…) d
Where c.주민번호 = d.주민번호
and c.이름 in (Select …. From… Where….)
Group by ….
46
성능관리
데이터 연결방법 1 : JOIN
SELECT 고객명, 상품구매일시, 금액 … … …
FROM 고객 X, 상품구매내역 Y
WHERE X.고객번호 =Y.고객번호
AND X. 고객번호 =’1235’;
47
참고자료 : 대용량DB솔류션
성능관리
가. NESTED LOOP JOIN
SELECT a.FLD1, ..., b.FLD1,...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = 'AB'
AND b.FLD2 = '10'
FLD1
='AB'
TABLE
ACCESS
BY
ROWID
KEY2
=
KEY1
TABLE
ACCESS
BY
ROWID
o
o
o
 순차적 (부분범위처리 가능)
운반
단위
 종속적 (먼저 처리되는 테이블의
처리범위에 따라 처리량 결정)
x
 랜덤(Random) 액세스 위주
 연결고리 상태에 따라 영향이 큼
 주로 좁은 범위 처리에 유리
97 : 3
INDEX
(FLD1)
TAB1
48
INDEX
(KEY2)
TAB2
FLD2 ='10'
check
성능관리
나. SORT MERGE JOIN
SELECT /*+ use_merge(a b) */
a.FLD1, ..., b.FLD2,...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = 'AB'
AND b.FLD2 = '10'
FLD1=
'AB'
TABLE
ACCESS
BY
ROWID
TABLE
ACCESS
BY
ROWID
a.KEY1=
b.KEY2 를
조건으로
Merge
 동시적 (무조건 전체범위처리)
 독립적 (자기의 처리범위만으로
처리량 결정)
FLD2=
'10'
.
.
.
.
.
.
S
O
R
T
.
.
.
.
.
.
.
.
.
.
.
.
S
O
R
T
.
.
.
.
.
.
 스캔(Scan) 액세스 위주
 연결고리 상태에 영향이 없음
운반단위
 주로 넓은범위 처리에 유리
* DB LINK
INDEX
(FLD1)
TAB1
49
TAB2
INDEX
(FLD2)
성능관리
다. Hash Join
SELECT B..Customer_Name, A.Order_amt
FROM CUSTOMER B, ORDER A
WHERE B.Customer_Id = A.Customer_Id
ㅇ 작은 table과 큰 table의 Join시에 유리.
ㅇ Equi Join에서만 가능.
ㅇ Driving Table에 Index를 필요로 하지 않는다.
ㅇ 각 table에 대하여 1번만 읽는다.
Customer
Partition
Hash Table
Cust
Id Name
Cust
Id Hash
Cust
Id Hash
a001
.....
b001
.....
c001
b002
....
.....
c002
....
H
A
S
H
a001
AA
b001
BB
b002
BB
c001
CC
c002
CC
H
A
S
H
a001
A1
b001
B1
b002
B2
c001
C1
c001
C2
Order
Partition
Hash Table
Ord
Id
Cust
Id
Amt
Cust
Id Hash
Cust
Id Hash
r01
b001
Seoul
r02
a001
r03
c001
....
New
r04
r05
a001
b002
....
Seoul
r06
b002
Seoul
H
A
S
H
a001
AA
a001
A1
a001
b001
AA
BB
a001
b001
A2
B1
b002
BB
b002
B2
b002
BB
b002
B2
c001
CC
c001
C1
50
서로 매칭되는
Partition별로 Hash
Key를 이용하여
조인
성능관리
데이터 연결방법 2 : Operator
51
성능관리
데이터 연결방법 3 : SCALAR SubQuery
세번째 사용자 함수를 이용한 방법 또는 네번째 SCALAR SUBQUERY를 이용한 방법은 대상 집합을 항상 한 개의 ROW로만
데이터를 연결한 후 하나의 컬럼을 Return 받는 방법이다.
결과 집합은 세로의 확장은 전혀 없고 가로의 확장은 단지 하나의 컬럼으로 확장하기 때문에 집합의 확장은 상당히 제한적
이다. 특히 사용자 함수를 사용하는 경우는 메인집합에서 추출된 ROW수만큼 반복 수행되기 때문에 응답속도에 큰 영향을 미
치게 된다. SCALAR SUBQUERY도 사용자 함수와 비슷하지만 단지 Multi Block Buffer를 사용함으로써 Buffer에있는 데이터
에 대해서는 대상 집합의 데이터를 다시 액세스하지 않는다는 점이 다르다.
52
성능관리
데이터 연결방법 4 : Function
Item별 Max Value 구하기
•
•
•
•
INDEX
S
O
R
T
운반단위
TABLE
53
성능관리
데이터 연결방법 5 : Sub Query
SUBQUERY를 이용한 방법은 집합
을 확장하는 것이 아니라 단지 특정
집합의 데이터를 추출하기 위한 조
건으로 사용하는 데이터 연결 방법
이다. 왜냐하면 SUBQUERY에 사용
된 집합의 컬럼들을 최종 결과집합
으로 추출이 불가능하기 때문이다.
그리고 SUBQUERY는 경우에 따라
서 메인 집합의 추출하기 위한 조건
의 값을 제공하는 제공자 역할을 담
당하는 경우와 메인 집합에서 추출
된 집합을 단지 체크하기 위한 확인
자 역할을 담당하는 경우등 두가지
경우로 데이터를 연결한다.
54
성능관리
데이타베이스 Constraints(제한조건)
ㅇ 어플리케이션의 코딩량을 줄일수 있다.
ㅇ SQL문장을 사용하지 않고 데이타를 제어
ㅇ 데이타베이스 설계시 정의된 설계 사상에 대하여 데이타베이스 Level에서 사전 정의
- 데이터의 일관성 및 일치성 그리고 무결성을 응용 프로그램의 추가 부담이 없이 보장
ㅇ 과도하게 사용하지 말것 : 데이타베이스 운영시 애로가 될수있다
PK(Primary Key)
해당 칼럼에 Null값을 허용하지 않고 유일한 값만 허용된다.
FK(Foreign Key)
참조무결성 보장 제한조건으로 Parent Table의 해당 칼럼의 값만 허용한다.
해당 칼럼에 유일한 값만 허용한다.
Unique
해당 칼럼에 Null값을 허용하지 않는다.
Not null
해당 칼럼에 정해진 값만 허용한다.
Check
Default
제한조건은 아니지만 해당 칼럼에 Default 값을 선언할수 있고
Not null과 조합되어 Sql튜닝에 유용하게 사용된다.
55
성능관리
SQL 관련 권고사항
•
HARD PARSING, LITERAL SQL에 의한 부하를 줄일것
- SQL이 공유되도록
•
데이터베이스에 접근하는 횟수를 줄일 것
- 고급SQL 및 DB Constraints(제한조건) 사용
•
고급 SQL(DECODE, CASE, Analytic Function, RI SQL, SQL MODEL) 활용
•
인덱스가 사용되도록 WHERE절을 기술할 것
•
DB모델에 맞는 SQL형태 사용
•
루핑문내 SQL문 사용자제(만일 어쩔수없이 사용한다면 완전 튜닝수행)
•
널 값에 대한 완전한 이해(DB Default Option사용)
•
DBMS의 자원소모(Performance Statistics)와 자원경합(Waiting event)을
이해하고 사용할것
•
옵티마이저를 이해하고 최적의 SQL이 되도록
실행계획을 제어할것(SQL형태 변경, 모델링 변경, 실행계획 변경 등)
56
성능관리
고급 SQL 활용
Decode, Case
Analytic Function
Super Update
…………….
다수의 처리를 최대한 단순하게 처리가능
시스템에 영향 최소화
유지보수 업무수행시 편리
사용되는 SQL횟수를 줄일수 있다.
데이타베이스 튜닝은 SQL튜닝으로….
57
참고자료 : 대용량DB솔류션
성능관리
고급 SQL 활용 예 : Decode
제품명
모델명
수량
매출액
HP
HJ300
1
10,000
HP
HJ300
2
20,000
HP
HA120
1
5,000
HP
HB580
1
20,000
HP
HB580
3
6,000
LD
A530G
1
100,000
LD
A530G
3
300,000
LD
A210K
2
400,000
LD
A210K
3
600,000
PP
P530C
10
100,000
PP
P530D
5
25,000
PP
R640A
10
15,000
PP
R650B
20
20,000
집계구분
요구사항
제
품
별
집
계
모
델
명
사
용
판매일자와 함께 각각의 제품에 대한 판매 내용이
있을 때 전체 판매에 대한 수량과 매출액 등의 통
계자료를 보고싶은 것이다.
단, 통계자료를 볼 때 제품명이 HP인 제품은 저가
여서 제품별로만 봐도 되니까 모델 별은 무시하고
제품별로만 수량과 매출액 합계를 내고, 제품명이
LD인 제품은 고가이므로 각 모델별로 수량과 매출
액 합계를 내고, 제품명이 PP인 제품은 저가 단품
들이므로 모든 모델을 하나로 몰아서 기타로 하여
수량과 매출액에 대한 합계를 구하라.
그러나 제품명이 PP인 제품 중에서 모델명이
P530C인 제품은 최근에 개발한 신제품이므로 기
타에 합치지 말고 별도로 빼내어 모델명까지 구분
하도록 하라.
모
델
별
집
계
기
타
58
성능관리
고급 SQL 활용 예 : Decode
제품명
모델명
수량
매출액
HP
HJ300
1
10,000
HP
HJ300
2
20,000
HP
HA120
1
5,000
HP
HB580
1
20,000
HP
HB580
3
6,000
LD
A530G
1
100,000
LD
A530G
3
300,000
LD
A210K
2
400,000
LD
A210K
3
600,000
PP
P530C
10
100,000
PP
P530D
5
25,000
PP
R640A
10
15,000
PP
R650B
20
20,000
집계구분
제품명이 HP인 제품은 저가여서 제품별로 만 봐도 되니까 모
델 별은 무시하고 제품별로만 수량과 매출액 합계를 내고, 제
품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액
합계를 내고, 제품명이 PP인 제품은 저가 단품들이므로 모든
모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합
계를 구하라.
그러나 제품명이 PP인 제품 중에서 모델명이 P530C인 제품
은 최근에 개발한 신제품이므로 기타에 합치지 말고 별도로
빼내어 모델명까지 구분하도록 하라.
제
품
별
집
계
모
델
명
사
용
기
타
모
델
별
집
계
SELECT DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECODE(모델명, 'P530C', 모델명, '기타')),
SUM(수량), SUM(매출액)
FROM 매출테이블
WHERE 매출일 LIKE '199610%'
GROUP BY DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',
DECODE(모델명, 'P530C', 모델명, '기타'));
59
성능관리
고급 SQL 활용 예
요구사항
1개의 레코드에서 3개 이상 값이 있는 행을 선별
AA
A1
A2
A3
1
2
3
11
2
7
33
3
2
8
4
33
5
6
A4
35
12
19
7
22
8
5
34
34
10
4
5
12
50
45
44
9
12
7
6
60
44
23
27
7
A6
36
43
6
11
A5
성능관리
고급 SQL 활용 예 : Decode & In Line View
select *
from (select y.aa, y.a1, y.a2, y.a3, y.a4, y.a5, y.a6,
y.x1 + y.x2 + y.x3 + y.x4 + y.x5 + y.x6 “y1”
from (select aa,
decode(a1,null,0,1) x1,
decode(a2,null,0,1) x2,
decode(a3,null,0,1) x3,
decode(a4,null,0,1) x4,
decode(a5,null,0,1) x5,
decode(a6,null,0,1) x6,
a1, a2, a3, a4, a5, a6
from ROTTO) y
)
where y1 >= 3;
AA
A1
A2
A3
1
2
3
11
2
7
33
3
2
8
4
33
5
6
A4
A5
35
12
36
19
43
7
61
A6
23
44
성능관리
고급 SQL 활용 예 : Case
아래 테이블에서 70대, 80대, 90대, 100세 이상의 수를 각각 구하라는 요구가 있다고
하자. 이것은 count(decode())와 case절을 이용하여 다음과 같이 구할 수 있다.
Ages
----100
96
93
90
88
85
79
76
76
72
select count(decode(sign(Ages-69),1,decode(sign(Ages-80),-1,1)) "70대",
count(decode(sign(Ages-79),1,decode(sign(Ages-90),-1,1)) "80대",
count(decode(sign(Ages-89),1,decode(sign(Ages-100),-1,1)) "90대",
count(decode(sign(Ages-99),1,1) "100세이상"
from tab ;
SELECT COUNT(CASE WHEN age BETWEEN 70 AND 79 THEN 1 END) as "70대",
COUNT(CASE WHEN age BETWEEN 80 AND 89 THEN 1 END) as "80대",
COUNT(CASE WHEN age BETWEEN 90 AND 99 THEN 1 END) as "90대",
COUNT(CASE WHEN age > 99 THEN 1 END) as "100세이상"
FROM tab ;
SELECT SUM(CASE WHEN age BETWEEN 70 AND 79 THEN 1 ELSE 0 END) as "70대",
SUM(CASE WHEN age BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "80대",
SUM(CASE WHEN age BETWEEN 90 AND 99 THEN 1 ELSE 0 END) as "90대",
SUM(CASE WHEN age > 99 THEN 1 ELSE 0 END) as "100세이상"
FROM tab ;
62
성능관리
고급 SQL 활용 예 : Update
Client/Server
SELECT *
FROM TAB1
WHERE SALE_DATE LIKE ‘199810%’ ;
데이터 처
리
DATA
UPDATE TAB1
SET (COL1=DECODE(…),
COL2=100,...
WHERE KEY = :KEY ;
매우 많은 로우가
리턴되고 SQL이
반복수행
SQL
Server 내에서 모두 수행하고
성공여부만 리턴
UPDATE TAB1 x
SET (COL1, COL2,….) =
(SELECT DECODE(…), 100, ...
FROM TAB2 y
WHERE x.KEY = y.KEY … )
WHERE SALE_DATE LIKE ‘199810%’
DISPLAY 로직처리,
화면제어
어떤 SQL을 사용하느냐에 따라 처리 주체가 달라지며 수행속도와 네트워크 부하에 큰 차이
63
성능관리
Analytic Function 소개 (v
8.1.6 이상)
SQL이 여러 분야에서 다양하게 사용되긴 하지만, 분석작업을 위한 강력한 기능을 가지고 있지
는 않다.
때문에 MOVING AVERAGES, RANKINGS, AND LEAD/LAG COMPARISONS 같은 기본적인
BUSINESS INTELLIGENCE CALCULATION 을 수행하기 위해서는 표준 SQL외에 과도한 프로
그램밍 작업을 요구하게 된다. 뿐만 아니라 이러한 작업은 간혹 시스템 성능에 치명적인 장애가
될 수도 있다.
ORACLE8i 8.1.6 에서는 이러한 요구들을 다루기 위해 BUSINESS INTELLIGENCE
CALCULATION에 대한 새로운 FUNCTIONS들을 제공하게 된다. 이 FUNCTIONS 들은 분석적인
작업에 유용하기 때문에 ANALYTIC FUNCTIONS 이라고 하며, 현재 SQL 표준에 추가중이다
Analytic SQL
=
Analytic SQL
Capabilities
+
Rank, N-tile, Moving averages
Cumulative sums, Lag/Lead
Ratio-to-Report, Inverse Percentiles
FIRST/LAST values
64
Aggegation
Capabilities
Rollup
Cube
Grouping
+
Models
model
성능관리
Analytic SQL Capabilities : (Rank & Dense_rank)
SELECT deptno,empno, sal,
RANK() OVER (ORDER BY sal) AS asc_rank,
RANK() OVER (ORDER BY sal desc nulls last) as desc_rank
FROM emp;
DEPTNO
EMPNO
SAL
ASC_RANK
DESC_RANK
10
7839
5000
14
1
20
7788
3000
12
2
20
7902
3000
12
2
20
7566
2975
11
4
30
7698
2850
10
5
10
7782
2450
9
6
30
7499
1600
8
7
30
7844
1500
7
8
10
7934
1300
6
9
30
7521
1250
4
10
30
7654
1250
4
10
20
7876
1100
3
12
30
7900
950
2
20
7369
800
1
SELECT deptno, empno, sal,
RANK() OVER (ORDER BY sal desc, empno
nulls last) as desc_rank
FROM emp
WHERE DEPTNO = 30;
DEPTNO
EMPNO
SAL
DESC_RANK
30
7698
2850
1
30
7499
1600
2
30
7844
1500
3
30
7521
1250
4
13
30
7654
1250
5
14
30
7900
950
6
65
성능관리
Analytic SQL Capabilities : Windowing
select deptno,empno,sal,sum(sal)
over (partition by deptno order by empno
rows unbounded preceding ) csum
from emp;
DEPTNO
EMPNO
SAL
CSUM
10
7782
2450
2450
10
7839
5000
7450
10
7934
100
7550
20
7369
800
800
20
7566
2975
3775
20
7788
3000
6775
20
7876
1100
7875
20
7902
3000
10875
30
7499
1600
1600
30
7521
1250
2850
30
7654
1250
4100
30
7698
2850
6950
30
7844
1500
8450
30
7900
950
9400
Windowing Clause :
O ROWS | RANGE
WINDOW에서 각 ROW에 대해 정의하는 부분으로 ROWS는
물리적인 단위로 WINDOW를 지정하는 것을 말하며 RNAGE
는 논리적인 상대번지로 WINDOW를 지정하는 것을 말한다.
O BETWEEN....AND
WINDOW에서 START POINT와 END POINT를 지정하는 절
로서 첫 번째 표현식은 START POINT를 나타내며 두 번째
표현식은 END POINT를 나타낸다.
O UNBOUNDED PRECEDING
"PARTITION의 첫 번째 ROW에서 WINDOW가 시작된다"는
것을 나타낸다.
select deptno,hiredate,sal,avg(sal)
over( partition by deptno order by hiredate
range interval '6' month preceding) mavg
from emp;
66
성능관리
Analytic SQL Capabilities : Ratio_To_Report
RATIO_TO_REPORT
어떤 한 집합의 합계에 대하여 각각의 비율을 구하는 report function.
RATIO_TO_REPORT (<value expression1>) OVER ([PARTITION BY <value expression2>[,...]])
다음은 부서 전체 월급에 대하여 직업별 비율을 구하기 위하여 RATIO_TO_REPORT를 사용한 사례이다.
select deptno,job,sum(sal) as sum_sal,
sum(sum(sal)) over(partition by deptno) sum_total,
ratio_to_report(sum(sal)) over(partition by deptno) as max_sum_sal
from emp a
group by deptno,job
DEPTNO
JOB
SUM_SAL
SUM_TOTAL
MAX_SUM_SAL
10
CLERK
1300
8750
0.15
10
MANAGER
2450
8750
0.28
10
PRESIDENT
5000
8750
0.57
20
ANALYST
6000
10875
0.55
20
CLERK
1900
10875
0.18
20
MANAGER
2975
10875
0.27
30
CLERK
950
9400
0.10
30
MANAGER
4100
9400
0.44
30
SALESMAN
4350
9400
0.46
67
성능관리
Analytic SQL Capabilities : NTile
SELECT calendar_month_desc AS MONTH ,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
NTILE(4) OVER (ORDER BY SUM(amount_sold)) AS TILE4
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id
AND sales.cust_id=customers.cust_id
AND sales.time_id=times.time_id
AND sales.channel_id=channels.channel_id
AND times.calendar_year=2003
AND prod_category= 'Men'
GROUP BY calendar_month_desc;
68
성능관리
N-Tile Results
MONTH
-------1999-10
1999-01
1999-11
1999-12
1999-07
1999-02
1999-06
1999-04
1999-08
1999-03
1999-09
1999-05
SALES$
-------------4,373,102
4,754,622
5,367,943
6,082,226
6,161,638
6,518,877
6,634,401
6,772,673
6,954,221
6,968,928
7,030,524
8,018,174
69
TILE4
--------1
1
1
2
2
2
3
3
3
4
4
4
Quartile 1
Quartile 2
Quartile 3
Quartile 4
성능관리
Aggegation SQL Capabilities : Rollup
SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
아래 결과에서 2개의 GROUPPING COLUMNS에 대하여 다음과 같은 3 LEVEL의 SUBTOTAL이 생성된 것을 볼 수
있다.
. GROUP BY 로 부터 생성되는 표준 집계
. DNAME별 모든 JOB의 SUBTOTAL
. GRAND TOTAL
Dname
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
All Departments
Job
CLERK
Total
1
Average Salary
15600
MANAGER
PRESIDENT
All Jobs
ANALYST
CLERK
MANAGER
1
1
3
2
2
1
29400
60000
35000
36000
11400
35700
All Jobs
5
26100
CLERK
MANAGER
SALESMAN
All Jobs
All Jobs
1
1
4
6
14
11400
34200
16800
18800
24878.5714
70
GROUP
BY
DNAM
E
LEVEL
GRAN
D
TOTA
L
성능관리
Aggegation SQL Capabilities : Cube
SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);
subtotal은 2의n승 = 4개 생성
Dname
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
Job
Total
Average
Salary
CLERK
1
15600
MANAGER
PRESIDENT
All Jobs
ANALYST
CLERK
MANAGER
1
1
3
2
2
1
29400
60000
35000
36000
11400
35700
All Jobs
5
26100
SALES
SALES
SALES
SALES
All Departments
CLERK
MANAGER
SALESMAN
All Jobs
ANALYST
1
1
4
6
2
11400
34200
16800
18800
36000
All Departments
CLERK
4
12450
All Departments
MANAGER
3
33100
All Departments
PRESIDENT
1
60000
All Departments
SALESMAN
4
16800
All Departments
All Jobs
14
24878.5714
RESEARCH
RESEARCH
RESEARCH
RESEARCH
71
GROU
P
BY
DNAM
E
LEVEL
JOB
LEVEL
GRAN
D
TOTA
L
성능관리
SQL MODEL Capabilities :Model
• Language: Spreadsheet-like calculations in SQL
–
–
–
–
–
–
–
Inter-row calculation. Treats relations as an N-Dim array
Symbolic references to cells and their ranges
Multiple Formulas over N-Dim arrays
Automatic Formula Ordering
Recursive Model Solving
Model is a relation & can be processed further in SQL
Multiple arrays with different dimensionality in one query
• Performance
– Parallel Processing in partitioning & formulas
– Multiple-self joins with one data access structure
– Multiple UNIONs with one data access structure
• Why Better?
– Automatic Consolidation (models as views – combine using SQL)
– One version of truth (calc directly over data base, no exchange)
72
성능관리
SELECT SUBSTR(country,1,20), SUBSTR(prod,1,15), year, s
FROM sales_view
MODEL
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale s)
(s['Bottle', 2002] = s['Bottle', 2001] + s['Bottle', 2000],
s['Suntan Lotion', 2002] = s['Suntan Lotion', 2001],
s['All_Products', 2002] = s['Bottle', 2002] + s['Suntan Lotion', 2002])
ORDER BY country, prod, year;
SUBSTR(COUNTRY,1,20) SUBSTR(PROD,1,1 YEAR
S
-------------------- --------------- --------------------France
All_Products
2002
25,509,995.40
France
Bottle
1998
269,995.02
France
Bottle
1999
357,842.17
France
Bottle
2000
625,473.44
France
Bottle
2001
354,302.96
France
Bottle
2002
979,776.40
France
Suntan Lotion
1998
16,110,519.40
France
Suntan Lotion
1999
18,584,977.60
France
Suntan Lotion
2000
30,123,863.60
France
Suntan Lotion
2001
24,530,219.00
France
Suntan Lotion
2002
24,530,219.00
73
성능관리
select channel_desc, prod_category, year, quantity_sold
From
(select c.channel_desc, p.prod_category, t.calendar_year year,
sum(s.quantity_sold) quantity_sold
from sales s, products p, channels c, times t
where s.prod_id = p.prod_id
and
s.channel_id = c.channel_id
and
s.time_id = t.time_id
group by c.channel_desc, p.prod_category, t.calendar_year)
sales
where channel_desc = 'Direct Sales'
Model
partition by (channel_desc)
dimension by (prod_category, year)
measures (quantity_sold)
rules (quantity_sold['Hardware', 2002]
= quantity_sold['Hardware', 2001] * 1.10)
order by channel_desc, prod_category, year;
74
성능관리
CHANNEL_DESC
---------------Direct Sales
Direct Sales
...
Direct Sales
Direct Sales
Direct Sales
Direct Sales
...
Direct Sales
Direct Sales
PROD_CATEGORY
YEAR QUANTITY_SOLD
------------------------------ ------ ------------Electronics
1998
7758
Electronics
1999
15007
Hardware
Hardware
Hardware
Peripherals and Accessories
2000
2001
2002
1998
1970
2399
2638.9
44258
Software/Other
Software/Other
2000
2001
64483
49146
75
성능관리
데이터베이스 튜닝
76
성능관리
데이터베이스 튜닝
• 메모리 튜닝
• Hit Ratio
• Free Memory의 유지
• 스와핑(Swapping)과 페이징(Paging)의 최소화
• 논리적 저장구조
• 물리적 저장구조와의 관계
• Tablespace, Extent, Block
• 저장구조의 역할
• 디스크의 분산배치
• Rollback Segment
• EXTENTS
• SQL 최적화를 위한 Tracing Tool
• Cost-Based의 Optimizer를 위한 Analyzer
77
성능관리
Oracle Database Architecture
SNPn
LCKn
Pnnn
RECO
Snnn
Dnnn
INSTANCE = SGA+BGP
Back Ground PRO’
SGA
PMON
Shared Pool
Data Buffer Cache
Redo Log Buffer
SMON
CKPT
DBWn
ARCH
SELECT EMPNO, NAME
FROM EMP
WHERE EMPNO = :NO ;
User
Server P
…….. = ‘123’;
Shared SQL Area
SQL, PL/SQL 문장
의 텍스트
SQL, PL/SQL 문장
의 분석된 형태
(Parsed form)
SQL, PL/SQL
Execute Plan
LGWR
LISTENER
Dictionary Cache
Data File
Data
Dictionary
Character Set
Network
Security
Etc…
DBMS = Instance + DB
* ORACLE DBA과정 : SQL/PLSQL, Administration, Backup & Recovery, Network, Tunnig
78
성능관리
메모리 튜닝
•
메모리 튜닝은 성능향상/추가투자의 관점
– 더 많은 메모리 = 더 적은 물리적 I/O = 더 빠른 반응시간
– 보다 적은 물리적 I/O가 되도록 구현
•
메모리 추가를 위해 System Monitor를 이용하여 진단
•
할당된 메모리 자원에 맞도록 사용자 Process의 수와 OPEN할 수 있는 CURSOR의 수
등을 할당, 제어할 것
•
이런 경우는 메모리를 더 사라 !!!
– Monitoring 의 결과 Hit Ratio 가 50 % 이하이고
– Dictionary Cache 및 Application 을 이미 튜닝했는데
– Response Time 이 형편없고
– Free Memory 도 없는 경우 !
79
성능관리
Hit Ratio
• Hit
–
–
• Hit
–
• Hit
Ratio란?
Query에 의해 요구된 데이터가 메모리에서 발견될 백분율
메모리의 효율성을 판단하는 근거가 되는 수치
Ratio 계산
Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Ratio에 영향을 미치는 요소
– Dictionary Table Activity
– Temporary Segment Activity
– Rollback Segment Activity
– Index Activity, Table Scan
* Buffer cache hit, lib cache hit
80
성능관리
Free Memory의 유지
• 최소한 전체 메모리의 5% 정도의 Free Memory를 유지
• 지속적인 Monitoring
– OS에서 제공하는 Command 이용
– CRON 혹은 Daemon process를 이용해 지속적으로 Reporting
– Peak Time시의 상태를 중점 확인
• Free Memory가 부족한 경우에는
– OS의 Kernel과 RDBMS의 Kernel이 사용하는 영역을 명확히 계산
– RDBMS에 의해 사용되는 공통영역(즉 SGA)과.
– 사용자 프로세스에 의해 사용되는 메모리 영역을 적절히 배분하여
Free Memory를 유지할 수 있도록 조정
81
성능관리
스와핑(Swapping)의 최소화
• OS 메카니즘의 하나로 메모리크기의 제한요소를 극복하기 위해 전체 프
로세스를 디스크로 이동시켜 메모리를 재 확보하는 기능
• Memory 부족 시 심각한 오버헤드 유발
• 해결방안
– 불필요한 시스템 Demon프로세스와 애플리케이션 프로세스를 실행
하지 않음
– 메모리 추가
– 타 시스템으로의 부하분산
– DBMS에서 사용하는 Memory영역(SGA)의 최적화로 사용자 프로세
스에 의해 사용되는 메모리 영역을 적절히 배분
82
성능관리
페이징(Paging)의 최소화
• 프로세스 전체의 이동이 요구되는 스와핑과는 달리 페이징은 메모리의
재확보를 위해 필요한 프로세스의 해당 Page를 디스크로 이동(LRU알
고리즘 사용)
• 소량의 Page-Out이 시스템 성능에 끼치는 영향은 그리 크지 않으나
Paging양이 증가할 수록 시스템의 성능은 급격히 저하
• 해결방안
– 메모리 추가
– 타 시스템으로의 부하분산
83
성능관리
SGA - Database Buffer Cache 튜닝
Database Buffer Cache
•
SGA Redo Log
Buffer
Shared
Pool
테이블, 인덱스, Rollback Segments, 클러스터등 데이터베이스 블록의 내용을 보관하는
메모리영역
•
Buffer Cache 에 많은 양의 데이터블록을 보관하고 있을 수록 성능향상에 도움이 됨
•
효과 : Online Transaction 처리의 경우 5 - 20 % 의 성능향상을 기대할 수 있음
•
Test 와 Monitoring 을 반복, 가장 적합한 수치를 지정
•
SQL튜닝과 병행하여 실시 *
84
성능관리
SGA - Shared Buffer Pool 튜닝
• Shared SQL Area (Library Cache) 해석(Parse)된 SQL문을 보관
• 충분히 지정하는 것이 바람직
Shared Buffer Pool
Dictionary
Cache
Shared SQL and PL/SQL
A
B
Session
Data
User1
SQL Statement D
D
User3
SQL Statement D
85
C
E
User2
SQL Statement D
성능관리
SGA - Redo Log Buffer
SGA
Database Buffer Cache
Redo Log
Buffer
Shared
Pool
• 거래처리(Transaction)에 의해 일어난 데이터베이스 버퍼블록의 변화
를 보관하는 메모리 영역
• LGWR 라는 ORACLE Process 에 의해 Redo Log File 에 Logging 되
므로 Log Buffer 의 크기가 작으면
Disk 에 자주 Write 하게 되고, 따라서 성능저하의 원인이 될 수 있음
86
성능관리
논리적 저장구조
•
•
•
•
Database Schema(user, role, table, index, cluster, procedure, function, db link …)
Tablespace
Segments
Objects
Table
index
Table
Table
index
tablespace
Tablespace
Database
87
성능관리
물리적 저장구조와의 관계
테이블스페이스
SYSTEM
Tablespace
회계
Tablespace
구매
Tablespace
데이타 파일
system datafile
회계
datafile1
회계
datafile2
구매
datafile1
구매
datafile2
Data file tunnig
88
성능관리
Tablespace, Extent, Block와의 관계
Tablespace
Segment
112K
Extent
84K
Extent
28K
2K
Database Blocks
Database
89
Object tunnig
성능관리
저장구조의 역할
• Segments
– 특정 데이타 유형을 저장하는 Extents 의 집합
– Data Segment
– Rollback Segment
Segment
– Index Segment
•
•
– Temporary Segment
•
Block
• Extents
Block
•
•
Extent
90
Extent
성능관리
블럭의 구조
• 블럭
– 가장 작은 저장단위(보통 2K)
– 데이타 및 자신의 Header 정보 포함
– Bytes/Block 결정요인
• Operating System
• DB_BLOCK_SIZE
• 구성요소
 Row Data
 Free Space(pct free)
 Block Infomation
Common and variable Header
Table Directory
Row Directory
Free Space
Row Data
Data Block
91
성능관리
공간 할당


데이터 객체(테이블, 인덱스)의 디스크 할당
Table 1
Index 1
Index 2
Table 2
. Disk I/O Contention 감소
. 빠른 Access time
테이블별 초기기억공간(Primary Space), 확장기억공간(Extent), 자유기억공간 (Free
Space) 지정
- 보관주기내의 데이터전체가 저장될수 있는 충분한 초기기억공간 할당
- 예상되는 입력/수정에 적합하도록 자유공간 할당
. Disk의 효율적 사용
. 과다 Extents 억제
92
성능관리
Rollback Segment
• Before image를 기록하는데 사용
– 거래처리(transaction)의 양과 유형에 따라 관리
– 대규모 갱신이나 장시간 업무에는 대규모의 Rollback segment를
할당한다
• Rollback Segment의 Monitoring
– Rollback segment는 엄격히 통제하지 않으면 오래지 않아 크게
증가.
• Rollback Segment의 Extent의 수
– 통계적으로 rollback segment 의 평균 extents 는 20 정도가
적당
93
성능관리
Rollback segment의 개수/크기 관리
• Rollback segment 의 갯수
Minimum number of rollback segment =
CEIL (TRANSACTION / TRANSACTIONS_PER_ROLLBACK_SEGMENT)
– 최소 4 이상
• 일반적으로 4개의 거래처리당 1개의 Rollback segment를 할당
– 거래처리 유형에 따라 Rollback segment의 사용을 제어
– Rollback segment의 크기는 생성할때 Storage 절에서 Optimal
Option을 지정
94
성능관리
EXTENTS의 정리
• 테이블 및 인덱스의 Extension은 Disk I/O를 많이 유발하며
Disk I/O의 성능을 저하시킴
• Monitoring
– SYSDBA로 Connect
– DBA_EXTENTS Dictionary View를 활용하여 각각 Object가
몇 개의 Extent로 구성되어 있는지 확인
• Extents의 정리 작업 절차
– 테이블의 경우
– 인덱스의 경우
95
성능관리
Extents Deallocate
• DEALLOCATE의 대상
– TABLE, INDEX, CLUSTER들이 생성된 이후에 한번도 사용되어지지
않은 공간.
이후의 Data Insert위한 공간
Extent 1
Extent 2
Free 되어질 공간
Extent 3
High Water Mark
현재 Data가 차 있는 위치
96
Free Space
성능관리
FREE EXTENTS의 정리
1. 초기설정
Segment 1 Segment 2 Segment 2 Segment 2 Segment 2 Segment 1
Extent 1
Extent 1
Extent 2
Extent 3
Extent 4
Extent 2
Free
Space
2. Segment 2가 Drop되었을 때
Segment 1
Extent 1
Free
Space
Free
Space
Free
Space
Free
Space
Segment 1
Extent 2
Free
Space
Segment 1
Extent 2
Free
Space
3. Free Extents의 Coalesce
Segment 1
Extent 1
Free
Space
97
성능관리
SQL 최적화를 위한 Tracing Tool
•
SQL_TRACE : Trace file의 생성
– Initial Parameter file의 수정
SQL_TRACE
USER_DUMP_DEST
TIMED_STATISTICS
MAX_DUMP_SIZE
•
TRUE
directory
TRUE
number
TKPROF : Trace file의 해석
– tkprof tracefile outputfile [EXPLAN=username/password]
•
EXPLAIN PLAN : Access Path의 Tracing
– "PLAN_TABLE" 테이블 생성
– 구문 :
SQL>EXPLAIN PLAN [SET STATEMENT_ID='identifier']
FOR SELECT statement
98
성능관리
Cost-Based의 Optimizer를 위한 Analyzer
• 테이블 및 인덱스의 각종 정보를 분석
• 작업내용
– 구문
. ANALYZE TABLE(or INDEX) 이름 COMPUTE STATISTICS
ESTIMATE
DELETE
. DBMS 제공 패키지 사용
– 분석내용은
• USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES
Dictionary View 참조
99
성능관리
DBMS 성능관리 측정 기준
• 자원소모(Peformance Statistics)
데이타베이스는 성능의 트랙킹과 측정을 위한 지표를 제공하는데, 여러 가지 리소스(I/O, CPU, 메모
리)와 관련된 상세 성능 정보를 보여준다. Performance Statistics는 ‘리소스 사용 직접 연관지표’ 로
서 이 정보의 수치 증가 여부로 특정 리소스의 과다사용에 대한 측정이 가능한 것이다.
Logical Read, Physical Read, table scan rows gotten, session pga memory, User Calls,
User rollbacks, Commit, Parse Count, Execution Count 등
• 자원경합(Waiting Event)
리소스 사용 간접 연관지표라 할수 있으며 어떠한 리소스든지 한정적인 자원이므로 이에 대한 경합
이 필연적으로 발생한다. Waiting의 대부분은 특정 리소스를 과다하게 사용하는 것은 아니지만,어쨌
든 요구되는 자원에 대한 경합이 발생하고 있다는 사실을 의미하고 무엇인가 데이타베이스 전체적으
로 자원의 과다사용으로 인한 성능 문제가 발생할 가능성을 예고하는 것이다.
DB File Sequential Read, DB File Scattered Read, Latch Free, Library Cache Pin/Lock
Pmon/Smon timer등
100
성능관리
Peformance Statistics & Waiting Event EX’
101
성능관리
시스템에서 주로 발생하는 문제들
102
성능관리
ㅇ 시스템에서 주로 발생하는 문제들
- 하드파싱 문제
- Literal SQL에 의한 메모리 단편화 문제
- 악성 SQL에 의한 자원 과다 소모 문제
· 잘못된 SQL사용, 인덱스 문제등
- Lock or Blocking session 문제
- Session Leak
- SQL에 의한 자원경합(Waiting Event) 문제
- DB 모델링 & 디자인 잘못으로 인한 문제
- DB 미튜닝으로 인한 문제
103
성능관리
SQL 처리과정
SNPn
LCKn
Pnnn
RECO
Snnn
Dnnn
INSTANCE = SGA+BGP
Back Ground PRO’
PMON
SGA
Shared Pool
Redo Log Buffer
SMON
CKPT
DBWn
ARCH
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘123’;
Shared SQL Area
SQL, PL/SQL 문장
의 텍스트
SQL, PL/SQL 문장
의 분석된 형태
(Parsed form)
SQL, PL/SQL
Execute Plan
Data Buffer Cache
LGWR
Dictionary Cache
Data
Dictionary
Character Set
Network
Security
Etc…
User
Server
Data File
LISTENER
Sql 처리순서
ㅇ Parse : Sintax, Semantics(User, Object, Privilege),
-> Optimizer (Excution Plan, Row Source생성),
Binding : 변수처리
ㅇ Execute : Physical Read, Logical Read
ㅇ Fetch
104
성능관리
HARD PARSING TECH’
SNPn
LCKn
Pnnn
RECO
PMON
Data Buffer Cache
Redo Log Buffer
????
SMON
CKPT
DBWn
미공유
미공유
SQL
미공유
SQL
미공유
SQL
미공유
SQL
미공유
SQL
미공유
SQL
미공유
SQL
미공유
SQL
SQL
미공유
SQL
미공유
SQL
미공유
SQL
Dnnn
LGWR
SGA
Shared Pool
Snnn
ARCH
Server P
Data File
????
User
User
Loop….
if then ….
select ….
loop
if then….
update….
.
.
105
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘123’
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘245’;
ㅇ Mem’ Fragmentation
- ora 4031….
ㅇ 각종 contention 발생
- Latch, Hard Parse등
- Lib’ cache miss
ㅇ 자원소모
성능관리
HARD PARSING TECH’
106
성능관리
HARD PARSING TECH’
107
성능관리
Literal SQL TECH’
SNPn
LCKn
Pnnn
RECO
Snnn
Dnnn
INSTANCE = SGA+BGP
Back Ground PRO’
PMON
SGA
Shared Pool
Data Buffer Cache
LGWR
Redo Log Buffer
????
SMON
CKPT
DBWn
Literal
SQL
Literal
SQL
Literal
Literal
SQL
SQL
Literal
SQL
Literal
SQL
Literal
SQL
Literal
Literal
SQL
SQL
ARCH
Server P
????
Data File
User
User
Loop….
if then ….
select ….
loop
if then….
update….
.
.
.
108
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘123’;
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘245’;
SELECT EMPNO,NAME
FROM EMP
WHERE EMPNO = ‘267’;
.
.
ㅇ Mem’ Fragmentation
- ora-4031….
ㅇ 비공유 Literal SQL사용으로
각종 contention이 발생하고
하드파싱을 유발하며, 메모리
단편화를 일으켜 DB가 다운
된다
성능관리
해결방안
ㅇ Literal SQL을 Bind 변수를 사용하여 수정
// Create Command Object.
Cmd1= new msado15.Command();
Cmd1.putActiveConnection( Conn1 );
Cmd1.putCommandText( "SELECT * FROM Authors WHERE Au_ID < ?" );
// Create Parameter Object.
v1.putString( "P1" );
v2.putInt( 5 );
Cmd1.getParameters().Append(
Cmd1.CreateParameter( v1.getString(),
msado15.DataTypeEnum.adInteger,
msado15.ParameterDirectionEnum.adParamInput,
0,
v2));
ㅇ Cursor_Sharing 옵션 사용
Alter Session Set cursor_sharing= ‘FORCE’ or ‘SIMILAR’;
* 디비링크, 복잡한 쿼리에는 사용하지 말것
ㅇ 주기적인 Shared Pool 리프레쉬
alter system flush shared_pool;
109
성능관리
Blocking Session or LOCK TECH’
LOCK & Waiting Session Monitoring
DB Session Monitoring
Unix Process Monitoring
110
성능관리
Blocking Session or LOCK(계속)
USER A : update sal…
EMPNO
SAL
CODE
7782
2450
3
7839
5000
2
7934
100
5
7369
800
1
7566
2975
1
7788
3000
5
7876
1100
7
7902
3000
6
7499
1600
2
7521
1250
2
7654
1250
3
7698
2850
5
7844
1500
6
7900
950
7
USER B : update
code
USER C
USER D
USER A
USER B
USER C
SELECT 10
UPDATE 10->20
USER E
SELECT
.
.
.
.
USER F
USER G
USER H
?
SELECT
?
UPDATE 10->60
SELECT ?
Commit
ㅇ Index Create, Drop
ㅇ Analyze 수행시
111
?
UPDATE 10->30
COMMIT ;
USER Z
SELECT
성능관리
Blocking Session or LOCK (계속)
112
성능관리
Blocking Session or LOCK (계속)
113
성능관리
Blocking Session or LOCK (계속)
114
성능관리
Blocking Session or LOCK (계속)
115
성능관리
Blocking Session or LOCK (계속)
해결방법은?
ㅇ 권한분리 : 관리자와 사용자의 접근권한 및 시기, 수정 가능 칼럼 권한조정
ㅇ COMMIT 횟수 조절 : 하드웨어 성능, 사용자수와 관련하여 레코드 20 – 200개에 1회 수행
ㅇ DB 오브젝트 튜닝 : ini Trans, free list등
SELECT parsing_user_id, executions, sorts, buffer_gets, disk_reads, first_load_time, sql_text
FROM V$SQL
WHERE first_load_time between '2004-10-12/11:00:00' and '2004-10-12/11:10:00'
ORDER BY first_load_time
116
성능관리
Session leak
발생원인은?
ㅇ 어플리케이션 코드 에러
ㅇ WAS 서버 이상동작(환경설정이상, 과부하등)
ㅇ 하드웨어 리소스 부족, Network 문제 등등
DB
Client
WAS
DB
117
해결방법은?
ㅇ 어플리케이션 코드 수정
ㅇ WAS서버 환경설정 확인,
과부하 원인 제거 (SQL튜닝등)
ㅇ 하드웨어 업그레이드, Network 문제해결
성능관리
성능관리 예제
118
성능관리
성능관리 예제 1
ㅇ 시스템 : 재무/회계, 인사, 전자입찰, 원격연수 시스템등 15개시스템
ㅇ 환 경 : ASP(CBD방법론), 오라클 DBMS 8.1.6, IIS, 사용자수 (4000 - 5000),
DB크기(0.5T), SUN5500(CPU 8, MEM 8G)
ㅇ 현 상 : 이유없는 시스템 다운현상 반복,
주요화면 속도 저하,
IIS 다운현상 심각(주 3 - 4회),
특정화면 조회시 시스템 다운현상,
전자입찰시 시스템 속도 저하 및 다운현상으로 민원제기가 많음.
ㅇ 사용자 : 기능은 좋으나 특정화면 속도가 너무 느리고 자주 시스템이 다운되어 믿을수 없다.
특정기능 사용시 시스템이 다운된다.
119
성능관리
120
성능관리
121
성능관리
ㅇ 수정문장
ㅇ 시스템 : WAMIS 시스템
ㅇ 원문장 :
SELECT /*+ INDEX(S103 S103_IX1) */
MIN(GX), MIN(GY), MAX(GX), MAX(GY)
FROM OWIS.S103
WHERE gx < 16,777,216
SELECT A.A , B.B , C.C , D.D
FROM (SELECT /*+ INDEX_ASC(S103 ix1_s103) */
GX A
FROM OWIS.S103
WHERE gx < 16777216
AND ROWNUM = 1) A,
(SELECT /*+ INDEX_ASC(S103 ix2_s103) */
GY B
FROM OWIS.S103
WHERE gx < 16777216
AND ROWNUM = 1) B,
(SELECT /*+ INDEX_DESC(S103 ix1_s103) */
GX C
FROM OWIS.S103
WHERE gx < 16777216
AND ROWNUM = 1) C,
(SELECT /*+ INDEX_DESC(S103 ix2_s103) */
GY D
FROM OWIS.S103
WHERE gx < 16777216
AND ROWNUM = 1) D
ㅇ 속 도 : 개선전 8초 -> 0.2초
QUERY_PLAN
--------------------------------------------SELECT STATEMENT cost = 4
.1 SORT AGGREGATE
.1 INDEX FAST FULL SCAN IX_S1031 NON-UNIQUE
ㅇ 원인 : min, max 함수로 인하여 index full scan이 일어나서 속도 저하
ㅇ 조 치 : 인덱스 2개 생성후 rownum을 이용하여 조치
create index ix2_s103 on owis.s103 (GY)
tablespace owis storage(initial 1m next 1m pctincrease 0) nologging;
create index ix1_s103 on owis.s103 (GX)
tablespace owis storage(initial 1m next 1m pctincrease 0) nologging;
•
•
•
•
INDEX
S
O
R
T
운반단위
운반단위
INDEX
TABLE
122
성능관리
SELECT B.NAME||'총계', '', '', '', '', '', '', '',
'', '', Sum (A.CASHAMT), Sum(A.CDAMT), Sum(A.INPAMT),
00, Count(B.NAME), ' '
FROM ofms.FACDCAUNM B,
( SELECT CSDAUN, CASHAMT, CDAMT, INPAMT
FROM ofms.FAABMDECD
WHERE CSDAUN = '01'
AND INPDT >= TO_DATE('2002/03/07', 'YYYY/MM/DD')
AND INPDT < TO_DATE('2002/03/08', 'YYYY/MM/DD') + 1
AND ACSLPNO >= '20020150278') A
WHERE A.CSDAUN = B.CODE
GROUP BY B.NAME
HAVING COUNT(B.NAME) > 1
UNION
SELECT B.NAME, TO_CHAR(A.INPDT,'YYYY/MM/DD'),
'일자소계', '', '', '', '', '', '' , '' ,
Sum (A.CASHAMT), Sum(A.CDAMT), Sum(A.INPAMT) , 01,
Count(B.NAME), ' '
FROM ofms.FACDCAUNM B,
(SELECT CSDAUN, INPDT, CASHAMT, CDAMT, INPAMT
FROM ofms.FAABMDECD
WHERE CSDAUN = '01'
AND INPDT >= TO_DATE('2002/03/07', 'YYYY/MM/DD')
AND INPDT < TO_DATE('2002/03/08', 'YYYY/MM/DD') + 1
AND ACSLPNO >= '20020150278') A
WHERE A.CSDAUN = B.CODE
GROUP BY B.NAME,TO_CHAR(A.INPDT,'YYYY/MM/DD')
HAVING COUNT(B.NAME) > 1
UNION
SELECT B.NAME, TO_CHAR(A.INPDT,'YYYY/MM/DD'), A.ACSLPNO,
REPLACE(C.TITCNT, Chr(13)||Chr(10), ' ') ,
A.CUSNAME, A.INBNAME, A.DPSNO, A.SAVNAME,
A.CDDPSSTS, A.CDCUSNO, A.CASHAMT , A.CDAMT,
A.INPAMT , 02, 1, C.ACCIND
FROM ofms.FACDCAUNM B, ofms.FAABMDECI C,
(SELECT INPDT, ACSLPNO, CUSNAME, INBNAME,
DPSNO, SAVNAME, CDDPSSTS, CDCUSNO,
CASHAMT, CDAMT, INPAMT, CSDAUN
FROM ofms.FAABMDECD
…………………………………
ㅇ 시스템 : 재무시스템 거래처별지급내역
ㅇ 속 도 : 3분48초 -> 0.78초
ㅇ 실행계획
QUERY_PLAN
------------------------------------------------------SELECT STATEMENT cost = 332
.1 SORT UNIQUE
.1 UNION-ALL
.1 FILTER
.1 SORT GROUP BY
.1 MERGE JOIN CARTESIAN
.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM
.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE
.2 SORT JOIN
.1 TABLE ACCESS BY INDEX ROWID FAABMDECD
.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE
.2 FILTER
.1 SORT GROUP BY
.1 MERGE JOIN CARTESIAN
.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM
.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE
.2 SORT JOIN
.1 TABLE ACCESS BY INDEX ROWID FAABMDECD
.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE
.3 HASH JOIN
.1 NESTED LOOPS
.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM
.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FAABMDECI
.1 INDEX RANGE SCAN FAABMDECI_PK_SLPNOSSENO UNIQUE
.1 SORT AGGREGATE
.1 FIRST ROW
.1 INDEX RANGE SCAN (MIN/MAX) FAABMDECI_PK_SLPNOSSENO UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FAABMDECD
.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE
ㅇ 원 인 : 8.1.6 버젼 optimizer는 first_rows optimizer에 의해 실행계획시
잘못된 실행계획을 세우는 확률이 매우 높아 rule optimizer로
변경 --first_rows 버그로 수정되었음
123
성능관리
ㅇ 시스템 : 재무시스템 원천 영수증 발행화면
ㅇ 속 도 : 28.85초 -> 1.32초
ㅇ 원문장
SELECT A.SLPNO, B.WRTDT, A.IBENO,
DECODE(A.IVNN, NULL, A.ICONAME, A.IVNN),
B.TITCNT, B.PPSCNT, A.INCIND, TO_CHAR(NVL(A.PRNCD,0))
FROM ofms.FTSOMSRTM A, ofms.FAABMSLPM B
WHERE A.ACSDT BETWEEN TO_DATE('2002/01/01','YYYY/MM/DD')
AND TO_DATE('2002/04/04','YYYY/MM/DD')
AND B.SLPNO = A.SLPNO
AND A.INCIND ='사업소득'
ㅇ SQL TRACE
AND A.AUNCD = '01'
call
count
cpu elapsed
disk
query current
rows
AND A.CBENO = '306-82-00471'
------- ------ -------- ---------- ---------- ---------ORDER BY A.SLPNO, B.WRTDT
Parse
1
0.00
0.00
0
0
0
0
------- ------ -------- ---------- ---------- ---------Parse
1
0.00
0.00
0
0
0
0
ㅇ 실행계획
Execute
1
0.00
0.00
0
0
0
0
Misses in library cache during parse: 1
Fetch
5
26.56
28.85
13938
911698
4
54
Optimizer goal: RULE
------- ------ -------- ---------- ---------- ---------Parsing user id: 5 (SYSTEM)
total
7
26.56
28.85
13938
911698
4
54
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: RULE
54
SORT (ORDER BY)
54
NESTED LOOPS
444919
TABLE ACCESS GOAL: ANALYZED (FULL) OF 'FAABMSLPM'
54
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'FTSOMSRTM‘
446612
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FTSOMSRTM_PK_SLPNOIBENO' (UNIQUE)
124
성능관리
ㅇ 원 인 : B와 A 테이블의 관계가 1 : M 인데 부모테이블인 B의 건수가
44만건이고 자식 테이블인 A의 건수는 44만건인 비정상적
상황으로 RULE 옵티마이저가 부모 테이블인 B를 먼저
드라이빙하여 문제가 발생
ㅇ 조 치 : WHERE 조건에서 날짜조건인 A.ACSDT를 B.ACSDT로 변경하여
실핼계획 변경 유도
B
444,919 (?)
ㅇ 수정후 계획
call
count
cpu elapsed
disk
query current
rows
------- ------ -------- ---------- ---------- --------Parse
1
0.00
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
5
1.34
1.32
0
45662
0
54
------- ------ -------- ---------- ---------- --------total
7
1.34
1.32
0
45662
0
54
QUERY_PLAN
--------------------------------SELECT STATEMENT cost =
.1 SORT ORDER BY
.1 NESTED LOOPS
.1
TABLE ACCESS BY INDEX ROWID FAABMSLPM
.1
INDEX RANGE SCAN FAABMSLPM_IDX_ACSDT NON-UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FTSOMSRTM
.1
INDEX RANGE SCAN FTSOMSRTM_PK_SLPNOIBENO UNIQUE
A
(446,612)
ㅇ 수정문장
SELECT A.SLPNO, B.WRTDT, A.IBENO,
DECODE(A.IVNN, NULL, A.ICONAME, A.IVNN),
B.TITCNT, B.PPSCNT, A.INCIND, TO_CHAR(NVL(A.PRNCD,0))
FROM ofms.FTSOMSRTM A, ofms.FAABMSLPM B
WHERE b.ACSDT BETWEEN TO_DATE('2002/01/01','YYYY/MM/DD')
AND TO_DATE('2002/04/04','YYYY/MM/DD')
AND B.SLPNO = A.SLPNO
AND A.INCIND ='사업소득'
AND A.AUNCD = '01'
AND A.CBENO = '306-82-00471'
ORDER BY A.SLPNO, B.WRTDT
125
성능관리
ㅇ 시스템 : 재무시스템 추산부2화면
ㅇ 속 도 : 개선전 50초 -> 1.5초
ㅇ 원 인 : FBPTMEXEM 테이블의 결합인덱스가 9개 컬럼으로 생성되었는데 순서가 잘못되어
속도가 저하됨
* 기존 인덱스 칼럼순서 : YEA,CMM,BGTIND,BGDPT,ACCCD, BUACD,SACCCD,CNSCD,SEQ
ㅇ 원문장
ㅇ조치
SELECT A.BGTIND, A.BGDPT, A.ACCCD, A.BUACD, A.SACCCD, A.CNSCD,
- 인덱스생성
A.SEQ, NVL(DECODE(A.ITMNAME,'****','*****',A.ITMNAME),'*****'), A.SYSCD,
create index ix_FBPTMEXEM2 on ofms.FBPTMEXEM(BUACD,SACCCD,BGDPT,ACCCD,SEQ,YEA,CNSCD,BGTIND)
REPLACE(A.TITCNT,CHR(13)||CHR(10),CHR(32)) TITCNT,
tablespace OFMSFIDX storage(initial 2m next 2m pctincrease 0) nologging;
TO_CHAR(A.AMT0) AMT0, TO_CHAR(A.AMT1) AMT1, TO_CHAR(A.AMT2) AMT2,
create index ix_FAABMSLPD3 on ofms.FAABMSLPD(slpno,bgdpt)
TO_CHAR(A.AMT3) AMT3, TO_CHAR(A.AMT4) AMT4, '0' AMT1TOT, '0' AMT2TOT,
tablespace OFMSFIDX storage(initial 2m next 2m pctincrease 0) nologging;
'0' AMT3TOT, '0' AMT4TOT, A.SLPNO, TO_CHAR(A.ACSDT,'YYYY/MM/DD'), A.MANITEM5
FROM (SELECT DISTINCT C.REQNO SLPNO, D.BGTIND, G.CONDPT WTDPT,
ㅇ 살행계획
G.CONDPTNAME WTDPTNAME, D.BGDPT,
D.ACCCD, D.BUACD,
D.SACCCD, D.CNSCD,
Misses in library cache during parse: 1
D.SEQ,
D.ITMNAME, G.CPIND SYSCD,
Optimizer goal: RULE
G.ORDNO MANITEM5, G.ORDKNAME TITCNT,
Parsing user id: 5 (SYSTEM)
C.PCONAMT AMT1, 0 AMT2, 0 AMT3, 0 AMT4,
A.CONDT ACSDT,
'' TEMPACC, SUM(NVL(D.MONAMT,0)) AMT0
Rows
Row Source Operation
FROM ofms.FOCOMDDSM A, ofms.FPODMDDSM G, ofms.FPRQMDDSM B,
------- --------------------------------------------------ofms.FPRQMBGTD C, ofms.FBPTMEXEM D
89 SORT ORDER BY
WHERE D.SEQ = C.BDTCD
89 VIEW
89 UNION-ALL
AND D.CNSCD = C.CNSCD
0
SORT UNIQUE
AND D.SACCCD = C.SACCD
0
SORT GROUP BY
AND D.BUACD = C.BUACD
0
NESTED LOOPS
AND D.ACCCD = C.ACCCD
1
NESTED LOOPS
AND D.BGDPT = C.DPTCD
7
NESTED LOOPS
AND D.BGTIND = C.BGTIND
66
NESTED LOOPS
AND D.YEA = C.YEA
66
TABLE ACCESS FULL FPRQMBGTD
AND C.PCONAMT > 0
130
TABLE ACCESS BY INDEX ROWID FPRQMDDSM
AND C.DPTCD = '190'
130
INDEX UNIQUE SCAN (object id 14006)
AND C.REQNO = B.REQNO
71
TABLE ACCESS BY INDEX ROWID FPODMDDSM
AND B.ORDNO = G.ORDNO
96
INDEX UNIQUE SCAN (object id 14002)
AND G.CPIND IN ('공사','용역')
6
TABLE ACCESS BY INDEX ROWID FOCOMDDSM
AND G.ORDNO = A.CONNO
10
INDEX UNIQUE SCAN (object id 11486)
AND A.CONDT BETWEEN TO_DATE('2002/02/20','YYYY/MM/DD')
0
TABLE ACCESS BY INDEX ROWID FBPTMEXEM
AND TO_DATE('2002/03/20','YYYY/MM/DD')
0
INDEX RANGE SCAN (object id 30476)
GROUP BY A.CONNO, D.BGTIND, G.CONDPT, G.CONDPTNAME, D.BGDPT,
4
SORT UNIQUE
D.ACCCD, D.BUACD, D.SACCCD, D.CNSCD, D.SEQ,
D.ITMNAME,
4
SORT GROUP BY
G.CPIND, G.ORDNO, G.ORDKNAME, C.PCONAMT, A.CONDT, C.REQNO
48
NESTED LOOPS
UNION ALL
5
NESTED LOOPS
SELECT DISTINCT C.REQNO SLPNO, D.BGTIND, G.CONDPT WTDPT,
56
NESTED LOOPS
G.CONDPTNAME WTDPTNAME, D.BGDPT,
59
NESTED LOOPS
D.ACCCD, D.BUACD,
D.SACCCD, D.CNSCD,
66
TABLE ACCESS FULL FPRQMBGTD
D.SEQ, D.ITMNAME, G.CPIND SYSCD,
123
TABLE ACCESS BY INDEX ROWID FPRQMITMM
G.ORDNO MANITEM5, G.ORDKNAME TITCNT,
123
INDEX UNIQUE SCAN (object id 9484)
C.PCONAMT AMT1, 0 AMT2, 0 AMT3,
0 AMT4,
113
TABLE ACCESS BY INDEX ROWID FPODMDDSM
A.CONDT ACSDT,
'' TEMPACC, SUM(NVL(D.MONAMT,0)) AMT0
113
INDEX UNIQUE SCAN (object id 14002)
FROM ofms.FOCOMDDSM A, ofms.FPODMDDSM G, ofms.FPRQMITMM E,
59
TABLE ACCESS BY INDEX ROWID FOCOMDDSM
ofms.FPRQMBGTD C, ofms.FBPTMEXEM D
110
INDEX UNIQUE SCAN (object id 11486)
WHERE D.SEQ = C.BDTCD
48
TABLE ACCESS BY INDEX ROWID FBPTMEXEM
AND D.CNSCD = C.CNSCD
-----------------------------AND D.SACCCD = C.SACCD
----------------------……….
AND D.BUACD = C.BUACD
………………………………….
126
성능관리
ㅇ 시스템 : 재무시스템 계정/보조검색화면
ㅇ 속 도 : 개선전 1.27초 -> 0.07초
ㅇ 원문장
Select P.ACCCD, P.NAME, P.SACCCD, P.SACCNAME ,
P.MACSTS, P.BUASTS
FROM (Select A.Code ACCCD, A.NAME NAME,
C.SACCCD SACCCD, C.SACCNAME SACCNAME,
C.MACSTS MACSTS, C.BATSTS BATSTS,
A.BUASTS BUASTS
FROM ofms.FACDCACCM A,
(Select A.ACCCD ACCCD, A.SACCCD SACCCD,
A.SACCNAME, A.MACSTS MACSTS,
A.BATSTS BATSTS
From ofms.FaCDCACSA A,
(select ACCCD, Count(SACCCD)
From ofms.FaCDCACSA
Where salevind = '중분류'
Group By ACCCD, ACCNAME
Having Count(SACCCD) = 1) B
Where A.ACCCD = B.ACCCD
And A.salevind = '중분류' ) C
Where A.CODE = C.ACCCD(+)
AND A.ACCSTS = 'Y'
AND A.BGTSTS = 'Y'
AND A.MACSTS ='N') P
Where P.BATSTS IS null
OR P.BATSTS = 'N'
ORDER BY P.ACCCD, P.NAME
ㅇ 실행계획
Rows
Execution Plan
------- --------------------------------------------------0 SELECT STATEMENT GOAL: RULE
71 SORT (ORDER BY)
71 FILTER
78
MERGE JOIN (OUTER)
79
SORT (JOIN)
78
TABLE ACCESS (FULL) OF 'FACDCACCM'
54
SORT (JOIN)
104
VIEW
104
NESTED LOOPS
105
VIEW
105
FILTER
140
SORT (GROUP BY)
1287
TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'
1288
INDEX (RANGE SCAN) OF
'FACDCACSA_IDX_SALEVIND' (NON-UNIQUE)
104
TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'
133952
INDEX (RANGE SCAN) OF 'FACDCACSA_IDX_SALEVIND'
(NON-UNIQUE)
127
성능관리
ㅇ 원 인 : FACDCACSA테이블의 분포도가 좋지않는 인덱스가 전체적인
실행계획을 방해하며 2번씩 읽히고 있음. FACDCACSA가
드라이빙될수 있도록 조치
ㅇ 조 치 : 다른 인덱스를 읽도록 rtrim을 이용하여 실행계획 조정
ㅇ 수정문
Select P.ACCCD, P.NAME, P.SACCCD, P.SACCNAME ,
P.MACSTS, P.BUASTS
FROM (Select A.Code ACCCD, A.NAME NAME,
C.SACCCD SACCCD, C.SACCNAME SACCNAME,
C.MACSTS MACSTS, C.BATSTS BATSTS,
A.BUASTS BUASTS
FROM ofms.FACDCACCM A,
(Select A.ACCCD ACCCD, A.SACCCD SACCCD,
A.SACCNAME, A.MACSTS MACSTS,
A.BATSTS BATSTS
From ofms.FaCDCACSA A,
(select ACCCD, Count(SACCCD)
From ofms.FaCDCACSA
Where RTRIM(salevind) = '중분류'
Group By ACCCD, ACCNAME
Having Count(SACCCD) = 1) B
Where A.ACCCD = B.ACCCD
And rtrim(A.salevind) = '중분류' ) C
Where A.CODE = C.ACCCD(+)
AND A.ACCSTS = 'Y'
AND A.BGTSTS = 'Y'
AND A.MACSTS ='N') P
Where P.BATSTS IS null
OR P.BATSTS = 'N'
ORDER BY P.ACCCD, P.NAME;
ㅇ 수정후 실행계획
Execution Plan
--------------------------------------------------SELECT STATEMENT GOAL: RULE
SORT (ORDER BY)
FILTER
MERGE JOIN (OUTER)
SORT (JOIN)
TABLE ACCESS (FULL) OF 'FACDCACCM'
SORT (JOIN)
VIEW
NESTED LOOPS
VIEW
FILTER
SORT (GROUP BY)
TABLE ACCESS (FULL) OF 'FACDCACSA'
TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'
INDEX (RANGE SCAN) OF 'FACDCACSA_CK_ACCCDSACCCD' (UNIQUE)
128
성능관리
ㅇ 시스템 : 재무시스템
ㅇ 속 도 : 기존 30초 -> 0.5초
ㅇ 원문장
SELECT A.ACSLPNO, A.SSENO, REPLACE(D.TITCNT, CHR(13) || CHR(10), ' '),
A.CUSNAME, A.DPSNO, A.CASHAMT+A.CDAMT INAMT, A.PAYIND,
TO_CHAR(D.APPDT,'yyyy/mm/dd'), A.CUSCD
FROM ofms.FAABMDECD A,
ofms.FFBNMMSTM B,
ofms.FACDCAUNM C,
ofms.FAABMDECI D,
ofms.FACDCAUNM E
WHERE A.OUTDPSNO = B.DPSNO
AND B.SRL = (SELECT MAX(SRL)
FROM ofms.FFBNMMSTM
WHERE DPSNO = A.OUTDPSNO )
AND B.AUNCD = C.CODE
AND A.ACSLPNO = D.SLPNO
AND D.SSENO = 1
AND D.WTAUN = E.CODE
AND B.AUNCD = '01'
AND A.PAYIND IN ('계좌이체', '구매카드', '~보수발행', '지준이체',
'현금(출장)', '계좌이체(출장)', '인터넷지로',
'자동이체', '대량이체' )
AND D.APPDT BETWEEN ADD_MONTHS(TO_DATE('2002/07/23','YYYY/MM/DD'),-1)
AND TO_DATE('2002/07/23','YYYY/MM/DD') +1
AND D.WTEMPNO = '19950872'
129
성능관리
ㅇ 실행계획
QUERY_PLAN
----------------------------SELECT STATEMENT cost =
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 TABLE ACCESS FULL FAABMDECI
.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FAABMDECD
.1 INDEX RANGE SCAN FAABMDECD_IDX_ACSLPNO
NON-UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FFBNMMSTM
.1 INDEX UNIQUE SCAN FFBNMMSTM_CK_DPSNO UNIQUE
.1 SORT AGGREGATE
.1 INDEX RANGE SCAN FFBNMMSTM_CK_DPSNO UNIQUE
.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE
ㅇ 원 인 : index가 1개 생략되어 있음
ㅇ 조 치 : index 생성
create index ix_FAABMDECI9 on fms.FAABMDECI(APPDT,
WTEMPNO)
tablespace indx storage(initial 2m next 2m pctincrease 0)
nologging;
ㅇ 실행계획
QUERY_PLAN
----------------------------SELECT STATEMENT cost =
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 TABLE ACCESS BY INDEX ROWID FAABMDECI
.1 INDEX RANGE SCAN IX_FAABMDECI9 NON-UNIQUE
.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FAABMDECD
.1 INDEX RANGE SCAN FAABMDECD_IDX_ACSLPNO
NON-UNIQUE
.2 TABLE ACCESS BY INDEX ROWID FFBNMMSTM
.1 INDEX UNIQUE SCAN FFBNMMSTM_CK_DPSNO UNIQUE
.1 SORT AGGREGATE
.1 INDEX RANGE SCAN FFBNMMSTM_CK_DPSNO UNIQUE
.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE
130
성능관리
튜닝완료후
131
성능관리
성능관리 예제 2
ㅇ 시스템 : 그룹웨어, KM시스템
ㅇ 환 경 : 오라클 DBMS 8.1.7, 사용자수 (4000), DB크기(100G), SUN5500(CPU 8, MEM 8G)
ㅇ 현 상 : 원인불명의 시스템 다운현상 반복,
주요화면 속도 저하,
아침시간대 30분간 시스템 HANGUP현상 발생
ㅇ 사용자 : 아침시간대 30분간 시스템을 사용 할 수 없고, 장애가 빈번하고 속도가
느려 도저히 못 쓰겠음.
132
성능관리
튜닝전
133
성능관리
134
성능관리
ㅇ 시스템 : 오아시스(지식관리 배치처리)
ㅇ 속 도 : 14시간
ㅇ문 장
select docnumber
from docsadm.profile
where fulltext = 'Y'
and board is null
and work_flag is null
and rownum < 501;
ㅇ 실행계획
QUERY_PLAN
--------------------------------SELECT STATEMENT cost = 11701
.1 COUNT STOPKEY
.1 TABLE ACCESS FULL PROFILE
ㅇ 원 인 : 200만건의 profile을 full scan하고 있음. fulltext 칼럼의 분포도가
50%로서 매우 좋지 않아 단일인덱스를 생성할수 없는 상황이며 테이블에
빈번한 수정 삭제가 일어나서 bitmap index를 생성할수 없으므로
b tree 결합인덱스로 테이블 접근이 이루어지지 않도록 조치
ㅇ조 치
create index ix_profile44 on docsadm.profile(fulltext,docnumber,board,work_flag)
tablespace docsind storage(initial 2m next 2m pctincrease 0) nologging;
ㅇ 수정후 실행계획
QUERY_PLAN
-------------------------SELECT STATEMENT cost = 4
.1 COUNT STOPKEY
.1 INDEX FAST FULL SCAN IX_PROFILE44 NON-UNIQUE
ㅇ 완료후 속도 : ??
135
성능관리
ㅇ 소요시간 : 14초 -> 0.01초
ㅇ 문장
SELECT A.indate, A.ggubungig,A.ggubun,A.sug_no, A.acceptid,
A.invres, A.invman, A.invDate, A.sname,
C.dept, C.empno, C.paffsort, C.grade, C.korname,
A.sgubun, D.codename, A.cause, A.improve, A.effect,
A.opflag, A.opdept, C.head
FROM oimi.imimidea A, oimi.imilidea B, ocom.ahambase C,
ocode.imicidea D
WHERE A.sug_no = B.sug_no
AND B.empno = C.empno(+)
AND A.ggubun = D.code
AND A.sug_no = '07679'
ㅇ 원인 : 14590건의 imimidea와 3216건의 ahambase 테이블을 중심으로
merge join이 일어나고 있다.
ㅇ 수정 : imimidea의 sug_no칼럼이 가장 좋은 인덱스 컬럼이므로 테이블 조인
순서를 바꾸어서 튜닝. 조인량을 줄이기 위해 인라인뷰를 사용.
ㅇ 수정문장
SELECT /*+ordered use_nl(a)*/
A.indate, A.ggubungig,A.ggubun,
A.sug_no, A.accepti , A.invres,
A.invman, A.invDate, A.sname,
C.dept, C.empno, C.paffsort,
C.grade, C.korname, A.sgubun,
D.codename, A.cause, A.improve,
A.effect, A.opflag, A.opdept, C.head
FROM (select *
from oimi.imimidea x
where x.sug_no = '07679') A,
oimi.imilidea B,
ocom.ahambase C,
ocode.imicidea D
WHERE A.sug_no = B.sug_no
AND B.empno = C.empno
AND A.ggubun = D.code ;
ㅇ plan_table :
-----------------------------------SELECT STATEMENT cost =
.1 MERGE JOIN OUTER
.1 SORT JOIN
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 TABLE ACCESS BY INDEX ROWID IMIMIDEA
.1 INDEX UNIQUE SCAN SYS_C004093 UNIQUE
.2 TABLE ACCESS BY INDEX ROWID IMICIDEA
.1 INDEX UNIQUE SCAN SYS_C00960 UNIQUE
.2 INDEX RANGE SCAN IX_IMILIDEA NON-UNIQUE
.2 SORT JOIN
.1 VIEW AHAMBASE
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 TABLE ACCESS FULL AHAMBASE
.2 TABLE ACCESS BY INDEX ROWID AHACDEPT
.1 INDEX RANGE SCAN AHACDEPT_PK_YEARDCODEID UNIQUE
.1 TABLE ACCESS FULL DUAL
.2 TABLE ACCESS BY INDEX ROWID AHACDEPT
.1 INDEX RANGE SCAN AHACDEPT_PK_YEARDCODEID UNIQUE
.1 TABLE ACCESS FULL DUAL
-------------------------------------136
성능관리
ㅇ 시스템 : 오아시스
ㅇ 속 도 : 기존 110msec(0.11초)
ㅇ 기존문장 :
SELECT COUNT(hsMsgID)
FROM intraware.DOCBOX
WHERE hsSaveDeptID ='00000655370000001050'
AND UWBOXTYPE=5010
AND UWMSGTYPE=5011
AND UWRECEIVERCOUNT>0
AND hsControllerID = '00000000000000000000'
AND hsDrafterID ='00000000010000003714‘ ;
ㅇ 실행계획
QUERY_PLAN
---------------------------------------------SELECT STATEMENT cost = 55
.1 SORT AGGREGATE
.1 TABLE ACCESS BY INDEX ROWID DOCBOX
.1 INDEX RANGE SCAN XIE2COUNTDOCBOX NON-UNIQUE
ㅇ 인덱스분석
- XIE2COUNTDOCBOX 칼럼순서 : HSSAVEDEPTID,UWBOXTYPE,UWMSGTYPE
- 분포도가 좋지않은 INDEX가 선택되고 있다.
create index ix_docbox20 on intraware.docbox(hsDrafterID,HSSAVEDEPTID)
tablespace indx storage(initial 4m next 4m pctincrease 0) nologging;
ㅇ 수정후
PLAN_TABLE
---------------------------------------------SELECT STATEMENT cost = 2
.1 SORT AGGREGATE
.1 TABLE ACCESS BY INDEX ROWID DOCBOX
.1 INDEX RANGE SCAN IX_DOCBOX20 NON-UNIQUE
ㅇ 수정후 속도 : 0.001초(1msec)
137
성능관리
튜닝완료후
138
성능관리
예제 3
139
성능관리
ㅇ
문제문장
Select a.title
From intraware.PARTICIPANT b, intraware.APPROVAL a
WHERE a.APPRID = b.APPRID
and b.APPROVALSTATUS in (4, 512)
and (b.SIGNERID = '00000000010'||'001012065')
ㅇ 수행속도 : 기존 1761ms -> 튜닝후 10ms
ㅇ 실행계획분석
ㅇ 생성후 실행계획
QUERY_PLAN
QUERY_PLAN
---------------------------------------
------------------------------------------
SELECT STATEMENT
SELECT STATEMENT
cost =
cost =
.1 NESTED LOOPS
.1 NESTED LOOPS
.1 TABLE ACCESS FULL PARTICIPANT
.1 INLIST ITERATOR
.2 TABLE ACCESS BY INDEX ROWID APPROVAL
.1 TABLE ACCESS BY INDEX ROWID PARTICIPANT
.1 INDEX UNIQUE SCAN XPKAPPROVAL UNIQUE
.1 INDEX RANGE SCAN IX_PARTICIPANT2 NON-UNIQUE
.2 TABLE ACCESS BY INDEX ROWID APPROVAL
.1 INDEX UNIQUE SCAN XPKAPPROVAL UNIQUE
140
성능관리
ㅇ 시스템 : GIS계열, 원격측정시스템등
141
성능관리
ㅇ 기타시스템
142
성능관리
ㅇ 기타시스템
143
성능관리
ㅇ 기타시스템
144
성능관리
감사합니다
145