쿼리 성능 최적화를 위한 팁 (ft. 통계정보 사용이 어려울 때)

효과적인 최적화는 선택된 데이터에 대한 정확한 정보에 의존하지만, 간혹 옵티마이저가 본질적으로 분석하기 어려운 상황이 있다. 다른 통계 데이터를 사용할 수 없는 경우 SELECTIVITY 절을 잘 사용하면 옵티마이저가 쿼리에 대한 최상의 액세스 계획을 만드는 데 필요한 정보를 제공할 수 있습니다.

 

Selectivity 에 대해 잠깐 다시 정리하고 넘어가자면, Selectivity는 선택도, 분포도 등의 이름으로 사용되기도 하는데, 대상 레코드에서 Result Set으로 선택되는 비중을 의미합니다. 

전체 대상 레코드가 10개인데, 쿼리 결과 1개의 레코드가 선택되는 조건이라면, Selectivity는 1/10 인 0.1이 됩니다. 

 

 

옵티마이저는 쿼리가 선택할 결과 레코드의 수를 평가하고 추정하기 위해 다양한 기술을 사용합니다. 이 정보는 Key Range Estimate, Column 통계 또는 데이터의 카디널리티에 기초한 추론에서 얻을 수 있습니다. 그러나 때때로 옵티마이저가 데이터에 술어를 어떻게 적용할지 확인할 방법이 없는 경우가 있습니다. 이러한 경우에는 옵티마이저에 필요한 추가 정보를 제공하기 위해 SELECTIVITY 절을 추가할 수 있습니다.

 

SELECTIVITY 절을 사용하여 부정확한 추정치를 정확하게 알려주는 방법

통계가 불완전한 경우에 해당하는 일반적인 유형 중 하나는 술어에 사용되기 전에 사용자 정의 함수나 내장 함수에 의해 변환되는 데이터가 있습니다. 두 경우 모두 함수에 대한 무언가가  옵티마이저가 변환이 데이터에 미치는 영향에 대한 추론을 할 수 없도록 방해합니다.

 

이런 경우 다음과 같이 SELECTIVITY 절을 추가하여 쿼리를 변경해 보십시오:

SELECT EXCHANGE, S.SYMBOL, NAME, CURRENT_POSITION 
	FROM SECURITIES S INNER JOIN POSITIONS P ON S.SYMBOL = P.SYMBOL 
	WHERE RISK_SCORE(EXCHANGE, S.SYMBOL) > .8 
		AND EXCHANGE = 'NYSE' 
		AND CURRENT_POSITION > 1000 ORDER BY EXCHANGE, S.SYMBOL

이 예문에서 옵티마이저는 RISK_SCORE 함수의 내부 로직을 모르기 때문에 디폴트값인 33% 가 RISK_SCORE(EXCHANCE, SYMOR) > .8 조건에 충족한다고 가정합니다. 이 가정이 정확하지 않으면 옵티마이저가 부적절한 쿼리 계획을 선택하게 될 수 있습니다. 이를 해결하기 위해 RISK_SCORE 함수의 동작을 이해하고 테이블에 있는 레코드의 1%만이 조건에 부합할 수 있다는 것을 알고 있는 사용자는 WHERE 절을 다음과 같이 다시 쓸 수 있습니다:

	WHERE RISK_SCORE(EXCHANGE, S.SYMBOL) > .8 SELECTIVITY .01 
		AND EXCHANGE = 'NYSE' 
		AND CURRENT_POSITION > 10000

 

정보가 누락되는 또 다른 일반적인 원인은 쿼리가 최근에 테이블에 추가된 레코드만을 선택한 경우입니다. 이러한 레코드는 통계 관리자에 의해 자동으로 유지 관리되는 통계 정보에 아직 반영되지 않을 수 있으므로 옵티마이저는 쿼리가 선택할 레코드가 없다고 믿게 됩니다. 이러한 경우에도 위의 예와 같이, 해결책은 술어에 의해 선택된 행의 백분율을 정확하게 나타내는 SELECTIVITY 절을 추가하는 것입니다.

 

SELECTIVITY를 사용하는 것이 쿼리에 도움이 될 수 있는가를 결정하려면 Visual Explain을 사용하여 Estimated rows selected and query join info 제목 아래에 있는 Percent Selectivity 값의 정확도를 확인하십시오. 대부분의 경우 올바른Selectivity에 대한 합리적인 결과가 나올 것입니다. 추가 정보가 필요한 경우에는 Run and Explain을 사용하거나Plan Cache에 캐시된 Plan에 대해 Visual Explain을 사용하십시오. 이 방법은 각 오퍼레이션에서 처리되는 실제 레코드 수에 대한 정보를 제공합니다. 이러한 값은 Actual Rows Selected Per Plan Step Iteration을 제공하며,Estimated Rows Selected Per Step Iteration과 비교할 수 있습니다. 오퍼레이션이 논리적으로 결합된 여러 술어를 처리하는 경우도 있는데, SELECTIVITY는 개별 술어에 적용되므로 오퍼레이션의 전체 백분율 Selectivity에 대한 영향을 예측하기 어려울 수 있습니다. 일반적으로, SELECTIVITY를 추가하는 효과는 연산이 단 하나의 단순한 술어(예: UDF)만을 가질 때 가장 쉽게 예측하고 이해할 수 있습니다.