About DATABASE

* ROLLUP 연산자
 - GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
   각 그룹에 계산함수를 적용한다.
 - GROUP BY절의 결과는 누적 계산 결과이다.
 

* CUBE 연산자
 - GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
 

* GROUPING 함수
 - 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
 - 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
 - GROUP BY절에 나타나는 컬럼에 적용된다.

사용 예)

  -- table생성(사원이름,급여,부서,직위,입사년도)
  CREATE TABLE roll_test (
    name   VARCHAR2(10),
    sal    NUMBER,
    dept   VARCHAR2(10),
    duty   VARCHAR2(10),
    entYear NUMBER(4)
  );

INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;

SELECT * FROM roll_Test;

-- 1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
--    (하나의 Column Grouping)

-----일반-----------
SELECT dept, SUM(sal)
FROM   roll_Test
GROUP BY dept;
---------------------

----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM   roll_Test
GROUP BY ROLLUP(dept);
-----------------------

----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------

-- 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
-- ROLLUP과 CUBE 차이점 없음

-- 2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
--    (두개의Column Grouping)

-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------

----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY CUBE(dept, duty);
-----------------------

-- ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
-- CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
-- GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨

-- 3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고, 총계를 구하라
--    (세개의 Column Grouping)

-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty, entYear;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------

----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------

- ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
  (부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
  ※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성

- CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
  (부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
  ※ 생성 가능한 모든 경우를 그룹 생성
 

사용 예)

* 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;

* 각 부서에 대한 직위별 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;

select SEGMENT_NAME ,segment_type ,sum(사용량)
from (
      SELECT SEGMENT_NAME , ROUND(SUM(BYTES)/1024/1024) AS "사용량", segment_type
      FROM USER_EXTENTS
      --WHERE SEGMENT_NAME LIKE 'TTXSTAT%'
      GROUP BY SEGMENT_NAME, segment_type)
GROUP BY rollup(SEGMENT_NAME),segment_type

1) export를 할때 데이터는 만들지 않고 schema image만 뽑아냅니다.
(만약 export를 받은 파일이 이미 있으시다면 1번 항목은 생략하셔도 됩니다.)

exp scott/tiger file=exp.dmp compress=n rows=n



2) 1번 스텝과 같이 해서 성공한 export dump파일을 이용하여 create script를 만드는 방법입니다.
이 방법은 import!라는 유틸리티를 사용하여 작업을 합니다.

imp scott/tiger file=exp.dmp indexfile=create_table.sql full=y



3) 요렇게 하면 create_table.sql이라는게 생성됩니다.
vi나 편집기로 여시면 REM이라는 코멘트로 앞부분이 막혀 있을 겁니다.
이걸 모두 없애시면 그야말로 DBMS내에 있는 형태 그대로 만들어진 Table Create Scripts입니다.

오라클 9i Release 2 기준으로 작성되었음.

◈ SGA 정보보기(정상 설치/실행 상태인가?)

$ sqlplus system/manager
SQL> show sga

◈ DBA로 접속

$ sqlplus "sys/passwd as sysdba"

◈ DB 시작
-- 일반 시작

$ sqlplus "sys/passwd as sysdba"
startup -- DB 인스턴스 시작
startup force -- DB가 실행중일 경우 강제로 종료했다 시작
startup restrict -- 일반 사용자 접근 금지 상태로 DB 시작

-- 단계별 시작

$ sqlplus "sys/passwd as sysdba"
startup nomount; -- NO Mount 단계
alter database mount; -- Mount 단계
alter database open; -- Open 단계

◈ DB 종료

$ sqlplus "sys/passwd as sysdba"
shutdown normal -- 세션,트랜잭션 종료시까지 대기후 종료
shutdown transactional -- 트랜잭션 종료시까지 대기후 종료
shutdown immediate -- 즉시 종료. 모든 DML 롤백
shutdown abort -- 비정상 즉시 종료. 백업과 복구시에만 사용.

◈ 로그인 없이 SQL*Plus 만 실행하기

$ sqlplus "/nolog"

◈ Table 생성 스크립트 뽑아내기

$ exp mlb/mlb file=결과덤프파일.dmp compress=n rows=n tables=테이블명

◈ & 기호 이용하기

1. 첫번째 방법
SELECT 'You '||Chr(38)||' Me' FROM DUAL;

2. 두번째 방법
SET DEFINE OFF
SELECT 'You & me' FROM DUAL;

◈ 편집기 및 SQL*Plus 공통 설정 지정하기
$ORACLE_HOME/sqlplus/admin/glogin.sql에 SQL*Plus 실행시 항상 지정되는 전체 설정을 할 수 있다.
여기서 "ed" 명령으로 실행되는 에디터는 다음 처럼 지정할 수 있다.

DEFINE_EDITOR=gvim.exe

◈ 프로시져 혹은 함수등의 소스 뽑아내기

SET NEWPAGE 0
SET TERMOUT OFF
SET VERIFY OFF
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET LINESIZE 500
SPOOL procedure_name.sql
SELECT TEXT FROM USER_SOURCE WHERE NAME='프로시져혹은함수이름' ORDER BY LINE;
SPOOL OFF

이렇게 저장된 procedure_name.sql 에서 불필요한 부분을 삭제하한다. 각 줄의 공백은 SET TRIMSPOOL ON 에 의해 제거된다.
VIM 공백 제거 : :%s/ *$//g
그리고 이렇게 생성된 소스 맨 앞에 CREATE OR REPLACE PROCEDURE 등을 붙여서 각 프로시져 등의 생성 스크립트로 사용할 수 있게 된다.

◈ 오류 내역출력

SHOW ERRORS

직전에 발생한 오류 내역 출력

◈ 숫자 출력 범위 늘리기

SET NUM 15

숫자를 15자리까지 출력

◈ 쿼리 결과를 셸 스크립트로 자동 저장하기

#!/bin/sh
# 오늘 날짜를 셸 변수로 지정
TODAY=`date +"%Y%m%d"`

sqlplus username/password << ENDSQL
-- 아래는 출력시 불필요한 형식 꾸미기가 안들어가게 한다.
SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON

SPOOL 저장할파일_${TODAY}_ORIG
SELECT COL1 || '|' || COL2 || '|' || COL3 -- 실행할 쿼리
FROM MYTABLE;
SPOOL OFF
QUIT
ENDSQL

# 처음과 마지막 쿼리 잔재 제거. 일단 스풀을 실행해보고나서 파일 위,아래에 추가되는
# 불필요한 줄수를 알아본다. (여기서는 위에 2줄과 맨 아래 1줄)
sed -e "1,2d" -e "\$d" 저장할파일_${TODAY}_ORIG > 저장할파일_${TODAY}

# 최종적으로 "저장할파일_${TODAY}"만 남겨둔다
rm 저장할파일_${TODAY}_ORIG

위와 같은 내용으로 셸 스크립트를 만들면 SQL*Plus 로 쿼리 결과를 특정한 형식(여기서는 각 컬럼을 세로 바(|)로 나눈 형식)으로 뽑아낼 수 있다.
그리고 이 경우 셸 환경 변수 값을 TODAY 처럼 SQL 쿼리 문장에 삽입하는 것이 가능하다.

◈ 쿼리 수행 시간 알아내기

SET TIMING ON

이렇게 설정하고 쿼리를 날리면 쿼리 수행시간도 함께 출력된다.

◈ DBMS_OUTPUT.* 으로 출력되는 양 조절

SET SERVEROUTPUT ON SIZE 4000;
Execute DBMS_OUTPUT.PUT_LINE(SYSDATE

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;

22. User의 object 정보 조회

- user_objects 데이터 사전을 이용하시면 됩니다.


SQL>col object_name format a25;
SQL>SELECT object_name, object_type
        FROM user_objects
        WHERE object_type = 'INDEX';

23. GRANT받은 권한을 조회하는 QUERY


GRANT받은 권한을 조회하는 QUERY
==============================
SELECT USERNAME, ROLENAME, PRIVILEGE
FROM (SELECT DECODE(SA1.GRANTEE#, 1, 'PUBLIC', U1.NAME) username,
             SUBSTR(U2.NAME,1,20) rolename,
             SUBSTR(SPM.NAME,1,27) PRIVILEGE
      FROM SYS.SYSAUTH$ SA1,
           SYS.SYSAUTH$ SA2,
           SYS.USER$ U1,
           SYS.USER$ U2,
           SYS.SYSTEM_PRIVILEGE_MAP SPM
      WHERE SA1.GRANTEE# = U1.USER#
      AND SA1.PRIVILEGE# = U2.USER#
      AND U2.USER# = SA2.GRANTEE#
      AND SA2.PRIVILEGE# = SPM.PRIVILEGE
      UNION
      SELECT U.NAME username, NULL rolename, SUBSTR(SPM.NAME,1,27) privilege
      FROM SYS.SYSTEM_PRIVILEGE_MAP SPM, SYS.SYSAUTH$ SA, SYS.USER$ U
      WHERE SA.GRANTEE#=U.USER#
      AND SA.PRIVILEGE#=SPM.PRIVILEGE)
WHERE USERNAME = 'SCOTT';



출처 : http://blog.empas.com/bjbjhong/7298879  and some additional query..

racle 10g에서는  내장 (Built-in) 롤 중 CONNECT 롤 및 RESOURCE 롤에 부여된 권한 변화

1. 새로운 undo tablespace를 다음과 같이 생성한다.

CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE '/oradata/undotbs02.dbf' SIZE 2048M;

2. undo tablespace의 이름을 변경하려면 다음과 같이 실행한다.

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

3 기존 undo tablespace 를 drop한다.

DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

4. drop 한 undo datafile을 지운다

 

실행 계획을 최적화하기 위해 Oracle Database 11 g 의 SQL 계획 관리 기능 사용

 

제대로 작동하던 데이터베이스 쿼리의 성능이 갑자기 떨어지는 상황이 발생한 적이 있습니까? 여러분의 십중팔구는 실행 계획의 변경사항에서 그 원인을 찾으려 했을 것입니다. 더 자세한 분석에서는 이러한 성능 변화가 해당 쿼리에서 참조한 테이블과 인덱스에 대해 새로 수집된 옵티마이저 통계 때문인 것으로 밝혀졌을 수도 있을 것입니다.

 

이런 상황에 당황하여 통계 수집을 중단하기로 성급하게 결정한 적이 있으십니까? 그러한 조치는 실행 계획을 해당 쿼리에 대해 거의 동일하게 유지시키지만 다른 것들은 더욱 악화시킬 수 있습니다. 오래된 통계를 통해 생성된 차선의 실행 계획은 다른 쿼리의 성능 또는 다른 술어(WHERE 절)를 가진 동일한 쿼리의 성능을 악화시킵니다.

 

다음에 어떤 조치를 취하든 여기에는 일부 위험이 따르기 마련입니다. 따라서 어떻게 하면 위험을 완화하고, 옵티마이저 통계가 정기적으로 수집되고, 모든 SQL문을 크게 변경하지 않고도(힌트 추가 등) 제대로 수행할 수 있는 정상적인 환경을 유지하면서, 생성된 SQL문에 대한 실행 계획을 최적으로 유지할 수 있겠습니까? 여러분 중에는 저장된 개요를 사용하여 계획을 유지하려 하는 분도 있을 것입니다. 하지만 그러한 행동은 옵티마이저가 도움이 될만한 실행 계획을 생성하는 것을 방해할 뿐입니다.

 

Oracle Database 11g에서는 새로운 SQL 계획 관리 기능을 사용하여 실행 계획이 계속해서 어떻게 변하는지 확인하고, 실행 계획을 사용하기 전에 먼저 데이터베이스에서 실행하여 계획을 확인하며, 통제된 방식으로 계획이 더 나아지도록 점차 개선할 수 있습니다.

 

SQL 계획 관리

 

SQL 계획 관리를 사용하는 경우, 옵티마이저는 특별 저장소인 SQL 관리 베이스에 생성된 실행 계획을 저장합니다. 특정 SQL문에 대해 저장된 모든 계획은 해당 SQL문의 계획 내역의 일부가 됩니다.

 

내역의 일부 계획은 "수락됨(Accepted)"으로 표시될 수 있습니다. SQL문이 재분석될 경우 옵티마이저는 내역 중 수락된 계획만을 고려합니다. 해당 SQL문에 대해 수락된 계획 세트를 SQL 계획 기준선 (SQL Plan Baseline) 또는 줄여서 기준선 (Baseline)이라고 합니다.

 

그러나 옵티마이저는 계속해서 더 나은 계획을 생성하기 위해 노력합니다. 옵티마이저가 새로운 계획을 생성한 경우, 계획 내역에 해당 계획을 추가하지만, 이 계획이 기준선의 수락된 모든 계획보다 더 나은 경우가 아니라면 SQL을 재분석할 때 고려하지 않습니다. 따라서 SQL 계획 관리를 사용하면 SQL문이 갑자기 효과가 떨어지는 계획을 가져와서 성능이 떨어지는 결과를 초래하는 현상이 발생하지 않습니다.

SQL 계획 관리를 사용하면 SQL문의 계획 내역에서 사용 가능한 모든 계획을 검사하고, 상대적인 효율성을 비교할 수 있습니다. 또한 특정 계획이 수락 상태로 되도록 하기도 하며 심지어 계획을 영구(고정) 계획이 되게 할 수도 있습니다.

 

이 문서에서는 SQL문의 최적 성능을 보장하기 위해 명령 행에서 Oracle Enterprise Manager 및 SQL을 사용하여 기준선의 캡처, 선택 및 개선을 포함한 SQL 계획 기준선의 관리 방법에 대해 알아봅니다.

 

캡처

 

SQL 계획 관리의 캡처 기능은 SQL문에서 사용한 다양한 옵티마이저 계획을 캡처합니다. 기본적으로 캡처 기능은 비활성화되어 있습니다. 즉, SQL 계획 관리에서 분석 또는 재분석중인 SQL문의 내역을 캡처하지 않습니다.

 

한 세션에서 파생된 몇몇 SQL문 예제에 대한 기준선을 캡처해 보겠습니다. 여기서는 Oracle Database 11 g 과 함께 제공된 샘플 스키마(SH)와 SALES 테이블을 사용할 것입니다.

 

먼저 세션에서 기준선 캡처 기능을 활성화합니다.

 

alter session <br /> set optimizer_capture_sql_plan_baselines = true;<br />

 

이제 이 세션에서 실행된 모든 SQL문이 해당 최적화 계획과 함께 SQL 관리 베이스에 캡처됩니다. SQL문의 계획은 변경될 때마다 계획 내역에 저장됩니다. 이를 확인하려면 목록 1에 표시된 스크립트를 실행해 보십시오. 이 스크립트는 완전히 동일한 SQL을 다른 환경에서 실행합니다. 먼저, SQL이 모든 기본값으로 실행됩니다(암시적 기본값 optimizer_mode = all_rows 포함). 다음 실행 시, optimizer_mode 매개 변수 값은 first_rows로 설정되어 있습니다. SQL의 세 번째 실행 전에 테이블 및 인덱스에 대한 새로운 통계를 수집합니다.

 

코드 목록 1: SQL 계획 기준선 캡처

alter session set optimizer_capture_sql_plan_baselines = true;<br /> -- First execution. Default Environment<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br /> -- Change the optimizer mode<br /> alter session set optimizer_mode = first_rows;<br /> -- Second execution. Opt Mode changed<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br /> -- Gather stats now<br /> begin<br /> dbms_stats.gather_table_stats (<br /> ownname =&gt; 'SH',<br /> tabname =&gt; 'SALES',<br /> cascade =&gt; TRUE,<br /> no_invalidate =&gt; FALSE,<br /> method_opt =&gt; 'FOR ALL INDEXED COLUMNS SIZE AUTO',<br /> granularity =&gt; 'GLOBAL AND PARTITION',<br /> estimate_percent =&gt; 10,<br /> degree =&gt; 4<br /> );<br /> end;<br /> /<br /> -- Third execution. After stats<br /> select * /* ARUP */ from sales<br /> where quantity_sold &gt; 1 order by cust_id;<br />

 

목록 1의 SQL 실행마다 계획이 변경될 경우, 해당 SQL문의 계획 내역에 다른 계획이 캡처됩니다. (/* ARUP */ 코멘트는 공유 풀의 특정 SQL문을 쉽게 식별합니다.)

계획 내역을 확인하는 가장 쉬운 방법은 Oracle Enterprise Manager를 사용하는 것입니다. Database 기본 페이지에서 Server 탭을 선택한 다음 SQL Plan Control을 클릭합니다. 페이지에서 SQL Plan Baseline 탭을 선택합니다. 그림 1처럼 해당 페이지에서 ARUP 이름이 포함된 SQL문을 검색합니다. 그러면 화면의 하단부에 SQL문에 대한 계획 내역이 표시됩니다.

 

그림 1: SQL 계획 내역

SQL 계획 이름(예: SYS_SQL_PLAN_27a47aa154bc8843)을 클릭하면 계획 내역에 저장된 계획의 세부 정보를 확인할 수 있습니다. 화면의 중요한 열은 다음과 같습니다.

• Enabled는 계획의 활성 여부를 나타냅니다.
• Accepted는 옵티마이저에서 계획을 고려하는지 여부를 나타냅니다. 하나 이상의 계획이 수락된 경우 옵티마이저는 최적의 계획을 선택합니다.
• Fixed는 계획이 해당 SQL문에 영구적으로 사용될 것인지 여부를 나타냅니다. 하나 이상의 계획이 고정된 경우 옵티마이저는 최적의 계획을 선택합니다.
• Auto Purge는 계획이 사용되지 않는 경우, 일정 기간 후에 계획 내역에서 해당 계획이 자동으로 삭제되는지 여부를 나타냅니다. 자동 삭제가 활성화된 경우, 지정한 기간 후에 계획 내역에서 사용되지 않는 계획이 자동으로 삭제됩니다. 사용되지 않는 계획이 삭제되도록 지정한 기간은 그림 1의 Plan Retention(Weeks) 레이블 옆에 표시되어 있습니다. 이 경우 53주로 설정되어 있지만 Configure 버튼을 클릭하여 변경할 수 있습니다.

또한 Settings 섹션에서 적절한 링크를 통해 이 Oracle Enterprise Manager 화면에서 SQL 계획 기준선의 캡처 및 사용을 활성화할 수도 있습니다.

 

커서 캐시 또는 SQL 튜닝 세트에서 SQL 계획 기준선에 계획을 로드할 수도 있다는 것을 참고하십시오. 계획을 SQL 계획 기준선에 수동으로 로드한 경우, 이 로드된 계획은 수락된 계획으로 추가됩니다. 자세한 내용은 Oracle Database Performance Tuning Guide의 15장 "Using SQL Plan Management"를 참조하십시오.

 

기준선 사용

 

SQL 계획 기준선을 캡처한 경우, 옵티마이저를 활성화하여 해당 기준선을 사용할 수 있습니다.

 

alter session set <br /> optimizer_use_sql_plan_baselines = true;<br />

 

기준선 사용이 활성화된 경우, 옵티마이저가 SQL문을 재분석할 때 해당 SQL문의 기준선에 저장된 계획을 검사하고 최적의 계획을 선택합니다. 이것이 바로 기준선의 가장 중요한 이점입니다. 또한 옵티마이저는 SQL문을 계속 재분석하며(기준선의 존재가 이를 방해하지 않음), SQL의 계획 내역에 새로 생성된 계획이 없는 경우, 이 계획을 추가하지만 “Accepted” 상태로 추가하지는 않습니다. 따라서 새로 생성된 계획이 더 나쁜 경우에도 그 계획은 사용되지 않기 때문에 SQL 성능은 영향을 받지 않습니다. 그러나 데이터 배포 및 애플리케이션 논리에 관한 개인적인 지식에 따라 새 계획이 더 낫다고 결정하는 경우도 있습니다. 예를 들어, 테이블이 실제로 비어 있을 때 계획이 캡처됐다고 가정해 봅시다. 이 경우 옵티마이저는 인덱스 검색을 선택합니다. 그러나 여러분은 나중에 SQL문을 호출하기 전에 애플리케이션이 테이블을 채운다는 것과 전체 테이블 검색이 계획에는 결국에는 더 좋다는 것을 알고 있습니다. 이런 상황에서는 새 계획을 검사하여 더 나은 경우 이를 수락할 수 있습니다. 그리고 옵티마이저는 그 후에 이 계획을 고려하게 됩니다. 이 경우 좋은 점은 항상 좋은 계획이 사용되고, 옵티마이저가 더 나은 계획을 생성한 경우에는 비교하여 사용할 수 있다는 것입니다.

SQL문의 기준선의 계획을 사용하지 않고자 하는 경우에는 SQL문을 호출하기 전에 세션에서 다음과 같은 문을 사용하여 기준선 사용을 비활성화할 수 있습니다.

 

alter session set <br /> optimizer_use_sql_plan_baselines = false;<br />

 

목록 2에서는 동일한 쿼리를 두 번 실행합니다. 먼저 기준선을 활성화하여 실행한 다음, 기준선을 비활성화하고 실행합니다. 여기서 기준선을 비활성화한 후 계획이 어떻게 변경되는지 확인할 수 있습니다. 맨 처음 옵티마이저는 SALES_TIME_BIX 인덱스에서 BITMAP INDEX FULL SCAN을 선택했습니다. 기준선을 비활성화한 후에는 계획이 SALES 테이블의 TABLE ACCESS FULL로 바뀌었습니다. 이는 이 계획이 옵티마이저 통계와 옵티마이저에 지금 당장 영향을 미치는 다른 변수를 기준으로 최적의 계획으로 여겨지기 때문입니다. 먼저 기준선을 활성화한 경우에서는 옵티마이저가 기준선에 저장된 수락된 계획 세트에서 최적의 계획을 선택했습니다.

코드 목록 2: SQL 계획 기준선 사용

SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> PLAN_TABLE_OUTPUT<br /> ---------------------------<br /> Plan hash value: 143117509<br /> --------------------------------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |<br /> | 4 | BITMAP CONVERSION TO ROWIDS | |<br /> | 5 | BITMAP INDEX FULL SCAN | SALES_TIME_BIX |<br /> --------------------------------------------------------------<br /> -- Now disable baselines and look at the latest plan<br /> SQL&gt; alter session set optimizer_use_sql_plan_baselines = false;<br /> Session altered.<br /> SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> PLAN_TABLE_OUTPUT<br /> ----------------------------<br /> Plan hash value: 3803407550<br /> --------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS FULL | SALES |<br /> --------------------------------------<br />

 

관리 및 개선

 

특정 SQL문에 대한 기준선을 만든 후, 그림 1에 표시된 Oracle Enterprise Manager 화면에서 연관된 계획 이름을 클릭하여 (Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab) 기준선을 검사하고 계획의 세부 정보를 확인할 수 있습니다. 특정 계획이 좋아지지 않을 경우, Disable 버튼을 클릭하여 계획을 완전히 비활성화할 수 있습니다. 나중에 생각이 바뀔 경우에는 다시 Enable 버튼을 클릭할 수 있습니다.. Drop 버튼을 사용하면 SQL 관리 베이스에서 계획이 완전히 삭제됩니다. 계획이 사용되지 않고 보관 기간이 경과하면 해당 계획은 자동으로 삭제된다는 점을 참고 하십시오.

 

현재 기준선의 계획이 최적이 아니고 계획 내역의 다른 계획이 더 낫다고 생각되는 경우, 진화 함수(Evolve Function)를 사용하여 계획의 성능을 비교해볼 수 있습니다(Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline 탭 또는 명령 행에서 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 함수 사용). 진화 함수를 사용하려면 그림 1의 Oracle Enterprise Manager 화면에서 비교할 계획을 선택한 다음 Evolve 버튼을 클릭합니다. 그러면 옵티마이저가 선택한 최고의 계획과 여러분이 선택한 계획 간의 비교가 수행됩니다. 이 기능으로 목록 3에서 보여지는 것과 같은 보고서가 생성됩니다. 목록 상단의 다음 행을 주목하십시오:

 

코드 목록 3: 기준선 개선 보고서

-----------------------------------------------------<br /> Evolve SQL Plan Baseline Report<br /> -----------------------------------------------------<br /> Inputs:<br /> ----<br /> PLAN_LIST = SYS_SQL_PLAN_27a47aa15003759b<br /> TIME_LIMIT = DBMS_SPM.AUTO_LIMIT<br /> VERIFY = YES<br /> COMMIT = YES<br /> Plan: SYS_SQL_PLAN_27a47aa15003759b<br /> ----------------------<br /> Plan was verified: Time used 41.06 seconds.<br /> Failed performance criterion: Compound improvement ratio &lt; .36<br /> Baseline Plan Test Plan Improv. Ratio<br /> -------------- --------- ------------- <br /> Execution Status: COMPLETE COMPLETE<br /> Rows Processed: 0 0<br /> Elapsed Time(ms): 5036 1033 4.88<br /> CPU Time(ms): 254 700 .36<br /> Buffer Gets: 1728 43945 .04<br /> Disk Reads: 254 22 11.55<br /> Direct Writes: 0 0<br /> Fetches: 49 22 2.23<br /> Executions: 1 1<br /> --------------------------------------------------------------------<br /> Report Summary<br /> --------------------------------------------------------------------<br /> Number of SQL plan baselines verified: 1.<br /> Number of SQL plan baselines evolved: 0.<br /> Failed performance criterion: <br /> Compound improvement ratio &lt; .36. <br />

 

이 행에서는 새로 고려된 계획이 원래 계획보다 성능이 떨어지는 것으로 표시됩니다. 따라서 옵티마이저의 최적 계획 선택을 위한 대체 항목으로서 거부되었습니다. 비교로 인해 개선 요인이 1보다 큰 것으로 드러나면 SQL 계획 관리에서는 해당 계획을 옵티마이저가 고려할 후보로 수락했을 것입니다.

진화 함수를 통한 결정이 정확하지 않다고 생각되어 옵티마이저가 한 특정 계획을 사용하도록 하고 싶은 경우에는 어떻게 하시겠습니까? 이는 계획이 기준선에 고정되도록 함으로써 할 수 있습니다. 목록 4처럼 dbms_spm 패키지에서 alter_sql_plan_baseline 함수를 실행하여 계획이 고정되게 할 수 있습니다.

 

코드 목록 4: 계획 기준선 고정

declare<br /> l_plans pls_integer;<br /> begin<br /> l_plans := dbms_spm.alter_sql_plan_baseline (<br /> sql_handle =&gt; 'SYS_SQL_f6b17b4c27a47aa1',<br /> plan_name =&gt; 'SYS_SQL_PLAN_27a47aa15003759b',<br /> attribute_name =&gt; 'fixed',<br /> attribute_value =&gt; 'YES'<br /> );<br /> end;<br /> -- Now examine the plan:<br /> SQL&gt; explain plan for select * /* ARUP */ from sales<br /> 2 where quantity_sold &gt; 1 order by cust_id;<br /> Explained.<br /> SQL&gt; select * from table(dbms_xplan.display(null, null, 'basic'));<br /> Plan hash value: 143117509<br /> --------------------------------------------------------------<br /> | Id | Operation | Name |<br /> --------------------------------------------------------------<br /> | 0 | SELECT STATEMENT | |<br /> | 1 | SORT ORDER BY | |<br /> | 2 | PARTITION RANGE ALL | |<br /> | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |<br /> | 4 | BITMAP CONVERSION TO ROWIDS | |<br /> | 5 | BITMAP INDEX FULL SCAN | SALES_PROMO_BIX |<br /> --------------------------------------------------------------<br />

출력 결과로부터 새로운 계획에서 이전 계획에서 사용되었던 (그리고 목록 2에서 보여진) SALES_TIME_BIX 인덱스 대신 SALES_PROMO_BIX 인덱스를 사용한 것을 알 수 있습니다. 이제 새 계획이 고정됩니다.

그렇다면 고정된 계획을 어디서 사용할 수 있을까요? SQL문의 계획이 최적이 아니며 (예를 들어, 계획이 SALES_TIME_BIX 인덱스를 사용할 경우 더 효율적일 수 있는데 SALES_PROMO_BIX 인덱스를 사용하는 계획) 코드를 변경하여 힌트를 배치할 수 없는 상황을 가정해 보십시오. 이 경우 다음 단계를 수행할 수 있습니다.

 

1. 다른 세션에서 optimizer_mode 매개 변수를 목록 1에서 보여진 것처럼 원하는 계획을 생성하는 값으로 변경합니다.

2. SQL문을 실행하고, 목록 1처럼 기준선을 캡처한 다음, 세션의 연결을 끊습니다.

3. 목록 4에서 보여진 것처럼 SALES_TIME_BIX 인덱스를 사용하여 계획을 고정으로 표시합니다. SQL 핸들과 계획 이름을 여러분의 케이스에 해당하는 이름으로 바꾸는 것을 기억하십시오.

 

계획이 고정으로 표시되고 나면 SQL문은 옵티마이저가 생성한 계획이 아닌 해당 계획만을 사용하게 됩니다. 하나 이상의 고정된 계획이 존재하는 경우, 옵티마이저는 최적의 계획을 선택합니다.

 

동일한 방법을 사용하여 데이터베이스 업그레이드 중 SQL문의 안정적인 실행 경로를 보장할 수도 있습니다. 먼저 시스템 매개 변수 optimizer_capture_sql_plan_baselines를 true로 설정하여 데이터베이스에서 모든 SQL문에 대한 기준선을 수집한 다음, 중요한 SQL문마다 하나의 계획만 고정으로 표시되도록 합니다. 그런 다음 점진적으로 계획을 "고정 해제"하고 진화 함수를 사용하여 다른 최적의 계획이 있는지 확인합니다. 옵티마이저가 나중에 생성한 계획이 더 나쁠 경우에는 항상 이전의 고정된 계획으로 되돌릴 수 있습니다.

 

결론

 

저장된 개요 또한 계획을 안정적으로 만들지만 융통성이 없게 합니다. 옵티마이저는 SQL문에 대한 개요가 있는지 확인하고 새로운 계획 생성을 중지합니다. 한편 기준선은 옵티마이저가 새로운 계획을 생성하는 것을 절대 중단하지 않습니다.

 

SQL 계획 관리 기능을 사용하면 SQL문에 대한 검증된 계획이나 잘 알려진 계획을 기준선의 형식으로 저장할 수 있습니다. 그러면 갑작스런 성능 저하를 진단할 때 매우 유용합니다. 기준선(및 해당 계획)이 저장소에 저장되기 때문에 기준선 및 계획을 비교하여 가장 효율적으로 사용할 수 있도록 결정할 수 있습니다.

 

필자소개

 

Arup Nanda ( arup@proligence.com ) 는 성능 조정에서 보안 및 재해 복구에 이르기까지 모든 데이터베이스 관리 측면을 관리하는 Oracle DBA로서 14년 이상 근무해 왔습니다. 그리고 2003년 Oracle Magazine에 의해 올해의 DBA로 선정된 바 있습니다

 

출처 : 한국 오라클

제공 : DB포탈사이트 DBguide.net

출처 : http://applejara.tistory.com

가끔 보면 행이 null인지 아니면 공백인지 구분이 안될때가 있다. 오라클 사용자는 대부분 Toad같은 툴을 사용하기 때문에 Toad를 기준으로 null값을 확인하는 방법을 알아보자.

일단 기본적인 화면에선 null은 표시되지 않는다. 그럼 null값을 표시하는 설정을 확인해 보자. 위치는 view -> toad options -> data grids , visual 이다. 필자는 toad 9.6을 사용하므로 버젼에 따라 조금씩의 차이가 있을 수 있다.

최 하단에 null columns가 있는데 여기에 옵션을 선택할 수 있다. 필자는 노란색으로 표시하기로 했지만, null이란 문자로 출력하게 바꿀수도 있다.

설정 후 null값은 노란색 배경으로 출력됨을 확인할 수 있다.