효율적인성능관리와품질강화전략 WareValley Database Audit and Protection [ DB 접근통제 ] Database Encryption [ DB 암호화 ] Database Vulnerability Assessment [ DB 취약점분석 ] Database SQL Query Approval [ DB 작업결재 ] Database Performance Monitoring and Management [ DB 성능관리및개발 ]
Contents I. 기본적인성능관리도구 II. DBMS_XPLAN 패키지활용 III. AWR을활용한성능관리 IV. 데이터품질관리
I. 기본적인성능관리도구 Explain Plan Auto Trace SQL Trace Dynamic View
기본적인성능관리도구 실행계획은 SQL 문장튜닝을위한핵심적인정보를제공한다. explain plan into PLAN_TABLE set statement_id = ID for < 쿼리 >; 실행계획에는 Join Order ( 어떤테이블을먼저읽었는지 ), Access Method (Full Table Scan, Index Unique Scan, Index Range Scan), Join Method (Nested Loop Join, Hash Join, Sort Merge Join 등 ) 가포함되어있다.
기본적인성능관리도구 Auto Trace 의 Statistics 활용 sql*plus 의 Auto Trace 를활용하면실행계획외에 SQL 통계정보를조회할수있다. 실행계획은쿼리실행을필요로하지않으나 SQL 실행통계를보여주기위해서는실제 쿼리를수행하므로긴시간이소요될수있다. SQL 통계정보의내용 recursive calls db block gets consistent gets physical reads redo size SQL*Net roundtrips to/from client sort (memory) sort (disk) rows processed
기본적인성능관리도구 SQL Trace 의활용 SQL Trace는실행계획뿐만아니라 SQL문실행단계별로처리된 Row 수, 읽은 Block 수, CPU Time을제공하기때문에더세심하고전문적인튜닝작업에사용된다. 자기세션에 Trace 걸기 alter session set sql_trace = TRUE; alter session set events 10046 trace name context forever, level 1 ; 다른세션에 Trace 걸기 exec dbms_system.set_ev (<sid>,<serial>,10046,12, ); exec dbms_monitor.session_trace_enable( session_id => 120, serial_num => 5, waits => TRUE, binds => TRUE);
기본적인성능관리도구 SQL Trace 에서기본적으로제공하는정보 Trace Level 에따라아래정보외에바인드변수값과대기이벤트정보가추가된다. select d.dname, e.ename, e.job from dept d, emp e where d.deptno = e.deptno SQL 문장 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 3 8 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.03 0.03 0 3 8 14 SQL 통계 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 32 (SCOTT) 기타정보 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 HASH JOIN 14 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP' 4 TABLE ACCESS (FULL) OF 'DEPT' 실행계획
기본적인성능관리도구 Orange Trace Tool 에서제공하는 Trace 상세정보 Trace 파일에있는모든 SQL 문과각각의 SQL 문간의 Hierarchy 정보 대기이벤트를기준으로해당대기이벤트가발생한 SQL 문정보제공
기본적인성능관리도구 Dynamic View 의활용 v$sql : 라이브러리캐시에캐싱되어있는각 Child 커서에대한수행통계정보제공 v$sql_plan : 9i 부터제공. Runtime Plan 제공 v$sql_plan_statistics : Row Source 통계정보제공 v$sql_bind_capture : 10g 부터제공. 바인드변수값제공
기본적인성능관리도구 Orange SQL Monitor 와 Plan Tool 의연계 SQL Monitor 에서 Plan Tool 로연동시 Plan Tool 에서기본적으로제공하는쿼리와 플랜정보외에런타임플랜정보와바인드변수정보자동제공. 바인드변수값제공 런타임플랜제공
Ⅱ DBMS_XPLAN 패키지의활용 예상실행계획의출력 실제실행계획의출력 실제실행계획에 Row Source 별통계출력 AWR에저장된 SQL의실행계획출력
DBMS_XPLAN 패키지의활용 DBMS_XPLAN 패키지로예상실행계획출력 select * from (dbms_xplan.display( PLAN_TABLE, <statement_id>, TYPICAL )); <statement_id> 대신에 null 을입력하면가장최근 explain plan 명령에사용한 쿼리의실행계획을보여준다. dbms_xplan 패키지는 9.2 버전부터사용이가능하다. dbms_xplan.display 의세번째 파라미터인자로는 BASIC, SERIAL, TYPICAL, ALL 세번째파라미터의더섬세한 제어를위해 ROWS, BYTES, COST, PARTITION, PARALLEL, PREDICATE, PROJECTION, ALIAS, REMOTE SQL, NOTE
DBMS_XPLAN 패키지의활용 DBMS_XPLAN 패키지로실제실행계획출력 select * from (dbms_xplan.display_cursor( <sql_id>, <child_no>, BASIC ROWS BYTES COST PREDICATE )); 첫번째, 두번째인자를 null 로주면가장최근에수행한 SQL을찾는다.
DBMS_XPLAN 패키지의활용 DBMS_XPLAN 패키지로실제실행계획에 Row Source 별통계출력 수행하는쿼리에 /*+ gather_statistics */ 힌트추가혹은세션레벨에서 statistics_level 의값을 all 로설정 select * from (dbms_xplan.display_cursor( <sql_id>, <child_no>, ALLSTATS )); v$sql_plan_statistics_all 뷰의 last_output_rows 컬럼에서 Row Source 별수행통계제공 select last_output_rows from v$sql_plan_statistics_all where sql_id = :sqlid and child_number = :childno order by id;
DBMS_XPLAN 패키지의활용 DBMS_XPLAN 패키지로 AWR 에저장된 SQL 의실행계획출력 select * from table (dbms_xplan.display_awr( gpv3kb4n2f2q1,null,null, SERIAL );
Ⅲ AWR 을활용한성능관리 AWR? AWR 보고서 ADDM 보고서 ASH 보고서
AWR 을활용한성능관리 AWR (Automatic Workload Repository) MMON 백그라운드프로세스에의해데이터가수집되어 SYSAUX 테이블스페이스에저장된다. 기본적으로 1시간단위로수집되고수집된데이터는 7일동안보관되며항목별수집 SQL 수는 30개이지만이러한설정의변경은가능하다. AWR에의해수집되는데이터 DB 대기이벤트및통계정보 시스템통계정보 데이터베이스부하정보 SQL 수행정보 Active Session 정보 DBMS_WORKLOAD_REPOSITORY 패키지를사용하여스냅샷생성및설정, 베이스라인생성및삭제와같은스냅샷관리기능을제공한다.
AWR 을활용한성능관리 AWR 보고서생성 보고서생성에필요한권한 SELECT ANY DICTIONARY 권한 SYS.DBMS_WORKLOAD_REPOSITORY 패키지실행권한 보고서종류별사용되는스크립트 사용용도 AWR DB 보고서생성 AWR DB 비교보고서생성 AWR SQL 보고서생성 AWR 정보검색보고서 ASH 보고서생성 ADDM 보고서생성 스크립트 awrrpt.sql, awrrpti.sql awrddrpt.sql, awrddrpi.sql awrsqrpt.sql awrsqrpi.sql awrinfo.sql ashrpt.sql, ashrpti.sql addmrpt.sql, addmrpti.sql
AWR 을활용한성능관리 AWR 보고서내용 (1) AWR 보고서내용 Report Summary : AWR 수행정보및인스턴스환경, 상태를요약해서제공. Cache Size Load Profile Instance Efficiency Percentages Buffer Nowait %, Redo Nowait %, Buffer Hit %, Library Hit %, Execute to Parse %, Soft Parse %, Parse CPU to Parse Elapsed % Shared Pool Statistics Top 5 Timed Foreground Events Host CPU Instance CPU Memory Statistics
AWR 을활용한성능관리 AWR 보고서내용 (2) AWR 보고서내용 Wait Events Statistics SQL Statistics SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets SQL ordered by Reads SQL ordered by Executions SQL ordered by Parse Calls SQL ordered by Sharable Memory SQL ordered by Version Count Complete List of SQL Text
AWR 을활용한성능관리 AWR 보고서내용 (3) AWR 보고서내용 Instance Activity Statistics IO Stats Buffer Pool Statistics Advisory Statistics Wait Statistics Undo Statistics Latch Statistics Segment Statistics Dictionary Cache Statistics Memory Statistics Resource Limit Statistics init.ora Parameters
AWR 을활용한성능관리 ADDM 보고서 ADDM 은 AWR 데이터를이용해 DB의문제점을자동으로분석해주는기능이다. ADDM 보고서는 DBA_HIST_SYS_TIME_MODEL 딕셔너리에서보여지는 DB Time을기준으로 DB Time에가장많은영향을주는요소에대해서내부로직에의해점검을수행한후권고안을제시한다. ADDM 보고서에명시된 SQL 이반드시악성 SQL 이거나튜닝의여지가있는 SQL이라는의미는아니므로직접 SQL을점검하여튜닝가능여부를판단해야함.
AWR 을활용한성능관리 ASH 보고서 (1) 오라클 10g 부터 Active Session History 를제공하여세션및 SQL 에대한정보 검색을제공하며문제발생시점에 AWR, SQL Trace 을생성하지못한경우에특히 유용하다. ASH 데이터 샘플링시간과샘플링 ID 세션정보, User 명, 트랜잭션 ID 수행중인 SQL 정보 현재세션의상태정보 병렬 Slave 세션일때 QC 정보를찾을수있게함. 현재세션의진행을막고있는세션정보 현재발생중인대기이벤트정보 현재발생중인대기이벤트의파라미터정보 해당세션이현재참조하고있는오브젝트정보
AWR 을활용한성능관리 ASH 보고서 (2) ASH 데이터의특징 AWR 데이터에비해수집되는정보가적다. 짧은시간동안발생한문제를파악하는데유용하다. 샘플세션만저장하므로성능문제발생시데이타가없을수있다. ASH 보고서의내용 Top Events Load Profile Top SQL Top Sessions Top Objects/Files/Latches Activity Over Time
Ⅳ 데이터품질관리 데이터품질? 데이터품질진단및개선
데이터품질이란? 데이터품질이란? Consistently meeting all knowledge worker and end-customer expectations through data and data services to accomplish enterprise and customer objectives. - Larry P. English 데이터를활용하는사용자의다양한활용목적이나만족도를지속적으로충족시킬수있는수준
데이터품질관리란? 데이터품질이란? Structure( 구조 ) 데이터사양및메타데이터에대한품질 표준화 & 모델링관점 데이터값이업무를수행할수있도록정의되어있는가? Value( 값 ) 데이터품질관리란? 데이터값의정확성에대한품질은? 완전성, 유효성, 일관성, 정확성관점 데이터값이정확하게정의된업무규칙을준수하는가? Presentation( 활용 ) 지식작업자에게전달되는정보제품으로서의품질은? 적시성, 접근성, 편리성, 활용성관점 필요할때즉시제대로된정보를얻을수있는가? 데이터의품질을획득하고이를지속적으로유지 / 개선시키는일련활동
데이터품질현황 데이터가중요한자산으로서전략적가치에대한인식이높아지고있는반면, 저품질데이터로인한손실과비용의낭비는줄어들지않고있어데이터품질관리요구가증대 개인연금가입자의정보오류 30 만 9,825 건, 이로인해보험료징수오류는 690 억원에이른다 (09 년한국일보 ) 급속한환경변화로신속 / 정확한데이터요구증대 데이터인터페이스증가 데이터종류및양증대 일본공적보험연금보험료납부기록 5,000 만건누락이발견돼 상당수가입자들이피해 ( 07 년국민일보 ) 잘못된데이터로재무제표를수정발표할경우시가총액감소비율이발표 1 일후평균 9.5% ( 딜로이트컨설팅 ) 데이터품질저하로인해발생하는손실비용은전체예산의 10 15% ( 05 년전자신문 )
데이터품질관리 저품질데이터로인한비용증가로데이터품질관리요구가증대 지속적인품질관리가중요 1 품질진단대상테이블선정 2 3 데이터프로파일링업무규칙 (BR) 도출 (BR 설계서 ) 통계적기법을활용한데이터전수검사 지표별오류추정데이터도출 업무규칙에따른 BR 도출 BR정의 BR정의서서 반복수행및지속공정 6 품질진단결과종합 / 개선활동 오류추정데이터분석 5 품질측정 4 통합된결과를지표별로계량화 오류리포트작성 오류데이터확인및원인분석 BR 을적용한품질진단
데이터품질진단 Entity Relation Diagram Schema 내의모든 Entity 관계를 Diagram 으로표현하며, 많은종속관계를쉽게판단할수있고, Entity 와특정 Attribute 와의속성도한눈에쉽게알아볼수있도록표현하고있다. Scripts => Entity Relation Diagram
데이터품질진단대상 Entity 선정 ERD, 도메인정의서, 코드정의서, 테이블명세서, 컬럼정의서등관련문서분석및실무자면담을통해진단대상테이블및컬럼을선정 대상업무별테이블을정하고, 가급적많은컬럼을대상으로데이터를진단함
데이터품질진단대상 Object Report 생성 각종 Object 를표준 Report 형식에맞게재구성하여, 결과물로서각종문서의체계적관리가가능하다. Table Report/ View Report/ Object List Report 등이있으며각 Object 의생성시간 Last DDL Time 등으로버전관리도가능하다.
데이터품질진단대상데이터검증 Data 자체의검증을통해 Data 결함을분석하고이후에러로발생할수있는요소들을분석차원에서지원한다. 분석항목 - Distinct Count/ Null count/ Space Count/ Minimum Value/ Maximum Value,.. 각종 Garbage 데이터지원또한이러한요소데이터를기반으로 Summary/ Patterns/ Distinct Value List 등의다양한분석자료를제공한다.
데이터프로파일링 선정된테이블데이터값에대해통계적기법을활용한다양한분석을수행 누락값, 비유효값, 중복값, 무결성위반등을분석 출처 : http://www.dqc.or.kr/
데이터프로파일링기법 칼럼분석 : 총건수, NULL건수, 스페이스건수, MIN/MAX등분석 패턴분석 : 데이터의패턴을분석하여표준패턴에위배된값들을확인
데이터프로파일링기법 날짜분석 : 날짜타입의데이터에대하여위배된값들을확인 코드분석 : 개별코드에대하여코드정보와코드값들을확인 관계분석 : 키값에대하여부모자식간관계확인하여참조무결성을확인
데이터품질지표 (1/2) 데이터결함을최소화하기위해지속적으로점검 & 관리해야할품질평가기준 품질지표세부품질지표품질기준설명활용예시 개별완전성컬럼값은누락이없어야한다. 고객의아이디는 NULL 일수없다. 완전성 조건완전성 조건에따라컬럼값은항상존재해야한 다. 기업고객의경우사업자등록번호가 NULL 일수없다. 범위유효성 컬럼값은주어진범위내에존재해야한 다. 기준점좌표각은 ' 360' 초과 '360' 미만까지의값을가진다. 유효성 코드유효성 날짜유효성 컬럼값이코드일경우에는유효한코드값 을가져야한다 컬럼값이날짜유형일경우에는유효한날 짜값을가져야한다. 고객의상태코드는 01: 활동, 02: 휴면, 03: 정지, 04: 탈퇴 값을가진다. '99991231', '20080231' 은유효하지않은값이다. 형식유효성 컬럼값이정해진형식과일치하는값을 가져야한다. 주민번호형식은 999999 9999999' 의형식이어야한다. 정확성 최신성 입력정확성 데이터의발생, 수집, 그리고갱신주기를 유지해야한다. 컬럼값은오탈자없이정확히입력해야 한다 고객의현재값은고객변경이력의마지막 ROW 와일치해야한다. 고객의한글성명은적어도한글두자이상을포함하되, 유니코드 (UTF 8) 허용문자세트내존재해야한다. 출처 : http://www.dqc.or.kr/
데이터품질지표 (2/2) 데이터결함을최소화하기위해지속적으로점검 & 관리해야할품질평가기준 품질지표세부품질지표품질기준설명활용예시 단독유일성컬럼은유일한값을가져야한다. 고객의이메일주소는유일해야한다. 유일성 조건유일성업무조건에따라컬럼은유일해야한다. 교육과정의오프라인강의실시여부가있으면강의실코드, 강의시작일시, 강사코드가모두동일한레코드는존재하지않는다. 일관성 복합유일성컬럼은조합시유일해야한다. 참조무결성 이행데이터일관성 중복컬럼일관성 선후관계일관성 테이블간의컬럼값이참조관계에있을경우에는무결성을유지해야한다. 시스템간데이터가이행되거나가공되는경우에는관련데이터는상호일치해야한다. 관리목적으로중복컬럼을임의생성하여활용하는경우에는중복컬럼값은일치해야한다. 복수의컬럼값이선후관계에있을경우에는선후관계를지켜야한다. 고객마스터의성명, 생년월일, 주소가모두동일한레코드는존재하지않는다. 대출상세내역의대출원장번호는대출원장의대출원장번호에존재해야한다. 운영계의현재가입고객수와 DW 의최신시점고객수는일치해야한다. 주문의주문번호와고객번호는배송의주문번호와고객번호가서로일치해야한다. 시작일은종료일이전시점이어야한다. 계산 / 집계일관성 업무규칙일관성 단일컬럼값이다수컬럼의계산된값일경우에는계산값이정확해야한다. 컬럼이업무적으로복잡하게연관된경우에는관련업무규칙에일치해야한다. 월통계테이블의매출액은기산일이현월인매출액의총합과일치해야한다. 지급원장의지급여부가 Y' 이면지급원장의지급일자는신청일보다이후시점이어야하고 NULL 이아니어야한다. 출처 : http://www.dqc.or.kr/
업무규칙도출 문서검토및관계자인터뷰등을통해규칙도출 도메인분류 테이블명 컬럼명 도메인 고객기본정보 국적코드 코드 고객기본정보 영문고객명 명칭 고객기본정보 CEO여부 분류 고객기본정보 국적코드 코드 고객기본정보실명번호구분코드 코드 고객기본정보 고객분류코드 코드 고객기본정보인터넷뱅킹가입일 날짜 고객기본정보 고객번호 번호 고객기본정보 전행고객번호 번호 보험계약정보 고객번호 번호 보험계약정보 보험료누계 금액 보험계약정보 최종납입회차 수량 업무규칙업무규칙고객기본의국적코드의개별완전성검증고객기본의국적코드에따른영문고객명의개별완전성검증고객기본의개인채무회생신청자여부의조건완전성검증고객기본의 CEO 여부의조건완전성검증고객기본의국적코드의통합코드적합성여부검증고객기본의실명번호구분코드통합코드적합성여부검증고객기본의고객분류코드통합코드적합성여부검증고객기본의개인인터넷뱅킹가입일의 YYYYMMDD 날짜적합성검증고객기본의영문고객명영어대소문자포맷적합성검증고객기본의개인채무회생신청여부의 Y / N 적합성여부검증고객기본의 CEO여부의 Y / N 적합성여부검증고객기본의기업인터넷뱅킹가입여부의 Y / N 적합성여부검증고객기본의인터넷뱅킹가입일과현재일과의시간순서연관성검증고객기본의전행고객번호의단독유일성검증보험계약정보의고객번호에대한조건유일성검증보험계약정보의고객번호와고객기본의고객번호와의테이블간무결성검증보험계약정보의보험료누계의계산 / 집계정합성검증보험계약정보의최종납입회차와납입횟수간의적시성검증 품질지표품질지표개별완전성조건완전성범위유효성코드유효성날짜유효성형식유효성최신성입력정확성단독유일성조건유일성계산 / 집계일관성참조무결성이행데이터일관성중복컬럼일관성선후관계일관성
품질측정 품질진단결과분석 품질지표세부지표 BR 개수 오류발생 BR 개수 진단건수오류건수오류율 (%) 완전성 20 15 8,778,036 2,732 0.03% 범위유효성 354 28 40,518,513 87,677 0.22% 유효성 날짜유효성 198 105 44,685,205 422,322 0.95% 형식유효성 161 58 17,587,977 166,193 0.94% 코드유효성 197 51 14,681,303 374,362 2.55% 일관성 계산일관성 6 5 3,818,242 157 0.00% 참조무결성 154 19 18,742,789 1,183,937 6.32% 총계 1,090 281 148,812,065 2,237,380 1.50% 6.32% 0.03% 0.22% 0.95% 0.94% 2.55% 0.00% 완전성범위유효성날짜유효성형식유효성코드유효성계산일관성참조무결성
오류추정데이터분석 완전성, 유효성, 일관성등의품질지표별로품질을분석 도메인점검 BR 수오류발생 BR 수진단건수오류건수오류율 키 138 20 21,684,101 1,183,933 5.46% [ 주요오류사례 ] 테이블명컬럼명부모테이블명부모컬럼명진단건수오류건수 과제분류과제 ID 과제과제 ID 118,245 4 업무규칙명과제분류는과제와상호참조무결성을유지해야한다. 과제분류과제 ID 100400501 100400502 100400503 참조무결성이단절된 값사용 오류현황 : 참조무결성이결여된오류데이터가발생되고있음 과제 과제ID TECL_CD TECL_TP_SE 10040698 SAF002 T020119 10040698 SAF006 E500602 10040701 SAF002 T020219 10040701 SAF006 E500606 원인분석 : 부모쪽의데이터삭제후자식데이터에대한처리가누락된경우또는트랜잭선처리또는입수되는다수의 NULL 값을허용하기위하여물리적관계를임의로단절시키거나설계미반영 권고사항 : 논리적참조무결성및테이블과테이블의컬럼간단순데이터처리규칙을프로그램 Logic 에반영
감사합니다. Contact us. Japan Office : Shinkasumigaseki Bldg 18F., 3-3-2, Kasumigaseki, Chiyoda-ku, Tokyo 100-0013 Tel +81.3.5532.8801 Seoul Office : 6F, Nuritkum Square R&D Tower, 1605 Sangam-dong, Mapo-gu, Seoul, Korea 121-795 Tel + 82.2.2132.5590 Online Contact : Sales@warevalley.com WareValley Database Audit and Protection [ DB 접근통제 ] Database Encryption [ DB 암호화 ] Database Vulnerability Assessment [ DB 취약점분석 ] Database SQL Query Approval [ DB 작업결재 ] Database Performance Monitoring and Management [ DB 성능관리및개발 ]