성능 개선을 위한 기본 지식 - 실행 계획

 

1. 들어가며,

 

SQL 성능 관련해서 '좌변은 절대 가공하지 말라'라는 말이 있다.

 

WHERE를 사용하며, 왼쪽 컬럼을 가공하면 인덱스를 사용하지 못하기 때문이다.

 

이러한 사실을 망각하고, 무조건 좌변을 가공하지 않기 위해 

 

WHERE '201701' = SUBSTR(T1.ORD_TMD,1,6)같은 SQL을 작성하거나, INNER JOIN 순서를 바꾼다고 성능이 좋아질 것이란 결론에 도달하기도한다. 

 

결론적으로 SQL 성능 개선을 위한 방법들은 절대 암기할 것이 아니라, 원리를 잘 파헤치고 고민하는 과정이 중요하다 

 

항상 실행계획을 확인하고, 문제점이 무엇인지 찾아보는 것이 좋다. 

 

---

2. 실행 계획 이란

 

오라클과 같은 DBMS에서 데이터를 조회하거나 변경하려면, SQL을 사용해야한다.

 

SELECT,UPDATE를 사용하는 일이 단순해보이지만, 내부적으로 많은 일을 하며, 그 중 중요한 작업 하나가 실행계획을 만드는 일이다.

 

실행계획은 SQL을 처리하기 위해 내부적으로 만드는 작업계획 또는 절차를 의미한다. 

 

1) 구문 해석 , 2) 실행계획 수립, 3) 실행 순서로 SQL을 처리한다. 

 

SQL을 작성함에 있어 실행 전 데이터를 어떤 방법과 순서로 처리할 지 실행계획을 먼저 만들고, 이에 다라 성능이 달라진다. 따라서 SQL 성능을 개선하기 위해 실행계획에 대한 이해가 필요하다.

 

---

 

2. 실행계획 확인하기 

 

오라클을 사용하는 SQL Developer에서 실행계획을 확인하는 방법은 다음과 같다.

-- 실행계획생성
EXPLAIN PLAN FOR 
SELECT * FROM T_ORD WHERE ORD_SEQ = 4;
--실행계획 확인 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

* 혹은 SQL을 작성 후 F10을 눌러보자 

 

 

위와 같은 실행계획을 얻을 수 있을 것이다. 

 

• Id : 실행계획 식별자 
• Operation : 실행계획을 전체적으로 보여주며, 계층적인 이유는 1,2가 0의 자식 단계이기 때문임 
  • 0번의 자식이 1, 1번의 자식이 2
  • 최종적으로 2->1->0의 순서로 실행된다. (처리 순서를 정확하게 정의하긴 어렵지만, 자식이 먼저 처리되는 개념이라는 것만 정확하게 이해하자)
• Predicate Information : 실행계획 각 단계에서 사용한 조건이나 연산 정보를 보여준다. 
  • 성능 개선을 잘 하려면, 이를 반드시 실펴봐야 한다. 
  • 어떤 조건으로 처리했는지에 따라 성능 차이가 발생한다.
• Name: 해당 단계에 작업을 수행한 대상 오브젝트(테이블 혹은 인덱스)
• Rows: 해당 단계 수행시 조회될 예상 데이터 건수 
• Bytes : 해당 단계까지 사용될 예상 데이터양 (누적)
• Cost : 해당 단계까지 사용될 예상 비용(누적)
• Time : 해당 단계까지 사용될 예상 시간(누적) 

ex) 2번 단계의 오퍼레이션은 INDEX UNIQUE SCAN이고, 대상은 PK_T_ORD이다 이와 함께 Predicate Information이 표시된 것을 살펴보면 access("ORD_SEQ"=4)를 사용한 것을 알 수 있다. 

즉, PK_T_ORD 인덱스를 INDEX UNIQUE SCAN방식으로 ORD_SEQ가 4인 데이터를 찾고 있는 것임

 

---

 

3. 실행계획의 순서 

 

오퍼레이션 실행 순서는 SQL 성능을 좌우하는 요소 중 하나이다. 

예를 들어 조인 전에 GROUP BY가 수행되면 조인 대상이 줄어들어 성능이 개선되기도 한다.

 

EXPLAIN PLAN FOR
SELECT * 
FROM T_ORD T1, M_CUS T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
AND T2.CUS_GD = 'A';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

• 1의 자식이 2와 3이고, 2와 3은 형제지만, 위에 있는 2가 형이다. 
• 부모보단 자식이 먼저 실행되고, 자식 중 형이 먼저 실행된다. 
• 2->3->1->0

쫌 더 복잡한 쿼리를 살펴보자 

 

EXPLAIN PLAN FOR
SELECT T3.ITM_ID, SUM(T2.ORD_QTY) ORD_QTY
FROM T_ORD T1, T_ORD_DET T2, M_ITM T3
WHERE T1.ORD_SEQ = T2.ORD_SEQ
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
AND T2.ITM_ID = T3.ITM_ID
AND T3.ITM_TP = 'ELEC'
GROUP BY T3.ITM_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

• 기본 순서는 부모보다 자식이 먼저, 자식 중 형이 먼저라고 생각하면 된다. 
• SORT조인 이전에 HASH JOIN을 했고, 2번 단계는 3번의 INDEX FULL SCAN을 포함하는구나 등을 생각하며 전체적인 그림만 그릴 수 있으면 일단 충분하다! 

---

실제 실행계획 확인하기 

 

실제 실행계획을 확인하려면, SQL의 GATHER_PLAN_STATISTICS 힌트를 사용하면된다.

 

힌트가 포함된 SQL이 실행 완료 후에는 DBMS_XPLAN.DISPLAY_CURSOR를 이용해 확인할 수 있다.

 

DBMS_XPLAN.DISPLAY_CURSOR를 이용하려면, 뷰에 SELECT 권한이 필요하다.

(SYS 계정을 이용해 SELECT 권한을 주어야한다)

- V_$SQL, V_$SQL_PLAN_STATISTICS_ALL,V_$SQL_PLAN, V_$SESSION 

위 뷰들에 SELECT 권한을 줬다면 다음과 같이 확인 가능하다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */ 
*
FROM T_ORD T1, M_CUS T2 
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
AND T2.CUS_GD = 'A';

-- 아래 힌트를 통해 SQL을 실행

 

 

실제 실행계획을 확인하려면 GATHER_PLAN_STATISTICS 힌트를 사용한 SQL의 SQL_ID와 CHILD_NUMBER가 필요하다.

이를 얻기 위해 다음과 같은 SQL을 작성하자 

SELECT T1.SQL_ID, T1.CHILD_NUMBER, T1.SQL_TEXT
FROM V$SQL T1
WHERE T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.LAST_ACTIVE_TIME DESC;

SQL_ID와 CHILD_NUMBER를 얻을 수 있다. 이를 이용해 실제 실행 계획을 확인해보자 

 

 

• Id: 실행계획의 오퍼레이션 Id
• Operation : 해당 단계 수행한 내용
• Name : 해당 단계 작업을 수행한 대상 오브젝트(테이블 또는 인덱스)
• Starts: 해당 단계 수행한 횟수
• E-Rows : 해당 단계의 예상 데이터 건수 
• A-Rows : 해당 단계의 실제 데이터 건수 
• A-Time : 해당 단계까지 수행된 실제 시간(누적)
• Buffers: 해당 단계까지 메모리 버퍼에서 읽은 블록수(논리적 IO 횟수,누적)
• Reads: 해당 단계까지 디스크에서 읽은 블록 수 (물리적 IO,누적)
• 0Mem,1Mem,Used_Mem : SQL처리를 위해 사용한 메모리 수치 

 

성능 개선을 위해 주의 깊게 볼 항목은 A-Rows,A-Time,Buffers이다. 이 항목들이 눈에 띄게 높은 수치가 높아진 단계가 있다면 해당 부분의 원인을 찾아 성능을 개선하면 된다. 

 

운영에서 서비스될 SQL에는 GATHER_PLAN_STATISTICS 힌트가 포함되지 않게 주의가 필요하다. 불필요한 수치 수집으로 성능상 손해를 볼 수 있다. '

 

---

https://www.yes24.com/Product/Goods/82818767

SQL BOOSTER - 예스24