1장. [SQL 분석 도구] 1-1. Explain Plan

@?/rdbms/admin/utlxplan.sql

1. 튜닝 쉽게 이해하기

내 기준에서다. 평소 축구를 즐겨보기에 축구라는 종목을 기준으로 SQL 튜닝을 이해해보려고 한다.

 

1. 실행계획

SQL 튜닝하기 위해서는 실행계획이 파악하는 것이 중요하다. 그 실행계획을 분석하여 어떻게 튜닝할 지를 결정할 수 있기 때문이다. 내가 한 구단의 감독이라면, 이 실행계획을 가볍게 스쿼드를 짜는 것이라고 생각하면 될 것이다.

예산은 정해져 있기 때문에, 각 포지션의 어떤 선수를 영업할 것인지 11명의 선수로 어떤 포메이션을 할 것인지 감독은 다양한 관점으로 생각해 둔다.

 

Auto Trace

Auto Trace는 쿼리 수행 결과와 계획 통계를 sql이 알아서 출력해주도록 설정해주는 것이다. 감독이 해당 선수를 가볍게 검증하기 위해 나이는 몇살인지, 출전횟 수, 골 수, 어시스트 수 등을 문서로 파악하는 과정 정도라고 보면 되겠다.

 

SQL Trace & Event Trace

단순히 골 수 정도만 보는 것이 아니라, 어느 영역에서 얼마나 뛰었는지, 부상의 정도는 어떠한지, 총 몇 KM를 뛰었는지 순간 속도는 어떤지, 점프는 얼마나 하는지, 포지션을 바꿔도 해당 능력이 잘 발현되는지 등등 좀 더 면밀하게 내 구단에 얼마나 영향을 미칠 수 있는 선수인지를 파악하는 과정? 이라고 보면 되겠다. 그거를 확인하는 과정을 메디컬테스트라고 보면 될 것 같다.

 

2. 문제해결

 

SQL 쿼리 변환, 힌트

축구를 하는 과정에서 선수를 교체하거나 포메이션을 변경하거나 다양한 전술을 변경하는 정도? 의 처리라고 보면 될 것 같다. 감독의 용병술이 경기장에서는 아주 중요한 요소중에 하나이다. 마찬가지로 시간을 많이 들이지 않고 최적화 할 수 있는 가장 좋은 방법중에 하나가 쿼리 변경과 힌트이다.

 

인덱스 조정

주전이었던 선수를 2군으로 내린다던지, 2군에 있던 선수를 1군으로 올린다던지, 공격수였던 선수를 미드필터로 변경한다던지 하는 조금 더 신중하게 분석한 후에 해야하는 결정정도로 이해하면 될 것 같다. 말 그대로 인덱스 조정은 가볍게 해서는 안될 부분이다. 검색에 아주 중요한 영향을 미치기 때문이다.

 

반정규화, 정규화

선수나 감독을 영입하거나 방출한다던지 하는 좀 더 무거운 결정이 되는 부분이다. 말 그대로 테이블 (선수단) 자체를 변경하지 않으면 개선되지 않는 수준일 때 이러한 방법을 사용하는 것을 고려한다? 정도로 이해하면 될 것같다.

 

결국 중요한 것은 실행계획을 철저하게 분석하고 준비하지 않으면, 동네 축구보다 못한 수준의 실력을 발휘하게 될 수도 있다는 것이다. 그만큼 감독(개발자)의 능력은 선수단(DB)에 매우매우 중요한 영향을 미친다. 그럼 본격적으로 정리를 시작해보자.

 

2. Plan Table 생성

오라클 10g 이전까지는 직접 Plan Table을 생성해줬어야 되었다고 한다. 바로 사용하자면 사실 11g 이후부터는 기본적으로 sys.plan_table$ 이 만들어지기 때문에 알 필요는 없지만, 나는 이해하고자 하기에 남겨두려고 한다.

 

코드정리

/* 생성방법 */
@?/rdbms/admin/utlxplan.sql

/* 테이블 구조 확인 */
desc plan_table

구조 출력 결과 

11g 이후

플랜테이블이 기본적으로 SYNONYM 으로 생성되어 있다.

select owner, synonym_name, table_owner, table_name from all_synonyms where synonym_name = 'PLAN_TABLE';

3. 실행계획 생성

/* 실행계획 생성 */
explain plan set statement_id = 'query1' for 
select * 
from emp
where empno = 7900;

/* 실행계획 플랜테이블로 출력 */
select operation, options, object_name, cost, cardinality, bytes
from plan_table
where statement_id = 'query1';

3. 실행계획 확인

/* 실행계획 확인 */
-- sql 파일 실행
@?/rdbms/admin/utlxpls

-- dbms_xplan으로 실행
select plan_table_output
from table(dbms_xplan.display('plan_table', 'query1', 'serial'));

 

본 글은 작성자가 공부한 것을 기록하기 위하여 작성한 것으로, 100% 정확한 정보가 아닐 수 있습니다. 정정을 요청하셔도 좋고 여러 의견을 주시면 너무너무 환영이고 감사하겠습니다.