다른 명령
인덱스 정보
INDEX 보기
- USER_INDEXES
- USER_IND_COLUMNS
SELECT A.INDEX_NAME , A.UNIQUENESS , TO_CHAR(COLUMN_POSITION, '999') AS POS , SUBSTRB(COLUMN_NAME, 1, 33) AS COLUMN_NAME FROM USER_INDEXES A , USER_IND_COLUMNS B WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER = UPPER('&OWNER명') AND A.TABLE_NAME = UPPER('&테이블명') ORDER BY 1, 3;
전체 INDEX 보기
- DBA_INDEXES
- DBA_IND_COLUMNS
SELECT SUBSTRB(A.TABLE_NAME, 1, 22) AS TABLE_NAME , SUBSTRB(A.INDEX_NAME, 1, 23) AS INDEX_NAME , SUBSTRB(A.UNIQUENESS, 1, 7) AS UNIQUE , TO_CHAR(COLUMN_POSITION, '999') AS POS , SUBSTRB(COLUMN_NAME, 1, 20) AS COLUMN_NAME FROM DBA_INDEXES A , DBA_IND_COLUMNS B WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_OWNER = B.TABLE_OWNER AND A.TABLE_OWNER = '&OWNER명' ORDER BY 1, 2, 3;
특정 테이블의 인덱스 확인
- ALL_IND_COLUMNS
/* 인덱스를 확인 하고자 할때 사용하는 쿼리 */ SELECT C.TABLE_NAME , C.INDEX_NAME , C.COLUMN_NAME , C.COLUMN_POSITION , T.NUM_ROWS FROM ALL_IND_COLUMNS C , (SELECT TABLE_NAME, NUM_ROWS FROM ALL_TABLES WHERE OWNER = 'ESTDBA' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE:IN_TABLE_NAME || '%' ) AND NUM_ROWS > 0 ) T WHERE C.TABLE_NAME = T.TABLE_NAME ORDER BY T.NUM_ROWS DESC, C.TABLE_NAME, C.INDEX_NAME, C.COLUMN_POSITION;
인덱스에 대한 컬럼 조회
- USER_IND_COLUMNS
SELECT TABLE_NAME , INDEX_NAME , COLUMN_POSITION , COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
PRIMARY KEY 재생성 방법
--- PRIMARY KEY DROP ALTER TABLE EMP DROP PRIMARY KEY; -- PRIMARY KEY 생성 ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO) USING INDEX STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE USERS;
PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기
- DBA_OBJECTS
- CDEF$
- CON$
SELECT C.NAME CONSTRAINT_NAME FROM DBA_OBJECTS A , CDEF$ B , CON$ C WHERE A.OBJECT_NAME = UPPER('&테이블명') AND A.OBJECT_ID = B.ROBJ# AND B.CON# = C.CON#;
중복인덱스 체크
- SYS.ICOL$
- SYS.IND$
- SYS.OBJ$
- SYS.USER$
SELECT O1.NAME || '.' || N1.NAME REDUNDANT_INDEX , O2.NAME || '.' || N2.NAME SUFFICIENT_INDEX FROM SYS.ICOL$ IC1 , SYS.ICOL$ IC2 , SYS.IND$ I1 , SYS.OBJ$ N1 , SYS.OBJ$ N2 , SYS.USER$ O1 , SYS.USER$ O2 WHERE IC1.POS# = 1 AND IC2.BO# = IC1.BO# AND IC2.OBJ# != IC1.OBJ# AND IC2.POS# = 1 AND IC2.INTCOL# = IC1.INTCOL# AND I1.OBJ# = IC1.OBJ# AND BITAND (I1.PROPERTY, 1) = 0 AND (SELECT MAX (POS#) * (MAX (POS#) + 1) / 2 FROM SYS.ICOL$ WHERE OBJ# = IC1.OBJ#) = (SELECT SUM (XC1.POS#) FROM SYS.ICOL$ XC1, SYS.ICOL$ XC2 WHERE XC1.OBJ# = IC1.OBJ# AND XC2.OBJ# = IC2.OBJ# AND XC1.POS# = XC2.POS# AND XC1.INTCOL# = XC2.INTCOL#) AND N1.OBJ# = IC1.OBJ# AND N2.OBJ# = IC2.OBJ# AND O1.USER# = N1.OWNER# AND O2.USER# = N2.OWNER#;
또는
select /*+ rule */ a.table_owner, a.table_name, a.index_owner, a.index_name, column_name_list, column_name_list_dup, dup duplicate_indexes, i.uniqueness, i.partitioned, i.leaf_blocks, i.distinct_keys, i.num_rows, i.clustering_factor from ( select table_owner, table_name, index_owner, index_name, column_name_list_dup, dup, max(dup) OVER (partition by table_owner, table_name, index_name) dup_mx from ( select table_owner, table_name, index_owner, index_name, substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list_dup, dup from ( select index_owner, index_name, table_owner, table_name, column_name, count(1) OVER (partition by index_owner, index_name) cnt, ROW_NUMBER () OVER (partition by index_owner, index_name order by column_position) as seq, count(1) OVER (partition by table_owner, table_name, column_name, column_position) as dup from sys.dba_ind_columns where index_owner not in ('SYS', 'SYSTEM')) where dup!=1 start with seq=1 connect by prior seq+1=seq and prior index_owner=index_owner and prior index_name=index_name )) a, ( select table_owner, table_name, index_owner, index_name, substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list from ( select index_owner, index_name, table_owner, table_name, column_name, count(1) OVER ( partition by index_owner, index_name) cnt, ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq from sys.dba_ind_columns where index_owner not in ('SYS', 'SYSTEM')) where seq=cnt start with seq=1 connect by prior seq+1=seq and prior index_owner=index_owner and prior index_name=index_name ) b, dba_indexes i where a.dup=a.dup_mx and a.index_owner=b.index_owner and a.index_name=b.index_name and a.index_owner=i.owner and a.index_name=i.index_name order by a.table_owner, a.table_name, column_name_list_dup;
테이블의 PK를 구성하는 컬럼 조회
- USER_TABLES
- USER_CONSTRAINTS
- USER_CONS_COLUMNS
SELECT A.TABLE_NAME, B.CONSTRAINT_NAME, C.COLUMN_NAME FROM USER_TABLES A , USER_CONSTRAINTS B , USER_CONS_COLUMNS C WHERE A.TABLE_NAME = B.TABLE_NAME AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE = 'P';
Index가 없는 Table 조회
- DBA_TABLES
- DBA_INDEXES
SELECT OWNER, TABLE_NAME FROM (SELECT OWNER , TABLE_NAME FROM DBA_TABLES MINUS SELECT TABLE_OWNER , TABLE_NAME FROM DBA_INDEXES ) WHERE OWNER NOT IN ('SYS', 'SYSTEM') ORDER BY OWNER, TABLE_NAME;
락 정보
테이블에 LOCK이 걸렸는지를 보기
- V$SESSION
- V$LOCK
- DBA_OBJECTS
SELECT A.SID , A.SERIAL# , SUBSTRB(A.USERNAME, 1, 16) AS USERNAME , SUBSTRB(A.MACHINE, 1, 30) AS MACHINE , A.TERMINAL , A.OSUSER , A.PROGRAM , SUBSTRB(TO_CHAR(A.LOGON_TIME, 'MM/DD HH24:MI:SS'), 1, 14) AS LOGON_TIME , SUBSTRB(C.OBJECT_NAME, 1, 58) AS OBJECT_NAME FROM V$SESSION A , V$LOCK B , DBA_OBJECTS C WHERE A.SID = B.SID AND B.ID1 = C.OBJECT_ID AND B.TYPE = 'TM' AND C.OBJECT_NAME LIKE UPPER('&테이블명');
Lock을 잡고있는 세션과 기다리는 세션 조회
- V$SESSION
- V$LOCK
- DBA_OBJECTS
SELECT DECODE(B.LOCKWAIT, NULL, ' ', 'w') AS WW , B.SID , B.SERIAL# AS SER# , SUBSTR(B.MACHINE, 1, 10) AS MACHINE , SUBSTR(B.PROGRAM, 1, 15) AS PROGRAM , SUBSTR(A.OBJECT_NAME, 1, 17) AS OBJ_NAME , SUBSTR(B.STATUS, 1, 1) AS S , DECODE(B.COMMAND, 0, NULL, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', B.COMMAND) AS SQLCMD , B.PROCESS AS PGM_PSS FROM V$SESSION B , ( SELECT A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) AS OBJECT_NAME FROM V$LOCK A , DBA_OBJECTS B WHERE A.ID1 = B.OBJECT_ID(+) GROUP BY A.SID, DECODE(B.OWNER, NULL, A.TYPE || '..ing', B.OWNER || '.' || B.OBJECT_NAME) ) A WHERE B.SID = A.SID AND B.TADDR IS NOT NULL;
테이블에 걸린 비정상적 LOCK 풀기
ALTER SYSTEM KILL SESSION '&SID,&SERIAL';
- RAC
ALTER SYSTEM KILL SESSION '&SID,&SERIAL,@1';
작업 중인 데이터베이스 트랜잭션 조회
- V$SESSION
- V$TRANSACTION
- DBA_ROLLBACK_SEGS
SELECT S.SID , S.SERIAL# , S.STATUS , S.OSUSER , S.USERNAME , T.STATUS , T.START_TIME FROM V$SESSION S , V$TRANSACTION T , DBA_ROLLBACK_SEGS R WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.SEGMENT_ID;
열려 있는 커서 조회
- V$SESSION
- V$OPEN_CURSOR
SELECT A.SID , A.OSUSER , COUNT(B.SID) AS "CURSOR" , A.PROGRAM , A.STATUS FROM V$SESSION A , V$OPEN_CURSOR B WHERE A.SID = B.SID(+) GROUP BY A.SID, A.OSUSER, A.PROGRAM, A.STATUS;
LOCK 발생 유형 조회
- V$LOCK
SELECT A.SID ,DECODE(A.TYPE ,'MR', 'MEDIA RECOVERY' ,'RT', 'REDO THREAD' ,'UN', 'USER_NAME' ,'TX', 'TRANSACTION' ,'TM', 'DML' ,'UL', 'PL/SQL USER LOCK' ,'DX', 'DISTRIBUTED XACTION' ,'CF', 'CONTROL FILE' ,'IS', 'INSTANCE STATE' ,'FS', 'FILE SET' ,'IR', 'INSTANCE RECOVERY' ,'FS', 'FILE SET' ,'ST', 'DISK SPACE TRANSACTION' ,'TS', 'TEMP SEGMENT' ,'IV', 'LIBRARY CACHE INVAILDATION' ,'LS', 'LOG START OR SWITCH' ,'RW', 'ROW WAIT' ,'SQ', 'SEQUENCE NUMBER' ,'TE', 'EXTEND TABLE' ,'TT', 'TEMP TABLE' ,A.TYPE ) AS "LOCK_TYPE" ,DECODE(A.LMODE ,0, 'NONE' ,1, 'NULL' ,2, 'ROW-S(SS)' ,3, 'ROW-X(SX)' ,4, 'SHARE' ,5, 'S/ROW-X(SSX)' ,6, 'EXCLUSIVE' ,TO_CHAR(A.LMODE) ) AS "MODE_HELD" ,DECODE(A.REQUEST ,0, 'NONE' ,1, 'NULL' ,2, 'ROW-S(SS)' ,3, 'ROW-X(SX)' ,4, 'SHARE' ,5, 'S/ROW-X(SSX)' ,6, 'EXCLUSIVE' ,TO_CHAR(A.REQUEST) ) AS "MODE_REQUESTED" ,TO_CHAR(A.ID1) AS "LOCK_ID1" ,TO_CHAR(A.ID2) AS "LOCK_ID2" ,DECODE(BLOCK, 0, 'NOT BLOCKING', 1, 'BLOCKING', 2, 'GLOBAL', TO_CHAR(BLOCK)) AS "BLOCKING_OTHERS" FROM V$LOCK A WHERE (ID1, ID2) IN (SELECT B.ID1, ID2 FROM V$LOCK B WHERE B.ID1 = A.ID1);
LOCK 상태 오브젝트 조회
- V$LOCKED_OBJECT
- V$SESSION
- DBA_OBJECT
SELECT A.SESSION_ID , B.SERIAL# , A.OS_USER_NAME , A.ORACLE_USERNAME , C.OBJECT_NAME , A.LOCKED_MODE , A.XIDUSN FROM V$LOCKED_OBJECT A , V$SESSION B , DBA_OBJECTS C WHERE A.OBJECT_ID = C.OBJECT_ID AND A.SESSION_ID = B.SID;
LOCK 걸린 SQL 구문 조회
- V$SQLTEXT
- V$SESSION
- V$ACCESS
SELECT B.USERNAME AS USERNAME , C.SID AS SID , C.OWNER AS OBJECT_OWNER , C.OBJECT AS OBJECT , B.LOCKWAIT , A.PIECE , A.SQL_TEXT AS SQL FROM V$SQLTEXT A , V$SESSION B , V$ACCESS C WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = C.SID AND C.OWNER!= 'SYS';
해당 테이블의 세션을 제거하는 쿼리
- V$LOCK
- DBA_OBJECTS
- V$SESSION
SELECT 'ALTER SYSTEM KILL SESSION '''|| S.SID||','||S.SERIAL# ||''';' FROM V$LOCK L, DBA_OBJECTS O, V$SESSION S WHERE L.ID1 = O.OBJECT_ID AND S.SID = L.SID AND O.OWNER = 'ESTDBA' AND O.OBJECT_NAME = 'TMP_GSYM2'
Blocking Lock Session 확인
- V$SQLTEXT
- V$SESSION
- V$ACCESS
SELECT B.BLOCKING_SESSION AS BLOCKING_SESSION_SID , C.SID AS LOCK_SESSION_SID , C.OWNER AS OBJECT_OWNER , C.OBJECT AS OBJECT , B.LOCKWAIT , A.PIECE , A.SQL_TEXT AS SQL FROM V$SQLTEXT A , V$SESSION B , V$ACCESS C WHERE A.ADDRESS = B.SQL_ADDRESS AND A.HASH_VALUE = B.SQL_HASH_VALUE AND B.SID = C.SID AND B.BLOCKING_SESSION IS NOT NULL AND C.OWNER NOT IN ('SYS', 'PUBLIC') AND C.OBJECT NOT IN ('TOAD_PLAN_TABLE') ORDER BY A.PIECE;
테이블 정보
테이블 생성일자 보기
- USER_OBJECTS
SELECT SUBSTRB(OBJECT_NAME, 1, 15) AS OBJECT_NAME , CREATED , LAST_DDL_TIME , TIMESTAMP , STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER('&테이블명') AND OBJECT_TYPE = 'TABLE';
테이블의 크기 및 블록 보기
- USER_SEGMENTS
SELECT SUBSTR(SEGMENT_NAME, 1, 20) , BYTES , BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('&테이블명');
파티션 테이블의 파티션 범위 보기
- USER_TAB_PARTITIONS
SELECT SUBSTRB(PARTITION_NAME, 1, 30) AS PARTITION_NAME, SUBSTRB(TABLESPACE_NAME, 1, 30) AS TABLESPACE_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = UPPER('&테이블명');
CONSTRAINT 보기
- DBA_CONSTRAINTS
- DBA_CONS_COLUMNS
SELECT DECODE(A.CONSTRAINT_TYPE, 'P', 'Primary Key', 'R', 'Foreign Key', 'C', 'Table Check', 'V', 'View Check', 'U', 'Unique', '?') AS "유형" , SUBSTRB(A.CONSTRAINT_NAME, 1, 25) AS CONSTRAINT_NAME , B.POSITION , SUBSTRB(B.COLUMN_NAME, 1, 25) AS COLUMN_NAME FROM DBA_CONSTRAINTS A , DBA_CONS_COLUMNS B WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER = 'E_LUCIS' AND A.TABLE_NAME = UPPER('&테이블명') ORDER BY 1, 2, 3;
특정 테이블의 스키마 구조 확인
DBA 용
- DBA_TAB_COLUMNS
- DBA_COL_COMMENTS
- DBA_CONS_COLUMNS
SELECT OWNER , TABLE_NAME , COLUMN_NAME , PK , COLUMN_NAME , DATA_TYPE || '( ' || NVL(DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE , NULLABLE , COMMENTS FROM (SELECT A.OWNER , A.TABLE_NAME , A.COLUMN_ID , B.POSITION PK , A.COLUMN_NAME , A.DATA_TYPE , A.DATA_PRECISION || DECODE(A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2 , A.DATA_LENGTH , A.DATA_PRECISION , A.DATA_SCALE , A.NULLABLE , A.COMMENTS , ROW_NUMBER() OVER (PARTITION BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN FROM (SELECT COL.OWNER , COL.TABLE_NAME , COL.COLUMN_ID , COL.COLUMN_NAME , COL.DATA_TYPE , COL.DATA_LENGTH , COL.DATA_PRECISION , COL.DATA_SCALE , COL.NULLABLE , COM.COMMENTS FROM DBA_TAB_COLUMNS COL , DBA_COL_COMMENTS COM WHERE COL.COLUMN_NAME = COM.COLUMN_NAME AND COL.OWNER = COM.OWNER AND COL.TABLE_NAME = COM.TABLE_NAME AND COM.OWNER =:IN_OWNER AND COM.TABLE_NAME LIKE:IN_TABLE_NAME || '%' ) A , DBA_CONS_COLUMNS B WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
일반 사용자 용
SELECT TABLE_NAME , COLUMN_NAME , PK , COLUMN_NAME , DATA_TYPE || '( ' || NVL (DATA_TYPE_2, DATA_LENGTH) || ' )' DATA_TYPE , NULLABLE , COMMENTS FROM (SELECT A.TABLE_NAME , A.COLUMN_ID , B.POSITION PK , A.COLUMN_NAME , A.DATA_TYPE , A.DATA_PRECISION || DECODE (A.DATA_SCALE, NULL, NULL, ',' || A.DATA_SCALE) DATA_TYPE_2 , A.DATA_LENGTH , A.DATA_PRECISION , A.DATA_SCALE , A.NULLABLE , A.COMMENTS , ROW_NUMBER () OVER (PARTITION BY A.TABLE_NAME, A.COLUMN_ID ORDER BY A.COLUMN_ID, B.POSITION) RN FROM (SELECT COL.TABLE_NAME , COL.COLUMN_ID , COL.COLUMN_NAME , COL.DATA_TYPE , COL.DATA_LENGTH , COL.DATA_PRECISION , COL.DATA_SCALE , COL.NULLABLE , COM.COMMENTS FROM USER_TAB_COLUMNS COL, USER_COL_COMMENTS COM WHERE COL.COLUMN_NAME = COM.COLUMN_NAME AND COL.TABLE_NAME = COM.TABLE_NAME AND COM.TABLE_NAME LIKE :IN_TABLE_NAME || '%') A , USER_CONS_COLUMNS B WHERE B.TABLE_NAME(+) = A.TABLE_NAME AND B.COLUMN_NAME(+) = A.COLUMN_NAME) X WHERE X.RN = 1 ORDER BY X.TABLE_NAME, X.COLUMN_ID;
사용자별 오브젝트 수
- DBA_OBJECTS
SELECT OWNER AS "OWNER" , SUM (DECODE (OBJECT_TYPE, 'TABLE', 1, 0)) AS "TABLE" , SUM (DECODE (OBJECT_TYPE, 'INDEX', 1, 0)) AS "INDEX" , SUM (DECODE (OBJECT_TYPE, 'SYNONYM', 1, 0)) AS "SYNONYMS" , SUM (DECODE (OBJECT_TYPE, 'SEQUENCE', 1, 0)) AS "SEQUENCES" , SUM (DECODE (OBJECT_TYPE, 'VIEW', 1, 0)) AS "VIEWS" , SUM (DECODE (OBJECT_TYPE, 'CLUSTER', 1, 0)) AS "CLUSTERS" , SUM (DECODE (OBJECT_TYPE, 'DATABASE LINK', 1, 0)) AS "DBLINKS" , SUM (DECODE (OBJECT_TYPE, 'PACKAGE', 1, 0)) AS "PACKAGES" , SUM (DECODE (OBJECT_TYPE, 'PACKAGE BODY', 1, 0)) AS "PACKAGE_BODY" , SUM (DECODE (OBJECT_TYPE, 'PROCEDURE', 1, 0)) AS "PROCEDURES" , SUM (DECODE (OBJECT_TYPE, 'FUNCTION', 1, 0)) AS "FUNCTION" FROM DBA_OBJECTS GROUP BY OWNER;
모니터링
연결되어 있는 OS 사용자 및 프로그램 조회
- V$SESSION
SELECT SID , SERIAL# , OSUSER , SUBSTRB(USERNAME, 1, 10) AS USER_NAME , SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME , STATUS , TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME FROM V$SESSION WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;
1시간 이상 유휴 상태인 세션
SELECT SID , SERIAL# , USERNAME , TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET FROM V$SESSION WHERE LAST_CALL_ET > 3600 AND USERNAME IS NOT NULL;
Active Session 중 Idle Time이 긴 작업
- V$SESSION
- V$PROCESS
SELECT VS.SID || ',' || VS.SERIAL# " SID" , VP.SPID , VS.MACHINE , VS.PROGRAM , VS.MODULE , VS.STATUS , TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME , ROUND(VS.LAST_CALL_ET / 60) "IDLE" FROM V$SESSION VS , V$PROCESS VP WHERE VS.STATUS = 'ACTIVE' AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7) AND VS.PADDR = VP.ADDR ORDER BY 8;
DBUser 별로 Session 정보를 조회
SELECT S.USERNAME , S.SID , S.SERIAL# , P.SPID , S.OSUSER , S.MACHINE , S.PROGRAM , TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME" , ROUND(S.LAST_CALL_ET / 60) "IDLE" FROM V$SESSION S , V$PROCESS P WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%') ORDER BY 9;
Session별 사용 명령어
- V$SESSION
- V$SESSTAT
- V$STATNAME
- V$PROCESS
SELECT SESS.SID ,SESS.SERIAL# ,SUBSTR(SESS.USERNAME, 1, 10) "USER NAME" ,SUBSTR(OSUSER, 1, 11) "OS USER" ,SUBSTR(SESS.MACHINE, 1, 15) "MACHINE NAME" ,STATUS ,UPPER( DECODE(NVL(COMMAND, 0) ,0, '---' ,1, 'CREATE TABLE' ,2, 'INSERT -' ,3, 'SELECT -' ,4, 'CREATE CLUST' ,5, 'ALTER CLUST' ,6, 'UPDATE -' ,7, 'DELETE -' ,8, 'DROP -' ,9, 'CREATE INDEX' ,10, 'DROP INDEX' ,11, 'ALTER INDEX' ,12, 'DROP TABLE' ,13, 'CREATE SEQ' ,14, 'ALTER SEQ' ,15, 'ALTER TABLE' ,16, 'DROP SEQ' ,17, 'GRANT' ,18, 'REVOKE' ,19, 'CREATE SYN' ,20, 'DROP SYN' ,21, 'CREATE VIEW' ,22, 'DROP VIEW' ,23, 'VALIDATE IX' ,24, 'CREATE PROC' ,25, 'ALTER PROC' ,26, 'LOCK TABLE' ,27, 'NO OPERATION' ,28, 'RENAME' ,29, 'COMMENT' ,30, 'AUDIT' ,31, 'NOAUDIT' ,32, 'CREATE DBLINK' ,33, 'DROP DB LINK' ,34, 'CREATE DATABASE' ,35, 'ALTER DATABASE' ,36, 'CREATE RBS' ,37, 'ALTER RBS' ,38, 'DROP RBS' ,39, 'CREATE TABLESPACE' ,40, 'ALTER TABLESPACE' ,41, 'DROP TABLESPACE' ,42, 'ALTER SESSION' ,43, 'ALTER USER' ,44, 'COMMIT' ,45, 'ROLLBACK' ,47, 'PL/SQL EXEC' ,48, 'SET TRANSACTION' ,49, 'SWITCH LOG' ,50, 'EXPLAIN' ,51, 'CREATE USER' ,52, 'CREATE ROLE' ,53, 'DROP USER' ,54, 'DROP ROLE' ,55, 'SET ROLE' ,56, 'CREATE SCHEMA' ,58, 'ALTER TRACING' ,59, 'CREATE TRIGGER' ,61, 'DROP TRIGGER' ,62, 'ANALYZE TABLE' ,63, 'ANALYZE INDEX' ,69, 'DROP PROCEDURE' ,71, 'CREATE SNAP LOG' ,72, 'ALTER SNAP LOG' ,73, 'DROP SNAP LOG' ,74, 'CREATE SNAPSHOT' ,75, 'ALTER SNAPSHOT' ,76, 'DROP SNAPSHOT' ,85, 'TRUNCATE TABLE' ,88, 'ALTER VIEW' ,91, 'CREATE FUNCTION' ,92, 'ALTER FUNCTION' ,93, 'DROP FUNCTION' ,94, 'CREATE PACKAGE' ,95, 'ALTER PACKAGE' ,96, 'DROP PACKAGE' ,46, 'SAVEPOINT' ) ) COMMAND ,SESS.PROCESS "C.PROC" ,PROC.SPID "S.PROC" ,TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI') FROM V$SESSION SESS , V$SESSTAT STAT , V$STATNAME NAME , V$PROCESS PROC WHERE SESS.SID = STAT.SID AND STAT.STATISTIC# = NAME.STATISTIC# AND SESS.USERNAME IS NOT NULL AND NAME.NAME = 'RECURSIVE CALLS' AND SESS.PADDR = PROC.ADDR ORDER BY 3, 1, 2;
사용자 session 2시간 이상 idle 상태가 지속되는 session kill
- V$SESSION
- V$PROCESS
SET PAGESIZE 0 SPOOL KILLIDLE3.SQL SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' FROM V$SESSION A , V$PROCESS B WHERE A.PADDR IN (SELECT S.PADDR FROM V$SESSION S WHERE STATUS = 'INACTIVE' GROUP BY S.PADDR HAVING MIN(ROUND(LAST_CALL_ET / 60)) > 120) AND A.PADDR = B.ADDR AND A.STATUS = 'INACTIVE'; SPOOL OFF
Oracle Process의 정보
- V$SESSION
- V$PROCESS
- SYS.V_$SESS_IO
SELECT S.STATUS "STATUS" ,S.SERIAL# "SERIAL#" ,S.TYPE "TYPE" ,S.USERNAME "DB USER" ,S.OSUSER "CLIENT USER" ,S.SERVER "SERVER" ,S.MACHINE "MACHINE" ,S.MODULE "MODULE" ,S.TERMINAL "TERMINAL" ,S.PROGRAM "PROGRAM" ,P.PROGRAM "O.S. PROGRAM" ,S.LOGON_TIME "CONNECT TIME" ,LOCKWAIT "LOCK WAIT" ,SI.PHYSICAL_READS "PHYSICAL READS" ,SI.BLOCK_GETS "BLOCK GETS" ,SI.CONSISTENT_GETS "CONSISTENT GETS" ,SI.BLOCK_CHANGES "BLOCK CHANGES" ,SI.CONSISTENT_CHANGES "CONSISTENT CHANGES" ,S.PROCESS "PROCESS" ,P.SPID ,P.PID ,S.SERIAL# ,SI.SID ,S.SQL_ADDRESS "ADDRESS" ,S.SQL_HASH_VALUE "SQL HASH" ,S.ACTION FROM V$SESSION S , V$PROCESS P , SYS.V_$SESS_IO SI WHERE S.PADDR = P.ADDR(+) AND SI.SID(+) = S.SID AND S.USERNAME IS NOT NULL AND NVL(S.OSUSER, 'X') <> 'SYSTEM' AND S.TYPE <> 'BACKGROUND' ORDER BY 3;
오브젝트에 접속되어 있는 프로그램 조회
- V$SESSION
- V$ACCESS
SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT FROM V$SESSION A ,V$ACCESS B WHERE A.SID = B.SID AND B.OWNER NOT IN ('SYS') AND A.TYPE!= 'BACKGROUND' AND B.OBJECT LIKE UPPER('&OBJECT_NAME') || '%' GROUP BY B.OBJECT, SUBSTR(A.PROGRAM, 1, 15);
롤백 세그먼트 경합 조회
- V$ROLLSTAT
- V$ROLLNAME
SELECT NAME T0 , GETS T1 ,WAITS T2 , TO_CHAR(TRUNC(WAITS / GETS * 100, 2), 099.99) || '%' T3 , TO_CHAR(ROUND(RSSIZE / 1024)) T4 , SHRINKS T5 , EXTENDS T6 FROM V$ROLLSTAT , V$ROLLNAME WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN;
CPU를 많이 사용하는 세션의 식별
- V$SESSTAT
- V$STATNAME
- V$SESSION
SELECT A.SID , C.SERIAL# , A.VALUE , C.USERNAME , C.STATUS , C.PROGRAM FROM V$SESSTAT A , V$STATNAME B , V$SESSION C WHERE A.STATISTIC# = B.STATISTIC# AND A.SID = C.SID AND B.NAME = 'CPU used by this session' AND A.VALUE > 0 ORDER BY A.VALUE DESC;
Disk Read 가 많은 SQL문 찾기
- V$SQLAREA
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA WHERE DISK_READS > 100 ORDER BY DISK_READS DESC;
Rollback Segment를 사용하고 있는 SQL문 조회
- V$ROLLNAME
- V$ROLLSTAT
- V$SESSION
- V$SQLTEXT
- V$TRANSACTION
SELECT A.NAME , B.XACTS , C.SID , C.SERIAL# , C.USERNAME , D.SQL_TEXT FROM V$ROLLNAME A , V$ROLLSTAT B , V$SESSION C , V$SQLTEXT D , V$TRANSACTION E WHERE A.USN = B.USN AND B.USN = E.XIDUSN AND C.TADDR = E.ADDR AND C.SQL_ADDRESS = D.ADDRESS AND C.SQL_HASH_VALUE = D.HASH_VALUE ORDER BY A.NAME, C.SID, D.PIECE;
오래도록 수행되는 Full Table Scan를 모니터링
- V$SESSION_LONGOPS
SELECT SID , SERIAL# , OPNAME , TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START" , (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE" FROM V$SESSION_LONGOPS;
System 테이블스페이스에 비시스템 세그먼트 조회
- DBA_SEGMENTS
SELECT OWNER , SEGMENT_NAME , SEGMENT_TYPE , TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND TABLESPACE_NAME = 'SYSTEM';
인덱스의 Delete Space 조회
- INDEX_STATS
--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. SELECT NAME , LF_ROWS , DEL_LF_ROWS , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%" FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');
딕셔너리/뷰 정보 조회
- DICTIONARY
- DICT_COLUMNS
SELECT A.TABLE_NAME , B.COLUMN_NAME FROM DICTIONARY A , DICT_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME;
유저의 모든 세션 트레이스
--- CREATE THE BELOW TRIGGER TO ENABLE TRACE ALL SESSION OF USER ( SCOTT) CREATE OR REPLACE TRIGGER USER_TRACE_TRG AFTER LOGON ON DATABASE BEGIN IF USER = 'SCOTT' THEN execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; /
커셔 내부 SQL의 상세내용 보기
select module,parsing_schema_name,inst_id,sql_id,plan_hash_value,child_number,sql_fulltext, to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),sql_plan_baseline,executions, elapsed_time/executions/1000/1000,rows_processed from gv$sql where sql_id in ('&sql_id');
커셔 내부 sql쿼리 Flush
-- First get the address, hash_value of the sql_id select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '5qd8a442c328k'; ADDRESS HASH_VALUE --------------- ------------ C000007067F39FF0 4000666812 -- Now flush the query SQL> exec DBMS_SHARED_POOL.PURGE ('C000007067F39FF0, 4000666812', 'C'); Note : For RAC, same need to be executed on all the nodes .
sql_id 의 바인드변수 값 조회
SELECT sql_id, b. LAST_CAPTURED, t.sql_text sql_text, b.HASH_VALUE, b.name bind_name, b.value_string bind_value FROM gv$sql t JOIN gv$sql_bind_capture b using (sql_id) WHERE b.value_string is not null AND sql_id='&sqlid' /
현재 실행 중인 세션의 SQL 조회
select sesion.sid, sesion.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time, sql_text from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address and sesion.username is not null;
ACTIVE 세션 정보 조회
set echo off set linesize 95 set head on set feedback on col sid head "Sid" form 9999 trunc col serial# form 99999 trunc head "Ser#" col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head "Client|Machine" col program form a15 trunc head "Client|Program" col login form a11 col "last call" form 9999999 trunc head "Last Call|In Secs" col status form a6 trunc select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser , substr(program||module,1,15) program,substr(machine,1,22) machine , to_char(logon_time,'ddMon hh24:mi') login , last_call_et "last call",status from gv$session where status='ACTIVE' order by 1 /
DB 대기이벤트 조회
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser , substr(b.program||b.module,1,15) program , substr(b.machine,1,22) machine , a.event , a.p1,b.sql_hash_value from v$session_wait a , V$session b where b.sid=a.sid and a.event not in('SQL*Net message from client','SQL*Net message to client', 'smon timer','pmon timer') and username is not null order by 6 /
세션의 템프 사용량 조회
SELECT b.tablespace , ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size , a.inst_id as Instance , a.sid||','||a.serial# AS sid_serial , NVL(a.username, '(oracle)') AS username , a.program , a.status , a.sql_id FROM gv$session a , gv$sort_usage b , gv$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id ORDER BY temp_size desc /
현재 트랜잭션 조회
col name format a10 col username format a8 col osuser format a8 col start_time format a17 col status format a12 tti 'Active transactions' select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS", decode(t.space, 'YES', 'SPACE TX', decode(t.recursive, 'YES', 'RECURSIVE TX', decode(t.noundo, 'YES', 'NO UNDO TX', t.status) )) status from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s where t.xidusn = r.usn and t.ses_addr = s.saddr /
리스너 트레이스
- Set to the listener you want to trace LSNRCTL> set cur LISTENER_TEST -- Enable Trace: LSNRCTL> set trc_level ADMIN Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TEST))) LISTENER_TEST parameter "trc_level" set to admin The command completed successfully
세션의 PGA 사용량
set lines 2000 SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM v$sesstat a, v$statname b WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%') AND a.statistic# = b.statistic# order by ROUND(a.VALUE/(1024*1024),2) desc
현재 SGA 사용량
select round(used.bytes /1024/1024 ,2) used_mb , round(free.bytes /1024/1024 ,2) free_mb , round(tot.bytes /1024/1024 ,2) total_mb from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used , (select sum(bytes) bytes from v$sgastat where name = 'free memory') free , (select sum(bytes) bytes from v$sgastat) tot
physical read가 높은 세그먼트 조회
set pagesize 200 setlinesize 120 col segment_name format a20 col owner format a10 select segment_name,object_type,total_physical_reads from ( select owner||'.'||object_name as segment_name,object_type, value as total_physical_reads from v$segment_statistics where statistic_name in ('physical reads') order by total_physical_reads desc) where rownum <=10;
템프 파일별 I/O 사용량
SELECT SUBSTR(t.name,1,50) AS file_name, f.phyblkrd AS blocks_read, f.phyblkwrt AS blocks_written, f.phyblkrd + f.phyblkwrt AS total_io FROM v$tempstat f,v$tempfile t WHERE t.file# = f.file# ORDER BY f.phyblkrd + f.phyblkwrt DESC; select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB, i.inst_id,i.host_name FROM gv$session s, gv$sort_usage u ,gv$instance i WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;
[ash] TOP 쿼리
Query to get list of top running sqls in PAST between sysdate-1 to sysdate-23/34 . You can change accordingly SELECT active_session_history.user_id, dba_users.username, sqlarea.sql_text, SUM(active_session_history.wait_time + active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds FROM v$active_session_history active_session_history, v$sqlarea sqlarea, dba_users WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24 AND active_session_history.sql_id = sqlarea.sql_id AND active_session_history.user_id = dba_users.user_id and dba_users.username not in ('SYS','DBSNMP') GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username ORDER BY 4 DESC
[ash] 블로킹 세션 찾기
Query will list the blocking session details between SYSDATE - 1 AND SYSDATE-23/24 ( PAST) set pagesize 50 set linesize 120 col sql_id format a15 col inst_id format '9' col sql_text format a50 col module format a10 col blocker_ses format '999999' col blocker_ser format '999999' SELECT distinct a.sql_id , a.inst_id, a.blocking_session blocker_ses, a.blocking_session_serial# blocker_ser, a.user_id, s.sql_text, a.module,a.sample_time FROM GV$ACTIVE_SESSION_HISTORY a, gv$sql s where a.sql_id=s.sql_id and blocking_session is not null and a.user_id <> 0 -- exclude SYS user and a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE-23/24
cpu 과사용 세션
col program form a30 heading "Program" col CPUMins form 99990 heading "CPU in Mins" select rownum as rank, a.* from ( SELECT v.sid, program, v.value / (100 * 60) CPUMins FROM v$statname s , v$sesstat v, v$session sess WHERE s.name = 'CPU used by this session' and sess.sid = v.sid and v.statistic#=s.statistic# and v.value>0 ORDER BY v.value DESC) a where rownum < 11;
라이브러리 캐시 락을 잡는 세션
For standalone db: select sid Waiter, p1raw, substr(rawtohex(p1),1,30) Handle, substr(rawtohex(p2),1,30) Pin_addr from v$session_wait where wait_time=0 and event like '%library cache%'; For RAC DB: select a.sid Waiter,b.SERIAL#,a.event,a.p1raw, substr(rawtohex(a.p1),1,30) Handle, substr(rawtohex(a.p2),1,30) Pin_addr from v$session_wait a,v$session b where a.sid=b.sid and a.wait_time=0 and a.event like 'library cache%'; or set lines 152 col sid for a9999999999999 col name for a40 select a.sid,b.name,a.value,b.class from gv$sesstat a , gv$statname b where a.statistic#=b.statistic# and name like '%library cache%';
라이브러리 캐시에 의해 락이 발생된 세션
select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,23) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 Lock_addr from dba_lock_internal where mode_requested'None' and mode_requestedmode_held and session_id in ( select sid from v$session_wait where wait_time=0 and event like '%library cache%') ;
유저가 엑세스 하는 오브젝트 조회
set lines 299 column object format a30 column owner format a10 select * from gv$access where owner='&OWNER' and object='&object_name' and /
FULL TABLE SCAN 하는 SQL
select sql_id,object_owner,object_name from V$SQL_PLAN where operation='TABLE ACCESS' and options='FULL' and object_owner not in ('SYS','SYSTEM','DBSNMP');
딕셔너리 캐시 히트율
select sum(gets) as "Gets", sum(getmisses) as "Misses", (1-(sum(getmisses)/sum(gets)))*100 as "CACHE HIT RATIO" from gv$rowcache; NOTE - CACHE HIT RATIO SHOULD BE MORE THAN 95 PERCENT.
데이터베이스 뮤텍스 SLEEP
column mux format a18 heading 'Mutex Type' trunc; column loc format a32 heading 'Location' trunc; column sleeps format 9,999,999,990 heading 'Sleeps'; column wt format 9,999,990.9 heading 'Wait |Time (s)'; select e.mutex_type mux , e.location loc , e.sleeps - nvl(b.sleeps, 0) sleeps , (e.wait_time - nvl(b.wait_time, 0))/1000000 wt from DBA_HIST_MUTEX_SLEEP b , DBA_HIST_MUTEX_SLEEP e where b.snap_id(+) = &bid and e.snap_id = &eid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.mutex_type(+) = e.mutex_type and b.location(+) = e.location and e.sleeps - nvl(b.sleeps, 0) > 0 order by e.wait_time - nvl(b.wait_time, 0) desc;
대량 Physical read를 유발하는 쿼리
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads, t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id) WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC) WHERE rownum <= 5;
대량 리두를 발생시키는 세션 조회
set lines 2000 set pages 1000 col sid for 99999 col name for a09 col username for a14 col PROGRAM for a21 col MODULE for a25 select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id from v$sesstat s join v$statname n on n.statistic# = s.statistic# join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc;
언두를 발생시키는 세션 조회
select a.sid, a.serial#, a.username , b.used_urec used_undo_record , b.used_ublk used_undo_blocks from v$session a , v$transaction b where a.saddr=b.ses_addr ;
latch contetion을 유발하는 쿼리
col OBJECT_NAME for a30 col owner for a12 with bh_lc as (select lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets, lc.immediate_misses, lc.spin_gets, lc.sleeps, bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class, bh.state, bh.obj from v$session_wait sw, v$latchname ld, v$latch_children lc, x$bh bh where lc.addr =sw.p1raw and sw.p2= ld.latch# and ld.name='cache buffers chains' and lower(sw.event) like '%latch%' and bh.hladdr=lc.addr ) select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type, bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets, bh_lc.immediate_misses, spin_gets, sleeps from bh_lc, dba_objects o where bh_lc.obj = o.data_object_id(+) order by 1,2 desc;
래치 타입 과 hash value
Set lines 160 pages 100 Column event format A35 Column name format A35 select x.event, x.sql_hash_value, case when x.event like 'latch%' then l.name else ' ' end name, x.cnt from ( select substr(w.event, 1, 28) event, s.sql_hash_value, w.p2,count(*) cnt from v$session_wait w, v$session s, v$process p where s.sid=w.sid and p.addr = s.paddr and s.username is not null and w.event not like '%pipe%' and w.event not like 'SQL*%' group by substr(w.event, 1, 28), sql_hash_value,w.p2 ) x, v$latch l where x.p2 = l.latch#(+) order by cnt;
커서에서 추출한 sql id에 대한 sql tuning advisor
Create tuning task set long 1000000000 DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'apwfwjhgc9sk8', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 500, task_name => 'apwfwjhgc9sk8_tuning_task_1', description => 'Tuning task for statement apwfwjhgc9sk8'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / Execute tuning task EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'apwfwjhgc9sk8_tuning_task_1'); Generate report SET LONG 10000000; SET PAGESIZE 100000000 SET LINESIZE 200 SELECT DBMS_SQLTUNE.report_tuning_task('apwfwjhgc9sk8_tuning_task_1') AS recommendations FROM dual; SET PAGESIZE 24
SGA 타겟 advisor
- STATISTICS_LEVEL should be TYPICAL/ALL. SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ -------------------------------- -------------------------- statistics_level string TYPICAL select * from v$sga_target_advice order by sga_size;
쉐어드풀 advisor
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB", shared_pool_size_factor "Size Factor", estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;
쉐어드풀 flush를 유발하는 오브젝트
Set lines 160 pages 100 Select * from x$ksmlru order by ksmlrnum;
ADDM 리포트 생성
cd $ORACLE_HOME/rdbms/admin SQL> @addmrpt.sql Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1058 Begin Snapshot Id specified: 1058 Enter value for end_snap: 1059 End Snapshot Id specified: 1059
프로시져/패키지
패키지 검색 1 - 특정 오라클 사용자 중에서 패키지 소스와 일치하는 텍스트를 조회
-- 오라클 사용자에서 사용하는 패키지를 보여는 방법 ---- :IN_OWNER: 오라클 사용자 ---- :IN_OBJECT_NAME: 패키지 이름 SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER =:IN_OWNER AND OBJECT_NAME LIKE '%' ||:IN_OBJECT_NAME || '%' AND OBJECT_TYPE = 'PACKAGE';
-- 오라클 사용자의 패키지 중에서 텍스트 내용을 검색하여 패키지 정보를 추출
- IN_OWNER: 오라클 사용자
- IN_TEXT : 패키지소 스에서 검색할 텍스트
SELECT NAME -- 패키지 이름 , LINE -- 라인 수 , TEXT -- 패키지 BODY에 수정된 내용 FROM DBA_SOURCE WHERE OWNER =:IN_OWNER AND TEXT LIKE '%' ||:IN_TEXT || '%';
특정 사용자의 패키지 내에서 주석처리가 되지 않은 항목을 조회
/*
오라클 사용자가 사용하는 패키지의 BODY 소스를 검색하여 주식이 없거나 패턴이 맞지 않는 항목을 조회 한다.
- /
SELECT * FROM DBA_OBJECTS B WHERE B.OWNER =:IN_OWNER AND B.OBJECT_TYPE = 'PACKAGE BODY' AND B.STATUS <> 'INVALID' -- VALID 상태만 조회, 만약 INVALID 된다고 해도 패키지를 수행하는 순간 컴파일 됨. AND NOT EXISTS (SELECT 1 FROM DBA_SOURCE A WHERE A.OWNER = B.OWNER AND A.TYPE = B.OBJECT_TYPE AND A.NAME = B.OBJECT_NAME AND A.LINE <= 5 AND A.TEXT LIKE '%NAME%');
다중 UPDATE 쿼리
/* BYPASS_UJVC 힌트를 이용하면 특정 뷰를 만들어서 컬럼 대 컬럼을 대입 할 수 있습니다. 아래 쿼리는 메타정보관리용 유저로서 코멘트의 내용 중에서 정규표현식을 이용하여 관련 데이터를 조작 하는 방법 입니다. */ UPDATE /*+ BYPASS_UJVC */ ( SELECT X.* ,CASE WHEN UNIT_INSTR > 0 THEN SUBSTR(X.COLUMN_DESC, X.UNIT_INSTR + 3, 1) END B_UNIT FROM (SELECT A.SERVER ,A.OWNER ,A.TABLE_NAME ,A.COLUMN_NAME ,A.COLUMN_DESC ,A.GRP_CD A_GRP_CD ,A.UNIT A_UNIT ,REGEXP_INSTR(COLUMN_DESC, '단위\:') UNIT_INSTR FROM DBA_MYCOL A WHERE A.SERVER =:IN_SERVER AND A.OWNER =:IN_OWNER AND A.TABLE_NAME =:IN_TABLE_NAME) X) SET A_UNIT = B_UNIT UPDATE_DT = SYSDATE;
CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)
SELECT A.* ,(SELECT SS.SQL_TEXT FROM V$SQLAREA SS WHERE SS.ADDRESS = A.SQL_ADDRESS AND ROWNUM <= 1 ) AS SQL_TEST FROM ( SELECT A.SID ,C.SERIAL# ,A.VALUE ,C.USERNAME ,C.STATUS ,C.PROGRAM ,C.SQL_ADDRESS ,ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN FROM V$SESSTAT A ,V$STATNAME B ,V$SESSION C WHERE A.STATISTIC# = B.STATISTIC# AND A.SID = C.SID AND B.NAME = 'CPU used by this session' AND A.VALUE > 0 AND C.STATUS = 'ACTIVE' AND C.USERNAME IS NOT NULL ) A WHERE A.RN <= 10;
현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)
SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.* FROM V$SQLAREA A , V$SESSION B WHERE A.SQL_TEXT LIKE '%SELECT%' AND A.ADDRESS = B.SQL_ADDRESS AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') AND B.USERNAME IS NOT NULL;
현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리
SELECT B.USERNAME ,A.SID ,A.PGA_USAGE ,A.UGA_USAGE ,A.CPU_USAGE_SECONDS ,B.MACHINE ,B.PROGRAM ,B.MODULE FROM (SELECT B.SID ,MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE ,MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE ,MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS FROM V$SESSTAT B ,V$STATNAME C WHERE B.STATISTIC# = C.STATISTIC# GROUP BY B.SID) A ,V$SESSION B WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;
상호 DB간에 컬럼 이름 비교
/* 양쪽 DB에서 사용하는 테이블 중에서 컬럼 이름 다른 항목을 찾는다. */ SELECT A.TABLE_NAME ,A.COLUMN_NAME ,A.COLUMN_ID ,A.DATA_TYPE || '(' || A.DATA_LENGTH || ')' DATA_TYPE FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME AND NOT EXISTS (SELECT 'X' FROM USER_TAB_COLUMNS B WHERE B.TABLE_NAME = A.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME);
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS@LINK_ESTDB A WHERE A.TABLE_NAME =:IN_TABLE_NAME MINUS SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_ID FROM USER_TAB_COLUMNS A WHERE A.TABLE_NAME =:IN_TABLE_NAME
DB Link 보기
SELECT SUBSTRB (U.NAME, 1, 10) AS OWNER , SUBSTRB (L.NAME, 1, 20) AS DB_LINK , SUBSTRB (L.HOST, 1, 10) AS HOST , SUBSTRB (L.USERID || '/' || L.PASSWORD, 1, 15) AS USERPASS FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
View의 정의 내역 보기
SET LONG 100000 SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME LIKE UPPER('&뷰_이름');
동일(중복) 자료 삭제 방법
- ROW_NUMBER() 이용
DELETE FROM 테이블명 WHERE ROWID IN ( SELECT ROWID FROM ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY 컬럼명 ORDER BY 컬럼명) AS num FROM 테이블명 ) WHERE num > 1 -- num의 값이 1초과인 데이터들만 삭제 ) );
- MIN(ROWID) 보다 큰건은 삭제(신규 추가된건 삭제)
DELETE FROM 테이블명 A WHERE ROWID > (SELECT MIN(ROWID) FROM 테이블명 B WHERE A.컬럼 = B.컬럼 );
- MAX(ROWID) 보다 작은건 삭제 (처음 추가된건 제)
DELETE FROM 테이블명 A WHERE ROWID < (SELECT MAX(ROWID) FROM 테이블명 B WHERE A.컬럼 = B.컬럼);
데이터베이스 시작/종료 관리 명령
svrctl 명령어
DB 시작 / 종료
DB start
emoji_objects SYNTAX - START DB
srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)
srvctl start database -d PRODB -o nomount srvctl start database -d PRODB -o mount srvctl start database -d PRODB -o open
DB stop
emoji_objects SYNTAX - STOP DB
srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort
srvctl stop database -d PRODB -o normal srvctl stop database -d PRODB -o immediate srvctl stop database -d PRODB -o transactional srvctl stop database -d PRODB -o abort
DB remove
emoji_objects SYNTAX - REMOVE DB SERVICE
srvctl remove database -d db_unique_name [-f] [-y] [-v]
srvctl remove database -d PRODB -f -y
DB add
emoji_objects SYNTAX - ADDING DB SERVICE
srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]
srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora
DB 인스턴스 추가 / 제거
DB 인스턴스 remove
emoji_objects SYNTAX - REMOVING INSTANCE
srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
srvctl remove instance -d PRODB - I PRODB1
DB 인스턴스 add
emoji_objects SYNTAX - ADDING INSTANCE
srvctl add instance –d db_unique_name –i inst_name -n node_name
srvctl add instance -d PRODB - i PRODB1 -n rachost1
DB 인스턴스 stop
emoji_objects SYNTAX - STOPPING INSTANCE
srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]
srvctl stop instance -d PRODB -i PRODB1
DB 인스턴스 start
emoji_objects SYNTAX - STARTING INSTANCE
srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]
srvctl start instance -d PRODB -i PRODB1
데이터베이스/인스턴스 ENABLE 과 DISABLE
-- ENABLE - Reenables management by Oracle Restart for a component. -- DISABLE - Disables management by Oracle Restart for a component. srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME srvctl enable database -d DB_UNIQUE_NAME srvctl disable database -d DB_UNIQUE_NAME
서비스 리로케이트(relocate)
emoji_objects SYNTAX - RELOCATE
srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name}
- EXAMPLE:(Relocating service PRDB_SRV from PREDB2 to PREDB1)
srvctl relocate service -d PREDB -s PRDB_SVC -i PREDB2 -t PREDB1 -- Check the status of service srvctl status service -d PREDB -s PRDB_SVC
서비스 START / STOP
emoji_objects SYNTAX - START/STOP SERVICE
srvctl start service -d {DB_NAME} -s {SERVICE_NAME} srvctl stop service -d {DB_NAME} -s {SERVICE_NAME}
- EXAMPLE:
srvctl start service -d PREDB -s PRDB_SRV srvctl stop service -d PREDB -s PRDB_SRV
서비스 ADD/ REMOVE
서비스 ADD
emoji_objects SYNTAX - ADDING A SERVICE
srvctl add service -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}]
- EXAMPLE:
srvctl add service -d PREDB -s PRDB_SRV -r "PREDB1,PREDB2" -a "PREDB2" -P BASIC
서비스 REMOVE
emoji_objects SYNTAX - REMOVING A SERVICE
srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}
- EXAMPLE:
srvctl remove service -d PREDB -s PRDB_SRV
환경변수(setenv) 설정
-- setenv to set env variables.(ORCL is the db_unique_name) srvctl setenv database -db ORCL -env "ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1" srvctl setenv database -db ORCL -env "TNS_ADMIN=/oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin" -- getenv to view the env setting: srvctl getenv database -db ORCL
ASM CONFIG 조회
srvctl config asm srvctl config asm -detail
crsctl 명령어
- oracle docs
crs autorestart Enable/Disable
- Run as root user
$GRID_HOME/bin/crsctl enable crs CRS-4622: Oracle High Availability Services autostart is enabled. $GRID_HOME/bin/crsctl disable crs CRS-4621: Oracle High Availability Services autostart is disabled.
crs Stop/Start
-- stop crs ( run from root) $GRID_HOME/bin/crsctl stop crs -- start crs( run from root) $GRID_HOME/bin/crsctl start crs
RAC에서 클러스터 명 찾기
$GRID_HOME/bin/cemutlo -n -- or $GRID_HOME/bin/olsnodes -c
voting disk 위치 조회
$GRID_HOME/bin/crsctl query css votedisk
OCR 위치 조회
$GRID_HOME/bin/ocrcheck
그리드 버전 조회
$GRID_HOME/bin/crsctl query crs softwareversion
클러스터 컴포넌트 상태 체크
$GRID_HOME/bin/crsctl stat res -t $GRID_HOME/bin/crsctl check crs $GRID_HOME/bin/crsctl check cssd $GRID_HOME/bin/crsctl check crsd $GRID_HOME/bin/crsctl check evmd
cluster_interconnect 상세조회
$GRID_HOME/bin/oifcfg getif app-ipmp0 172.21.39.128 global public loypredbib0 172.16.3.192 global cluster_interconnect loypredbib1 172.16.4.0 global cluster_interconnect select NAME,IP_ADDRESS from v$cluster_interconnects; NAME IP_ADDRESS --------------- ---------------- loypredbib0 172.16.3.193 loypredbib1 172.16.4.1
ocr /list 수동 백업
List down the backups of OCR $GRID_HOME/bin/ocrconfig -showbackup Take manual OCR backup $GRID_HOME/bin/ocrconfig -manualbackup
Voting Disk를 새로운 DistGroup으로 이동하기
$GRID_HOME/bin/crsctl replace votedisk +NEW_DG -- Check the status using below command $GRID_HOME/bin/crsctl query css votedisk
디스크 타임아웃 조회
- Disk timeout from node to voting disk(disktimeout)
crsctl get css disktimeout CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services.
- Network latency in the node interconnect (Misscount)
crsctl get css misscount CRS-4678: Successful get misscount 30 for Cluster Synchronization Services.
olsnode를 이용한 node 정보 조회
-- List of nodes in the cluster olsnodes -- Nodes with node number olsnodes -n -- Node with vip olsnodes -i olsnodes -s -t -- Leaf or Hub olsnodes -a -- Getting private ip details of the local node olsnodes -l -p -- Get cluster name olsnodes -c
클러스터 환경설정 정보 조회
$ crsctl get cluster configuration Name : dbaclass-cluster Configuration : Cluster Class : Standalone Cluster Type : flex The cluster is not extended. -------------------------------------------------------------------------------- MEMBER CLUSTER INFORMATION Name Version GUID Deployed Deconfigured ================================================================================ ================================================================================
$ crsctl get node role status -all Node 'hostnode1' active role is 'hub' Node 'hostnode2' active role is 'hub'
RAC 인터페이스 정보조회
oifcfg iflist -p -n backup0 172.21.56.0 PRIVATE 255.255.254.0 cdnet0 162.168.1.0 PRIVATE 255.255.255.0 cdnet0 169.254.0.0 PUBLIC 255.255.128.0 cdnet1 162.168.2.0 PRIVATE 255.255.255.0 cdnet1 169.254.128.0 PUBLIC 255.255.128.0 pap-ipmp0 172.20.179.128 PUBLIC 255.255.255.128 tan-ipmp0 172.20.128.0 PRIVATE 255.255.252.0 dppp0 162.168.224.0 PRIVATE 255.255.255.0
RAC - OLR(Oracle Local Registry)
- OLR(ORACLE LOCAL REGISTRY)
-- Get current OLR location:(run from root only) $GRID_HOME/bin/ocrcheck -local -- List the OLR backups: $GRID_HOME/bin/ocrconfig -local -showbackup -- Take manual OLR backup: $GRID_HOME/bin/ocrconfig -local -manualbackup