# SQL 쿼리 실습을 위한 DB 서버 구축 > [개발자를 위한 오라클 SQL 튜닝](https://www.hanbit.co.kr/store/books/look.php?p_code=E9267570814) 내용에서 참고한 내용입니다. ## Intro * 쿼리 학습을 위한 DBMS 구축 * [Oracle with Docker](broken://pages/-MhiiTOSk8_LUaFG1Q0d) ## 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. ``` * 생성된 테이블 스페이스, 임시 테이블 스페이스 파일 ![생성된 오라클 테이블스페이스 및 임시 테이블 스페이스](/files/-MeQTET6hp07TPO0Tral) > 사용자 계정 생성 * 앞서 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](https://dba.stackexchange.com/questions/58917/ora-30009-not-enough-memory-for-connect-by-operation) * 우선 사용자 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; ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://seokrae.gitbook.io/sr/book/tune/_1.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.