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

1. 쿼리

select count(subobject_name)

from

 big_table.big_table

 

2. Tkprof 분석 내용

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.01          0          3          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2     99.36     262.10    1840758    1840797          0           1

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

total        4     99.36     262.11    1840758    1840800          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 344

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=1840797 pr=1840758 pw=0 time=262104893 us)

128000000   TABLE ACCESS FULL BIG_TABLE (cr=1840797 pr=1840758 pw=0 time=384004887 us)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file scattered read                      14425        0.22        195.87

  db file sequential read                        13        0.01          0.06

  SQL*Net message from client                     2        0.00          0.00

 

 

# 분석 #
A. 상기 항목으로 알수 있는 사실

1. 많은 양의 physical I/O가 수행되었다. (disk : 1840758)
2. 많은 양의 논리적 일기가 수행되었다. (queyr + current : 1840797)
3. 100 cpu 시간을 소비했다. (Fetch Cpu : 99.36)
4. 262 초 동안의 elapse time 소비되었다. 그러므로 어떠한 이유로 많은 시간동안 대기를 수행했다.
5. 오랜 기간동안 scattered read db 파일 읽기를 위해 대기했었다. 이것은 테이블 full scan을 수행했기 때문에 발생
    (db file scattered read                      14425        0.22        195.87)
6. 아주 거대한 테이블을 풀 스캔 했음
7. 우리가 처리한 쿼리를 볼 수 있다.
8. 사용된 플랜을 확인할 수 있다.

B. 상기 사실로 할수 잇는 무엇일까? 이미 우리는 다음과 같은 내용을 알고 있다고 가정한다.
a) the data                  : 데이터량
b) how oracle works : 오라클 작동 방식

튜닝을 수행한다면, 상단에서 명백하게 우리가 알수 잇는것은 무엇인가?

일단 128,000,000의 로에서 700,000의 결과를 보였음을 알수 있다. 이것은 우리에게 2가지 사실을 알려준다.
 
9. 실제로 우리가 필요로 하는 결과 셋의 row는 매우 작은 양이다.
10. 테이블의 HWM(High Water Mark)가 동작 가능하다. - 이것은 테이블이 매우 압축되어 있음을 나타낸다. 만약 테이블에 1,000,000 로만이 들어 있었다면, 테이블이 너무 크다고 걱정했을 것이다. 그래서 테이블을 축소하거나, 재구성 하지 않았을 것이다. So, shrinking or reorging the table isn't going to do anything

됬다. 이제 우리는 몇가지 가능한 옵션을 제시할 수 있다.

a) full scan을 더 빠르게 만들기 - 테이블 압축을 수행하여 처리
b) 테이블의 0.54%의 테이블 로를 이용하여 우리의 쿼리를 수행할 수 있다. (subobject_name 의 not null 만 세는 행동), 아마도 인덱스가 도움을 줄 것이다.

# 다음과 같이 바꿔보자.

big_table%ORA10GR2> create index big_table_so_idx on big_table(subobject_name);

 

Index created.

그리고 다음과 같이 수행한다.

ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable( waits=>true );

 

PL/SQL procedure successfully completed.

 

 

ops$tkyte%ORA10GR2> select count(subobject_name) from big_table.big_table;

 

COUNT(SUBOBJECT_NAME)

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

               688256

tkprof는 다음과 같이 보일 것이다.

select count(subobject_name)

from

 big_table.big_table

 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.04          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.28       0.52       3342       3355          0           1

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

total        4      0.28       0.56       3342       3355          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 344

 

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=3355 pr=3342 pw=0 time=520528 us)

 688256   INDEX FAST FULL SCAN BIG_TABLE_SO_IDX (cr=3355 pr=3342 pw=0 time=730570

us)(object id 161278)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file scattered read                         42        0.01          0.27

  SQL*Net message from client                     2        0.00          0.00

 

실제 에서는 물론 더 복잡할 것이다. 아마도 복합 테이블 조인을 이용하거나 복잡한 관계를 다뤄야 할 것이다.

상단 쿼리를 튜닝한 후 결과는 :

a) 툴에 실행을 수행하도록 하고, 처리하도록 노하두자, (tuning/performance pack of Enterprise manager). 그러면 적용된 룰에 따라 인덱스가 무엇이고, 뷰는 어떤것이 있는지,쿼리를 재작업해서 다시 작성 하고, (11g라면 스키마 파티셔닝 수행도 함) 해서 쿼리의 유용한 정보를 낼 것이다.

b) tkprof로 부터 도출된 사실들을 이용하고, 다른 소스나 당신이 이미 알고 있는 지식 1) 데이터, 데이터의 패턴, 2) 오라클의 인덱스 정보, 스키마 구조(클러스터, IOT, 힙테이블, 파티셔닝 등)을 제공한다. 많은 경우 SQL쿼리는 재 작성 된다.

tkprof로 튜닝을 할때에는 반은 tkprof가 산출한 사실들과, 다른 반은 당신이 가지고 있는 DB 지식을 이용하여 처리하는 것이다. 만약 DB의 지식을 모른다면 엔터프라이즈 관리자의 성능 패키지를 이용하여 정보를 수집할 수도 있다.