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

12. 서버 튜닝 도구

서버튜닝은 2회에서 3회 적정(최적)을 찾아야한다.

*변천 흐름

8i부터

utlbstat.sql -> 시작

utlestat.sql -> 끝

이 스크립트를 이용해서 일정기간 동안의 튜닝 통계정보 수집할 수 있다. => Report.txt

|

9i부터 기능 향상

Statspack package(데이터베이스 내부에서 프로시저와 펑션들을 이용해서 데이터베이스 상태정보를 수집하고 분석)  -> Data Dictionary Table형태로 보고서를 받을 수 있다.

|

10g부터 기능 향상

log

Automatic Workload Repository(AWR 통계 정보를 저장하고 관리해 주는 기능) => 결과를 txt, html 파일 형태로도 볼 수 있다.

• 오라클 메모리 상태정보를 자동으로 수집 및 분석하여 통계정보를 제공
• SQL문의 사용유형, 시작과 종료시점, Re-Booting과 Crash 상태 등을 성능검색을 위한 알고리즘 구현에 사용
• init.ora 파일에 정의되는 STATISTICS_LEVEL 파라메터 값에 의해 기능을 활성화할 수 있습니다.(basic, typical, all)
• DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 패키지를 통해 통꼐정보의 수집시간을 조절
• DBA_HIST_~ 또는 WRx$ ~ 자료사전을 통해 분석결과를 참조

(자동으로 작업되어서 저장되는 공간 P252, 245, 244)

STATSPACK 패키지(오라클 데이터베이스 서버구조에 대한 모든 튜닝정보를 참조할 수 있다.)

• 데이터베이스 명과 인스턴스 명
• 현재 sga 영역의 크기
• 메모리 상에 로더된 SQL문에 대한 정보
• 롤백 세그멘트와 언두 세그멘트의 상태
• INIT.ORA 파일의 파라메터 값
• 래치의 상태
• 테이블스페이스와 파일들의 I/O 상태

12.3 동적 성능 뷰1

-V$SGASTAT(SGA 영역의 현재 상태를 참조할 수 있음)

-V$SYSSTAT(오라클 데이터베이스의 각 구조들 중에 어떤 자원에서 대기현상이 발생하는지 알 수 있음)

12.4 동적 성능 뷰2

-V$FILESTAT(물리적 구조들에 대한 읽기/쓰기 작업이 일어 날 때 발생하는 파일 I/O에 대한 정보를 제공함)

12.5 ALERT파일과 트레이스 파일

13. 인스턴스 튜닝

SERVER 튜닝(DB구조 튜닝, 차례대로)

1)memory 튜닝(=SGA 튜닝 + Process 튜닝 = Instance 튜닝)

2)Disk-I/O 튜닝(=DB 튜닝)

3)Resource 튜닝(공유 자원)

SQL> SELECT * FROM V$SGAINFO;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1375792 No
Redo Buffers                        4603904 No
Buffer Cache Size                 570425344 Yes
Shared Pool Size                  234881024 Yes
Large Pool Size                     8388608 Yes
Java Pool Size                      8388608 Yes
Streams Pool Size                   8388608 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        8388608 No
Maximum SGA Size                 1071333376 No
Startup overhead in Shared Pool    75497472 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available         234881024

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

P537
SQL> SELECT NAMESPACE, GETS, GETHITS, GETHITRATIO
  2  FROM V$LIBRARYCACHE
  3  WHERE NAMESPACE = 'SQL AREA';

NAMESPACE                                                              GETS    GETHITS GETHITRATIO
---------------------------------------------------------------- ---------- ---------- -----------
SQL AREA                                                             126679      95059  .750392725

SQL> CONNECT SYSTEM/MANAGER
연결되었습니다.

세션이 변경되었습니다.

SQL> SELECT SUM(PINS), SUM(RELOADS),
  2         SUM(RELOADS)/SUM(PINS)
  3  FROM V$LIBRARYCACHE;

 SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
   2527196         6355             .002514645

SQL> SELECT NAMESPACE, INVALIDATIONS
  2  FROM V$LIBRARYCACHE
  3  WHERE NAMESPACE = 'SQL AREA';

NAMESPACE                                                        INVALIDATIONS
---------------------------------------------------------------- -------------
SQL AREA                                                                  2309

13.2.3 공유 영역의 튜닝 솔루션(효율성 향상)

1)풀 영역보다 크게 할당한다.(하지만 최종 해결책은 아니다.)

2)동일한 SQL문 작성을 위한 표준화 작업을 한다.

3)자주 사용하는 SQL문을 캐싱한다.

P548

-CURSOR_SPACE_FOR_TIME = FALSE(이 파라미터값을 TRUE로 세팅하면 PRIVAE SQL AREA에 공유 풀 영역의 구문 분석정보를 참조하는 커서가 하나라도 있으면 AGING 알고리즘에 의해 공유 풀 영역으로부터 제거되지 않고 계속 상주할 수 있다.)

DEFAULT는 FIFO이다.

CURSOR_SPACE_FOR_TIME = TRUE(TRUE로 바꾸면 LRU로 세팅됨)

LRU(가장 최근에 재사용된건 버리지마)

-SESSION_CACHED_CURSORS = 0(C, COBOL, FORTRAN과 같은 3GL언어로 시스템을 개발하는 경우 하나의 세션에서 캐싱할 수 있는 커서의 수를 지정할 수 있음)

-OPEN_CURSORS = 300(하나의 세션에서 동시에 오픈할 수 있는 커서의 수를 지정할 때 사용)

4)자주 사용하는 PL/SQL 블록을 캐싱한다.

SQL> SELECT NAME, TYPE, KEPT
  2  FROM V$DB_OBJECT_CACHE
  3  WHERE TYPE IN ('PACKAGE', 'PROCEDURE', 'TRIGGER', 'PACKAGEBODY')
  4  AND KEPT = 'YES';
SQL> CONNECT /AS SYSDBA
연결되었습니다.

세션이 변경되었습니다.

SQL> CREATE OR REPLACE PROCEDURE CHECK_SAWON
  2  (V_EMP_NO IN SCOTT.EMP.EMPNO%TYPE)
  3             IS
  4             BEGIN
  5                     DELETE FROM SCOTT.EMP WHERE EMPNO = V_EMP_NO;
  6             END CHECK_SAWON;
  7  /

프로시저가 생성되었습니다.

SQL> EXECUTE DBMS_SHARED_POOL.KEEP('CHECK_SAWON');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT TYPE, NAME, KEPT
  2  FROM V$DB_OBJECT_CACHE
  3  WHERE TYPE IN ('PACKAGE', 'PROCEDURE', 'TRIGGER', 'PACKAGE BODY')
  4  AND KEPT = 'YES';

TYPE
----------------------------------------------------------------
NAME
---------------------------------------------------------------------------

PROCEDURE
CHECK_SAWON

5)공유 풀 영역의 Flush

13.3 데이터버퍼 캐시 영역

*data 유형에 따른

data block(after)

undo block(before)

*status에 따른

Free-Block

|

Pinned-Block

|

Dirty-Block

LRU_LIST

P556

13.3.2 데이터버퍼 캐시 영역의 튜닝 솔루션

1)데이터 버퍼 캐시영역을 보다 크게 할당한다. (로드된 데이터들이 지속적으로 로드되지 않고 캐시에 잘 있구나)

히트율 = 1 - (physical reads / (db block gets + consistent gets))

(캐시 히트율이 90% 이상일 때(최소 3회이상 검증) 충분한 데이터버퍼 캐시영역이 설저오디어 있고 좋은 성능을 기대할 수 있다.)

SQL> SELECT 1 - ((PHY.VALUE) / (CUR.VALUE + CON.VALUE)) "CACHE HIT RATIO"
  2  FROM V$SYSSTAT CUR, V$SYSSTAT CON, V$SYSSTAT PHY
  3  WHERE CUR.NAME = 'db block gets'
  4     AND CON.NAME = 'consistent gets'
  5     AND PHY.NAME = 'physical reads';

CACHE HIT RATIO
---------------
     .991890658

2)멀티 버퍼 캐시영역을 할당한다.(전용 메모리공간을 따로주자, 각자 필요한만큼)

-Keep Data Buffer Cache-소량 DATA, 빈번

-Recycle Data Buffer Cache-대량 DATA, 드물게

-Default Data Buffer Cache-보편적인 DATA

SQL> SHOW PARAMETER DB_KEEP_CACHE_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0(할당되어있지 않다)

멀티 버퍼캐시 적용
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=4M;

시스템이 변경되었습니다.

SQL> ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=4M;

시스템이 변경되었습니다.

SQL> SHOW PARAMETER DB_KEEP_CACHE_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 32M
SQL> SHOW PARAMETER DB_RECYCLE_CACHE_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size                big integer 32M
************************************************************
SQL> SELECT NAME, BUFFERS FROM V$BUFFER_POOL;

NAME                    BUFFERS
-------------------- ----------
KEEP                       3980
RECYCLE                    3980
DEFAULT                   59700
************************************************************

1))KEEP 데이터버퍼 캐시영역

소량 DATA, 빈번하게 접근하는 TABLE 선별하는게 중요하다.

1개 BLOCK의 크기를 알아야한다.

SQL> SHOW PARAMETER DB_BLOCK_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
************************************************************

2))RECYCLE 데이터버퍼 캐시영역

대량 DATA, 드물게 접근하는 TABLE 선별하는게 중요하다.

SQL> ALTER TABLE BIG_EMP STORAGE(BUFFER_POOL KEEP);

테이블이 변경되었습니다.

SQL> ALTER TABLE BIG_DEPT STORAGE(BUFFER_POOL KEEP);

테이블이 변경되었습니다.

SQL> ALTER TABLE ACCOUNT STORAGE(BUFFER_POOL RECYCLE);

테이블이 변경되었습니다.

SQL> SELECT TABLE_NAME, BUFFER_POOL
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME IN ('BIG_EMP', 'BIG_DEPT');

TABLE_NAME                     BUFFER_
------------------------------ -------
BIG_DEPT                       KEEP
BIG_EMP                        KEEP

SQL> SELECT * FROM BIG_DEPT;
SQL> SELECT * FROM BIG_EMP;
SQL> SELECT * FROM ACCOUNT;
************************************************************
SQL> SELECT NAME,
  2  1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MULTIPLE RATIO"
  3  FROM V$BUFFER_POOL_STATISTICS
  4  WHERE DB_BLOCK_GETS + CONSISTENT_GETS > 0;

NAME                 MULTIPLE RATIO
-------------------- --------------
KEEP                     .884032114
DEFAULT                  .963945874
RECYCLE                  .878504673
************************************************************

P693

SQL Result Cache

테이블이 아니라 데이터단위로 캐시해놓는 공간, SQL Query의 결과나 PL/SQL function의 결과를 메모리 내에 cache할 수 있는 기능

참고 주소 : http://wiki.gurubee.net/display/CORE/5.+RESULT+CACHE

p696

************************************************************
ALTER USER oe IDENTIFIED BY oe;
ALTER USER oe ACCOUNT UNLOCK;
SQL> conn oe/oe
연결되었습니다.

SQL> conn /as sysdba
연결되었습니다.

세션이 변경되었습니다.

SQL> grant dba to oe;

권한이 부여되었습니다.

SQL> conn oe/oe
연결되었습니다.

세션이 변경되었습니다.
SQL> ALTER SYSTEM SET result_cache_mode = AUTO;

시스템이 변경되었습니다.

SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
************************************************************
--힌트절을 이용하여 쿼리의 결과를 캐시 메모리 영역에 저장하는 방법
SQL> SELECT /*+result_cache*/ GENDER, COUNT(*),
  2  MIN(DATE_OF_BIRTH), MAX(DATE_OF_BIRTH)
  3  FROM CUSTOMERS
  4  GROUP BY GENDER;

G   COUNT(*) MIN(DATE MAX(DATE
- ---------- -------- --------
M        209 50/03/16 49/11/21
F        110 50/01/11 49/07/29

경   과: 00:00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 1577413243

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

| Id  | Operation           | Name                       | Rows  | Bytes | Cost
(%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |                            |     2 |    20 |     6
  (17)| 00:00:01 |

|   1 |  RESULT CACHE       | 4yvjp80cwb1p9azp4tb8s0txv3 |       |       |
      |          |

|   2 |   HASH GROUP BY     |                            |     2 |    20 |     6
  (17)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| CUSTOMERS                  |   319 |  3190 |     5
   (0)| 00:00:01 |

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


Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=4; dependencies=(OE.CUSTOMERS); parameters=(nls); name="SELE
CT /*+result_cache*/ GENDER, COUNT(*),
MIN(DATE_OF_BIRTH), MAX(DATE_OF_BIRTH)
FROM CUSTOMERS
GROUP BY GENDER"



Statistics
----------------------------------------------------------
       1460  recursive calls
          0  db block gets
        399  consistent gets --데이터 버퍼 캐시로부터 읽은 블록 수
         25  physical reads
          0  redo size
        715  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         30  sorts (memory)
          0  sorts (disk)
          2  rows processed
************************************************************

3) 자주 사용되는 테이블을 캐싱한다.

4) Multiple Block 구조를 생성한다.(운영단계에서는 되도록 하지 않도록, 블록 크기가 바뀌면 다바뀜)

테이블에 대해 읽기/쓰기 작업을 수행할 때 한 번에 읽고 쓸 수 있는 데이터의 크기를 블록이라고 한다.

************************************************************
SQL> CREATE TABLESPACE sales
  2     DATAFILE 'C:\sales1.dbf' SIZE 100M
  3  BLOCKSIZE 4K;
CREATE TABLESPACE sales
*
1행에 오류:
ORA-29339: 테이블스페이스 블록 크기 4096이(가) 구성된 블록 크기와 일치하지 않음


경   과: 00:00:00.10
SQL> CREATE TABLESPACE acc
  2     DATAFILE 'C:\acc1.dbf' SIZE 100M0M
  3  BLOCKSIZE 16K;
        DATAFILE 'C:\acc1.dbf' SIZE 100M0M
                                       *
2행에 오류:
ORA-02180: CREATE TABLESPACE 옵션이 부적합합니다


경   과: 00:00:00.02
************************************************************
활성화

SQL> ALTER SYSTEM SET db_4k_cache_size=4M;

시스템이 변경되었습니다.

경   과: 00:00:00.04
SQL> ALTER SYSTEM SET db_16k_cache_size=4M;

시스템이 변경되었습니다.

경   과: 00:00:00.03
SQL> SHOW PARAMETER DB_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 32M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 32M
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_cache_advice                      string      ON
db_cache_size                        big integer 0
************************************************************

작은블록 vs 큰 블록(표)

작은 블록	큰 블록
적은 행이 저장되기 때문에 한 개 블록에 대한 사용자들의 검색이 큰 블록의 경우보다 적기 때문에 경합이 적게 발생	많은 사용자가 동시에 검색하는 경우 많은 행이 하나의 블록에 저장되어 있기 때문에 집중적인 경합현상이 발생
WHERE 조건에 의한 RANDOM검색에 탁월한 성능개선이 기대됨	테이블 전체 스캔의 경우에 탁월한 성능개선이 기대됨
하나의 블록에 적은 수의 행이 저장되기 때문에 불필요한 오버헤드가 많이 발생함	하나의 블록의 많은 행을 저장할 수 있기 때문에 작은 블록의 경우보다 오버헤드가 적게 발생
인덱스 스캔시 보다 많은 블록을 읽을 수도 있기 때문에 성능이 저하될 수 있음	보다 충분한 메모리 공간을 확보할 수 있기 때문에 성능개선이 기대됨

*DML문 성능

1. Log-Buffer 크기 적정하지 않을때

2. Redo-log-file

3. logging, no logging 적절하게(table - logging, index - no logging)

4. 불필요한 index는 제거하자(P390)

한번도 사용된 적이 없다면 불필요한 공간과 변경작업으로 인해 성능을 저하시키는 원인이 될 수 있으므로 삭제하도록 함
SQL> CREATE INDEX I_EMP_ENAME ON EMP(ENAME);

인덱스가 생성되었습니다.

SQL> ALTER INDEX I_EMP_ENAME MONITORING USAGE;

인덱스가 변경되었습니다.

SQL> SELECT INDEX_NAME, USED FROM V$OBJECT_USAGE;

INDEX_NAME           USE
-------------------- ---
I_EMP_ENAME          NO --한번도 사용된 적이 없음을 의미

P625

임시 테이블스페이스 그룹(TEMP를 나눠 사용하도록 적절히 분배)

************************************************************
C:\Users\user>SQLPLUS "/AS SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on 목 3월 30 17:25:14 2017

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


다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


세션이 변경되었습니다.

SQL> SELECT TABLESPACE_NAME, GROUP_NAME
  2  FROM DBA_TABLESPACE_GROUPS;

선택된 레코드가 없습니다.
************************************************************
SQL> CREATE TEMPORARY TABLESPACE TEMP01
  2  TEMPFILE 'C:\TEMP11.DBF' SIZE 10M
  3  TABLESPACE GROUP TEMP_GROUP_1;

테이블스페이스가 생성되었습니다.
************************************************************
SQL> SELECT TABLESPACE_NAME, GROUP_NAME
  2  FROM DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME                GROUP_NAME
------------------------------ ------------------------------
TEMP01                         TEMP_GROUP_1

************************************************************
기본적으로 제공되는 TEMP 테이블스페이스도 새로 만든 그룹에 재배치
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP TEMP_GROUP_1;

테이블스페이스가 변경되었습니다.
************************************************************
SQL> SELECT TABLESPACE_NAME, GROUP_NAME
  2  FROM DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME                GROUP_NAME
------------------------------ ------------------------------
TEMP                           TEMP_GROUP_1
TEMP01                         TEMP_GROUP_1

************************************************************
SQL> CREATE TEMPORARY TABLESPACE TEMP02
  2  TEMPFILE 'C:\TEMP12.DBF' SIZE 10M
  3  TABLESPACE GROUP TEMP_GROUP_2;

테이블스페이스가 생성되었습니다.

SQL> SELECT TABLESPACE_NAME, GROUP_NAME
  2  FROM DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME                GROUP_NAME
------------------------------ ------------------------------
TEMP                           TEMP_GROUP_1
TEMP01                         TEMP_GROUP_1
TEMP02                         TEMP_GROUP_2

************************************************************
SQL> CREATE TEMPORARY TABLESPACE TEMP03
  2  TEMPFILE 'C:\TEMP13.DBF' SIZE 10M
  3  TABLESPACE GROUP ''; --그룹 없이

테이블스페이스가 생성되었습니다.
************************************************************
P627
SQL> SELECT TABLESPACE_NAME, GROUP_NAME
  2  FROM DBA_TABLESPACE_GROUPS; --그룹에 포함된 것만 나옴

TABLESPACE_NAME                GROUP_NAME
------------------------------ ------------------------------
TEMP                           TEMP_GROUP_1
TEMP01                         TEMP_GROUP_1
TEMP02                         TEMP_GROUP_2
************************************************************