SQL 쿼리 실습을 위한 DB 서버 구축
Oracle 인프라 구축하기
개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.
Intro
쿼리 학습을 위한 DBMS 구축
Oracle with Docker
SQL 튜닝을 위한 학습 목표
여기서는 SQL문법에 대한 학습을 위한 준비를 하도록 판다.
데이터를 저장할 저장소와 사용자 계정 생성
학습 테이블을 생성
대용량의 더미 데이터를 입력
통계 정보 갱신
테이블 스페이스 및 계정 생성
테이블스페이스는 테이블이 저장될 공간을 뜻하며, 이를 나누어 관리함으로써 성능향상을 가져올 수 있다.
테이블은 기본 용량 8G를 보관할 수 있고, 추가 시 1G 단위로 테이블 스페이스의 크기를 확장할 수 있도록 설정한다.
오라클 내부에서는 데이터 블록(data block), 익스텐트(extent), 세그먼트(segment), 테이블 스페이스(tablespace)라는 논리적 개념으로 데이터를 관리한다.
데이터를 저장하는 가장 최소의 논리적 단위를 데이터 블록이라 한다.
데이터 블록이 모여서 익스텐트가 된다.
익스텐트가 모여서 세그먼트가 된다.
세그먼트가 모여서 테이블 스페이스가 된다.
실제로 물리적인 데이터 파일은 테이블 스페이스와 대응된다.
seok@SRMac ~ % docker exec -it sql_level /bin/bash
root@9f20c7673c75:/# sqlplus system/oracle
테이블 스페이스 생성
DBMSEXPERT_DATA 라는 이름으로 테이블 스페이스를 생성
새로 생성할 테이블 스페이스가 사용할 데이터 파일(.dbf)
데이터 파일이 다 채워진 경우 자동으로 데이터 파일을 확장 (다음 1G)
extent 할당과 관련된 정보를 테이블 스페이스 DATAFILE의 헤더에 비트맵으로 기록하고 사용(64kb 짜리 extent 할당 후, 확장 요청이 들어오면 더 큰 사이즈의 extent를 할당)
SQL>
CREATE TABLESPACE DBMSEXPERT_DATA
DATAFILE 'data_space.dbf' SIZE 8G
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Tablespace created.
임시 테이블 스페이스 생성
SQL>
CREATE TEMPORARY TABLESPACE DBMSEXPERT_TMP
TEMPFILE 'tmp_space.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Tablespace created.
생성된 테이블 스페이스, 임시 테이블 스페이스 파일

사용자 계정 생성
앞서 DBMSEXPERT_DATA와 DBMSEXPERT_TMP라는 테이블 스페이스를 관리하는 seok 계정을 신규로 생성
seok 계정을 통해 생성하는 테이블 및 인덱스는 모두 DBMSEXPERT_DATA 영역에 생성된다.
임시 영역이 필요한 경우 DBMSEXPERT_TMP영역을 사용한다.
SQL>
CREATE USER seok IDENTIFIED BY 1234
DEFAULT TABLESPACE DBMSEXPERT_DATA
TEMPORARY TABLESPACE DBMSEXPERT_TMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
User created.
권한 부여
SQL>
GRANT RESOURCE TO seok;
GRANT CONNECT TO seok;
GRANT CREATE VIEW TO seok;
GRANT CREATE SYNONYM TO seok;
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
테이블 생성
SQL>
CREATE TABLE TB_CUST
(
CUST_ID VARCHAR2(10), --고객ID
CUST_NM VARCHAR2(50), --고객명
BIRTH_DT VARCHAR2(8), --생일
INST_DT VARCHAR2(8), --입력일자
INST_ID VARCHAR2(10), --입력자ID
INST_NM VARCHAR2(50) --입력자명
);
CREATE TABLE TB_ORD
(
ORD_NO VARCHAR2(15), --주문번호
ORD_DT VARCHAR2(8), --주문일자
PRDT_CD VARCHAR2(6), --제품코드
ORD_AMT NUMBER(15), --주문금액
DIS_AMT NUMBER(15), --할인금액
INST_DT VARCHAR2(8), --입력일자
INST_ID VARCHAR2(10), --입력자ID
INST_NM VARCHAR2(50), --입력자명
CUST_ID VARCHAR2(10) --고객ID
);
더미 데이터 생성 및 복제
LOLOGGING 모드 설정
오라클에서 테이블에 해당 설정을 하면 테이블에 INSERT 작업 시 Redo 로그 작업을 최소화할 수 있다.
대용량의 데이터를 INSERT 작업할 때 데이터 입력 시간을 줄일 수 있다.
SQL>
ALTER TABLE TB_CUST NOLOGGING;
INSERT /*+ APPEND */ INTO TB_CUST --고객테이블
SELECT
LPAD(TO_CHAR(ROWNUM), 10, '0'), --고객ID
DBMS_RANDOM.STRING('U', 10), --고객명
TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,36500)), 'YYYYMMDD'), --생일
TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
'DBMSEXPERT', --입력자ID
'username' --입력자명
FROM DUAL CONNECT BY LEVEL <= 500000;
COMMIT;
# [2021-07-11 17:16:14] 500,000 rows affected in 40 s 270 ms
이슈 1
[99999][30009] ORA-30009: Not enough memory for CONNECT BY operation Position: 12
우선 사용자 50만건, 주문 정보 2500만건으로 건수를 줄여서 테스트 하도록 한다.
PGA, 즉 하나의 프로세스 또는 스레드를 위한 개별적인 메모리 할당 영역, 프로세스 고유 영역의 용량을 늘려 해결 할 수 있지만, XE 버전은 수정 불가
oracle 메모리 확인
SQL> show sga
Total System Global Area 601272320 bytes
Fixed Size 2228848 bytes
Variable Size 188747152 bytes
Database Buffers 406847488 bytes
Redo Buffers 3448832 bytes
Oracle이 테이블에 데이터를 입력할 때의 단계
데이터 버퍼 캐시(Data Buffer Cache)를 경유
테이블 세그먼트의 비어있는 블록(Free Block)을 검색
비어있는 블록에 데이터를 저장
APPEND 힌트를 사용하는 경우
세그먼트의 HWM(High Water Mark) 바로 뒤부터 데이터를 입력하게 된다.
HWM은 세그먼트의 가장 끝이라고 볼 수 있다.
데이터 버퍼 캐시를 경유하지 않고 바로 데이터를 저장하게 되므로 데이터의 입력 시간을 단축할 수 있다.
데이터의 복제 방법
Cartesian Product Join
계층형 쿼리
RANDOM 함수의 사용
테이블 구성 시 특정 값을 인위적으로 만들기 위한 RANDOM 함수
기본적으로 실수를 리턴하기 때문에 TRUNC 함수로 감싸 정수를 리턴하도록 한다.
SQL>
ALTER TABLE TB_ORD NOLOGGING;
INSERT /*+ APPEND */ INTO TB_ORD --APPEND힌트 사용
SELECT
LPAD(TO_CHAR(ROWNUM), 15, '0'),--주문번호
TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,3650)), 'YYYYMMDD'), --주문일자
DBMS_RANDOM.STRING('X', 6), --제품코드
TRUNC(DBMS_RANDOM.VALUE(1000, 100000)), --주문금액
TRUNC(DBMS_RANDOM.VALUE(100, 10000)), --할인금액
TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
'DBMSEXPERT', --입력자ID
'user', --입력자명
A.CUST_ID--고객ID
FROM TB_CUST A, ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 50);
COMMIT;
# [2021-07-11 17:58:22] 25,000,000 rows affected in 41 m 4 s 995 ms
기본키(Primary Key), 외래키(Foreign Key) 설정
SQL>
ALTER TABLE TB_CUST
ADD CONSTRAINT TB_CUST_PK
PRIMARY KEY (CUST_ID);
ALTER TABLE TB_ORD
ADD CONSTRAINT TB_ORD_PK
PRIMARY KEY (ORD_NO);
ALTER TABLE TB_ORD
ADD CONSTRAINT TB_ORD_FK
FOREIGN KEY (CUST_ID) REFERENCES TB_CUST(CUST_ID);
-- 통계 정보 갱신
SQL>
ANALYZE TABLE TB_CUST COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;
ANALYZE TABLE TB_ORD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;
Last updated
Was this helpful?