<오라클 성능 고도화 원리와 해법1> Ch03-10 V$SQL
오라클 성능 고도화 원리와 해법 1 - Ch03-10 V$SQL
모든 업무가 그렇듯 정해진 시간 내에 가장 효율적인 방법으로 효과성을 높이려면 전략적 접근 방법이 필요하다. 튜닝 프로젝트에 나갈 때마다 시스템에서 사용 중인 SQL 개수를 세어보면, 최소한 수천 개가 넘고, 가장 많을 때는 20,000여 개에 이르는 것을 본 적도 있다. 아무리 뛰어난 튜닝 팀이라 하더라도 그 많은 SQL을 모두 튜닝할 수는 없다. 이런 상황에서 최소 인력으로 튜닝의 효과성을 극대화하려면 어떻게 해야 할까?
그림 3-10은 모 통신회사에서 자체 조사한 자료인데, 다른 시스템도 이와 크게 다르지 않을 것이다. 조사 결과에 의하면, 전체 애플리케이션 물량 중 상위 2%에 해당하는 프로그램이 전체 시스템 부하의 70%를 차지한다. 그리고 상위 14%가 전체 부하량의 90%를 차지하는 것을 볼 수 있다.
잘 알려진 파레토 최적의 법칙(Pareto’s Law) 또는 리처드 코치의 80/20 법칙은 튜닝 대상을 선정하는 데 있어서도 똑같이 적용할 수 있다. 수천, 수만 개의 SQL이 있더라도 그 중 대부분은 사용되지 않거나 가끔 사용되는 것들이다. 주기적으로 사용되는 상위 10% 이내의 프로그램만 집중적으로 튜닝하더라도 시스템 안정화 및 고도화를 이룰 수 있는 이유가 여기에 있다.
본 절에서 설명하려는 v$sql은 개별 SQL 커서의 수행 통계를 분석할 목적으로 많이 활용되지만, 집중 튜닝이 필요한 대상 SQL을 선정하는 데 활용할 수 있는 매우 유용한 도구다. 그뿐만 아니라 튜닝 전후 성능 향상도를 비교할 목적으로 통계를 내는 데도 활용할 수 있다.
v$sql은 라이브러리 캐시에 캐싱돼 있는 각 Child 커서에 대한 수행 통계를 보여준다. 그리고 v$sqlarea는 Parent 커서에 대한 수행 통계를 나타내며, 많은 컬럼이 v$sql을 group by 해 구한 값이다.
v$sql에 보이는 통계치들도 다른 동적 성능 뷰처럼 누적값이다. 따라서 보이는 수치를 그대로 놓고 판단하는 것은 별 의미가 없다. SQL 수행 횟수로 나눈 평균값, 즉 SQL 한 번 수행당 얼마나 많은 일량과 시간을 소비하는지를 계산해야 의미 있는 분석이 가능하다.
그림 3-11은 어떤 병원 관련 기관에서 위 쿼리 수행 결과를 그대로 표로 옮겨서 분석한 내용이다.
세 번째 컬럼 ‘SQL개수(Unique)’는 SQL 문자열 중 선행 100개 문자가 같으면 동일 SQL인 것으로 간주하고 집계한 것이다. 같은 SQL인데도 바인드 변수를 사용하지 않으면 Literal 상수값 별로 오라클이 다른 sql_id를 부여해 SQL 개수가 무수히 많은 것으로 집계되는 오류를 보완하려는 것이다. 프로젝트마다 대개 SQL을 식별할 목적으로 SELECT, INSERT, UPDATE, DELETE 키워드 바로 뒤에 주석으로 고유한 SQL 식별자를 적어놓기 때문에 선행 100개 문자가 같으면 동일 SQL로 간주하는데에 큰 무리가 없다.
위 통계 수치에 한 번 수행되었다가 금방 캐시에서 밀려난 쿼리들은 제외되므로 100% 정확하다고 할 수는 없지만 튜닝을 위한 의사결정 시 매우 유용하고 활용 가치가 높은 정보를 제공한다. 예를 들어, 위 표에 집계된 결과를 보면 ‘진료 지원’ 업무에서 라이브러리 캐시에 로드된 총 SQL 개수가 8,680개인데, Unique하게는 1,822개이므로 바인드 변수를 사용하지 않아 각각 하드 파싱을 발생시키며 캐시에 로드된 SQL 비중이 매우 높은 것을 알 수 있다. SQL이 공유될 수 있도록 바인드 변수를 사용하는 방식으로 프로그램을 수정할 필요가 있다.
그리고 ‘공통’ 업무를 보면, 한 번 수행할 때의 평균 논리적 I/O가 11,688개로 다른 업무에 대해 매우 높게 나타나고 있다. 논리적 I/O가 많다 보니 디스크 I/O도 많고 당연히 쿼리 평균 소요 시간도 가장 높게 나타나고 있다. 게다가 SQL 개수는 가장 적지만 수행 횟수는 가장 많다. 따라서 가장 먼저 시급하게 튜닝해야 할 대상 업무로 판단할 수 있다.
‘Colored SQL’이라고 명명된 기능으로서, 위 기준에 의해 Top SQL에 포함되지 않더라도 사용자가 명시적으로 지정한 커서의 수행 통계가 AWR에 주기적으로 수집되도록 마크하는 기능이다. 마치 책을 읽다가 중요한 부분에 형광펜으로 칠해두는 것과 같다.
Colored SQL 목록은 dba_hist_colored_sql 또는 wrm$_colored_sql 뷰를 통해 조회 가능하다.
이처럼 sql_id에 색깔 표시를 해두면 오라클이 AWR 정보를 수집할 때마다 Top SQL 선정 기준과 상관없이 해당 SQL의 수행 통계를 저장한다.