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

• dba.stackexchange.com
• 우선 사용자 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;