튜닝이 필요한 SQL 골라내기

출처 : KACHISORI

 

본 자료는 일본 @IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#tuneorasql) 에 株式会社アゲハ加藤 猛씨가 연재한 Oracle SQLチューニング講座를 번역 재구성한 것입니다.

 

전회까지 SQL 튜닝을 실시하기 위해 필요한 기초지식을 설명했습니다. 이번회에서는 튜닝 대상으로 해야 할 SQL를 어떠한 관점으로부터 어떻게 골라내는지 설명합니다.

 

■ 튜닝이 필요한 SQL 골라내기

---

통상 어플리케이션에서는 많은 SQL이 실행되어지고 있습니다. SQL 튜닝의 스텝은 실행되고 있는 많은 SQL중에서 튜닝의 목표에 맞추어 대상 SQL를 밝혀내는 것부터 시작됩니다.

 

일반적으로 표 1의 조건을 1개이상 만족하는 SQL은 튜닝에 의한 개선이 가능할지를 검토하는 대상이 됩니다. 우선은 이러한 SQL를 밝혀내 튜닝의 최종 목표를 고려하고 대상으로 할 SQL를 결정해야 합니다.

 

예를 들면 특정 어플리케이션의 처리 속도 개선이 목표의 경우에는 해당 어플리케이션중에서 처리에 시간이 걸리는 SQL에 주목합니다. 또 시스템 전체의 throughput 향상이나 시스템 부하(CPU, 디스크 I/O등)의 절감을 목표로 하는 경우에는 많은 자원을 사용하고 있는 SQL나 실행 회수가 많은 SQL에 주목합니다.

 

1실행 당 실행 시간이 긴 SQL

디스크 읽기 블록수가 많은 SQL

버퍼의 읽기수가 극단적으로 많은 SQL

실행 회수가 극단적으로 많은 SQL

표 1　튜닝 대상으로서 골라니야할 SQL

 

■ 대상 SQL의 선별 방법

---

튜닝 대상으로 선정할 SQL를 선별하는 방법은 이하의 2개가 있습니다.

1. 동적 퍼포먼스뷰에서 SQL를 추출하는 방법

2. 어플리케이션의 SQL 트레이스를 취득하는 방법

어플리케이션이 특정 되어 있는 경우라면 각 SQL의 해석 시간, 실행 시간, 실행 계획등의 정보를 간단하게 얻을 수 있는 SQL 트레이스의 취득이 가장 유효합니다. 취득한 트레이스 파일을 포맷 해 SQL를 「처리 시간」 「독해 블록수」 「실행 회수」등으로 소팅해 튜닝 대상의 SQL를 간단하게 좁힐 수 있습니다.

다만, SQL 트레이스는 비교적 부하가 높고 어플리케이션의 처리 속도에 영향을 줄수 있습니다. SQL 트레이스의 오버헤드가 부담되는 경우나 어플리케이션이 특정 되어 있지 않은 경우에는 동적 성능뷰(V$ 테이블)를 사용하는 방법을 검토합니다. 동적 성능뷰에도 각 SQL이 인스턴스 전체에서 어느 정도 자원을 사용하고 있었는지를 확인할 수 있습니다.

표 2는 각각의 방법의 주된 특징과 메리트/디메리트를 집계한 것입니다.어느 쪽을 사용할까는 튜닝의 목적 취득하는 환경에 맞추어 선택하시면 됩니다.

 

	동적 퍼포먼스뷰	SQL 트레이스
조사 가능한 SQL	・현재 공유 SQL영역에 캐쉬되어 있는 SQL ・사용하는 V$테이블 　- V$SQL 　- V$SQL_TEXT 　- V$SQL_PLAN	・세션으로 실행된 모든 SQL ・초기화 파라미터 SQL_TRACE=TRUE로 기동 후 확립한 모든 세션의 SQL ・DBMS_SYSTEM 패키지를 사용해 지정한 개별세션의 SQL
주로 확인할 수 있는 항목	- SQL - 누적 CPU 시간 - 누적 처리 시간 - 버퍼에서의 누적 읽기 블록수 - 디스크에서의 누적 읽기 블록수 - 이 SQL가 실행된 누적 회수 - 실행 계획	- SQL - CPU 시간 - 처리 시간 - 버퍼에서의 읽기 블록수 - 디스크에서의 읽기 블록수 - 실행 계획
메리트	・어플리케이션의 처리 속도에 대한 영향이 적다 ・SQL로 간단하게 확인 가능	・어플리케이션중에서 실행되고 있는 각  SQL에 관해서 상세한 정보를 취득 가능 ・어플리케이션의 처리 속도가 RDBMS 측에 있는지 아닌지의 분리에 사용 가능
디메리트	・1회당의 실행 시간 등은 평균치 밖에 취득할 수 없다 ・메모리상에 캐쉬되고 있는 SQL의 정보 밖에 확인할 수 없다	・트레이스의 취득, 파일에의 기입에 수반하는 오버헤드가 있다 ・트레이스 파일 취득을 위한 디스크 영역이 필요
표 2　동적 파포만스뷰와 SQL 트레이스의 차이

그림 1　V$SQL와 SQL 트레이스의 사용구분

 

①v$SQL을 검색

    메모리상의 전SQL정보(SQL1-SQL5)를 참조

②SQL 트레이스를 취득

    이 세션에 SQL트레이스를 설정해 SQL4,SQL5를 실행

 

■ 동적 성능뷰를 사용한 SQL문 걸러내기

---

 

문제가 있을 법한 SQL을 걸러내기 위해서 사용하는 동적 성능뷰는 주로 V$SQL, V$SQL_TEXT, V$SQL_PLAN의 3개로 이것들은 공유 SQL 영역에 보관 유지되고 있는 SQL의 정보를 표시합니다.이 공유 SQL 영역은 SQL를 재이용하기 위한 정보가 캐쉬되는 영역이 됩니다. 실행된 SQL의 정보는 반드시 이 영역에 캐쉬됩니다만 빈영역이 부족하게 되었을 경우 새로운 SQL를 위해서 실행 빈도가 낮은 SQL의 정보를 메모리에서 내려버립니다. 그 때문에 이 방법은 지금까지 실행된 모든 SQL의 정보를 취득할 수 있는 것은 아닌 점에 주의하삼.

 

필요한 정보는 직접 SELECT문으로 확인할 수도 있고 또, 일부의 정보는 Oracle가 표준으로 제공하고 있는 STATSPACK 유틸리티 , AWR를 사용하여 취득가능 　

 

표 3은, SQL 조사에 사용하는 V$ 테이블의 열명, 의미입니다. 여기서 소개한 V$SQL_PLAN는 실행 계획을 확인하는 뷰이므로 다음에 설명합니다.

뷰명	주된 격납 정보	주된 열명	열값의 의미
V$SQL	・SQL의 선두1000bytes ・SQL의 누적 자원 사용 상황	SQL_TEXT	SQL의 선두 1000bytes
		SORTS	SORT의 회수
		FETCHES	FETCH의 회수
		EXECUTIONS	실행 회수
		USERS_EXECUTING	현재 실행중의 유저수
		PARSE_CALLS	해석 콜의 회수
		DISK_READS	디스크 읽기수
		BUFFER_GETS	버퍼 읽기수
		ROWS_PROCESSED	SQL가 되돌리는 행수
		CPU_TIME	처리에 사용한 CPU 시간(마이크로 세컨드)
		ELAPSED_TIME	처리에 사용한 경과시간(마이크로 세컨드)
		ADDRESS	다른 View와의 결합에 사용
		HASH_VALUE	다른 View와의 결합에 사용
V$SQL_TEXT	・SQL의 전문	SQL_TEXT	64 bytes 단위로 분할된 SQL
		PIECE	분할된 SQL의 단편 번호
		ADDRESS	다른 View와의 결합에 사용
		HASH_VALUE	다른 View와의 결합에 사용
V$SQL_PLAN	・SQL의 실행 계획	OBJECT_NAME	실행 계획중의 테이블, 색인명
		OPERATION	실행 계획 오퍼레이션
		OPTIONS	실행 계획 오퍼레이션의 옵션
		OPTIMIZER	옵티마이져 모드
		DEPTH	실행 계획의 Tree의 깊이
		ADDRESS	다른 View와의 결합에 사용
		HASH_VALUE	다른 View와의 결합에 사용
표 3　SQL의 조사에 사용하는 주된 V$테이블

 

◆ 합계 실행 시간이 긴 SQL 추출

V$SQL를 참조해 대상이 되는 SQL를 걸러내려면 리스트 1에 나타낸 SQL를 실행합니다. ORDER BY구의 조건을 변경해 목적에 맞추어 걸러낼 조건을 변경합니다. 합계 실행 시간이 긴 SQL를 밝혀내는 경우에는 ORDER BY구의 조건에“elapsed_time”를 지정합니다. 예에서는 톱 10을 표시하고 있습니다만 맨 마지막 줄의 rownum의 지정을 변경해 출력하는 건수를 변경하시길..

 

SET LINES 140 COL sql_text            FORM A140 COL buffer_per_run      FORM 999999999999 COL disk_per_run        FORM 999999999999 COL cpu_time            FORM 999999999999 COL elapsed_time        FORM 999999999999 SELECT *    FROM (SELECT sql_text,address,hash_value,parse_calls,executions,                buffer_gets,disk_reads,                buffer_gets/executions buffer_per_run,                disk_reads/executions disk_per_run,cpu_time,                elapsed_time           FROM v$sql          WHERE executions > 0          ORDER BY elapsed_time desc)   -- ←이 조건을 변경한다  WHERE rownum <= 10;                   -- ←표시건수는 이 값을 변경한다

리스트 1　V$SQL를 참조하는 SQL

SQL_TEXT ----------------------------------------------------------------- ----------------------------------------------------------------- ---------- ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME -------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- ------------- SELECT count(*) FROM lineitem WHERE l_orderkey=:b1 563F6734  349970465           1          3      325716     325606           108572        108535      44190000     186913533

리스트 2　합계 실행 시간의 긴 SQL의 출력예

 

출력예를 보면 ELAPSED_TIME열의 값이 「186.913533초」인 것을 확인할 수 있습니다. 이 SQL는 EXECUTIONS가  3 이기 때문에 1회의 실행 당  약 62초 (≒186.91353/3) 걸렸다고 추측됩니다.

 

◆ 버퍼 읽기 블록수가 많은 SQL

버퍼 읽기수에 관해서는 한번 실행 당  읽기수와 누적 읽기수의 2개의 관점으로부터 조사합니다.

한번 실행 당 버퍼 읽기수가 많은 SQL를 밝혀내는 경우는 ORDER BY구의 조건에   buffer_gets/executions를하고  누적 읽기수가 많은 SQL를 밝혀내는 경우는 ORDER BY구의 조건에 buffer_gets 를 지정합니다. STATSPACK 리포트에서는 SQL statements ordered by buffer gets 섹션의 출력에 해당하는 정보가 됩니다.

 

대량의 버퍼 읽기를 실시하고 있는 SQL는 적절치 않은 색인이나 연결 색인의 일부를 사용하고 있을 가능성이 있습니다.

　

이하의 출력예는, buffer_gets를 지정한 결과입니다.

SQL_TEXT ----------------------------------------------------------------- -------------------- ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS   BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME -------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- ------------- SELECT o_orderkey,o_orderstatus FROM orders WHERE o_orderkey=:b1 565714CC 3088221154           1          4       96300      90649            24075         22662       4930000       5201728

리스트 3　버퍼 읽기 블록수가 많은 SQL의 출력예

출력예를 보면, BUFFER_GETS열의 값으로 누적 읽기수 (96300), 한번 실행 당의 읽기수가 (24075)라 확인 할수 있습니다.

 

◆ 디스크 읽기 블록수가 많은 SQL

디스크 읽기수가 많은 SQL를 밝혀내는 경우는 ORDER BY구의 조건에 “disk_reads”를 지정합니다. STATSPACK의 리포트의 「SQL ordered by Reads for DB」섹션의 출력에 해당합니다.

 

디스크 읽기가 다발하고 있는 경우 효율이 나쁜 색인을 사용하고 있거나 풀 테이블 스캔을 실시하고 있을 가능성을 있습니다. 디스크 I/O는 메모리 I/O와 비교해서 큰폭으로 처리 속도가 떨어지므로 튜닝에 의해 디스크 I/O를 줄일 수 없는가 검토해야 합니다.

 

SQL_TEXT ----------------------------------------------------------------- ----------------------------------------------------------------- ---------- ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS   BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME -------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- ------------- select  100.00 * sum(case   when p_type like 'PROMO%'    then l_extendedprice * (1 - l_discount)   else 0  end) /  sum(l_extendedprice * (1 - l_discount)) as promo_revenue from   lineitem,  part where  l_partkey = p_partkey  and l_shipdate >= date '95-03-30' 54AE6DD0 3192138774           1          1      114571     291138           114571        291138     131140000     374139324

리스트 4　디스크 읽기 블록수가 많은 SQL의 출력예

 

출력예를 보면 DISK_READS열의 값이 (291138), 한번 실행 당 DISK_READS의 값을 나타내는 DISK_PER_RUN열의 값도  (291138) 인 것을 확인할 수 있습니다.

데이타베이스의 기동 후, 처음으로 그 데이터에 액세스 한 경우는 반드시 디스크에서 읽어 들여야 합니다. 이 때문에 이러한 정보는 기동 후 어느 정도 시간이 경과한후 취득하는 것이 좋습니다.

 

◆실행 회수가 많은 SQL

실행 회수가 많은 SQL를 밝혀내는 경우는 ORDER BY구의 조건에 “executions”를 지정합니다.STATSPACK의 리포트의 「SQL ordered by Executions for DB」섹션의 출력에 해당합니다.

 

실행 회수가 많은 SQL는 1회의 실행으로 액세스 하는 블록수가 적은 경우라 하더라도 합계를 보면 매우 많은 블록수를 나타내므로 튜닝 대상이 됩니다. 예를 들면 한번 실행으로 20 블록을 스캔 하고 있는 SQL가 10만회 실행되고 있는 경우라면 튜닝으로 블록수를 불과 5 블록만이라도  줄일 수 있다 하더라도 5 블록*10만회에 50만 블록분을 줄일수 있습니다. ORACLE 블록 사이즈가 8 Kbytes의 경우에서는, 4 Gbytes만큼의 논리 읽기를 피할수 있는 계산이 됩니다

 

SQL_TEXT ----------------------------------------------------------------- ----------------------------------------------------------------- ---------- ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS  BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME -------- ---------- ----------- ---------- ----------- ---------- -------------- ------------- ------------- ------------- SELECT n_nationkey,n_name FROM nation WHERE n_nationkey=:b1 547AE7A4 1851458320         229     791679     23750370          1              30             0      45650000     136377414

리스트 5　실행 회수가 많은 SQL의 출력예

 

출력예를 보면, EXECUTIONS열의 값이 (791679)이며, 1 실행 당의 읽기수가 (30) 이라고 확인할 수 있습니다. 전체의 읽기 블록수^注1에 대한 이 SQL의 읽기 블록수가 많다고 생각할 수 있는 경우는, SQL 튜닝 후보로 합니다

 

注1：전체의 읽기 블록수 전체의 독해 블록수는  session logical reads 통계치가 해당합니다.V$SYSSTAT로 확인하면 값이 데이타베이스 기동시부터의 누적치이므로 해당 SQL의 처리 전후의 값을 취득하고, 비율을 확인합니다

 

STATSPACK 리포트에는 지금까지 설명한 섹션 이외에 해석 회수가 많은 SQL(SQL statements ordered by Parse Calls) 공유 메모 리사이즈가 큰 SQL(SQL statements ordered by Sharable Memory), 버젼 카운트가 많은 SQL(SQL statements ordered by Version Count)도 리포트됩니다.

 

■ SQL 전문의 취득 방법

---

 

지금까지 이용해 온 V$SQL 뷰는 SQL의 선두로부터 1000 bytes까지 밖에 표시되지 않았습니다. 1000 bytes를 넘는 긴 SQL를 사용하고 있는 경우에는 V$SQL_TEXT를 참조해 완전한 SQL를 취득하는 것이 가능합니다.

 

 V$SQL로부터 ADDRESS열, HASH_VALUE열의 값을 확인해, 리스트 6의 SQL를 실행합니다.

set pages 100 feed off timing off echo off lines 140 SELECT sql_text   FROM v$sqltext  WHERE hash_value=1273901568    and address='54B4DF20'  ORDER BY piece;

리스트 6　전문을 취득하는 SQL의 예

SQL_TEXT ---------------------------------------------------------------- select  l_returnflag,  l_linestatus,  sum(l_quantity) as sum_qty ,  sum(l_extendedprice) as sum_base_price,  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,  sum(l_extendedprice * (1  - l_discount) * (1 + l_tax)) as sum_charge,  avg(l_quantity) as  avg_qty,  avg(l_extendedprice) as avg_price,  avg(l_discount) a s avg_disc,  count(*) as count_order from  lineitem where  l_shi pdate <= date '1998-12-01' - interval '1' day (3) group by  l_re turnflag,  l_linestatus order by  l_returnflag,  l_linestatus

리스트 7　SQL를 전문 출력한 출력예

 

◇

 

이번회에서는 튜닝 대상의 SQL를 동적 성능뷰를 통해 걸러내는 방법을 설명했습니다.다음 회에서는 SQL 트레이스 실행 계획의 취득 방법과 그 견해에 대해 설명합니다.