오라클 레벨업 - 맛보기 by 한빛미디어

최고의 전문가 그룹이 알려주는 오라클 실전 100가지 기술

ORACLE

오라클 레벨업 최고의 전문가 그룹이 알려주는 오라클 실전 100가지 기술 초판발행 2016년 12월 20일 지은이 스즈키 겐고, 다마오키 다케히로, 시오바라 고타, 고바야시 오사무, 오모리 신지, 우치무라 도모아키 옮긴이 윤인성, 조은옥 / 펴낸이 김태헌 펴낸곳 한빛미디어 (주) / 주소 서울시 마포구 양화로 7길 83 한빛미디어(주) IT출판부 전화 02 – 325 –5544 / 팩스 02 – 336 – 7124 등록 1999년 6월 24일 제10 – 1779호 / ISBN 978-89-6848-468-1 93000 총괄 전태호 / 책임편집 김창수 / 기획·편집 박지영 디자인 표지 더 그라프 내지 김연정 / 조판 방유선 영업 김형진, 김진불, 조유미 / 마케팅 박상용, 송경석, 변지영 / 제작 박성우, 김정우 이 책에 대한 의견이나 오탈자 및 잘못된 내용에 대한 수정 정보는 한빛미디어(주)의 홈페이지나 아래 이메일로 알려주십시오. 잘못된 책은 구입하신 서점에서 교환해 드립니다. 책값은 뒤표지에 표시되어 있습니다. 한빛미디어 홈페이지 www.hanbit.co.kr / 이메일 ask@hanbit.co.kr

Oracle NO GENBA WO KORITSUKA SURU 100 NO WAZA by Kengo Suzuki, Takehiro Tamaoki, Kota Shiobara, Osamu Kobayashi, Shinji Omori, Tomoaki Uchimura Copyright © 2015 Kengo Suzuki, Takehiro Tamaoki, Kota Shiobara, Osamu Kobayashi, Shinji Omori, Tomoaki Uchimura All rights reserved. Original Japanese edition published by Gijyutsu-Hyoron Co., Ltd., Tokyo This Korean language edition published by arrangement with Gijyutsu-Hyoron Co., Ltd., Tokyo in care of Tuttle-Mori Agency, Inc., Tokyo through Botong Agency, Seoul 이 책의 한국어판 저작권은 Botong Agency를 통한 저작권자와의 독점 계약으로 한빛미디어가 소유합니다. 신 저작권법에 의하여 한국 내에서 보호를 받는 저작물이므로 무단전재와 무단복제를 금합니다.

지금 하지 않으면 할 수 없는 일이 있습니다. 책으로 펴내고 싶은 아이디어나 원고를 메일(writer@hanbit.co.kr)로 보내주세요. 한빛미디어(주)는 여러분의 소중한 경험과 지식을 기다리고 있습니다.

최고의 전문가 그룹이 알려주는 오라클 실전 100가지 기술

ORACLE 스즈키 겐고 외 5인 지음 윤인성, 조은옥 옮김

지은이 소개

지은이 스즈키 겐고 鈴木健吾 (Senior Principal Consultant)

지난 2000년 오라클에 자리를 잡은 뒤 처음에는 고객지원 팀에서 문제 해결에 몰 두했다. 미국 플로리다주 지원센터에서는 일본 심야 시간대의 긴급 트러블에 대 응했다. 이후 오라클 교육센터에서 강사로 활동하며 RAC, 튜닝 등 데이터베이스 관련 기술 교육을 담당했다. 이때 ORACLE MASTER Platinum 트레이닝 코스 를 만들었으며 기술자 육성을 담당했다. 현재는 컨설턴트로 고객의 비즈니스가 성공할 수 있게 프로젝트를 돕고 있다. 바 쁘지만 즐거운 나날을 보내고 있으며 달리기를 좋아한다. 올해 목표는 일본 후지 산 등산을 제한 시간 내에 완주하는 것이다.

지은이 다마오키 다케히로 玉置雄大 (Staff Consultant)

SI 업계에서 일하며 오라클 제품을 활용한 시스템을 만들었다. 이후에 세계 1위 데이터베이스 업체인 오라클에서 더 큰 가치를 창출하고자 오라클로 이직했다. 주로 Exadata, Oracle Database, Grid Infrastructure, Enterprise Manager 컨설팅을 담당한다. DBA가 아닌 개발자도 오라클 데이터베이스를 잘 알면 할 수 있는 일의 폭이 넓어질 것으로 여겨 자신의 현장 경험과 반성을 바탕으로 책을 집 필했다. 취미는 포커 해외 원정이며 목표는 세계적인 타이틀 획득이다.

지은이 시오바라 고타 塩原浩太 (Senior Consultant)

오라클 제품 관련 기술자로 일을 시작했다. 오라클 제품 사용자 모임(JPOUG)에 서 만난 오다 게이지(小田圭二)의 “데이터베이스를 더 알고 싶지 않나?”라는 말 에 넘어가 일본오라클에 입사했다. 성능 분석, 마이그레이션, DB 운용, 지원 PJ 등을 담당하면서 긴급 트러블 대응도 하고 있다. 고객에게 힘이 될 수 있게 밤낮 으로 열심히 일하고 있다. 다른 사람들이 데이터베이스 관련 일을 하다가 곤란한

지은이 모두 (주)일본오라클 기술컨설팅 통괄본부 소속입니다.

문제가 생겼을 때 해결할 수 있도록 책 집필에 참여했다. 좌우명은 ‘격물치지(格物致知)’, 취미는 마라톤이다. 철인삼종경기도 시작할 예정이다. 최근 관심사는 DB 통합과 클라우드다.

지은이 고바야시 오사무 小林修 (Senior Principal Consultant)

오라클 경력 20년 차의 컨설턴트. 다양한 대규모의 미션 크리티컬 시스템에 매 진하고 있다. 이전에는 인사이트 테크놀로지에서 오라클과 관련된 다양한 테 스트를 하며 오라클 아키텍처를 이해했다. 일본오라클에서 지원하는 메일 매 거진의 창설 멤버다. 음악도 좋아해서 데이터베이스에 대한 고뇌를 ‘Rollback Segment’, ‘Data Guard’라는 이름의 음악으로 풀어내기도 했다. 최근에는 Exadata/GoldenGate와 관련된 일을 담당하고 있다. 매일 지식과 컨설팅 능력, 현장 능력을 키워 고객에게 보탬이 되고자 노력하고 있다. 취미는 배구다. 40대 중반의 어깨에 문제가 생기는 것은 아닐까 고민하면서도, 옛날처럼 스파이크를 칠 수 있을 것이라 믿으며 살고 있다.

지은이 오모리 신지 大森慎司 (Principal Consultant)

SI 업계에서 애플리케이션 엔지니어로 종사하며 다양한 업무를 경험했다. 이때 오라클 DB의 DBA, WebLogic 설계/구축 인프라 경험을 쌓고 일본오라클에 입 사했다. 주로 데이터베이스 관련 업무를 중심으로 대규모 미션 크리티컬 시스템 의 설계와 구축, 운용 컨설팅을 담당한다. 평소 ‘오라클 컨설턴트는 오라클 제품 의 전도사’라는 마음가짐으로 생활한다. Oracle Technology Network(OTN) 에 여러 기사를 냈다. 좋은 아빠가 되고자 노력하고 있으며 집필 기간 중 가족의 도움을 고맙게 느끼고 있다.

지은이 소개

지은이 우치무라 도모아키 内村友亮 (Managing Principal Consultant)

고달픈 SI 업계에서 많은 트러블 슈팅 경험을 쌓고 2007년 일본오라클에 입사했 다. DB 아키텍트로서 대규모 미션 크리티컬 시스템의 마이그레이션과 업그레이 드를 담당한다. 다양한 아키텍처를 연구하면서도 경력이란 사실 우연히 만들어 지는 것이라 생각한다. 인프라 요소(미들웨어, OS, 스토리지, 네트워크)와 관련 된 기술을 가진 사람이 되고자 매일 노력하고 있다. 이전에 집필했던 도서를 바 탕으로 이번 오라클 저서를 기획했다. 취미는 집에서 기르는 치와와 ‘고타로’와 놀아주는 것이다.

옮긴이 소개

옮긴이 윤인성

출근하는 게 싫어서 책을 집필/번역하기 시작했다. 현재 직업 특성상 집에서 나 갈 이유가 별로 없다는 것에 굉장히 만족하고 있다. 몇 번째 책인지도 모르겠다. 홍차와 커피를 좋아하며 요리, 음악, 그림, 스컬핑 등이 취미다. 『모던 웹을 위한 JavaScript +jQuery 입문』, 『모던 웹을 위한 Node.js 프로그래 밍』, 『모던 웹 디자인을 위한 HTML5 +CSS3 입문』 등을 저술하였으며, 『모던 웹 사이트 디자인의 정석』, 『유니티 게임 이펙트 입문』(이상 위키북스), 『TopCoder 알고리즘 트레이닝』, 『Nature of Code』(이상 한빛미디어), 『소셜 코딩으로 이끄 는 GitHub 실천 기술』(제이펍) 등을 번역했다.

옮긴이 조은옥

IBM의 디지털 마케터로 근무 중이다. 한양대학교에서 경영학과 홍보학을 전공 하고, 일본 와세다대학 상학부에서 유학했다. 한국마이크로소프트,후지 제록스, 제일기획 등 6개의 IT, 솔루션, 광고 회사에서 기획자로서 경험을 쌓았다. 취미는 연주 활동으로, 오케스트라에서 콘트라베이스를 연주하며 주말을 보내고 있다.

지은이의 말

시스템을 구축하는 프로젝트 현장에서는 데이터베이스 성능 관리나 트러블 슈 팅, 데이터 활용 등과 관련한 다양한 문제가 발생합니다. 독자 여러분도 혹시 작 업 도중에 다음과 같은 문제로 곤란했던 경험이 있었나요? •아무런 변경도 하지 않았는데 데이터베이스 처리 시간이 급격하게 길어졌다. •데이터베이스를 통합했는데, 어떤 시스템 하나가 갑자기 모든 CPU 자원을 독점한다. •성능 테스트 때 실제 상황을 재현해 워크로드를 시뮬레이션하려고 대규모 인원을 투입했다. • 데이터베이스를 활용해 통계 분석을 하고 싶지만, SQL만으로는 분석을 할 수 없을 것 같다.

사실 이러한 문제는 모두 오라클의 자체적인 기능으로 해결할 수 있습니다. 오라 클 데이터베이스는 Oracle 7, 8i, 9i, 10g, 11g, 12c로 바뀌며 계속 진화하고 있 습니다. 그리고 단순한 데이터 처리를 위한 관계형 데이터베이스 관리 시스템에 서 엔터프라이즈 급의 관리, 개발, 운용 지원 기능을 추가하며 데이터베이스 인 프라적인 기능 발전이 이루어져왔습니다. 똑같은 말을 반복하는 셈이지만 오라클 데이터베이스는 풍부한 기능을 갖추고 있 습니다. 이를 활용하면 간단한 설정으로 개발 효율을 높이거나 상황에 맞는 다양 한 선택지를 구현할 수 있고, 시스템의 생명주기 전반을 더욱 견고하게 떠받들 수 있습니다. 오라클의 능력을 최대로 끌어올려주는 비법들인 셈이죠. 하지만 아쉽게도 이 같은 새롭고 편리한 기능을 소개하는 도서는 많지 않습니다. 이러한 오라클의 기능을 알고 있다면 데이터베이스의 능력을 최대로 끌어올려 사용할 수 있겠지요. 필자들은 일본오라클에서 근무하는 컨설턴트로 수많은 시 스템 개발과 운용 현장에서 이러한 기능을 활용해왔습니다. 그래서 이번 책을 집 필하게 되었습니다. 이 책은 독자 여러분에게 추천하고 싶은 기능을 다음과 같은 카테고리로 나누고 정확히 100개의 팁으로 정리했습니다.

•성능 관리 Tips

• 트러블 슈팅 Tips

•아키텍처 Tips

• 개발/운용 Tips

•시스템 테스트 Tips

• 데이터마이닝 Tips

이전 버전의 오라클 데이터베이스를 사용하던 사람과, 오라클 데이터베이스의 기본을 알고 있는 사람이 편리한 기능을 쉽게 살펴볼 수 있도록 설정 절차와 샘 플 스크립트를 상당수 넣었습니다. 각 팁에서는 어떤 상황에서 사용할 수 있고, 어떤 효과가 있는지를 2~3페이지 정도로 간단하게 정리했습니다. 이 책에서 대 응하는 오라클 버전은 11g Release 2와 12c Release 1입니다. 라이선스가 필 요한 경우에는 따로 표시했습니다. 이 책을 읽으며 데이터베이스를 잘 활용할 수 있기를 바랍니다. 마지막으로 이런 집필 기회를 주신 기술평론사의 쓰토(傳) 님, 모리카와(森川) 님 을 비롯해 교정을 도와준 가족에게 감사의 말씀 드립니다. 필자 일동

옮긴이의 말

얼마 전 출간한 『SQL 레벨업』에 이어 이번 『오라클 레벨업』도 번역하게 되었습 니다. 사실 이 책의 난이도는 상당히 높습니다. 『SQL 레벨업』의 내용 정도는 완전히 이 해하고, 오라클 데이터베이스를 어느 정도 사용해본 경험이 있어야 이 책의 내 용을 온전히 따라갈 수 있습니다. 『SQL 레벨업』은 역주를 많이 달았는데요, 이 번 책도 역주를 꽤 많이 달았습니다(추가 설명이 있어도 내용 자체가 어렵기는 합니다). 총 100가지 기술을 소개하는데, 그중에서도 실제로 자신이 관리하는 데이터베이 스의 용도에 맞게 바로 활용할 수 있는 내용은 30~40개 정도로 꼽을 수 있습니 다(장애 대응 팀에서 데이터마이닝 부분을 활용할 필요는 없을 테니까요). 이러 한 점은 염두에 두고 읽어나가시기 바랍니다. 이 책의 구성은 다음과 같습니다. • 1장에서는 성능 관리 내용을 다룹니다. 『SQL 레벨업』의 내용을 조금 심화해서 복습한다고 생 각하면 좋을 것 같습니다. • 2장에서는 트러블 슈팅을 다룹니다. AWR 리포트나 ADDM 리포트를 확인하는 방법을 아는 사람이라면 내용을 따라갈 수 있습니다. AWR 리포트를 보는 방법을 모른다면 시작하기 꽤 어 려운 부분입니다. 개인적으로는 이 책에서 가장 어려운 부분이라고 생각합니다. • 3장과 4장, 5장은 대부분의 데이터베이스 관리자에게 도움 되는 내용이라고 생각합니다. 난이 도가 높지 않으므로 2장이 어렵다면 2장을 건너뛰고 3장과 4장부터 읽기 바랍니다. • 6장은 데이터마이닝을 다룹니다. 관련 분야 종사자에게 특히 유용한 부분이라고 할 수 있겠 군요.

이 책은 6명의 저자가 공동 집필했습니다. 따라서 용어가 조금 섞이는 부분도 있 고 “이렇게 하면 좋다”라고 하고는 잠시 뒤에 “사실 그것보다 이게 좋다”라고 하 기도 합니다. 조금 혼란스러운 느낌도 있지만, 일본오라클 직원들의 다양한 노하

우를 확인할 수 있습니다. 어쨌거나 ‘이러한 문제가 있을 때는 이러한 기능을 사용한다”라는 내용으로 이루 어진 만큼, 해당 문제 자체나 해당 문제를 일으킬 만한 대규모의 데이터베이스가 없으면 실습하기 어려운 경우가 많습니다. 따라서 “도대체 책을 어떻게 활용하는 게 좋을까?”라는 의문이 생길 수 있는데요, 이 책에 나오는 기능들의 이름과 해 당 기능을 활용할 적절한 시기만 알고 있어도 정말 큰 가치가 있을 것입니다. 잘 기억해두었다가 이후 실제로 자신의 업무에서 관련 문제가 발생했을 때 책을 참 고하면 좋을 것 같습니다. 이 책을 번역하는 데 도움을 주신 유우 님께 감사의 말씀 드립니다. 윤인성

CONTENTS

지은이 소개 ......................................................................................................... 4 옮긴이 소개 ........................................................................................................... 7 지은이의 말 ........................................................................................................... 8 옮긴이의 말 ........................................................................................................ 10

1장 성능 관리 Tips 001 실행 계획 이해 ......................................................................................... 20 002 최적의 결합 방법 선택 .............................................................................. 26 003 커버링 인덱스로 접근 블록 수 줄이기 ........................................................ 30 004 NOT IN 구문으로 성능 문제 발생 시 대응 방법 ......................................... 33 005 파티션을 활용해 접근 블록 수 줄이기 ........................................................ 37 006 SQL 병렬 쿼리를 사용해 성능을 빠르게 만드는 방법 ................................. 42 COLUMN

애플리케이션으로 병렬화를 할 때의 주의 사항 ................................... 45

007 대량의 레코드 INSERT를 빠르게 만드는 방법 ........................................... 46 008 SPM을 사용한 실행 계획 관리 ................................................................. 49 COLUMN

SQL 계획 관리란? .......................................................................... 51

009 SPM 아키텍처 이해하기 .......................................................................... 52 COLUMN

SPM 등록 방법 .............................................................................. 55

010 SPM을 사용한 하드 파싱 구조 이해 ......................................................... 56 COLUMN

SIGNATURE와 PLAN_ID ............................................................. 60

011 SPM에 실행 계획 등록하기 ..................................................................... 61 012 SPM이 사용되는지 확인하기 ................................................................... 65 013 SQL을 바꾸지 않고 실행 계획 변경 .......................................................... 69 014 과거의 실행 계획으로 SQL 실행하기 ........................................................ 76 COLUMN

SQL 튜닝 셋(STS) ......................................................................... 84

015 SPM을 다른 데이터베이스로 마이그레이션 ............................................... 85 016 시스템 개발에서의 SPM 도입 포인트 ....................................................... 90

2장 트러블 슈팅 Tips 017 트러블 원인을 빠르게 찾는 테크닉 ............................................................ 96 COLUMN

오라클 오류 내용 확인 방법 .............................................................. 99

018 AWR 이벤트 활용 ................................................................................. 101 019 인터커넥트 바틀넥을 찾아내는 성능 분석 요령 ......................................... 107 020 2개의 AWR 리포트를 효율적으로 비교하는 방법 ..................................... 112 021 자동 성능 진단 기능 ............................................................................... 114 022 느린 SQL의 원인 자동 분석 ................................................................... 117 COLUMN

SQL 튜닝 전에 어드바이저 기능 사용해보기 ................................... 120

023 이전에 실행한 SQL과 실행 계획 추출 : AWR과 V$SQL ............................... 121 024 SQL이 느려 보일 때 확인사항 ................................................................ 128 025 실행 시간이 긴 SQL 조사 ...................................................................... 131 026 ASH를 활용해 현재 또는 짧은 기간 동안의 문제 분석하기 ....................... 135 027 대기 이벤트의 매개변수 필드로 세그먼트 찾기 ......................................... 137 028 물리 I/O 줄이기 - 대기 이벤트 처방(1) ................................................... 140 029 일시 테이블 영역 접근 줄이기 - 대기 이벤트 처방(2) ............................... 143 030 SQL의 모든 동작 정보 추출 ................................................................... 146 COLUMN

특정 SQL 트레이스만 추출하는 방법 .............................................. 149

031 실행 계획이 변화하는 원인 조사 .............................................................. 150 032 SQL 성능 분석 정보의 간단 추출 ........................................................... 153 033 ADRCI를 사용한 서포트 문의 ................................................................ 157

CONTENTS

3장 아키텍처 Tips 034 자동 메모리 관리 기능 ............................................................................ 164 035 자동 공유 메모리 관리 기능의 조정 규칙과 IMMEDIATE 모드 이해 .......... 168 036 공유 풀 구조 이해와 ORA-4031 오류 회피 ........................................... 172 037 공유 풀 설계의 문제 ............................................................................... 175 COLUMN

DEFERRED 모드와 IMMEDIATE 모드 ........................................ 180

038 공유 풀의 효과적인 감시 방법 ................................................................. 181 039 PGA 메모리 증가 막기 .......................................................................... 184 040 PGA가 증가하는 원인과 대책 ................................................................. 188 041 PGA_AGGREGATE_TARGET이 실행 계획에 미치는 영향 ................... 193 042 라지 페이지를 사용하는 대규모 메모리 환경에서의 트러블 대처 ................ 197 043 온라인 인덱스 재구축과 일반 인덱스 재구축 ............................................ 202 044 인덱스 생성 순서가 실행 계획에 미치는 영향 ........................................... 205 045 의도하지 않은 병렬 쿼리 실행 막기 ......................................................... 208 046 삭제 처리를 빠르게 하는 테크닉 .............................................................. 211 047 효과적인 NOLOGGING 오퍼레이션 활용 .............................................. 216 048 주의해야 할 NOLOGGING 오퍼레이션의 부작용 ................................... 220 049 롤백 시간 예측 ....................................................................................... 224 050 커넥션 풀링을 사용할 수 없는 환경에서 신규 접속 빠르게 만들기 .............. 229

4장 개발·운용 관련 Tips 051 SQL* Plus를 사용한 작업 효율화 기술 .................................................... 234 052 SQL* Plus를 사용한 검증 기술 .............................................................. 238

053 SQL* Plus를 사용한 트러블 슈팅 방법 ................................................... 241 054 객체 정의 정보를 확인해서 SQL 구문을 간단하게 작성 ............................ 245 055 간단한 테스트 데이터 생성 방법 .............................................................. 249 056 PL/SQL 바틀넥 조사 ............................................................................ 254 057 Enterprise Manager를 사용한 간단하고 확실한 DB 객체 관리 .............. 258 058 Enterprise Manager를 사용해 정기적인 DB 가동 상태 리포트 생성하기 .. 261 059 Enterprise Manager를 사용해 실시간으로 SQL 실행 상황 감시하기 ..... 265 COLUMN

실시간 SQL 감시의 SQL 실행 계획 길이 제한 ............................... 268

060 데이터베이스 상황을 실시간으로 파악하기 ............................................... 269 061 간단하게 OS 리소스 정보를 추출하는 방법 ............................................. 273 062 오랜 시간 실행되는 SQL을 타임아웃 시키기 ........................................... 277 063 데이터베이스의 OS 리소스 사용 제한 ..................................................... 280 064 데이터베이스의 OS 리소스 사용 제한내용 확인 ....................................... 284 065 데이터베이스에 접속할 수 있는 클라이언트 제한 ...................................... 290 066 애플리케이션 서버와 데이터베이스 서버 사이에서 발생하는 무효 접속 감지 .. 292 COLUMN

무효 접속을 감지하는 새로운 기능 .................................................. 295

067 인덱스 효과를 간단하게 검증하기 ............................................................ 296 068 통계 정보를 조작해서 실제 실행 계획 재현하기 ........................................ 300 069 세그먼트 단편화 해소 효과를 예측 .......................................................... 303 COLUMN

세그먼트 단편화 ............................................................................ 306

070 AUTOTRACE 기능으로 간단하게 SQL 실행 계획 확인하기 ................... 307 071 바틀넥 확인을 위한 SQL 트레이스 추출 .................................................. 310 COLUMN

SQL 트레이스 파일 이름에 원하는 문자열 추가하기 ........................ 312

072 외부 테이블을 사용해 SQL로 csv 파일 다루기 ....................................... 313 073 숨겨진 초기화 매개변수 확인 .................................................................. 316

CONTENTS

074 배치 처리 진행 상황을 로그 출력 ............................................................ 318 075 매뉴얼을 효율적으로 찾는 방법 ............................................................... 321

5장 시스템 테스트 Tips 076 시스템 테스트를 자동화할 때 주의사항 .................................................... 324 077 DB 서버 중심의 시스템 테스트 ............................................................... 328 COLUMN

설치할 때 RAT 옵션을 선택하지 않은 경우 ..................................... 330

078 SQL 성능 테스트 자동화하기 ................................................................. 333 079 SQL 튜닝 셋 생성하기 ........................................................................... 336 080 SQL 튜닝 셋을 테스트 환경으로 마이그레이션하기 .................................. 339 081 SQL 퍼포먼스 비교하기 ......................................................................... 342 082 SQL Tuning Advisor와 연계하기 ......................................................... 346 COLUMN

SQL 프로파일 .............................................................................. 348

083 SPA를 잘 사용하기 위한 테크닉 ............................................................. 349 084 DB Replay로 실제 환경 부하 재현하기 .................................................. 352 085 워크로드 캡처하기 ................................................................................. 356 086 DB Replay에 필요한 전처리 ................................................................. 359 087 워크로드 리플레이하기 ........................................................................... 361 088 리플레이 퍼포먼스 비교 .......................................................................... 364 089 DB Replay 부하 조절하기 .................................................................... 366 090 DB Replay를 잘 사용하기 위한 테크닉 .................................................. 371

6장 데이터마이닝 Tips 091 Oracle R의 특징과 데이터 분석 목적 파악하기 ....................................... 376 COLUMN

R이란? ......................................................................................... 380

092 R로 데이터베이스에 접근하고 통계 분석하기 ........................................... 381 COLUMN

어소시에이션 분석이란? ................................................................. 386

093 데이터베이스에서 R에 접근하기 ............................................................. 387 COLUMN

R 패키지에 대해 ............................................................................ 390

094 R로 스프레드시트에 접근하기 ................................................................. 391 COLUMN

클러스터 분석이란? ....................................................................... 396

095 R을 디버그하는 테크닉과 바틀넥을 발견하는 테크닉 ................................ 399 COLUMN

OS의 strace 명령어 ..................................................................... 403

096 바이트 코드 컴파일로 R 실행 빠르게 만들기 ............................................ 405 COLUMN

바이너리 형식으로 객체 입출력 빠르게 만들기 ................................. 409

097 R의 메모리 사용량 제한하기 ................................................................... 410 098 히트맵으로 발생 빈도 쉽게 파악하기 ....................................................... 416 COLUMN

패키지 설치 방법 ........................................................................... 420

099 3차원 그래프를 사용해 유사도 파악하기 .................................................. 421 COLUMN

과거의 질문 내용을 데이터베이스에 저장하는 예제 스크립트 ............ 427

100 워드클라우드로 데이터 시각화하기 .......................................................... 429 COLUMN

트위터 애플리케이션을 생성하려면? ............................................... 433

INDEX ................................................................................................. 434

장

성능 관리 Tips

001

실행 계획 이해

▒▒ 실행 계획이란? 실행 계획이란 SQL 결과를 생성하기 위해 어떤 방법으로 테이블 데이터에 접근 할지, 어떤 순서로 테이블 데이터에 접근할지 나타내는 것입니다. 예를 들어 종 업원 테이블에서 종업원 번호 1000번의 데이터에 접근하는 경우를 생각해봅시 다. 다음과 같은 2가지 접근 방법을 사용하면 정상적으로 테이블 데이터를 추출 할 수 있습니다. ① 전체 테이블 스캔 테이블 앞부터 뒤까지 차례대로 테이블 데이터 전체를 스캔하고 종업원 번호 1000번을 검색

② 인덱스 스캔 (종업원 번호 필드에 인덱스가 있을 경우) 인덱스로 종업원 번호 1000번을 찾고 테이블 데이 터에 접근 그림 1-1 전체 테이블 스캔

그림 1-2 인덱스 스캔 종업원 번호 필드의 인덱스

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

20 오라클 레벨업

종업원 테이블

종업원 번호 = 1000

이처럼 테이블 데이터에 접근하는 방법을 실행 계획이라고 부릅니다. 2가지 실 행 계획 모두 결과는 같으므로, ‘결과가 정확할 것’이라는 요소만 고려한다면 어 떤 실행 계획으로 테이블 데이터에 접근해도 상관없습니다. 하지만 대부분의 경 우 이러한 2가지 종류의 실행 계획 중에 어떤 것을 사용할지에 따라 SQL 처리 성 능에 차이가 생깁니다.

▒▒ 실행 계획과 테이블 크기에 따른 성능 차이 그럼 실행 계획에 따라 성능에 어떤 차이가 발생할까요? 예를 들어 앞에서 설명했던 종업원 테이블에 수만 명의 종업원 정보가 저장되었 다고 합시다. 이러한 경우 ‘테이블 데이터 전체를 스캔해서 종업원 번호 1000번을 검색’하면 큰 테이블을 스캔하므로 처리 시간이 오래 걸립니다. 반대로 ‘인덱스로 종업원 번호 1000번을 찾고 테이블 데이터에 접근’하면 미리 인덱스라는 구조로 압축된 테이 블에 접근하므로 짧은 처리 시간으로 데이터에 접근할 수 있습니다. 이처럼 실행 계획에 따라 성능에 차이가 생깁니다. 이어서 종업원 수가 적어서 몇 명의 종업원 정보만 테이블에 저장되었다고 합시다. 이러한 경우 ‘테이블 데이터 전체를 스캔해서 종업원 번호 1000번을 검색’하면 테이블이 작으므로 짧은 처리 시간으로 데이터에 접근할 수 있습니다. 반대로 ‘인덱스로 종업원 번호 1000번을 찾고 테이블 데이터에 접근’하면 테이블 데이터 뿐만 아니라 인덱스 데이터에도 접근해야 하므로 오버헤드가 발생해 처리 시간 이 길어질 수 있습니다.

1장 _ 성능 관리 Tips 21

표 1-1 테이블 크기와 실행 계획에 따른 처리 시간 비교 테이블 크기

실행 계획에 따른 처리 시간 비교

큰 테이블의

종업원 번호 필드의 인덱스

경우

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

큰 테이블에서 1개의 데이터만 추출하는 경우, 인덱스를 사용하면 처리 시간을 줄일 수 있 습니다. 작은 테이블의 종업원 번호 필드의 인덱스

경우

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

작은 테이블에서 1개의 데이터만 추출하는 경우, 인덱스를 사용하면 접근 블록 수가 많으므로 처리 시간이 상대적으로 길어집니다.

이처럼 테이블의 크기에 따라 최적의 실행 계획이 달라집니다.

▒▒ 실행 계획과 추출하는 데이터양에 따른 성능 차이 이어서 수만 명의 종업원 정보에서 모든 종업원의 정보를 추출하는 경우를 생각 해봅시다.

22 오라클 레벨업

이때 종업원 테이블의 전체 데이터가 필요하므로 ‘테이블 데이터 전체를 스캔’해 야 합니다. 반대로 ‘인덱스로 테이블 데이터에 접근’하더라도 같은 결과를 얻을 수 있겠지만, 데이터마다 반복적으로 인덱스에 접근한 이후 테이블에 접근해야 하 는 오버헤드가 발생합니다. 따라서 처리 시간이 매우 길어집니다. 표 1-2 추출할 데이터양과 실행 계획에 따른 처리 시간 비교 추출할 데이터양

실행 계획에 따른 처리 시간 비교

추출할 데이터양이

종업원 번호 필드의 인덱스

많은 경우

종업원 번호 = 1

종업원 테이블

종업원 번호 = 1 종업원 번호 = 2 종업원 번호 = 3 종업원 번호 = 49,999 종업원 번호 = 50,000 종업원 수 반복

종업원 테이블

종업원 번호 = 1 종업원 번호 = 2 종업원 번호 = 3 종업원 번호 = 49,999 종업원 번호 = 50,000 종업원 수 반복

모든 데이터가 필요한 경우에는 표 데이터 전체를 스캔하는 것이 가장 처리 시간이 짧습니다. 인덱스를 사용하면 레코드 수만큼의 오버헤드가 발생하므로 처리 시간이 길어집니다. 추출할 데이터양이 종업원 번호 필드의 인덱스

적은 경우

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

종업원 테이블

종업원 번호 = 1000

일부 데이터에만 접근하는 경우는 인덱스를 사용하는 편이 처리 시간이 짧습니다.

이처럼 추출하는 데이터양에 따라서도 실행 계획이 달라집니다.

1장 _ 성능 관리 Tips 23

SQL 실행 시점에서의 테이블 크기 또는 추출하는 데이터양에 따라 해당 시점에 서 최적의 실행 계획을 선택합니다. ‘테이블 데이터 전체를 스캔’하는 방법과 ‘인 덱스로 테이블 데이터에 접근’하는 방법 중에 어느 쪽이 더 빠른지는 실행 계획만 으로는 알 수 없습니다. 모든 데이터를 항상 고속으로 처리할 수 있는 만능 실행 계획은 존재하지 않습니다! 최적의 실행 계획이 선택되지 않은 경우의 성능 영향은 특히 데이터양이 큰 대규 모 데이터베이스에서 현저하게 나타납니다.

▒▒ 실행 계획의 구성 요소 실행 계획의 구조는 인덱스의 사용 여부, 즉 접근 경로(Access Path)만으로 이 루어지지 않습니다. 결합을 포함한 복잡한 SQL이라면 실행 계획은 다음과 같은 요소로 구성됩니다. 표 1-3 실행 계획의 구성 요소 실행 계획의 구성 요소

설명

접근 경로

•데이터베이스에서 데이터를 추출하는 경로를 나타냅니다. • 테이블 데이터 전체를 스캔하는 ‘전체 테이블 스캔’과 인덱스로 레코드를 특정하고 테이블 데이터에 접근하는 ‘인덱스 스캔’이 있습니다.

결합 방법

•결합 방법에는 ‘Nested Loops’, ‘Sort Merge’, ‘Hash’ 가 있습니다. •자세한 내용은 이후의 팁에서 살펴보겠습니다.

결합 순서

•여러 개의 테이블을 결합할 때 결합할 테이블 순서를 나타냅니다. • 3개 이상의 테이블을 결합한다면 일단 2개의 테이블을 결합하고, 그 결과를 남은 테이블과 결합합니다. •모든 테이블이 결합해 결과가 생성될 때까지 이 과정을 반복한답니다.

접근 경로뿐만 아니라 결합 방법과 순서도 SQL의 성능을 결정하는 중요한 요소 입니다. 여러 개의 테이블을 결합할 때 처음부터 큰 테이블을 결합해버리면 큰 테 이블의 데이터에 여러 번 접근해야 하므로 처리 시간이 길어집니다. 따라서 더 작 은 테이블 또는 조건으로 크게 압축1되는 테이블부터 접근하는 게 좋습니다. 이와 관련한 내용은 팁 002를 참고해주세요.

1 옮긴이_ ‘크게 압축’이란 조건으로 필터링해서 결과의 수가 적게 나오는 것을 의미합니다.

24 오라클 레벨업

실행 계획은 이러한 ‘접근 경로’, ‘결합 방법’, ‘결합 순서’를 조합해 구성되므로 SQL 구문이 복잡할수록 한 개의 SQL 구문을 실행하기 위해 선택할 수 있는 ‘실 행 계획’의 종류도 매우 많아집니다. 이러한 여러 종류의 ‘실행 계획’ 중에서 무엇 을 사용할지가 성능에 매우 큰 영향을 미칩니다. 효율 높은 최적의 실행 계획으 로 SQL 구문을 실행한다면 좋은 성능을 낼 수 있지만, 그렇지 않다면 성능이 좋 지 않을 것입니다. 좋은 성능을 유지하기 위한 데이터베이스 운용 관리를 다루는 책은 매우 많으므 로, 최적의 실행 계획이 선택되지 않았을 때 사용할 수 있는 편리한 기술들을 소 개하겠습니다.

1장 _ 성능 관리 Tips 25

002

최적의 결합 방법 선택

SQL 실행 중 특히 부하가 높은 작업이 테이블 결합 처리입니다. 따라서 큰 테이 블을 결합할 때 최적의 실행 결합을 선택하게 튜닝하면 성능이 비약적으로 높아 집니다.

▒▒ 테이블 결합 방법의 적절한 사용 SQL 결합 방법을 튜닝할 때는 Nested Loops 결합과 Hash 결합을 많이 사용합 니다. 대략적 방침으로는 온라인 화면처럼 소수의 결과를 리턴할 때는 Nested Loops 결합, 배치 처리 또는 장부 출력처럼 대량의 결과를 리턴할 때는 Hash 결 합을 사용하면 성능이 개선됩니다. 표 1-4 결합의 종류와 장점 결합 이름

처리 내용

장점

Nested Loops 결합

인덱스를 사용해 결합할 레코드를 검색

소수의 결과를 리턴하는 온라인 화면

Hash 결합

결합 키로 해시 테이블을 생성하고, 해당

처음에 해시 테이블을 생성해야 하므

해시 테이블을 기반으로 결합할 레코드

로 테이블 풀 스캔이 발생하지만, 이후

를 검색

에는 고속으로 대량의 레코드를 다룰

처리 등에 적합

수 있으므로 배치 처리 또는 장부 출 력에 적합

▒▒ Nested

Loops 결합

외부 테이블에서 레코드를 페치(Fetch)2 하고, 결합 조건에 일치하는 레코드를 내부 테이블에서 검색해 결합하는 방식입니다.

2 옮긴이_ 페치(Fetch)를 단순하게 설명하면 테이블에서 데이터를 가져오는 것을 의미합니다.

26 오라클 레벨업

그림 1-3 Nested Loops 결합 외부 테이블

1 내부 테이블

외부 테이블과 내부 테이블이라는 용어는 다음 while 반복문을 생각하면 이해 하기 쉽습니다3 . ● 외부 테이블과 내부 테이블의 결합 처리 while { 레코드를 1개 페치 ⇐ 외부에 있는 반복이므로 ‘외부 테이블’

while { 외부 테이블의 결합 조건에 일치하는 레코드를 패치 ⇐ 내부에 있는 반복이므로 ‘내부 테이블’

} }

▒▒ Hash 결합 외부 테이블을 읽어 들이고 결합 키를 기준으로 해시 테이블을 만들어 PGA 영 역에 올리고, 해시에 기반을 두고 내부 테이블을 검색해서 결합하는 방식입니다.

3 옮긴이_내부적으로 반복문을 중첩해서 결합하므로 Nested Loops(중첩 반복) 결합이라고 부르는 것입니다.

1장 _ 성능 관리 Tips 27

그림 1-4 Hash 결합 외부 테이블

해시 테이블

내부 테이블

해시 테이블 생성에 시간이 걸리므로 처음 데이터를 결합할 때는 시간이 꽤 걸립 니다. 하지만 이후의 결합 처리는 메모리의 PGA 영역에서 수행되므로 매우 빨 라집니다. 또한 인덱스를 사용하지 않으므로 버퍼 추출 수를 줄일 수 있습니다. 이러한 처리 특정 때문에 최대 효율(Maximum Throughput)이 요구되는 배치 처리 또는 장부 출력처럼 대량의 레코드를 리턴할 때 적합합니다. 참고로 해시 테이블은 PGA 영역을 사용하므로 주의가 필요합니다. PGA 영역이 부족하면 디스크에 있는 일시 테이블 영역을 사용하므로 성능에 문제가 발생할 수 있습니다 4 . 이러한 때는 초기화 매개변수 pga_aggregate_target으로 PGA 의 크기를 확장하기 바랍니다. 추가로 PGA 크기와 관련된 주의점은 팁 041을 함께 참고해주세요.

▒▒ 결합 방법 확인과 변경 SQL의 실행 계획을 확인해보면 Operation 필드에 결합 방법이 다음과 같 이 나타납니다. 실행 계획을 확인할 때는 AUTOTRACE 기능을 사용합니다. AUTOTRACE 기능을 사용하는 방법은 팁 070을 참고해주세요.

4 옮긴이_ 디스크의 속도가 메모리의 속도보다 훨씬 느리기 때문입니다. 이와 관련한 자세한 내용은 이 책

의 이전 도서인 『SQL 레벨업』(한빛미디어, 2016)을 참고해주세요.

28 오라클 레벨업

● Nested Loops 결합 |

1 |

NESTED LOOPS

1 |

| 10000 |

517K |

7688

(2)| 00:01:33 |

| 10000 |

517K |

(8)| 00:00:01 |

● Hash 결합 |*

1 |

HASH JOIN

결합 방법을 변경하고 싶을 때는 힌트 구를 지정합니다. Nested Loop 결합을 지 정할 때는 USE_NL 힌트 구를, Hash 결합을 지정할 때는 USE_HASH 힌트 구 를 사용합니다. 힌트 구에 지정할 테이블 이름은 FROM 구를 사용합니다. 만약 지정한 별칭이 있다면 별칭을 사용하며, 지정하지 않은 경우에는 테이블 이름을 그대로 사용합 니다. ● Nested Loops 결합 지정 SELECT

/*+ USE_NL (<테이블 별칭1> <테이블 별칭2>…) */

● Hash 결합 지정 SELECT

/*+ USE_HASH (<테이블 별칭1> <테이블 별칭2>…) */

결합 방법의 힌트 구를 지정할 때는 ORDERED 또는 LEADING 힌트 구로 결합 순서도 함께 지정해주세요. 참조 테이블을 결합의 내부 테이블로 사용할 때는 이 러한 힌트 구를 사용합니다.

1장 _ 성능 관리 Tips 29

003

커버링 인덱스로 접근 블록 수 줄이기

매우 많은 필드가 있는 MASTER라는 이름의 테이블이 있습니다 5 . 내부에는 code와 name 필드가 있는데, code를 기반으로 검색해서 name을 추출하려 합 니다. 이때 필요한 요소는 code와 name뿐이지만, 데이터 블록은 레코드의 모든 필드를 포함하므로 다른 필드 데이터도 함께 추출합니다. ● Master 테이블 code

name

price

create_at

update_at

기타

XXX

…

이처럼 일반적인 검색에서는 불필요한 필드까지 추출하므로 무의미한 I/O가 발 생합니다. 이러한 때 필요한 필드만 추출하는 방법이 있다면 최저한의 I/O로 빠 르게 접근할 수 있겠지요6 .

▒▒ 복합 인덱스를 사용해 테이블 접근 생략 인덱스를 사용한 레코드 접근은 인덱스 검색으로 해당 레코드의 물리 주소를 추 출하고, 해당 주소를 기반으로 테이블의 레코드를 추출하는 과정을 거칩니다. 그런데 인덱스에 참조할 필드가 모두 포함된다면, 이후의 부분을 생략하고 정보 추출을 완료할 수 있습니다. 따라서 테이블에 접근할 필요가 없어지죠. 이런 동

5 옮긴이_ 원서에 나오는 설명만으로는 상황을 이해하기 어려울 수 있어 이 책에서는 Master 테이블 샘플 이미지를 임의로 작성해 제공합니다. 6 옮긴이_ 컬럼 지향 데이터베이스와 관련된 내용을 추가로 살펴보면 좋습니다. 이 책의 이전 도서 『SQL 레벨업』(한빛 미디어, 2016)이 있다면 374페이지 ‘인덱스 온리 스캔과 컬럼 지향 데이터베이스’를 참고하기 바랍니다.

30 오라클 레벨업

작을 ‘커버링 인덱스’라고 부릅니다. 따라서 인덱스 접근만으로 결과를 얻을 수 있습니다. 그림 1-5 Hash 결합

추출 조건을 기반으로 인덱스를 검색하고, 레코드가 저장된 물리 주소 추출

인덱스에 포함된 필드의 값으로 결과 리턴

서버 프로세스

인덱스로 추출한 물리 주소를 기반으로 테이블에서 해당 레코드 추출

인덱스

테이블 복합 인덱스에 추출할 필드가 모두 포함되면, 테이블에서 필드값을 추출할 필요가 없으므로 과정을 생략할 수 있음

예를 들어 code와 name을 기반으로 복합 인덱스를 생성하면, code를 기반으로 name을 추출하는 검색 처리를 빠르게 만들 수 있습니다.

▒▒ 커버링 인덱스를 사용한 성능 향상 예 다음과 같이 CODE를 조건으로 NAME 필드를 MASTER 테이블에서 검색하는 쿼리를 생각해봅시다. ● MASTER 테이블 검색 SELECT FROM WHERE

NAME MASTER CODE = ‘검색 조건’

이러한 SQL 쿼리를 실행할 때 필요한 필드는 CODE와 NAME입니다. 검색 조건의 CODE 필드에만 인덱스를 생성한 경우와, CODE 필드와 NAME 필 드에 모두 인덱스를 생성한 경우의 SQL 트레이스를 비교하면 다음과 같습니다.

1장 _ 성능 관리 Tips 31

● CODE 필드에만 인덱스 생성(커버링 인덱스 ×) call

count

------- ------

current

rows

-------- ---------- ---------- ---------- ----------

cpu

elapsed

disk

query

----------

Parse

0.00

Execute

0.00

Fetch

0.00

-------- ---------- ---------- ---------- ----------

----------

------- -----total

0.00

~생략~ Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------1

1 TABLE ACCESS BY INDEX ROWID MASTER (cr=3 pr=0 pw=0 time=25 us cost=2 size=9 card=1)

INDEX RANGE SCAN MASTER_IDX1 (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 76278)

● CODE 필드와 NAME 필드에 복합 인덱스 생성(커버링 인덱스 ○) call

count

------- ------

current

rows

-------- ---------- ---------- ---------- ----------

cpu

elapsed

disk

query

----------

Parse

0.00

Execute

0.00

Fetch

0.00

-------- ---------- ---------- ---------- ----------

----------

------- -----total

0.00

~생략~ Rows (1st) Rows (avg) Rows (max) Row Source Operation

query가 적음 실행 계획에 TABLE ACCESS가 없음

---------- ---------- ---------- --------------------------------------------------1

1 INDEX RANGE SCAN MASTER_M1 (cr=2 pr=0 pw=0 time=18 us cost=1 size=9 card=1)(object id 76280)

각각의 실행 계획과 SQL 통계를 보면, 일단 복합 인덱스를 사용한 실행 계획에서 는 MASTER 테이블에 TABLE ACCESS가 일어나지 않습니다. 또한 SQL 트레이 스의 query를 보더라도 복합 인덱스를 사용하는 쪽은 TABLE ACCESS가 일어 나지 않으므로 매우 적은 블록에만 접근한다는 것을 알 수 있습니다. 이처럼 복합 인덱스를 사용해서 테이블 접근을 생략할 수 있다면 쿼리를 빠르게 만들 수 있습니다.

32 오라클 레벨업

004

NOT IN 구문으로 성능 문제 발생 시 대응 방법

두 테이블을 비교해서 한 쪽 테이블에 존재하지 않는 레코드를 찾을 때 NOT IN 구문을 사용하는 경우가 있습니다. 그런데 부정 연산자와 NULL이 포함된 연산 자는 인덱스를 사용하지 못하므로 성능 문제가 발생할 수 있습니다 7 . 이때 NOT EXISTS 구문을 사용하도록 SQL을 변경하면 인덱스를 활용하므로 처리가 빨라 집니다. MERGE 구문을 사용할 수도 있는데요, csv 또는 다른 애플리케이션의 데이터 를 인터페이스 테이블에 넣고 트랜잭션 테이블로 활용할 때 MERGE 구문을 사 용하면 1개의 SQL로 모든 처리를 할 수 있습니다. 따라서 존재 확인과 INSERT, UPDATE 분기 등을 할 때 따로 프로그램을 만들 필요가 없으므로 코드가 간단 해지고 가독성도 좋아집니다.

▒▒ NOT

EXISTS 구문으로 변환

그럼, NOT IN 구문을 NOT EXISTS 구문으로 변경하는 예제를 살펴보겠습니 다. EMP_HIST 테이블에는 존재하지 않는 EMP 테이블의 레코드를 ID 필드로 비교해 추출합니다. ● 변경 전 : NOT IN 구문을 사용한 경우 SELECT FROM WHERE

ID,NAME EMP ID NOT IN (SELECT ID FROM EMP_HIST )

7 옮긴이_ 이 책의 이전 도서인 『SQL 레벨업』(한빛미디어, 2016)이 있다면 363페이지 ‘인덱스를 사용하지 않는 검 색 조건’을 함께 참고해주세요.

1장 _ 성능 관리 Tips 33

● 변경 후 : NOT EXISTS로 변경한 경우 SELECT FROM WHERE

ID,NAME EMP e NOT EXISTS (SELECT ‘X’ FROM EMP_HIST eh WHERE e.ID = eh.ID )

NOT EXSITS 구문에 포함된 SELECT 구의 값은 무엇을 사용해도 상관없지만, 관습적으로 ‘X’를 사용하는 경우가 많습니다. 실행 계획을 비교해보면 다음과 같습니다. NOT IN 구문에서는 인덱스를 사용 하지 않지만, NOT EXISTS 구문으로 변경하면 인덱스가 쓰이는 것을 확인할 수 있습니다. ● NOT IN 구문의 실행 계획 ------------------------------------------------------------------------------------------| Id | Operation

| Name

------------------------------------------------------------------------------------------|

0 | SELECT STATEMENT

|* 1 | HASH JOIN RIGHT ANTI NA |

| 102K | 3312K |

376

(2) | 00:00:05 |

| 102K | 3312K | 1648K |

376

(2) | 00:00:05 |

2 |

TABLE ACCESS FULL

| EMP_HIST | 67309 | 854K |

(2) | 00:00:01 |

3 |

TABLE ACCESS FULL

| EMP

(2) | 00:00:01 |

| 102K | 2007K |

-------------------------------------------------------------------------------------------

‘TABLE ACCESS FULL’이므로 테이블 풀 스캔이 이루어집니다. ● NOT EXISTS 구문의 실행 계획 -------------------------------------------------------------------------------------------| Id | Operation

| Name

-------------------------------------------------------------------------------------------|

0 | SELECT STATEMENT

|* 1 | HASH JOIN RIGHT ANTI |

| 102K | 3312K |

370

| 102K | 3312K | 1648K |

370

(2)| 00:00:05 | (2)| 00:00:05 |

2 |

INDEX FAST FULL SCAN | EMP_HIST_IDX1 | 67309 | 854K |

(2)| 00:00:01 |

3 |

TABLE ACCESS FULL

(2)| 00:00:01 |

| EMP

| 102K | 2007K |

--------------------------------------------------------------------------------------------

EMP HIST 테이블 조작이 ‘INDEX FAST FULL SCAN’이므로 인덱스가 사용 됩니다.

34 오라클 레벨업

▒▒ MERGE 구문 사용

NOT IN 구문은 원래 테이블과 이력 테이블의 비교, 인터페이스 테이블에 데 이터를 넣고 INSERT/UPDATE 처리를 하는 경우 등에 사용합니다. 하지만 MERGE 구문을 사용하면 이들을 하나의 SQL로 적을 수 있습니다. MERGE 구문은 원래 테이블과 비교 대상 테이블을 비교하여 값이 존재하면 UPDATE하고 존재하지 않으면 INSERT합니다. MERGE 구문을 사용하면 존재 확인을 위한 IF 분기 코드가 필요하지 않으므로 SQL만으로 구현할 수 있습니다. ● 구문 MERGE INTO <원래 테이블> USING <비교 대상 테이블> ON <비교 조건> WHEN MATCHED THEN <ON 구문의 조건에 일치하는 레코드가 존재할 때의 UPDATE 구문> WHEN NOT MATCHED THEN <ON 구문의 조건에 일치하는 레코드가 존재하지 않을 때의 INSERT 구문>

예를 들어보겠습니다. EMP_IF 테이블의 레코드가 EMP 테이블에 존재하는지를 EMP_NO로 검색해 존재하면 UPDATE하고 존재하지 않으면 INSERT합니다. ● NOT IN 구문 INSERT INTO emp ( ID, CREATION_DATE, EMP_NO, NAME ) SELECT emp_s.NEXTVAL, SYSDATE, EMP_NO, NAME FROM emp_if WHERE EMP_NO NOT IN (SELECT EMP_NO FROM EMP); UPDATE SET WHERE

emp e UPDATE_DATE = SYSDATE, NAME = (SELECT NAME FROM emp_if ei WHERE ei.EMP_NO = e.EMP_NO) EMP_NO IN (SELECT EMP_NO FROM emp_if);

1장 _ 성능 관리 Tips 35

● MERGE 구문 MERGE INTO EMP e USING EMP_IF if ON (e.emp_no = if.emp_no) WHEN MATCHED THEN UPDATE SET UPDATE_DATE = SYSDATE, NAME = if.NAME WHEN NOT MATCHED THEN INSERT ( ID, CREATION_DATE, EMP_NO, NAME ) VALUES ( EMP_S.NEXTVAL, SYSDATE, if.EMP_NO, if.NAME )

36 오라클 레벨업

005

파티션을 활용해 접근 블록 수 줄이기

▒▒ 대규모 테이블을 분할해 다루기 테이블 레코드가 수천만 개를 넘으면 검색 처리가 늦어집니다. 이러한 때 활용 할 수 있도록 데이터를 파티션이라고 부르는 구조에 나눠 저장하는 시스템이 있 습니다. 바로 Oracle Partitioning인데요, 파티션과 관련된 내부 처리는 오라클 데이터베이스가 자동으로 처리해줍니다. SQL을 다시 작성할 필요가 없고 테이 블 이름을 변경할 필요도 없으므로 애플리케이션을 따로 변경할 것 없이 쉽게 활 용할 수 있습니다.

▒▒ 파티션을 사용한 처리 속도 향상 일반적인 검색 처리는 <인덱스 검색> ⇒ <물리 주소 추출> ⇒ <데이터 블록 추출> 이라는 과정으로 이루어집니다. 그런데 레코드 수가 많아지면 <인덱스 검색> 부 분에 걸리는 시간이 매우 길어지죠. 이러한 때는 (예를 들어) 날짜 범위를 기반으로 파티션을 분할하고 검색 대상 레 코드가 포함된 파티션만 참조하면, 전체적인 처리를 빠르게 만들 수 있습니다.

1장 _ 성능 관리 Tips 37

그림 1-6 지정한 연도의 파티션에만 접근해서 처리 고속화 테이블 데이터를 포함하지 않은 파티션에는 접근하지 않음 (프루닝, Pruning)

SQL 발행

2011년

2012년

2013년 2012년의 데이터에 접근할 때는 2012년 이외의 파티션 접근을 생략해서 빠르게 만듦

▒▒ 파티션 분할 방법 대표적인 파티션 분할 방법으로 범위(range), 리스트, 해시가 있습니다. 각각 다 음과 같은 분할을 수행합니다. 표 1-5 파티션 분할 방법과 특징 이름

분할 방법

범위

지정된 값의 범위를 기반으로 파티션을 분할합니다. 날짜처럼 특정 범위를 검색할 때 좋습니다.

리스트

지정된 값 리스트를 기반으로 분할합니다. 우편번호처럼 검색할 값이 정해져 있을 때 좋습니다.

해시

해시 키를 기반으로 분할합니다. ID 번호처럼 값에 의미가 없는 단일 검색에 좋습니다. 해시값으로 분할하므로 파티션마다 레코드 분포가 균형을 이룹니다.

파티션을 더 세부적으로 분할하는 콤퍼짓 파티션(composite partition) 기능도 있습니다. 이 기능을 사용하면 파티션 키를 2개 갖습니다.

▒▒ 파티션 테이블의 효과 그럼 검증해보겠습니다. 하루에 레코드 100개씩, 10년간 총 365,000개의 레코 드를 포함하는 테이블을 2개 만듭니다. 한 테이블에는 날짜 필드에 인덱스를 만 들고, 다른 테이블에는 날짜 필드로 범위 파티션하겠습니다. 기간을 1개월로 잡고 레코드를 추출한 뒤 SQL 트레이스를 확인하면 다음과 같

38 오라클 레벨업

습니다.

● 인덱스로 필터링 call

count

------- ------

current

rows

-------- ---------- ---------- ---------- ----------

cpu

elapsed

disk

query

----------

Parse

0.00

Execute

0.00

208

0.01

0.03

440

3100

-------- ---------- ---------- ---------- ----------

----------

Fetch

------- -----total

210

0.01

0.03

440

3100

~생략~ Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------3100

3100

3100 TABLE ACCESS BY INDEX ROWID TIME_TBL (cr=440 pr=23 pw=0 time=15604 us cost=26 size=70422 card=3201)

3100

INDEX RANGE SCAN TIME_TBL_I1 (cr=218 pr=11 pw=0 time=11624 us cost=11 size=0 card=3201)(object id 76405)

● 파티션 프루닝을 사용한 필터링 call

count

------- ------

current

rows

-------- ---------- ---------- ---------- ----------

cpu

elapsed

disk

query

----------

Parse

0.00

Execute

0.00

208

0.01

260

3100

-------- ---------- ---------- ---------- ----------

----------

Fetch

------- -----total

210

0.01

0.02

260

3100 ←query가 감소했음

~생략~ Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------3100

3100

3100 PARTITION RANGE SINGLE PARTITION: 62 62 (cr=260 pr=49 pw=0 time=23724 us cost=14 size=68200 card=3100)

3100

TABLE ACCESS FULL PART_TIME_TBL PARTITION: 62 62 (cr=260 pr=49 pw=0 time=12216 us cost=14 size=68200 card=3100)

각각의 query 블록 수를 비교해보니, 파티션 프루닝을 사용한 필터링은 인덱스 필터링에 비해 절반 정도로 줄었습니다.

1장 _ 성능 관리 Tips 39

017

트러블 원인을 빠르게 찾는 테크닉

▒▒ 트러블의 종류 실제 환경에서 데이터베이스 트러블이 발생하면 원인을 찾아야 합니다. 그런데 어디서부터 어떻게 확인해야 할까요? 일단 데이터베이스와 관련한 트러블(오류) 종류를 크게 나누면 [표 2-1]과 같습니다. 표 2-1 장애의 종류와 예 장애 패턴

패턴 개요

장애 예

사양 오류

애플리케이션 또는 데이터 문

데이터 문제로 발생하는 오류, 예를 들어 테이블 필드 정

제로 발생하는, 데이터베이스

의 자릿수보다 큰 숫자 데이터를 저장하려 하면 ‘ORA-

동작은 문제없는 사양의 오류

12899: value too large for column’이 발생함

내부 오류 등

예상하지 못한 사태로 데이터

ORA-600, ORA-7445(내부 오류) 또는 아카이브 로그

예상 밖의 동작

베이스에 어떤 문제가 발생한

출력 대상이 DISKFULL 되어 출력할 수 없는 경우 발생하

경우의 내부 오류 등

는 ORA-257, 데이터 파일 또는 제어 파일 손상 또는 예 상치 못한 프로세스 다운 등에 의한 ORA 오류 등

성능 지연 문제

기능 오류가 아닌, 성능 지연

데이터베이스 오류로 감지되지 않고 애플리케이션 측에

으로 발생하는 트러블

서 타임아웃 오류 또는 사용자 클레임으로 발각, 잡넷으로 경고 감지, CPU 사용량 증가 또는 메모리 고갈처럼 OS 리소스 모니터로 경고 감지(확인해보면 장시간 실행되는 SQL이 존재하기 때문) 등

장애가 발생하면 일단 어디에서 어떤 오류가 발생하는지 또는 처리 지연 문제인 지 등을 확인해야 합니다. 이를 확인했다면 앞의 [표 2-1]을 사용해 어떤 종류의 트러블인지 구분할 수 있습니다.

▒▒ 사양 오류 애플리케이션 측에서 오류를 감지해 애플리케이션 로드로 출력된 것을 모니터 링 툴에서 감시하여 장애가 발각되거나, 사용자 클레임 등으로 발각되는 경우입 니다. 이러한 종류의 문제는 오류 메시지를 기반으로 오라클 오류 메시지 매뉴얼

96 오라클 레벨업

등을 참조하면서 원인을 찾아야 합니다.

▒▒ 내부 오류 등 예상 외 동작 일반적으로 오라클 데이터베이스의 경고 로그에 ORA 오류가 발생합니다 (ORA-600 등, 경우에 따라서는 출력되지 않을 수 있습니다). 이런 종류의 문제는 오라클의 오류 메시지, 오류 코드를 기반으로 오라클 오류 메시지 매뉴얼 또는 오라클 공개 기술 정보 등을 참조해서 원인을 찾아야 합니 다. 원인을 찾았다면 그에 대한 대처를 수행합니다. 만약 원인을 찾지 못했다면 오라클 서포트에 문의하는 것이 좋습니다. 이때 경고 로그 이외에도 오류 발생 시간 부근의 트레이스 파일 또는 Core 파일이 출력되지 않았는지 등도 확인하 여 문의 시 함께 제출하면, 오라클 서포트 쪽에서의 대응이 훨씬 원활하게 진행 될 것입니다.

▒▒ 성능 지연 문제 이런 종류의 문제는 일단 성능 바틀넥이 있는 부분을 확인해야 합니다. 따라서 테 크닉이 조금은 필요한 부분입니다. 성능 넥이 일어나는 부분이 데이터베이스에 없는 경우도 있는데요, 이러한 때는 오라클 서포트에 문의해도 빠르게 대응할 수 없습니다. 따라서 지금부터 성능 지연 문제가 발생했을 때 어떤 부분이 문제인지 찾아내는 테크닉을 소개하겠습니다.

▒▒ 성능 지연 문제를 구분하는 플로 이유도 잘 모르겠는데 장애 지연 상황이 해소되었다면, 과거 상태를 분석해야 합니다. 이때는 Statspack(Diagnostics Pack 라이선스가 있다면 AWR 또는 ASH)을 사용해서 과거 시점의 상황을 확인합니다. 만약 지연이 발생하는 중 이라면 Enterprise Manager 화면에서 실시간 정보를 확인하고 V$SESSION, V$SESSION_WAIT, V$LOCK 등의 동적 성능 뷰를 참조해서 상황을 확인합니 다. 여기서 확인한 상황을 바탕으로 문제를 찾으면 됩니다. [그림 2-1]은 대략적 2장 _ 트러블 슈팅 Tips 97

인 성능 지연 확인 플로입니다. 그림 2-1 성능 지연을 구분하는 플로(현재 지연 발생 중일 경우)

2 문제가 특정 대기 이벤트인가, 순수하게 SQL 실행 자체가 늦어지는 것인가

장애/지연 상태

특정 부분만 느림

전체적으로 느림

대기 이벤트 확인

액티브 세션 확인 같은 세션에서 같은 대기 이벤트가 계속되지 않음

같은 세션에서 같은 대기 이벤트가 계속됨

전체 세션 중 액티브 세션이 많음

전체 세션 중 액티브 세션이 적음 DB 문제인가, CPU 또는 메모리 리소스 부족인가

DB 문제일 가능성 있음

특정 SQL이 CPU를 대량으로 소비할 가능성 있음 (이후에 해당 SQL을 찾아야 함)

발생하는 대기 이벤트의 원인이 성능 넥일 가능성 있음

문제가 발생한 곳이 DB인가, DB가 아닌가

DB에 SQL이 들어오지 않음. DB에는 문제없음

R 필드 ≦ CPU 코어 수 db file sequential read 또는 db file scattered read

기타 대기 이벤트

즉각적인 대응이 어려움. 해당 이벤트 와 관련한 독자적인 해석/대처가 필요

락 개방 대기

SQL 탐색(V$SESSION 또는 EM 활용)

특정 SQL이 락을 건 상태로 계속해서 처리가 멈춰있을 가능성 있음

락 대상 처리 확인

애플리케이션에서 명시적으로 DBMS_ LOCK으로 락을 걸었을 가능성 있음

오라클 내부의 어떤 처리에 의해 시스템 락의 가능성 있음

네트워크 관련 문제인가

이상 있음 V$LOCK 출력 결과로 어떤 처리가 락을 거는지 판단

TX 처리 완료까지 대기 또는 세션을 kill해버림

98 오라클 레벨업

이상 없음

지연이 event로 인해 발생했을 가능성 있음

메모리 리소스 부족의 가능성 있음

이상 없음 CPU/MEM 소비량에 문제는 없지만, 다음과 같은 문제가 발생할 수 있음 ① 디스크 접근량 증가 또는 I/O 응답에 문제 ② 대기 이벤트로 인한 지연 ③ 디스크 I/O 응답에 문제

전체 대기 이벤트를 확인하고 해당 SQL 탐색 (V$SESSION 또는 EM 활용)

기타

트랜잭션끼리 락 충돌 가능성 있음

Swap 또는 메모리의 free/buff/ cache 확인

CPU 리소스 부족의 가능성 있음(특정 처리가 CPU를 대량 으로 소비해서 늦어지는 경우). ‘특정 부분만 느림’으로 이동

디스크 접근이 빈번하게 발생할 가능성이 있는 SQL 탐색

락 상태 확인

이상 있음

R 필드 > CPU 코어 수

네트워크 문제일 가능성 있음

SQL 실행 통계 확인 CPUTime 큼 (DiskRead 많음) Elapsed 큼

CPUTime 작음 (DiskRead 적음) Elapsed 큼

접근 블록 수 증가. 실행 계획이 변경 되었을 가능성 있음

해당 SQL에 해당 SQL이 대기 문제없음 이벤트에서 대기 중인가 (다른 SQL / 디스크 응답에 문제가 있는가 을 조사)

네트워크 통신 계열의 대기 이벤트

다른 대기 event

대기 이벤트 확인

CPUTime 작음 (DiskRead 적음) Elapsed 적음

Disk I/O 계열의 대기 이벤트 디스크 응답에 문제가 있을 가능성 있음

트러블이 발생했을 때 허둥지둥 대응하는 일이 없도록, 미리 이러한 장애 구분 플 로를 준비해두는 것이 좋습니다. 트러블을 대비해서 충분히 준비해두면 DB 트러 블이 발생했을 때 재빠르게 대응할 수 있을 것입니다.1 column

오라클 오류 내용 확인 방법

오라클 오류가 발생하면 일단 오류 코드의 의미를 알아야 합니다. 오라클 매뉴얼 사이트가 공개 한 오라클 데이터베이스 오류 메시지 문서를 확인합니다. 하지만 실제 환경에서 장애가 발생했을 때 ‘실제 환경의 작업실에서 인터넷이 안 되는’ 상황 이 있을 수 있습니다. 이러한 경우에는 oerr이라는 편리한 명령어를 사용해주세요(oerr은 $ORACLE_HOME/bin에 있습니다). 예를 들어 ORA-12899 오류가 발생했다고 합시다. 이런 오류의 원인과 대처 방법을 알고 싶 을 때는 다음과 같은 명령어를 실행합니다. ● ORA 오류의 원인과 대처 방법을 확인하는 명령어 $ oerr ora 12899

그러면 다음과 같이 ORA-12899의 ‘Cause(원인)’과 ‘Action(대처 방법)’이 표시됩니다. 이를 사용해 무엇이 원인이고 어떻게 대처할지 간단하게 확인할 수 있습니다. ● 실행 결과 12899, 00000, “value too large for column %s (actual: %s, maximum: %s)” // *Cause: An attempt was made to insert or update a column with a value // which is too wide for the width of the destination column. //

The name of the column is given, along with the actual width

of the value, and the maximum allowed width of the column.

Note that widths are reported in characters if character length

semantics are in effect for the column, otherwise widths are

reported in bytes.

// *Action: Examine the SQL statement for correctness. Check source // and destination column data types. //

Either make the destination column wider, or use a subset

of the source column (i.e. use substring).

이 명령어는 ORA 오류 이외의 오류에도 사용할 수 있습니다. 다음은 EXP 오류와 TNS 오류 의 예입니다 .

1 옮긴이_ 윈도우에서 버그로 인해 특정 파일이 없어 명령어에 오류가 발생하는 경우가 있습니다. 이럴 때는 유닉스 계 열의 운영체제에 있는 오라클의 파일을 가져와서 사용하기 바랍니다.

2장 _ 트러블 슈팅 Tips 99

● EXP 오류의 원인과 대처 방법을 확인하는 명령어 $ oerr exp 5

● 실행 결과 00005, 00000, “all allowable logon attempts failed” // *Cause: Attempts were repeatedly made to log on with an invalid username // or password. // *Action: Shut down the utility, then restart and retry with a valid // username and password.

● TNS 오류의 원인과 대처 방법을 확인하는 명령어 $ oerr tns 12541

● 실행 결과 12541, 00000, “TNS:no listener” // *Cause: The connection request could not be completed because the listener // is not running. // *Action: Ensure that the supplied destination address matches one of // the addresses used by the listener - compare the TNSNAMES.ORA entry with // the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to // go by way of an Interchange). Start the listener on the remote machine.

필자도 트러블 슈팅을 할 때 이러한 명령어를 많이 사용합니다. ‘Cause(원인)’과 ‘Action(대처)’ 가 명확하게 기재되었다면 사양 오류인 경우가 많습니다. 반대로 ‘Action(대처)’이 ‘Contact Oracle Support Services(오라클 서포트 센터에 연락해주세요.)’라고 되어있을 경우에는 내 부 오류 등 예상하지 못한 동작일 가능성이 높습니다. 이처럼 대략적인 원인을 추가로 파악할 수 있답니다.

100 오라클 레벨업

018

AWR 이벤트 활용 2

‘DB CPU가 높은 상태라면 아무 문제도 없다’라는 말이 있습니다. Statspack 리 포트 또는 AWR 리포트의 Top 5 Timed Events(또는 Top 10 Foreground Events by Total Wait Time)를 확인한 사람들이 자주 하는 이야기입니다. 이는 DB CPU가 대기 이벤트 상태가 아니므로 ‘DB CPU가 높다’ = ‘대기가 적게 CPU를 사용한다’고 생각하는 것입니다. 하지만 대기가 적어 DB CPU가 높다는 것은 정말 아무 문제가 없다는 의미일까요? 이번 팁에서는 Top 5 Timed Event를 확인할 때 추가로 확인하면 좋을 부분들 을 소개합니다. Top 5 Timed Event를 확인할 때 이들 부분을 확인한다면 정확 하게 분석할 수 있습니다. 그림 2-2 Top 10 Foreground Events by Total Wait Time

4 core 머신에서 10분 동안의 결과라고 해도 문제없는 부분일까요? 두 번째 대기 이벤트는 cpu quantum(리소스 매니저의 CPU 할당 대기)입니다.

▒▒ DB

CPU가 높다 ≠ 문제가 없다

DB CPU가 높다는 것이 대기가 적다는 뜻은 아닙니다. CPU 처리에 많은 시간 이 든다는 의미입니다. 예를 들어 배치 처리에서 DB CPU가 높을 때, 다른 대기 이벤트 시간이 짧더라도 DB CPU가 길면 배치 처리 시간도 길어집니다. 자주 있 2장 _ 트러블 슈팅 Tips 101

는 경우로는, SQL 처리 1회는 대기 없이 처리하지만 해당 SQL이 배치 처리 중 반복해 실행되면 배치 시간이 길어지는 경우가 있습니다. 한번의 SQL 처리시간 이 대기가 거의 없는 10msec이라도, 100만 번 반복되면 3시간 정도 걸립니다. 2

따라서 ‘DB CPU가 높다’ = ‘문제가 없다’라는 공식은 성립하지 않습니다. ● 데이터베이스 처리 시간과 CPU 대기 시간의 관계 <DB 처리 시간> = <DB가 사용한 CPU 시간> + <비(非) 유휴 대기 시간> AWR 리포트의 내용에서는 다음과 같이 나타납니다. <DB TIME> = <DB CPU> + <non-idle wait time>

따라서 DB CPU가 높은 경우에는 해당 상태가 적절한지를 다른 섹션을 확인하 면서 판단해야 합니다. 대표적으로 CPU를 소비하는 처리로는 SQL 파싱 처리, 데이터 읽어 들이기, 연산 처리 등이 있습니다. 그러므로 AWR 리포트에서 다음 과 같은 다양한 섹션을 통합적으로 분석해서 판단해야 합니다. 표 2-2 AWR 리포트 섹션과 주목해야 할 부분 관점

AWR 리포트 확인 포인트

주목해야 할 부분

처리량

Load Profile

처리량

SQL ordered by Executions

실행 횟수가 많은 SQL

CPU 시간

SQL ordered by CPU Time

CPU 시간이 많은 SQL

SQL 파싱 시간

Instance Efficiency Percentages

SQL 파싱 상태

SQL ordered by Parse Calls

파싱 횟수가 많은 SQL

Segments by Logical Reads

접근 블록 수가 많은 세그먼트

SQL ordered by Gets

접근 블록 수가 많은 SQL

SQL ordered by Reads

디스크 접근 수가 많은 SQL

데이터 읽어 들이기

그럼 어떠한 흐름으로 조사하는지를 그림으로 살펴봅시다. 그림 2-3 SQL ordered by CPU Time

한번 실행의 CPU 처리 시간은 30msec 정도 (CPU 시간 ≒ 실행 시간)

102 오라클 레벨업

그림 2-4 SQL ordered by Gets

1회 실행에 읽어 들인 블록 수는 100블록 정도

2 처리가 완료되지 않은 것은 Executions가 0회로 출력

그림 2-5 SQL ordered by Reads

1회 실행에 디스크 접근은 거의 없음

그림 2-6 SQL ordered by Executions

1회 실행에 처리하는 레코드 수는 1개이므로 실행 횟수가 꽤 많음

그림 2-7 Segments by Logical Reads2

접근 블록 수가 많은 객체가, 같은 SQL의 대상 객체가 되어있음 TEST_RANDOM3의 인덱스 도 접근 블록 수가 많은 객체로 되어있음

이들을 종합하면, 비효율적인 인덱스 접근으로 이루어져 있어 메모리의 접근 블 록 수가 많아지고 CPU 사용 시간이 길어진 것이라고 생각할 수 있습니다. 이러

2 옮긴이_ I_TEST_RANDOM3_1이 TEST_RANDOM3의 인덱스입니다. 옆에 있는 Obj.Type을 보면 더 명확하 게 인덱스라는 것을 알 수 있습니다.

2장 _ 트러블 슈팅 Tips 103

한 SQL의 실행 횟수가 많으므로 전체적으로 처리 시간이 길어지는 결과가 나온 다는 것도 파악할 수 있겠지요.

▒▒ Wait

Event Histogram에서 주목할 부분

Top 10 Foreground Events by Total Wait Time에는 Wait Avg(ms)라는 필 드가 있습니다. 이 값은 1회 평균 대기 시간을 파악할 때 사용할 수 있습니다. 이 필드는 대기 이벤트의 합계 시간이 발생 횟수가 많기 때문인지, 아니면 각 대기 시간이 길기 때문인지 판단할 때 유용합니다. 발생 건수가 많은 대기라면 발생 횟수를 줄이는 대책을 생각하면 되고, 각 대기 시간이 길기 때문이라면 대기 시 간 자체를 감소시키는 대책을 생각하면 됩니다. 하지만 이 값은 평균값(합계 시간을 대기 횟수로 나눈 값)이므로 주의해야 합니 다. 동시 처리가 많은 시스템에서는 I/O 처리 또는 같은 레코드에 대한 처리가 충 돌하면서, 기다리는 레코드와 필드가 길어지고 대기 시간도 길어지는 경우가 있 습니다. 따라서 각각의 평균 대기 시간이 균일하지 않을 수 있으므로 Wait Event Histogram에도 주목해야 합니다. 또한 어떤 처리가 너무 느려지는 등의 문제가 발생했다면 Wait Event Histogram 에 주목하기보다, 평소와는 달리 장시간 동안 실행되는 대기가 발생하지 않았음 을 조사하는 것이 좋습니다(처리가 늦어지는 원인이 해당 대기인지 판단하려면 V$SESSION 또는 ASH 등의 상세한 정보가 필요합니다). 그림 2-8 Top 10 Foreground Events by Total Wait Time 평균 대기 시간은 316msec

104 오라클 레벨업

그림 2-9 Wait Event Histogram Detail(64msec to 2sec)

실제로 대기 시간에 큰 편차가 보임

▒▒ 대기 클래스에서 주목할 부분 Statspack 리포트에는 출력되지 않지만, AWR 리포트를 보면 Wait Classes by Total Wait Time 섹션이 출력됩니다. 이 섹션에는 대기 클래스의 대기 시간 통 계가 출력됩니다. 대기 클래스란 대기 이벤트를 특징 또는 특성에 기반해 그룹으로 나눈 것입니 다. 비유하자면 각각의 대기 이벤트가 나무라면 대기 클래스는 숲에 해당합니다. 각 대기 이벤트의 상세를 확인하기 전에 대기 클래스를 확인하면, 시스템의 경향 또는 바틀넥 부분을 쉽게 알 수 있으므로 어떤 부분이 문제인지 압축해서 확인할 수 있습니다. 따라서 전체적인 조사 시간이 짧아지겠지요. 예를 들어 “대기 이벤트인 direct path write temp 또는 direct path read가 높 군”이라고 확인하는 대신, USER I/O 대기 클래스가 높다는 상황부터 파악한다 고 합시다. 이렇게 하면 USER I/O 관계의 대기 이벤트 상태, 물리 로딩이 많은 SQL 또는 물리 I/O가 많은 객체의 유무 등, 더 큰 관점에서 상태를 분석할 수

2장 _ 트러블 슈팅 Tips 105

있습니다. 그림 2-10 Wait Classes by Total Wait Time 어떤 대기가 높은지 확인합니다.

그림 2-11 Top 10 Foreground Events by Total wait Time

User I/O 대기 클래스 내부에서도 어떤 I/O 처리에서 대기가 많은지 찾습니다. 이어서 해당 I/O 처리와 관련한 SQL 또는 객체에 주목합니다.

106 오라클 레벨업

019

인터커넥트 바틀넥을 찾아내는 성능 분석 요령 2

시스템을 개발할 때는 성능 테스트 또는 성능 검증이라 부르는 태스크를 계획합 니다. 이 작업을 간단하게 말하면 ‘성능 목표를 달성할 때까지 실제로 동작시켜 보면서 응답 시간과 스루풋을 측정하고, 바틀넥 튜닝을 반복하는 작업’입니다. 필자의 경험으로는 어떤 리소스가 바틀넥인지 찾을 때 StatsPack 또는 AWR 리포트를 활용하는 것이 좋습니다. 예를 들어서 Oracle Real Application Clusters(이하 Oracle RAC) 구성의 데이터베이스에서 캐시 퓨전이 증가했다 면, 네트워크에서 새로운 바틀넥이 발생하지 않았는지 확인해야 합니다. 또한 데 이터베이스 서버에 CPU 또는 메모리를 추가한 경우에도 마찬가지로 네트워크에 새로운 바틀넥이 발생하지 않았는지 확인해야 합니다. 그림 2-12 Oracle Real Application Cluster 구성

공유 디스크

상호 연결(인터넷)

이번 팁에서는 Oracle RAC 노드가 서로 통신하는 인터커넥트에 사용되는 네트 워크 스위치에 바틀넥이 걸린 경우를 다룹니다. 성능이 떨어졌을 때 오라클에서 어떤 상태가 감지되는지를 다루므로, 인터커넥트에서의 바틀넥을 찾는 요령을 이해할 수 있습니다.

2장 _ 트러블 슈팅 Tips 107

▒▒ 인터커넥트 바틀넥으로 인한 대기 이벤트의 특징 인터커넥트의 네트워크 스위치에 바틀넥이 발생했을 때, Global Resource 계 열의 대기 이벤트에 나타나는 특징이 있습니다. Global Resource는 버퍼 캐시 2

의 블록처럼 Oracle RAC 환경에 존재하는 리소스입니다. Global Resource 계열의 대기 이벤트에는 다음과 같은 것이 있습니다3 . • gc [current/cr] multi block request • gc [current/cr] block [2/3]-way • gc [current/cr] block busy • gc [current/cr] grant 2-way • gc current grant busy • gc [current/cr] [block/grant] congested • gc cr failure / gc current retry

그럼 이러한 경우의 예를 살펴봅시다. [표 2-3]을 보면 gc cr multi block request 가 크게 증가한 것을 확인할 수 있습니다. 또한 cr request retry도 증가했습니다. cr request retry는 인터커넥트의 UDP 통신에서 패킷 손실(Packet Loss) 등으 로 인해 블록을 주고받지 못해 다시 요구할 때 발생하는 대기 이벤트입니다. 이 러한 이벤트는 대기 카운트업이 되지 않고 재전송 요구도 없는데요, 보통 이러한 이벤트를 ‘마커 이벤트(Marker Event)’라고 부릅니다. 이러한 마커 이벤트는 대 기 시간이 아니라 대기 횟수에 주목해야 합니다.

3 지은이_ Oracle RAC 대기 이벤트와 관련된 자세한 내용은 다음 매뉴얼을 참고해주세요.

Oracle Real Application Clusters Administration and Deployment Guide 11g Release 2(11.2) E41960-03 13 Monitoring Performance https://goo.gl/ifpcey Oracle Real Application Clusters Administration and Deployment Guide 12c Release 1(12.1)

E48838-09 13 Monitoring Performance https://goo.gl/ifpcey

108 오라클 레벨업

표 2-3 인터커넥트의 네트워크 스위치에 바틀넥이 발생했을 경우의 대기 이벤트(발췌) 정상 때 Waits

대기 이벤트 이름 gc cr multi block request

지연 때 Total Wait Time(s)

Waits

Total Wait Time(s)

110

16.4320

502

2.2

cr request retry

gc cr multi block request의 대기 시간이 증가하는 시점에서 cr request retry 의 대기 회수가 늘어납니다. 왜 그럴까요? 다음 조건들이 그 원인이라고 가설을 세운 뒤 조사해보겠습니다. 1. 수신 측(서버 프로세스)에서 아무것도 수신할 수 없는 경우 2. UDP 버퍼가 오버플로된 경우 3. 네트워크 드라이버, 네트워크 스위치 측에서 패킷이 드롭된 경우

▒▒ 원인을 찾아내는 과정 1. 수신 측(서버 프로세스)에서 아무것도 수신할 수 없는 경우 일단 수신 측(서버 프로세스)에서 아무것도 수신할 수 없을 가능성을 조사합니 다. LMS 프로세스 수에 따라 경우를 나누어 확인해봅시다. 다음은 검증 환경에 서 cr request retry 이벤트와 GCS_SERVER_PROCESSES 매개변수의 상관 관 계를 검증한 것입니다. 캐시 퓨전양과 트랜잭션양 등의 워크로드에 영향을 받을 수 있으므로, 어디까지나 참고 자료라고 생각하고 파악하기 바랍니다. 그림 2-13 cr request retry 이벤트와 GCS_SERVER_PROCESSES 매개변수의 상관 관계 발생 횟수

검증 결과

2장 _ 트러블 슈팅 Tips 109

결과를 보면 GCS_SERVER_PROCESSES 값이 늘어나면서 cr request retry가 줄어든다는 것을 알 수 있습니다. 하지만 cr request retry가 아직 많은 상황입니 다. 따라서 첫 번째 가설은 아니라고 할 수 있습니다. 2

2. UDP 버퍼가 오버플로된 경우 이어서 UDP 버퍼의 오버 플로를 생각해봅시다. UDP 버퍼의 크기는 <DB_BLOCK_ SIZE> × <DB_FILE_MULTIBLOCK_READ_COUNT>라는 공식으로 계산하고, UDP의 receive buffer size에 설정하는 것이 일반적입니다. 성능 테스트 전후에 netstat -s -p udp를 출력해서 socket overflows를 확인 해보겠습니다. socket overflows가 발생하지 않는다면 UDP 버퍼에는 문제가 없다고 판단할 수 있습니다. 더 확실하게 판단할 수 있도록 UDP 패킷 손실 감 소 빈도를 DB_FILE_MULTIBLOCK_READ_COUNT의 감소로 확인해도 좋 습니다. 캐시 퓨전으로 멀티 블록을 읽어 들일 때 최대 전송 크기는 DB_FILE_ MULTIBLOCK_READ_COUNT이기 때문입니다. 현재 경우는 DB_FILE_ MULTIBLOCK_READ_COUNT를 작게 해서 cr request retry를 줄일 수 있는 데요, 하지만 cr request retry가 아직 발생하지 않은 상태이므로 두 번째 가설 도 아니라고 할 수 있습니다.

3. 네트워크 드라이버, 네트워크 스위치 측에서 패킷이 드롭된 경우 마지막 가설은 네트워크 드라이버, 네트워크 스위치에 문제가 발생했다는 것입 니다. 패킷이 드롭되는지는 데이터베이스 서버 측의 netstat -s -p udp 명령어 의 incomplete headers와 bad checksums로 확인할 수 있습니다. 더 정확하 게 상황을 확인하려면 네트워크 스위치 쪽에서 계측하는 편이 좋습니다. 현재 경 우에는 네트워크 스위치 드롭 수를 계측했을 때 다음과 같은 결과가 나옵니다. 그래프 내부의 왼쪽 막대그래프가 지연 때의 네트워크 스위치입니다. 그리고 오 른쪽 막대그래프는 약 2배 성능을 보이는 네트워크 스위치를 사용한 경우의 계측 결과입니다. 따라서 패킷 드롭이 원인이라는 것을 확인할 수 있습니다.

110 오라클 레벨업

그림 2-14 지연 때의 네트워크 스위치(왼쪽)과 2배 성능을 가진 네트워크 스위치(오른쪽)의 계층 결과 비교 드롭 수

인터커넥트에 바틀넥이 의심되는 경우 원인을 어떻게 찾을 수 있는지 Statspack 과 AWR 보고서로 가설을 세우고 증명하는 과정을 소개했습니다. 만약 이런 상 황에서 네트워크 스위치를 교체할 수 없다면 GCS_SERVER_PROCESSES를 늘 리거나 DB_FILE_MULTIBLOCK_READ_COUNT를 줄이는 대책을 사용해야 합니다. 여담이지만 cr request retry 이외에 gc current retry, gc cr failure도 비슷한 종류의 대기 이벤트입니다. 여기서 주의할 점은 retry 계열의 대기 이벤트는 대 기 시간이 나오지 않으므로 대기 횟수로 확인해야 합니다. AWR의 Top5 이벤트 에는 나오지 않으므로 AWR의 Foreground Wait Events 섹션을 확인해야 합 니다. 또 DB_FILE_MULTIBLOCK_READ_COUNT를 변경하면 실행 계획에 도 영향이 갑니다. 어느 정도 영향을 미치는지 확실하게 파악하고 매개변수를 변 경하기 바랍니다.

2장 _ 트러블 슈팅 Tips 111

020

2개의 AWR 리포트를 효율적으로 비교하는 방법

▒▒ 데이터베이스 분석에서의 AWR 활용 과거 성능 문제가 발생했던 시간대의 DB 성능 분석과 일반적인 헬스 체크를 위 한 DB 분석에는 보통 AWR 리포트를 사용합니다. 그런데 ‘성능 문제가 발생했을 때의 리포트를 보아도 어디가 문제인지 알 수 없는’ 상황이 많습니다. AWR 리포트를 효율적으로 살펴보려면 평소의 AWR 리포트와 비교해보는 것 이 좋습니다. 문제가 있는 시간대의 리포트만 보아서는 해당 수치가 정상인지 비 정상인지 알기 어렵기 때문입니다. 예를 들면 일주일 전의 같은 요일/시간의 리 포트(따라서 평소의 리포트)를 추출해서 함께 비교하며 수치 변화가 큰 곳을 찾 아야 합니다. AWR 리포트를 비교할 때 텍스트 또는 HTML 형식의 리포트 파일을 나란히 놓 고 위에서부터 차근차근 비교하는 경우도 있는데요, 생각만 해도 눈이 굉장히 피 곤한 작업입니다. 이런 때는 ‘AWR 기간 비교 리포트(AWR Compare Periods Report)’를 사용하는 것이 편리합니다. 이 기능은 Diagnostics Pack 라이선스로 제공됩니다. Oracle Database 11g Release 1(11.1) 이후 버전에서는 AWR의 유지 기간이 기본 8일로 설정됩니다. 따라서 저장 기간 설정을 늘리거나, 삭제되기 전에 정기적으로 리포트를 자동 생 성하고 백업하는 것을 추천합니다. ‘과거의 트러블 때를 분석하고 싶지만 AWR 정보가 남아 있지 않은’ 경우가 의외로 많기 때문입니다.

▒▒ AWR 기간 비교 리포트 생성 Enterprise Manager를 사용한 AWR 기간 비교 리포트를 만드는 방법과 비 교하는 방법을 소개하겠습니다. Enterprise Manager의 데이터베이스 인스턴 스 홈에서 [Performance] → [AWR] → [AWR Report]를 클릭합니다. 이어 112 오라클 레벨업

서 이동한 페이지에서 Manage Snapshots and Baselines 아래에 있는 [Run Compare Periods Report] 버튼을 누릅니다. 1nd 기간의 시작/종료 시점과 2nd 기간의 시작/종료 시점을 스냅샷에서 선택 해서 비교 리포트를 생성합니다. 스냅샷 대신에 저장된 베이스라인을 사용할 수 도 있습니다. 그림 2-15 비교 대상 기간(시작/종료 시점)의 스냅샷 지정

[Generate Report]을 클릭하면 비교 리포트가 만들어져 출력됩니다. 그림 2-16 AWR 기간 비교 리포트 출력 결과 1nd 기간과 2nd 기간의 수치 차이를 비율로 확인 가능

여러 가지 정보가 출력되므로, 단순하게 리포트 파일을 나란히 놓고 보는 것보다 효율적으로 분석할 수 있습니다.

2장 _ 트러블 슈팅 Tips 113

021

자동 성능 진단 기능

▒▒ AWR 기간 비교 리포트는 중급자 전용 앞서 팁 020에서는 AWR 기간 비교 리포트를 사용해 일반적인 경우와 문제가 발 생했을 경우의 AWR 리포트를 비교해서 문제가 있는 부분을 찾는 방법을 소개 했습니다. 그런데 ‘비교해서 수치 차이를 살펴보아도 무엇이 문제인지 원인을 알 수 없는’ 경우가 있습니다. AWR 기간 비교 리포트는 AWR 리포트를 어느 정도 해석할 수 있는 기술이 필요한 만큼 중급자 전용 기술이라고 말할 수 있습니다. 이때 AWR 데이터를 기반으로 자동으로 진단해주는 편리한 기능이 요구될 수 있 는데요, 당연히 제공됩니다. 바로 ADDM이라는 기능으로 Diagnostics Pack 라 이선스로 제공됩니다.

▒▒ 자동 성능 진단의 구조 ADDM은 오라클 데이터베이스 내부에 탑재된 성능 진단 엔진입니다. ADDM은 AWR에 축적된 가동 정보를 정기적으로 분석해서 문제를 발견하고 원인과 대처 방안을 제시합니다. AWR 정보를 기반으로 분석하므로 AWR 스냅샷의 추출 간 격을 지정해서 진단할 수도 있습니다. 그림 2-17 ADDM 구조

자동 실행

부하가 높은 SQL 통계 정보 등

진단 결과 추출 수동 실행

결과 생성

114 오라클 레벨업

스냅샷들의 차이 분석

정기적으로 스냅샷 저장

ADDM은 2개의 AWR 스냅샷에서 데이터베이스의 부하 정보를 추출해 진단합 니다. 수동으로 데이터베이스를 진단할 수도 있지만, 보통 정기적으로 자동 실행 되어 데이터베이스 성능에 문제가 없는지 감시합니다. ADDM은 AWR 스냅샷 을 추출할 때 MMON 프로세스에 의해서 자동으로 실행됩니다. 자동 실행되면 추출한 스냅샷과 바로 이전의 스냅샷을 비교해서 진단합니다. 수동으로 실행할 때는 원하는 기간을 지정해서 진단할 수 있습니다.

▒▒ ADDM 확인 방법 정기적으로 자동 수행되는 ADDM 분석 결과는 대상 데이터베이스의 ‘Advisor Central’ 화면 아래 표시되는 ‘Advisor Tasks’에서 대상 기간의 ADDM을 선택 해서 확인할 수 있습니다. 이때 ‘View Result’를 클릭하면 다음과 같이 성능에 영 향을 주는 요소가 표시됩니다. 그림 2-18 ADDM 결과 표시 4

www.oracle.com

현재 예에서는 ‘Shared Pool Latches’가 성능 영향의 44.8%를 차지합니다. Finding 필드에 있는 ‘Shard Pool Latches’ 등을 클릭하면 ADDM 진단 결과 상 세가 표시되며 튜닝 방법을 추천해줍니다. 튜닝 방법에 따라서 버튼 한번만 클릭 하면 곧바로 튜닝을 해주는 경우도 있습니다 . 앞의 예에서는 자동으로 ADDM 분석을 정기 수행합니다. 만약 수동으로 분석

4 옮긴이_ 참고 자료 : https://goo.gl/Xzx6M5

2장 _ 트러블 슈팅 Tips 115

column

R이란?

R은 다음과 같은 통계 분석 전용 환경을 제공합니다 . • 데이터를 인터랙티브하게 분석하고 시각화하기 위한 R Console • 사용하기 쉬운 프로그래밍 언어 • 다양한 통계 기법을 제공하는 R 패키지(집필 시점 기준 6,000개 이상)

1994년 등장한 R은 GPL이라는 라이선스 형태로 사용할 수 있는 무료 통계 분석 환경입니 다. 주요 용도는 데이터 분석과 시각화입니다. 현재 전 세계 사용자수는 약 200만 명입니다 . 최초의 통계 분석 환경은 1984년 AT&T 벨 연구소가 개발한 ‘S 시스템’입니다. S 시스템은 이 후 GNU 프로젝트에 의해 오픈 소스가 되었습니다. R은 S 시스템의 오픈 소스 버전이라고도 할 수 있습니다. 한편 ‘Bay Area R User Group 2009 Kickoff’에서는 R의 사용법을 둘러싸고 토론이 벌어 지기도 했습니다 . 이 토론에서는 R을 사용하는 기업들이 어떤 워크플로를 사용해 일을 하는지, 어떤 예측 모델을 사용하는지 등을 다루었습니다. 예를 들어 구글에서는 R을 사용해서 다음과 같은 워크플로를 사용한다고 밝혔습니다. 1. 외부 도구로 데이터 내보내기 2. R로 가져오기 3. R로 분석과 모델링 생성하기 4. 모델링 결과를 파이썬과 C++로 구현

또한 페이스북은 사용자의 페이스북 체류 시간을 늘리기 위해 R의 rpart 패키지를 활용한 사례 를 소개하기도 했습니다. 어디까지나 담당자 개인의 의견이므로 기업 전체의 입장을 대변하는 예 라고는 할 수 없지만, 어떻게 활용할 수 있을지 조금이나마 도움은 될 것입니다. 789

7 지은이_The R Project for Statistical Computing 7 http://www.R-project.org/ 8 지은이_Learning R Series 8 https://goo.gl/krOrH 9 지은이_R-bloggers How Google and Facebook are using R 9 https://goo.gl/0Mt2ES

380 오라클 레벨업

092

R로 데이터베이스에 접근하고 통계 분석하기

Oracle R을 사용하면 데이터 불러오기부터 저장까지 한번에 작업할 수 있습니 다. 따라서 업무 데이터를 쉽게 분석할 수 있습니다. 이번 팁에서는 Oracle R Enterprise(이하 ORE)를 사용해 데이터를 불러오고 저장하는 방법을 설명합니 다. 구체적으로는 ORE로 데이터베이스에 접속한 이후 다음과 같은 흐름으로 설 명하겠습니다. ❶ db_opt 테이블에 저장된 데이터 불러오기 ❷ 데이터 통계 분석하기 ❸ 결과를 result 테이블에 저장하기

db_opt 테이블은 소스 데이터를, result 테이블은 분석 결과 데이터를 저장하 는 사용자 테이블입니다. ORE를 사용하려면 Oracle Database Enterprise Edition 옵션 중에서 Oracle Advanced Analytics가 필요합니다. 그림 6-4 처리 과정

데이터베이스

R 환경

통계 분석 알고리즘

소스 데이터

소스 데이터 ❶ 데이터 불러오기

분석 결과 데이터

분석 결과 데이터 ❸ 데이터 저장

❷ 데이터 분석 이번 팁에서 다룰 범위

6장 _ 데이터마이닝 Tips 381

▒▒ 데이터베이스에서 데이터 불러오기 표 6-3 데이터 이미지 No

데이터베이스 옵션 사용 상태

Oracle Real Application Clusters, Oracle Active Data Guard, Oracle ...

Oracle Real Application Clusters, Oracle Advanced Compression, Ora ...

Oracle Real Application Clusters, Oracle Partitioning, Oracle Diagnost …

Oracle Real Application Clusters, Oracle Real Application Testing …

Oracle Real Application Clusters, Oracle Database Lifecycle Manage …

Oracle Real Application Clusters, Oracle Partitioning, Oracle Diagnosti …

Oracle Real Application Clusters, Oracle Advanced Analytics, Oracle …

Oracle Multitenant, Oracle Advanced Security, Oracle Database Vault …

Oracle Real Application Clusters, Oracle Active Data Guard, Oracle P …

Oracle Real Application Clusters, Oracle Real Application Testing, Ora …

일단 db_opt 테이블에서 데이터를 불러오겠습니다. db_opt 테이블은 데이터 베이스의 옵션 사용 상태를 저장한 테이블입니다. 예를 들어 ‘Oracle Database 6

In-Memory, Oracle Real Application Cluster, Oracle Active Data Guard, Oracle Partitioning, Oracle Diagnostics Pack, Oracle Tuning Pack, Oracle Database Lifecycle Management Pack’과 같은 내용을 저장합니다. db_opt 테이블에서 R로 데이터를 불러오려면 다음과 같은 방법을 사용합니 다. 이때 library() 함수로 읽어 들이는 패키지는 install.packages() 함수로 미 리 설치해야 합니다. 자세한 사항은 팁 098의 컬럼 ‘패키지 설치 방법’을 참고 해주세요. ● 예제 스크립트 .libPaths(“<R 패키지 저장 경로>”) library(ORE)

# 경로를 지정합니다. # ORE 패키지를 불러옵니다.

# 데이터베이스에 접속합니다. if(!ore.is.connected()) { ore.connect(user=”ruser”, sid =”orcl”, host=”localhost”, password = ”ruser”, port = 1521, all = TRUE) }

382 오라클 레벨업

# db_opt 테이블을 참조하는 getDB_opt 함수를 생성합니다. getDB_opt <- function() { con <- dbConnect(Extproc()) rs <- dbSendQuery(con, “SELECT * FROM DB_OPT”) df <- fetch(rs) } # db_opt 테이블을 참조하는 getDB_opt 함수를 실행합니다. df <- ore.doEval(getDB_opt, ore.connect =TRUE)

데이터베이스 접속과 관련한 자세한 사항은 매뉴얼10 을 참조해주세요

▒▒ 통계 분석 실행하기 이어서 통계 분석을 실행합니다. 이번 팁에서는 데이터의 인과 관계를 확인하는 어소시에이션 분석을 실행합니다. 어소시에이션 분석에서 사용하는 arules 패키 지는 뒤에 나오는 컬럼 ‘어소시에이션 분석이란?’을 참조해주세요. ● 예제 스크립트 # arules에서 사용할 수 있게 데이터를 트랜잭션 데이터로 변환합니다. db_opt <- as(sapply(ore.pull(df)[,2], strsplit, “,”), “transactions”) # 어소시에이션 분석 전용 패키지 arules를 불러옵니다. library(arules) # apriori() 함수를 실행합니다. res.apriori <- apriori(db_opt) # result.R 파일에 출력합니다. sink(file = ”result.R”)

10 지은이_Oracle R Enterprise User’s Guide Release 1.2 for Linux, Solaris, AIX, and Windows

E35158-07 Connectto an Oracle Database 10 https://goo.gl/6HJoCU 10 Oracle R Enterprise User’s Guide Release 1.3 for Windows, Linux, Solaris, and AIX E36761-08 10 Automatic Database Connection in Embedded R Scripts 10 https://goo.gl/E5yAgR 10 Oracle R Enterprise User’s Guide Release 1.4 E39886-03 About Connecting to the Database 10 https://goo.gl/y6adAg

6장 _ 데이터마이닝 Tips 383

# 인과 관계(TOP10)를 파일에 출력합니다. inspect(head(sort(res.apriori, d =T, by =”confidence”), n = 10)) # 콘솔에 출력합니다. sink() # 파일을 읽기 전용으로 오픈합니다. dataBuf <- file(“result.R”, “r”) # 텍스트를 불러옵니다. result <- as.data.frame(readLines(dataBuf)) # 데이터 프레임에 열 ”db_opt”를 추가합니다. colnames(result) <- c(“db_opt”) # 파일을 닫습니다. close(dataBuf)

예제 스크립트의 result 객체를 출력하면 다음과 같습니다. ● 실행 결과 예 > result db_opt lhs

rhs

support confidence

lift

1 {}

=> {Oracle Partitioning}

1.0

1 1.000000

2 {}

=> {Oracle Real Application Clusters}

1.0

1 1.000000

3 {Oracle Spatial and Graph} => {Oracle Advanced Compression}

0.1

1 2.500000

4 {Oracle Spatial and Graph} => {Oracle Tuning Pack}

0.1

1 1.428571

5 {Oracle Spatial and Graph} => {Oracle Diagnostics Pack}

0.1

1 1.111111

6 {Oracle Spatial and Graph} => {Oracle Partitioning}

0.1

1 1.000000

7 {Oracle Spatial and Graph} => {Oracle Real Application Clusters}

0.1

1 1.000000

8 {Oracle Advanced Security} => {Oracle Database Vault}

0.1

1 10.000000

9 {Oracle Database Vault}

0.1

1 10.000000

0.1

1 10.000000

=> {Oracle Advanced Security}

10 {Oracle Advanced Security} => {Oracle Multitenant}

표 6-4 실행 결과의 항목 설명 항목

설명

lhs

좌변을 나타내는 left hand side의 약어입니다. 조건부를 의미합니다.

rhs

우변을 나타내는 right hand side의 약어입니다. 결론부를 의미합니다.

support

지지도를 나타냅니다. 모든 트랜잭션에 걸쳐 조건과 결론이 동시에 발생하는 케이스가 차 지하는 비율을 의미합니다.

384 오라클 레벨업

confidence

신뢰도를 나타냅니다. 조건이 발생한 케이스에서 결론이 발생한 비율을 의미합니다.

lift

리프트 수치를 나타냅니다. 일반적으로 리프트 수치 >1이라면 인과 관계가 유효하다고 간 주할 수 있습니다.

▒▒ 데이터베이스에 데이터 저장하기 마지막으로 통계 분석 결과(앞에서 설명한 inspect 명령어의 실행 결과)를 데이 터베이스의 result 테이블에 저장합니다. ● 예제 스크립트 # 테이블이 이미 있는 경우 삭제합니다. if (ore.exists(“RESULT”)) { ore.drop(table=”RESULT”) } # 데이터베이스에 저장합니다. ore.create(result, table=”RESULT”) # 데이터베이스로의 접속을 차단합니다. if (ore.is.connected()) { ore.disconnect() }

SQL*Plus에서 result 테이블을 검색하면 다음과 같은 결과를 확인할 수 있습니다. ● 실행 결과 예 SQL> SELECT * FROM RESULT; db_opt

R의 result 객체와 동일한 내용을 참조할 수 있습니다.

-----------------------------------------------------------------------------------------------lhs

rhs

support confidence

lift

1 {}

=> {Oracle Partitioning}

1.0

1 1.000000

2 {}

=> {Oracle Real Application Clusters}

1.0

1 1.000000

3 {Oracle Spatial and Graph} => {Oracle Advanced Compression}

0.1

1 2.500000

4 {Oracle Spatial and Graph} => {Oracle Tuning Pack}

0.1

1 1.428571

5 {Oracle Spatial and Graph} => {Oracle Diagnostics Pack}

0.1

1 1.111111

6 {Oracle Spatial and Graph} => {Oracle Partitioning}

0.1

1 1.000000

7 {Oracle Spatial and Graph} => {Oracle Real Application Clusters}

0.1

1 1.000000

8 {Oracle Advanced Security} => {Oracle Database Vault}

0.1

1 10.000000

9 {Oracle Database Vault}

0.1

1 10.000000

0.1

1 10.000000

=> {Oracle Advanced Security}

10 {Oracle Advanced Security} => {Oracle Multitenant}

6장 _ 데이터마이닝 Tips 385

column

어소시에이션 분석이란?

데이터로부터 조건과 결론 사이의 인과 관계를 규명하는 통계 분석 방법입니다. 마트에서 장을 볼 때 함께 구매하는 상품을 알아낼 때 많이 사용하므로 ‘바스켓 분석’이라고도 합니다. 많은 데 이터 조합 속에서 인과 관계를 효율적으로 발견할 수 있는 ‘apriori(상관 분석)’라는 알고리즘 을 주로 사용합니다. 자세한 사항은 CRAN 사이트(https://goo.gl/sRqsXu)를 참조해주세요.

386 오라클 레벨업

093

데이터베이스에서 R에 접근하기

Oracle Data Mining을 사용하면 MDL(속성 평가), apriori(상관 분석), 일반 화 선형 모델, k-means(클러스터 분석), 서포트 벡터 머신 등 다양한 알고리즘 을 활용할 수 있습니다11 . R 패키지를 사용하면 다양한 통계 분석 방법을 사용 할 수 있습니다. 이러한 R 패키지들은 ORE를 활용해 SQL 내부에서 R 스크립트 를 실행할 수 있습니다. 이번 팁에서는 SQL 내부에서 R 스크립트를 실행하는 4가지 방법을 설명합니다. 이 방법을 응용하면 R 패키지의 다양한 통계 분석 방법을 SQL에서 사용할 수 있 습니다. ORE를 사용하려면 Oracle Database Enterprise Edition 옵션 중에 Oracle Advanced Analytics가 필요합니다. 그림 6-5 처리 흐름

데이터베이스

R 환경

R 스크립트 등록된 R 스크립트

❶ R 스크립트 등록

이번 팁에서 다룰 범위 ❷ SQL에서 R 스크립트 호출 ❸ 생성한 스크립트를 출력 ❹ SQL 스크립트 삭제 클라이언트 (SQL*Plus 등)

11 지은이_Oracle Data Mining Application Developer’s Guide 11g Release 2 (11.2) E12218-07 5

Building a Model 11 https://goo.gl/RPQ68w 11 Oracle Data Mining User’s Guide 12c Release 1 (12.1)E53115-01 5 Creating a Model 11 https://goo.gl/3xkmnn

6장 _ 데이터마이닝 Tips 387

표 6-5 이번에 소개할 R 스크립트 조작 No

조작

주요 키워드

R 스크립트 등록

sys.rqScriptCreate()

SQL에서 R 스크립트 호출

rqEval()

작성한 스크립트를 출력

RQ$SCRIPT 뷰

SQL 스크립트 삭제

sys.rqScriptDrop()

▒▒ R 스크립트 등록 R 스크립트를 등록할 때는 sys.rqScriptCreate() 함수를 사용합니다. SQL*Plus 에서 ‘Example1’이라는 이름의 함수를 사용하겠습니다. ● 예제 스크립트

SQL> begin sys.rqScriptCreate(‘Example1’, ‘function() { ID <- 1:10 res <- data.frame(ID =ID, RES=ID / 100) res}’); end; /

▒▒ SQL에서 R 스크립트 호출 SQL에서 R 스크립트를 호출할 때는 rqEval() 함수를 사용합니다. 다음 코드는 SQL*Plus에서 rqEval() 함수를 실행하고, table 함수를 사용해 테이블 형태로 출력했습니다. ● 예제 스크립트 SQL> SELECT * FROM TABLE(rqEval(NULL, ‘SELECT 1 id, 1 res FROM DUAL’, ‘Example1’)); ID RES ---------- ---------1

.01

.02

.03

(생략) 10

388 오라클 레벨업

▒▒ 생성한 스크립트를 출력 작성한 스크립트를 리스트 형태로 출력하려면 SQL*Plus에서 RQ$SCRIPT 뷰를 사용합니다. NAME 필드에 스크립트 이름을 조건으로 지정하면 출력하고 싶은 스크립트를 필터링 할 수 있습니다. ● 예제 스크립트 SQL> SELECT * FROM RQ$SCRIPT; NAME SCRIPT -------------------- --------------------------------------------Example1 function() { ID <- 1:10 res <- data.frame(ID=ID, RES=ID / 100) res}

▒▒ SQL 스크립트 삭제 SQL 스크립트를 삭제할 때는 sys.rqScriptDrop() 함수를 사용합니다. 다음 예 제 스크립트에서는 SQL*Plus에서 ‘Example1’라는 이름의 함수를 삭제합니다. 6

● 예제 스크립트 begin sys.rqScriptDrop(‘Example1’); end; / --삭제 후에 RQ$SCRIPT 뷰를 참조하면 테이블이 없어진 것을 확인할 수 있습니다. SELECT * FROM RQ$SCRIPT;

6장 _ 데이터마이닝 Tips 389

column

R 패키지에 대해

R 패키지를 CRAN(Comprehensive R Archive Network) 에서 확인해보니 집필 시점에 6,585개에 달했습니다. [표 6-6]은 자주 사용할 수 있는 R 패키지를 나타낸 것입니다. 모든 R 패키지를 알기란 불가능 하지만, 이 정도만 알아도 통계 분석에 많은 도움이 될 것입니다. 표 6-6 R패키지의 예

패키지명

설명

arules

상관 규칙 및 빈출 아이템의 마이닝

ccgarch

조건부 상관 GARCH 모델

FactoClass

요인 분석과 클러스터 분석의 조합

hclust

클러스터 분석(최단거리법 등)

homals

동질성 분석

inetwork

네트워크 분석

lars

최소각 회귀

회귀 분석(선형)

mco

다기준 최적화 알고리즘

mfp

다변량 분수 다항식

mvtnorm

다변량 정규 분포 및 T분포

nnet

오차 역전파법

princomp

주 성분 분석(principal component analysis)

rpart

재귀적 분할

sensitivity

감도분석

텍스트 마이닝 패키지

tsfa

시계열 인자 분석

12 지은이_Comprehensive R Archive Network > Software > Packages 12 h ttp://cran.r-project.org/

390 오라클 레벨업

094

R로 스프레드시트에 접근하기

업무에서 다루는 데이터에는 상품 판매 데이터, 고객 정보 데이터, 구매처 재고 데이터 등 여러 가지 종류가 있습니다. 데이터양이 적을 때는 스프레드시트 등을 활용하면 쉽습니다. 대표적인 스프레드시트 소프트웨어로는 MS Office Excel 이 있습니다. 예를 들어 담당자가 고객 정보 데이터를 등록할 때 스프레드시트 소프트웨어를 사용하면 데이터를 쉽게 입력할 수 있을 것입니다. 그리고 고객 정 보를 통계적으로 분석하는 등 데이터를 활용해야 할 경우에는 데이터소스에서 데이터를 추출해 일괄 등록하면 됩니다. 이러한 때 R로 스프레드시트 소프트웨 어의 데이터를 불러오는 방법을 알면 데이터를 효율적으로 활용할 수 있습니다.

▒▒ 엑셀 파일의 데이터를 R로 분석해 그래프 그리기 스프레드시트 소프트웨어를 활용한 데이터 통계 분석은 다음과 같은 순서로 진 6

행합니다. ❶ XLConnect 패키지로 스프레드시트 소프트웨어에 R로 접근하기 ❷ 시트 내부의 데이터를 불러옴 ❸ 통계 분석을 그래프로 그림

이번 팁에서는 통계 분석에 k-means로 클러스터 분석을 해보겠습니다. 클러스 터 분석과 관련한 설명은 컬럼을 참조해주세요.

6장 _ 데이터마이닝 Tips 391

그림 6-6 처리 과정

❷ 데이터 추출

클라이언트

R 스크립트

Microsoft Office Excel 파일

❸ 그래프 그리기

❶ 파일 읽어 들이기

R 스크립트 SQL 실행횟수(/s)

REDO 생성량(Kbyte/s)

R 환경 이번 팁에서 다룰 내용

▒▒ XLConnect 패키지로 엑셀 파일 읽어 들이기 R로 엑셀 파일을 조작할 때는 XLConnect 패키지를 사용합니다. 이 패키지는 Apache POI API(MS Office 형식의 파일을 읽고 쓰는 자바 라이브러리)13 를 6

사용합니다. 엑셀 워크시트 읽고 쓰기, 셀 범위 지정해 읽고 쓰기, 워크시트 생성 과 삭제, 그래프 추가 등의 조작이 가능합니다. 표 6-7 XLConnect 패키지를 사용한 엑셀 파일 조작 예 함수 이름

설명

loadWorkbook

엑셀 파일을 엽니다.

createSheet

엑셀 파일 내부에 시트를 생성합니다.

readWorksheetFromFile

엑셀 파일 내부에 시트를 불러옵니다.

writeWorksheet

엑셀 파일 내부에 시트에 객체를 출력합니다.

saveWorkbook

파일을 저장합니다.

13 지은이_XLConnect 패키지 사용 방법 13 https://goo.gl/YbV3h

392 오라클 레벨업

이외에도 함수는 여러 가지 있습니다. 자세한 사항은 CRAN 사이트14 를 참조 해주세요. 예제 스크립트로 읽어 들인 엑셀 파일에는 SQL 실행 횟수(회/s)와 REDO 생성량(Kbyte/s) 등의 시스템 정보가 저장됩니다. library() 함수로 읽어 들일 패키지는 install.packages() 함수로 미리 설치해야 합니다. 자세한 사항은 팁 098의 컬럼 ‘패키지 설치 방법’을 참조해주세요. 그림 6-7 예제 스크립트로 읽어 들인 엑셀 파일

이번 팁에서 사용할 정보

14 지은이_XLConnect 패키지 사용 방법 14 h ttps://goo.gl/YbV3h7

6장 _ 데이터마이닝 Tips 393

● 예제 스크립트 # 작업 디렉터리를 지정합니다 setwd(“<Excel 파일 경로>”) # Excel 파일 작업 전용 XLConnect 패키지를 읽습니다. library(XLConnect) # 작업 디렉터리의 파일 목록을 취득합니다 files.list <- list.files() # 데이터 프레임 객체를 작성합니다. xl.data <- data.frame() # 파일 목록에 readWorksheetFromFile() 함수를 실행합니다 # “Col6 = file.name”은 6번째 필드에 파일 이름을 저장합니다. for (file.name in files.list) { xl.data <- rbind(xl.data, transform( readWorksheetFromFile(file.name, sheet= 1, header = FALSE, startRow =0, endRow =0, startCol =0, endCol = 0), Col6 =file.name)) }

2. k-means를 사용한 클러스터 분석 6

● 예제 스크립트 # SQL 실행 횟수(/s)를 나타내는 서브셋 데이터 프레임을 추출합니다. w1 <- subset(xl.data, Col2==”SQL 실행 횟수(/s)”, select = c(Col3, Col6)) rownames(w1) <- gsub(“.xlsx”, “”, w1$Col6) # 레코드 이름을 지정합니다. # 필드 이름을 지정합니다. colnames(w1) <- c(“SQL 실행 횟수(/s)”) w1 <- subset(w1, select= c(“SQL 실행 횟수(/s)”)) # 서브셋을 추출합니다. # REDO 생성량(Kbyte/s)의 서브셋 데이터 프레임을 추출합니다. w2 <- subset(xl.data, Col2= =”REDO 생성량(Kbyte/s)”, select = c(Col3, Col6)) rownames(w2) <- gsub(“.xlsx”, “”, w2$Col6) # 레코드 이름을 지정합니다. # 필드 이름을 지정합니다. colnames(w2) <- c(“REDO 생성량(Kbyte/s)”) w2 <- subset(w2, select= c(“REDO 생성량(Kbyte/s)”)) # 서브셋을 추출합니다. # SQL 실행 횟수와 REDO 생성량 데이터 프레임을 결합합니다. w3 <- na.omit(cbind(w1, w2)) # na.omit 함수로 NA값을 없앱니다. # k-means법으로 클러스터 분석을 수행합니다. km.result <- kmeans(w3, 3)

394 오라클 레벨업

# 클러스터 수= 3을 지정합니다.

예제 스크립트 내부의 km.result 객체를 출력하면 다음과 같습니다. ● 실행 결과 > km.result K-means clustering with 3 clusters of sizes 2, 3, 2 Cluster means: SQL 실행 횟수(/s) 1 85000.0 2 256666.7 3 5150.0

REDO 생성량(Kbyte/s) 4621.5 41290.0 722.5

Clustering vector: 시스템A 시스템B 시스템C 시스템D 시스템E 시스템F 시스템G 2 2 1 3 3 1 2 Within cluster sum of squares by cluster: [1] 4.544910e+08 1.356549e+10 3.011125e+05 (between_SS / total_SS= 85.9 %) Available components: [1] “cluster” “centers” “totss” “withinss”“tot.withinss” “betweenss” “size” [8] “iter” “ifault”

표 6-8 실행 결과의 항목 설명

항목

설명

Cluster means

클러스터들의 대표점(평균)을 나타냅니다.

Clustering vector

클러스터 루프 내부의 값을 나타냅니다.

Within cluster sum of squares by cluster

데이터 합계를 나타냅니다.

Available components

클러스터 분석과 관련한 자세한 정보를 저장한 객체를 나타냅니다.

3. 분석 결과를 그래프로 그리기 ● 예제 스크립트 # 그래프 그리기 plot(w3, type = ”n”, main= ”시스템 규모를 기준으로 하는 분류”) # 데이터 플롯 text(w3, rownames(w3), pch = km.result$cluster) # 클러스터 이름 points(km.result$centers, pch = 18) # 중심점 플롯

6장 _ 데이터마이닝 Tips 395

분석 결과를 그래프로 나타내면 다음과 같습니다. 그림 6-8 클러스터 분석 결과

REDO 생성량(Kbyte/s)

시스템 규모를 기준으로 하는 분류

3개의 클러스터가 출력됩니다. ◆는 클러스터의 중심 지점을 나타냅니다.

시스템 A

시스템 F

시스템 E 시스템 D

시스템 G 시스템 B

시스템 C SQL 실행 횟수(/s)

R에서 스프레드시트로 접속하고, 데이터를 뽑고, 통계 해석해서, 그래프를 그리 는 과정을 이해하고 응용하면 더 효율적으로 업무 데이터를 분석할 수 있습니다. 이번 팁에서 분석한 클러스터 분석 방법 이외에도 다양한 분석 방법을 사용할 수 있으므로 꼭 활용해보기 바랍니다. 6 column

클러스터 분석이란?

데이터에서 같은 특징을 가지는 것끼리 정리해 분석하는 통계 방법입니다. 객관적인 기준에 따 라 분류할 수 있어 고객 세분화나 포지셔닝 등 마케팅 조사에 활용합니다. 다음과 같은 두 가지 방법이 있습니다.

① 단층적 클러스터 분석 비슷한 특징을 가진 데이터 집합을 관련 지어 수형도, 즉 덴드로그램(dendrogram)을 그립니 다. 수형도가 서로 관련 있는 데이터 집합을 나타내주므로 쉽게 전체 모습을 파악할 수 있습니 다. 분류 대상이 너무 많은 경우 계산량이 많아 실행 시간이 길어질 수 있습니다. 처리 흐름을 대략적으로 설명하면 <거리가 짧은 것을 선별> → <그것을 하나의 점으로 간주> → <거리를 다 시 계산> → <반복>해서 마지막 한 점을 찍을 때까지 반복합니다. 사용되는 알고리즘으로는 다 음과 같은 것들이 있습니다. 자세한 것은 R Console에서 “?hclust”를 입력해 매뉴얼을 확인 하기 바랍니다.

396 오라클 레벨업

그림 6-9 수형도(덴드로그램)의 예

표 6-9 계층적 클러스터 분석에 사용되는 알고리즘 알고리즘명

설명

최단거리법

가장 짧은 거리를 사용함

최장거리법

가장 긴 거리를 사용함

군평균법

거리의 평균을 구함

중심법

중심과의 거리를 계산

워드법

그룹 내 거리의 최소치를 구함

② 비계층적 클러스터 분석 데이터 집합의 평균을 가지고, 분석자가 사전에 정한 데이터 집합 개수에 따라 분류합니다. 최 대한 데이터 집합이 작아지도록 계산되지만, 처음 선택해 대표가 되는 점에 의해 분석 결과가 달라질 수 있습니다. 분석자가 다시 몇 개의 데이터 집합으로 분류할 것인지를 정해야 하는 번 거로움이 있지만, 대량의 항목을 분류할 때 사용해도 실행 시간이 안정적인 것이 장점입니다. 처리 흐름을 대략적으로 설명하면 <대표가 되는 점을 선별> → <거리를 계산> → <가까운 쪽에 있 는 점들을 그룹으로 분류> → <반복>해서 분석자가 설정한 데이터 집합 수 K개가 될 때까지 반 복합니다. 사용하는 알고리즘은 보통 k-means법을 사용합니다. 자세한 것은 R Console에서 “?kmeans”를 입력해서 매뉴얼을 확인하기 바랍니다.

6장 _ 데이터마이닝 Tips 397

098

히트맵으로 발생 빈도 쉽게 파악하기

필자가 사용했던 시스템 운용의 대부분은 경향 분석을 통해 메모리, 디스크 I/O, CPU 상태 등 데이터베이스가 사용하는 하드웨어 리소스 성능 문제를 초기에 발 견했습니다. 경향 분석의 목적은 시스템의 큰 변화를 알아내는 것입니다. 따라서 그래프 등으로 전체적인 모습을 파악할 수 있는 방법을 알면 도움이 됩니다. 그 방법 중 하나가 값의 크고 작음을 색의 농도로 표현하는 히트맵입니다. 히트맵을 쓰면 어떤 변화가 얼마나 일어나는지를 시각적으로 확인할 수 있습니다. 이번 팁에서는 세그먼트 레벨의 통계 정보 경향 분석 사례를 통해 히트맵 사용 방법을 설명하겠습니다. 그림 6-14 처리 구성 클라이언트

CSV 파일

❶ 데이터 추출

데이터베이스

R 환경 CSV 파일 읽기

그래프 그리기

•데이터 읽기 •축 설정 •크로스 집계 •그래프 그리기 •레이블 설정

❷ 히트맵 생성 表領域ごとの論理読み込みデータブロックの状況 USER TS#2 TS#1 SYSTEM SYSAUX 08 月 10 日 13 時

08 月 10 日 14 時

08 月 10 日 15 時

08 月 10 日 16 時

08 月 10 日 17 時

08 月 10 日 18 時

이번 팁에서 다루는 범위

▒▒ SQL 세그먼트 레벨의 이력통계정보 취득하기 세그먼트 레벨의 이력통계정보는 DBA_HIST_SEG_STAT 뷰에 저장됩니다. DBA_HIST_SEG_STAT 뷰는 데이터 블록의 논리 로딩량과 물리 로딩량 등 V$SEGSTAT에서 얻은 정보 이력을 저장합니다. DBA_HIST_SEG_STAT_OBJ 뷰와 결합해서 테이블 영역과 세그먼트 이름을 참조할 수 있습니다. 잘 알려져 있지 않지만 DBA_HIST_SEG_STAT 및 DBA_HIST_SEG_STAT_OBJ 뷰는 416 오라클 레벨업

Diagnostics Pack 라이선스가 필요한 데이터 딕셔너리 뷰입니다17 . 표 6-16 세그먼트 레벨의 이력통계정보로 취득한 정보의 예 No

정보명

데이터 블록 논리 로딩양

버퍼 비지 대기량

데이터 블록 변화량

데이터 블록 물리 로딩양

데이터 블록 물리 쓰기량

사용된 영역

글로벌 캐시의 버퍼 비지

판독 일관성 데이터 블록의 송신량

최신 데이터 블록 송신량

SQL를 사용해 세그먼트 레벨 통계 정보를 확인하는 예제 스크립트는 다음과 같 습니다. ● 예제 스크립트 SQL> SELECT ‘ROWNUM, BEGIN_INTERVAL_TIME, TABLESPACE_NAME, datum’ FROM DUAL UNION ALL SELECT ROWNUM||’,’||BEGIN_INTERVAL_TIME||’,’||TABLESPACE_NAME||’,’||datum FROM ( SELECT ss.BEGIN_INTERVAL_TIME, sego.TABLESPACE_NAME, SUM(seg.LOGICAL_READS_DELTA) datum FROM DBA_HIST_SEG_STAT seg, DBA_HIST_SNAPSHOT ss, DBA_HIST_SEG_STAT_OBJ sego WHERE seg.SNAP_ID AND seg.DBID

= ss.SNAP_ID = ss.DBID

AND seg.INSTANCE_NUMBER = ss.INSTANCE_NUMBER AND seg.DBID

= sego.DBID

AND seg.TS#

= sego.TS#

17 지은이_집필 시점의 정보이므로 사용할 때는 추가로 매뉴얼을 참고하기 바랍니다. 17 Oracle Database Licensing Information 11g Release 2 (11.2) E47877-05 2 Options and Packs 17 https://goo.gl/2SY3JZ 17 Oracle Database Licensing Information 12c Release 1 (12.1) E49208-06 2 Options and Packs 17 https://goo.gl/M3UNGp

6장 _ 데이터마이닝 Tips 417

AND seg.OBJ#

= sego.OBJ#

GROUP BY ss.BEGIN_INTERVAL_TIME, sego.TABLESPACE_NAME ORDER BY ss.BEGIN_INTERVAL_TIME )dhseg;

앞의 예제 스크립트에서는 워크로드 리퍼지토리의 각 스냅샷마다 데이터 블록 로드량을 CSV 형식으로 출력합니다. 세그먼트 레벨의 이력통계정보는 DBA_ HIST_SEG_STAT 뷰에서, 테이블 영역 이름 등의 세그먼트 정보는 DBA_ HIST_SEG_STAT_OBJ 뷰에서, 워크로드 리퍼지토리의 스냅샷 시각 정보는 DBA_HIST_SNAPSHOT 뷰에서 추출합니다. 용도에 따라 Pluggable 데이터 베이스와 인스턴스 데이터베이스를 식별하는 조건, 기간을 지정하는 조건 등을 추가하기 바랍니다.

▒▒ R로 히트맵 작성하기 세그먼트 레벨 통계 정보의 CVS 형식 데이터를 사용해 R로 히트맵을 생성합 니다. 예제 스크립트는 다음과 같습니다. 세로 축에는 테이블 영역, 가로 축에 는 시간을 배치하고 데이터 블록 논리 로딩량을 크로스 집계합니다. 크로스 집 6

계는 reshape2 패키지의 melt() 함수와 acast() 함수를 사용합니다. 히트맵은 image() 함수를 사용합니다. png 형식의 파일에 출력할 때는 png() 함수를 사 용합니다. dev.off()는 파일 출력을 완료할 때 호출하는 함수입니다. ● 예제 스크립트 # 작업 디렉터리를 지정합니다. setwd(“<작업 디렉터리 경로>”) # 데이터 프레임 가공을 위한 reshape2 패키지를 읽어옵니다. library(reshape2) # 섹션 데이터를 읽어와 매트릭스 형식으로 만듭니다. seg1 <- read.csv(“seg.csv”) seg2 <- melt(seg1, id=c(“ROWNUM”,”BEGIN_INTERVAL_TIME”,”TABLESPACE_NAME”), na.rm = TRUE) seg3 <- acast(seg2, seg2[,2]~seg2[,3],value=seg2[,4],fun.aggregate = sum)

418 오라클 레벨업

# x축과 y축의 값을 지정합니다 x <- (1:nrow(seg3)) y <- (1:ncol(seg3)) # 히트맵을 그립니다. png(“plot01.png”) image(x,y,seg3,col=heat.colors(100), xaxt=”n”, yaxt = ”n”, ann = FALSE) # 레이블을 설정합니다 axis(side = 1,labels=c(rownames(seg3)),at=1:nrow(seg3),cex.axis = 0.8,las = 1) axis(side = 2,labels=c(colnames(seg3)),at =1:ncol(seg3),cex.axis = 0.8,las = 1) title(main = ”테이블 영역의 논리 로딩량 데이터 블록 상태”) dev.off()

예제의 출력 결과는 다음과 같습니다. 세로 축에 테이블 영역을, 가로 축에 시간 을 배치했습니다. 데이터 블록의 논리 로딩량이 많을수록 밝은 색으로 표시됩니 다. [그림 6-15]에서는 TS#2라고 하는 테이블 영역의 17시에서의 데이터 블록 논리 로딩량이 두드러집니다. 이처럼 히트맵 등의 그래프화 방법을 사용해 전체 적인 모습을 쉽게 파악할 수 있습니다. 그림 6-15 처리 결과 그래프 테이블 영역별 논리 로딩량 데이터 블록 상태

user TS#2 TS#1 SYSTEM SYSAUX |

8월 10일 13시

8월 10일 14시

8월 10일 15시

8월 10일 16시

8월 10일 17시

8월10일 18시

6장 _ 데이터마이닝 Tips 419

column

패키지 설치 방법

추가 패키지를 사용하려면 install.packages 명령어를 실행합니다. install.packages 명령어 를 실행하면 CRAN의 미러 선택 화면이 나옵니다. 내려받을 곳을 임의로 선택해 패키지를 설 치합니다. ● 실행 예 > install.packages(“reshape2”) --- 현재 세션에서 사용할 CRAN 미러를 선택해 주세요 . --URL ‘http://cran.rstudio.com/bin/windows/contrib/3.0/reshape2_1.4.zip’ 을 시도하고 있습니다. Content type ‘application/zip’ length 498179 bytes (486 Kb) 열린 URL downloaded 486 Kb 패키지 ‘reshape2’를 성공적으로 압축 해제하였고 MD sums이 확인되었습니다.

의존 관계의 패키지를 함께 인스톨할 경우에는 ‘dep =T ’ 매개변수를 지정합니다. ●예 > install.packages(“reshape2”, dep = T)

18 옮긴이_‘Korea (Seoul 1)’ 또는 ‘Korea (Seoul 2)’를 선택하면 다운 속도가 빠릅니다. 의존 관계에 있는 패키지를 한꺼번에 설치하려면 ‘dep=T’ 매개변수를 지정합니다.

420 오라클 레벨업

099

3차원 그래프를 사용해 유사도 파악하기

몇몇 문서의 유사성을 판단하기 어려웠던 적은 없나요? 예를 들어 FAQ 작성을 떠올려봅시다. FAQ를 효율적으로 작성하려면 자주 나오는 질문과 답변을 미리 정리해야 합니다. 그런데 실제로 해보면 과거 고객과 사내 질문 데이터를 참조 해서 유사성이 있고 빈도가 높은 질문을 찾기란 매우 어려운 일입니다. 이러한 때 R을 사용하면 편리합니다. ORE를 사용하려면 Oracle Database Enterprise Edition의 옵션인 Oracle Advanced Analytics가 필요합니다. 그림 6-16 처리 구성 ● 과거의 질문 내용의 유사도를 파악해 FAQ를 효율적으로 작성하기 No.

과거 질문 내용

오라클 제품은 어떤 라이선스로 구입할 수 있나요?

Oracle Database 12c를 사용하려면 어 떤 라이선스를 구입해야 하나요?

버전 업그레이드를 할 때 추가 구입이 필

R을 사용해서 문서의 유사성을 파악할 수 있도록 한다.

자주 들어오는 질문을 FAQ로 준비한다. No. 자주 있는 질문 1

오라클 제품은 어떤 라이선스로 구입 할 수 있나요?

오라클 제품은 어떤 형태로 라이선스가 구성되어 있나요?

요한가요? Q4

오라클 데이터베이스의 라이선스 가격 측 정 방법을 알려주세요.

오라클 제품은 어떤 형태로 라이선스가 구 성되어 있나요?

버전을 업그레이드하려면 무엇을 추가로 구입해야 하나요?

오라클 데이터베이스를 사용하고 싶은데

No. 자주 있는 질문

클라이언트 라이선스가 따로 필요한가요?

오라클 제품은 어떤 라이선스로 구입 할 수 있나요?

오라클 제품은 어떤 형태로 라이선스가 구성되어 있나요?

오라클 제품의 라이선스 체계를 알려주

Patch Set Release만 구하고 싶은데 이

세요.

것만 구할 수 있나요? Q10

HA 구성에서 오라클을 검증 중입니 다. 필요한 라이선스가 몇 개인지 알려 주세요.

6장 _ 데이터마이닝 Tips 421

▒▒ 데이터베이스에 있는 과거 질문 내용을 참조하기 데이터베이스에 접속해 과거 질문 내용을 저장한 token_freq 테이블을 참조하 는 getToken_freq 함수를 생성하고 실행합니다19 . ● 예제 스크립트 .libPaths(“<R 패키지 저장 경로>”)

# 경로를 지정합니다.

library(ORE)

# ORE 패키지를 읽어옵니다.

# 데이터베이스의 접속자를 설정합니다. # 유저 “user01”, 패스워드 “Oracle100”으로 데이터베이스에 접속합니다. if (!ore.is.connected()) { ore.connect(user = ”user01”, sid = ”orcl”, host = ”localhost”, password = ”Oracle100”, port = 1521, all= TRUE) } # 과거 질문 내용을 저장한 token_freq 테이블을 참조하는 getToken_freq 함수를 생성합니다. getTokenFreq <- function() { con <- dbConnect(Extproc()) rs <- dbSendQuery(con, “SELECT token, NVL2(q1,q1,’0’) q1, NVL2(q2, q2,’0’) q2, NVL2(q3, q3, ‘0’) q3, NVL2(q4, q4, ‘0’) q4, NVL2(q5, q5, ‘0’) q5, NVL2(q6, q6, ‘0’) q6, NVL2(q7, q7, ‘0’) q7, NVL2(q8, q8, ‘0’) q8, NVL2(q9, q9, ‘0’) q9, NVL2(q10, q10, ‘0’) q10

FROM (SELECT query_id, token, COUNT(offset) freq FROM token_freq GROUP BY query_id, token) PIVOT (SUM(freq) FOR query_id IN(1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4, 5 AS q5, 6 AS q6, 7 AS q7, 8 AS q8, 9 AS q9, 10 AS q10))”) df <- fetch(rs) } # getTokenFreq 함수를 실행합니다 token_freq <- ore.doEval(getTokenFreq, ore.connect = TRUE) # getTokenFreq 함수의 실행 결과를 데이터 프레임에 저장합니다 df_tf <- as(ore.pull(token_freq), “data.frame”)

getToken_freq 함수에서 실행한 SQL 구문은, token_freq 테이블의 query_id 필드를 피벗 필드로 전개해 토큰별 빈출도를 계산합니다([그림 6-17] 피벗 처리

19 옮긴이_이때 전제로 CONNECT, RESOURCE, CREATE ANY DIRECTORY, CTXAPP, RQADMIN의 권 한을 부여한 계정 ‘사용자 이름：user01, 패스워드：Oracle100’을 사용합니다. 422 오라클 레벨업

참조). 참고로 과거의 질문 내용을 데이터베이스에 저장하는 예제 스크립트는 컬 럼에서 설명하겠습니다. 그림 6-17 피벗 필드 전개 ● R에서 getToken_freq 함수로 추출한 정보

● token_freq 테이블 QUERY_ID TOKEN

FREQ

TOKEN

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

오라클

제품

라이선스

단체

구입

(생략)

12C

구성

DATABASE

검사

라이선스

의

(생략)

검사

업

RELEASE 0

버전

라이선스

무료

오라클

(생략)

▒▒ 질문 내용의 통계 분석하기 질문 내용이 비슷한지 여부는 내용을 분석해보면 거리가 얼마나 가까운지로 알 수 있습니다. R은 거리 계산을 위한 dist() 함수를 제공하는데, 그 덕분에 매우 쉽게 거리를 구해 유사도를 알아낼 수 있습니다. 디폴트로 사용되는 알고리즘은 euclidean(유클리드 거리)입니다. ● 예제 스크립트 # dist 함수로 거리 행렬(모든 문서들 간의 거리)를 계산합니다. df_tf_dist <- dist(t(df_tf[,2:11]))

``예제 스크립트의 df_tf_dist 객체를 출력하면 다음과 같습니다.

6장 _ 데이터마이닝 Tips 423

● 실행 결과 예 > df_tf_dist Q1

Q2 3.651255 Q3 2.868578 3.828528 Q4 2.566504 3.462858 2.765686 Q5 2.976762 3.933263 3.371592 3.037406 Q6 2.851582 3.764508 2.440970 2.665962 3.281830 Q7 2.836420 3.697559 2.997057 2.530371 3.268664 2.637153 Q8 2.100794 3.349710 2.544682 1.781463 2.559731 2.424497 2.361438 Q9 3.872713 4.718064 4.143904 3.887925 4.342697 3.900463 3.884351 3.726499 Q10 3.335957 4.147255 3.505411 3.115820 3.740284 3.408994 3.337497 2.942741 4.419574

▒▒ 3차원 그래프 그리기 2차원 데이터를 3차원 데이터로 변환할 경우에는 고전적인 다차원 척도법을 사 용합니다. R에는 cmdscale() 함수가 있어 원하는 차원으로 간단하게 변환할 수 있습니다. ● 예제 스크립트

# 차원 최대값으로 “3”을 지정합니다. df_tf_dist_3d <- cmdscale(df_tf_dist, 3)

예제 스크립트 내부의 df_tf_dist_3d 객체를 출력하면 다음과 같습니다. ● 실행 결과 예 > df_tf_dist_3d [,1] Q1 0.107548330 Q2 1.250569981 Q3 0.272458106 Q4 0.279447447 Q5 0.515627025 Q6 0.012758733 Q7 0.008258848 Q8 0.256128199 Q9 -3.084053864 Q10 0.381257196

424 오라클 레벨업

[,2] -0.2463550 2.5721105 -0.5589927 -0.2029270 -0.3343826 -0.4721418 -0.2644638 -0.2696527 0.7636586 -0.9868535

[,3] 0.389936062 -0.397958320 0.515825323 -0.003778523 1.216500124 0.397153694 0.041019378 0.210956343 -0.153810832 -2.215843250

마지막으로 3차원 플롯 그리기 패키지인 ‘rgl’을 사용합니다. ● 예제 스크립트 library(rgl)

# rgl 패키지를 읽어옵니다.

x <- df_tf_dist_3d[,1]

# x값을 설정합니다.

y <- df_tf_dist_3d[,2]

# y값을 설정합니다.

z <- df_tf_dist_3d[,3]

# z값을 설정합니다.

plot3d(x, y, z, type = ”s”, col = rainbow(10), size = 4) # 3차원 플롯으로 만듭니다. (type = ”s”는 구체) text3d(x, y, z, text = colnames(df_tf[2:11]),

# 그래프의 레이블을 표시합니다.

adj=2, justify = ”left”)

3차원 플롯의 결과는 다음과 같습니다. 그래프 내부의 구체는 하나하나의 문서를 나타냅니다. 공간 안에 배치된 구체 중 가까운 곳에 배치된 구체는 문서의 유사성이 높다고 판단합니다. 다음 그림에는 질문 내용의 Q1과 Q5, 그리고 Q4와 Q10이 서로 가까운 곳에 배치되어 있습니 다. 이처럼 3차원 그래프를 그리면 유사도를 쉽게 파악할 수 있습니다. 그림 6-18 분석 결과 그래프

또한 rgl 패키지로 만든 3차원 그래프는 마우스 조작으로 축을 회전시킬 수 있 습니다. 이렇게 축을 회전시키면 여러 각도에서 유사성을 파악할 수 있지요. 예

6장 _ 데이터마이닝 Tips 425

w w w. h a n b i t . c o . k r

이것이 프로그래밍이다! 저자 직강 동영상 제공!

이것이 안드로이드다

이것이 C언어다

이것이 자바다

진정한 안드로이드 개발자로 이끌어줍니다.

세상에 없던 새로운 C언어 입문서 탄생!

가장 중요한 프로그래밍 언어를 하나 배워야 한다면, 결론은 자바다!

SDK 5.0 롤리팝 호환!

삼성, LG에서 펼쳐졌던 전설의 명강의를 풀타임 동영상 강좌로!

중급 개발자로 나아가기 위한 람다식, JavaFX, NIO 수록

이보다 더 확실한 방법은 없다, 칠판강의 전체 동영상 강좌 유투브 전격 공개!

자바의 모든 것을 알려주는 인터넷 강의 궁금한 것은 카페에서!

cafe.daum.net/superdroid

http://goo.gl/tJK3Tu

cafe.naver.com/thisisjava

박성근 저 | 1,164쪽 | 45,000원

서현우 저 | 708쪽 | 25,000원

신용권 저 | 1,224쪽 | 30,000원

책만 보고, 동영상 강좌로도 만족하지 못했다면 Daum 카페 '슈퍼드로이드'에서 만나요

w w w. h a n b i t . c o . k r

지금은 모던 웹 시대!

모던 웹 디자인을 위한

모던 웹을 위한

HTML5 + CSS3 입문 HTML5 분야 부동의 1위 도서

JavaScript + jQuery 입문

HTML5 표준안 확정에 맞춘 완전 개정판의 귀환!

자바스크립트에서 제이쿼리, 제이쿼리 모바일까지 한 권으로 끝낸다!

HTML5 권고안과 최신 웹 브라우저 환경 대응

시대의 흐름에 맞춰 다시 쓴 자바스크립트 교과서

윤인성 저 | 624쪽 | 30,000원

윤인성 저 | 980쪽 | 32,000원

모던 웹을 위한

HTML5 + CSS3 정복

Node.js

프로그래밍 페이스북, 월마트, 링크드인은 왜 Node.js를 선택했는가?

필요한 것만 배워 바로 현장에서 쓰는 HTML5

이 물음에 대한 답은 Node.js가 보여주는 빠른 처리 능력 때문이다.

순서대로 읽으며 실습할 수 있는 HTML5 자습서

윤인성 저 | 484쪽 | 25,000원

김상형 저 | 700쪽 | 32,000원

w w w. h a n b i t . c o . k r

Hanbit eBook

Realtime w w w. h a n b i t . c o . k r / e b o o k

DRM free! 어떤 디바이스에서도 자유롭게

eBook Oriented! 전자책에 꼭 맞는 최적의 내용과 디자인

Hanbit eBook

Realtime 70

Realtime 89 49

MFC 프로그래밍 주식분석 프로그램 만들기 김세훈 지음

Hanbit eBook

Realtime 90

Realtime 92 49

자바 개발자를 위한

Vert.x JavaScript Promise azu지음 /주우영옮김

애플리케이션 개발 모바일/웹 메시징 STOMP와 MQTT로 개발하는 IoT 모바일/웹 애플리케이션 Mobile and Web Messaging 제프 메스닐 지음 / 조건희 옮김

이연복 지음

w w w. h a n b i t . c o . k r

즐거운 상상이 가득! 2015년 화제의 신간

전자부품 백과사전 vol.1 찰스 플랫 지음 / 배지은 옮김 / 30,000원

취미공학에 필요한 핵심 전자부품을 사전식으로 정리한 안내서.

전자부품 백과사전 vol.1 찰스 플랫 지음 / 배지은 옮김 / 30,000원

취미공학에 필요한 핵심 전자부품을 처음 시작하는 센서 사전식으로 정리한 안내서. 전자부품 백과사전 vol.2

찰스 플랫 지음 / 가격미정

키모 카르비넨, 테로 카르비넨 지음 임지순 옮김 / 13,000원

세상을 수치로 읽어내는

<전자부품 백과사전> 시리즈의 두 번째 도서다.

부품인 센서를 알려주 는 책. 이 책을 통해 자신

처음 만의 프로젝트에 다양한 Zero to Maker

: 누구나 메이커가 될 수 있다

전자부품 백과사전 vol.2

찰스 플랫 지음 / 가격미정

데이비드 랭 지음 / 장재웅 옮김 / 14,000원

센서를 사용해보자. 키모 카르비넨, 테로 카르비넨 지음 임지순 옮김 / 13,000

세상을 수치로 읽어

일반인에서 메이커로. 날백수에서 무인 잠

<전자부품 백과사전> 시리즈의 두 번째 도서다.

수정 회사 CEO가 된 사나이, 데이비드 랭의 메이커 도전기.

시작하는

Make: 센서

부품인 센서를 알

키모 카르비넨, 테로 카르비 는 책. 이 책을 통해 넨, 빌 발토카리 지음 / 가격미정 만의 프로젝트에 다

필수 전자부품인 센서를 센서를 사용해보자

Zero to Maker

마이크로 컨트롤러 보드

: 누구나 메이커가 될 수 있다 데이비드 랭 지음 / 장재웅 옮김 / 14,000원

Maker Pro 일반인에서 메이커로. 날백수에서 무인 잠 존 베이첼 지음 / 가격미정

수정 회사 CEO가 메이커라면 된 사나이,반드시 데이비드 읽어야 할 필수 계발 랭의 메이커 도전기. 서. 프로 메이커들과의 인터뷰 및 에세이 수록.

에 응용하는 방법을 담 았다.

Make: 센서 키모 카르비넨, 테로 넨, 빌 발토카리 지음 / 가격미정

필수 전자부품인 센 프로젝트로 배우는 라즈베리 파이

도날드 노리스 지음 / 임지순 옮김

다양한 실전 프로젝트를 통해 라즈베리 파이를 쉽고 재미있게 배워본다.

Maker Pro

존 베이첼 지음 / 가격미정

메이커라면 반드시 읽어야 할 필수 계발

마이크로 컨트롤러

에 응용하는 방법 았다.