Tibero

Transcription

1 Tibero 모니터링가이드 Copyright 2013 TmaxData Co., Ltd. All Rights Reserved.

2 Copyright Notice Copyright 2013 TmaxData Co., Ltd. All Rights Reserved. 대한민국경기도성남시분당구황새울로 329 번길 5 티맥스빌딩우 ) Restricted Rights Legend All TmaxData Software (Tibero ) and documents are protected by copyright laws and international convention. TmaxData software and documents are made available under the terms of the TmaxData License Agreement and may only be used or copied in accordance with the terms of this agreement. No part of this document may be transmitted, copied, deployed, or reproduced in any form or by any means, electronic, mechanical, or optical, without the prior written consent of TmaxData Co., Ltd. 이소프트웨어 (Tibero ) 사용서의내용과프로그램은저작권법과국제조약에의해서보호받고있습니다. 사용서의내용과여기에된프로그램은 TmaxData Co., Ltd. 와의사용권계약하에서만사용이가능하며, 사용권계약을준수하는경우에만사용또는복제할수있습니다. 이사용서의전부또는일부분을 TmaxData 의사전서면동의없이전자, 기계, 녹음등의수단을사용하여전송, 복제, 배포, 2차적저작물작성등의행위를하여서는안됩니다. Trademarks Tibero is a registered trademark of TmaxData Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Tibero 는 TmaxData Co., Ltd. 의등록상표입니다. 기타모든제품들과회사이름은각각해당소유주의상표로서참조용으로만사용됩니다. 안내서정보안내서제목 : Tibero 모니터링가이드발행일 : 소프트웨어버전 : Tibero 5 안내서버전 : 2.1.1

3 내용목차 안내서에대하여... vii 제1장 기본정보 Tibero 인스턴스및데이터베이스정보 TSM(Tibero Shared Memory) 정보... 2 제2장 메모리 데이터베이스버퍼 Cache 적중률 SQL Cache 적중률 Dictionary Cache 적중률 공유 Cache의 Free 메모리 제3장 Tibero 대기이벤트 V$SYSTEM_EVENT V$SYSSTAT Spin Lock Contention Spin Lock 모니터링 제4장 Lock & Current 트랜잭션 Current 세션의수 Current 트랜잭션 Current SQL 정보 (for 세션 ) WLOCK 정보 (for 세션 ) WLOCK Holder & Waiter 정보 제5장 디스크 I/O 모니터링 데이터파일 I/O Online Redo Log Switch 횟수 제6장 공간관리 빈번한 Extents 발생 Tablespace 사용률 Undo 세그먼트사용률 Undo 세그먼트조회 현재사용중인 Undo 확인 Undo 세그먼트의수동감소 제7장 파라미터튜닝 위치지정파라미터 Optimizer 설정파라미터 Execute 메모리파라미터 기타설정파라미터 데이터베이스 I/O 튜닝 Tibero iii

4

5 그림목차 [ 그림 3.1] V$session_wait 수행결과 [ 그림 3.2] V$system_event 수행결과 [ 그림 3.3] WE_LOG_FLUSH_COMMIT 개선효과 [ 그림 3.4] WE_BUF_WAIT event [ 그림 3.5] Spin Lock 획득과정 Tibero v

6

7 안내서에대하여 안내서의대상 본안내서는 DB 모니터링가이드문서이다. 안내서의전제조건 본안내서는모니터링방법에대해한안내서이다. 따라서본안내서를원활히이해하기위해서는다음과같은사항을미리알고있어야한다. 데이터베이스의이해 RDBMS 의이해 운영체제및시스템환경의이해 UNIX 계열 (Linux 포함 ) 의기본지식 안내서의제한조건 본안내서는 Tibero 모니터링가이드문서는실무에적용하거나운용하는데필요한내용을포함하고있 다. 안내서에대하여 vii

8 안내서규약 표기 <AaBbCc123> <Ctrl>+C [Button] 진하게 " "( 따옴표 ) ' 입력항목 ' 하이퍼링크 > 참고 의미프로그램소스코드의파일명, 디렉터리 Ctrl과 C를동시에누름 GUI의버튼또는메뉴이름강조다른관련안내서또는안내서내의다른장및절언급화면 UI에서입력항목에대한메일계정, 웹사이트메뉴의진행순서하위디렉터리또는파일있음하위디렉터리또는파일없음참고또는주의사항 [ 그림 1.1] [ 표 1.1] AaBbCc123 그림이름 표이름 명령어, 명령어수행후화면에출력된결과물, 예제코드 { } [ ] 필수인수값 옵션인수값 viii Tibero 모니터링가이드

9 제 1 장기본정보 본장에서는 Tibero 데이터베이스기본정보및 TSM 정보에대해서한다 Tibero 인스턴스및데이터베이스정보 대상데이터베이스의인스턴스및데이터베이스의기본정보를확인한다. 다음은 Single DB 쿼리작성예이다. SELECT i.version, bd.value "Build Ver", i.instance_name, d.name "DB name", i.status, ch.value "NLS_CHARACTERSET", d.log_mode, d.create_date "DB create_date", d.current_tsn, d.ckpt_tsn, l.edition, l.expire_date, l.limit_user, l.limit_cpu FROM _vt_instance i, v$database d, _vt_license l, (SELECT value FROM _vt_version WHERE name='build_number') bd, (SELECT value FROM _dd_props WHERE name='nls_characterset') ch; 결과는아래와같다. VERSION Build Ver INSTANCE_NAME DB name STATUS NLS_CHARACTERSET LOG_MODE duduk duduk NORMAL MSWIN949 ARCHIVELOG DB create_date CURRENT_TSN CKPT_TSN EDITION EXPIRE_DATE LIMIT_USER LIMIT_CPU 제 1 장기본정보 1

10 /03/ standard 2013/09/05 UNLIMITED UNLIMITED 1 row selected. 각항목에대한은다음과같다. 항목 VERSION Build Ver INSTANCE_NAME DB name STATUS NLS_CHARACTER SET LOG_MODE DB create_date CURRENT_TSN CKPT_TSN EDITION EXPIRE_DATE Tibero의버전이다. Tibero의빌드번호이다. 데이터베이스의이름이다. (Init 파라미터의 DB_NAME) 데이터베이스를생성했을때의 SID이다. ( 컨트롤파일에적혀있는데이터베이스의이름으로항상인스턴스이름과동일함 ) Tibero 서버의현재 Boot 모드상태이다. (NOMOUNT, MOUNT, NORMAL 등 ) 데이터베이스에자료가저장될때사용되는 Encoding으로 Create Database 구문에서결정한다. (Characterset UTF-8, MS949(EUC-KR), ASCII 등을지원함 ) Redo Log를 Archive하고있는지아닌지확인한다. 데이터베이스의생성날짜이다. 현재의 TSN 값이다. 마지막으로체크포인트가일어난시간이다. (=TSN) 라이선스관련내용이다. 라이선스의만료날짜이다 TSM(Tibero Shared Memory) 정보 Tibero 메모리를구성하는 TSM 에대한정보및 DB 운영에필요한기본적인메모리정보를확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT 'TSM(Tibero Shared Memory) FROM v$sga WHERE name = 'SHARED MEMORY' UNION ALL SELECT 'Shared Cache Size FROM v$sga WHERE name = 'SHARED POOL MEMORY' UNION ALL : ' total/1024 ' K' as "TSM(Tibero Shared Memory)" : ' ROUND(total/1024, 0) ' K' 2 Tibero 모니터링가이드

11 SELECT 'Database Buffer Size : ' value/1024 ' K' FROM _vt_parameter WHERE name='db_cache_size' UNION ALL SELECT 'DB Block Size : ' value/1024 ' K' FROM _vt_parameter WHERE name = 'DB_BLOCK_SIZE' UNION ALL SELECT 'Redo Log BUffer Size : ' value/1024 ' K' FROM _vt_parameter WHERE name = 'LOG_BUFFER' UNION ALL SELECT 'WPM(Working Process Memory) : ' ROUND(value/1024, 0) ' K' FROM _vt_parameter WHERE name='ex_memory_hard_limit'; 결과는아래와같다. TSM(Tibero Shared Memory) TSM(Tibero Shared Memory) : K Shared Cache Size : K Database Buffer Size : K DB Block Size : 8 K Redo Log BUffer Size : K WPM(Working Process Memory) : K 6 rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT Inst_id, TSM as "TSM(Tibero Shared Memory)" FROM (SELECT Inst_id, 'TSM(Tibero Shared Memory) : ' total/1024 ' K' as TSM FROM gv$sga WHERE name = 'SHARED MEMORY' UNION ALL SELECT Inst_id, 'Shared Cache Size : ' ROUND(total/1024, 0) ' K' as TSM FROM gv$sga WHERE name = 'SHARED POOL MEMORY' 제 1 장기본정보 3

12 UNION ALL SELECT inst_id, 'Database Buffer Size : ' value/1024 ' K' as TSM FROM _gvt_parameter WHERE name='db_cache_size' UNION ALL SELECT inst_id, 'DB Block Size : ' value/1024 ' K' as TSM FROM _gvt_parameter WHERE name = 'DB_BLOCK_SIZE' UNION ALL SELECT inst_id, 'Redo Log BUffer Size : ' value/1024 ' K' as TSM FROM _gvt_parameter WHERE name = 'LOG_BUFFER' UNION ALL SELECT inst_id, 'WPM(Working Process Memory) : ' ROUND(value/1024, 0) ' K' FROM _gvt_parameter WHERE name='ex_memory_hard_limit') ORDER BY 1 결과는아래와같다. INST_ID TSM(Tibero Shared Memory) 101 TSM(Tibero Shared Memory) : K 101 Shared Cache Size : K 101 Database Buffer Size : K 101 DB Block Size : 8 K 101 Redo Log BUffer Size : K 101 WPM(Working Process Memory) : K 102 TSM(Tibero Shared Memory) : K 102 Shared Cache Size : K 102 Database Buffer Size : K 102 DB Block Size : 8 K 102 Redo Log BUffer Size : K 102 WPM(Working Process Memory) : K 각항목에대한은다음과같다. 항목 TSM(Tibero Shared Memory) Tibero 에서사용하는전체공유메모리크기로 Init 파라미터에서조정 한다. (TOTAL_SHM_SIZE) TSM = 공유 Cache 크기 + 데이터베이스버퍼크기 + 고정메모리 4 Tibero 모니터링가이드

13 항목 ( 고정메모리 : 최초전역변수나 Thread 개수등에따라사이즈가결정 되며 Runtime 에결정 ) Shared Cache Size Tibero 에서사용하는공유메모리 ( 공유 Pool : 라이브러리 Cache, DD Cache) 로각종용도에사용된다. 공유 Cache 크기는 (TSM - 고정메모리 - 데이터베이스버퍼크기 ) 로결 정된다. 공유 Cache 와라이브러리 Cache 의영역제한은없다 Database Buffer Size DB Block Size Redo Log Buffer Size WPM(Working Process Mem ory) 데이터베이스버퍼 Cache 크기로환경파일에서 DB_CACHE_SIZE로조정이가능하다. 데이터베이스 Block의크기로 Init 파라미터에서조정 (DB_BLOCK_SIZE) 이가능하며 Create Database 이후엔변경이불가능하다. Redo Log를저장하는메모리공간의크기를지정하며 Init 파라미터에서조정 (LOG_BUFFER) 이가능하다. Tibero에서사용할수있는물리메모리의총크기를말하며 Init 파라미터에서조정 (EX_MEMORY_HARD_LIMIT) 이가능하다. 제 1 장기본정보 5

14

15 제 2 장메모리 본장에서는 Tibero 에서사용되는메모리정보에대해서한다 데이터베이스버퍼 Cache 적중률 데이터베이스버퍼 Cache의적중률을점검한다. 데이터베이스버퍼의적중률이란사용자가액세스한메모리 Block 가운데이미 Cache가되어있어물리적 I/O 없이액세스할수있는 Block의비율을나타낸다. 만약데이터베이스버퍼의적중률이권장값미만일경우에는할당된데이터베이스버퍼의크기가너무적거나지나치게많은 I/O를유발하는애플리케이션이존재한다는것을의미한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT pr1.value + pr2.value "Physical read", bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read", TRUNC((1-((pr1.value+pr2.value)/ (bg1.value+bg2.value+bg3.value +bg4.value+bg5.value)) ) *100, 2) "hit(more THAN 60-70%)" FROM v$sysstat pr1, v$sysstat pr2, v$sysstat bg1, v$sysstat bg2, v$sysstat bg3, v$sysstat bg4, v$sysstat bg5 WHERE pr1.name = 'block disk read' and pr2.name = 'multi block disk read - blocks' and bg1.name = 'consistent block gets' and bg2.name = 'consistent multi gets - blocks' and bg3.name = 'block gets (CRX)' and bg4.name = 'current block gets' and bg5.name = 'current block gets - no wait'; 결과는아래와같다. Physical read Logical read hit(more THAN 60-70%) 제 2 장메모리 7

16 1 row selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT ins.inst_id, pr1.value + pr2.value "Physical read", bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read", TRUNC((1-((pr1.value+pr2.value)/ (bg1.value+bg2.value+bg3.value+bg4.value+bg5.value)) ) *100, 2) "hit(more THAN 60-70%)" FROM (select distinct inst_id from gv$sysstat ) ins, gv$sysstat pr1, gv$sysstat pr2, gv$sysstat bg1, gv$sysstat bg2, gv$sysstat bg3, gv$sysstat bg4, gv$sysstat bg5 WHERE pr1.name = 'block disk read' and pr2.name = 'multi block disk read - blocks' and bg1.name = 'consistent block gets' and bg2.name = 'consistent multi gets - blocks' and bg3.name = 'block gets (CRX)' and bg4.name = 'current block gets' and bg5.name = 'current block gets - no wait' and pr1.inst_id = ins.inst_id and pr2.inst_id = ins.inst_id and bg1.inst_id = ins.inst_id and bg2.inst_id = ins.inst_id and bg3.inst_id = ins.inst_id and bg4.inst_id = ins.inst_id and bg5.inst_id = ins.inst_id 결과는아래와같다. INST_ID Physical read Logical read hit(more THAN 60-70%) Tibero 모니터링가이드

17 2 rows selected. 각항목에대한은다음과같다. 항목 INST_ID Physical read Logical read hit Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 데이터 Block을디스크에서읽어오는횟수이다. 데이터 Block을버퍼 Cache에요청한횟수이다. Hit 버퍼캐시의적중률로계산법은 ( 1 - physical read / logical read) * 100이다. 데이터베이스버퍼의적중률은 OLTP, DSS, DW 등데이터베이스를사용하는애플리케이션의특성에따라권장값이다르다. 일반적으로 OLTP 시스템은 90% 이상이좋고, DSS나 DW 시스템은 80~85% 이상이면좋다고할수있으나순수하게 Batch 처리업무위주의시스템인경우는 50% 아래라도문제가된다고단정할순없다. 데이터베이스버퍼의적중률이기준값보다낮은경우에는 O/S상메모리여유량을확인하여메모리부족으로인한 Paging, Swapping이발생하지않는한도내에서 tip 파일의 DB_CACHE_SIZE값을늘려데이터베이스버퍼의크기를증가시킬수있다. 그러나현실적으로디스크상의모든데이터 Block을 Cache하는것은불가능하므로일반적으로데이터베이스버퍼의증가가최선의해결책이될수는없다. 특히, 다량의디스크 Block을빈번히 Full Scan하는애플리케이션이존재한다면적중률을높은수준으로유지하는것이불가능하므로액세스패턴이비효율적인애플리케이션을추출하여지속적인튜닝을수행해야한다 SQL Cache 적중률 SQL Cache 적중률을확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio FROM v$librarycache WHERE namespace = 'SQL AREA'; 결과는아래와같다. 제 2 장메모리 9

18 NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO SQL AREA row selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT inst_id, namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio FROM GV$LIBRARYCACHE WHERE namespace = 'SQL AREA' 결과는아래와같다. INST_ID NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO SQL AREA SQL AREA rows selected. 각항목에대한은다음과같다. 항목 INST_ID NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) Library Cache, DD Cache의항목들이다. (SQL AREA : Library Cache) Cache의총접근횟수이다. Cache의적중횟수이다. Cache의적중률로계산법은 (PINHITS / PINS) * 100이다. Cache에있는객체에대한요청을 PIN 한횟수이다. Cache에이미 PIN 되어진객체의횟수이다. PIN 수로나눈 PINHITS 수의비율로 1에가까운값은객체시스템의대부분이 PIN을시도하고액세스 Cache 된것을나타낸다. 10 Tibero 모니터링가이드

19 데이터 Dictionary Cache 의적중률은 90% 이상유지할것을권장한다. 적중률이기준값이하일때초기화 파라미터 TOTAL_SIZE 와 DB_CACHE_SIZE 의비율을조정하여공유 Cache 의크기를증가시켜적중률 을높일수있다 Dictionary Cache 적중률 Dictionary Cache 적중률을확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time", ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1) "Dictionary Cache Hit Ratio(%)" FROM v$rowcache; 결과는아래와같다. Current Time Dictionary Cache Hit Ratio(%) /05/16 17:34: row selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT INST_ID, TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time", ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1) "Dictionary Cache Hit Ratio(%)" FROM gv$rowcache GROUP BY INST_ID; 결과는아래와같다. INST_ID Current Time Dictionary Cache Hit Ratio(%) /05/16 17:42: /05/16 17:42: 제 2 장메모리 11

20 2 rows selected. 각항목에대한은다음과같다. 항목 INST_ID Current Time Hit Ratio Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 해당 SQL을수행한시간이다. 데이터 Dictionary Cache의적중률이다. 데이터 Dictionary Cache는데이터 Dictionary 정보를 Cache하는영역으로공유 Cache에서할당한다. Dictionary Cache의크기는사용자가따로지정할수없다. Tibero는공유 Cache 가운데 Dictionary Cache 를우선적으로할당하므로라이브러리 Cache의적중률이양호하다면 Dictionary Cache의적중률도양호하다. Dictionary Cache 적중률은인스턴스가기동한후어느정도시간이지난후에야의미가있으며적중률을 90% 이상유지할것을권장한다. 적중률이기준값이하일때초기화파라미터 TOTAL_SHM_SIZE와 DB_CACHE_SIZE의비율을조정하여공유 Cache의크기를증가시켜적중률을높일수있다 공유 Cache 의 Free 메모리 공유 Cache 의 Free 메모리크기를확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT round(total/1024/1024, 1) "Shared Cache Total (MB)", round(used/1024/1024, 1) "Used (MB)", round((total - used)/1024/1024, 1) "free (MB)" FROM v$sga WHERE name='shared POOL MEMORY'; 리결과는아래와같다. Shared Cache Total (MB) Used (MB) free (MB) row selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. 12 Tibero 모니터링가이드

21 TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT inst_id, round(total/1024/1024, 1) "Shared Cache Total (MB)", round(used/1024/1024, 1) "Used (MB)", round((total - used)/1024/1024, 1) "free (MB)" FROM gv$sga WHERE name='shared POOL MEMORY'; 결과는아래와같다. INST_ID Shared Cache Total (MB) Used (MB) free (MB) rows selected. 각항목에대한은다음과같다. 항목 INST_ID Shared Cache Total Used free Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 전체공유 Cache의메모리사이즈이다. 현재사용중인메모리의양이다. 가용한메모리의사용량이다. 공유 Cache 영역은라이브러리 Cache, Dictionary Cache로구성된다. 공유 Cache는기본적으로 Cache 를위한영역이므로여유공간이남아있는한기존오브젝트에대해 Cache에두고계속소모하려는속성이있다. 따라서라이브러리 Cache나 Dictionary Cache의적중률이양호하다면일반적인운영시간대에 Free 메모리크기가낮은상태를유지하는것은정상이다. 항상큰 Free 메모리가남아있다면공유 Cache가불필요하게크게설정되어있다는것을의미하며일시적으로갑자기 Free 메모리크기가증가하는경우는 Fragmentation이많이발생하여많은오브젝트가한꺼번에 Flush된것을의미하므로문제를겪고있다는증상일수있다. 공유 Cache 메모리 Fragmentation을예방하기위해사이즈가큰 PSM Block 사용을자제하고 Literal SQL 문의경우바인드변수를사용하도록수정한다. 제 2 장메모리 13

22

23 제 3 장 Tibero 대기이벤트 본장에서는 Tibero 에서발생하는대기이벤트정보에대해서한다 V$SYSTEM_EVENT Tibero는서버프로세스와백그라운드프로세스들이일을처리하는과정에서발생하는대기현상들을측정하기위해여러가지대기이벤트들을정의하고프로세스들이일을진행하다가대기이벤트가발생할때마다횟수와대기시간을내부에저장한다. V$system_event는인스턴스기동후현재까지누적된이벤트발생현황을시스템레벨로확인할때사용한다. 개별세션별로누적치를확인하려면 V$session_event를조회하고세션레벨에서현재진행중이거나바로직전에발생했던이벤트정보를보여주는 View는 V$session_wait이다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT name, total_waits, time_waited/ "time_waited", average_wait/ "average_wait", total_timeouts FROM v$system_event WHERE Total_waits>0 ORDER BY time_waited DESC; 결과는아래와같다. NAME TOTAL_WAITS time_waited average_wai TOTAL_TIMEOUTS WE_CONN_IDLE WE_LARC_IDLE WE_DBWR_IDLE WE_SEQ_IDLE WE_LGWR_IDLE WE_CKPT_IDLE WE_WTHR_RECV WE_JC_REDO_WRITE WE_JC_BUF_DISK_READ WE_JC_DBWR_WRITE_OS 제 3 장 Tibero 대기이벤트 15

24 WE_JC_LARC_WRITE WE_LOG_FLUSH_COMMIT WE_JC_BUF_MBR_GET_COUNT WE_JC_BUF_DISK_READM WE_BG_READY WE_SPIN_TOTAL_WAIT WE_LOG_FLUSH_SPACE WE_JC_BUF_MBR_CHECK_CA WE_SPIN_BUF_WS WE_JC_BUF_WAIT_WRITE WE_BUF_WRITE WE_SPIN_SHP_ALLOC WE_WTHR_READY NAME TOTAL_WAITS time_waited average_wai TOTAL_TIMEOUTS WE_LOG_SWITCH_REQ WE_SPIN_BUF_BUCKET WE_SPIN_ALLOC_LRU WE_LOG_FLUSH_REQ WE_WLOCK_DD_USER WE_WLOCK_CF WE_WLOCK_MR WE_WLOCK_TX WE_WLOCK_ST WE_WLOCK_ST_SGMT WE_WLOCK_SPLIT WE_WLOCK_SESS WE_WLOCK_SVRMODE WE_WLOCK_DML WE_WLOCK_USER WE_WLOCK_CLEANUP_DROPPED_SE WE_WLOCK_UNDO_TS WE_WLOCK_IR WE_WLOCK_XA_BUCKET WE_WLOCK_SEQ_GET_NEXTVAL WE_WLOCK_DD_AUX_STATS WE_WLOCK_DD_HISTOGRAM WE_WLOCK_DD_PSMIR NAME TOTAL_WAITS time_waited average_wai TOTAL_TIMEOUTS WE_WLOCK_DD_SYSAUTH WE_WLOCK_DD_OBJAUTH WE_WLOCK_DD_TS WE_WLOCK_DD_SGMT WE_WLOCK_DD_OBJ Tibero 모니터링가이드

25 51 rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT inst_id, name, total_waits, time_waited/ "time_waited", average_wait/ "average_wait", total_timeouts FROM gv$system_event WHERE Total_waits>0 ORDER BY inst_id, time_waited DESC ; 결과는아래와같다. INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS WE_ACF_RCVR WE_CONN_IDLE WE_ACF_SCVR WE_ACF_SCNR WE_DBWR_IDLE WE_LARC_IDLE WE_SEQ_IDLE WE_ACF_SNDR WE_LGWR_IDLE WE_CKPT_IDLE WE_CCC_AST_CR WE_CCC_AST_CUR WE_WTHR_RECV WE_WLOCK_TX WE_CWS_AST WE_JC_DBWR_WRITE_OS WE_JC_BUF_DISK_READ WE_SPIN_TOTAL_WAIT WE_SPIN_BUF_BUCKET WE_JC_REDO_WRITE WE_LOG_FLUSH_COMMIT WE_BUF_WAIT WE_MT_DRIFT 제 3 장 Tibero 대기이벤트 17

26 INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS WE_LOG_FLUSH_SPACE WE_BUF_WRITE WE_JC_BUF_WAIT_WRITE WE_SVC_TX WE_JC_BUF_DISK_READM WE_SPIN_BUF_WS WE_ACF_GMC WE_WTHR_MSG WE_CCC_AST_MBR WE_SPIN_ALLOC_LRU WE_JC_BUF_MBR_GET_co WE_JC_BUF_MBR_CHECK_ WE_SPIN_SHP_ALLOC WE_WLOCK_ST_SGMT WE_TEMP_GRANULE_ACQU WE_WLOCK_SPLIT WE_WLOCK_ST WE_WLOCK_CF WE_WLOCK_CLEANUP_DRO_ WE_WLOCK_TEMP_GRANUL WE_WLOCK_XA_BUCKET WE_WLOCK_DDL_CREATE_ WE_WLOCK_SESS INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS WE_WLOCK_DML WE_WLOCK_USER WE_WLOCK_DD_OBJ WE_WLOCK_DD_USER WE_WLOCK_DD_SGMT WE_WLOCK_DD_TS WE_WLOCK_DD_OBJAUTH WE_WLOCK_DD_SYSAUTH WE_WLOCK_DD_PSMIR WE_WLOCK_DD_HISTOGRA WE_WLOCK_DD_AUX_STAT WE_WLOCK_SEQ_GET_NEX WE_CONN_IDLE WE_ACF_RCVR WE_ACF_SCVR WE_ACF_SCNR WE_DBWR_IDLE WE_LARC_IDLE WE_SEQ_IDLE Tibero 모니터링가이드

27 102 WE_CKPT_IDLE WE_ACF_SNDR WE_LGWR_IDLE WE_WTHR_RECV INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS WE_WLOCK_TX WE_CCC_AST_CR WE_SPIN_TOTAL_WAIT E WE_CWS_AST WE_CCC_AST_CUR WE_LOG_FLUSH_COMMIT WE_JC_DBWR_WRITE_OS WE_SPIN_BUF_BUCKET WE_JC_REDO_WRITE WE_JC_BUF_DISK_READ WE_MT_DRIFT WE_BUF_WAIT WE_LOG_FLUSH_SPACE WE_SPIN_BUF_WS WE_SPIN_SHP_ALLOC WE_BUF_WRITE WE_SPIN_ALLOC_LRU WE_JC_BUF_WAIT_WRITE WE_WTHR_MSG WE_JC_BUF_DISK_READM WE_CCC_AST_MBR WE_JC_BUF_MBR_CHECK_ WE_ACF_GMC INST_ID NAME TOTAL_WAITS time_waited average_wait TOTAL_TIMEOUTS WE_SVC_TX WE_JC_BUF_MBR_GET_CO WE_WLOCK_ST_SGMT WE_WLOCK_SPLIT WE_WLOCK_CF WE_WLOCK_ST WE_WLOCK_SESS WE_WLOCK_DML WE_WLOCK_DD_OBJ WE_WLOCK_XA_BUCKET WE_WLOCK_SEQ_GET_NEX WE_WLOCK_DD_AUX_STAT WE_WLOCK_USER WE_WLOCK_DD_SYSAUTH 제 3 장 Tibero 대기이벤트 19

28 102 WE_WLOCK_DD_TS WE_WLOCK_DD_SGMT WE_WLOCK_DD_USER WE_WLOCK_DD_HISTOGRA rows selected. 각항목에대한은다음과같다. 항목 INST_ID TOTAL_WAITS time_waited Average_wait Total_Timeout Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 해당이벤트가호출된총횟수이다. 해당이벤트에서대기한시간이다. ( 단위 : 초 ) 해당이벤트에서한번에대기한평균시간이다. ( 단위 : 초 ) 해당이벤트에 Timeout이설정이되어있을때 Timeout 발생횟수이다. 주요이벤트는아래와같다. 이벤트 WE_SEQ_WRITEBACK WE_SEQ_FREESLOT WE_SEQ_NEXTVAL WE_BUF_WAIT WE_BUF_WRITE WE_BUF_FREE WE_MBR_WAIT WE_SEQ_FLUSH WE_WTHR_READY WE_LGWR_ARCHIVE WE_LGWR_LNW WE_LOG_FLUSH WE_LOG_FLUSH_SPACE WE_LOG_FLUSH_REQ WE_CKPT_WAIT WE_PROC_DOWN WE_WTHR_START WE_WTHR_MSG Agent가 Seq를 Writeback할때까지기다리는이벤트다. Seq_Buffer_ 의 Free Slot이없을때까지기다리는이벤트다. Agent가 New Value를 Cache에올릴때까지기다리는이벤트다. Buf Pin을위해기다리는이벤트다. Buf Write까지기다리는이벤트다. Free Buf가생길때까지기다리는이벤트다. MBR이끝날때까지기다리는이벤트다. Seq alba가 Cache Flush를마칠때까지기다리는이벤트다. WTHR Ready까지기다리는이벤트다. LGWR Process가 Archive 완료할때까지기다리는이벤트다. LGWR Process가네트워크를통해로그를쓸때까지기다리는이벤트다. Log Flush 할때까지기다리는이벤트다. 공간이부족해서 Log Flush할때까지기다리는이벤트다. Log Flush를요청해서기다리는이벤트다. Checkpoint를기다리는이벤트다. Shutdown Finish되는경우발생하는이벤트다. WTHR가시작되기위해서메시지를받을때까지기다리는이벤트다. WTHR가클라이언트의 MSG를기다리는이벤트다. 20 Tibero 모니터링가이드

29 이벤트 WE_ACF_SCVR WE_ACF_AST SCVR(scavenger) 에서 CCC/CWS RSB(Resource Block) 를 reclaim 요청받을때까지기다리는이벤트다. 만약 reclaim 요청이없더라도주기적 (_ACF_SCVR_TIMEOUT( 기본값 : 1초 )) 으로깨어나서 reclaim을수행한다. WTHR가 AST(asynchronous system trap) 인경우이벤트다. Waiting 은실제수행되는작업의성능을저하하므로가장많이발생하는대기이벤트를중점적으로해결 하고각대기이벤트의종류에따라 Waiting 이많이발생하는이벤트를해결한다. 또한모든 wlock 관련 정보는대기이벤트로나타나므로 wlock Contention 을알아보는데사용할수있다. 예제 다음은여러세션이특정 Block에지속적으로업데이트를하면서동시에계속 Commit을수행하고있을때 WE_LOG_FLUSH_COMMIT 이벤트가많이발생하는예이다. V$session_wait 쿼리를조회해보면다음과같다. [ 그림 3.1] V$session_wait 수행결과 V$system_event 수행결과를통해해당이벤트가 번호출된것을아래와같이확인할수있다. [ 그림 3.2] V$system_event 수행결과 Commit 횟수를줄이면 WE_LOG_FLUSH_COMMIT 이벤트가호출된횟수또는 Waiting 한시간이크게 개선된것을확인할수있다. 제 3 장 Tibero 대기이벤트 21

30 [ 그림 3.3] WE_LOG_FLUSH_COMMIT 개선효과 WE_LOG_FLUSH_COMMIT 이벤트가줄어든만큼 Hot Block의경합으로인해 WE_BUF_WAIT 이벤트가발생하는것을확인할수있다. WE_BUF_WAIT는 Buffer Pinning을위해대기하는이벤트로동일 Block 에대해업데이트를하기때문에발생하는이벤트다. [ 그림 3.4] WE_BUF_WAIT event 참고 여러개의세션이서로다른로우를업데이트하더라도같은 Block 안에있다고하면 Buffer Lock 에 의한동기화가필요하다 V$SYSSTAT 인스턴스기동후현재까지누적된수행통계치를시스템레벨로확인할때사용하는 View가 v$sysstat 이고개별세션별로확인할때사용하는 View가 v$sesstat 이다. 또한현재접속해있는본인세션에대한수행통계는 v$mystat을통해확인이가능하다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT * FROM v$sysstat order by value desc; 22 Tibero 모니터링가이드

31 결과값은아래와같다. STAT# NAME CLASS VALUE transactions total time csr fetch update time redo log size table full scan time fscan fetch time consistent multi gets time table full scan - ex filtering and compacting time redo write time current block gets - wait time candidate bh scanned buffers sql dml lock acquire time dbwr write time dbwr write time - OS loga write time fast cleanout time redo wait time for flush time block disk read time candidate bh scanned block unpin block pin - not conflict block cleanout time consistent rollback time fscan fetch - multiblk read time optimizer time consistent gets - no clone csr fetch insert time fscan fetched block count get multi block read count - block count 이하생략... 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT * FROM gv$sysstat order by inst_id, value desc; 제 3 장 Tibero 대기이벤트 23

32 결과값은아래와같다. INST_ID STAT# NAME CLASS VALUE transactions total time E Number of bytes sent INC packet Total Times sleeps to wait acf task ready Number of bytes received INC packet req service time SQL processing time csr fetch select time csr fetch insert time block disk read time sort group by - build intermediate run stage time csr fetch delete time Total Times sleeps to wait ctx sync optimizer time hash join time hash join 1p hash table probing time sort group by - build key stage time table full scan time fscan fetch time fscan fetch - multiblk read time multi block disk read time Total Round Trip Times to grant lock redo log size dbwr writing list scans quicksort time 이하생략... 각항목에대한은다음과같다. 항목 INST_ID STAT# NAME VALUE Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) Stat 번호이다. Stat 이름이다. Stat 값이다. V$sysstat와 v$sesstat에나타나는값들은인스턴스기동후또는세션수립후현재까지누적된값이므로그값의크고작음으로의미있는정보를얻기어렵다. 이를제대로활용하는방법은두구간사이의변화량을구해 SQL 수행도중에내부적으로어떤일들이발생했는지판명하는것이다. 24 Tibero 모니터링가이드

33 3.3. Spin Lock Contention TSM(Tibero Shared Memory) 의모든 Spin Lock 에대한적중률을확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT name "Name", SUM(fast_cnt + try_get_cnt) as "Gets", SUM(spin_cnt + sleep_cnt + try_fail_cnt) as "Misses", SUM(sleep_cnt) as "Sleeps", round((1 - (SUM(spin_cnt + sleep_cnt + try_fail_cnt) / SUM(fast_cnt + spin_cnt + sleep_cnt + try_get_cnt + try_fail_cnt))) * 100, 3) as "Hit Ratio" FROM _vt_spinlock GROUP BY type, spin_level, name HAVING SUM(fast_cnt + try_get_cnt) > 0 ORDER BY 4 DESC, 2 DESC; 결과는아래와같다. Name Gets Misses Sleeps Hit Ratio SPIN_BUF_BUCKET SPIN_WLOCK SPIN_CCC_RSB SPIN_CWS_RSB SPIN_PPC_BUCKET SPIN_DD_CACHE_BUCKET SPIN_FD_BUCKET SPIN_ACF_RECL_WS SPIN_CWS_RSBTBL SPIN_REDO_ALLOC SPIN_BITQ SPIN_ACF_TIMEOUT SPIN_CWS_LCB SPIN_CF SPIN_PARAM SPIN_ALLOC_LRU SPIN_USGMT_CACHE SPIN_CCC_RSBTBL SPIN_PPSTAT 제 3 장 Tibero 대기이벤트 25

34 SPIN_REDO_WRITING SPIN_ACF_MSGQ_WS SPIN_BUF_WS_CKPT SPIN_ALLOC 이하생략... 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT INST_ID, name "Name", SUM(fast_cnt + try_get_cnt) as "Gets", SUM(spin_cnt + sleep_cnt + try_fail_cnt) as "Misses", SUM(sleep_cnt) as "Sleeps", round((1 - (SUM(spin_cnt + sleep_cnt + try_fail_cnt) / SUM(fast_cnt + spin_cnt + sleep_cnt + try_get_cnt + try_fail_cnt))) * 100, 3) as "Hit Ratio" FROM _gvt_spinlock GROUP BY INST_ID, type, spin_level, name HAVING SUM(fast_cnt + try_get_cnt) > 0 ORDER BY 2, 5 DESC, 3 DESC; 결과는아래와같다. INST_ID Name Gets Misses Sleeps Hit Ratio SPIN_ACF_COMM_GMC SPIN_ACF_COMM_GMC SPIN_ACF_COMM_IIC SPIN_ACF_CTX_LIST SPIN_ACF_CTX_LIST SPIN_ACF_LKBTBL SPIN_ACF_LKBTBL SPIN_ACF_MSGQ_WS SPIN_ACF_MSGQ_WS SPIN_ACF_MTX_RW SPIN_ACF_MTX_RW SPIN_ACF_NMGR SPIN_ACF_NMGR SPIN_ACF_RCO Tibero 모니터링가이드

35 101 SPIN_ACF_RCO SPIN_ACF_RECL_WS SPIN_ACF_RECL_WS SPIN_ACF_RMSG SPIN_ACF_RMSG SPIN_ACF_SCNR_ASTQ SPIN_ACF_SCNR_ASTQ SPIN_ACF_SCNR_SCANQ SPIN_ACF_SCNR_SCANQ SPIN_ACF_TIMEOUT SPIN_ACF_TIMEOUT SPIN_ALLOC SPIN_ALLOC SPIN_ALLOC_LRU SPIN_ALLOC_LRU SPIN_AUTO_COALESCE SPIN_AUTO_COALESCE SPIN_BITQ SPIN_BITQ SPIN_BUF_BUCKET SPIN_BUF_BUCKET SPIN_BUF_FREE_WAIT 이하생략... 각항목에대한은다음과같다. 항목 INST_ID NAME Gets Misses Sleeps Hit Ratio Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) Spin Lock의종류이다. 최초시도에서 Spin Lock 획득을요청한경우값을 Gets값을 1씩증가한다. 최초시도에서 Spin Lock 획득에실패한경우 Misses값을 1씩증가한다. 슬립상태에빠질때마다 1씩증가한다. 적중률이다. Willing-to-Wait 모드에서의 Latch 획득성공과실패에대한통계값은 v$latch View를통해조회한다. Gets(I), Misses(I) 항목은각각 Gets( 또는 Immediate gets) 와 Misses( 또는 Immediate misses) 값을의미하며 Gets 와 Immediate gets 가운데큰값을보여준다. Gets가 Willing-to-Wait 모드로요청된경우 Immediate gets는 No-Wait 모드로요청된다. Willing-to-Wait 모드는 Spin Lock 획득에실패하는경우 Spin과 Sleep을하면서획득에성공할때까지재시도를하고, No- Wait 모드는원하는 Spin Lock 획득에실패하는경우해당 Latch를위해 Wait하지않으므로기본적으로 Willing-to-Wait 모드를사용한다는것은 Spin Lock을획득할때까지대기한다는의미이다. 제 3 장 Tibero 대기이벤트 27

36 Spin Lock 획득에실패할경우적절한횟수만큼루프를돌고 Spin Lock 획득을다시시도하게되며루프를도는횟수는 "SPIN_COUNT_MAX( 기본값 : 2500)" 파라미터로설정한다. 만약 SPIN_COUNT 만큼루프를돌고 Spin Lock을획득하지못하면 Sleep(Tibero 기본값 : 10초 ) 한다. [ 그림 3.5] Spin Lock 획득과정 No-Wait 모드는 Redo Copy Spin Lock처럼동일한기능의 Spin Lock이여러개인경우또는 Deadlock을피하기위해사용되고 Redo Copy Spin Lock를제외하면모든 Spin Lock은대부분 Willing to Wait 모드로요청된다 No-Wait 모드에서의 Spin Lock 획득과실패에대한통계값조회항목은다음과같다. 항목 IMMEDIATE_GETS IMMEDIATE_MISSES 획득에성공하면 1 씩증가한다. 획득에실패하면 1 씩증가한다. GETS값은성공이나실패에상관없이 1씩증가하는반면 IMMEDIATE_GETS 컬럼은성공했을때만 1씩증가한다. 모든 Spin Lock의적중률이 98% 이상이되어야하지만각각의 Spin Lock 별로적중률이좋지않는경우에는각각의 Spin Lock에대한 Resource를늘려주거나해당 Spin Lock를많이사용하는애플리케이션사용을감소시켜야한다. 다음은 Spin Lock의정보를보여주는항목에대한이다. 항목 fast_cnt try_get_cnt Lock 을시도할때한번도 Waiting 없이 Lock 을잡은횟수이다. wlock 보다가볍기때문에 Deadlock 이일어나지않는순서로잡는다. Spin Lock 의적중률이 98% 이상이되지않을때 no spin 으로잡아보고어긋날때에는처음부터다시잡아 실패할경우모든 Lock 을풀고처음부터다시시도한다. 28 Tibero 모니터링가이드

37 3.4. Spin Lock 모니터링 Spin Lock 의 Contention 에대한정보를확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT name, gets, misses, spin_gets, sleeps, decode(gets,0,0,trunc((1-misses/(misses+gets))*100,2)) "Hit Ratio", wait_time, round(ratio_to_report(wait_time)over()*100,2) "WAIT_TIME(%)" FROM v$latch ORDER BY 7 desc,3 desc; 결과는아래와같다. NAME GETS MISSES SPIN_GETS SLEEPS Hit Ratio WAIT_TIME WAIT_TIME SPIN_BUF_BUCKET SPIN_ROOT_ALLOC SPIN_SHP_ALLOC SPIN_WLOCK SPIN_PPC_BUCKET SPIN_BUF_WS SPIN_PPSTAT SPIN_REDO_ALLOC SPIN_USGMT_HASH SPIN_BUF_WS _CKPT SPIN_CF SPIN_MEM_AUTO _TUNER SPIN_USGMT_CACHE SPIN_DD_CACHE _BUCKET SPIN_TX SPIN_BITQ SPIN_PARAM SPIN_MEM_AUTO _TUNER_DRIFT SPIN_RCMAP_BUCKET SPIN_SLAB_ALLOC SPIN_RECR_UNPIN SPIN_SESS_CSR_POOL SPIN_QUERY_REWRITE 각항목에대한은다음의 "TAC DB" 내용을참고한다. 제 3 장 Tibero 대기이벤트 29

38 TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT inst_id, name, gets, misses, spin_gets, sleeps, decode(gets,0,0,trunc((1-misses/(misses+gets))*100,2)) "Hit Ratio", wait_time, round(k(wait_time)over()*100,2) "WT(%)" FROM gv$latch ORDER BY 8 desc, 4 desc; 결과는아래와같다. INST_ID NAME GETS MISSES SPIN_GETS SLEEPS Hit Ratio WAIT_TIME WT(%) SPIN_BUF_BUCKET E SPIN_BUF_BUCKET E SPIN_ACF_RECL_WS E SPIN_CCC_RSB SPIN_BUF_WS_CKPT SPIN_ACF_RECL_WS SPIN_WLOCK SPIN_BUF_WS SPIN_FD_BUCKET SPIN_WLOCK SPIN_FD_BUCKET SPIN_DD_CACHE_BUCKET SPIN_CWS_RSB SPIN_CWS_RSB SPIN_DD_CACHE_BUCKET SPIN_REDO_ALLOC SPIN_TEMP_UNIT_POOL_TF SPIN_CCC_RSB SPIN_PPC_BUCKET SPIN_PPC_BUCKET SPIN_ACF_TIMEOUT SPIN_REDO_ALLOC SPIN_BITQ 각항목에대한은다음과같다. 항목 INST_ID NAME GETS MISSES SPIN_GETS Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) Spin Lock의종류이다. 최초시도에서 Spin Lock 획득을요청한경우값을 Gets값을 1씩증가한다. 최초시도에서 Spin Lock 획득에실패한경우 Misses값을 1씩증가한다. Spin Lock을잡을때까지실패한횟수를의미하며실패할경우 1씩증가한다. 30 Tibero 모니터링가이드

39 항목 SLEEPS Hit Ratio WAIT_TIME WT 슬립상태에빠질때마다 1씩증가한다. 적중률이다. Spin Lock을기다린총슬립시간이다. WAIT_TIME을백분율로표현한항목이다. 다음은 Spin Lock Contention 을발생할수있는파라미터에대한이다. Shared Pool SPIN_SHP_ALLOC 관련파라미터 _SHARED_POOL_ALLOC_CNT( 기본값 : 1) Shared Pool를여러개로쪼갤수있고몇개의 allocator로나누어관리할지를결정한다. 최고 15까지설정가능하며설정값이클수록 Contention은줄지만메모리사용효율은떨어진다. SPIN_ALLOC_LRU 관련파라미터 _SHARED_POOL_LRU_PER_ALLOC( 기본값 : 4) 하나의 Shared Pool Allocator에서사용할 LRU list 개수를결정한다. 2^n 값으로설정해야성능저하가줄어들며설정값이클수록 Contention은줄지만메모리사용효율은떨어진다. SHARED_POOL_LRU_PER_ALLOC 설정값을늘려 LRU가늘었음에도 Contention 이높다면 SHARED_POOL_ALLOC_CNT 늘려준다. 하지만 SHARED_POOL_AL LOC_CNT의경우여유있는 Allocator가있더라도 Full인 Allocator를사용할수있다는문제점이있다. 두개의파라미터를곱했을때 LRU 개수가총 16개까지가능하며최신버전에서는 15개까지설정가능하다. SPIN_RECR_HANDLE_POOL 관련파라미터 _RECR_HANDLE_FREELIST_CNT( 기본값 : 8) DD Cache와라이브러리 Cache에서사용하는구조체의 Free List 개수를설정한다. DD와라이브러리가메모리에서사라질수있기때문에 Handle이라는구조체를사용하며매번생성은오버헤드가크기때문에 Pool로관리한다. WLOCK SPIN_WLOCK 관련파라미터 _WLOCK_BUCKETSET_CNT( 기본값 : 32) 제 3 장 Tibero 대기이벤트 31

40 _WLOCK_BUCKET_PER_SET( 기본값 : 1) wlock 을찾을때사용할 Hash Bucket 의 Bucketset 개수를결정한다. Bucketset 별 로 Spin Lock 이있으므로개수가늘어날수록 Contention 이줄어들지만 Hash Table 이커지므로메모리사용량이늘어난다. Spin Lock 만늘려준다면해당파라미터만늘려주고전체적으로분산을원한다면 _WLIST_FREELIST_CNT 파라미터값도같이늘려준다. wlock을찾을때사용할 Hash Bucket의 Bucketset 개수를결정한다. Bucketset별로 Spin Lock이있으므로개수가늘어날수록 Contention이줄어들지만 Hash Table이커지므로메모리사용량이늘어난다. Spin Lock만늘려준다면해당파라미터만늘려주고전체적으로분산을원한다면 _WLIST_FREELIST_CNT 파라미터값도같이늘려준다. SPIN_WLIST_FREELIST 관련파라미터 _WLIST_FREELIST_CNT( 기본값 : 8) wlock 또는 Buffer Cache 에서사용하는구조체 Pool 에서 Freelist 의개수를결정한 다. 설정값은 2 ^ n 값을권장한다. Buffer Cache SPIN_BUF_WS / SPIN_BUF_WS_CKPT 관련파라미터 _DB_BLOCK_LRU_LATCHES( 기본값 : cpu 개수 * 3) Buffer Cache 에서사용할 LRU 의개수로 Workingset 값을결정한다. SPIN_BUF_BUCKET 관련파라미터 _DB_BLOCK_HASH_BUCKETS( 기본값 : 87379) _DB_BLOCK_HASH_LATCHES( 기본값 : 0) _DB_BLOCK_HASH_BUCKETS 파라미터설정으로 Buffer Cache 에서특정 Buffer 를찾을때사용할 Hash Bucket 개수를결정한다. _DB_BLOCK_HASH_LATCHES 파라미터설정으로 Buffer Cache 에서사용하는 Hash 에서사용할 Latch 개수를결정한다. Contention 이심하면 BUCKETS 와 LATCHES 값을같게할수있으나 Bucket 마다 Latch 가있게되어메모리효율은떨어지게된다. 32 Tibero 모니터링가이드

41 Redo SPIN_REDO_COPY 관련파라미터 _LOG_SIMULTANEOUS_COPIES( 기본값 : cpu 개수 * 2) Redo 버퍼에서동시에 Copy 하는트랜잭션의개수를설정한다. Undo SPIN_USGMT_HASH 관련파라미터 _USGMT_HASH_CNT( 기본값 : 8) Undo 세그먼트의 Spin Lock Hash Bucket 수를설정한다. Spin Lock Hash Bucket 수를늘려도메모리를많이사용하지않으므로최소 64 로설정하는것을권장한다. DD Cache SPIN_DD_CACHE_BUCKET 관련파라미터 _DD_BUCKETSET_CNT( 기본값 : 16) _DD_BUCKET_PER_SET( 기본값 : 64) wlock 과거의동일하게 Hash Bucket 과 Bucketset 의개수를결정한다. PP Cache SPIN_PPC_BUCKET 관련파라미터 _PPC_BUCKET_CNT( 기본값 : 1024) _PPC_BUCKET_PER_SET( 기본값 : 64) wlock 과거의동일하게 Hash Bucket 과 Bucketset 의개수를결정한다. Temp SPIN_TEMP_UNIT_POOL_TF Temp 파일당하나의 Spin Lock이할당되며여러세션에서 Temp Tablespace를사용할경우발생한다. 해당 Spin Lock Contention이비정상적으로높을경우 Temp Tablespace에 Temp 파일을여러개추가하여해결한다. 제 3 장 Tibero 대기이벤트 33

42

43 제 4 장 Lock & Current 트랜잭션 본장에서는 Lock 과트랜잭션에대해서한다 Current 세션의수 대상데이터베이스에접속되어있는모든세션과현재작업중인세션의수, Total WPM(Working Process Memory) 을확인한다. 세션의상태는 ACTIVE, RUNNING, RECOVERING 이있다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time", "Working Process Memory", (a.acs + b.run) "Total Sess", b.run "Running Sess", c.recover "Recover Sess" FROM (SELECT SUM(pga_used_mem) "Working Process Memory", COUNT(*) acs FROM v$session WHERE status='active') a, (SELECT COUNT(*) run FROM v$session WHERE status='running') b, (SELECT COUNT(*) recover FROM v$session WHERE status='sess_recovering') c; 결과는아래와같다. Time Working Process Memory Total Sess Running Sess Recover Sess /06/05 20:39: row selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. 제 4 장 Lock & Current 트랜잭션 35

44 TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT a.inst_id, to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Time", "Work Proc Memory", (a.acs + b.run) "Tot Sess", b.run "Run Sess", c.recover "Recover Sess" FROM (SELECT INST_ID, SUM(pga_used_mem) "Work Proc Memory", COUNT(*) acs FROM gv$session WHERE status='active' group by inst_id) a, (SELECT INST_ID, COUNT(*) run FROM gv$session WHERE status='running' group by inst_id) b, (SELECT INST_ID, COUNT(*) recover FROM gv$session WHERE status='sess_recovering' group by inst_id) c WHERE a.inst_id = b.inst_id(+) and a.inst_id = c.inst_id(+); 결과는아래와같다. INST_ID Time Work Proc Memory Tot Sess Run Sess Recover Sess /06/05 20:33: /06/05 20:33: rows selected. 각항목에대한은다음과같다. 항목 INST_ID Time Work Proc Memory Active RUNNING Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 쿼리를날린현재시간이다. 현재동작 (Active state) 중인세션이사용하고있는 PGA의전체크기이다. 일반적인상태로사용자가접속만하고있는상태이다. 실제작업을수행하고있는상태이다. 36 Tibero 모니터링가이드

45 항목 SESS_RECOVERING Booting 또는세션을강제종료할때트랜잭션이복구중인상태이다. 총세션의상당수가 Running 세션이라면 Lock 과 Current 트랜잭션정보를확인하여적절한조치를한다 Current 트랜잭션 현재진행중인트랜잭션들의목록과관련정보를확인한다. Single DB 다음은 Single DB 쿼리작성예이다. 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; 결과는아래와같다. SID SERIAL# USERNAME Object STATUS USED_BLK USN START_TIME DBTECH DBTECH.JOBHIST ACTIVE /06/ DBTECH DBTECH.JOBHIST ACTIVE /06/10 Transactio SQL_TEXT :09:24 update jobhist set t3='duduk' 제 4 장 Lock & Current 트랜잭션 37

46 where t1=1 0:10:07 update jobhist set t3='duduk1' where t1=2 2 rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. select distinct vs.inst_id, 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 gv$session vs, _gvt_transaction vt, _GVT_SQLTEXT vst, _GVT_ACCESS va WHERE vt.sess_id = vs.sid and vt.sess_id = va.sess_id and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id ORDER BY 1 결과는아래와같다. INST_ID SID SERIAL# USERNAME Object STATUS USED_BLK USN START_TIME DBTECH DBTECH.204 ACTIVE /06/ DBTECH DBTECH.204 ACTIVE /06/10 2 Transactio SQL_TEXT :08:44 update jobhist set t3='duduk' 38 Tibero 모니터링가이드

47 where t1=1 0:08:59 update jobhist set t3='duduk1' where t1=2 2 rows selected. 각항목에대한은다음과같다. 항목 INST_ID SID USERNAME Object STATUS USED_BLK START_TIME Transaction Time SQL_TEXT 일 Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 트랜잭션을수행중인세션의 ID이다. 트랜잭션을수행중인세션의사용자이다. 현재동작중인트랜잭션에서사용중인 Table들의목록이다. ( 하나의트랜잭션에서여러 Table을 Access할경우 Table마다하나의 row로나온다 ) 트랜잭션을수행하고있는세션의상태정보이다. 현재트랜잭션이사용중인 Block의개수이다. 트랜잭션이시작된시간이다. 트랜잭션이진행된시간이다. 현재수행중인 SQL 내용이다. 현재트랜잭션이사용중인지확인하고장시간 Commit 이나 Rollback 없이진행되는트랜잭션의경우애 플리케이션의잘못된코딩인지아니면관리자나개발자의실수인지를판단하여목적없는트랜잭션의 Commit 또는 Rollback 을권고한다 Current SQL 정보 (for 세션 ) 현재세션별수행되고있는 SELECT 및 DML 구문을확인한다. Single DB 다음은 Single DB 쿼리작성예이다. select sid, serial#, '{' aggr_concat(sql_text, '' ORDER BY PIECE) '}' SQL FROM v$session vs, v$sqltext vst WHERE vs.sql_id=vst.sql_id GROUP BY SID, SERIAL#; 결과는아래와같다. SID SERIAL# SQL 제 4 장 Lock & Current 트랜잭션 39

48 {select sid, serial#, '{' aggr_concat(sql_text, '' ORDER BY PIECE) '}' SQL FROM v$session vs, v$sqltext vst WHE RE vs.sql_id=vst.sql_id GROUP BY SID, SERIAL# } {select 1 from dual connect by level < } 2 rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. select max(vs.inst_id) inst_id,sid, serial#, '{' aggr_concat(sql_text, '' ORDER BY PIECE) '}' SQL FROM gv$session vs, _gvt_sqltext vst WHERE vs.sql_id=vst.sql_id GROUP BY SID, SERIAL#; 결과는아래와같다. INST_ID SID SERIAL# SQL {select max(vs.inst_id) inst_id,sid, serial#, '{' aggr_concat(sql_text, '' ORDER BY PIECE) '}' SQL FROM gv$session vs, _gvt_sqltext vs t WHERE vs.sql_id=vst.sql_id GROUP BY SID, SERIAL# } {select 1 from dual connect by level < } 2 rows selected. 각항목에대한은다음과같다. 항목 INST_ID SID SERIAL# SQL Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 세션 ID이다. 세션의 Serial이다. ( 같은세션 ID라도사용자가바뀌면 Serial이바뀐다 ) 현재수행중인 SQL 내용이다. 현재활성화된세션의 SQL 구문을확인하여불량 SQL 문이나루프문으로 DB 에부하를주고있는세션 이있다면그쿼리를찾아내고해당세션에대한적절한조치를한다. 40 Tibero 모니터링가이드

49 4.4. WLOCK 정보 (for 세션 ) 현재실행중인세션의트랜잭션시간및 Lock 모드를확인한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT S.USER_NAME "USERNAME", S.SESS_ID ',' S.SERIAL_NO "SESSION", O.OWNER '.' O.OBJECT_NAME "OBJECT", S.STATUS "STATUS", 션 --S.TYPE, S.LOGON_TIME, S.VTR_TID, L.TYPE, 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", DECODE(L.LMODE, 0,'[0]NONE ', 1,'[1]NULL', 2, '[2]ROW-S (SS)',3,'[3]ROW-X (SX)', 4, '[4]SHARE', 5, '[5]S/ROW-X (SSX)', 6, '[6]EXCLUSIVE', TO_CHAR(L.LMODE) ) "LOCK MODE", L.STATUS "STATE", --L.ID1, L.ID2, NVL(S.SQL_ID, S.PREV_SQL_ID) SQL_ID FROM VT_WLOCK L, VT_SESSION S, DBA_OBJECTS O, VT_TRANSACTION VT WHERE L.LMODE =2 -- Grater than 1 AND L.THR_ID = S.VTR_TID AND L.ID1 = O.OBJECT_ID (+) AND L.THR_ID = VT.SESS_ID ; 결과는아래와같다. USERNAME SESSION OBJECT STATUS TRANSACTION TIME DBTECH 19,162 DBTECH.JOBHIST ACTIVE 0:02:21 DBTECH 20,153 DBTECH.JOBHIST ACTIVE 0:00:37 DBTECH 21,176 DBTECH.JOBHIST RUNNING 0:03:04 LOCK MODE STATE SQL_ID [2]ROW-S (SS) OWNER 29 [2]ROW-S (SS) OWNER 28 [2]ROW-S (SS) OWNER 33 제 4 장 Lock & Current 트랜잭션 41

50 3 rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. SELECT S.INST_ID, S.USER_NAME "USERNAME", S.SESS_ID ',' S.SERIAL_NO "SESSION", O.OWNER '.' O.OBJECT_NAME "OBJECT", S.STATUS "STATUS", 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", DECODE(L.LMODE, 0,'[0]NONE ',1,'[1]NULL',2,'[2]ROW-S (SS)',3,'[3]ROW-X (SX)', 4, '[4]SHARE', 5, '[5]S/ROW-X (SSX)', 6, '[6]EXCLUSIVE', TO_CHAR(L.LMODE) ) "LOCK MODE", L.STATUS "STATE", NVL(S.SQL_ID, S.PREV_SQL_ID) SQL_ID FROM _GVT_WLOCK L, _GVT_SESSION S, DBA_OBJECTS O, _GVT_TRANSACTION VT WHERE L.LMODE = 2 -- Grater than 1 AND L.THR_ID = S.VTR_TID AND L.ID1 = O.OBJECT_ID (+) AND L.THR_ID = VT.SESS_ID; 결과는아래와같다. INST_ID USERNAME SESSION OBJECT STATUS TRANSACTION TIME DBTECH 171,1340 DBTECH.JOBHIST ACTIVE 0:12: SYS 172,1545 DBTECH.JOBHIST RUNNING 0:30: DBTECH 174,1335 DBTECH.JOBHIST ACTIVE 0:31: DBTECH 171,1359 DBTECH.JOBHIST ACTIVE 0:31: DBTECH 172,963 DBTECH.JOBHIST ACTIVE 0:30:14 LOCK MODE STATE STATE SQL_ID [2]ROW-S (SS) OWNER Tibero 모니터링가이드

51 [2]ROW-S (SS) OWNER 126 [2]ROW-S (SS) OWNER 66 [2]ROW-S (SS) OWNER [2]ROW-S (SS) OWNER rows selected. DML Lock 관련정보에대한각항목의은다음과같다. 항목 INST_ID USERNAME SESSION OBJECT LMODE Tibero 인스턴스정보이다. (TAC DB 질의쿼리 ) 트랜잭션을수행중인세션의사용자이다. 트랜잭션을수행중인세션의 ID 와 Serial# 이다. DML Lock이걸려있는 ( 또는요청하고있는 ) Schema 오브젝트이다. wlock owner의모드이다. (RS=1, RX=2, S=3, SRX=4, X=5) DML Lock 을발생시키거나걸려있는트랜잭션과오브젝트를확인하고해당세션을끊거나어플리케이션 상잘못된구현또는사용자의잘못된쿼리로장시간 Lock 걸려있는부분을확인하고 Commit 또는 Rollback 을권고한다 WLOCK Holder & Waiter 정보 Blocking 하고있는 Lock 정보와 Waiting 하고있는 Lock 정보를모니터링한다. Single DB 다음은 Single DB 쿼리작성예이다. SELECT bs.user_name "Blocking User", ws.user_name "Waiting User", bs.sess_id "Blocking Sid", ws.sess_id "Waiting Sid", wk.type "Lock Type", hk.lmode "Holding mode", wk.lmode "Request mode", nvl(bs.sql_id, bs.prev_sql_id) "Sql_id" FROM vt_wlock hk, vt_session bs, vt_wlock wk, vt_session ws WHERE wk.status = 'WAITER' and hk.status = 'OWNER' 제 4 장 Lock & Current 트랜잭션 43

52 and hk.lmode > 1 and wk.type = hk.type and wk.id1 = hk.id1 and wk.id2 = hk.id2 and wk.thr_id = ws.vtr_tid and hk.thr_id = bs.vtr_tid ORDER BY 1; 결과는아래와같다. Blocking User Waiting User Blocking Sid Waiting Sid Lock Type DBTECH DBTECH WLOCK_TX DBTECH DBTECH WLOCK_TX DBTECH DBTECH WLOCK_TX Holding mode Request mode Sql_id rows selected. 각항목에대한은다음의 "TAC DB" 내용을참고한다. TAC DB 다음은 TAC DB 쿼리작성예이다. Select bs.inst_id, bs.user_name "Blocking User", ws.user_name "Waiting User", bs.sess_id "Blocking Sid", ws.sess_id "Waiting Sid", wk.type "Lock Type", hk.lmode "Holding mode", wk.lmode "Request mode", nvl(bs.sql_id, bs.prev_sql_id) "Sql_id" from _GVT_WLOCK hk, _GVT_SESSION bs, _GVT_WLOCK wk, _GVT_SESSION ws where hk.lmode > 1 and wk.status = 'WAITER' and hk.status = 'OWNER' 44 Tibero 모니터링가이드