[2017.03.27~2017.03.30]데이터베이스 분석 및 개선을 통한 성능 확보 교육 1/4

#강사님께서 교재 전체를 진행하지는 않았기 때문에 추후에 공부하면서 추가할 부분은 추가적으로 채워 넣을 예정

데이터베이스 분석 및 개선을 통한 성능 확보

필수로 공부해야할 것

초급 1. SQL&PL/SQL(35) : RDB개념, Syntax

중급 2. Fundamental(35) : DB구조, Data 저장방법 관리기법

       3. Fundamental(35) : 백업&복구, Network&Solution

       4. SQL Statement(튜닝)

       5. Server 튜닝(Performance)

       6. Data-Modeling(분석, 설계쪽)

       7. Data-Architecture

참고로 공부하면 좋은 것

-Embedded-SQL, IEM, EA(BA, AA, DA, TA)

성능저하의 문제가 sql문제인지 db구조적 문제인지 파악할 것

1일차

1. 튜닝의 개요

1.1 데이터 베이스의 발전방향

1970년대 데이터베이스 관리기법의 태동 시기

1980년대 관계형 데이터베이스의 상용화시기

1990년대 데이터베이스의 도입 및 구축 시기 - 클라이언트/서버환경의 급속한 발전

2000년대 보다 많은 사용자들은 보다 빠르게 데이터를 검색할 수 있는 방법을 요구 하게 되는 시기

1.2 성능을 저하시키는 기업의 문제점

-결과물 위주

-충분하지 못한 개발 비용의 산정

-기술력의 한계

-개발 환경에 적절하지 못한 H/W와 RDBMS의 도입

-RDBMS의 불명확한 이해

1.3 좋은 성능 위한 요소

-RDBMS기술에 대한 명확한 이해

-시스템 개발의 생산성을 향상시킬 수 있는 분석/설계 기법

-좋은 성능의 SQL을 작성할 수 있는 개발자가 요구됨

2. 튜닝의 개념

2.1. 튜닝의 개념

튜닝방법(method)

- SQL문

- DB구조

2.2 튜닝 방법론

튜닝방법론(Methodology) - 자세한건(dbguide.net ->  사이트맵(DB튜닝 메뉴) 참고할 것)

준비단계(Step-1) - 고객 Interview(1), 자료수집 및 분석, 튜닝계획 수립, 고객 Interview(2)

실행단계(Step-2) - 디자인 튜닝, Application 튜닝, 서버 튜닝, 의문점/문제점 분석, 튜닝대상 적용, 고객 Interview(3)

결과분석단계(Step-3) - 튜닝 후 자료수집/분석, 튜닝결과 평가, 산출물 작성, Interview(4)

이미지 출처: http://www.dbguide.net/dbqna/oracle.db?cmd=view8

2.3 튜닝 절차

이미지 출처 : http://www.dbguide.net/dbqna/oracle.db?cmd=view2

튜닝 절차 참조

2.4 튜닝의 원인과 결과

이미지 출처 : http://www.dbguide.net/dbqna/oracle.db?cmd=view2

현상별 튜닝 참조

2.5 Tuner

-함께 현상을 분석해야한다. 혼자 하는게 아니라 함께 분석해야 한다.

3. 오라클 데이터베이스의 구조

3.1 오라클 데이터베이스의 구조

• 메모리 영역(SQL>SHOW SGA)

  --사용자가 실행한 SQL문에 의해 검색 또는 변경되는 테이블 데이터를 임시로 저장하는 메모리 영역을 시스템글로벌 영역(SGA)라고 함. 모든 사용자들이 공유하는 영역이며 기본적으로 오라클 데이터베이스 서버는 하나의 SGA영역으로 구성되어 있다.(Shared Pool Area, Data Buffer Cache Area, Log Buffer Area, Large Pool Area)

• 파일 영역(SQL>SELECT FILE_NAME FROM DBA_DATA_FILES;)

  --데이터베이스가 생성되면서 만들어졌던 자료사전 테이블과 뷰 그리고 사용자가 직접 생성한 테이블, 인덱스, 뷰, 시퀀스, 시노늄 등이 저장되어있음. 데이터베이스의 모든 상태정보가 저장되어 있음(Control Files, Parameter File, Data Files, Redo-Log Files)

• 프로세스 영역(SQL>SELECT PROGRAM FROM V$PROCESS;)

  --오라클 데이터베이스의 현재 상태를 모니터링하거나, 사용자가 실행한 sql문의 작업을 처리해주는 프로세스,(DBWR, LGWR, SMON, PMON, CKPT)

3.2 SELECT문의 처리과정

1. 구분분석

   -Syntax_check

   -Semantics_check(테이블 존재여부)-recursive_sql(SELECT문 한개를 실행해도 오라클 서버에서는 recursive_sql이 실행된다.(테이블존재여부 등))#과도한 recursive_sql실행으로 성능저하가 일어날 수 있음

   -privilege_check(권한)

   -execution_plan(실행계획수립)-#옵티마이저의 잘못된 선택으로 성능저하가 일어날 수 있음

2. 1번과정 후 오라클 instance의 Shared pool에 있는지 없는지 확인 후 없으면 적재됨 -> 인출

p.s 

SQL(Structure Query Language)은 #인터프리터 언어적 문제(트랜잭션 위주로 제어하기 위한 언어로 태어남, 태생자체가 성능과는 거리가 멀다.)

PL/SQL(Procedure language)은 컴파일 언어(추가적인 문법을 배워야하는 한계, 기술 이해 부족으로 아직 많이 쓰지 않음)

3.3 DML문의 처리과정

1. 해당 데이터 파일로부터 테이블을 일고, ROLLBACK문 때문에 변경전 데이터 복구를 위해 언두 세그멘트를 DB로부터 읽게 됨. 

2. 읽혀진 테이블, 언두 세그멘트는 데이터버퍼 캐시영역에 저장

3. 변경하려는 ROW는 LOCK걸어놓는다.

4. 로그버퍼 영역에서 변경 전 정보와 변경 후 정보가 저장됨.(로그버펴 영역에 백업)

3.4 COMMIT문의 처리과정 

1. 사용자가 DML문을 씰헁한 후 COMMIT문을 실행

2. 서버 프로세스는 DML문의 처리결과(변경전의 데이터, 변경후의 데이터)가 저장되어 있는 로그 버퍼 영역에 시스템 변경번호(SYSTEM CHANGE NUMBER)를 부여함

3. 로그 기록기(LGWR)는 로그버퍼 영역에 있는 변경 데이터를 영구적으로 보관하기 위해 리두로그 파일에 저장(메모리 영역에는 데이터를 영구히 저장할 수 없기 때문)

4. 사용자 프로세스는 화면에 메시지를 출력해줌.

p.s 

로그버퍼 영역의 데이터를 하나의 리두로그 파일에 모두 저장하지 못하면 다음 로그파일로 위치를 이동시킴 - 로그 스위치

로그 스위치가 발생하면 CKPT프로세스는 컨트롤 파일과 데이터 파일의 헤드영역에 시스템 변경 번호와 관련 상태정보를 저장함-체크포인트 이벤트

이후 데이터베이스 기록기(DBWR)는 데이터버퍼 캐시영역에 있는 사용자의 변경정보를 최종적으로 테이블에 저장

-->이부분 다시 이해할 것

3.5 논리적 저장구조

논리적구조 : 데이터베이스, 테이블스페이스, 세그멘트, 익스텐드, 블록

데이터베이스 - 여러 개의 테이블스페이스라는 논리적 구조가 모여서 구성됨.

테이블스페이스 - 여러 개의 테이블, 인덱스, 뷰 등과 같은 세그멘트들이 모여서 하나의 테이블 스페이스가됨.

세그멘트 - 테이블, 뷰, 인덱스 등

익스텐드 - 하나의 세그멘트를 구성하는 조각을 의미함.

블록 - 하나의 익스텐드를 구성하는 요소, 오라클 데이터베이스의 가장 작은 입출력 단위, 데이터의 읽기/쓰기 작업을 할 때 한 번에 처리 할 수 있는 BYTE 크기를 의미함.

물리적구조 : 데이터 파일

데이터 파일 - SYSTEM01.DBF, UNDO01.DBF, TEMP01.DBF, 로그파일, 컨트롤 파일 들을 말함.

3.5.1 Database의 생성과 저장구조

-->Database의 생성쪽 요약 추가할 것

오라클 데이터베이스의 저장구조

1. SYSTEM_TABLESPACE

   --user_table(data dictionary table에 저장)-1. 이 요약정보를 갖고 튜닝을 진행 2. 효율적인 관리를 위해 필요 등등

2. UNDO_TABLESPACE

   --undo_segment(SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;) ROLLBACK(AFTER데이터를 BEFORE데이터로 바꿔라 이후 BEFORE데이터는 지워라)을 기준으로 어딘가에는 BEFORE 데이터와 AFTER 데이터가 있다.

   ROLLBACK, COMMIT 전까지 갖고있는 임시 공간(undo_segment)

   공유공간이기 때문에 크기, 개수 제한적이라면 성능저하가 일어날 수 있다.

3. TEMP_TABLESPACE

   --temp_segment(데이터를 임시로 받아 SORTING작업을 한다. order by, group by 등)

   SELECT * FROM EMP ORDER BY NO ASC;를 한다면 어딘가에서 SELECT를 받고 그 데이터를 임시로 어딘가에 보관후 sorting 작업을 했다.

   SORTING작업을 하기에 충분한 TEMP_TABLESPACE가 있지 않다면 성능저하가 일어날 수 있다.

3.5.2 Tablespace의 설계구조

개발환경 및 업무성격, 데이터의 속성, 사용자의 수에 따라 설계해야한다.

3.6 시스템 개발절차

System 개발범위 결정(단위업무, 전체업무)->업무 분석 및 데이터 분석(데이터 성격, 데이터량, 사용자수 등)->시스템에 적합한(Hardware 및 DB선정)->분석결과에 의한 Hardware Spec 결정(memory/CPU/Disk/Controller)->Vender를 통한 시스템 발주 후 설치->Physical Design에 의한 S/W환경설정 및 DB생성

3.7 Tablespace 설계 시 주의사항

1. DATA 테이블스페이스와 SYSTEM 테이블스페이스는 분리
2. DATA 테이블스페이스와 INDEX 테이블스페이스는 분리
3. 각 DATA 테이블스페이스는 I/O 경합을 줄이기 위해 분리
4. DATA 테이블스페이스와 UNDO 테이블스페이스는 분리
5. DATA 테이블스페이스와 TEMP 테이블스페이스는 분리
6. 대용량의 Sorting 작업을 위해 TEMP 테이블스페이스를 각 사용자에게 분리 할당

4. 튜닝도구

4.1 튜닝도구

오라클 데이터베이스를 사용하다 보면 발생하는 다양한 성능 저하에 대한 원인 분석을 과학적이고 효율적으로 수행하기 위해서는 반드시 튜닝 도구를 사용해야함

4.2 EXPLAIN PLAN 명령어

SQL문이 처리되는 과정의 구문분석 단계에서 서버 프로세스에 의해 해당 SQL문이 어떻게 실행되는 것이 가장 빠르게 실행될 수 있는지를 결정하게되는데 이 결과를 실행계획(EXPLAIN PLAN)이라고 한다. 이 결과를 확인할 때 사용하는 튜닝도구가 EXPLAIN PLAN 명령어이다.

• 데이터베이스에서 SQL문이 실행될 때의 상태정보를 제공해준다.
• SQL문이 FULL_SCAN인지 INDEX_SCAN인지에대한 실행경로 정보를 제공해준다.
• 테이블에 INDEX를 추가하거나 삭제한 경우 어떻게 실행계획이 달라지는지 보여준다.
• SQL 명령문의 WHERE 절과 FROM절의 미묘한 변화에 대한 정보를 제공한다.

SET AUTOTRACE 명령어

PLAN_TABLE 테이블을 생성한 후 한번 만 설정해주면 SQL문이 실행될 때마다 실행계획을 화면에 출력해 준다.(해제는 SET AUTOTRACE OFF)

문법

SET AUTOTRACE [ON|OFF|TRACE|TRACEONLY]

[ON]은 SQL문의실행결과와 실행계획 그리고 통계정보를 보여주는 옵션이다

[TRACEONLY]는 실행계획과 통계정보 만을 보여준다.

SET AUTOTRACE명령어
[C:\]SQLPLUS "/AS SYSDBA"
SQL> @C:\app\KODB\product\11.2.0\dbhome_1\sqlplus\admin\PLUSTRCE.SQL
SQL> GRANT PLUSTRACE TO SCOTT;
SQL> CONNECT SCOTT/TIGER
SQL> @C:\app\KODB\product\11.2.0\dbhome_1\rdbms\admin\utilxplan.sql
SQL> set autotrace trace;

SQL> SELECT * FROM BIG_EMP WHERE DEPTNO =10;

5336 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   294 | 11760 |    52   (2)|
|*  1 |  TABLE ACCESS FULL| BIG_EMP |   294 | 11760 |    52   (2)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

Note
-----
   - 'PLAN_TABLE' is old version


Statistics //서버상태
----------------------------------------------------------
        317  recursive calls
          0  db block gets
        587  consistent gets
        183  physical reads
          0  redo size
     256740  bytes sent via SQL*Net to client
       4321  bytes received via SQL*Net from client
        357  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       5336  rows processed

4.2.3 V$SQL_PLAN

SQL문의 실행계획을 공유 풀 영역으로부터 직접 참조할 수 있는 자료사전

(해당 SQL문의 HASH_VALUE와 ADDRESS값을 알아야 하는데 V$SQL, V$SQLAREA 자료사전을 참조해야함)

Statistics //서버상태
----------------------------------------------------------
 1       317  recursive calls --사용자가 실행한 SQL문장의 파싱을 위해서 서버 프로세스는 Data Dictionary 테이블로부터 테이블의 상태정보 및 통계정보를 참조하게되는데 이때 실행되는 SQL을 Recursive-SQL이라한다.
 2        0  db block gets --DML문이 실행될 때 발생하는 변경 전 데이터(Roll back Data)를 잠시 저장하기 위한 임시 공간을 나타낸다.
 3       587  consistent gets --SQL문이 실행 될 때 디스크 상에 존재하는 테이블 및 인덱스를 저장하기위한 메모리 공간을 의미한다.
 4      183  physical reads --데이터가 실제로 존재하는 디스크 상의 데이터 파일로부터 읽혀진 데이터 블록 수를 의미한다.
 5         0  redo size --DML문을 실행했을 때 변경 전 데이터와 변경 후 데이터를 고르버퍼 영역에 백업하기 위한 블록 수를 의미한다.
 6    256740  bytes sent via SQL*Net to client --클라이언트와 서버 간에 전송된 데이터를 바이트 단위로 표시했으며 왕복 전송된 횟수를 의미한다.
       4321  bytes received via SQL*Net from client
        357  SQL*Net roundtrips to/from client
 7          2  sorts (memory) --PGA(Program Global Area) 공간을 나타낸다.
 8         0  sorts (disk) --임시 테이블스페이스의 사용 블록 수를 의미한다.(분류작업(Sorting)이 발생하는 경우 사용됨)
 9      5336  rows processed --SQL문이 실행된 후 조건을 만족하는 행수를 의미한다.
----------------------------------------------------------
1 recursive calls - 해당 테이블과 컬럼 권한, syntax 확인을 위해 읽은 블록 수
2 + 3 + 4 = 총 read block 수
2 + 3 = Logical_Read(메모리로부터 읽은)
4 = Physical_Read(디스크로부터 읽은)
(2 + 3 > 4 좋은 성능 기대 / 2 + 3 < 성능지연 문제 우려)

7 PGA(Program Global Area) 1차적 메모리 영역
8 2차 temp_segment(디스크영역)
(7 > 8 좋은 성능 기대 / 7 < 8 디스크에 더 많은 데이터 저장(성능지연 문제 우려))
8 (SELECT file_name from dba_temp_files;)

4.3 SQL*TRACE 기능(서버레벨에서 측정)

SQL문의 실행계획과 실행 소요시간과 디스크 및 메모리로부터 읽은 블록 수에 대한 정보도 포함하고 있다.

분석되는 결과는 OS상의 디렉토리 구조에 생성된다. BINARY형태이기 때문에 TKPROF 유틸리티를 사용하여 텍스트 파일형태로 변환하여 참조하게 된다.

SQL> SHOW PARAMETER TIMED_STATISTICS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics(시간분석위해)         boolean     TRUE(TRUE로 설정되어 있어야함)
SQL> SHOW PARAMETER USER_DUMP_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest(분석결과가저장되는위치)   string      c:\app\yblee\diag\rdbms\orcl\orcl\trace
                                                 
SQL> SHOW PARAMETER MAX_DUMP_FILE_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      unlimited

C:\Windows\system32>SQLPLUS

SQL*Plus: Release 11.2.0.1.0 Production on 월 4월 3 13:56:36 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

사용자명 입력: SCOTT
비밀번호 입력:

다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SESSION SET SQL_TRACE = TRUE;

세션이 변경되었습니다.

SQL> SELECT * FROM BIG_EMP WHERE DEPTNO = 10; --끝까지 기다려야하고, 하나의 세션에서 똑같은 SQL문을 쓰면 안된다.(결과가 따로나오지않고 누적치가 나오기 때문)
SQL> EXIT

C:\Windows\system32>CD c:\app\yblee\diag\rdbms\orcl\orcl\trace
<SID>_ORA_<PROCESS_ID>.TRC 형태의 포맷으로 분석 결과가 생성됨.(가장최근에 생성된 .TRC파일)

C:\Windows\system32>CD c:\app\yblee\diag\rdbms\orcl\orcl\trace
_ORA_.TRC 형태의 포맷으로 분석 결과가 생성됨.

C:\app\yblee\diag\rdbms\orcl\orcl\trace>TKPROF orcl_ora_9052.trc 임의의이름.TKF SYS=NO(이 옵션이 없으면 Recursive_SQL도 보고서에 포함되어 나옴) EXPLAIN=SYSTEM/MANAGER(실행계획과 함께 보여줌)
파일 생성후 만들어진 TKF파일을 메모장 등으로 읽는다.

4.4 V$SQLAREA 자료사전

공유-풀 영역의 구문분석 결과를 제공

• 가장 많은 Disk-I/O가 발생한 SQL문을 제공
• 가장 많은 메모리를 사용한 SQL문을 제공
• 가장 많은 CPU 사용시간이 소요된 SQL문을 제공
• 사용자가 실행한 SQL문의 패턴 제공
• SORT 횟수, INVALIDATION된 SQL문 등