<div class="warning"> 여기서 사용된 내용중 일부는 http://blog.naver.com/hirokorea/20028862482 에서 가져온 것입니다. </div>

튜닝할 대상 SQL 선별하기#

  1. 동적 파포만스뷰로부터 SQL를 추출하는 방법
  2. 어플리케이션의 SQL 트레이스를 취득하는 방법

두 방법의 차이점

동적 뷰#

  1. 현재 공유 SQL 영역에 캐쉬되고 있는 SQL
  2. 사용하는 V$테이블(10g 이전)
  • V$SQL(공통)
  • V$SQL_TEXT(10g이전)
  • V$SQL_PLAN(10g이전)
  • V$SQLAREA(10g이후) - V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
  • V$SQLAREA_PLAN_HASH(10g이후) - V$SQLAREA_PLAN_HASH displays statistics on shared SQL area (V$SQL) by grouping on the SQL_ID and PLAN_HASH_VALUE columns. It can potentially create several rows for one parent cursor, one for each distinct value of the column PLAN_HASH_VALUE.
  • V$SQLSTATS(10g이후) - V$SQLSTATS returns basic performance statistics for SQL cursors, with each row representing the data for a unique combination of SQL text and optimizer plan (that is, unique combination of SQL_ID, and PLAN_HASH_VALUE). The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool). Note that V$SQLSTATS contains a subset of columns that appear in V$SQL and V$SQLAREA.
  • V$SQLTEXT(10g이후) - This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

SQL TRACE#

  1. 세션으로 실행된 모든 SQL
  2. 초기화 파라미터 SQL_TRACE=TRUE로 기동 후 확립한 모든 세션의 SQL
  3. DBMS_SYSTEM 패키지를 사용해 지정한 개별세션의 SQL

동적 뷰#

  1. SQL
  2. 누적 CPU 시간
  3. 누적 처리 시간
  4. 버퍼로부터의 누적 읽기 블록수
  5. 디스크로부터의 누적 읽기 블록수
  6. 이 SQL가 실행된 누적 회수
  7. 실행 계획

SQL TRACE#

  1. SQL
  2. CPU 시간
  3. 처리 시간
  4. 버퍼로부터의 읽기 블록수
  5. 디스크로부터의 읽기 블록수
  6. 실행 계획

동적 뷰#

  1. 어플리케이션의 처리 속도에 대한 영향이 적다
  2. SQL로 간단하게 확인 가능

SQL TRACE#

  1. 어플리케이션중에서 실행되고 있는 각 SQL에 관해서 상세한 정보를 취득 가능
  2. 어플리케이션의 처리 속도가 RDBMS 측에 있는지 아닌지의 절단 분리에 사용 가능

동적 뷰#

  1. 1회당의 실행 시간 등은 평균치 밖에 취득할 수 없다
  2. 메모리상에 캐쉬되고 있는 SQL의 정보 밖에 확인할 수 없다

SQL TRACE#

  1. 트레이스의 취득, 파일에의 기입에 수반하는 오버헤드가 있다
  2. 트레이스 파일 취득을 위한 디스크 영역이 필요

실행시간이 긴 SQL#

SELECT * FROM
(SELECT
        sql_text,
        executions,
        cpu_time,
        elapsed_time,
  (elapsed_time/1000000)/executions average_time
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc)
WHERE rownum <= 10;

여기서 중요한 값은 executions, elapsed_time 두가지이다. executions는 촐 실행횟수, elapsed_time는 총 소요시간이니 평균소요시간을 보기 위해서는 elapsed_time/executions 값인 average_time(초 단위) 를 보면 된다.

버퍼 읽기 블럭수가 많은 SQL#

SELECT * FROM
(SELECT
        sql_text,
        executions,
        buffer_gets,
        buffer_gets/executions buffer_per_run
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc)
WHERE rownum <= 10;

여기서 중요한 값은 buffer_gets, buffer_per_run 이다. 대량의 버퍼를 읽는 SQL은 적절하지 않은 인덱스나 연결 인덱스의 일부를 사용할 가능성이 높다.

디스크 읽기 블럭수가 많은 SQL #

SELECT * FROM
(SELECT
        sql_text,
        executions,
        disk_reads,
        disk_reads/executions disk_per_run
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc)
WHERE rownum <= 10;

디스크를 많이 읽을 경우 인덱스를 잘못 사용하거나 Full table scan작업을 많이 수행하는 것으로 볼수 있다.

실행횟수가 많은 SQL#

SELECT * FROM
(SELECT
        sql_text,
        executions,
  buffer_gets/executions buffer_per_run
FROM v$sql
WHERE executions>0
ORDER BY elapsed_time desc)
WHERE rownum <= 10;

실행횟수의 경우 executions 값을 참고하면 되지만 총 실행횟수에 대한 디스크및 버퍼 사용도 함께 체크해볼 필요가 있다. 그래서 가급적이면 buffer_per_run 값도 함께 체크해주는 것이 좋다.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-4) was last changed on 05-Jan-2008 22:38 by DongGukLee