조회 : 829
ALTIBASE 모니터링 쿼리 가이드
ALTIBASE 5.3.3 (5.1.5)
2010. 03.31
iSQL에서 내용을 보기좋게
set linesize 200; set colsize 30 ;
알티베이스에서 좀더 보기 쉽도록 포퍼먼스 뷰를 만든다.
출처 : 알티베이스 튜닝 교육
/** --테이블크기 정보 조회 TABLESPACE_NAME : 테이블스페이스명 TABLE_NAME : 테이블명 ALLOC : 테이블 ALLOC 크기(MB) USED : 테이블 사용량 크기(MB) **/ CREATE OR REPLACE view ADM_TBL_INFO AS SELECT A.NAME TABLESPACE_NAME, B.TABLE_NAME TABLE_NAME, TRUNC((C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM)/1024/1024,2) "ALLOC(MB)", TRUNC((C.FIXED_USED_MEM+C.VAR_USED_MEM)/1024/1024,2) "USED(MB)" FROM V$TABLESPACES A, SYSTEM_.SYS_TABLES_ B, V$MEMTBL_INFO C WHERE A.ID = C.TABLESPACE_ID AND B.TABLE_OID = C.TABLE_OID AND B.USER_ID != 1 AND B.TABLE_TYPE = 'T' UNION ALL SELECT A.NAME TABLESPACE_NAME, B.TABLE_NAME, TRUNC(C.DISK_TOTAL_PAGE_CNT*8/1024,2) ALLOC, TRUNC(C.DISK_PAGE_CNT*8/1024,2) USED FROM V$TABLESPACES A, V$DISKTBL_INFO C, SYSTEM_.SYS_TABLES_ B WHERE C.TABLE_OID = B.TABLE_OID AND A.ID = C.TABLESPACE_ID ORDER BY 1,2; /** --테이블스페이스 정보 조회 TBS_NAME : 테이블스페이스명 MAXSIZE(MB) : 테이블스페이스 최대크기 USEDSIZE(MB) : 테이블스페이스 사용량 CURRSIZE(MB) : 테이블스페이스 현재크기 **/ CREATE OR REPLACE view ADM_TBS AS SELECT RTRIM(NAME) AS TBS_NAME, MAXSIZE/1024/1024 AS 'MAXSIZE(MB)', USEDSIZE/1024/1024 AS 'USEDSIZE(MB)', CURRSIZE/1024/1024 AS 'CURRSIZE(MB)' FROM ( SELECT B.SPACEID, B.MAXSIZE, A.USEDSIZE, CURRSIZE FROM ( SELECT A.SPACE_ID, NVL(sum(B.ALLOC), 0) ALLOCSIZE, NVL(sum(B.USED), 0) USEDSIZE FROM V$MEM_TABLESPACES A LEFT JOIN ( SELECT A.TABLESPACE_ID, B.TABLE_NAME, SUM(A.FIXED_ALLOC_MEM+A.VAR_ALLOC_MEM) ALLOC, SUM(A.FIXED_USED_MEM+A.VAR_USED_MEM) USED FROM V$MEMTBL_INFO A, SYSTEM_.SYS_TABLES_ B WHERE A.TABLE_OID=B.TABLE_OID GROUP BY A.TABLESPACE_ID, B.TABLE_NAME ) B ON A.SPACE_ID=B.TABLESPACE_ID WHERE A.SPACE_ID != 0 group by a.space_id ) A, ( SELECT 1 SPACEID, MEM_MAX_DB_SIZE MAXSIZE, MEM_ALLOC_PAGE_COUNT * 32 * 1024 ALLOCSIZE, MEM_ALLOC_PAGE_COUNT * 32 * 1024 CURRSIZE FROM V$DATABASE ) B UNION ALL SELECT DF.SPACEID SPACEID, df.MAXSIZE, NVL(UF.USEDSIZE,'N/A') USEDSIZE, df.CURRSIZE FROM ( SELECT SPACEID, SUM(MAXSIZE) * 8 * 1024 MAXSIZE, SUM(CURRSIZE) * 8 * 1024 CURRSIZE FROM X$DATAFILES GROUP BY SPACEID ) DF, ( SELECT ID SPACEID, to_char(ALLOCATED_PAGE_COUNT * 8 * 1024) as USEDSIZE FROM v$TABLESPACES WHERE ID NOT IN ( SELECT SPACE_ID FROM V$MEM_TABLESPACES ) ) UF WHERE DF.SPACEID = UF.SPACEID ) TBS_SZ LEFT OUTER JOIN V$TABLESPACES TBS_INFO ON TBS_SZ.SPACEID = TBS_INFO.ID ORDER BY TBS_SZ.SPACEID; /** --USER 별 모든 OBJECT USER_NAME : USER 명 TABLE_NAME : OBJECT_TYPE : OBJECT 종류 **/ CREATE OR REPLACE view ADM_OBJECT AS SELECT Y.USER_NAME, X.TABLE_NAME, DECODE(X.TABLE_TYPE, 'T', 'TABLE', 'V', 'VIEW', 'S', 'SEQUENCE') OBJECT_TYPE FROM SYSTEM_.SYS_TABLES_ X INNER JOIN SYSTEM_.SYS_USERS_ Y ON X.USER_ID = Y.USER_ID WHERE Y.USER_ID > 1 UNION ALL SELECT Y.USER_NAME, X.PROC_NAME , 'PSM' FROM SYSTEM_.SYS_PROCEDURES_ X INNER JOIN SYSTEM_.SYS_USERS_ Y ON X.USER_ID = Y.USER_ID WHERE Y.USER_ID > 1 UNION ALL SELECT NVL(B.USER_NAME,'PUBLIC') USER_NAME, A.OBJECT_OWNER_NAME||'.'||OBJECT_NAME,'SYNONYMS' FROM SYSTEM_.SYS_SYNONYMS_ A LEFT OUTER JOIN SYSTEM_.SYS_USERS_ B ON A.SYNONYM_OWNER_ID = B.USER_ID WHERE OBJECT_OWNER_NAME NOT IN ('SYSTEM_') ORDER BY 1; /** --시노님 정보 조회 USER_NAME : 유저이름(PUBLIC, 유저) SYSNONYM_NAME : 시노님이름 OBJ_NAME : OBJECT 이름 **/ CREATE OR REPLACE view ADM_SYNONYM AS SELECT NVL(B.USER_NAME,'PUBLIC') AS USER_NAME, SYNONYM_NAME, A.OBJECT_OWNER_NAME||'.'||OBJECT_NAME AS OBJ_NAME FROM SYSTEM_.SYS_SYNONYMS_ A LEFT OUTER JOIN SYSTEM_.SYS_USERS_ B ON A.SYNONYM_OWNER_ID = B.USER_ID WHERE OBJECT_OWNER_NAME NOT IN ('SYSTEM_') ORDER BY 1; /** --리플리케이션 정보 조회 REPLICATION_NAME : 리플리케이션 명 XSN : Replication Sequence No REPGAP : Replication Gap REMOTE_REPL : Replication 대상 정보 RECEIVER_STAT : Reciever 상태 **/ CREATE OR REPLACE view ADM_REPLICATION AS SELECT A.REPLICATION_NAME, A.XSN, NVL(TO_CHAR(E.REP_GAP), 'N/A') AS REPGAP, D.HOST_IP || ' (' || D.PORT_NO || '):' || CASE2(TO_CHAR(B.PEER_PORT) = '', 'OFF', 'ON') AS REMOTE_REPL, CASE2(TO_CHAR(C.PEER_PORT) = '', 'OFF', 'ON') AS RECEIVER_STAT FROM ( SELECT REP_NAME, MAX(REP_GAP) AS REP_GAP FROM V$REPGAP GROUP BY REP_NAME ) E, SYSTEM_.SYS_REPL_HOSTS_ D , SYSTEM_.SYS_REPLICATIONS_ A LEFT OUTER JOIN V$REPSENDER B ON A.REPLICATION_NAME = B.REP_NAME LEFT OUTER JOIN V$REPRECEIVER C ON A.REPLICATION_NAME = C.REP_NAME WHERE D.REPLICATION_NAME = A.REPLICATION_NAME AND A.REPLICATION_NAME = E.REP_NAME ORDER BY A.REPLICATION_NAME; /** --시퀀스의 정보를 나타냄 SEQ_NAME : SEQUENCE 명 CURRENT_SEQ : 현재 SEQUENCE 번호 START_SEQ : SEQUENCE 시작 번호 INCREMENT_SEQ : 증가 번호 CACHE_SIZE : 캐시 MAX_SEQ : 최대 값 MIN_SEQ : 최소 값 IS_CYCLE : 순환 여부 **/ CREATE OR REPLACE view ADM_SEQUENCE AS SELECT C.USER_NAME||'.'||B.TABLE_NAME SEQ_NAME, A.CURRENT_SEQ, A.START_SEQ, A.INCREMENT_SEQ, A.CACHE_SIZE, A.MAX_SEQ, A.MIN_SEQ, A.IS_CYCLE FROM V$SEQ A, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C WHERE A.SEQ_OID = B.TABLE_OID AND B.USER_ID = C.USER_ID AND B.USER_ID != 1 ORDER BY SEQ_NAME; /** --전체 뷰에 대한 정보를 조회 VIEW_NAME : 뷰 명 VIEW_ID : View Object ID STATUS : View 의 상태 **/ CREATE OR REPLACE view ADM_VIEW AS SELECT C.USER_NAME||'.'||B.TABLE_NAME VIEW_NAME, A.VIEW_ID, DECODE(A.STATUS,0,'VALID','INVALID') STATUS FROM SYSTEM_.SYS_VIEWS_ A, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C WHERE A.VIEW_ID = B.TABLE_ID AND A.USER_ID=C.USER_ID ORDER BY VIEW_NAME; /** --인덱스에 속한 전체 컬럼명을 출력 TABLE_NAME : 테이블명 INDEX_NAME : 인덱스명 COLUMN_NAME : 컬럼명 COLUMN_ORDER : 해당 컬럼 Ordering 된 순서 **/ CREATE OR REPLACE view ADM_COL_INDEX AS SELECT D.USER_NAME||'.'||C.TABLE_NAME TABLE_NAME, B.INDEX_NAME, E.COLUMN_NAME, DECODE(SORT_ORDER,'A','ASC','D','DESC') COLUMN_ORDER FROM SYSTEM_.SYS_INDEX_COLUMNS_ A, SYSTEM_.SYS_INDICES_ B, SYSTEM_.SYS_TABLES_ C, SYSTEM_.SYS_USERS_ D, SYSTEM_.SYS_COLUMNS_ E WHERE 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 AND D.USER_ID != 1 ORDER BY TABLE_NAME, INDEX_NAME, INDEX_COL_ORDER; /** -- 모든 인덱스들의 대한 정보 INDEX_ID : Index ID TABLE_NAME : 해당 인덱스가 있는 테이블 명 TBS : Index의 Tablespace 명 UNIQUENESS : Unique Index 여부 COLUMN_COUNT : Column 갯수 **/ CREATE OR REPLACE view ADM_INDEX AS SELECT A.INDEX_ID, C.USER_NAME||'.'||B.TABLE_NAME TABLE_NAME, A.INDEX_NAME INDEX_NAME, NVL(D.NAME,'MEMORY') TBS, DECODE(IS_UNIQUE,'T','UNIQUE','NONUIQUE') UNIQUENESS, A.COLUMN_CNT COLUMN_COUNT FROM SYSTEM_.SYS_INDICES_ A LEFT OUTER JOIN V$TABLESPACES D ON A.TBS_ID = D.ID, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C WHERE A.TABLE_ID = B.TABLE_ID AND A.USER_ID = C.USER_ID AND C.USER_ID != 1 ORDER BY TABLE_NAME, INDEX_NAME ; /** -- Constraint 에 대한 조회 TABLE_NAME : Contraint 가 설정된 테이블 명 CONSTRAINT_NAME : Constraint 명 CON_TYPE : Contraint 종류 INDEX_NAME : 해당 Constraint 가 index 존재를 필요로 할 경우 해당 Index 명 REFERENCE_TABLE : Constraint 가 FK 일 경우 참조하는 테이블 명 REFERENCE_INDEX : Constraint 가 FK 일 경우 참조하는 인덱스 명 **/ CREATE OR REPLACE view ADM_CONSTRAINT AS SELECT C.USER_NAME||'.'||B.TABLE_NAME TABLE_NAME, A.CONSTRAINT_NAME, DECODE(A.CONSTRAINT_TYPE,0,'FOREIGNKEY', 1,'NOTNULL', 2,'UNIQUE', 3,'PRIMARYKEY', 4,'NULL', 5,'TIMESTAMP','UNKNOWN') CON_TYPE, D.INDEX_NAME, (SELECT TABLE_NAME FROM SYSTEM_.SYS_TABLES_ IT WHERE IT.TABLE_ID = A.REFERENCED_TABLE_ID) REFERENCE_TABLE, (SELECT INDEX_NAME FROM SYSTEM_.SYS_INDICES_ II WHERE II.INDEX_ID = A.REFERENCED_INDEX_ID) REFERENCE_INDEX FROM SYSTEM_.SYS_CONSTRAINTS_ A LEFT OUTER JOIN SYSTEM_.SYS_INDICES_ D ON A.INDEX_ID = D.INDEX_ID , SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C WHERE A.TABLE_ID = B.TABLE_ID AND A.USER_ID = C.USER_ID ORDER BY TABLE_NAME ,CON_TYPE; /** -- 현재 Altibase 에 접속된 세션의 정보를 출력합니다. ID : Session ID CONN_INFO : 접속된 세션의 정보 ACTIVE : Action 여부를 판단합니다. OPEN_STMT : 해당 세션이 연 모든 Statment 갯수 CURRENT_STMT : 현재 수행 중인 Statement ID COMMIT_MODE : Commit 모드 **/ CREATE OR REPLACE view ADM_SESSION AS SELECT ID, REPLACE2(REPLACE2(COMM_NAME,'SOCKET-',NULL),'-SERVER',NULL) CONN_INFO, DECODE(ACTIVE_FLAG,1,'ACTIVE','INACTIVE') ACTIVE, OPENED_STMT_COUNT OPEN_STMT, CURRENT_STMT_ID CURRENT_STMT, CLIENT_PID, DECODE(AUTOCOMMIT_FLAG,0,'Non AutoCommit','AutoCommit') COMMIT_MODE FROM V$SESSION; /** -- LOCK 정보 조회 SESSION_ID : 세션 ID TX_ID : 트렌젝션 ID TBL_INFO : 테이블 정보 LOCK_ITEM_TYPE : LOCK 상태 STATE : 쿼리 상태 QUERY : 쿼리 내역 **/ CREATE OR REPLACE view ADM_LOCK AS SELECT A.SESSION_ID AS SESSION_ID, A.TX_ID AS TX_ID, DECODE(LOCK_ITEM_TYPE,'TBS',B.NAME,'TBL',C.TABLE_NAME,DBF_ID) TBL_INFO , LOCK_DESC AS LOCK_ITEM_TYPE, DECODE(A.STATE,0,'ALLOC',1,'PREPARED',2,'FETCH-READY',4,'FETCHING','UNKNOWN') AS STATE, RPAD(QUERY,120) AS QUERY FROM V$LOCK_STATEMENT A LEFT OUTER JOIN V$TABLESPACES B ON A.TBS_ID=B.ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ C ON A.TABLE_OID=C.TABLE_OID ORDER BY SESSION_ID; /** -- PROCEDURE 정보 조회 USER_NAME : 유저이름 PROC_NAME : procedure 이름 OBJECT_TYPE : 타입(PROCEDURE,FUNCTION,TYPE_SET) STATUS : 상태 **/ CREATE OR REPLACE view ADM_PROC AS SELECT B.USER_NAME, A.PROC_NAME, DECODE(A.OBJECT_TYPE,0,'PROCEDURE',1,'FUNCTION','TYPE_SET') AS TYPE, DECODE(A.STATUS,0,'VALID','INVALID') AS STATUS FROM SYSTEM_.SYS_PROCEDURES_ A, SYSTEM_.SYS_USERS_ B WHERE A.USER_ID=B.USER_ID AND B.USER_NAME <> 'SYSTEM_'; /** -- TRIGGER 정보 조회 USER_NAME : 유저이름 TRIGGER_NAME : 트리거 이름 TABLE_NAME : 테이블 이름 ENABLE : 상태 EVENT_TIME : 이벤트 시간 EVENT_TYPE : 이벤트 타입 GRANULARITY : 이벤트 조건 **/ CREATE OR REPLACE view ADM_TRIGGER AS SELECT B.USER_NAME, A.TRIGGER_NAME, C.TABLE_NAME, DECODE(A.IS_ENABLE,0,'DISABLE','ENABLE') AS ENABLE, DECODE(A.EVENT_TIME,1,'BEFORE','AFTER') AS EVENT_TIME, DECODE(A.EVENT_TYPE,1,'INSERT',2,'DELETE',3,'UPDATE') AS EVENT_TYPE, DECODE(A.GRANULARITY,1,'FOR EACH ROW',2,'FOR EACH STATEMENT') AS GRANULARITY FROM SYSTEM_.SYS_TRIGGERS_ A, SYSTEM_.SYS_USERS_ B, SYSTEM_.SYS_TABLES_ C WHERE A.USER_ID=B.USER_ID AND C.TABLE_ID=A.TABLE_ID ;
http://newhyuki.egloos.com/1957158
알티베이스 메모리 사이즈 부분별로 보기 (v$memstat) -> SELECT * FROM V$MEMSTAT ORDER By 4 DESC; ------------------------------------------------------------------------------------------------------------------ 메모리 테이블 별 사이즈 보기. -> SELECT A.TABLE_OID TABLEOID, C.USER_NAME||'.'||B.TABLE_NAME TABLENAME, A.MEM_SLOT_SIZE SLOTSIZE, FIXED_ALLOC_MEM+VAR_ALLOC_MEM ALLOCBYTE, FIXED_USED_MEM+VAR_USED_MEM USEDBYTE, ROUND ((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM)*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 AND C.USER_ID != 1 AND B.TABLE_TYPE != 'V' AND B.TABLE_TYPE = 'T' ORDER BY TABLENAME;
슬로우 쿼리 확인 쿼리
set vertical off;
set colsize 40;
set linesize 1024;
select to_char(sysdate, '[HH:MI:SS]') time,
round(TOTAL_TIME/1000000, 2) TOT_SEC,
round(EXECUTE_TIME/1000000, 2) EXE_SEC, session_id, id, --tx_id,
replace(replace(replace(substring(query, 1,60) , chr(10), ' '),chr(13), ' '),chr(9),' ') AS QUERY
from v$statement
where execute_flag = 1 and session_id <> session_id()
--and total_time/1000000 > 1
order by 1 desc
limit 40
;
위 쿼리문으로 아래 쿼리가 동시에 여럿 수행되고 수행 시간도 오래 걸리는 것을 확인하였습니다.
TIME TOT_SEC EXE_SEC SESSION_ID ID QUERY
----------------------------------------------------------------------------------------------------------------------------------
[21:22:34] 6.08 0 6157 12 {해당쿼리 앞부분(잘려서 보임)}
현재시간 총시간(초) 실행시간(초) 세션아이디 아이디 쿼리앞부분
이중화 대상 테이블에 DDL 실행
제목 | 작성자 | 날짜 |
---|---|---|
공대여자 | ||
공대여자 | ||
mins01 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 | ||
공대여자 |