DBA의 정석

DB Admin Knowhow

Oracle DB Admin

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

Byhaisins

1월 27, 2018 , ,

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

By haisins

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

Related Post

Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023 haisins
Oracle DB Admin

Oracle Merge 문

4월 18, 2023 haisins
Oracle DB Admin

IT 스페셜리스트. Oracle Certified Master가 되고 싶다면?

4월 18, 2023 haisins
64 thoughts on “[DB Admin] 통계 정보 데이터 갱신 작업 이후 장애 발생”

  4. 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.

  8. 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.

  9. 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.

  10. 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

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

  12. 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?

  13. 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!

  14. 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.

  15. 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.

  16. 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.

  17. 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

  21. 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!

  22. I got this website from my pal who told me regarding
    this site and at the moment this time I am browsing this web site and reading very informative articles here.

  23. Hello my family member! I want to say that this article is amazing, nice written and come with almost all vital infos.
    I would like to peer more posts like this .

  24. 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!

  25. 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!

  26. 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.

  28. 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.

  29. 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!

  31. 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.

  32. 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

  34. 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
    .

  36. 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.

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

  38. 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.

  39. I’m very happy to find this website. I need to to
    thank you for your time just for this fantastic read!! I definitely savored
    every bit of it and I have you book-marked to check out new things
    on your blog.

  40. If some one wishes to be updated with most up-to-date technologies
    then he must be pay a visit this web site and be up to date everyday.

  41. For the reason that the admin of this web page is working,
    no question very quickly it will be famous, due to its feature contents.

  43. I don’t know whether it’s just me or if everyone else experiencing issues with your website.
    It appears as though some of the written text in your posts are running off the screen. Can someone else please provide
    feedback and let me know if this is happening to them too?

    This might be a issue with my internet browser because I’ve
    had this happen before. Many thanks

  44. I read this piece of writing fully regarding
    the difference of newest and earlier technologies,
    it’s awesome article.

  45. Greetings from Carolina! I’m bored to tears at work so I
    decided to browse your blog on my iphone during lunch break.

    I really like the information you provide here and can’t
    wait to take a look when I get home. I’m amazed at how fast your blog loaded on my cell phone ..
    I’m not even using WIFI, just 3G .. Anyhow, excellent site!

  47. Hi there! This is kind of off topic but I need some help from an established blog.
    Is it very hard to set up your own blog? I’m not very techincal but I can figure things out
    pretty fast. I’m thinking about setting up my own but I’m not sure where to start.
    Do you have any ideas or suggestions? Appreciate it

  48. I do not even know how I ended up here, but I thought
    this post was great. I don’t know who you are but certainly you’re going to a famous blogger if you aren’t
    already 😉 Cheers!

  49. Wow! This blog looks just like my old one! It’s on a entirely different topic but it has
    pretty much the same page layout and design. Excellent
    choice of colors!

  51. Heya i’m for the first time here. I came across this board and I in finding It truly
    helpful & it helped me out a lot. I’m hoping to offer one thing again and help others like you helped me.

  52. I think this is one of the most significant info for me.
    And i’m glad reading your article. But wanna remark on some general
    things, The website style is ideal, the articles is really great
    : D. Good job, cheers

  53. Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest
    twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience
    with something like this. Please let me know if you
    run into anything. I truly enjoy reading your blog and I look
    forward to your new updates.

  54. This is a good tip particularly to those new to the blogosphere.
    Short but very precise info… Appreciate your sharing this one.
    A must read article!

  55. Wonderful beat ! I wish to apprentice at the same time as you amend your site,
    how could i subscribe for a blog web site? The account aided me a
    applicable deal. I were a little bit familiar of this your broadcast provided shiny clear idea

  56. Pretty nice post. I just stumbled upon your weblog and wished to
    say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your rss feed and I hope you write
    again very soon!

  59. I’m really enjoying the theme/design of your web site. Do
    you ever run into any internet browser compatibility problems?
    A small number of my blog readers have complained about my
    blog not operating correctly in Explorer but looks great in Safari.
    Do you have any suggestions to help fix this problem?

  60. Hi, I read your blogs like every week. Your humoristic
    style is witty, keep doing what you’re doing!

  61. Thanks for some other magnificent post. The place else may anyone get that kind of info in such an ideal way
    of writing? I have a presentation subsequent week, and I am at the search for
    such information.

  63. Great post. I was checking continuously this weblog and I
    am inspired! Very helpful info specially the final
    part 🙂 I maintain such information much. I used to be looking for this certain information for
    a long time. Thank you and good luck.

  64. Greetings! Very useful advice in this particular
    post! It is the little changes that make the largest changes.
    Many thanks for sharing!

Comments are closed.

You missed

Oracle News

[NEWS] 오라클, ‘오라클 데이터베이스 23c 개발자용 무료 버전’ 공개

5월 8, 2023
Oracle DB Admin

Oracle Block change tracking 기능

5월 8, 2023
Oracle Up,Mig

DB Migration Using TTS

4월 20, 2023
DB 설치 & 구축

Windows 용 Oracle Database 19c 설치 필요 조건

4월 19, 2023