Real Alternative DBMS ALTIBASE, Since 1999 ALTIBASE 모니터링쿼리가이드 ALTIBASE 5.3.3 (5.1.5) 2010. 03.31 Copyright c 2000~2010 ALTIBASE Corporation. All Rights Reserved.
Document Control Change Record Date Author Change Reference 2010-03-09 durusari Created 2010-03-29 khhan Template Modified 2010-10-20 durusari Added, Modified Reviews Date Name (Position) 2010-11-09 lim272(sp), dplee(ts), son2865(sc), fhan(ts), khhan(sc) Distribution Name Location ALTIBASE 모니터링쿼리가이드 2 page of 43
목차 개요... 4 ALTIBASE 모니터링개요... 5 모니터링분류... 5 데이터딕셔너리... 5 모니터링방법... 5 ALTIBASE 메타테이블및성능뷰개요... 6 ERD 표기에관하여... 6 유의사항... 6 용어... 6 세션, 쿼리, 트랜잭션, LOCK, 서비스쓰레드, 메모리 DB GC 관련주요메타테이블및성능뷰... 8 주요메타테이블... 8 주요성능뷰... 9 테이블스페이스, 테이블, 컬럼, 인덱스, 제약조건관련주요메타테이블및성능뷰... 10 주요메타테이블... 11 주요성능뷰... 11 통계정보관련주요성능뷰... 13 대기이벤트관련성능뷰... 13 연산관련성능뷰... 14 데이터파일 I/O... 14 기타주요성능뷰... 14 이중화관련주요메타테이블및성능뷰... 16 주요메타테이블... 16 주요성능뷰... 17 사용자의테이블스페이스접근가능여부, 시스템 / 객체권한, PSM, 뷰관련주요메타테이블및성능뷰... 18 주요메타테이블... 18 모니터링요소... 20 모니터링쿼리... 22 Session... 22 Statement... 23 Service Thread... 25 Transaction & Lock... 26 redo logfile... 27 GC... 27 Memory... 28 TBS(tablespace)... 29 Disk Buffer... 34 Object... 34 Privileges... 38 Constraints... 39 Replication... 41 ALTIBASE 모니터링쿼리가이드 3 page of 43
개요 본문서는 ALTIBASE 모니터링을위해기본적으로습득해야할사항과그에따른일반적인모니터링쿼리예시를제시하는문서로아래와같이크게 3 개의섹션으로구성되어있습니다. ALTIBASE 모니터링개요 ALTIBASE 메타테이블 (meta table) 및성능뷰 (performance view) 개요 모니터링요소와그에대응하는모니터링쿼리 모든섹션은 ALTIBASE 5.3.3 을기준으로작성되었습니다. 다만, 모니터링쿼리는 5.1.5 에서도사용할수있도록변경하여야할부분에대하여별도로명시를하였습니다. 본문서는예시로제시되는모니터링쿼리와관련된메타테이블및성능뷰일부에대하여서만간략히설명하고있으며컬럼설명또한컬럼이름이직관적이어서별도의설명이필요없다고판단되는경우, 이전에이미설명한컬럼이중복되어나오는경우는생략하였습니다. 따라서, 보다상세한설명과이해를위해서기본적으로 ATC (http://atc.altibase.com) 에게시된운영자매뉴얼을함께참조하시기바랍니다. 모니터링쿼리예시만참조하려면 모니터링요소 섹션으로바로이동하시기바랍니다. 본문서에포함된 ERD 는 CA ERwin Data Modeler 을사용하여 IDEF1X 표기법으로작성되었습니다. 본문서와관련된오류사항은 ALTIBASE 기술본부대표메일인 support@altibase.com 으로보내주시기바랍니다. ALTIBASE 모니터링쿼리가이드 4 page of 43
ALTIBASE 모니터링개요 ALTIBASE 모니터링을위한기본적인사항에대해설명한다. 모니터링분류 ALTIBASE 는 DBMS 이다. DBMS 모니터링은아래와같이크게 3 가지로분류할수있다. 내부모니터링 데이터딕셔너리에대한쿼리를통한 DBMS 내부에서의모니터링을의미한다. 외부모니터링 OS 명령어를통한 DBMS 외부에서의모니터링을의미한다. trace 로그모니터링 DBMS 에의해기록되는각종 trace 로그에대한모니터링을의미한다. 데이터딕셔너리 데이터베이스에대한모든정보를요약및저장하여 DBMS 를효율적으로사용할수있도록하는것으로 ALTIBASE 데이터딕셔너리는두가지로구성되어있다. 메타테이블 (meta table) 데이터베이스객체를관리하기위해데이터베이스생성시점에자동으로생성되는테이블이다. 해당테이블은사용자 SYSTEM_ 의소유로일반사용자는 SELECT 만가능하다. 성능뷰 (performance view) SELECT 시점에 ALTIBASE 내부의상태정보를뷰형태로제공되는것으로 ALTIBASE 내부의최신정보를얻을수있다. 물리적으로저장되지않으며이역시 SELECT 만가능하며성능뷰의접두어는 v$ 이다. 모니터링방법 현업에서일반적으로사용하는모니터링방법은아래와같다. 쉘스크립트작성 응용프로그램작성 유틸리티활용 유틸리티의경우, 비공식유틸리티이나 ALTIBASE 기술본부에의해활용되는 ALTIMON 과 ALTIBASE 5.1.5 부터공식적으로제공되는 AdminCenter2 for DBA 와 3 rd party 제품인 Orange for ALTIBASE DBA edition 등이있다. ALTIBASE 모니터링쿼리가이드 5 page of 43
ALTIBASE 메타테이블및성능뷰개요 ALTIBASE 내부모니터링을위해서는 ALTIBASE 데이터딕셔너리인메타테이블과성능뷰에대한이해가우선적으로필요하다. 본문서에서는사용자의이해와쿼리편의를고려하여메타테이블과성능뷰의관계를 ERD(Entity- Relationship Diagram) 를사용하여표현하였다. 이에앞서숙지해야할사항에대해설명한다. ERD 표기에관하여 사용자의이해와쿼리편의를고려하여기반테이블이존재하지않는성능뷰특성을무시, 아래와같이성능뷰를테이블로표현하여조인시참고할주요키를 FK 형태로표현하였다. 이러한표기로인한혼동을피하기위해메타테이블은배경을노란색으로성능뷰는흰색으로표시하였다. META_TABLE ID v$performance_view ID MET A_ID (FK) 또한, 위 ERD 에서메타테이블의 ID 컬럼과성능뷰의 META_ID 컬럼처럼동일한속성의컬럼이메타테이블및성능뷰에따라서로다른이름일수있는데, 이런경우는컬럼에동일한색상을주어같은속성임을나타냈다. 유의사항 ALTIBASE 메타테이블과성능뷰는아래와같은특성이있음을유의해야한다. 1. 동일한속성의컬럼이메타테이블및성능뷰에따라서로다른이름일수있다. 2. ALTIBASE 버전에따라메타테이블및성능뷰의컬럼이름이변경되거나삭제될수있다. 3. ALTIBASE 버전에따라메타테이블및성능뷰가추가되거나삭제될수있다. 용어 설명에앞서혼동이발생할수있는일부용어에대한정의이다. 세션 (session) ALTIBASE 에접속한사용자의접속단위를의미한다. 하나의사용자는동시에여러개의세션을가질수있다. 구문 (statement) 트랜잭션에서수행되는 SQL 하나하나를의미하는용어로문맥에따라 SQL ( 구 ) 문, Query, 질의 ( 문 ), 쿼리 ( 문 ) 는모두동일한의미를가지는경우가많다. 본문서에서는 쿼리 로통일한다. 메모리 DB GC (garbage collector) or GC 사용자가 commit 을수행하면 MVCC(Multi Versioning Concurrency Control / 다중레코드동시제어 ) 기법에의해유지되던변경전의레코드는삭제가되어야한다. ALTIBASE 모니터링쿼리가이드 6 page of 43
ALTIBASE 는변경전의레코드삭제를위해별도쓰레드를운영하는데, 이를각각메모리 DB GC, 디스크 DB GC 라칭한다. 하지만, ALTIBASE 5.3.3 부터디스크 DB 의 MVCC 방식이변경되어디스크 DB GC 가없어지면서메모리 DB GC 만존재하기에줄여서 GC 또는 Ager 라한다. ALTIBASE 모니터링쿼리가이드 7 page of 43
세션, 쿼리, 트랜잭션, lock, 서비스쓰레드, 메모리 DB GC 관련주요메타테이블및성능뷰 메타테이블및성능뷰를통하여실시간으로세션, 쿼리, 트랜잭션, lock, 서비스쓰레드, 메모리 DB GC 상태를확인가능하다. 관련조인쿼리시참고할주요키컬럼만표기한메타테이블및성능뷰의관계는아래와같다. 별개로표시되어있는 v$memgc 는메모리 DB GC 에대한성능뷰로다른메타테이블및성능뷰와직접적인관계는없으나 v$transaction 의특정컬럼과함께활용된다. 유의사항으로는 v$statement 의컬럼중쿼리수행과관련된통계정보는 TIMED_STATISTICS 프로퍼티가활성화 (1) 되어야만갱신되므로해당프로퍼티의활성화여부를반드시확인하여야한다는것이다. TIMED_STATISTICS 기본값은비활성화 (0) 이다. 참고로이중화를수행으로인한트랜잭션도 v$transaction, v$lock, v$lock_wait 를활용하나세션번호, 쿼리번호가없는이중화트랜잭션특성상이중화섹션에서별도로언급하는것으로한다. v$service_thread ID SESSION_ID (FK) ST AT EMENT _ID (FK) Z SYS_USERS_ USER_ID v$session ID DB_USERID (FK) v$statement SESSION_ID (FK) ID T X_ID (FK) 1 v$sqltext SID (FK) ST MT _ID (FK) v$memgc v$transaction ID v$plantext SID (FK) ST MT _ID (FK) MINMEMSCNINT XS SESSION_ID (FK) MEMORY_VIEW _SCN 1 v$lock_statement SYS_TABLES_ TABLE_ID T ABLE_OID USER_ID (FK) v$lock_wait T RANS_ID (FK) WAIT _FOR_T RANS_ID (FK) v$lock T RANS_ID (FK) T ABLE_OID (FK) LOCK_DESC Z Z SESSION_ID (FK) ID (FK) T X_ID (FK) T ABLE_OID (FK) LOCK_DESC (FK) 주요메타테이블 SYS_USERS_ 모든사용자의정보가저장된메타테이블로사용자이름 (USER_NAME) 과같은정보를확인할수있어가장기본적으로활용된다. ALTIBASE 모니터링쿼리가이드 8 page of 43
SYS_TABLES_ 모든테이블 ( 큐테이블포함 ) 뿐만아니라시퀀스, 뷰의정보도함께저장한메타테이블로해당객체이름 (TABLE_NAME) 과같은정보를확인할수있어 SYS_USERS_ 와함께기본적으로활용된다. 주요성능뷰 v$session 현재접속되어있는사용자의세션정보를나타내는성능뷰이다. v$statement, v$sqltext, v$plantext v$statement 는세션과관련된쿼리의정보및쿼리레벨의통계정보를나타내는성능뷰로쿼리의수행시간및수행빈도측정이가능하다. 세션별로가장마지막에 direct 수행 (execution) 한하나의쿼리와 prepare 된다수의쿼리에한하여서만유지되는뷰로관련세션을종료되면사라지게된다. 이뷰를통하여쿼리의텍스트도최대 16K 까지확인이가능하다. 쿼리의텍스트가 16K 를초과하는경우는쿼리텍스트전체를나타내는성능뷰인 v$sqltext 를활용하며쿼리에대응하는실행계획을확인하려할때는 v$plantext 를활용한다. 앞서언급했듯 v$statement 의컬럼중쿼리수행과관련된통계정보는 TIMED_STATISTICS 프로퍼티가활성화 (1) 되어야만제공되므로해당프로퍼티의활성화여부를반드시확인하여야한다. TIMED_STATISTICS 기본값은비활성화 (0) 이다. v$service_thread 서비스쓰레드의상태를나타내는성능뷰로 v$session, v$statement 와조인하여관련세션과쿼리를확인할수있다. 뷰자체만으로도의미가있어단독으로모니터링하기도한다. v$transaction, v$memgc v$transaction 은현재수행되는모든트랜잭션의정보를나타내는성능뷰로 lock 관련모니터링시기본적으로사용된다. 또한, 메모리 DB GC 의정보를나타내는성능뷰인 v$memgc 를통하여세션, 구문과연계된 MVCC 상태를확인할수도있다. v$lock, v$lock_wait v$lock 은트랜잭션수행중발생한 lock 에대한모든정보를나타내는성능뷰로 lock 의선후관계를나타내는 v$lock_wait 과함께활용한다. v$lock_statement 현재 lock 을획득한트랜잭션에서가장마지막에수행된구문을나타내는성능뷰로 lock 발생시관련세션및구문에대한신속한확인을위해주로활용된다. ALTIBASE 모니터링쿼리가이드 9 page of 43
테이블스페이스, 테이블, 컬럼, 인덱스, 제약조건관련주요메타테이블및성능뷰 ALTIBASE 는 hybrid DBMS 특성상테이블스페이스, 테이블에대한정보를메모리 DB, 디스크 DB 에따라별도 (TBS, TABLE 박스참조 ) 로확인이가능하게끔되어있으며세그먼트의경우는 v$segment 와관계 ( 보라색 ) 된각각의성능뷰를통해상세한자원사용현황을확인할수있다. 하지만, 인덱스의경우현재로서메모리인덱스크기측정을위한메타테이블이나성능뷰가제공되지않는관계로테이블전체레코드수에하나의레코드에대한메모리인덱스크기인 16byte 를곱하는것으로대체하여야한다. 디스크인덱스는 v$index 를참조, 해당세그먼트를통하여실시간크기측정이가능하다. 테이블스페이스, 테이블, 컬럼, 인덱스, 제약조건관련조인쿼리시참고할주요키컬럼만표기한메타테이블및성능뷰의관계는아래와같다. SYS_CONSTRAINTS_ SYS_CONSTRAINT_COLUMNS_ SYS_COLUMNS_ CONSTRAINT_ID USER_ID (FK) INDEX_ID (FK) 1 P COLUMN_ID (FK) CONSTRAINT_ID (FK) SYS_USERS_ COLUMN_ID T ABLE_ID (FK) P USER_ID SYS_INDICES_ INDEX_ID USER_ID (FK) T ABLE_ID (FK) T BS_ID (FK) TBS DEFAULT _T BS_ID (FK) T EMP_T BS_ID (FK) v$tablespaces ID TABLE SYS_TABLES_ T ABLE_ID T ABLE_OID (AK1.1) T BS_ID (FK) USER_ID (FK) Z Z v$mem_tablespaces SPACE_ID (FK) Z v$datafiles SPACEID (FK) ID v$memtbl_info T ABLE_OID (FK) v$disktbl_info T ABLE_OID (FK) SEG_PID (FK) Z v$index 1 INDEX_ID (FK) INDEX_SEG_PID (FK) T ABLE_OID Z v$udsegs Z P v$segment SEGMENT _PID T ABLE_OID SPACE_ID (FK) SEG_PID (FK) Z v$tssegs SEG_PID (FK) 특이사항으로는주요메타테이블인 SYS_TABLE_ 과다른메타테이블간의조인시는조인컬럼으로 TABLE_ID 를사용하고성능뷰와의조인시는대체키인 TABLE_OID 컬럼 ( 파란색 ) 을사용한다는것이다. 참고로표기는되지않았지만 SYS_USERS_, SYS_TABLES_ 을제외한모든메타테이블은쿼리편의를고려하여 USER_ID 와 TABLE_ID 컬럼이모두존재한다. 성능뷰 v$segment 와 v$index 역시테이블별합산을고려하여 TABLE_OID 컬럼이존재한다. ALTIBASE 모니터링쿼리가이드 10 page of 43
주요메타테이블 SYS_COLUMNS_ 모든테이블의컬럼정보가저장된메타테이블로컬럼이름 (COLUMN_NAME) 은물론데이터타입 (DATA_TYPE), 컬럼순서 (COLUMN_ORDER) 와같은상세한정보를확인할수있다. SYS_CONSTRAINTS_ 테이블의제약조건정보를저장한메타테이블로제약조건유형 (CONSTRAINT_TYPE) 을확인할수있으며 PK, FK, UK 와같은인덱스생성을필요로하는제약조건의경우관련인덱스번호 (INDEX_ID) 를확인할수있다. 또한, FK 의경우참조테이블 (REFERENCED_TABLE_ID) 도확인이가능하다. SYS_CONSTRAINT_COLUMNS 제약조건의대상이되는컬럼의컬럼번호 (COLUMN_ID) 를확인할수있는메타테이블로관련제약조건번호 (CONSTRAINT_ID) 와함께대응되어있다. SYS_INDICES_ 모든인덱스정보가저장된메타테이블로인덱스이름 (INDEX_NAME) 은물론테이블스페이스번호 (TBS_ID), 인덱스유형 (INDEX_TYPE), 구성컬럼개수 (COLUMN_CNT) 와같은상세한정보를확인가능하다. 주요성능뷰 v$tablespaces 모든테이블스페이스정보를나타내는성능뷰로테이블스페이스관련모니터링시기본적으로활용된다. v$mem_tablespaces 메모리테이블스페이스에대해서만상세한정보를나타내는성능뷰로메모리 DB 운영을위해실제로사용하는물리적메모리크기를구할수있다. v$datafiles 디스크테이블스페이스를구성하는데이터파일의상세정보를나타내는성능뷰로물리적인데이터파일의경로, 상태및크기를확인수있다. v$segment 디스크 DB 의세그먼트정보를나타내는성능뷰로디스크테이블, 디스크인덱스, 언두테이블스페이스, 임시테이블스페이스와같은디스크 DB 관련객체의정확한크기를구할수있다. v$memtbl_info 메모리테이블에대해서만상세한정보를나타내는성능뷰로메모리테이블별순수데이터의크기및합계를구할수있다. ALTIBASE 모니터링쿼리가이드 11 page of 43
v$disktbl_info 디스크테이블에대해서만상세한정보를나타내는성능뷰로해당세그먼트 (SEG_PID) 를알수있어디스크테이블별순수데이터의크기및합계를구할수있다. v$index 모든인덱스에대한간략한정보를나타내는성능뷰로디스크인덱스의경우해당세그먼트 (INDEX_SEG_PID) 를알수있어디스크인덱스의크기를구할수있다. ALTIBASE 모니터링쿼리가이드 12 page of 43
통계정보관련주요성능뷰 성능뷰를통하여 DBMS 레벨, 세션레벨, 쿼리레벨에서의대기이벤트 (wait event) 와연산 (operation) 에대한통계정보, DBMS 레벨의디스크테이블스페이스데이터파일 I/O, 메모리사용량버퍼풀통계정보와같은다양한통계정보를확인할수있다. 레벨에따라통계정보를생성하는기준은차이가있다. DBMS 레벨의통계정보는 ALTIBASE 구동시점부터정보를누적하며 ALTIBASE 가종료되면초기화된다. 따라서, 특정기간동안의값을알기위해서는 ( 현재의값 - 측정시작시점의값 ) 을모든칼럼값에대해계산해야한다. 세션레벨통계정보는관련세션이접속되어있는동안만유지된다. 쿼리레벨의통계정보는세션별로가장마지막에 direct 수행 (execution) 한하나의쿼리와 prepare 된다수의쿼리에한하여서만유지되며관련세션이종료되면이역시사라지게된다. 쿼리레벨의통계정보는쿼리섹션에서이미언급한 v$statement 을통하여제공되는것으로본섹션에서는생략한다. 유의할사항으로는통계정보는 TIMED_STATISTICS 프로퍼티가활성화 (1) 되어야만가능하므로해당프로퍼티의활성화여부를반드시확인하여야한다는것이다. TIMED_STATISTICS 기본값은비활성화 (0) 이다. 통계정보관련성능뷰는대부분뷰자체만으로도모니터링이가능하므로조인이불필요하나대기이벤트, 연산, 디스크테이블스페이스의데이터파일 I/O 에대한통계정보의경우특정세션또는특정데이터파일에연관을지어확인할필요가있다. 관련정보를얻기위해조인쿼리시참고할주요키컬럼만표기한메타테이블및성능뷰의관계는아래와같다. OPERATION v$system_wait_class WAIT _CLASS_ID WAIT EVENT datafile I/O 1 v$filestat SPACEID (FK) FILEID (FK) v$sysstat SEQNUM v$system_event EVENT _ID WAIT _CLASS_ID (FK) P P P v$datafiles SPACEID (FK) ID v$sesstat SID (FK) SEQNUM (FK) v$session_event SID (FK) EVENT _ID (FK) v$session_wait SID (FK) SEQNUM (FK) P P P v$tablespaces ID SYS_USERS_ USER_ID DEFAULT _T BS_ID (FK) T EMP_T BS_ID (FK) v$session ID DB_USERID (FK) 대기이벤트관련성능뷰 대기이벤트란 세션 또는 ALTIBASE 쓰레드 의일련의대기작업을의미한다. 예를들면, 서비스쓰레드가 disk buffer 에적재된 page 를접근하기위해 page 의 latch 획득을대기하는작업, 로그기록을위해로그버퍼의 latch 획득을대기하는작업등이있다. 참고로 ALTIBASE 는대기이벤트를그룹화하기위해상위개념인대기이벤트클래스 (wait event class) 를사용하여 8 가지로분류하고있다. ALTIBASE 모니터링쿼리가이드 13 page of 43
v$system_wait_class 세션 또는 ALTIBASE 쓰레드 의대기이벤트에대한통계정보를 대기이벤트클래스 별로나타낸다. ALTIBASE 구동이후부터누적되는통계정보로종료시초기화된다. v$system_event 세션 또는 ALTIBASE 쓰레드 의대기이벤트에대한통계정보를 대기이벤트 별로나타낸다. ALTIBASE 구동이후부터누적되는통계정보로종료시초기화된다. v$session_event 세션 의대기이벤트에대한서만통계정보를나타낸다. 세션이종료되면관련통계정보는사라진다. v$session_wait 세션 중조회시점에 활성화된세션 의대기이벤트에대한서만통계정보를나타낸다. 세션이유휴상태 (idle) 가되거나종료되면관련통계정보는사라진다. 연산관련성능뷰 연산이란 세션 또는 ALTIBASE 쓰레드 가수행하는각종연산작업을의미한다. 예를들면, 특정세션의쿼리수행, 서비스쓰레드의리두로그쓰기등이있다. v$sysstat 세션 또는 ALTIBASE 쓰레드 의연산에대한통계정보를 연산 별로나타낸다. ALTIBASE 구동이후부터누적되는통계정보로종료시초기화된다. v$sesstat 세션 의연산에대한서만통계정보를나타낸다. 세션이종료되면관련통계정보는사라진다. 데이터파일 I/O v$filestat 디스크테이블스페이스의데이터파일별 I/O 통계정보를나타낸다. 기타주요성능뷰 그외주요통계정보를제공하는성능뷰는아래와같다. 성능뷰자체가의미있는통계정보를제공하므로다른성능뷰와조인할필요가없다. v$memstat ALTIBASE 가현재사용하는메모리의사용량을모듈별로나타내는성능뷰로 ALTIBASE 메모리사용량이비정상적일때주요참고지표가된다. 모듈의최고메모리사용량을의미하는컬럼 MAX_TOTAL_SIZE 는 ALTIBASE 구동시점부터유지하는것으로 ALTIBASE 종료시초기화된다. ALTIBASE 모니터링쿼리가이드 14 page of 43
v$buffpool_stat 쿼리수행시 Disk 에서페이지를읽지않고버퍼풀의기존페이지를재사용한비율을의미하는 hit ratio 와같은버퍼풀관련통계정보를실시간으로나타낸다. v$lfg 리두로그파일관련성능뷰로특정컬럼하나가주요모니터링대상이다. 컬럼 LF_PREPARE_WAIT_COUNT 는현재리두로그파일에서새로운리두로그파일로 switching 하려할때, 다음리두로그파일이미처생성되지않아 service thread 가기다린횟수를나타낸다. 이값이크다면 PREPARE_LOG_FILE_COUNT 프로퍼티의값을더큰값으로변경후적용 ( 재구동 ) 하여충분한개수의리두로그파일을미리만들어지도록한다. ALTIBASE 구동시점부터누적및합산되는형태로 ALTIBASE 종료시초기화된다. ALTIBASE 모니터링쿼리가이드 15 page of 43
이중화관련주요메타테이블및성능뷰 이중화관련모니터링을위한조인쿼리시참고할주요키컬럼만표기한메타테이블및성능뷰의관계는아래와같다. 참고로 SYS_REPL_ITEMS_ 는사용자명과테이블이름을알기위해 SYS_USERS_ 와 SYS_TABLES_ 을굳이조인하지않아도되게끔컬럼이름과사용자이름이함께저장되어있다. 또한, 앞서언급했듯이세션번호와쿼리번호가없는이중화트랜잭션특성상 v$repsender_transtbl, v$repreceiver_transtbl 을통하여이중화상대서버에서대응되는트랜잭션을식별한다. SYS_REPL_HOSTS_ REPLICAT ION_NAME (FK) P 1 v$repsender REP_NAME (FK) 1 1 v$repsender_transtbl REP_NAME (FK) LOCAL_T ID (FK) REMOT E_T ID Z SYS_REPLICATIONS_ v$repgap REPLICAT ION_NAME REP_NAME (FK) P SYS_REPL_ITEMS_ REPLICAT ION_NAME (FK) T ABLE_OID (FK) 1 v$repreceiver REP_NAME (FK) 1 v$repreceiver_transtbl REP_NAME (FK) LOCAL_T ID (FK) REMOT E_T ID Z Z SYS_TABLES_ TABLE_ID T ABLE_OID (AK1.1) USER_ID v$lock T RANS_ID (FK) T ABLE_OID (FK) LOCK_DESC v$transaction ID MEMORY_VIEW _SCN v$lock_wait T RANS_ID (FK) WAIT _FOR_T RANS_ID (FK) 주요메타테이블 SYS_REPLICATIONS_ 모든이중화객체에대한정보가저장된메타테이블로이중화대상서버가이중화를반영한시점 (XSN) 과같은상세한정보를확인할수있다. SYS_REPL_HOSTS_ 이중화대상서버의정보가저장된메타테이블로이중화대상서버의주소 (HOST_IP) 와포트번호 (PORT_NO) 를확인할수있다. SYS_REPL_ITEMS_ 이중화대상테이블에대한정보가저장된메타테이블이다. ALTIBASE 모니터링쿼리가이드 16 page of 43
주요성능뷰 v$repsender 이중화송신쓰레드인 sender 의상태를나타내는성능뷰로 sender 가구동되어있지않으면조회되지않는다. v$repgap 최신리두로그일련번호와 sender 가전송한리두로그일련번호의간격 (gap) 나타내는성능뷰로이중화대상서버간동기화정도를의미하므로이중화모니터링시필수적으로활용된다. sender 에의해측정되므로 sender 가구동되어있지않으면이역시조회되지않는다. v$repreceiver 이중화수신쓰레드인 receiver 의상태를나타내는성능뷰로 receiver 가구동되어있지않으면조회되지않는다. v$repsender_transtbl sender 가수행중인이중화트랜잭션에대한정보를나타내는성능뷰로해당트랜잭션과대응되는상대편서버의트랜잭션을알수있다. 이역시 sender 가구동되어있지않으면조회되지않는다. v$repreceiver_transtbl receiver 가수행중인이중화트랜잭션에대한정보를나타내는성능뷰로해당트랜잭션과대응되는상대편서버의트랜잭션을알수있다. 이중화트랜잭션의 lock 으로인해로컬트랜잭션이대기하는현상이발생할때주요참고지표가된다. 이역시 receiver 가구동되어있지않으면조회되지않는다. ALTIBASE 모니터링쿼리가이드 17 page of 43
사용자의테이블스페이스접근가능여부, 시스템 / 객체권한, PSM, 뷰관련주요메타테이블및성능뷰 실시간모니터링이필요하진않으나운영업무중자주확인되는요소로 사용자의테이블스페이스접근가능여부, 시스템 / 객체권한, 스키마객체 를들수있다. 스키마객체의경우는 PSM( 프로시져 / 펑션 / 타입세트 ), 뷰가대표적이다. 관련정보를얻기위해조인쿼리시참고할주요키컬럼만표기한메타테이블및성능뷰의관계는아래와같다. TBS ACCESS SYS_GRANT_SYSTEM_ PRIVILEGES v$tablespaces ID GRANT OR_ID (FK) GRANT EE_ID (FK) PRIV_ID (FK) SYS_PRIVILEGES_ SYS_TBS_USERS_ TBS_ID (FK) USER_ID (FK) Z SYS_USERS_ USER_ID DEFAULT_TBS_ID (FK) SYS_GRANT_OBJECT_ PRIV_ID (FK) GRANT OR_ID (FK) GRANT EE_ID (FK) USER_ID (FK) OBJ_ID PRIV_ID SYS_TABLES_ SYS_VIEWS_ SYS_PROCEDURES_ T ABLE_ID VIEW _ID (FK) PROC_OID USER_ID (FK) TABLE_OID (AK1.1) Z USER_ID (FK) 1 USER_ID (FK) 1 SYS_VIEW_PARSE_ SYS_PROC_PARSE_ VIEW _ID (FK) PROC_OID (FK) PSM,VIEW 참고로 SYS_GRANT_OBJECT_ 에서객체번호인 OBJ_ID 컬럼 ( 녹색 ) 에대응하는다른객체의컬럼이름은객체에따라서로달라지므로주의가필요하다. 여기서는 TABLE_ID( 붉은색 ), VIEW_ID( 붉은색 ), PROC_OID( 보라색 ) 컬럼을예로들수있다. 또한, 대부분의데이터베이스객체는그에대응하는별도의메타테이블을가지나뷰의경우는테이블과함께관리되므로뷰의이름과같은구체적인정보는 SYS_TABLES_ 에서확인하여야만한다. SYS_VIEWS_ 는뷰의컴파일여부만저장되어있다. ( 즉, SYS_VIEWS_ 의 VIEW_ID 는 SYS_TABLES_ 의 TABLE_ID 와같다.) 주요메타테이블 SYS_TBS_USERS_ 사용자가접근가능한테이블스페이스목록이저장된메타테이블이다. SYS_PRIVILEGES_ 시스템권한, 객체권한에대한권한번호 (PRIV_ID) 와권한이름 (PRIV_NAME) 이저장된메타테이블이다. ALTIBASE 모니터링쿼리가이드 18 page of 43
SYS_GRANT_SYSTEM_ 사용자가부여받은시스템권한에대한권한번호 (PRIV_ID) 가저장된메타테이블이다. SYS_GRANT_OBJECT_ 사용자가부여받은객체권한에대한권한번호 (PRIV_ID) 와객체정보가저장된메타테이블로객체번호 (OBJ_ID) 를통해해당객체의상세정보를확인할수있다. SYS_VIEWS_, SYS_VIEW_PARSE_ SYS_VIEWS_ 는뷰의컴파일상태만저장된메타테이블이다. 뷰의생성구문은 SYS_VIEW_PARSE_ 를통하여확인할수있다. SYS_PROCEDURES_, SYS_PROC_PARSE_ PSM( 프로시져, 펑션, 타입세트 ) 에대한상세한정보가저장된메타테이블이다. 각 PSM 에대응하는생성구문은 SYS_PROC_PARSE_ 를통하여확인할수있다. ALTIBASE 모니터링쿼리가이드 19 page of 43
모니터링요소 ALTIBASE 모니터링시일반적으로요구되는사항을최소단위로분리, 이를모니터링요소라칭하고각모니터링요소를표로제시한다. 사용자는아래표를참조하여모니터링하고자하는요소에대응되는쿼리및 OS 명령어를확인하여활용하도록한다. 쿼리이해를위한메타테이블및성능뷰의관계에대한간략한설명은 ALTIBASE 메타테이블및성능뷰개요 섹션을참조하도록한다. 표에서각항목의의미는아래와같다. 구분 MEM( 메모리 DB 만해당 ), DISK( 디스크 DB 만해당 ), ALL( 메모리 / 디스크 DB 공통 ), REP( 이중화사용시 ) 그룹 모니터링요소 의그룹 ID 모니터링요소 에대한인공식별자 모니터링요소모니터링이가능한최소단위 비고세부사항및기타참고사항 구분그룹 ID 모니터링요소비고 ALL Session SS01 전체세션개수 ALL SS02 세션정보 ALL SS03 SYSDBA 자격으로접속중인세션정보 ALL Statement ST01 전체쿼리개수 ALL ST02 쿼리정보 TIMED_STATISTICS 활성필요 ALL ST03 현재수행중인쿼리개수 ALL ST04 현재수행중인쿼리정보 TIMED_STATISTICS 활성필요 ALL ST05 장시간수행쿼리정보 10 분이상 (600 초 ) / TIMED_STATISTICS 활성필수 ALL ST06 장시간수행되는 DML 트랜잭션의마지막쿼리정보 한시간이상 (3600 초 ) / TIMED_STATISTICS 활성필요 ALL ST07 풀스캔쿼리정보 인덱스미사용, 모든페이지를접근하는쿼리 ALL ST08 풀스캔쿼리수행횟수통계 ALL ST09 세션별쿼리목록 ALL Service Thread SV01 서비스쓰레드상태 ALL Transaction & lock TL01 트랜잭션및 Lock 정보 이중화트랜잭션포함 ALL redo logfile LO01 리두로그파일정보 ALL LO02 리두로그파일 prepare 대기누적횟수 MEM G C GC01 메모리 DB GC gap MEM GC02 GC 가대기하고있는트랜잭션에서수행중인쿼리 순간적인 CPU 소모가큰쿼리검출용으로활용가능 ALL Memory MS01 ALTIBASE 의메모리사용현황 ( 모듈단위 ) ALL MS02 ALTIBASE 의메모리사용량합계 MEM TBS(tablespace) TS01 메모리테이블스페이스사용량 MEM TS02 전체메모리테이블스페이스사용량 DISK TS03 디스크테이블스페이스사용량 DISK TS04 디스크언두테이블스페이스사용량 [TS03] 에서언두테이블스페이스부분만추림. ALL TS05 전체테이블스페이스사용량 [TS01], [TS02], [TS03] 을 UNION ALL 한결과 MEM TS06 메모리테이블스페이스데이터파일체크포인트경로 MEM TS07 메모리테이블스페이스데이터파일 DISK TS08 디스크테이블스페이스데이터파일 DISK TS09 디스크테이블스페이스데이터파일별 I/O DISK TS10 디스크테이블스페이스데이터파일별단일페이지 Read I/O 현재멀티페이지 Read 를지원하지않음. ALL TS11 전체테이블스페이스상태 DISK Disk Buffer DB01 디스크버퍼 Hit Ratio MEM Object OB01 메모리테이블 MEM OB02 큐 MEM OB03 Efficiency 가낮은메모리테이블 크기가 1024M, efficiency 가 50% 이하 MEM OB04 메모리인덱스, 큐인덱스 DISK OB05 디스크인덱스 DISK OB06 디스크테이블 ALL OB07 시퀀스 ALL OB08 시노님 ALL OB09 PSM( 프로시저 / 펑션 / 타입세트 ) ALTIBASE 모니터링쿼리가이드 20 page of 43
ALL OB10 PSM 생성구문 PSM 이름을입력 ALL OB11 VIEW ALL OB12 VIEW 생성구문 ALL Privileges PV01 사용자시스템권한 ALL PV02 사용자오브젝트권한 ALL Constraints CT01 전체제약조건목록 ALL CT02 PK, FK, UNIQUE 관련제약조건및테이블, 인덱스목록 ALL CT03 복합인덱스컬럼구성목록 ALL CT04 인덱스정보요약 REP Replication RP01 이중화 sender 정보 REP RP02 이중화 receiver 정보 REP RP03 이중화갭 REP RP04 이중화전체현황 REP RP05 이중화를수행하지못해누적된리두로그파일측정 응용에따라간접적으로 MB 환산이가능. REP RP06 이중화대상테이블목록 ALTIBASE 모니터링쿼리가이드 21 page of 43
모니터링쿼리 각모니터링요소에대응하는쿼리로모니터링목적에따라 SELECT, WHERE, LIMIT 절을변경하도록한다. 컬럼에대한간략한설명은있으나컬럼이름이직관적이어서별도의설명이필요없다고판단되는경우, 이전에이미설명한컬럼이중복되어나오는경우에는생략하였다. 컬럼에대한보다상세한설명과이해를위해서기본적으로 ATC (http://atc.altibase.com) 에게시된운영자매뉴얼을함께참조하는것을권장한다. Session 세션의상태를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. 특정세션에관한정보만모니터링하려면 WHERE 절에 v$session 의 id 컬럼을기술하면된다. [SS01] 전체세션개수 [back] SELECT count(*) tot_stmt_cnt FROM v$session [SS02] 세션정보 [back] SELECT a.id session_id, a.db_username user_name, replace2(replace2(a.comm_name, 'socket-', null), '-server', null) client_ip, a.client_app_info, a.client_pid, a.session_state, decode(a.autocommit_flag, 1, 'ON', 'OFF') autocommit, decode(a.login_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.login_time / (24*60*60), 'mm/dd hh:mi:ss')) login_time, decode(a.idle_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.idle_start_time / (24*60*60), 'mm/dd hh:mi:ss')) idle_time, nvl(ltrim(b.query), 'NONE') current_query FROM v$session a left outer join v$statement b on a.current_stmt_id = b.id client_ip 세션과관련된클라이언트응용프로그램의 ip 주소. client_app_info 세션과관련된클라이언트응용프로그램의이름. client_pid session_state idle_time 세션과관련된클라이언트응용프로그램의프로세스아이디로클라이언트응용프로그램이실행되는 OS 에서관련프로세스를식별가능하다. 세션의상태를나타내는문자열로 INIT, AUTH, SERVICE READY, SERVICE, END, ROLLBACK, UNKNOWN 7 가지상태가있다. 세션이아무것도하지않기시작한시간으로 idle_timeout 의기준이된다. 단위는유닉스시간 (unix time) 이다. current_query 세션에서가장마지막으로수행하였거나현재수행중인쿼리. [SS03] SYSDBA 자격으로접속중인세션정보 [back] SELECT a.id session_id, a.db_username user_name, replace2(replace2(a.comm_name, 'socket-', null), '-server', null) client_ip, a.client_app_info, a.client_pid, a.session_state, decode(a.autocommit_flag, 1, 'ON', 'OFF') autocommit, decode(a.login_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.login_time / (24*60*60), 'mm/dd hh:mi:ss')) login_time, decode(a.idle_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + ALTIBASE 모니터링쿼리가이드 22 page of 43
a.idle_start_time / (24*60*60), 'mm/dd hh:mi:ss')) idle_time, nvl(ltrim(b.query), 'NONE') current_query FROM v$session a left outer join v$statement b on a.current_stmt_id = b.id WHERE a.sysdba_flag = 1 Statement 쿼리의상태를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. 쿼리수행과관련된상세한시간정보를확인하기위해서는기본적으로 TIMED_STATISTICS 가활성화 (1) 되어있어야한다. 특정세션에관한정보만모니터링하려면 WHERE 절에 v$statement 의 session_id 컬럼을기술하면된다. [ST01] 전체쿼리개수 [back] SELECT count(*) as stmt_cnt FROM v$statement [ST02] 쿼리정보 [back] SELECT session_id, id stmt_id, tx_id, (parse_time+validate_time+optimize_time) prepare_time, fetch_time, execute_time, total_time, execute_flag, decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time, nvl(ltrim(query), 'NONE') query FROM v$statement ORDER BY execute_time desc stmt_id 쿼리의아이디. tx_id 쿼리와관련된트랜잭션의아이디. prepare_time execute_time fetch_time total_time prepare(parse, validation, optimization) 를수행하는데소요된시간으로단위는마이크로초다. 쿼리가수행될때마다갱신된다. prepare 완료후 execution 을수행하는데소요된시간으로쿼리가수행될때마다갱신된다. query timeout 의기준이되며단위는마이크로초다. 쿼리에대한결과를클라이언트가가져갈 (fetch) 때소요되는시간이다. 결과의크기에따라하나의쿼리는여러번의 fetch 를수행할수있으며이때마다갱신된다. fetch_timeout 의기준이되며단위는마이크로초이다. 하나의쿼리가수행되기위해소요된시간의합계를의미하는것으로 prepare, fetch, execution 을모두포함한다. 쿼리가수행될때마다갱신되며단위는마이크로초다. execute_flag 이값이 0 이면 prepare 만된상태이며 1 이면 execution 중인상태이다. last_start_time 가장마지막으로수행된쿼리가수행을시작한시간으로단위는유닉스시간 (unix time) 이다. 이값은 TIMED_STATISTICS 프로퍼티를활성화 (0) 하지않아도갱신된다. [ST03] 현재수행중인쿼리개수 [back] SELECT count(*) as active_stmt_cnt FROM v$statement WHERE execute_flag = 1 ALTIBASE 모니터링쿼리가이드 23 page of 43
[ST04] 현재수행중인쿼리정보 [back] SELECT session_id, id stmt_id, tx_id, (parse_time+validate_time+optimize_time) prepare_time, fetch_time, execute_time, total_time, decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time, nvl(ltrim(query), 'NONE') query FROM v$statement WHERE execute_flag = 1 ORDER BY execute_time desc [ST05] 장시간으로수행쿼리정보 (600 초기준 ) [back] SELECT session_id, id stmt_id, tx_id, (parse_time+validate_time+optimize_time) prepare_time, fetch_time, execute_time, total_time, decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time, nvl(ltrim(query), 'NONE') query FROM v$statement WHERE execute_flag = 1 and execute_time/1000000 > 600 ORDER BY execute_time desc [ST06] 장시간수행되는 DML 트랜잭션의마지막쿼리정보 (3600 초기준 ) [back] SELECT st.session_id, ss.comm_name client_ip, ss.client_pid, ss.client_app_info, (base_time - tr.first_update_time) as utrans_time, execute_time, total_time, decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time, nvl(ltrim(st.query), 'NONE') query FROM v$transaction tr, v$statement st, v$sessionmgr, v$session ss WHERE tr.id = st.tx_id and st.session_id = ss.id and tr.first_update_time!= 0 -- 0:read only transaction and (base_time - tr.first_update_time) > 3600 ORDER BY utrans_time desc utrans_time 트랜잭션이최초에변경연산을시작한시점을기준으로현재까지의경과시간을의미한다. utrans_timeout 의기준이되며단위는초 (sec) 다. 참고로이값을계산하기위한 v$sessionmgr 의 base_time 과 v$transaction 의 first_update_time 은 TIMED_STATISTICS 프로퍼티를활성화 (0) 하지않아도갱신된다. ALTIBASE 모니터링쿼리가이드 24 page of 43
[ST07] 풀스캔쿼리정보 [back] SELECT session_id, s.comm_name client_ip, s.client_pid, s.client_app_info, decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time, (parse_time+validate_time+optimize_time) prepare_time, fetch_time, execute_time, total_time, nvl(ltrim(query), 'NONE') query FROM v$statement t, v$session s WHERE s.id = t.session_id and (mem_cursor_full_scan > 0 or disk_cursor_full_scan > 0) and upper(query) not like '%INSERT%' ORDER BY execute_time desc [ST08] 풀스캔쿼리수행횟수통계 ( 현재접속중인세션에한함 ) [back] SELECT count(execute_success) execute_cnt, substr(ltrim(query),1,40) query FROM v$statement WHERE (mem_cursor_full_scan > 0 or disk_cursor_full_scan > 0) and upper(query) not like '%INSERT%' GROUP BY query ORDER BY execute_cnt desc execute_cnt 성공적으로수행완료된쿼리실행회수의합계 [ST09] 세션별쿼리목록 [back] SELECT session_id, id stmt_id, tx_id, substr(query,1,30) query FROM v$statement ORDER BY 1,2 Service Thread 서비스쓰레드와관련된정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [SV01] 서비스쓰레드상태 [back] SELECT run_mode, state, count(*) cnt FROM v$service_thread GROUP BY run_mode, state run_mode state 서비스쓰레드의작동모드. / SHARED, DEDICATED 서비스쓰레드의상태. 일반적으로 POLL 또는 EXECUTE 상태이다. NONE : 서비스쓰레드가초기화된상태 POLL : 서비스쓰레드가이벤트를기다리고있는상태 QUEUE-WAIT : 서비스쓰레드가작업큐 (Task Queue) 를대기하는상태 EXECUTE : 서비스쓰레드가쿼리를수행중인상태 ALTIBASE 모니터링쿼리가이드 25 page of 43
Transaction & Lock 트랜잭션및 Lock 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [TL01] Transaction 및 lock 정보 [back] SELECT tx.id tx_id, wait_for_trans_id blocked_tx_id, decode(tx.status, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END') status, decode(tx.log_type, 0, u1.user_name, 'REPLICATION') user_name, decode(tx.log_type, 0, tx.session_id, rt.rep_name) session_id, decode(tx.log_type, 0, st.comm_name, rr.peer_ip) client_ip, decode(st.autocommit_flag, 1, 'ON', 'OFF') autocommit, l.lock_desc, decode(tx.first_update_time, 0, '0', to_char(to_date('1970010109','yyyymmddhh') + tx.first_update_time / (60*60*24), 'MM/DD HH:MI:SS')) first_update_time, u2.user_name '.' t.table_name table_name, decode(tx.log_type, 0, substr(st.query, 1, 10), 'REMOTE TX_ID ' remote_tid) current_query, decode(tx.ddl_flag,0, 'non-ddl', 'DDL') ddl, decode(tx.first_undo_next_lsn_fileno, -1, '-', tx.first_undo_next_lsn_fileno) 'logfile#' FROM v$transaction tx, v$lock l left outer join (SELECT st.*, ss.autocommit_flag, ss.db_userid, ss.comm_name FROM v$statement st, v$session ss WHERE ss.id = st.session_id and ss.current_stmt_id = st.id) st on l.trans_id = st.tx_id left outer join v$repreceiver_transtbl rt on l.trans_id = rt.local_tid left outer join v$repreceiver rr on rt.rep_name = rr.rep_name left outer join v$lock_wait lw on l.trans_id = lw.trans_id left outer join system_.sys_users_ u1 on st.db_userid = u1.user_id, system_.sys_tables_ t left outer join system_.sys_users_ u2 on t.user_id = u2.user_id WHERE tx.id = l.trans_id and t.table_oid = l.table_oid and tx.status!= 6 -- 6:END ORDER BY tx.id, st.id, tx.first_update_time desc tx_id 트랜잭션의아이디. blocked_tx_id lock 획득대기를유발한트랜잭션의아이디로없는경우는공백이다. status 트랜잭션의상태를의미하는 0~6 까지의숫자를문자열로나타낸다. BEGIN(0), PRECOMMIT(1), COMMIT_IN_MEMORY(2), COMMIT(3), ABORT(4), BLOCKED(5), END(6) user_name 트랜잭션을수행중인사용자의이름으로이중화트랜잭션은 REPLICATION 이다. session_id 트랜잭션을수행중인세션의아이디로이중화트랜잭션은해당 이중화객체이름 이다. client_ip 세션과관련된클라이언트응용프로그램의 ip 주소로이중화트랜잭션은 이중화대상서버 의 ip 이다. first_update_time 트랜잭션이최초로변경연산을시작한시간. SELECT 만수행하는트랜잭션은 0 이다. lock_desc lock 에대한문자열로 IX_LOCK, IS_LOCK, X_LOCK 등이있다. table_name lock 획득대상테이블로 사용자이름. 테이블이름 형식으로나타낸다. current_query ddl 트랜잭션에서가장마지막으로수행하였거나현재수행중인쿼리. 이중화트랜잭션의경우는상대편서버의 ip 주소와트랜잭션아이디를나타낸다. 트랜잭션의 DDL 여부. / DDL(0) non-ddl(1) logfile# 트랜잭션과관련된리두로그파일번호. SELECT 만수행하는트랜잭션은 - 이다. ALTIBASE 모니터링쿼리가이드 26 page of 43
redo logfile redo logfile 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [LO01] 리두로그파일정보 [back] SELECT oldest_active_logfile oldest_logfile, current_logfile current_logfile, current_logfile-oldest_active_logfile logfile_gap FROM v$archive oldest_logfile 가장오래된리두로그파일의번호. current_logfile 현재사용중인온라인리두로그파일의번호. logfile_gap 현재사용중인온라인리두로그파일과가장오래된리두로그파일사이의리두로그파일개수. [LO02] 리두로그파일 prepare 대기누적횟수 [back] SELECT lf_prepare_wait_count FROM v$lfg lf_prepare_wait_count 현재리두로그파일에서새로운리두로그파일로 switching 하려할때, 다음리두로그파일이미처생성되지않아 service thread 가기다린횟수를나타낸다. 이값이크다면 PREPARE_LOG_FILE_COUNT 프로퍼티의값을더큰값으로변경후적용 ( 재구동 ) 하여충분한개수의리두로그파일을미리만들어지도록한다. ALTIBASE 구동시점부터누적및합산되는형태로 ALTIBASE 종료시초기화된다. GC GC 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [GC01] 메모리 DB GC gap [back] SELECT gc_name, scnoftail, minmemscnintxs, add_oid_cnt-gc_oid_cnt gc_gap FROM v$memgc gc_name scnoftail minmemscnintxs gc_gap GC 의이름으로 2 개가존재한다. MEM_LOGICAL_AGER 는인덱스의 old version 을삭제하는 GC 이며 MEM_DELTHR 는테이블레코드의 old version 을삭제하는 GC 이다. GC 에의해확인된삭제해야할 old version 중가장최신 version 의번호 ALTIBASE 가장오래된 old version 의번호 각 GC 가삭제해야할 old version 의양의의미하는것으로이값이클수록삭제해야할 old version 의양이많다는의미이다. 이때 scnoftail > minmemscnintxs 이면 active 트랜잭션때문에삭제를진행하지못하는것이고, 반대로 scnoftail < minmemscnintxs 이면삭제는계속진행중이지만계속되는갱신트랜잭션의완료로인해삭제해야할 old version 이증가하고있는상황으로판단할수있다. [GC02] GC 가대기하고있는트랜잭션에서수행중인쿼리 [back] ALTIBASE 모니터링쿼리가이드 27 page of 43
GC 가대기하고있는트랜잭션이란 ALTIBASE 에서가장오래된 old version 을참조하는트랜잭션을의미한다. 이와같은트랜잭션으로인해 GC 가동작하지않고무한히대기하게된다면메모리사용량또한무한히증가될수있으므로 GC gap 증가시확인요소중한가지다. 아래쿼리를수행함으로 GC 가대기하고있는트랜잭션을수행하는세션에서수행한쿼리를확인할수있다. 또한, 인덱스스캔은하나분포도가좋지않아풀스캔이나다름없어순간적인 CPU 소모를하는쿼리를검출할때도유용하게활용될수있다. 주의할것은상황에따라한번의수행으로검출되지않을수있기에주기적, 반복적으로수행하여야한다는것이다. SELECT session_id, total_time, execute_time, tx_id, query FROM v$statement WHERE tx_id in (SELECT id FROM v$transaction WHERE memory_view_scn = (SELECT minmemscnintxs FROM v$memgc limit 1)) and execute_flag = 1 ORDER BY 2 desc Memory OS 에서 ALTIBASE 가점유하는메모리사용률관련정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [MS01] ALTIBASE 의메모리사용현황 ( 모듈단위 ) [back] SELECT name, round(max_total_size/1024/1024) 'ALLOC_MAX(M)', round(alloc_size/1024/1024) 'ALLOC(M)' FROM v$memstat ORDER BY 3 desc NAME ALTIBASE 를구성하는모듈의이름으로모듈의목록은 ALTIBASE 버전에따라차이가있을수있다. 주요모듈에대한간략한설명은아래와같다. Storage_Disk_Ager : Disk Garbage Collection 에사용되는메모리 Storage_Disk_Buffer : Disk Buffer 관리에사용되는메모리 Storage_Disk_Collection : Direct Path Insert 와 LOB 연산에사용되는메모리 Storage_Disk_Datafile : Disk File 관리에사용되는메모리 Storage_Disk_Index : Disk Index 관리에사용되는메모리 Storage_Disk_Page : Disk Page 관리에사용되는메모리 Storage_Disk_Recovery : Disk 복구에사용되는메모리 Storage_Memory_Ager : Memory Garbage Collection 에사용되는메모리 Storage_Memory_Collection : Memory Record 관리에사용되는메모리 Storage_Memory_Interface : Cursor 등의 Interface 관리에사용되는메모리 Storage_Memory_Locking : Locking 관리에사용되는메모리 Storage_Memory_Manager : Memory Data 저장에사용되는메모리 Storage_Memory_Index : Index 관리에사용되는메모리 Storage_Memory_Page : Memory Page 관리에사용되는메모리 Storage_Memory_Recovery : Memory 복구에사용되는메모리 Storage_Memory_Utility : Storage Manager Tool 에사용되는메모리 Storage_Memory_Transaction : Transaction 관리에사용되는메모리 Temp_Memory : Memory Index 저장에사용되는메모리 Transaction_Table : Transaction Table 관리에사용되는메모리 Transaction_OID_List : Garbage Collection 의 OID 저장에사용되는메모리 Transaction_Table_Info : Transaction Table 정보관리에사용되는메모리 ALTIBASE 모니터링쿼리가이드 28 page of 43
Index_Memory : Index 저장에사용되는메모리 LOG_Memory : 로깅에사용되는메모리 Query_Meta : meta 정보관리에사용되는메모리 Query_DML : dml 수행에사용되는메모리 Query_Sequence : 시퀀스관리에사용되는메모리 Query_Replication : 이중화에사용되는메모리 Query_PSM_Node : psm array 변수관리에사용되는메모리 Query_PSM_Execute : psm 수행에사용되는메모리 Query_Prepare : 쿼리 prepare 에사용되는메모리 Query_Execute : 쿼리 execution 에사용되는메모리 Query_Transaction : trigger 수행에사용되는메모리 Query_Binding : 쿼리호스트변수 bind 에사용되는메모리 Query_Conversion : 쿼리호스트변수 bind 시 conversion 에사용되는메모리 Query_Common : 쿼리관리에사용되는기타메모리 Mathematics : 각종연산에사용되는메모리 ALLOC_MAX(M) 해당모듈이사용했던최대메모리크기. ALLOC(M) 해당모듈이현재사용하고있는메모리크기. [MS02] ALTIBASE 의메모리사용량합계 [back] SELECT round(sum(max_total_size)/1024/1024) 'ALLOC_MAX(M)', round(sum(alloc_size)/1024/1024) 'ALLOC(M)' FROM v$memstat TBS(tablespace) TBS 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [TS01] 메모리테이블스페이스사용량 [back] SELECT id tbs_id, decode(type, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') tbs_type, name tbs_name, decode(maxsize, 140737488322560, 'UNDEFINED', maxsize) 'MAX(M)', ROUND(allocated_page_count * page_size / 1024 / 1024, 2) 'TOTAL(M)', ROUND(nvl(m.alloc_page_count-m.free_page_count,total_page_count)*page_size/1024/1024,2) 'ALLOC(M)', mt.used 'USED(M)', decode(maxsize, 140737488322560, ROUND((m.alloc_page_count-m.free_page_count)*page_size/ mem_max_db_size*100,2), ROUND((m.alloc_page_count-m.free_page_count)*page_size/maxsize*100,2)) 'USAGE(%)', decode(state,1,'offline',2,'online',5,'offline BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') state, decode(autoextend_mode,1,'on','off') 'AUTOEXTEND' FROM v$database d, v$tablespaces t, v$mem_tablespaces m, (SELECT tablespace_id, round(sum((fixed_used_mem + var_used_mem))/(1024*1024),3) used FROM v$memtbl_info GROUP BY tablespace_id) mt WHERE t.id = m.space_id and id = mt.tablespace_id MAX(M) TOTAL(M) 메모리테이블스페이스가최대로할당가능한페이지의합계로페이지는 사용중인페이지 와 빈페이지 로구분된다. 최대크기가지정되지않은경우는 UNDEFINED 로나타낸다. 메모리테이블스페이스가현재까지할당받은페이지의합계. 즉, 현재까지할당된 사용중인페이지 와 빈 ALTIBASE 모니터링쿼리가이드 29 page of 43
ALLOC(M) USED(M) USAGE(%) AUTOEXTEND 페이지 의합계로데이터파일크기와대응한다. ALTIBASE 구동시점엔 사용중인페이지 만메모리에적재된다. 메모리테이블스페이스가현재까지할당받은페이지중 빈페이지 를제외한 사용중인페이지 만의합계이다. 예를들어, 100M 크기의메모리테이블에 DROP 또는 TRUNCATE 를수행하면전체페이지합계는변함없으나페이지반납을통해 사용중인페이지 가 빈페이지 가되므로이값이 0 에가깝게된다. 메모리테이블스페이스의 사용중인페이지 중에서 실제로데이터가적재된페이지 의합계이다. 예를들어, ALLOC 이 100M 크기인메모리테이블에전체 DELELE 를수행하면 ALLOC 은 100M 로변함없으나 USED 는 0 에가깝게된다. 메모리테이블스페이스가 최대로할당가능한페이지 대비 사용중인페이지 에대한백분율.( 즉,ALLOC/ TOTAL) 딕셔너리테이블스페이스와같이최대크기가지정되지않은메모리테이블스페이스의경우는프로퍼티 MEM_MAX_DB_SIZE 에의해정의되는 전체메모리테이블스페이스가최대로할당가능한페이지 대비로계산된다. 메모리테이블스페이스의자동확장여부. / ON(1), OFF(2) [TS02] 전체메모리테이블스페이스사용량 [back] SELECT mem_max_db_size/1024/1024 'MAX(M)', round(mem_alloc_page_count*32/1024, 2) 'TOTAL(M)', trunc((mem_alloc_page_count-mem_free_page_count)*32/1024, 2) 'ALLOC(M)', (SELECT round(sum((fixed_used_mem + var_used_mem))/(1024*1024),3) FROM v$memtbl_info) 'USED(M)', trunc(((mem_alloc_page_count-mem_free_page_count)*32*1024)/mem_max_db_size, 4)*100 'USAGE(%)' FROM v$database MAX(M) 전체메모리테이블스페이스가최대로할당가능한페이지의합계로프로퍼티 MEM_MAX_DB_SIZE 에의해정의된다. TOTAL(M) 전체메모리테이블스페이스가현재까지할당받은페이지의합계이다. ALLOC(M) 전체메모리테이블스페이스가현재까지할당받은페이지중 빈페이지 를제외한 사용중인페이지 만의합계이다. USED(M) 전체메모리테이블스페이스의 사용중인페이지 중에서 실제로데이터가적재된페이지 의합계이다. USAGE(%) 전체메모리테이블스페이스가 최대로할당가능한페이지 대비 사용중인페이지 에대한백분율. ( 즉,ALLOC/MAX) [TS03] 디스크테이블스페이스사용량 [back] SELECT id tbs_id, decode(type, 3, 'DISK_SYS_DATA', 4, 'DISK_USER_DATA', 5, 'DISK_SYS_TEMP', 6, 'DISK_USER_TEMP', 7, 'DISK_UNDO') tbs_type, name tbs_name, ROUND(d.max * page_size / 1024 /1024, 2) 'MAX(M)', ROUND(total_page_count * page_size / 1024 / 1024, 2) 'TOTAL(M)', decode(type, 7, ROUND((SELECT (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$udsegs)+(select (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$tssegs),2), ROUND(allocated_page_count * page_size / 1024 / 1024,2)) 'ALLOC(M)', decode(type, 7, ROUND(((SELECT sum(total_extent_count*page_count_in_extent) FROM v$udsegs)+(select sum(total_extent_count*page_count_in_extent) FROM v$tssegs)) / d.max* 100, 2), ROUND(allocated_page_count / d.max * 100, 2)) 'USAGE(%)', decode(state,1,'offline',2,'online',5,'offline BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') state, d.autoextend FROM v$tablespaces t, (SELECT spaceid, sum(decode(maxsize, 0, currsize, maxsize)) as max, decode(max(autoextend),1,'on','off') 'AUTOEXTEND' FROM v$datafiles group by spaceid) d ALTIBASE 모니터링쿼리가이드 30 page of 43
WHERE t.id = d.spaceid MAX(M) TOTAL(M) ALLOC(M) USED(M) USAGE(%) 5.1.5 변경사항 디스크테이블스페이스가최대로할당가능한페이지의합계로페이지는 사용중인페이지 와 빈페이지 로구분된다. 디스크테이블스페이스가현재까지할당받은페이지의합계. 즉, 현재까지할당된 사용중인페이지 와 빈페이지 의합계로데이터파일크기와대응한다. 디스크테이블스페이스가현재까지할당받은페이지중 빈페이지 를제외한 사용중인페이지 만의합계이다. 예를들어, 100M 크기의디스크테이블에 DROP 또는 TRUNCATE 를수행하면전체페이지합계는변함없으나페이지반납을통해 사용중인페이지 가 빈페이지 가되므로이값이 0 에가깝게된다. 디스크테이블스페이스의 사용중인페이지 중에서 실제로데이터가적재된페이지 의합계이다. 하지만, 디스크테이블스페이스관리기법의변화로인해 ALTIBASE 5.1.5 부터 5.3 까지는확인할수없다. ALTIBASE 4, 5.5 이상부터는메모리테이블스페이스처럼확인이가능하다. 디스크테이블스페이스가 최대로할당가능한페이지 대비 사용중인페이지 에대한백분율이다.( 즉,ALLOC/ TOTAL) 5.1.5 에는 v$udssegs 와 v$tssegs 가없습니다. 아래와같이수정되어야한다. ALLOC(M) USAGE(%) decode(type, 7, round((select sum(total_page_count-free_page_count)*(select page_size from v$tablespaces where id = 3)/1024/1024 FROM v$undo_seg), 2), round(allocated_page_count*page_size/1024/1024, 2)) 'ALLOC(M)' decode(type, 7, round((select sum(total_page_count-free_page_count) FROM v$undo_seg) / d.max* 100, 2), round(allocated_page_count / d.max * 100, 2)) 'USAGE(%)' [TS04] 디스크언두테이블스페이스사용량 [back] SELECT name tbs_name, ROUND(d.max * page_size / 1024 /1024, 2) 'MAX(M)', ROUND(total_page_count * page_size / 1024 / 1024, 2) 'TOTAL(M)', ROUND((SELECT (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$udsegs)+(select (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$tssegs),2) 'ALLOC(M)', ROUND(((SELECT sum(total_extent_count*page_count_in_extent) FROM v$udsegs)+(select sum(total_extent_count*page_count_in_extent) FROM v$tssegs)) / d.max* 100, 2) 'USAGE(%)', decode(state,1,'offline',2,'online',5,'offline BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') state, d.autoextend FROM v$tablespaces t, (SELECT spaceid, sum(decode(maxsize, 0, currsize, maxsize)) as max, decode(max(autoextend),1,'on','off') 'AUTOEXTEND' FROM v$datafiles group by spaceid) d WHERE t.id = d.spaceid and t.id = 3 5.1.5 변경사항 5.1.5 에는 v$udssegs 와 v$tssegs 가없습니다. 아래와같이수정되어야한다. ALLOC(M) USAGE(%) decode(type, 7, round((select sum(total_page_count-free_page_count)*(select page_size from v$tablespaces where id = 3)/1024/1024 FROM v$undo_seg), 2), round(allocated_page_count*page_size/1024/1024, 2)) 'ALLOC(M)' decode(type, 7, round((select sum(total_page_count-free_page_count) FROM v$undo_seg) / d.max* 100, 2), round(allocated_page_count / d.max * 100, 2)) 'USAGE(%)' [TS05] 전체테이블스페이스사용량 [back] SELECT '-' tbs_id, 'MEMORY_DB_TOTAL' tbs_type, '-' tbs_name, to_char(mem_max_db_size/1024/1024) 'MAX(M)', round(mem_alloc_page_count*32/1024, 2) 'TOTAL(M)', trunc((mem_alloc_page_count-mem_free_page_count)*32/1024, 2) 'ALLOC(M)', ALTIBASE 모니터링쿼리가이드 31 page of 43
to_char((select round(sum((fixed_used_mem + var_used_mem))/(1024*1024),3) FROM v$memtbl_info)) 'USED(M)', trunc(((mem_alloc_page_count-mem_free_page_count)*32*1024)/mem_max_db_size, 4)*100 'USAGE(%)', '' state, '' 'AUTOEXTEND' FROM v$database UNION ALL SELECT to_char(id) tbs_id, decode(type, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') tbs_type, name tbs_name, decode(maxsize, 140737488322560, 'UNDEFINED', maxsize) 'MAX(M)', ROUND(allocated_page_count * page_size / 1024 / 1024, 2) 'TOTAL(M)', ROUND(nvl(m.alloc_page_count-m.free_page_count,total_page_count)*page_size/1024/1024,2) 'ALLOC(M)', to_char(mt.used) 'USED(M)', decode(maxsize, 140737488322560, ROUND((m.alloc_page_count-m.free_page_count)*page_size/ mem_max_db_size*100,2), ROUND((m.alloc_page_count-m.free_page_count)*page_size/maxsize*100,2)) 'USAGE(%)', decode(state,1,'offline',2,'online',5,'offline BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') state, decode(autoextend_mode,1,'on','off') 'AUTOEXTEND' FROM v$database d, v$tablespaces t, v$mem_tablespaces m, (SELECT tablespace_id, round(sum((fixed_used_mem + var_used_mem))/(1024*1024),3) used FROM v$memtbl_info GROUP BY tablespace_id) mt WHERE t.id = m.space_id and id = mt.tablespace_id UNION ALL SELECT to_char(id) tbs_id, decode(type, 3, 'DISK_SYS_DATA', 4, 'DISK_USER_DATA', 5, 'DISK_SYS_TEMP', 6, 'DISK_USER_TEMP', 7, 'DISK_UNDO') tbs_type, name tbs_name, to_char(round(d.max * page_size / 1024 /1024, 2)) 'MAX(M)', ROUND(total_page_count * page_size / 1024 / 1024, 2) 'TOTAL(M)', decode(type, 7, ROUND((SELECT (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$udsegs)+(select (sum(total_extent_count*page_count_in_extent) * page_size)/1024/1024 FROM v$tssegs),2), ROUND(allocated_page_count * page_size / 1024 / 1024,2)) 'ALLOC(M)', '-' 'USED(M)', decode(type, 7, ROUND(((SELECT sum(total_extent_count*page_count_in_extent) FROM v$udsegs)+(select sum(total_extent_count*page_count_in_extent) FROM v$tssegs)) / d.max* 100, 2), ROUND(allocated_page_count / d.max * 100, 2)) 'USAGE(%)', decode(state,1,'offline',2,'online',5,'offline BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') state, d.autoextend FROM v$tablespaces t, (SELECT spaceid, sum(decode(maxsize, 0, currsize, maxsize)) as max, decode(max(autoextend),1,'on','off') 'AUTOEXTEND' FROM v$datafiles group by spaceid) d WHERE t.id = d.spaceid 5.1.5 변경사항 5.1.5 에는 v$udssegs 와 v$tssegs 가없습니다. 디스크테이블스페이스쿼리의컬럼은아래와같이수정되어야합니다. ALLOC(M) USAGE(%) decode(type, 7, round((select sum(total_page_count-free_page_count)*(select page_size from v$tablespaces where id = 3)/1024/1024 FROM v$undo_seg), 2), round(allocated_page_count*page_size/1024/1024, 2)) 'ALLOC(M)' decode(type, 7, round((select sum(total_page_count-free_page_count) FROM v$undo_seg) / d.max* 100, 2), round(allocated_page_count / d.max * 100, 2)) 'USAGE(%)' ALTIBASE 모니터링쿼리가이드 32 page of 43
[TS06] 메모리테이블스페이스데이터파일체크포인트경로 [back] SELECT m.space_id tbs_id, space_name tbs_name, checkpoint_path FROM v$mem_tablespaces m, v$mem_tablespace_checkpoint_paths c WHERE m.space_id = c.space_id [TS07] 메모리테이블스페이스데이터파일 [back] SELECT mem_data_file datafile_name FROM v$stable_mem_datafiles datafile_name 데이터파일의이름. 물리적인경로와파일명까지나타낸다. [TS08] 디스크테이블스페이스데이터파일 [back] SELECT b.name tbs_name, a.id 'FILE#', a.name datafile_name, currsize*8/1024 'ALLOC(M)', round(case2(a.maxsize=0, currsize, a.maxsize)*8/1024) 'MAX(M)', decode(autoextend, 0, 'OFF', 'ON') 'AUTOEXTEND' FROM v$datafiles a, v$tablespaces b WHERE b.id = a.spaceid ORDER BY b.name, a.id FILE# 데이터파일의번호. 하나의디스크테이블스페이스는여러개의데이터파일을가질수있다. [TS09] 디스크테이블스페이스데이터파일별 I/O [back] SELECT name tbs_name, a.phyrds phy_read, a.phywrts phy_write, a.phyrds+a.phywrts phy_total, trunc(a.phyrds/read_sum*100,2) 'READ(%)', trunc(a.phywrts/write_sum*100,2) 'WRITE(%)', trunc( (a.phyrds+a.phywrts) / (read_sum+write_sum) * 100, 2) 'TOTAL(%)', a.avgiotim avg_io_time FROM v$filestat a, v$datafiles b, (SELECT sum(phyrds) read_sum, sum(phywrts) write_sum FROM v$filestat ) c WHERE a.spaceid = b.spaceid and a.fileid = b.id and read_sum > 0 and write_sum > 0 ORDER BY a.phyrds+a.phywrts desc, rownum desc phy_read 물리적 Read I/O 발생회수. phy_write 물리적 Write I/O 발생회수. phy_total 물리적 I/O 발생회수. read_per 전체물리적 Read I/O 대비발생비율. write_per 전체물리적 Write I/O 대비발생비율. ALTIBASE 모니터링쿼리가이드 33 page of 43
total_per 전체물리적 I/O 대비발생비율. avg_io_time 평균물리적 I/O 시간으로단위는밀리초다. [TS10] 디스크테이블스페이스데이터파일별단일페이지 Read I/O [back] SELECT b.name tbs_name, a.singleblkrds read_cnt_per_page, a.singleblkrdtim read_time_per_page, trunc(a.singleblkrdtim/a.singleblkrds,2) average_time FROM v$filestat a, v$datafiles b WHERE a.spaceid = b.spaceid and a.fileid = b.id and a.singleblkrds > 0 ORDER BY average_time desc singleblkrds 단일페이지에대한 Read 개수. singleblkrdtim 단일페이지에대한 Read 시간으로단위는밀리초이다. average_wait 단일페이지에대한평균 Read 시간으로단위는밀리초이다. [TS11] 전체테이블스페이스상태 [back] SELECT name tbs_name, decode(state, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARD') state FROM v$tablespaces Disk Buffer Disk Buffer 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [DB01] 디스크버퍼 Hit Ratio [back] SELECT hit_ratio 'HIT_RATIO(%)' FROM v$buffpool_stat HIT_RATIO(M) ALTIBASE 구동이후부터 Disk Buffer 에이미적재된 page 가재사용되는비율을의미하는것으로이값이작을수록물리적인읽기 (read page) 횟수가많다는것이다. 물리적인읽기횟수가많으면, 시스템이빠른질의처리를못한다. 이컬럼은 (get_pages + fix_pages - read_pages) / (get_pages + fix_pages) 계산을수행한것이다. Object object 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. ALTIBASE 모니터링쿼리가이드 34 page of 43
[OB01] 메모리테이블 [back] SELECT a.user_name, b.table_name, d.name tablespace_name, c.table_oid, round((c.fixed_alloc_mem + c.var_alloc_mem)/(1024*1024),2) 'ALLOC(M)', round((c.fixed_used_mem + c.var_used_mem)/(1024*1024),2) 'USED(M)', round((c.fixed_used_mem + c.var_used_mem)/(c.fixed_alloc_mem + c.var_alloc_mem)*100,2) 'EFFICIENCY(%)' FROM system_.sys_users_ a, system_.sys_tables_ b, v$memtbl_info c, v$tablespaces d WHERE a.user_name <> 'SYSTEM_' and b.table_type = 'T' and a.user_id = b.user_id and b.table_oid = c.table_oid and b.tbs_id = d.id ALLOC(M) 테이블이할당받은페이지의합계이다. USED(M) EFFICIENCY(%) 테이블이할당받은페이지중에서 실제로데이터가적재된페이지 의합계이다. 예를들어, ALLOC 이 100M 크기인메모리테이블에전체 DELELE 를수행하면 ALLOC 은 100M 로변함없으나 USED 는 0 에가깝게된다. 테이블이소유한페이지중에서 실제로데이터가적재된페이지 에대한백분율로공간효율성을나타낸다. ( 즉, USED/ALLOC) [OB02] 큐 [back] SELECT a.user_name, b.table_name, d.name tablespace_name, c.table_oid, round((c.fixed_alloc_mem + c.var_alloc_mem)/(1024*1024),2) 'ALLOC(M)', round((c.fixed_used_mem + c.var_used_mem)/(1024*1024),2) 'USED(M)', round((c.fixed_used_mem + c.var_used_mem)/(c.fixed_alloc_mem + c.var_alloc_mem)*100,2) 'EFFICIENCY(%)' FROM system_.sys_users_ a, system_.sys_tables_ b, v$memtbl_info c, v$tablespaces d WHERE a.user_name <> 'SYSTEM_' and b.table_type = 'Q' and a.user_id = b.user_id and b.table_oid = c.table_oid and b.tbs_id = d.id [OB03] Efficiency 가낮은메모리테이블 ( 크기가 1024M, Efficiency 가 50% 이하 ) [back] SELECT c.user_name, b.table_name, round((fixed_alloc_mem+var_alloc_mem)/1024/1024, 2) as 'ALLOC(M)', round((((fixed_alloc_mem+var_alloc_mem)-(fixed_used_mem+var_used_mem))/1024/1024),2) 'FREE(M)', round((fixed_used_mem+var_used_mem)/(fixed_alloc_mem+var_alloc_mem+0.01)*100, 2) 'EFFICIENCY(%)' FROM v$memtbl_info a, system_.sys_tables_ b, system_.sys_users_ c WHERE a.table_oid = b.table_oid and b.user_id = c.user_id ALTIBASE 모니터링쿼리가이드 35 page of 43
and round((fixed_alloc_mem+var_alloc_mem)/1024/1024, 2) >= 1024 and round((fixed_used_mem+var_used_mem)/(fixed_alloc_mem+var_alloc_mem+0.01)*100, 2) <= 50 and b.user_id <> 1 ORDER BY 'FREE(M)' desc FREE(M) 테이블이할당받은페이지에서 실제로데이터가적재된페이지 를제외한페이지의합계 ( 즉, ALLOC-USED) [OB04] 메모리인덱스, 큐인덱스 [back] SELECT c.user_name, decode(f.table_type, 'Q', 'QUEUE', 'T', 'TABLE') object_type, table_name object_name, d.space_name tablespace_name, e.index_name, rpad(case2(e.index_type=1, 'b-tree', 'r-tree'),10,' ') index_type, '16 bytes * rowcount' 'ALLOC' FROM v$index b, system_.sys_users_ c, v$mem_tablespaces d, system_.sys_indices_ e, system_.sys_tables_ f WHERE b.index_id = e.index_id and e.user_id = c.user_id and f.user_id = e.user_id and f.tbs_id = d.space_id and f.table_oid = b.table_oid and c.user_name <> 'SYSTEM_' [OB05] 디스크인덱스 [back] SELECT user_name, table_name, d.name tbs_name, e.index_name, rpad(case2(e.index_type=1,'b-tree', 'R-TREE'),10,' ') index_type, round(d.extent_page_count*d.page_size*a.extent_total_count/1024/1024) 'ALLOC(M)' FROM v$segment a, v$index b, v$tablespaces d, system_.sys_users_ c, system_.sys_indices_ e, system_.sys_tables_ f WHERE a.segment_pid = b.index_seg_pid and b.index_id = e.index_id and e.user_id = c.user_id and a.space_id = d.id and f.tbs_id = d.id and a.segment_type='index' and f.user_id = e.user_id and f.table_oid = b.table_oid [OB06] 디스크테이블 [back] SELECT user_name, a.table_name, d.name tbs_name, a.table_oid, round((b.disk_page_cnt*8)/1024) 'ALLOC(M)' FROM system_.sys_tables_ a, v$disktbl_info b, ALTIBASE 모니터링쿼리가이드 36 page of 43
system_.sys_users_ c, v$tablespaces d WHERE a.table_oid = b.table_oid and a.user_id = c.user_id and a.tbs_id=d.id and c.user_name <> 'SYSTEM_' [OB07] 시퀀스 [back] SELECT user_name, table_name seq_name, min_seq min, current_seq, max_seq max, increment_seq increment, is_cycle, cache_size cache FROM v$seq a, system_.sys_users_ b, system_.sys_tables_ c WHERE a.seq_oid = c.table_oid and b.user_id = c.user_id and b.user_name <> 'SYSTEM_' [OB08] 시노님 [back] select nvl(user_name, 'PUBLIC') synonym_owner, synonym_name, object_owner_name object_owner, object_name, to_char(a.last_ddl_time, 'YYYY-MM-DD HH:MI:SS') last_ddl_time from system_.sys_synonyms_ a left outer join system_.sys_users_ b on a.synonym_owner_id = b.user_id and object_owner_name <> 'SYSTEM_' last_ddl_time 마지막으로 DDL 이수행된시간. [OB09] PSM( 프로시저 / 펑션 / 타입세트 ) [back] SELECT a.user_name, decode(object_type, 0, 'PROCEDURE', 1, 'FUNCTION', 3, 'TYPESET') psm_type, proc_name psm_name, decode(status, 0, 'VALID', 'INVALID') status, to_char(b.last_ddl_time, 'YYYY-MM-DD HH:MI:SS') last_ddl_time FROM system_.sys_users_ a, system_.sys_procedures_ b WHERE a.user_id = b.user_id and a.user_name <> 'SYSTEM_' status 컴파일상태를나타낸다. 1 이면컴파일이필요한상태이다. [OB10] PSM 생성구문 (PSM 이름을입력 ) [back] SELECT parse FROM system_.sys_proc_parse_ WHERE proc_oid = (SELECT proc_oid ALTIBASE 모니터링쿼리가이드 37 page of 43
FROM system_.sys_procedures_ WHERE proc_name = 'MY_PROC') ORDER BY seq_no [OB11] VIEW [back] SELECT a.user_name, b.table_name view_name, decode(status, 0, 'VALID', 'INVALID') status, to_char(b.last_ddl_time, 'YYYY-MM-DD HH:MI:SS') last_ddl_time FROM system_.sys_users_ a, system_.sys_tables_ b, system_.sys_views_ c WHERE a.user_id = b.user_id and b.table_id = c.view_id and b.table_type = 'V' [OB12] VIEW 생성구문 [back] SELECT parse FROM system_.sys_view_parse_ WHERE view_id = (SELECT table_id FROM system_.sys_tables_ WHERE table_name = 'MY_VIEW') ORDER BY seq_no Privileges Privileges 정보를확인하기위한각모니터링요소에대응하는쿼리는아래와같다. [PV01] 사용자시스템권한 [back] SELECT a.user_name grantee, c.user_name grantor, replace(d.priv_name, '_', ' ') priv_name FROM system_.sys_users_ a, system_.sys_grant_system_ b, system_.sys_users_ c, system_.sys_privileges_ d WHERE c.user_name <> 'SYSTEM_' and b.grantee_id = a.user_id and b.grantor_id = c.user_id and b.priv_id = d.priv_id grantee grantor priv_name 권한을부여받은사용자의이름 권한을부여해준사용자의이름 권한의이름 [PV02] 사용자오브젝트권한 [back] SELECT a.user_name grantee, c.user_name grantor, ALTIBASE 모니터링쿼리가이드 38 page of 43
f.user_name object_owner, e.table_name object_name, e.table_type object_type, replace(d.priv_name, '_', ' ') priv_name, decode(b.with_grant_option, 0, 'NO', 'YES') grantable FROM system_.sys_users_ a, system_.sys_grant_object_ b, system_.sys_users_ c, system_.sys_privileges_ d, system_.sys_tables_ e, system_.sys_users_ f WHERE c.user_name <> 'SYSTEM_' and b.grantee_id = a.user_id and b.grantor_id = c.user_id and b.priv_id = d.priv_id and b.obj_id = e.table_id and e.user_id = f.user_id ORDER BY grantee, grantor, object_owner, object_type, object_name, priv_name object_owner grantable 오브젝트를소유하는사용자의이름 오브젝트권한을부여받은사용자가다른사용자에게도해당권한을부여할수있는지를의미한다. 1 이면가능하고 0 이면불가능하다. Constraints constraints 정보를확인하기위한모니터링요소에대응하는쿼리는아래와같다. [CT01] 전체제약조건목록 [back] SELECT user_name, table_name object_name, decode(b.table_type, 'T', 'TABLE', 'Q', 'QUEUE', 'V', 'VIEW', 'SEQUENCE') object_type, c.constraint_name const_name, decode(c.constraint_type,0, 'FK', 1, 'NOT NULL', 2, 'UNIQUE', 3, 'PK', 4, 'NULL', 5, 'TIMESTAMP', 6, 'LOCAL UNIQUE') const_type, column_name FROM system_.sys_users_ a, system_.sys_tables_ b, system_.sys_constraints_ c, system_.sys_columns_ d, system_.sys_constraint_columns_ e WHERE a.user_id=c.user_id and b.table_id = c.table_id and a.user_id = d.user_id and a.user_id = e.user_id and b.table_id = d.table_id and b.table_id = e.table_id and c.constraint_id = e.constraint_id and d.column_id = e.column_id and a.user_name <> 'SYSTEM_' object_type const_type 오브젝트의타입. / TABLE(T), VIEW(V), SEQUENCE(S), QUEUE(Q) 제약조건타입. / FK(0),NOT NULL(1),UNIQUE(2),PK(3),NULL(4),TIMESTAMP(5),LOCAL UNIQUE(6) [CT02] PK, FK, UNIQUE 관련제약조건및테이블, 인덱스목록 [back] ALTIBASE 모니터링쿼리가이드 39 page of 43
SELECT a.user_name, b.table_name, decode(c.constraint_type,0, 'FK', 2, 'UNIQUE', 3, 'PK', 4, 'NULL') const_type, c.constraint_name const_name, decode(d.index_name,c.constraint_name,null,index_name) index_name, (select table_name from system_.sys_tables_ where table_id = c.referenced_table_id) r_table, (select index_name from system_.sys_indices_ where index_id = c.referenced_index_id) r_index FROM system_.sys_users_ a, system_.sys_tables_ b, system_.sys_constraints_ c left outer join system_.sys_indices_ d on c.index_id = d.index_id WHERE c.table_id = b.table_id and a.user_name <> 'SYSTEM_' and c.user_id = a.user_id and c.constraint_type in (3, 0, 2, 6) --PK, FK, UNIQUE, LOCAL UNIQUE ORDER BY table_name, const_type r_table 제약조건이 FK 의경우참조대상테이블의이름. r_index 제약조건이 FK 의경우참조대상인덱스의이름. [CT03] 복합인덱스컬럼구성목록 [back] SELECT d.user_name, c.table_name, b.index_name, e.column_name, a.index_col_order col_order, decode(a.sort_order, 'A', 'ASC', 'D', 'DESC') sort FROM system_.sys_index_columns_ a, system_.sys_indices_ b, system_.sys_tables_ c, system_.sys_users_ d, system_.sys_columns_ e WHERE d.user_name <> 'SYSTEM_' and c.table_type = 'T' and a.index_id = b.index_id and a.table_id = c.table_id and a.user_id = d.user_id and a.column_id = e.column_id ORDER BY user_name, table_name, index_name, col_order col_order 인덱스컬럼의구성순서로 0 부터시작한다. sort 인덱스컬럼의정렬형태. / ASC(A), DESC(D) [CT04] 인덱스정보요약 [back] SELECT a.user_name, c.index_name, c.index_id, b.table_name, nvl(d.name, 'SYS_TBS_MEMORY') tbs_name, c.is_unique, c.column_cnt FROM system_.sys_users_ a, system_.sys_tables_ b, system_.sys_indices_ c left outer join v$tablespaces d on c.tbs_id = d.id WHERE a.user_name <> 'SYSTEM_' and b.table_type = 'T' and c.table_id = b.table_id and c.user_id = a.user_id ALTIBASE 모니터링쿼리가이드 40 page of 43