프로그램을 작동시키기 위하여 데이터베이스(이하 DB)에서 특정 데이터를 읽어와야 하는 경우가 다분하다. 많은 이들이 관계형 데이터베이스 관리 시스템(Relational DataBase Management System, 이하 RDBMS)을 사랑하고, 증오하며, 이용하고 있다.
그러나 필자는 개발자와 RDBMS는 애증의 관계라고 생각한다.
어째서 애증의 관계라고 생각하는가 함은, RDBMS는 말 그대로 DB 내 데이터들에 대하여 여러 관계를 이어줌으로써 구체적인 정보를 조회할 수 있게하는 것과 연관이 있다. 조건문 또는 연계(join 구문)을 이용하여 데이터를 조회하는 과정에서, 요청 데이터를 산출하는 과정이 굉장히 느려질 수 있기 때문이다. (흔히들 퍼포먼스가 좋지 않다, 라고 표현한다)
필자 역시 이러한 위기에 처하였기에, 어떻게든 대처를 하고 조치를 취한 상태이다. 훗날 필자의 기억력이 가물가물해지고 검색 솜씨 또한 형편없어졌을 때를 대비하여 기록하는 것이니, 누구든 참고할 수 있다면 그리하자.
Query Tuning 하는 이유
Query 구문을 개선하는 이유는 다양하다. 호출 시 참조되는 메모리가 너무나도 방대하여 DB가 뻗어버린다거나(죽는다고도 표현한다. 개발자의 눈 앞이 하얘지는 상황이다) 혹은 응답에 대한 속도가 너무나도 느린 경우(사실 대부분이지 않을까 싶다)가 대표적일 것이다. 적어도 필자는 그러하였다. Query 관련 이슈 10개가 존재한다면, 그 중 9개가 퍼포먼스 이슈였고 나머지 하나가 메모리 이슈였다.
SQL Query Tuning 방식
- 목표 명확화
- 가장 먼저 고려해야 할 사항 중 하나는 비즈니스 결과입니다. 애초에 이 정보를 검색하는 이유는 무엇이며, 비즈니스 목표를 달성하기 위해 무엇이 필요합니까? 튜닝하기 전에 관련 이해 관계자가 누구이며 쿼리가 구체적으로 무엇을 목적으로 하는지 확인하는 것이 중요합니다. 또한 결과에 대한 의도된 청중을 알고 있는지 확인하십시오. 이를 통해 수신자에게 적절한 수준의 세부 정보가 포함된 결과 테이블을 생성할 수 있습니다.
- 요구 사항이 설정되면 다시 확인한 다음 필요한 요청을 수행하도록 프로덕션 데이터베이스를 설정합니다.
- 영향력이 높은 SQL 문 식별
- 영향력이 큰 SQL은 처리된 행 수, 버퍼 가져오기, 디스크 읽기, 메모리 KB, CPU 초, 정렬 또는 실행으로 결정할 수 있습니다. 이러한 영역에서 높은 숫자를 가진 모든 쿼리는 높은 영향을 미치므로 비용이 많이 듭니다.
- 실행 계획 확인
- SQL 문을 어떻게 실행할지 결정합니다. Oracle은 이를 위한 설명 계획 유틸리티를 포함합니다. Oracle 설명 계획 또는 실행 계획을 사용하면 SQL 문을 실행하지 않고도 SQL 문이 어떻게 실행되는지 볼 수 있습니다.
- 영향력이 큰 SQL을 결정하고 실행 경로의 구성을 살펴본 다음 권장 튜닝을 완료하는 데 도움이 되는 다양한 소프트웨어가 있습니다. 아래에서 도구를 자세히 다루겠습니다.
- 대규모 검색 피하기
- 특히 큰 데이터 테이블을 처리하는 경우 전체 테이블 검색을 방지해야 합니다. 이로 인해 불필요한 입출력이 발생하고 모든 속도가 느려질 수 있습니다.
- 쿼리로 요청하는 행의 수를 확인하고 인덱스를 사용하여 결과를 더 작은 조각으로 나눕니다. 인덱스를 사용하면 책에 대한 인덱스와 같이 데이터를 보다 효율적으로 검색할 수 있습니다. 인덱스를 작성하려면 포함할 열을 식별하고 인덱스 이름을 지정해야 합니다. 구문은 다음과 같습니다.
- 선택 항목 최적화
- SELECT 쿼리를 최적화하는 주요 방법 중 하나는 실제로 필요한 열만 포함하는 것입니다. 처리할 필요가 없을 수도 있는 대량의 데이터를 빼낼 수 있으므로 가능한 한 SELECT *를 사용하지 마십시오.
- 또한 가능한 한 SELECT DISTINCT를 사용하는 것을 피해야 합니다. 많은 경우 표에 수많은 값이 포함되어 있으며, 이러한 값이 중복되는 경우도 있습니다. SELECT DISTINCT를 사용하면 다른 값만 반환됩니다. 이것은 유용하게 들리지만, 이 요청을 처리하는 데는 많은 리소스가 필요한데, 쿼리의 모든 필드를 그룹화하여 고유한 결과를 생성하기 때문입니다.
- 타사 도구 사용
- DPA는 SQL 쿼리 성능 모니터링, 분석 및 튜닝을 위해 특별히 제작되었습니다. 특히 응답 시간 분석에 중점을 두어 데이터베이스의 응답 및 수행 방식을 추적합니다. 이를 통해 병목 현상을 파악하고, 변경으로 인해 응답 속도가 저하되는지 여부를 확인하고, 가장 중요한 근본 원인을 정확히 파악할 수 있습니다. DPA의 응답 시간 분석은 매초 수집되는 SQL 데이터를 기반으로 합니다.
- 소프트웨어에는 성능 병목 현상의 원인이 될 수 있는 SQL 문, 애플리케이션 대기 시간, 대기 유형 또는 이벤트를 보여주는 그래프가 포함되어 있습니다. 그래프는 상호 작용합니다. 그래프를 클릭하여 문제에 대한 자세한 내용을 확인할 수 있습니다.
- 데이터베이스 조정을 위한 DPA의 가장 유용한 측면 중 하나는 IT 직원을 올바른 방향으로 가리키도록 설계된 특별한 "튜닝 어드바이저"입니다. 튜닝 어드바이저는 즉각적인 초점이 필요한 문제를 강조하고 현재 메트릭과 비교할 수 있도록 보기 쉬운 과거 데이터를 제공합니다. 어드바이저는 실행 가능한 조언을 제공하며, 이상 탐지 기능은 비효율적인 SQL 문을 신속하게 탐지하는 데 도움이 될 수 있습니다. 이 조언을 적용하여 쿼리를 조정하면 프로세스가 훨씬 빨라져 데이터베이스를 느리게 실행할 수 있는 쿼리 비효율성 문제를 간소화할 수 있습니다.
위 내용은 출처에 명시된 DNSstuff(How to Make Select Query Faster in Oracle) 에서 가져왔다. 영문을 그대로 번역한 것이니 간단하게 설명을 덧붙여 알아보도록 하자.
- 해당 Query 구문을 통하여 '무엇을' 조회할 것인지 명확해야 한다.
- 필자 역시 4년차의(어찌보면 주니어, 어찌보면 시니어이기도 한 가장 애매한 시기라고 생각한다) 초보 개발자로, 처음에는 이해할 수 없었던 맥락이다. 그러나 점차 경력이 쌓이고 여러 회사를 거치며 다양한 영역과 연령대의 고객을 거치며, 개개인이 가질 수 있는 여러 개발론적 접근 방식 및 운영 방식을 접할 수 있었다.
- 그렇기에 우리는 [ 누가 ], [ 무엇을 ], [ 왜 ] 하는지 명확하게 인지하고 개발에 임해야한다.
- 사원급이 원하는 데이터의 경우, 사원에게 허용된 데이터만 조회하면 될 것이다. 전체 데이터는 과분하다는 것이다. 더불어 특수한 경우가 아닌이상 통계 데이터를 요청할 필요 역시 없을 것이다. 그만한 책임이 필요한 일을 사원급에게 요구할 회사는 거의 없을 테니까.
- 부장급이 원하는 데이터의 경우, 충분히 전체 데이터를 요구할 수 있다. 동시에 부서 내 통계 데이터 또한 요청할 수 있으며, 이러한 가능성을 충분히 인지해야한다. 책임이 따르는 직급인 이상, 더 많은 정보를 필요로 하며 개발자를 통해 정리된 자료를 원할 수 있다.
- 우리를 화나게 하는 원인을 정확하게 찾으라는 것(3번을 통해 확인할 수 있다) 같다.
- DB 관리 도구에서 해당 Query 구문에 대한 실행 계획을 검토해야 한다.
- 필자는 DBeaver(ver.Community) 프로그램을 애용한다. 개발 입문 때 부터 유용하게 사용해왔으며 굳이 유료 버전을 사용하지 않더라도 상용중인 대부분의 RDBMS 들은 DBeaver 하나로 관리가 가능하기 때문이다.
- DBeaver 내 SQL 편집기를 이용하여 Query 구문을 작성하는 경우가 많을 것이다. 작성한 Query 구문에서 우클릭 후 '실행' 탭을 확장하여 '실행 계획 보기' 기능을 사용하면 Query Tuning 시 많은 도움이 된다. (하단의 추가 내용을 참고하자)
- 전체 검색을 진행하는 경우를 피해야 한다.
- 앞서 설명한 실행 계획을 통해 파악할 수 있는 정보이다. 흔히들 Full Scan 이라고 부른다. 1000개의 데이터 중 10개의 데이터가 필요할 때, 미리 선언된 Index 가 존재한다면 1000개의 데이터를 모두 읽어올 필요가 없을 것이다. 그러나 별도로 선언된 Index 가 없다면, 10개의 데이터를 찾기 위해 1000개의 데이터를 모두 읽어야 하는 수고를 겪어야한다.
- 당연히 속도가 느려진다.
- 불필요한 데이터를 함께 조회하지 말아야 한다.
- 우리는 '스위트 바나나' 라는 정보만 있으면 된다. 하지만 그 바나나를 찾기 위해 브라질 전역의 모든 나무를 조회하여 정보를 가져올 필요가 없다는 것이다. 특정 나라의 특정 지역의 특정 나무의 특정 품종만 끌고와도 충분하다. 쓸데없이 고무나무 이딴거 같이 조회되지 않도록 하자.
- Query Tuning 작업에 적합한 도구를 사용하자.
- 필자는 사용하기도 쉽고, 이미 오랫 사용했기에 익숙하기도 한 DBeaver를 선호한다.
- 앞으로의 작업들 역시 DBeaver 내 기능을 이용할 예정이다.
예시 Query
필자의 Query 안에는 Inine view(From 절 내에서 사용되는 Sub Query) 형태로 잡혀있는 From 절이 존재하고, 해당 From 절 안에는 3개의 테이블이 join 으로 연계되어 있다.
select *
from
(
select *
from apple A
left join banana B on A.price = B.price
left join cheese C on A.producer = C.producer
) test
where 1=1
실행 계획 보기
위에서 설명한 것과 같이 DBeaver 내 SQL 편집기를 이용해 작성한 Query 구문에서 우클릭 후 '실행' 탭을 확장하여 '실행 계획 보기' 기능을 사용해보도록 하자. 다음과 같이 확인될 것이다.
실행 계획 설명
- 상기 이미지 중 두번째 이미지가 중요한 부분이다. 작성한 Query 구문이 실행될 때, Optimizer(옵티마이저. '최적화' 라는 뜻을 가진 Optimization 에서 파생된 단어이다. 쉽게 생각하면 Query 조회 빠르고 잘 하도록 해주는 내장 AI 같은 녀석이다. 여러 통계와 사용자 경험을 토대로 최적의 Query 호출을 내부적으로 인계해준다)에서 이루어지는 작동 방식을 나열한 것이다. 우리가 봐야하는 것은 Operation 및 Cost, Cardinality, Bytes 이다.
- Operation : 말 그대로 내부 과정에 대한 내용이다.
- Cost : 해당 과정에 대해 자원의 소모 수치를 뜻한다. 옵티마이저가 계산한 값이다.
- Cardinality : 해당 과정에 대해 읽어들이는 예상 row 수를 뜻한다. 역시나 옵티마이저가 계산한 값이다.
- Bytes : 해당 과정에 대해 읽어들이는 예상 byte 수를 뜻한다. 마찬가지로 옵티마이저가 계산한 값이다.
[ 연관 게시글 ]
SQL Query Tuning [2] 적용(Index)
SQL Query Tuning [3] 적용(Function Index)
SQL Query Tuning [4] 적용(Hash Join)
[ 출처 ]
'SQL > Oracle' 카테고리의 다른 글
| SQL Query Tuning [4] 적용(Hash Join) (0) | 2024.05.17 |
|---|---|
| SQL Query Tuning [3] 적용(Function Index) (0) | 2024.05.17 |
| SQL Query Tuning [2] 적용(Index) (0) | 2024.05.16 |