문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣ7FS02PS
문서번호ㅣTADTI121
개요
데이터베이스를 장애 예방과 높은 성능을 보장하기 위해 DBA의 모니터링이 필요합니다.
단순, 쿼리 튜닝만이 아닌 환경 설정 변경, 때로는 모니터링의 결과에 따라 Application 설계 변경까지도 고려하여 성능 관리를 수행 할수 있습니다.
해당문서는 선능관리를 위한 요소들을 정리한 문서입니다.
방법
1. 모니터링
데이터베이스의 성능 모니터링은 데이터베이스가 응용 환경 및 사용자의 기대한 성능으로 수행 하고 있는가,
그리고 설계대로 실행되고 있는가 를 확인하기 위해 수해동작 및 실행결과를 확인하는 행동입니다.
모니터링에 의해 산출된 수치가 제한 범위에 도달하였을 경우 그에 대한 조치가 필요합니다.
1.1. 모니터링 대상
목적 : 운영 중인 DBMS의 모니터링 대상 항목에 대해 기술합니다.
현재 환경
- 현재 데이터베이스 구성은 TAC로 구성되어 있습니다.
- Database의 Host: TEST1(Active), TEST2(Active)
현재 모니터링을 위한 Tibero monitor tool은 각 Node마다 실행 파일로 구성되어 있습니다.
환경 설정 방법
- 일반 shell파일이며, tbsql을 통해 Query가 실행되기 때문에 서버에 존재합니다. 다른 환경 설정은 필요 없습니다.
실행 방법 : tm 명령 실행
환경 설정 방법
| HOST | SID | 포트 | 사용자 | 기타 |
|---|---|---|---|---|
| TEST1 | NODE1 | 8629 | sys | Active서버(상시 모니터링) |
| TEST2 | NODE2 | 8629 | sys | Active서버(상시 모니터링) |
1.2. Tibero studio 활용 방법
Tibero Studio 사용자 안내서 문서의 “제 4장 DBA 기능” 을 참고바랍니다.
1.3. Memory 관리
목적 : 운영 중인 시스템의 메모리 관리를 위한 모니터링 방법에 대해 기술합니다.
현재 환경
- Monitor 및 TPR을 사용해 시스템 메모리의 사용에 대한 모니터링 정보를 추출 할 수 있습니다.
단, Report 생성은 해당 시스템에서 직접 수행해야 합니다.
환경 설정 방법
- DB 서버에 Telnet 혹은 원격 데스크톱 연결 및 FTP 접속 권한이 필요합니다.
Monitor 내부적으로 SQL 구문 을 통해 TPR(Tibero Performance Repository)을 생성할 수 있습니다.
운영 절차
TPR Report 생성
- DB 서버 원격 데스크톱 연결
- Window 서버는 원격 데스크톱으로 연결하고, Unix or Linux는 Telnet으로 연결
- TPR Report 생성
- TPR Report 는 HTML 과 text를 지원하여 Reporting 됩니다. DBMS내장 Package를 호출하며,
인자값으로 Instance 번호, 시작 시간, 종료 시간을 받습니다.
- TPR Report 는 HTML 과 text를 지원하여 Reporting 됩니다. DBMS내장 Package를 호출하며,
- 생성 위치 및 파일
- 생성 위치: $TB_HOME/instance/$TB_SID
- 생성 파일: tpr_report.$[SID].$[실행날짜].txt
[예제] TPR 리포트 출력
$ tbsql sys/tibero SQL> column BEGIN_INTERVAL_TIME format a20; SQL> column END_INTERVAL_TIME format a20; SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'; SQL> select SNAP_ID, THREAD#, INSTANCE_NUMBER, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from _tpr_snapshot; -- TPR 대상 조회 SNAP_ID THREAD# INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ---------- --------------- -------------------- -------------------- 1 0 0 2023/11/17 00:32:46 2023/11/17 01:32:46 21 0 0 2023/11/17 09:06:43 2023/11/17 10:06:43 22 0 0 2023/11/17 10:06:43 2023/11/17 11:06:43 23 0 0 2023/11/17 11:06:43 2023/11/17 12:06:43 24 0 0 2023/11/17 12:06:43 2023/11/17 13:06:43 25 0 0 2023/11/17 13:06:43 2023/11/17 14:06:43 26 0 0 2023/11/17 14:06:43 2023/11/17 15:06:43 27 0 0 2023/11/17 15:06:43 2023/11/17 16:06:43 28 0 0 2023/11/17 16:06:43 2023/11/17 17:06:43 41 0 0 2023/11/20 10:34:08 2023/11/20 11:34:08 SQL> exec dbms_tpr.report_text('0', '23', '23'); PSM completed.- DB 서버 원격 데스크톱 연결
메모리 관련 정보
Memory Summary 확인
1.2 Memory Usage ================================================================================ HOST Mem Size : 4,797M Total SHM Size : 1,024M Buffer Cache Size : 683M Avg. Shared Pool Size : 145.42M Avg. DD Cache Size : 1.42M Avg. PP Cache Size : 28.92M DB Block Size : 8K Log Buffer Size : 10M
- Total Memory, Buffer Cache Size, Shared Pool Size 등의 정보를 볼 수 있습니다.
Shared Pool 정보 확인
[예제] TPR Report(Shared Pool Statistics)
5.2 Shared Pool Statistics ================================================================================ Name Begin(MB) End(MB) Diff % Max(MB) Min(MB) -------------------- --------------------- --------------------- ----------- ------------ ------------ SLAB_TCBUF 39.63 (27.25%) 39.63 (27.25%) +0.00% 39.63 39.63 PP 28.92 (19.89%) 28.92 (19.89%) +0.00% 28.92 28.92 Free Space 25.10 (17.26%) 25.10 (17.26%) +0.00% 25.10 25.10[예제] TPR Report(Cache Summary)
3.1 Instance Efficiency ========================================== Value -------- Buffer Cache Hit %: 100.00 Library Hit %: 92.73 PP Hit %: 95.32 Latch Hit %: 100.00 Redo Alloc Hit %: 100.00 Non-Parse CPU %: 98.27 Parse Time: 2,182.01 DB Time: 16.53항목 설명
[표] Cache Summary 항목 설명
항목 메모리 영역 설명 Buffer Cache Hit Data Buffer Cache 데이터를(메모리)에서 처리한 비율 In-Memory Sort Data Buffer Cache 메모리에서 Sort 처리 비율
(실제PGA: WTHR에서 동적으로 처리)
Library Hit Shared Pool 메모리에서 라이브러리 재 사용 비율 PP Hit Shared Pool 메모리 이전 Parsing 된 Plan 정보 재 사용 비율 Latch Hit Shared Pool 메모리에서 latch를 사용하여 자원 획득 확률
- Buffer Pool 정보 확인
Database buffer
Cache는 TSM(Tibero Shared Memory)의 한 주요 부문으로 SQL 문장에 의해 테이블로부터 읽어 온 데이터가 저장되는 영역입니다.
따라서, 다시 한번 같은 데이터가 Access 될 때는 DISK에서 찾지 않고 이 영역에 옮겨 놓은 데이터를 이용하기 때문에 DISK I/O를 줄여 수행 속도는 자연스럽게 빨라집니다.
[예제] TPR Report(Buffer Cache Statistics)
7.1 Buffer Cache Statistics ================================================================================ Num of DB Cache Physical Physical Free Buf Buf Busy- Buffers Hit % Buffer Gets Reads Writes Waits Write Waits ----------- -------- ---------------- --------------- --------------- ----------- ----------- 87,392 100.00 790,140 0 555 0 0Buffer Pool Statistics 항목 설명
[표] TPR Report(Buffer Pool Statistics 정보)
항목 설명 Number of Buffers Pool을 구성하는 Buffer의 총 개수 DB Cache Hit % Physical Read 없이 Buffer에서 데이터를 Read 하는 비율 Buffer Gets Buffer에서 읽는 데이터 Block의 수 Physical Read DISK에서 읽는 데이터 Block의 수 Physical Writes DISK에서 쓰는 데이터 Block의 수 Free Buff Wait DISK에서 Block을 읽어서 담을 빈 버퍼 공간을 못 찾는 횟수 Buffer Busy Waits 해당 데이터에 대한 버퍼를 다른 사용자가 사용 중일 때 발생하는 Wait 횟수
1.4. Lock 관리
목적 : Lock 경합 모니터링 방법에 대해 기술합니다.
환경 설정 방법
- DB 서버에 Window는 원격 데스크톱, Unix및 Linux시스템은Telnet 접속 권한이 필요하며 tbsql 로그인
을 수행하여 Lock 정보를 SQL문으로 확인합니다. (Tibero Studio 기능도 이용 가능) 운영 절차
[예제] Lock 확인 쿼리
SET FEEDBACK OFF SET LINESIZE 132 SET PAGESIZE 50 COL "USER" FORMAT A15 COL "SID" FORMAT 9999 COL "OBJECT" FORMAT A35 COL "STATUS" FORMAT A8 COL "LOCK_TIME" FORMAT A15 COL "LOCK MODE" FORMAT A15 @$MONITOR/SQL/SQLID_FORMAT.SQL SELECT S.SESS_ID "SID" ,S.STATUS "STATUS" ,S.USER_NAME "USER" ,O.OWNER|| '.' ||O.OBJECT_NAME "OBJECT" ,FLOOR((SYSDATE - VT.START_TIME)*24) || ':'|| LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*1440, 60)),2,0) ||':'|| LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*86400,60)),2,0) AS "LOCK_TIME" ,DECODE(LMODE, 0, '[0]', 1, '[1]ROW-S(RS)', 2, '[2]ROW-X(RX)', 3, '[3]SHARED(S)', 4, '[4]S/ROW-S(SRX)', 5, '[5]EXCLUSIVE(X)', 6, '[6]PIN', TO_CHAR (LMODE) ) "LOCK MODE" --,NVL(S.SQL_ID, S.PREV_SQL_ID) "SQL_ID" ,NVL(S.SQL_ID, S.PREV_SQL_ID) || '/' || NVL2(S.SQL_ID, S.SQL_CHILD_NUMBER, S.PREV_CHILD_NUMBER) "SQL_ID" FROM VT_WLOCK L, VT_SESSION S, DBA_OBJECTS O , VT_TRANSACTION VT WHERE L.TYPE='WLOCK_DML' AND L.SESS_ID = S.VTR_TID AND L.ID1 = O.OBJECT_ID (+) AND L.SESS_ID = VT.SESS_ID ORDER BY "LOCK_TIME" DESC /- SID 별로 데이터베이스 OBJECT에 어떤 종류의 Lock을 걸고 있는지 확인합니다.
Lock 잡고 있는 Session에 대해 확인합니다.
[예제] Session 정보 확인
SET LINES 200 SET FEEDBACK OFF COL "SID,SERIAL" FORMAT A10 COL "USERNAME" FORMAT A15 COL "STATUS" FORMAT A10 COL "IPADDR" FORMAT A15 COL "LOGON_TIME" FORMAT A18 COL "PROGRAM" FORMAT A18 @$MONITOR/SQL/SQLID_FORMAT.SQL SELECT * FROM ( SELECT SID || ',' ||SERIAL# "SID,SERIAL" ,USERNAME "USERNAME" ,STATUS "STATUS" ,IPADDR "IPADDR" ,TO_CHAR(LOGON_TIME,'YY/MM/DD HH24:MI:SS') "LOGON_TIME" ,PROG_NAME "PROGRAM" ,WLOCK_WAIT "WLOCK_WAIT" ,NVL(SQL_ID, PREV_SQL_ID) || '/' || NVL2(SQL_ID, SQL_CHILD_NUMBER, PREV_CHILD_NUMBER) "SQL_ID" ,PID "WTHR_PID" ,TRUNC(PGA_USED_MEM/1024/1024,3) "PGA(MB)" FROM V$SESSION WHERE SID != ( SELECT TID FROM VT_MYTID ) ORDER BY 1,5 ) UNION ALL SELECT '[RUN: ' || SUM(DECODE(STATUS, 'RUNNING', CNT, 0)) || ']' , '[TOT: ' || SUM(CNT) || ']' ,NULL ,NULL ,NULL ,NULL ,NULL,NULL,NULL,NULL FROM ( SELECT STATUS , COUNT(*) CNT FROM V$SESSION WHERE SID != ( SELECT TID FROM VT_MYTID ) GROUP BY STATUS ) T /- Session의 IP 및 PGA 메모리 사용량 등을 확인합니다.
Lock 풀기
- Lock이 발생하였을 경우 유형에 따라 Session을 Kill 하여야 하는 경우가 발생합니다.
- Lock 은 DBMS 시스템에 반드시 필요한 알고리즘으로 Session을 확인하고 Deadlock인지 구분하여 Lock을 풀어야 합니다.
- Lock 확인 절차 를 통해 확인 후 deadlock으로 진행될 경우 해당 Session Kill 수행으로 풀수 있습니다.
- 쿼리 문 확인 다음을 파일로 저장하고 실행
[예제] SQL 구문 및 Plan 확인
SET LINESIZE 132
SET PAGESIZE 120
SET VERIFY OFF
SET FEED OFF
COL "SQL TYPE" FORMAT A8
COL "ID" FORMAT 99999
COL "PLAN" FORMAT A100
ACCEPT SQL_ID PROMPT 'INPUT SQL_ID(EX: SQL_ID/SQL_CHILD_NUMBER ) : '
VAR SQL_ID VARCHAR(13);
VAR SQL_CHILD_NUMBER NUMBER;
EXEC :SQL_ID := REGEXP_SUBSTR('&SQL_ID','[^/]+', 1, 1)
EXEC :SQL_CHILD_NUMBER := REGEXP_SUBSTR('&SQL_ID','[^/]+', 1, 2)
PROMPT ## SQL INFO
PROMPT ----------------------------------------------------------------------------------------------------
SELECT SQL_ID, CHILD_NUMBER, HASH_VALUE, PLAN_HASH_VALUE
,DECODE(EXECUTIONS, 0, -1, ROUND(BUFFER_GETS/EXECUTIONS,3)) "GETS/EXEC"
,DECODE(EXECUTIONS, 0, -1, ROUND(ELAPSED_TIME/EXECUTIONS/1000,3)) AS "ELAP/EXEC(MS)"
,EXECUTIONS
FROM V$SQL
WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER
/
SET HEAD OFF
PROMPT ## SQL TEXT
PROMPT ----------------------------------------------------------------------------------------------------
SELECT SQL_TEXT "PLAN"
FROM V$SQLTEXT_WITH_NEWLINES
WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER
UNION ALL
SELECT '.----------------.' FROM DUAL
UNION ALL
SELECT '| EXECUTION PLAN |' FROM DUAL
UNION ALL
SELECT '+-----------------------------------------------------------------------------------------' FROM DUAL
UNION ALL
SELECT *
FROM
(
SELECT SUBSTRB(TO_CHAR(ID),1, 3) || LPAD(' ', LEVEL * 2) || UPPER(OPERATION) ||
DECODE(OBJECT_NAME, NULL, NULL, ':' || OBJECT_NAME) || '(COST:' || COST || ',%%CPU:' ||
DECODE(COST, 0, 0, TRUNC( (COST - IO_COST) / COST * 100) ) || ',CARD:' || CARDINALITY || ')' ||
DECODE(PSTART, '', '', '(PS:' || PSTART || ',PE:' || PEND || ')') AS "EXECUTIONPLAN"
FROM (
SELECT *
FROM V$SQL_PLAN
WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER)
START WITH DEPTH = 1 CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR SQL_ID = SQL_ID AND PRIOR CHILD_NUMBER = CHILD_NUMBER
ORDER SIBLINGS BY POSITION
)
UNION ALL
SELECT '+-----------------------------------------------------------------------------------------' FROM DUAL
UNION ALL
SELECT '| EXECUTION STATS |' FROM DUAL
UNION ALL
SELECT '+-----------------------------------------------------------------------------------------' FROM DUAL
UNION ALL
SELECT *
FROM
(
SELECT SUBSTRB(TO_CHAR(ID), 1, 3) ||
LPAD(' ', L * 2) || UPPER(OPERATION) ||
DECODE(OBJECT_NAME, NULL, NULL, ': '||OBJECT_NAME) ||
' (TIME:' || TO_CHAR(LAST_ELAPSED_TIME, 'FM9999999.99') ||
' MS, ROWS:' || LAST_OUTPUT_ROWS ||
', STARTS:' || LAST_STARTS || ') ' AS "EXECUTION STAT"
FROM (SELECT A.ID, A.OPERATION, A.OBJECT_NAME, A.COST,
A.IO_COST, A.CARDINALITY, A.PSTART, A.PEND,
S.LAST_ELAPSED_TIME, S.LAST_OUTPUT_ROWS, S.LAST_STARTS, A.L
FROM (SELECT I.*, LEVEL L
FROM (SELECT * FROM V$SQL_PLAN
WHERE SQL_ID = :SQL_ID AND CHILD_NUMBER = :SQL_CHILD_NUMBER) I
START WITH DEPTH = 1
CONNECT BY PRIOR ID = PARENT_ID AND PRIOR SQL_ID = SQL_ID AND PRIOR CHILD_NUMBER = CHILD_NUMBER
ORDER SIBLINGS BY POSITION) A, V$SQL_PLAN_STATISTICS S
WHERE A.SQL_ID = S.SQL_ID AND A.CHILD_NUMBER=S.CHILD_NUMBER AND A.ID = S.ID)
)
/Session Kill
[예제] Session Kill
alter system kill session(${Session ID},${serial})참고 내용
- Lock 유형은 아래와 같은 DML Lock과 DDL Lock이 존재합니다.
- DML Lock : 데이터의 무결성을 보장하기 위한 것입니다. (SHARED LOCK(TM), EXCLUSIVE LOCK(TX))
테이블 레벨 잠금(TM)
- 테이블 레벨 잠금(TM)은 테이블을 수정하는 모든 DML Transacion에 대해 설정됩니다.
- INSERT, UPDATE, DELETE, SELECT..FOR UPDATE, LOCK TABLE
- DDL작업이 Transacion과 충돌하지 못하도록 막습니다.
[예제] TM Lock Case
# Session1의 작업
SQL > UPDATE EMP
# Session2의 작업
SET SALARY = SALARY*100;
행 레벨 잠금(TX)
- Insert, Update, Delete, Select … for Update에 의해 수정된 각 행에 대해 자동으로 행 레벨의 lock[TX]이 설정됩니다.
DML Lock 종류
- 행 독점(ROW EXCLUSIVE, RX)
- 이 모드는 다른 Transacion들이 동일한 테이블의 다른 행을 동시에 INSERT, UPDATE, DELETE하거나 잠글 수 있도록 허용하고, 다른 Transacion들이 독점 읽기 또는 쓰기를 위해 테이블을 수동으로 잠그는 것을 막습니다.
- RX LOCK과 비슷한 내용이고, 단지 S, SRX, X LOCK 을 걸 수 없습니다.
- ROW 에 대해서 UPDATE, DELETE를 실행 할 때는 테이블에 대해서는 RX LOCK 이 생기므로 에러는 안 나지만, COMMIT 이나 ROLLBACK 할 때까지 WAITING을 합니다.
(제일 많이 발생하는 Lock 종류 중하나)
- 행 공유 (ROW SHARE, RS)
- SELECT ….FOR UPDATE 문을 사용하는 질의 동안 행을 잠그도록 선택할 수 있습니다.
- 이 모드는 다른 Transacion들이 동일한 테이블의 다른 행을 동시에 질의, 삽입, 갱신, 삭제하거나 잠글 수 있도록 허용하고, 다른 Transacion들이 독점 쓰기 액세스를 위해 테이블을 수동으로 잠그는 것을 막아주는 활을 합니다.
공유 (SHARE LOCK, S)
- S LOCK 은 같은 테이블에 대해서 RS, S LOCK 만 가능 하고, RX, SRX, X LOCK 을 걸 수 없습니다.
- 해당 락에 대해 INSERT, UPDATE, DELETE 할 수 없습니다.
- 공유 행 독점 (SHARED ROW EXCLUSIVE, SRX)
- SRX LOCK 은 같은 테이블에 대해서 RS LOCK 만 가능하고 RX, SRX, X LOCK을 걸 수 없습니다. SELECT .. FOR UPDATE 문은 가능하지만 INSERT, DELETE, UPDATE 는 할 수 없습니다.
- SRX LOCK 은 같은 테이블에 대해서 RS LOCK 만 가능하고 RX, SRX, X LOCK을 걸 수 없습니다. SELECT .. FOR UPDATE 문은 가능하지만 INSERT, DELETE, UPDATE 는 할 수 없습니다.
DDL Lock 종류서버는 동일한 Schema 객체를 수정하거나 참조할지도 모르는 다른 DDL 작업을 막기 위해 DDL Lock 을 자동으로 설정합니다.
Lock 종류
- 독점(EXCLUSIVE, X)
- X LOCK 은 같은 테이블에서는 어떠한 LOCK도 걸 수 없습니다. 즉, DROP TABLE ..;, ALTER TABLE..; 등의
DDL문장에 의해 테이블에 생기는 LOCK 입니다.
- X LOCK 은 같은 테이블에서는 어떠한 LOCK도 걸 수 없습니다. 즉, DROP TABLE ..;, ALTER TABLE..; 등의
- 독점(EXCLUSIVE, X)
DEAD LOCK
- DEAD LOCK은 2 개 이상의 Tranaction이 각각 서로가 lock을 건 데이터를 기다리고 있을 때 발생합니다.
- 티베로는 DEAD LOCK 을 프로세스가 감시하고 있으면 감지시 DEAD LOCK 해소를 수행합니다.
1.5. Session 관리
목적 : Session의 접속 상태 및 상세 정보를 조회하는 방법에 대해 기술합니다.
DB 서버에 Window는 원격 데스크톱, Unix 및 Linux시스템은 Telent 접속 권한이 필요하며 tbsql 로그인을 수행하여 Lock 정보를 SQL문으로 확인합니다.(Tibero Studio 기능도 이용 가능)
운영 절차
전체 Session Count를 확인
[예제]전체 Session Count With 메모리
SET LINESIZE 120 COL "WPM(WORKING PROCESS MEMORY)" FOR A30 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MM:SS') "TIME", "WPM(WORKING PROCESS MEMORY)", (A.ACS + B.RUN) "TOTAL SESSION", B.RUN "RUNNING SESSION", C.RECOVER "RECOVER SESSION" FROM (SELECT COUNT(1) ACS FROM V$SESSION WHERE STATUS='ACTIVE') A, (SELECT COUNT(1) RUN FROM V$SESSION WHERE STATUS='RUNNING') B, (SELECT COUNT(1) RECOVER FROM V$SESSION WHERE STATUS='SESS_RECOVERING') C, (SELECT (ROUND(SUM(PGA_USED_MEM)/1024, 2)||' K' ) AS "WPM(WORKING PROCESS MEMORY)" FROM V$PROCESS P, V$SESSION S WHERE P.NAME = 'WTHR' AND S.TYPE = 'WTHR') D /Column(Alias) 설명
[표]전체 Session Count Column 설명
Column 설명 Time 현재 시간 WPM Working Process Memory: PGA 사용량 Total Session Session Total Count Running Session 현재 실행 되고 있는 Session 수(cf. 접속만 유지 된 Session: Active) Recover Session booting시 혹은 Session 강제 종료 시 Transaction 복구 중인 상태
프로그램 단위 Session Count
[예제] 프로그램 별 Session 수
SELECT PROG_NAME AS PGM_NAME, TYPE, COUNT(1) AS PGM_CNT, COUNT(DECODE(STATUS, 'RUNNING', 1)) AS ACT_CNT, COUNT(DECODE(STATUS, 'ACTIVE', 0, 1)) AS INACT_CNT, SUM(DECODE(WLOCK_WAIT, NULL, 0, 1)) AS LOCKWAIT FROM V$SESSION GROUP BY PROG_NAME, TYPE /Column(Alias) 설명
Column 설명 PGM_NAME 실행 프로그램 명(예, Tibero Studio, tbsql, JDBC Thin Client.. etc) TYPE PROCESS 종류 PGM_CNT 프로그램 Session count ACT_CNT Active (RUNNING) Session Count INACT_CNT Inactive Session Count(Running 외 Session) LOCKWAIT Lock에 의해 대기 중인 Session Count
서버 내에서 실행되는 Session의 개수 (Batch 등)
[예제] DB Instance 내 연결되어 있는 Session 유형 및 개수
SET LINESIZE 120 COL MACHINE FOR A30 COL USERNAME FOR A30 SELECT MACHINE, USERNAME, PROG_NAME, COUNT(1) CNT FROM V$SESSION GROUP BY MACHINE, USERNAME, PROG_NAME;- 현재 Transaction 확인
- 현재 진행 중인 Transaction들의 목록과 관련 정보를 확인합니다.
- [object_name]은 현재 Transaction에 사용 중인 테이블 명 의미
- [used_blk] 는 현재 Transaction이 사용 중인 블록 개수 의미
- [start_time] 은 Transaction이 시작된 시간 의미
[Transaction Time] 은 Transaction이 진행된 시간 의미
- 현재 진행 중인 Transaction들의 목록과 관련 정보를 확인합니다.
[예제] 현재 Transaction 확인
SELECT DISTINCT VS.SID, VS.SERIAL#, VS.USERNAME, VS.USERNAME || '.' || VA.OBJECT "OBJECT", VS.STATUS, VT.USED_BLK, VT.USN, VT.START_TIME, FLOOR(MOD((SYSDATE-VT.START_TIME)*24, 24))||':'|| LPAD(FLOOR(MOD((SYSDATE-VT.START_TIME)*1440,60)), 2, 0)||':'|| LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*86400, 60)),2,0) AS "TRANSACTION TIME", VST.SQL_TEXT FROM V$SESSION VS, VT_TRANSACTION VT, V$SQLTEXT VST, V$ACCESS VA WHERE VT.SESS_ID = VS.SID AND VT.SESS_ID = VA.SID AND NVL(VS.SQL_ID,VS.PREV_SQL_ID) = VST.SQL_ID /Column(Alias) 설명
[표] 현재 수행되는 SQL문 쿼리 설명
Column 설명 SID Transaction을 수행 중인 Session 의 id User Name Transaction을 수행 중인 session 의 사용자 Object Name 현재 동작 중인 Transaction에서 사용 중인 Table들의 목록(하나의 Transaction에서 여러 table을 access 할 경우 table 마다 하나의 row로 나온다. ) Status transaction을 수행하고 있는 Session의 상태 정보 Transaction Time Transaction이 진행된 시간 SQL_TEXT 현재 수행 중인 SQL 내용
1.6. Disk I/O 관리
목적 : Disk I/O 성능 파악을 위한 정보를 조회하는 방법에 대해 기술합니다.
현재 환경
- 현재 시스템이 Single(HA Cluster 이용한 Backup구성)로 동작하고 있고, 저장소는 D(Data 저장소), E(Archive 저장소), V(Online Backup 저장소) Driver로 구성되어 있습니다.
환경 설정 방법
- DB 서버에 원격 데스크톱(Window system) 혹은 Telnet(Unix, Linux) 접속 권한이 필요하며 sql을 수행 하여 Disk I/O를 확인합니다. (Tibero Studio Tool 사용 가능)
확인 절차
- File I/O
데이터 파일의 I/O 정도 및 시간을 확인
[예제] File I/O Check
SET LINESIZE 150 COL TABLESPACE_NAME FOR A20 COL NAME FOR A40 SELECT FL.TABLESPACE_NAME, DF.NAME, FS.PHYRDS, FS.PHYWRTS, ROUND((PHYRDS / (SELECT SUM(PHYRDS) FROM V$FILESTAT)) *100, 1) "P_READ(%)", ROUND((PHYWRTS / DECODE ((SELECT SUM(PHYWRTS) FROM V$FILESTAT), 0, 1, (SELECT SUM(PHYWRTS) FROM V$FILESTAT)))*100, 1) "P_WRITE(%)", ROUND((PHYRDS + PHYWRTS) / (SELECT SUM(PHYRDS) + SUM(PHYWRTS) FROM V$FILESTAT) *100, 1) "TOTAL IO(%)" , ROUND(FS.AVGIOTIM/1000, 3) "AVG_TIME(MSEC)" FROM V$DATAFILE DF, V$FILESTAT FS, DBA_DATA_FILES FL WHERE DF.FILE# = FS.FILE# AND DF.FILE# = FL.FILE_ID ORDER BY PHYRDS+PHYWRTS DESC /Column(Alias) 설명
Column 설명 PHYRDS disk에서 block을 읽은 회수 PHYWRTS disk에서 block을 기록한 회수 P_READ 해당 datafile에 read를 한 빈도 (전체 block read중, 해당 datafile의 비중) P_WRITE 해당 datafile에 Write를 한 빈도 (전체 block write 중, 해당 datafile의 비중) TOTAL IO 해당 datafile에 read/write를 한 빈도(전체 block read/write 중, 해당 datafile의 비중 AVG TIME 평균 IO 시간(원래 값의 단위가 usec 이므로, 적절히 변환하여 사용합니다. 위의 예에서는msec으로 표기)
- 가이드
- 데이터 파일의 I/O 정도 및 시간을 확인합니다. Read(%), Write(%), Total IO(%)는 데이터 베이스 내의 모든 파일에 대한 Read, Write, Read+Write가운데 해당 파일이 차지하는 비율을 의미합니다.
- 평균 I/O 시간이 긴 데이터 파일에 대해서는 해당 데이터 파일이 위치한 Disk의 성능을 고려해 봐야 하며, 많은 데이터 파일이 같은 위치에 있으므로 해서 발생하는 I/O 병목 현상의 가능성도 확인해야 합니다.
- 또한, I/O가 많이 발생하는 데이터 파일은 하나의 Device에 몰리지 않도록 분산 시킵니다.
- Online redo log의 시간 별 Switch 횟수로 데이터베이스의 Transaction양을 예측할 수 있으며
현 시점에서 가장 Transaction이 많은 시간을 파악할 수 있습니다. - 많은 Transaction이 발생하는 시간에는 대량의 Batch Job을 가능한 다른 시간에 수행토록 하는 것이 바람직합니다.
참고
[표] File I/O 관련 View
옵션 설명 V$FILESTAT File Read/Write에 대한 정보를 제공 V$DATAFILE 데이터베이스 내의 데이터 파일에 대한 정보를 제공한 DBA_DATA_FILES 데이터 파일의 사이즈,상태 등의 정보를 제공 V$LOG 로그 그룹에 대한 정보를 제공
2. Reorg
2.1. Reorg 개요
Reorganization은 extent의 수나 row chain이 많이 발생한 테이블, blevel4 이상인 인덱스를 대상으로 수행하게 된며, 이는 성능 저하의 원인이 될 수 있으므로 주기적으로 reorg 작업을 수행하여 Database를 효율적이고 최적화 운용 하셔야 합니다.
Reorg는 여러 가지 방법으로 할 수 있으나 여기서 reorg 기능인 shrink와 DBMS_REDIFINITION에 대해 설명합니다.
환경 설정 방법
- 설치로 해당 기능 사용이 가능하며 별도의 설정 방법은 없습니다.
2.2. Reorg 대상 선정
각 TABLE이 차지하고 있는 사이즈와 예상되는 테이블 사이즈를 비교해 비율 차이가 많이 나는 테이블을 대상으로 REORG 작업을 수행하게 됩니다.
- (row count - row max length) / (table block count - block size)
- REORG 대상 조회 쿼리
- 테이블의 extents 된 사이즈와 예상 테이블 사이즈의 차이가 100MB 초과와 예상 테이블 사이즈 에서
extents 된 사이즈의 비율이 50% 이하일 경우 조회합니다. - REORG 대상 조회 쿼리는 통계 정보 수집한 테이블을 대상으로 확인합니다
- 테이블의 extents 된 사이즈와 예상 테이블 사이즈의 차이가 100MB 초과와 예상 테이블 사이즈 에서
[예제] Reorg 대상 선정 쿼리 문
SET LINESIZE 160
COL OWNER FOR A15
COL TABLE_NAME FOR A15
SELECT *
FROM ( SELECT EXT.OWNER,
TBL.TABLE_NAME,
TBL.TABLE_TYPE,
TO_CHAR(TBL.LAST_ANALYZED,'YYYY/MM/DD HH24:MI:SS') LAST_ANALYZED,
TBL.NUM_ROWS,
ROUND(EXT.EXTENTS_SIZE/1024/1024,2) AS "EXTENTS_SIZE(MB)",
ROUND(TBL.MAX_DATA_SIZE/1024/1024, 2) AS "MAX[DATA_SIZE(MB)]",
ROUND((EXT.EXTENTS_SIZE - TBL.MAX_DATA_SIZE)/1024/1024, 2) AS "DIFF_SIZE(MB)",
ROUND((TBL.MAX_DATA_SIZE / EXT.EXTENTS_SIZE * 100), 2) AS "DATA_RATE(%)"
FROM ( SELECT OWNER,
SEGMENT_NAME,
SUM(BYTES) AS EXTENTS_SIZE, -- TABLE EXTENTS TOTAL SIZE
SUM(BLOCKS) AS EXTENTS_BLOCK_COUNT --TABLE EXTENTS BLOCK TOTAL COUNT
FROM DBA_EXTENTS
WHERE OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITON')
GROUP BY OWNER, SEGMENT_NAME ) EXT,
( SELECT A.OWNER,
A.TABLE_NAME,
DECODE(PARTITIONED, 'YES', 'PARTITION TABLE', 'TABLE') AS TABLE_TYPE,
LAST_ANALYZED,
ROUND(NUM_ROWS) AS NUM_ROWS,
BLOCKS,
AVG_ROW_LEN,
MAX_ROW_LEN,
(ROUND(NUM_ROWS) * MAX_ROW_LEN) AS MAX_DATA_SIZE -- MAX DATA SIZE
FROM DBA_TABLES A,
( SELECT OWNER,
TABLE_NAME,
SUM(DATA_LENGTH) AS MAX_ROW_LEN -- 1 ROW MAX SIZE
FROM DBA_TBL_COLUMNS
WHERE OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
GROUP BY OWNER, TABLE_NAME ) B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME ) TBL
WHERE 1=1
AND EXT.OWNER=TBL.OWNER
AND EXT.SEGMENT_NAME=TBL.TABLE_NAME ) X
WHERE 1=1
AND "DIFF_SIZE(MB)" > 100
AND "DATA_RATE(%)" < 50
ORDER BY "DATA_RATE(%)" DESC, "DIFF_SIZE(MB)" DESC
/
2.3. Reorg 운영 절차
적용 대상
- Normal table, Index, Lob, Materialized View
Reorg 전 체크 리스트
- Reorg기능인 REDEFINITION을 시작하기 전 반드시 확인해야 될 사항들이 아래같이 존재합니다
- 대상 Object Backup 수행 여부
- Online Table Copy 혹은 Export Backup 수행 여부
데이터 사이즈 check
SELECT OWNER,
SEGMENT_NAME,
SUM(BYTES) AS EXTENTS_SIZE,
SUM(BLOCKS) AS EXTENTS_BLOCK_COUNT
FROM DBA_EXTENTS
WHERE 1=1
AND OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION')
AND SEGMENT_NAME = '${OBJECT 명}'
GROUP BY OWNER, SEGMENT_NAME
/Index 상태 확인
SELECT INDEX_NAME, LAST_ANALYZED, NUM_ROWS, STATUS
FROM DBA_INDEXES
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT 명}'
ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME
/Index Column 확인
SELECT INDEX_OWNER, INDEX_NAME, COLUMN_NAME
FROM DBA_IND_COLUMNS
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT 명}'
ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME, COLUMN_POSITION
/테이블 관련 Trigger 상태 확인
SELECT TRIGGER_NAME, STATUS
FROM DBA_TRIGGERS
WHERE TABLE_OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT 명}'
/특권 확인
SELECT *
FROM DBA_TAB_PRIVS
WHERE OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT 명}'
/Table 상태 확인
SELECT TABLE_NAME, LAST_ANALYZED, NUM_ROWS
FROM DBA_TABLES
WHERE OWNER = '${USER}'
AND TABLE_NAME = '${OBJECT 명}'
/테이블 제약 조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, STATUS
FROM DBA_CONSTRAINTS
WHERE OWNER = '${USER}'
AND TABLE_NAME = '{$OBJECT 명}'
ORDER BY 1,2,3
/데이터 건수 확인
SELECT COUNT(*) FROM ${USER}.${OBJECT 명};
3. Tuning
3.1. DB Tuning 개요
목적 : DB Tuning의 목표에 대해 기술합니다.
- 최상의 튜닝은 시스템과 응용프로그램을 치밀하게 설계하는 것입니다. 대부분의 성능 향상은 응용 프로그램의 튜닝을 통해 실현됩니다. 튜닝의 주요 목표는 사용자가 해당 명령문에 대한 응답을 가능한 빨리 얻을 수 있도록 하는 것입니다. 일반적으로 응답 시간, 처리 능력 또는 복수 시간의 관점에서 측정 됩니다.
- Tibero에서는 TPR(Tibero Performance Repository) 기능을 제공하며, Oracle의 AWR(Automatic
Workload Repository)와 유사합니다.
환경 설정 방법
- Snapshot 저장
- V$SYSSTAT, V$SYSTEM_EVENT 등 각종 성능 통계 정보의 변화를 주기(1시간)로 테이블에 저장해 둬야합니다.
- V$SYSSTAT, V$SYSTEM_EVENT 등 각종 성능 통계 정보의 변화를 주기(1시간)로 테이블에 저장해 둬야합니다.
- 저장 환경 및 원리
- 통계 관련 View Table를 조회하여 변화 내용을 통계 정보 테이블에 현재 값은 임시 테이블에 넣는습니다.
- TPR 관련 테이블에 최소한의 데이터만 저장하고, Multi Table INSERT 를 통해 View를 한 번만 조회하여 성능이 빠릅니다.
- 기존에 저장한 임시 테이블 데이터 DELETE
- 7일이 지난 통계 데이터 DELETE
- 한 번의 Snapshot으로 쌓이는 데이터 총 량 (최대치) : 약 150kB
- 최대 차지하는 공간 : 150kb - 24 - 7 = 25Mb
- Snapshot 저장 기능
- Snapshot 저장 기능은 V$SYSSTAT, V$SYSTEM_EVENT, V$SQLSTATS, V$SGASTAT 등 Tibero의 각종 성능 통계 정보를 주기(보통 1시간)로 테이블에 저장하여 두게 됩니다.
- 이렇게 저장된 정보를 Snapshot이라 부릅니다.
- 저장해 놓은 Snapshot 정보를 이용하여 성능 분석 리포트를 만드는 기능을 제공하게 됩니다.
- 리포트를 생성할때 특정 구간을 지정하여 생성하고, 이를 이용해 DB의 성능 문제를 진단할 수 있습니다.
- Session 상태 저장 기능
- Session 상태 저장 기능은 1초에 한 번 씩 현재 RUNNING 상태인 Session들의 ID와 대기 중인 이벤트 정보를 메모리에 저장해 둡니다.
- 이렇게 저장해 놓은 정보는 V$ACTIVE_SESSION_HISTORY View로 조회할 수 있습니다.
- 이 View를 이용해 DB의 성능 문제를 보다 세밀하게 진단할 수 있습니다.
- 현재 Session 상태 저장 기능은 부하 상황에 취약할 수 있으므로, 모니터링 결과에 따라 주기를 더 늘리기를 권고합니다.
3.2. Tuning 대상 선정 및 수행 방법
Tuning 대상을 선정을 위한 TPR의 기능에 대해 알아보고 Tuning 수행 방법에 대해 알아본다.
환경 설정 방법
- TIP 설정
Snapshot 저장 기능을 사용하려면 tip 파일에
'TIBERO_PERFORMANCE_REPOSITORY=Y로 설정하고,
Session 상태 저장 기능을 사용하려면 'ACTIVE_SESSION_HISTORY=Y'로 설정하면 됩니다.
대부분의 경우 이 정도면 충합니다.
이때 TOTAL_SHM_SIZE는 2GB 초과 값으로 설정해야 합니다.
- 관련 TIP 설정(Tibero 7버전 기준)
- TIBERO_PERFORMANCE_REPOSITORY : Y'로 설정하면 Snapshot 저장 기능 활성화합니다.
(기본 값: Y)
- TPR_SNAPSHOT_SAMPLING_INTERVAL : Snapshot을 추출하는 주기를 설정합니다.
(기본 값: 60,단위:분)
TPR_SNAPSHOT_RETENTION : Snapshot을 최대 저장할 기간을 설정합니다. (기본 값: 7,단위:일)
- TPR_SNAPSHOT_TOP_SQL_CNT : 리포트에 출력할 상위SQL개수를 설정합니다.
(기본 값: 5,단위:개)
- TPR_SEGMENT_STATISTICS : 'Y'로 설정하면 TPR에서 Segment 별 Stat 수집 기능을 활성화합니다. (기본 값: N)
- TPR_SNAPSHOT_TOP_SEGMENT_CNT : 리포트에 출력 할 상위 Segment 개수를 설정합니다.
(기본 값: 5,단위:개)
- TPR_METRIC : 'Y'로 설정하면TPR METRIC기능을 활성화합니다. (기본 값: N)
- TPR_AGGREGATION : 'Y'로 설정하면TPR AGGREGATION기능을 활성화합니다. (기본 값: N)
- ACTIVE_SESSION_HISTORY : 'Y'로 설정하면 Session 상태 저장 기능 활성화합니다. (기본 값: N)
- _ACTIVE_SESSION_HISTORY_SAMPLING_INTERVAL : Session 상태 저장 주기를 설정합니다.
(기본 값: 1,단위:초)
- TIBERO_PERFORMANCE_REPOSITORY : Y'로 설정하면 Snapshot 저장 기능 활성화합니다.
- Snapshot
TPR을 설정하면 정해진 주기에 자동으로 Snapshot이 생성되지만,
원하는 경우 현재 시점의 Snapshot을 남길 수 있습니다..[예제] Snapshot 수동 생성 예시
SQL> exec dbms_tpr.create_snapshot();운영 절차
준비
SNAPSHOT 테이블을 조회하여 원하는 기간에 대한 시작, 종료 시각을 확인합니다.
[예제] TPR Snapshot 시작, 종료 대상 조회
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; SQL> select * from _tpr_snapshot;TPR Report 생성
다음은 원하는 기간의 시작과 종료 시점을 각각 begin, end라고 설정한 경우 tbsql 에서 다음과 같이 입력하여 성능 분석 리포트를 작성하는 예시 쿼리 입니다.
인자 값으로는 BEGIN_INTERVAL_TIME, END_INTERVAL_TIME 값을 주면 됩니다.SQL> exec DBMS_TPR.REPORT_TEXT(BEGIN_INTERVAL_TIME, END_INTERVAL_TIME );- 성능 분석 리포트는 다음의 별도 설정이 없다면 아래 와 같이 기본 경로에 파일로 생성됩니다. $TB_HOME/instance/$TB_SID/tpr_report.[mthr_pid].[current_time]
- TPR 분석
Cache Summary
[예제] TPR(Cache Summary)
3.1 Instance Efficiency =============================================== Value -------- Buffer Cache Hit %: 100.00 Library Hit %: 92.73 PP Hit %: 95.32 Latch Hit %: 100.00 Redo Alloc Hit %: 100.00 Non-Parse CPU %: 98.27 Parse Time: 2,182.01 DB Time: 16.53- Tuning Guide
- Buffer Cache Hit
- TSM 내의 Buffer Cache hit 비율로서, 해당 부분이 낮다면, Buffer Cache 사이즈 증가 또는 대량 테이블을 불필요하게 full scan 하는 부분이 없는지 확인합니다.
- TSM 내의 Buffer Cache hit 비율로서, 해당 부분이 낮다면, Buffer Cache 사이즈 증가 또는 대량 테이블을 불필요하게 full scan 하는 부분이 없는지 확인합니다.
- PP Hit
SQL 재사용율 관련 hit 비율(%)입니다.
해당 부분이 낮다면 static query가 많아서 hard parsing 이 자주 일어나는 부분이므로, Application 에서 bind query 를 사용하여 soft parsing 이 일어나도록 변경해 줘야 합니다.
- Buffer Cache Hit
- Tuning Guide
Wait Events by Wait Time
[예제] TPR(Wait Events by Wait Time)
7.3 Wait Events by Wait Time ================================================================================ Time- Wait Max Avg Waits DB Event Waits out(%) Time(s) Wait(ms) Wait(ms) /TX Time(%) ----------------------------- --------------- ------ ------------ ---------- ---------- ---------- ------- block disk read time 1,316 0.0 0.81 35.0 0.62 9.6 4.92 log flush wait for commit 129 0.0 0.53 72.0 4.11 0.9 3.21 ckpt wait 2 0.0 0.32 165.0 159.00 0.0 1.92 multi block disk read time 8 0.0 0.01 6.0 1.63 0.1 0.08 direct path write wait time 4 0.0 0.00 0.0 0.00 0.0 0.00 connection wait 8,083 0.0 52,502.86 3.599E+06 6,495.5 59.00 wthr receive client msg 1,562 0.0 8,327.81 275,828.0 5,331.5 11.40 dbwr idle 39,431 99.6 7,199.68 1,088.0 182.6 287.82 RSRC idle 1,200 100.0 3,600.80 3,060.0 3,000.7 8.76 notification coordinator 3,598 100.0 3,600.54 1,115.0 1,000.7 26.26 notification sender 3,598 100.0 3,600.52 1,115.0 1,000.7 26.26 TPM collector idle 3,598 100.0 3,600.49 1,115.0 1,000.7 26.26 ACTM idle 360 100.0 3,600.24 10,036.0 10,000.7 2.63 job scheduler idle 12 50.0 3,600.18 600,003.0 300,015.1 0.09- Tuning Guide
- 대기 시간 기준 TOP 이벤트 정보입니다.
- 대기 시간 기준으로 가장 많은 시간이 소요되는 대기 이벤트 정보입니다.
- 각 항목의 이름을 기준으로 해서 어떤 이벤트가 가장 많이 소요되는지 확인을 합니다.
- Tuning Guide
SQL Ordered by Elapsed Time
[예제] TPR(SQL Ordered by Elapsed Time)
8.3 SQL Ordered by Elapsed Time ================================================================================ === SQL #1 === Overall Stats ============= Elapsed Execution Time DB SQL Plan Time(s) Count /Exec(s) Time(%) Hash Value Hash Value SQL ID Module -------------- -------------- ------------ ---------- ---------- ---------- ------------- -------------------- 2.06 216 0.01 12.45 775257190 1090908829 fngnts8r3az36 TPR SESSION SQL Text ======== SELECT /*+ default_stat index(HH _DD_HIST_HEAD_IDX1) use_nl_with_index(H _DD_HISTOGRAM_IDX1) ordered */ HH.BUCKET_CNT, HH.NULL_CNT, HH.SAMPLE_SIZE,HH.DISTINCT_CNT, HH.LOW_VAL, HH.HIGH_VAL, HH.AVG_COL_SIZE, NVL(H.BUCKET, 0), NVL(H.DISTINCT_CNT, HH.DISTINCT_CNT), H.END_POINT_ACTUAL FROM _DD_HIST_HEAD HH, _DD_HISTOGRAM H WHERE HH.OBJ_ID = ? AND HH.COL_NO = ? AND HH.HIST_HEAD_ID = H.HIST_HEAD_ID(+) ORDER BY BUCKET Execution Plan ============== ID Output Rows Elapsed(ms) CR Gets CU Gets Plan ----- -------------- -------------- ------------ ------------ -------------------------------------------------------------------------------- 1 0 0.00 0 0 ORDER BY (SORT) (Cost:6, %%CPU:0, Rows:15) 2 0 0.00 0 0 INDEX JOIN (LEFT OUTER) (Cost:6, %%CPU:0, Rows:15) 3 0 0.00 0 0 TABLE ACCESS (ROWID): _DD_HIST_HEAD (Cost:4, %%CPU:0, Rows:1) 4 0 0.00 1 0 INDEX (UNIQUE SCAN): _DD_HIST_HEAD_IDX1 (Cost:3, %%CPU:0, Rows:1) 5 0 0.00 0 0 TABLE ACCESS (ROWID): _DD_HISTOGRAM (Cost:15, %%CPU:0, Rows:14) 6 0 0.00 0 0 INDEX (RANGE SCAN): _DD_HISTOGRAM_IDX1 (Cost:3, %%CPU:0, Rows:14) : Plan statistics is from snapshot #1 (2023/11/17 00:32:46)- Tuning Guide
- 수행 시간이 많이 걸린 SQL의 PLAN을 확인 후 Hint 혹은 Index생성/변경/제거 등의 방법으로 튜닝 합니다.
- 수행 시간이 많이 걸린 SQL의 PLAN을 확인 후 Hint 혹은 Index생성/변경/제거 등의 방법으로 튜닝 합니다.
- Tuning Guide
I/O Statistics
7.9 Tablespace I/O Statistics ================================================================================ Tablespace --------------- Avg. Avg. Avg. Avg. Avg. Avg. Reads Reads/s Read(ms) Blks/Rd Writes Write/s Write(ms) Blks/Wt ------------ -------- ---------- -------- ------------ -------- ---------- -------- SYSTEM 1,340 0.37 0.61 1.08 227 0.06 10.18 1.00 SYSSUB 69 0.02 0.70 1.00 8 0.00 1.43 1.00 UNDO 11 0.00 2.99 1.00 232 0.06 13.29 1.00 USR 6 0.00 1.67 1.00 33 0.01 9.81 1.00 TBS_USER2_002 2 0.00 0.40 1.00 28 0.01 10.16 1.00 TBS_USER1_001 2 0.00 0.26 1.00 27 0.01 9.89 1.00 TEMP 0 0.00 0.00 0.00 0 0.00 0.00 0.00 7.10 File I/O Statistics ================================================================================ Tablespace Filename --------------- ------------------------------------------------------------ Avg. Avg. Avg. Avg. Avg. Avg. Reads Reads/s Read(ms) Blks/Rd Writes Write/s Write(ms) Blks/Wt ------------ -------- ---------- -------- ------------ -------- ---------- -------- SYSSUB /DATA/tibero_data/syssub001.dtf 69 0.02 0.70 1.00 8 0.00 1.43 1.00 SYSTEM /DATA/tibero_data/system001.dtf 1,340 0.37 0.61 1.08 227 0.06 10.18 1.00 TBS_USER1_001 /tbdata/tbs_user_001.dtf 2 0.00 0.26 1.00 27 0.01 9.89 1.00 TBS_USER2_002 /tbdata/tbs_user_002.dtf 2 0.00 0.40 1.00 28 0.01 10.16 1.00 TEMP /DATA/tibero_data/temp001.dtf 0 0.00 0.00 0.00 0 0.00 0.00 0.00 UNDO /DATA/tibero_data/undo001.dtf 11 0.00 2.99 1.00 232 0.06 13.29 1.00 USR /DATA/tibero_data/usr001.dtf 6 0.00 1.67 1.00 33 0.01 9.81 1.00- Tuning Guide
- Tablespace 및 Datafile I/O를 확인할 수 있습니다.
- 특정 Tablespace 및 Datafile에 I/O가 특정 부분에 집중된다면, 테이블 및 인덱스 등의 분산을 고려할 수 습니다.
- Tuning Guide
3.3. Parameter Tuning
위치 지정 Parameter
CONTROL_FILES
- Control file들의 위치를 절대 경로로 지정합니다.
장애에 대비해서 별도의 디스크로 이중화를 권장합니다.
CONTROL_FILES='/data01/Tibero_data/c1.ctl, /data02/Tibero_Data/c2.ctl'
DB_CREATE_FILE_DEST
- Datafile이 저장되는 위치를 절대 경로로 지정합니다.
- I/O 분산을 위해서 Datafile들을 여러 디스크로 분산하는 것을 권장합니다.
LOG_ARCHIVE_DEST
- Archive Log File이 저장될 위치를 절대 경로로 지정합니다.
- Datafile이나 Logfile의 위치와 분리하는 것을 권장합니다.
OPTIMIZER 설정 Paramete
OPTIMIZER_MODE
- Optimizer의 모드를 설정하는 파라미터 입니다.
- FIRST_ROWS_N : 최초의 n개의 결과를 빨리 응답하기 위한 최적화를 수행 합니다.
- ALL_ROWS : 전체 결과를 응답하는 데 가장 비용이 적게 드는 수행 방법을 결정 합니다.
Size 설정 Parameter
DB_BLOCK_SIZE
- Database Block의 크기를 지정합니다.
- Database를 생성할 때 지정 가능하고 DB 생성 후에는 변경이 불가능합니다.
- 일반적으로 OLTP 환경에서는 8K, OLAP or DW환경에서는 16K나 32K를 사용합니다.
TOTAL_SHM_SIZE
- Instance에서 사용할 전체 shared memory의 크기를 정의합니다.
DB_CACHE_SIZE
- 데이터베이스 Cache의 크기를 정의합니다.
- 일반적으로 Single 일 경우 TOTAL_SHM_SIZE의 2/3 정도, TAC 경우 1/2 정도 설정합니다.
- 데이터베이스 Cache가 너무 크면 checkpoint하는 비용이 많이 들지만 Cache Hit Ratio가 증가하여 I/O는 줄일 수 있습니다.
- 데이터베이스 Cache가 너무 작으면 checkpoint비용은 줄지만 I/O는 증가할 수 있습니다.
Execute Memory Parameter
SORT_AREA_SIZE
- 정렬 작업을 위한 크기를 설정합니다.
- EX_MEMORY_AUTO_MANAGEMENT가 Y이면 무시됩니다.
HASH_AREA_SIZE
- Hash 작업을 위한 크기를 설정합니다.
- EX_MEMORY_AUTO_MANAGEMENT가 Y이면 무시됩니다.
EX_MEMORY_AUTO_MANAGEMENT
- Execute memory 영역을 Memory Tuner를 통해 자동으로 설정할 것인지, 아니면 SORT_AREA_SIZE, HASH_AREA_SIZE 등을 수동으로 설정할 지를 결정합니다.
EX_MEMORY_HARD_LIMIT
- Execute memory의 사이즈를 정의합니다.
EX_MEMORY_SOFT_LIMIT_RATIO
- Execute memory중 sort, join 등의 작업에서 사용할 수 있는 execute memory의 비율을 정의합니다. (기본 값 80%)
EX_MEMORY_OPERATION_LIMIT
- Sort, join과 작업 하나에게 할당해 줄 수 있는 execute memory의 최대 값을 정의합니다. (기본 값 5%)
- 하나의 작업이 많은 execute memory를 사용하는 것을 방지합니다.
EX_MEMORY_DRIFT_CHECK_INTERVAL
- 급격한 메모리 상의 변화를 감지하기 위한 주기로 0.1초 단위로 검사합니다. (기본 값 :2)
- Execute memory의 변화가 전체 EX_MEM 양의 10%를 넘어서는 지를 검사하고
10%를 넘으면 drift 합니다.
EX_MEMORY_COMPENSATE_INTERVAL
- SOFT_LIMIT_RATIO, OPERATIOM_LIMIT의 재 설정 주기를 01.초 단위로 정의하고 기본 값은 30입니다.
기타 설정 Parameter
CURSOR_SHARING
- 유사한 SQL의 경우 SQL 문장의 커서 공유 여부를 설정합다.
- EXACT : 문자의 텍스트가 완전히 동일해야만 공유합니다.
- FORCE : SQL 문장 중 WHERE 절의 Literal만 다를 경우에도 공유합니다.
- CURSOR_SHARING을 통한 Cursor의 공유 보다는 애플리케이션에서 Bind 변수 처리를 통한 커서
공유를 권장합니다.
DB_FILE_MULTIBLOCK_READ_COUNT
- 전체 테이블 Scan 시 디스크 상의 데이터 파일에서 여러 블록을 한 번에 읽어 올 때 한번에 읽는 블록의 수를 정의합니다.
- Scan에 필요한 I/O 호출 수를 줄일 수 있어 풀 테이블 Scan의 성능을 향상 시킬 수 있습니다.
기본 값 : 8, Instance 레벨에서 설정 합니다. - 각 플랫폼 별로 해당 파라미터에 대한 제한이 다르므로 확인을 해야 합니다.
UNDO_RETENTION
- UNDO 정보를 재 사용하기 전에 디스크에 저장하고 있을 시간을 초 단위로 설정합니다.
- ‘Snapshot Too Old’ 같은 에러 발생을 줄이는 데 도움이 됩니다.
- UNDO Tablespace에 충분한 공간이 있거나 확장하여 공간을 확보할 수 있으면 UNDO 정보는
Transaction이 만료되기 전까지 재 사용되지 않습니다. - 공간이 충분하지 않더라도 Transaction은 실패하지 않는데 commit된 Transaction이 생성한 UNDO 공간을 재 사용 할 수 있습니다. 그래도 부족하면 에러가 발생하게됩니다.
3.4. Tuning 관련 참조
[표] Snapshot 저장 관련 View
| View | 설명 |
|---|---|
| _TPR_SNAPSHOT | 저장된 Snapshot의 ID와 시간에 관한 정보를 관리합니다. |
| _TPR_BASELINE | 등록된 Baseline의 정보를 관리합니다. |
| _TPR_ACTIVE_SESSION_HISTORY | 저장된 ASH Sample정보를 관리합니다. |
| _TPR_METRIC | 저장된 TPR Metric정보를 관리합니다. |
| _TPR_JCNTSTAT | _VT_JCNTSTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_SQLSTATS | V$SQLSTATS View의 Snapshot 정보를 관리합니다. |
| _TPR_SQL_PLAN | V$SQL_PLAN뷰의 Snapshot 정보를 관리합니다. |
| _TPR_SQL_PLAN_STAT | V$SQL_PLAN_STATISTICS View의 Snapshot 정보를 관리합니다. |
| _TPR_LATCH | V$LATCH View의 Snapshot 정보를 관리합니다. |
| _TPR_SYSTEM_EVENT | V$SYSTEM_EVENT View의 Snapshot 정보를 관리합니다. |
| _TPR_WAITSTAT | V$WAITSTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_SGASTAT | V$SGASTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_PGASTAT | V$PGASTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_LIBRARYCACHE | V$LIBRARYCACHE View의 Snapshot 정보를 관리합니다. |
| _TPR_SQLTEXT | V$SQLTEXT View의 Snapshot 정보를 관리합니다. |
| _TPR_FILESTAT | V$FILESTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_SEGMENTSTAT | V$SEGMENT_STATISTICS View의 Snapshot 정보를 관리합니다. |
| _TPR_TEMPSEG_OP_USAGE | V$TEMPSEG_OP_USAGE View의 Snapshot 정보를 관리합니다. |
| _TPR_PROCESS | V$PROCESS View의 Snapshot 정보를 관리합니다. |
| _TPR_SESSION | V$SESSION View의 Snapshot 정보를 관리합니다. |
| _TPR_WAITER_SESSION | V$WAITER_SESSION View의 Snapshot 정보를 관리합니다. |
| _TPR_UNDOSTAT | V$UNDOSTAT View의 Snapshot 정보를 관리합니다. |
| _TPR_OSSTAT2 | V$OSSTAT2 View의 Snapshot 정보를 관리합니다. |
| _TPR_SQLWA_HIST | V$SQLWA_HIST View의 Snapshot 정보를 관리합니다. |
| _TPR_MODIFIED_PARAM | _VT_PARAMETER 테이블의 Snapshot 정보를 관리합니다. |
| _TPR_MISC | Session 수와 같은 기타 정보의 Snapshot 정보를 관리합니다. |
[표] Session 상태 저장 관련 View
| View | 설명 |
|---|---|
| _TPR_ACTIVE_SESSION_HISTORY | 활동 중인 Session 상태 정보를 관리합니다. |
| V$ACTIVE_SESSION_HISTORY | 최근 1시간 동안의 Session 상태 정보를 관리합니다. |