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


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

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

clip_image002[10]

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

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

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

clip_image004

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

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

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

 

clip_image006

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

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

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

clip_image008

<테스트 배치 프로그램>

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

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

clip_image010

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

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

clip_image012

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

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

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

clip_image014

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

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

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

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

clip_image016

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

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

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

clip_image018

<갑자기 느려진 동일 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로 변경되었습니다.

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

clip_image020

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

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

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

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

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

clip_image022

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

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

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

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

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

clip_image024

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

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

clip_image026

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

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

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

clip_image028

<느려 졌던 SQL 수행>

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

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

clip_image030

<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에 대비를 하는 방법을 권합니다.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

[DB Admin] 통계 정보 데이터 갱신 작업 이후 장애 발생”의 36개의 댓글

  • 2018-11-04 12:32 오후
    Permalink

    1 surround sokund plus a powerful performance with
    maximum speed delivered through the power of third generation Intel Core Processors.

    You feel that when youu possessed given your relationship a lot more effort and time it could been employed
    out. Just finished watching Dabangg, Abhinnav Kashyap’s sterling debut performance.

    댓글달기
  • 2019-03-24 4:23 오전
    Permalink

    Heya i am for the first time here. I found this board and I find It really useful
    & it helped me out much. I hope to give something back and help others
    like you aided me.

    댓글달기
  • 2019-03-27 4:28 오후
    Permalink

    We’re a group of volunteers and starting a new scheme in our community.
    Your website provided us with valuable information to work
    on. You’ve done a formidable job and our whole community will
    be grateful to you.

    댓글달기
  • 2019-04-02 9:12 오후
    Permalink

    Great blog right here! Additionally your site a lot up fast!
    What web host are you using? Can I get your associate
    link on your host? I desire my website loaded up as quickly as yours
    lol

    댓글달기
  • 2019-04-25 12:52 오후
    Permalink

    I am actually thankful to the owner of this web site who has shared this wonderful post at at
    this time.

    댓글달기
  • 2019-04-28 11:32 오후
    Permalink

    Write more, thats all I have to say. Literally, it seems as though you relied on the video to
    make your point. You obviously know what youre talking about, why
    waste your intelligence on just posting videos to your weblog when you could be giving us something informative to read?

    댓글달기
  • 2019-05-01 7:49 오전
    Permalink

    When someone writes an post he/she maintains the thought of
    a user in his/her mind that how a user can understand it.

    Thus that’s why this post is perfect. Thanks!

    댓글달기
  • 2019-05-01 6:30 오후
    Permalink

    whoah this weblog is magnificent i really like reading
    your posts. Keep up the great work! You already know, lots
    of persons are looking round for this info, you can help them greatly.

    댓글달기
  • 2019-05-01 6:58 오후
    Permalink

    Hi there, i read your blog from time to time and i
    own a similar one and i was just curious if you get a lot of
    spam comments? If so how do you protect against it, any plugin or anything you can recommend?
    I get so much lately it’s driving me crazy so any help is very much appreciated.

    댓글달기
  • 2019-05-01 7:15 오후
    Permalink

    continuously i used to read smaller articles that also clear their motive, and that is also happening with this post which I am reading now.

    댓글달기
  • 2019-06-12 12:46 오후
    Permalink

    Unquestionably believe that that you stated.

    Your favourite justification appeared to be at the web the easiest thing to take note of.
    I say to you, I certainly get irked while people consider
    worries that they plainly do not know about.
    You controlled to hit the nail upon the highest and
    also outlined out the whole thing without having side effect , folks can take a signal.
    Will likely be back to get more. Thanks

    댓글달기
  • 2019-06-29 10:26 오전
    Permalink

    A person necessarily help to make critically posts I
    might state. This is the first time I frequented your website
    page and thus far? I amazed with the research you made to make this particular submit extraordinary.
    Great activity!

    댓글달기
  • 2019-07-11 7:32 오전
    Permalink

    Thanks for your personal marvelous posting! I actually enjoyed reading it, you might be
    a great author.I will be sure to bookmark your blog and may come back in the foreseeable future.
    I want to encourage continue your great writing, have a nice morning!

    댓글달기
  • 2019-07-15 3:47 오후
    Permalink

    Hola! I’ve been reading your website for a while now and
    finally got the courage to go ahead and give you a shout out from Austin Texas!
    Just wanted to tell you keep up the great work!

    댓글달기
  • 2019-07-18 7:07 오후
    Permalink

    I’m not sure why but this website is loading very
    slow for me. Is anyone else having this problem or is it
    a problem on my end? I’ll check back later and see if the problem still exists.

    댓글달기
  • 2019-07-20 12:37 오전
    Permalink

    We’re a group of volunteers and starting
    a new scheme in our community. Your site offered us with valuable info to work on.
    You have done an impressive job and our entire community will be grateful to
    you.

    댓글달기
  • 2019-07-20 10:16 오후
    Permalink

    Hi great blog! Does running a blog similar to this require a large amount of work?
    I have very little understanding of computer programming
    but I had been hoping to start my own blog soon.
    Anyway, should you have any suggestions or tips for new blog owners please share.
    I understand this is off topic but I just had to ask.

    Cheers!

    댓글달기
  • 2019-07-22 9:49 오전
    Permalink

    Its such as you learn my mind! You seem to understand a lot approximately
    this, like you wrote the ebook in it or something. I believe that you simply could do with
    some p.c. to pressure the message house a bit, but other than that, this is great blog.
    An excellent read. I will definitely be back.

    댓글달기
  • 2019-07-23 12:34 오전
    Permalink

    With havin so much content do you ever run into any issues of plagorism or copyright
    infringement? My website has a lot of unique content I’ve
    either created myself or outsourced but it looks
    like a lot of it is popping it up all over the web without my permission. Do you know any ways to
    help protect against content from being stolen? I’d truly appreciate
    it. natalielise plenty of fish

    댓글달기
  • 2019-07-30 11:43 오후
    Permalink

    Hello my family member! I want to say that this post is awesome, nice written and include almost
    all important infos. I would like to look more posts like this
    .

    댓글달기
  • 2019-07-31 8:54 오후
    Permalink

    I was very pleased to uncover this website. I need to to
    thank you for ones time for this particularly wonderful read!!
    I definitely loved every part of it and i also have you saved as
    a favorite to look at new things in your blog.

    댓글달기
  • 2019-07-31 11:52 오후
    Permalink

    Asking questions are actually good thing if you are not understanding anything totally, but this piece of
    writing presents nice understanding yet.

    댓글달기
  • 2019-08-02 3:46 오전
    Permalink

    It is actually a great and helpful piece
    of information. I’m satisfied that you shared this helpful information with us.

    Please stay us informed like this. Thanks for sharing.

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다