[DB Admin] 통계 정보 데이터 갱신 작업 이후 장애 발생

오라클DB 내에 테이블의 정보. 데이타의 정보를 가지고 있는 것이 통계 정보라고 합니다. 통계 정보 갱신은 DBA들이 많이 하는 작업입니다. 이 정보가 갱신 되었을 때 발생 할 수 있는 장애에 대해서 알아보고 대체 방법이 어떻게 되는지 살펴보겠습니다.

테스트용 테이블과 인덱스를 생성합니다.

<테스트 테이블,인덱스 생성 후 통계 정보 생성>

통계 정보 갱신 시 발생 할 수 있는 SQL 성능 저하의 테스트를 위한 테이블과 인덱스를 만들었습니다.

다음은 일반적으로 사용하는 어플리케이션의 SQL을 테스트 하겠습니다.

<어플리케이션이 자주 사용하는 SQL>

어플리케이션에서 사용하는 SQL 입니다. 0.02초가 걸리고 있습니다. 특정 시간에 생성된 데이타의 NOT NULL 값의 건수를 확인하는 쿼리 입니다.

해당 쿼리의 실행 플랜을 살펴 보겠습니다.

 

<자주 사용하는 SQL의 실행 PLAN>

SQL PLAN의 순서를 보면 REGDATE 컬럼에 생성한 인덱스인 STAT_TEST_N2를 가장 먼저 Range Scan 하는 Plan으로 수행 되며 1건의 Rows를 만들기 위해 읽는 횟수도 물리적인 디스크 블록 접근 횟수 3번 과 변경된 버퍼 블록 수 7번으로 매우 양호하고 좋은 SQL 로 풀리고 있습니다.

이 DB에는 Batch 프로그램이 수행되고 있습니다. 이 배치의 순서는 다음과 같습니다.

<테스트 배치 프로그램>

배치 프로그램은 매일 STAT_TEST 테이블을 백업 받고 BIRTH 컬럼을 NULL로 치환 후 프로그램을 돌리고 종료 되면 STAT_TEST 테이블을 원복 시키는 순서의 프로그램 입니다.

이제 이 배치 프로그램을 실행 시킵니다.

<테스트 배치 프로그램 실행 중간 단계>

배치 프로그램을 실행 시키는 도중에 통계 정보 갱신 작업을 합니다.

<테스트 배치 프로그램 실행 도중 통계 정보 갱신>

BIRTH 컬럼이 Null로 치환이 완료 된 다음에 아래의 통계 정보 갱신 작업이 수행 되었습니다. 통계 정보 갱신을 On-line 중에 다량의 데이터가 변경 되는 도중에 갱신 한다고 보면 됩니다.

그 이후 배치 프로그램은 수행이 끝까지 완료 되었고, 데이터는 원복 되었습니다.

<테스트 배치 프로그램 실행 완료>

BIRTH 컬럼이 Null로 치환이 완료 된 다음에 아래의 통계 정보 갱신 작업이 수행 되었습니다. 통계 정보 갱신을 On-line 중에 다량의 데이터가 변경 되는 도중에 갱신 한다고 보면 됩니다.

그 이후 배치 프로그램은 수행이 끝까지 완료 되었고, 데이터는 원복 되었습니다.

이제 배치 프로그램 테스트 이전에 자주 사용하는 쿼리를 수행 시켜 봅니다.

<자주 사용하는 SQL문 재 실행>

0.02초 수행 되었던 SQL 이 9분 가량 수행 되었습니다. 똑 같은 SQL의 수행시간이 수 백배 느려졌습니다.

서비스가 느려진 장애가 발생한 상황 입니다. 왜 같은 SQL 이 느려진 걸까요? PLAN을 살펴 보겠습니다.

<갑자기 느려진 동일 SQL문의 수행 PLAN>

SQL PLAN의 순서가 REGDATE 컬럼에 생성한 인덱스인 STAT_TEST_N2를 가장 먼저 Range Scan 했던 Plan이 바뀌었습니다. BIRTH컬럼에 생성된 인덱스 STAT_TEST_N1를 가장 먼저 Full Scan 하고 있습니다. 그리고 1건의 Rows를 만들기 위해 읽는 횟수도 물리적인 디스크 블록 접근 횟수 3번 과 변경된 버퍼 블록 수 7번으로 매우 양호하고 좋았던 SQL이 1건의 Rows를 만들기 위해 읽는 횟수도 물리적인 디스크 블록 접근 횟수 495만번 과 변경된 버퍼 블록 수 568만건을 읽어야만 하는 악성 SQL로 변경되었습니다.

어마어마한 차이를 보이며 변경되었습니다. 왜 이런 현상이 발생하였는지 통계 정보의 데이터를 보겠습니다.

<테스트 테이블의 통계 정보 값 조회>

BIRTH 값의 NULL 통계 정보 값이 높아졌습니다. 즉 배치 프로그램 도중에 NULL 데이터로 치환된 후 통계 정보가 수집되어 현재 데이터 값과 다른 수치를 보이고 있습니다.

왜 옵티마이저는 이런 Plan을 만들었는지 통계 정보 데이터 값과 SQL을 보면 알 수 있습니다.

그건 바로 ‘BIRTH IS NOT NULL’ 조건 때문입니다. 위에 배치 프로그램 수행 중간에 BIRTH 컬럼의 값이 모두 NULL 변경 된 후 값을 넣고 통계 정보를 갱신 한 것을 기억 하실 겁니다.

옵티마이저 또한 위의 SQL을 가지고 Plan을 만들 때 ‘BIRTH IS NOT NULL’ 이 조건을 가지고 통계 정보를 살펴보았고 살펴보니 ‘BIRTH’ 컬럼에 값이 모두 NULL 이다 는 것으로 확인 하였습니다.

< 옵티마이저의 예상 건수와 실제 건수가 큰 차이>

그렇기 때문에 옵티마이저는 INDEX FULL SCAN을 하여 0건을 액세스 하는 것이 최적이므로 판단하였고 1건을 액세스 한 뒤 FROM절의 STAT_TEST 에 ACCESS 하는 것보다 더 낫다고 판단을 한 것입니다.

이처럼 옵티마이저는 통계 정보를 바탕으로 실행계획을 만든다는 것을 확인할 수 있습니다

하지만 배치 프로그램 종료 후 BIRTH 컬럼의 데이터는 NULL에서 원복 되었기 때문에 정확히 매칭되는 통계 정보 값은 아니어서 예상 건수와 실제 건수는 차이를 보이고 있습니다.

그럼 이렇게 통계 정보를 갱신 하고 어플리케이션이 갑자기 느려지는 상황에서 대처 방법은 어떻게 될까요? 지금부터 알아 보겠습니다.

< 테이블의 통계 정보 생성 확인>

통계 정보의 갱신 이력을 조회 합니다. 어느 시간에 통계 정보가 갱신 되었는지 확인 하고 통계 정보를 원복 시킬 시점을 결정 합니다.

<통계 정보 원복 후 통계 정보 원복 확인>

DBMS_STATS 패키지 내에 RESTORE_TABLE_STATS 프로시저를 이용하여 해당 테이블의 통계 정보를 원복 시킵니다. 원복 시킨 이후 해당 테이블의 통계 정보 원복을 확인하고, 문제가 되었던 컬럼의 통계 정보 데이터를 조회합니다.

이제 문제가 되었던 느려 졌던 SQL문을 수행해 봅니다.

<느려 졌던 SQL 수행>

9분 정도 걸렷던 SQL문이 0.05초로 수행 시간이 다시 좋아졌습니다.

이제 해당 SQL의 SQL 수행 PLAN도 확인해 봅니다.

<SQL PLAN 확인>

SQL PLAN의 순서를 보면 REGDATE 컬럼에 생성한 인덱스인 STAT_TEST_N2를 가장 먼저 Range Scan 하는 Plan으로 수행 되며 1건의 Rows를 만들기 위해 읽는 횟수도 물리적인 디스크 블록 접근 횟수 4번 과 변경된 버퍼 블록 수 7번으로 매우 양호하고 좋은 SQL 로 다시 수행되고 있습니다.

지금까지 통계 정보 갱신으로 인한 서비스 응답시간 저하 장애에 대해서 알아 보았습니다. 본 장의 핵심은 데이터데 통계정보 값이 다를 경우 문제가 될 수 있는 점을 테스트를 통해 알아 보았습니다.

통계 정보 갱신은 SQL PLAN의 95%의 성능 효과 장점과 5%의 성능 저하 Risk를 가지고 있다고 알려져 있습니다.

따라서 저자 또한 통계 정보는 DB내의 데이터와 맞도록 갱신을 추천합니다.

TIP. 가장 추천하는 안전한 통계 정보 갱신 방법은 데이터 변경이 없을 때 통계 정보 갱신을 하고 통계 정보 갱신 후 DBMS 모니터링 / 서비스 모니터링 / 통계 정보 원복 준비 등 통계 정보 갱신에 따른 SQL PLAN 변경으로 인한 어플리케이션 서비스의 성능 저하 RISK에 대비를 하는 방법을 권합니다.