유용한 오라클 스크립트

1. 테이블에 특정컬럼에 중복된 값을 찾는 SQL
/*--------------------------------------------------------------------------*/
/* USAGE : @중복찾기.SQL [테이블명] [중복을조사할컬럼명]
/*
/* WARNING : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째
/* 값부터 출력됨. <>
/*--------------------------------------------------------------------------*/
SELECT * FROM &1 A
WHERE ROWID >
(SELECT MIN(ROWID) FROM &1 B
WHERE B.&2 = A.&2)
ORDER BY &2;

2. PK와 FK간의 연관관계를 찾아 보여주는 SQL
/*--------------------------------------------------------------------------*/
/*　 사용법　　 　 :> @SHOW_POSITIONS　 PARENT_TABLE　 CHILD_TABLE　　
/*　 DESCRIPTION　 :　 SHOWS PRIMARY AND FOREIGN KEY POSITIONS　　
/*　　
/*　 WARNING　　 :　 이 문장은 해당 TABLE의 CONSTRAINT생성시 NAMING　　　
/*　　　　　　　　　 CONVENTION을 따른 경우에 적용되도록 되어 있다.　　
/*--------------------------------------------------------------------------*/
SET VERIFY OFF　　
CLEAR BREAK　　
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&2')
AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

3. 컬럼에 걸려있는 CONSTRAINT 를 보여주는 SQL.
/*--------------------------------------------------------------------------*/
/* USAGE : @SHOW_CONSTRAINTS TABLE_NAME COLUMN_NAME
/* DESCRIPTION: 해당 TABLE의 COLUMN에 걸려 있는 CONSTRAINT를 보여준다.
/* < 실행 예 >
/* SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
/*--------------------------------------------------------------------------*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

4. CONSTRAINT이름으로 해당 테이블과 컬럼찾는 SQL
/*--------------------------------------------------------------------------*/
/* USAGE : @SHOW_COLUMNS CONSTRAINT_NAME
/* DESCRIPTION : SHOWS THE COLUMNS BOUND BY A CONSTRAINT
/* 사용예 : SQL> @SHOW_COLUMNS PK_EMPNO
/*--------------------------------------------------------------------------*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');


5. 컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사용법 : SQL> @COL_FIND [컬럼명]
/*--------------------------------------------------------------------------*/
COL CNAME FORMAT A20
COL COLTYPE FORMAT A10
COL NULLS FORMAT A5
COL DEFAULTVAL FORMAT A10

SELECT TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
FROM COL
WHERE CNAME = UPPER('&1')


6. 딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사용법 : SQL> @DIC_FIND [키워드(대소문자가림)]
/*--------------------------------------------------------------------------*/
TABLE_NAME FORMAT A15
COL COMMENTS FORMAT A100

SELECT * FROM DICTIONARY
WHERE COMMENTS LIKE ('%&1%')
/

7. DEAD LOCK이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL
/*--------------------------------------------------------------------------*/
/* 사 용 법　　:SQL> @FIND_DEADLOCK
/*　 DESCRIPTION : 데드락이 발생할 경우 LOCKING 된 유저와 SQL문을 보여준다.
/*　 데드락이 발생한 유저를 KILL 하려면.
/* ALTER SYSTEM KILL SESSION '{SERIAL#},{SID}';
/*--------------------------------------------------------------------------*/
SELECT A.SERIAL#, A.SID, A.USERNAME, B.ID1, C.SQL_TEXT
FROM V$SESSION A, V$LOCK B, V$SQLTEXT C
WHERE B.ID1 IN( SELECT DISTINCT E.ID1 FROM V$SESSION D, V$LOCK E
WHERE D.LOCKWAIT = E.KADDR)
AND A.SID = B.SID
AND C.HASH_VALUE = A.SQL_HASH_VALUE
AND B.REQUEST = 0;

8. 테이블 데이터의 사이즈를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE DATA SIZE를 정확히 계산해주는 스크립트. <<박제용>>
/* 사용법 : @TAB_SIZE [TABLE_NAME]
/*--------------------------------------------------------------------------*/
ANALYZE TABLE &1 DELETE STATISTICS;
ANALYZE TABLE &1 COMPUTE STATISTICS;

SELECT GREATEST(4, CEIL(NUM_ROWS/
((ROUND(((1958-(INI_TRANS*23))*
((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048)
TABLESIZE_KBYTES
FROM USER_TABLES
WHERE TABLE_NAME = UPPER('&1');


9. 테이블을 복사해주는 스크립트 (V8.0 ONLY)
/*--------------------------------------------------------------------------*/
/* TABLE을 다른 스키마 혹은 TABLE로 복사 <<박제용>>
/* NOTICE) 1. ORACLE 8.0 이상에서만 지원.
/* 2. SQL*NET 이 설정되어 있어야만 한다.
/* 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
/* 따라서 테이블을 생성해 주고 입력하는것이 좋다.
/* 4. SQL*PLUS 에서만 실행된다.
/* 사용법) @TAB_COPY SCOTT/TIGER@LINK SOURCE_TABLE_NAME
TARGET_TABLE_NAME
/*--------------------------------------------------------------------------*/
COPY FROM &1 CREATE &3 USING SELECT * FROM &2

/* 다른 DB로 복사할때는
COPY FROM &1 TO &2 CREATE &4 USING SELECT * FROM &3
*/

/* 미리 만들어진 TABLE에 입력할때는
COPY FROM &1 INSERT &3 USING SELECT * FROM &2
*/

10.이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트.
/*--------------------------------------------------------------------------*/
/* PL/SQL 소스를 보기위한 스크립트.. <박제용>
/* 사용법 : FIND_PLSQL [프로시져명칭]
/*--------------------------------------------------------------------------*/
SELECT TEXT
FROM USER_SOURCE
WHERE NAME = UPPER('&1')
ORDER BY LINE;

11. 테이블이 사용중인 블록 크기를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
/* 사용법 : 1) DBA 권한으로 로그인한다.
/* 2) SQL> @TAB_BLOCK [TABLE명]
/* NOTICE : SUM(BLOCKS)는 사용하는 블럭의 갯수이며 사이즈는
/* DB_BLOCK_SIZE를 곱하여 얻을 수 있다.
/*--------------------------------------------------------------------------*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

12. SQL CURSOR를 보여주는 스크립트
/*--------------------------------------------------------------------------*/
/* SQL CURSOR를 조사하는 스크립트. <<박제용 99.11>>
/* SQL CURSOR 를 조사하여 부하가 많이 걸리는 SQL문과
/* 메모리를 조사한다.
/* LOADS : 캐쉬에서 나갔다 들어온 횟수(BEST=1).
/* INVALIDATIONS : LRU에서 무효화된 횟수. 이 값이 4이상이면
/* SHARED_POOL_AREA를 확장해야한다.
/* PARSE_CALLS : 이 커서의 호출 수.
/* SORTS : 수행된 소트횟수
/* COMMAND_TYPE: 2 - INSERT, 3-SELECT, 4-UPDATE, 7-DELETE
/*--------------------------------------------------------------------------*/
SELECT SQL_TEXT, LOADS, INVALIDATIONS, PARSE_CALLS, SORTS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%'
AND COMMAND_TYPE IN(2,3,6,7);

13. EXPAIN PLAN 결과를 보기 쉽게 출력해주는 스크립트
/*--------------------------------------------------------------------------*/
/* EXPAIN PLAN 결과를 보기 쉽게 출력해주는 스크립트.
/* 1) EXPAIN을 처음 사용할 경우엔 [ORACLE_HOME]/RDBMS/ADMIN/UTLXPLAN.SQL을 실행,
/* PLAN_TABLE을 생성한다.
/* 2) 처음 사용이 아니면 DELETE FROM PLAN_TABLE; 을 실행하여 이전 결과를 삭제.
/* 실행결과 파싱번호(ID)가 길면 SQL이 비효율적이거나, SHARED_POOL_SIZE가 작은것이다.
/* 기타 SQL문이 인덱스를 사용하는지 등등을 알수 있다.
/*--------------------------------------------------------------------------*/
COL OPERATION FORMAT A30
COL OPTIONS FORMAT A20
COL ID FORMAT 99

SELECT ID, LPAD(' ',2*LEVEL) || OPERATION ||
DECODE(ID, 0, ' COST= ' || POSITION )"OPERATION",
OPTIONS, OBJECT_NAME "OBJECT"
FROM PLAN_TABLE
CONNECT BY PRIOR ID=PARENT_ID
START WITH ID =0;

14. 과도한 DISK READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 과도한 DISK READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/* 원인 => 1) SQL문이 최적화 되지 않아 DISK READ를 많이 할 수 밖에 없는 쿼리일경우.
/* (INDEX가 없거나 사용되지 않을때)
/* 2) DB_BLOCK_BUFFERS 또는 SHARED_POOL_SIZE 가 작은 경우. (메모리가 적음)
/*--------------------------------------------------------------------------*/
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA
WHERE DISK_READS > 10000
ORDER BY DISK_READS DESC;

15. 과도한 LOGICAL READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 과도한 LOGICAL READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/*
/* 원인 => 1) 인덱스 컬럼에 DISTINCT한 값이 적은, 부적절한 인덱스의 사용.
/* (대체로 인덱스를 지워야 할 경우)
/* 2) 최적화 되지 않은 SQL 문장
/*--------------------------------------------------------------------------*/
SELECT BUFFER_GETS, SQL_TEXT FROM V$SQLAREA
WHERE BUFFER_GETS > 200000
ORDER BY BUFFER_GETS DESC;

16. 유저별로 과도한 LOGICAL READ를 수행하는 SQL 문 찾기
/*--------------------------------------------------------------------------*/
/* SQL QUERY 튜닝 스크립트.. <박제용>
/* 유저별로 과도한 LOGICAL READ를 수행하는 SQL 문 찾기
/*--------------------------------------------------------------------------*/
BREAK ON USER_NAME ON DISK_READS ON BUFFER_GETS ON ROWS_PROCESSED
SELECT A.USER_NAME, B.DISK_READS, B.BUFFER_GETS, B.ROWS_PROCESSED,
C.SQL_TEXT
FROM V$OPEN_CURSOR A, V$SQLAREA B, V$SQLTEXT C
WHERE A.USER_NAME = UPPER('&&USER') AND A.ADDRESS = C.ADDRESS
AND A.ADDRESS = B.ADDRESS
ORDER BY A.USER_NAME, A.ADDRESS, C.PIECE;

17. SHARED_POOL의 HIT RATIO보는 스크립트
/*--------------------------------------------------------------------------*/
** SHARED_POOL의 HIT RATIO보는 스크립트.. <박제용>
** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
** 만일 적게 할당되면 유저의 접속이 많아질수록 THROUGHPUT에 큰 영향을 준다.
** HIT RATIO는 95% 이상을 유지시켜야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS", SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES) / (SUM(GETS)+SUM(GETMISSES))))*100
"HITRATE"
FROM V$ROWCACHE;

18. SHARED_POOL에 저장된 내용보기
/*--------------------------------------------------------------------------*/
/* SHARED_POOL에 저장된 내용보기 <박제용>
/* 프로시져나 패키지등은 SHARED_POOL에 저장되며 저장된 객체중
/* 그 크기가 100K 가 넘는것을 보여준다.
/*--------------------------------------------------------------------------*/
COL NAME FORMAT A30

SELECT NAME, SHARABLE_MEM
FROM V$DB_OBJECT_CACHE
WHERE SHARABLE_MEM > 100000
AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
AND KEPT = 'NO';

19. SHARED_POOL_SIZE의 현재 사용 현황을 보여줌
/*--------------------------------------------------------------------------*/
/* SHARED_POOL_SIZE의 현재 사용 현황을 보여줌. <박제용>
/* SHARED_POOL_SIZE의 현재의 사용현황을 보여준다.
/* 이 데이터를 주기적으로 보관하여 분석한다.
/*--------------------------------------------------------------------------*/
COL VALUE FOR 999,999,999,999 HEADING "SHARED POOL SIZE"
COL BYTES FOR 999,999,999,999 HEADING "FREE BYTES"
SELECT TO_NUMBER(V$PARAMETER.VALUE) VALUE, V$SGASTAT.BYTES,
(V$SGASTAT.BYTES/V$PARAMETER.VALUE)*100 "PERCENT FREE"
FROM V$SGASTAT, V$PARAMETER
WHERE V$SGASTAT.NAME = 'FREE MEMORY'
AND V$ PARAMETER .NAME = ‘SHARED_POOL_SIZE;

20. LIBRARY CACHE HITRATIO 출력 스크립트
/*--------------------------------------------------------------------------*/
/* LIBRARY CACHE HITRATIO 출력 스크립트
/* LIBRARY CACHE 의 HITRATIO 가 0.9 이하이면
/* SHARED POOL SIZE를 늘려주거나, SQL 문의 이상을
/* 조사해야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(PINS) EXECUTIONS,
SUM(PINHITS) "EXECUTION HITS",
SUM(RELOADS) MISSES,
((SUM(PINS) / (SUM(PINS) + SUM(RELOADS))) * 100) HITRATIO
FROM V$LIBRARYCACHE;

20. ROWCACHE 의 MISSRATIO를 조사하는 스크립트
/*--------------------------------------------------------------------------*/
/* ROWCACHE 의 MISSRATIO를 조사하는 스크립트
/* ROW CHACHE 의 MISS RATIO는 15% 이하로 유지하는 것이 좋다.
/* 그렇지 않을경우 SHARED_POOL_SIZE를 늘리는것을 고려해야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS",
SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES)/(SUM(GETS)+SUM(GETMISSES))))*100 "HITRATE"
FROM V$ROWCACHE;

21. SHARED_POOL의 HIT RATIO보는 스크립트
/*--------------------------------------------------------------------------*/
/* SHARED_POOL의 HIT RATIO보는 스크립트.. <박제용>
/* 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
/* 만일 적게 할당되면 유저의 접속이 많아질수록 THROUGHPUT에 큰 영향을 준다.
/* HIT RATIO는 95% 이상을 유지시켜야 한다.
/*--------------------------------------------------------------------------*/
SELECT SUM(GETS) "GETS", SUM(GETMISSES) "MISSES",
(1-(SUM(GETMISSES) / (SUM(GETS)+SUM(GETMISSES))))*100
"HITRATE"
FROM V$ROWCACHE;