[데이터베이스 설계 이용] Tkprof 분석 예제

1. 다음 통계 결과 분석으로 적절하지 못한것은

call        count      cpu       elapsed  disk      query       current     rows
----       -----      ----       -------  ------   -----        --------  -----
parse      92946     809.39    1017.69   52         5851          1832        0
Execute  136965    227.29    274.50    1631       79767        50174      5763
Fetch      200694    3291.63  4178.38   725075    20716520   139014     240450
----       -----      ----       -------  ------   -----        --------  -----
total       430605     4328.31  5470.57   726758    20802138   191020     246213

1. Parsing Overhead 징후가 나타난것으로 보아 SQL문을 작성할때 조건절에 바인딩 변수를 사용하지 않았을 가능성이 크다.
2. SQL 문을 저장한 문자열에 사용자로 부터 입력받은 상수값을 결합시키겨 동적으로 실행시키는 경우에 많이 나타나는 현상이다.
3. Execute Elapse/Parsing Elapse의 비율이 0.26정도 이므로 Parsing Overhead가 발생하고 잇다는 것을 알수 있다.
4. 동적으로 작성된 SQL은 사용자 수가 적을때 문제가 되지 않지만, 동시 접속자수가 많아지면 심각한 성능저하를 일으킨다.


해설)
상기 Tkprof로 알수 있는 사항
1. parse 카운트 : 92946으로 파싱 작업이 매우 빈번하게 일어나고 있음
2. parse cpu     : 809.39로 cpu를 매우 많이 사용하고 있음
3. 파싱 오버헤드 = parsing Elapse / Execute Elapse 가 0.1 이상인가? (이상이면 파싱오버헤드 높음)
- 1017.69 / 274.50 = 약 3.70 임 (파싱 오버헤드가 매우 심각)
4. 3번에 의해서 파싱 오버헤드가 매우 심각할경우 (동시접속자수가 많으면, 심각한 cpu 로드로 인한 성능저하 매우 심각)
5. 파싱 오버헤드가 큰 경우 본통 Dynamic SQL을 사용하였음 (즉, 바인딩 변수를 사용하지 않고, 상수값을 개별적으로 결합시켜 사용한 경우임)
6. Fetch Count와 Fetch Row의 수를 비교해볼때 Array를 거의 사용하지 못함


2. 다음 통계 결과 분석으로 적절하지 못한것은

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call        count        cpu         elapsed  disk          query         current     rows
----       -----        ----         -------  ------       -----          --------  -----
parse      506406      128.01      251.81    1299          15381          5963        0
Execute  14353178   16232.38   52304.01  74727828    118940085    52404153  96427108
Fetch      1201383     10530.23   63209.44  13652020   210333475    5724986    39923155
----       -----        ----        -------    ------        -----        --------  -----
total       16060962     26890.62  115765.26  88381147   329288941   58135102   136350263

Misses in library cache during parse : 7551
Misses in library cache during execute : 1404

(74727828 + 13652020) / 14353178   = 6.15
(118940085  + 52404153 + 210333475 + 5724986 ) / 14353178  = 26.9 
(329288941  + 58135102)

1. Parse Count(506406) * 0.01이 CPU Count(128.01)보다 작게 나타나고 있으나 Parse Disk 수치가 비교적 높고 Misses in Library Cache 수치도 높은것으로 보아 Dynamic SQL이 다수 사용되고 있거나, 라이브러리 캐시가 작게 세팅된것으로 판단

2. Parse Count(506406)와 Execute Count(14353178)의 비율로 보아 (0.035) 많은 SQL이 Hold Cursor로 선언되었거나 Loop Query로 수행되고 있는것으로 보이지만 Array Processing은 전혀 이루어 지지 않은 것으로 판단

3. 1회 실행시에 평균 Disk I/O는 6 Block, 평균 Logical I/O는 26.9 Block이며, 1Row당 평균 Disk I/O는 1.5 Block, 평균 Logical I/O는 4.3 Block이 발생하고 있는것으로 보아 대채적으로 옵티마이징 전략(인덱스, 클러스터)에 문제가 많은 것으로 판단

4. CPU Fetch Time(10530)과 Elapse Fetch Time(63209)이 5배 정도의 차이를 보이며, 1회 SQL 실행시 평균 CPU 타임이 0.002초, 평균 Elapse Time은 0.008초가 소요된 것에서 보듯이 개선 여지가 많이 나타나고 있는 것으로 인덱스 전략 부재에 따른 I/O 비효율이 성능 저하의 주된 요인으로 판단된다.

해설)
상기 Tkprof로 알수 있는 사항
1. Misses in library cache during parse : 7551 은 하드 파싱 수행이 매우 빈번하게 일어낫음을 나타냄 이 값이 0인 경우 소프트파싱으로 완전하게 수행된것임, (보통 이럴경우 라이브러리 캐시 사이즈를 늘려 주거나, 쿼리 수정을 해야한다.)
2. Parse Disk는 파싱을 수행하면서, 물리적인 read를 수행했느냐에 대한 값이다. 즉, 파싱을 수행하기 위해서 물리적인 디스크에서 파싱 관련 정보를 획득했다는 것이다.
3. 하드파싱은 보통 Dynamic SQL을 이용하여 쿼리를 수행할때 주로 나타나게 된다.
4. 하드파싱을 수행하면, 온라인 접속자수가 늘어남에 따라 수행속도는 급격하게 감소된다.
5. Fetch Count와 Fetch Row의 비율로 볼때, Array Porcessing을 수행하고 잇음을 나타냄 (두 값이 동일하다면 array processing이 이루어 지지 않음을 나타낸다.)
6. 1회 수행시 평균 Disk 블록    = (74727828 + 13652020) / 14353178   = 6.15
7. 1회 수행시 평균 Logical블록 = (118940085  + 52404153 + 210333475 + 5724986 ) / 14353178  = 26.9
8. 1 Row당 평균 Disk I/O = (전체 Row) / (전체 Disk 회수) = ?
9. 1 Row당 평균 Logical I/O = (전체 query + 전체 current) = ?
10. Elapse Time = CPU Fetch Time + Wait Time 이므로 대기 시간이 길다.
11. 1회 실행시 평균 CPU Time = 총 CPU Time(26890) / Execute Count(16060962) = 0.0016 (약 0.002초)
12. 1회 실행시 평균 Elapse Time = Elapse Time(115765) / Execute Count(16060962) = 0.007 (약 0.008)
13. 11, 12에 의해서 실행과 패치 사이의 차이가 4배 차이이므로, 실행은 정상 수행되었으나, 데이터를 가져오는데 시간이 많이 소모되고 잇음을 나타낸다. 이는 인덱스 전략이나, I/O 성능 개선이 필요한 건이다.

3. 다음 통계 결과 분석으로 적절하지 못한것은

call        count        cpu         elapsed  disk          query         current     rows
----       -----        ----         -------  ------       -----          --------  -----
parse      1            0.04           0.08       0              3                0             0
Execute  100         0.09           0.09        0              0                0             0
Fetch      100         6.54           6.71       12             12510          567          100
----       -----        ----        -------    ------        -----        --------  -----
total        201         6.67           6.88        12            12513          567          100

1. Fetch Count와 Row수가 동일한 것으로 보아 다중처리로 SQL을 수행하지 않았다.
2. Execute Count가 Parse Count의 배수인것으로 보아 Loop 내에서 반복 수행 되었다.
3. SQL이 한번 수행될 때마다 0.07초 정도 소요되었다.
4. 애플리케이션이 여러번 수행되었지만 실제로 SQL을 파싱하지 않고 Shared SQL Area에서 찾아왔다.

해설)
상기 분석으로 알수 있는것
1. parse count : 1 한번 파싱을 수행했음,
2. parse query : 3 으로 파싱하기 위해서 버퍼에 존재하는 데이터를 논리적 읽기 수행
3. Execute Count와 Parse Count는 배수로, Loop 내에서 반복 수행됨
4. SQL 1회 수행 시간 = 총 Elapse Time(6.71 + 0.09) / 수행횟수 (100) = 약 0.07 (Fetch와 Execute는 합은 200이지만 실제 수행은 100이됨)
5. Fetch Count와 Row 수가 동일하므로 Array Processing은 수행되지 않음