동일한 쿼리인데 왜 실행시간이 달라질까?

SQL 옵티마이저는 예측 기반으로 움직이는 것으로, 예상 실행 시간을 기준으로 액세스 플랜(실행 계획) 리스트에서 선택을 합니다. 쿼리가 실행될 때의 입력 파라미터는 액세스 플랜의 예상 실행 시간에 영향을 미치게 되므로, 쿼리를 실행할 때마다 액세스 플랜을 다르게 선택할 수 있습니다. 이번 글에서는 쿼리 성능에 영향을 미치는 파라미터의 종류와 쿼리 성능 이슈를 분석하는 방법을 소개하고자 합니다.

 

IBM DB2 for i는 가능한 최소한의 자원을 사용하여 빠르게 쿼리를 실행하도록 하는 알고리즘으로 구성된 비용 기반의 옵티마이저를 사용합니다. 이를 위해서, 쿼리 옵티마이저는 어플리케이션, 인프라스트럭쳐, 데이터 (SQL Index, DDS-Logical File, SQL Statistics 도 포함하여) 로부터 수집된 정보를 사용하여 액세스 플랜을 작성합니다. 만일, 정보가 부족하다면, 옵티마이저는 디폴트 필터 요소 (default guess)라고 하는 것을 참조합니다. 정보는 있지만, 정확하지 않다면, 액세스 플랜의 성능이 불안정해질 것입니다.

 

IBM i는 쿼리를 최적화하는 순간에, 어떤 정보가 있는지를 먼저 검토합니다. 정보 중 일부가 실행할 때마다 달라진다면, 성능 또한 달라질 수 있습니다. 또한, 정보가 사라지거나 불충분할 때도, 액세스 플랜은 이전에 실행했을 때와 달라질 수 있습니다.

 

여러 가지 요인이 좋은 성능을 일관되게 유지하는 데 영향을 미칠 수 있습니다. 최적의 성능을 구현하는 방법은 아래 관련 요소들을 검토하고 쿼리의 비즈니스 요구사항과 기술적인 요구사항에 맞추어 정확하게 설정하는 것입니다.

 

 

쿼리 성능에 영향을 미치는 요인들 

• View/Index/Temporary Index 유무
• PTF 레벨, 서버 구성과 속성값 차이
• 서버의 Version/Release/Modification level 차이
• 서버 사양
• Job & Query 속성값
• SQL 요청의 품질
• Cursor sensitivity
• Cursor reuse
• Symmetric Multi-Processing 사용 유무
• 데이터베이스 설계 / 데이터 모델
• 테이블 크기 / 레코드 건수 / 삭제된 레코드 건수
• Optimization Goal
• Fair share of memory
• 백그라운드 통계 작업 진행 여부
• Data skew
• SQL 환경 차이

 

진단 방법

위에 언급한 많은 요인들 대부분은 Visual Explain 에서 확인할 수 있습니다. 쿼리가 빨리 실행되었을 때의 데이터와 느리게 실행되었을 때의 데이터를 비교해보고, 위의 요인들 중 다른 부분이 있는지 먼저 확인해보십시오.

만약 두 Visual Explain의 요인이 동일하다면, 위 요인들의 정확도를 조사해보십시오. 예를 들면, JOIN 조건의 쿼리를 위한 Index가 없는 경우, 옵티마이저가 가지고 있는 정보의 품질이 제한적이므로, 옵티마이저는 디폴트 값에 의존하게 되고, 이는 예측이 어렵고 일관되지 못한 런타임을 일으킬 수 있게 됩니다. JOIN 조건에 대한 옵티마이저 정보를 제공할 수 있는 Index를 만들고 나면, 옵티마이저는 가장 효율적인 JOIN 순서를 사용하는 쿼리를 일관되게 구현할 수 있게 됩니다.

 

오리지날 액세스 플랜이 있는데도 새로운 액세스 플랜으로 교체되는 경우가 발생한다면, 소위 Query reoptimization이 발생한다면, Dynamic Replan Reason Code 를 검토해 보십시오. 

 

아래 예시는 Parallel Processing Degree (QQRYDEGREE system value) 값의 차이로 인해 옵티마이저가 새로운 플랜을 생성했다는 내용이 있습니다. 만약 Parallel Processing Degree가 *NONE으로 설정되어 있었다면, 쿼리가 느리게 실행된 이유는 더이상 여러 프로세스를 사용할 수 없어졌기 때문에 느리게 실행되었을 수 있습니다. 

 

 

 

[참고] Dynamic replan이 발생하는 이유

Dynamic replan (access plan rebuilt) 

• NA - No replan.
• NR - SQL QDT rebuilt for new release.
• A1 - A table or member is not the same object as the one referenced when the access plan was last built. Some reasons why they might be different are: 
  • Object was deleted and recreated.
  • Object was saved and restored.
  • Library list was changed.
  • Object was renamed.
  • Object was moved.
  • Object was overridden to a different object.
  • This run is the first run of this query after the object containing the query has been restored.
  • Mask or permission attributes changed for the object.
• A2 - Access plan was built to use a reusable Open Data Path (ODP) and the optimizer chose to use a nonreusable ODP for this call.
• A3 - Access plan was built to use a non-reusable Open Data Path (ODP) and the optimizer chose to use a reusable ODP for this call.
• A4 - Either the number of rows in the table member has changed by more than 10% or a selectivity or cardinality statistic has change by more than 25% since the access plan was last built. 
• A5 - A new index exists over one of the tables in the query.
• A6 - An index that was used for this access plan no longer exists or is no longer valid.
• A7 - IBM® i Query requires the access plan to be rebuilt because of system programming changes.
• A8 - The CCSID of the current job is different from the CCSID of the job that last created the access plan.
• A9 - The value of one or more of the following values is different for the current job than it was for the job that last created this access plan: 
  • date format
  • date separator
  • time format
  • time separator
• AA - The sort sequence table specified is different from the sort sequence table that was used when this access plan was created.
• AB - Storage pool changed or DEGREE parameter of CHGQRYA command changed.
• AC - The system feature DB2® Symmetric Multiprocesing has been installed or removed.
• AD - The value of the degree query attribute has changed.
• AE - A view is either being opened by a high-level language or a view is being materialized.
• AF - A user-defined type or user-defined function is not the same object as the one referred to in the access plan; or the SQL Path is not the same as when the access plan was built.
• B0 - The options specified have changed as a result of the query options file.
• B1 - The access plan was generated with a commitment control level that is different in the current job.
• B2 - The access plan was generated with a static cursor answer set size that is different from the previous access plan.
• B3 - The query was reoptimized because this run is the first run of the query after it was prepared. This run is the first run with actual parameter marker values.
• B4 - The query was reoptimized because referential or check constraints have changed. 
• B5 - The query was reoptimized because Materialized query tables have changed.
• B6 - The query was reoptimized because the value of a host variable changed and the access plan is no longer valid.
• B7 - The query was reoptimized because AQP determined that it was beneficial.
• B8 - The query was reoptimized because Expression Evaluator determined that the statement should be reoptimized.