dbms. 오라클(Oracle) 관련 정리

예전에 정리했던 내용들입니다.

기동과 종료



  • 오라클 서버가 시작되지 않았을 때의 에러메시지
ORA-01034 : ORACLE not available
ORA-27101 : shared momory realm does not exist 
  • 서버 시작시키기
sqlplus /nolog 로 실행 
sql> connect sys/pass as sysdba 
sql> startup
  • 리스너 시작시키기
lsnrctl start ( 쉘에서 실행한다 ) 

테이블스페이스와 사용자 관리



테이블스페이스

생성

CREATE tablespace TABLESPACE_NAME
datafile '/xxx/yyyy.dbf'
size 10240M AUTOEXTEND ON NEXT 5M online;
online으로 설정하여 테이블 스페이스를 생성하면 테이블스페이스를 생성함과 동시에
데이터베이스 사용자들이 사용가능하다는 것을 의미하며 일반적으로 online으로 설정하여 사용한다.

수정

  • 테이블스페이스 online / offline
ALTER tablespace tablespace_name offline;
ALTER tablespace tablespace_name online;
  • 생성된 테이블 스페이스에 데이터파일 추가하여 공간 늘여주기
ALTER tablespace info_data
ADD datafile '/oracle/infodata/infodata/dbf'
size 100m;
  • 생성된 테이블 스페이스 크기 변경하기
ALTER DATABASE datafile '/oracle/infodata/infodata/dbf'
RESIZE 200M;
  • 테이블스페이스 변경하기
ALTER tablespace tablespace_name
DEFAULT storage(
     initial        1024k
     next           2048k
     minextents     1
     maxextents     5
)online ;
pctincrease 기본이 50%이다
  • 테이블스페이스 자동확장 추가 (Automatic Extension)
ALTER tablespace tax2110
ADD datafile '/home/aaa/bbb.dbf'
size 50m
autoextend ON next 10m
maxsize 100m;
maxsize 를 지정할때 기존 데이터 파일보다 크거나 같아야함

삭제

/** tablespace 삭제 **/
DROP tablespace TABLESPACE_NAME;
 
/** 테이블 스페이스에 포함된 객체를 소유자에 관계없이 모두 삭제한다 **/
DROP tablespace TABLESPACE_NAME including contents cascade constraints;
 
/** 테이블 스페이스와 연관되 실제 데이터 파일마저도 삭제한다. **/
DROP tablespace TABLESPACE_NAME including contents AND datafiles;

사용자

  • 생성
CREATE user USER_NAME IDENTIFIED BY USER_PWD DEFAULT tablespace TABLESPACE_NAME quota unlimited ON TABLESPACE_NAME;
/** quota unlimited on 은 해당 테이블스페이스를  모두 사용할 수 있다는 의미 **/
  • 권한부여
/** 데이터베이스 객체를 생성할 수 있는 권한, 데이터베이스 사용권한 부여 **/
GRANT connect, resource TO USER_NAME;
 
/** select 권한만 주기 **/
GRANT SELECT ON TABLE_NAME TO USER_NAME; 
/** grant select on OWNER.TABLE_NAME to USER_NAME; 식으로 다른 유저 다른 테이블스페이스에 대한 권한도 줄 수 있다. **/
 
/** select, update, delete 모든 권한 주기 **/
GRANT  ALL ON TABLE_NAME TO USER_NAME; 
 
/** 모든 테이블스페이스의 모든 테이블에 대한 select 권한 주기 **/
GRANT SELECT any TABLE TO USER_NAME
 
/** 프로시져 실행 권한 주기 **/
GRANT execute ON 프로시져명 TO USER_NAME;
  • 권한제거
REVOKE resource FROM USER_NAME
  • 사용자정보 변경
/** 사용자가 사용할 테이블 스페이스 영역을 할당한다. **/
ALTER USER USER_NAME QUOTA (2048M | UNLIMITED) ON TABLESPACE_NAME;
/** 패스워드를 변경한다 **/
ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWORD;
  • 디폴트 테이블스페이스 변경
/** 디폴트 테이블스페이스 변경 **/
ALTER USER testuser DEFAULT TABLESPACE tablespacename
 
/** 임시 테이블스페이스 변경 **/
ALTER USER testuser TEMPORARY TABLESPACE tablespacename
 
/** 디폴트 테이블스페이스와 임시 테이블스페이스 동시 변경 **/
ALTER USER testuser DEFAULT TABLESPACE tablespacename1
TEMPORARY TABLESPACE tablespacename2
  • 사용자 삭제
DROP user USER_NAME cascade;
  • 사용자 정보 조회
SELECT username, default_tablespace, temporary_tablespace FROM dba_users;

DDL



테이블 수정

  • 속성 부여하며 컬럼 추가
ALTER TABLE [TABLE_NAME] 
ADD ( [COLUMN_NAME] [COLUMN_TYPE] { DEFAULT [VALUE] } { NOT NULL } ); 
  • 컬럼 속성 변경
ALTER TABLE [TABLE_NAME] 
MODIFY ( [COLUMN_NAME] {NOT NULL....등등} ); 
  • 컬럼 제약조건 추가
ALTER TABLE [TABLE_NAME] 
ADD ( CONSTRAINT [CONSTRAINT_NAME] PRIMARY KEY( [COLUMN_NAME] ) ); 
  • 컬럼 삭제
ALTER TABLE [TABLE_NAME] 
DROP COLUMN [COLUMN_NAME]; 
  • 컬럼명 변경
ALTER TABLE [TABLE_NAME] 
RENAME COLUMN [COLUMN_NAME] TO [NEW_COLUMN_NAME]; 
  • 테이블명 변경
ALTER TABLE [TABLE_NAME] RENAME TO [NEW_TABLE_NAME]; 

제약 설정

  • PRIMARY KEY
생성 : ALTER TABLE TABLE_NAME ADD PRIMARY KEY( FIELD_NAME1, FIELD_NAME2 ); 
삭제 : ALTER TABLE TABLE_NAME DROP PRIMARY KEY; 
  • FOREIGN KEY
생성 : ALTER TABLE TABLE_NAME ADD  ( FOREIGN KEY ( FIELD_NAME ) REFERENCES RTABLE_NAME ) ; 
  • UNIQUE
생성 : ALTER TABLE TABLE_NAME ADD ( UNIQUE ( FIELD_NAME ) ); 

실행 계획



SQL 실행 절차

Client에서 수행된 SQL은 오라클 SGA의 Shared Pool에서 구문분석을 하면서 오라클 옵티마이저를 호출한다. 옵티마이저는 구문 분석 과정에 있는 SQL을 Rewrite한 후 Query 옵티마이징을 수행하며, 이 때 Rule Base 또는 Cost Base 옵티마이저 중 한가지를 선택하여 실행계획을 생성한 후 실행하게 된다.
오라클의 디폴트 옵티마이저 모드는 Choose로 되어있으며, 이는 Analyze를 통해 데이터를 수집했다면 Cost Base 로 수행되고, 아니라면 Role Base 로 수행된다.
1. Rule Base 옵티마이저
  • SQL의 Syntax 및 Where 조건으로 Index의 존재유무 등을 확인하여 실행 계획을 생성한다.
  • Rule Base 옵티마이저는 다음의 우선순위로 실행계획을 세운다.
    1. ROWID 를 통한 Table Access
    2. Unique Index 를 통한 Table Access
    3. Index 를 통한 Table Access
    4. Full Scan 을 통한 Table Access
  • SQL을 작성한 개발자의 의도대로 실행계획을 생성하기가 수월하다.
2. Cost Base 옵티마이저
  • 해당 테이블에 대한 Analyze를 수행하여 통계 데이터를 수집해 놓아야 사용 가능( Analyze 하는 동안은 테이블 전체에 Lock이 걸림 )

실행계획 조회

/** 1. Plan Table 생성 **/
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
/** 2. Plan 조회 **/
EXPLAIN PLAN FOR SELECT ....

실행계획 조회 - AUTOTRACE 이용

/** 1. Plan Table 생성 **/
/** 2. PLUSTRACE Role 생성 **/
@$ORACLE_HOME/sqlplus/admin/plustrace.sql ( sys 계정으로 수행 )
/** 3. 실행계획을 확인하고자 하는 유저에게 PLUSTRACE Role 부여 **/
/** 4. TRACE 옵션 설정 ( 실행계획만 볼 것인지, 실제 쿼리수해을 할 것인지 결정 ) **/
SET autotrace [ traceonly EXPLAIN | traceonly ]

실행계획 분석

1. Unique Index의 Unique Scan - 하나의 Index Entry만 Access → 옵티마이저의 높은 우선순위
SELECT STATEMENT
  TABLE ACCESS (BY INDEX ROWID) OF 사번
    INDEX (UNIQUE SCAN) OF 사번_IDX (UNIQUE)
2. Unique Index의 Range Scan
SELECT STATEMENT
  TABLE ACCESS (BY INDEX ROWID) OF 사번
    INDEX (RANGE SCAN) OF 사번_IDX (UNIQUE)
:?: Range Scan
  • Where 조건에 Like, Between, <, > 등의 범위를 나타내는 조건이 Index Scan을 할 경우 발생
  • Nonunique Index 에 대한 Equal 조건으로 조회하는 경우 발생
  • Unique Scan에 비해 추가적인 Index Entry Scan 발생
  • Reverse Key Index 는 Range Scan 을 지원하지 않음
  • IN 구문을 사용하여 튜닝 가능
3. Index Full Scan
  • Full Scan으로 인한 부하가 발생하는 Access Operation
  • 이미 저장되어있는 Index 순서로 데이터를 추출하므로 정렬을 하지 않아도 되는 장점을 가짐
  • 꺼내올 데이터들이 테이블 ROW의 상당부분이라면 추천, 극히 일부라면 모든 데이터를 Access 한 후 그냥 버리는 셈이므로 낭비
SELECT 사번, CODE FROM 사원 WHERE CODE = 'A' ORDER BY 사번;
->
SELECT STATEMENT
  INDEX (FULL SCAN) OF IDX

쿼리 관련 이슈



이스케이프 처리

SELECT ... LIKE 'AAA\_%' escape '\' 
escape를 '\'로 지정하면 '\' 뒤에 오는 문자는 와일드카드건 뭐건 단순 문자로 처리된다.

테이블에서 랜덤하게 ROW 하나만 뽑아오기

SELECT * FROM( 
    SELECT * FROM XXX ORDER BY DBMS_RANDOM.RANDOM 
) WHERE rownum = 1 

테이블간 자료이동 insert.. select, create.. select 등

  • create.. select..
CREATE TABLE TABLE1 AS SELECT * FROM TABLE2
  • insert.. select..
INSERT INTO TABLE1 SELECT FROM TABLE2 [ WHERE 어쩌구 저쩌구 ]
INSERT INTO TABLE1 ( COL1, COL2, COL3 ) SELECT  COL1, COL2, COL3 FROM TABLE2 [ WHERE 어쩌구 ]
  • LONG 타입 데이터 insert.. select..
CREATE TABLE t1(t1name long);
CREATE TABLE t2(t2name long);
CREATE global TEMPORARY TABLE t_long_temp(text_temp clob);
INSERT INTO t_long_temp SELECT to_lob(t1name) FROM t1;
INSERT INTO t2(t2name) SELECT  text_temp FROM t_long_temp;

인덱스



ROWID ( 각 Row들의 유일한 ID )
  • Object Number ( 32bit 6자리 ) : 해당 Row가 속하는 Object의 Number
  • 상대File Number ( 10bit 3자리 ) : Tablespace에서, Row가 어느 Datafile에 연결되있는지의 고유값
  • Block Number ( 22bit 6자리 ) : 저장된 Block 고유번호
  • Row Number ( 16bit 3자리 ) : Block안에서의 Row 고유번호
인덱스의 역기능
  • DML 작업에서 성능 저하의 원인
  • 잘못된 Index 선정은 스토리지 낭비 유발
B*TREE Index
  • 어떤 로우든지 같은 개수의 Block을 스캔해 접근 가능 ( Root-Branch-Leaf의 순서 )
  • Leaf 들은 Double Linked List로 연결되어있다. ( Range Scan시 활용 )
  • Root와 Branch 블록은 고유의 인덱스키를 가지고 있다. 검색대상의 인덱스키가 더 큰 값이면 왼쪽으로, 더 작은 값이면 오른쪽의 하위 노드로 분기하게 된다. Branch 블록은 인덱스의 크기에 따라 여러 단계가 존재할 수 있으며, 최 하위의 Leaf 블록은 인덱스키와, 실데이터를 가르키고 있는 ROWID를 쌍으로 가지고 있다.
  • 적은 Row Access에 유리
  • Cardinality가 낮은( 분포도가 나쁜 ) 컬럼에 대해서는 B*TREE Index가 불리
  • OR 연산자에 대해 Table Full Scan 위험 ( IN을 사용하는 쿼리로 튜닝 )
B*TREE Index 생성
CREATE (UNIQUE) INDEX idx_name ON tbl_name ( col_name );
  • UNIQUE 컬럼이라면 반드시 UNIQUE인덱스로 생성하도록 하자. UNIQUE 인덱스가 아닐 경우, 조건을 만족하는 값이 하나여도 ( ex - where col_name = 'aa' ) 옵티마이저는 그 사실을 알지 못하기 때문에 다음 노드까지 검색하는 Range Scan 계획을 세우게 된다.
Bitmap Index
  • DataWarehouse 등의 대용량 DB에서 Cardinality가 낮은 컬럼에 사용해야 함
  • DML 작업시 많은 부하를 발생시키는 구조
  • B*TREE Index에 비해 적은 저장공간 사용
Bitmap Index 생성
CREATE BITMAP INDEX idx_name ON tbl_name( col_name );
Reverse Key Index
  • B*TREE 인덱스와 같으나 index키를 거꾸로 뒤집어서 저장
  • B*TREE 인덱스의 단점인 좌측 Leaf Block에 대한 경합을 방지
  • Range Scan 불가
Reverse Key Index 생성
CREATE INDEX idx_name ON tbl_name( col_name ) REVERSE;
B*TREE Index를 Reverse Key Index로 변경
ALTER INDEX idx_name REBUILD REVERSE;
ALTER INDEX idx_name REBUILD NOREVERSE;
Function Based Index
  • 계산 값에 대해 Index Scan 가능
  • DML시 부하 발생
  • Index의 유연성 저하
  • Function Based Index를 생성하기 위한 필요조건
    1. Oracle Version 8.1.0 or Higher
    2. 해당 Table에 대한 Analyze 정보
    3. QUERY_REWRITE_ENABLED = TRUE 파라미터 ( ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; )
    4. QUERY_REWRITE_INTEGRITY = TRUSTED 파라미터 ( ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED )
    5. 해당 User에 QUERY REWRITE 시스템권한이 있어야 함
Function Based Index 생성
CREATE INDEX idx_name ON tbl_name( FUNCTION( col_name ) );




다른글 읽기